A lot of Database Connections

[jnunez]

Hello everybody, I have the following escenary: 

I have a EC2 instances cluster and a RDS Database MySQL server, all in AWS. Our website has 1500 visitors per minute, and in a specific hour of the day, the connections pass from 10 to 1200 per second, with 3 EC2 intances, then there are some intermittences.

This is my revive DB configuration

[database]
type=mysql
host="my.db.com"
socket="/var/run/mysqld/mysqld.sock"
port=3306
username=revive
password="mypass"
name=ultra_ads
persistent=
mysql4_compatibility=1
protocol=tcp
compress=
ssl=
capath=
ca=

[databaseCharset]
checkComplete=1
clientCharset=

[databaseMysql]
statisticsSortBufferSize=

[databasePgsql]
schema=

Please, tell me if you need any more information or configuration file, I'm really new to this kind of service.

[Ian]

So is there an actual issue you encounter? Or you simply find it a lot? You could try implementing memcache ...

[jnunez]

5 hours ago, Ian van Marwijk said:

So is there an actual issue you encounter? Or you simply find it a lot? You could try implementing memcache ...

 

Hi Ian. How are you?. Sorry I didn' explain it completly. 

Well, I have a problem: 

I have only one website with my banners, when there are more than 1200 visitors on the website at 10:00am, the databases connections passed from 10 to more than 1200 connections per second (My RDS instance connection limit is 1250ps) then the revive platform fails and in the apache error.log I can see this error: 

[Mon Aug 29 11:02:01.284807 2016] [:error] [pid 3898] [client 10.240.122.187:52538] PHP Fatal error:  Call to undefined method MDB2_Error::setFetchMode() in /var/www/html/lib/pear/DB/QueryTool/Query.php on line 299

My infrastructure consist: 

-A Load balancer

-From 1 to 6 instances (Ussually has 2 or 3 with more than 1200 visitors) with 1.6GB of RAM and a 1 CPU 2.5 Ghz

-A Database instance (RDS) 2 CPU and 15GB of RAM.

-A bucket S3 with all images with a CDN. 
 

 

It is normal that generates so many connections?

How I do an memache with a revive cluster? 

[Jimmy T.]

The problem could possibly be that your MYSQL server has reached max_connections which you can solve by editing /etc/my.cnf.

Before making any changes I recommend checking the logs and running mysqltuner.pl on your server.

[Ian]

@jnunez

Jimmy T. gave a good tip there as well.

To enable memcache (be sure to have memcached running, and to have the php module installed on all delivery boxes!!) : 

Go to Plugins --> Delivery cache store, click on "Details".

Go to OXmemcached --> Settings--> OXmemcached configuration settings,

in that "List of memcached server" it holds local ip address and port number (typically 127.0.0.1:11211 .... Just set the correct value and check to the working

 

----

Go to Configuration-->Global Settings --> Banner Delivery Settings --> Banner Delivery Cache Settings,

Under this, there will be "Banner Delivery Cache Store Type", choose memcached.

 

 

If you need more help, feel free to PM me!

[firstimpression]

Memcached in ANY more or less intense environment is a must. It can reduce MySQL connections count by 90%, depending on number of zones/ads of your adserver. 

RDS - make sure you place in the same availability zone with delivery servers.

Do not use S3 for images delivery. Instead, set up an nginx server for that - S3 traffic is is more expensive, although, maybe it knows to deliver images faster across the world.

[jnunez]

10 minutes ago, firstimpression said:

Memcached in ANY more or less intense environment is a must. It can reduce MySQL connections count by 90%, depending on number of zones/ads of your adserver. 

RDS - make sure you place in the same availability zone with delivery servers.

Do not use S3 for images delivery. Instead, set up an nginx server for that - S3 traffic is is more expensive, although, maybe it knows to deliver images faster across the world.

Hi first*... Thank you very much for your help. 

What is the best configuration for memcache? 

 

Edited September 8, 2016 by jnunez
Language