Issue with; 'max_user_connections' - Please Help

[claudio]

Hi,

 

My site goes down every so often and displays the following message;

Warning: mysql_connect() [function.mysql-connect]: User stopshop_claudio already has more than 'max_user_connections' active connections in /home/shop/public_html/classes/MySQL.php on line 34 Link to database cannot be established.

 

I contact Hostgator about this problem and this was their reply;

 

"This is due to the following query, which is taking some time to complete:

| 125318 | stopshop_claudio | localhost | stopshop_prestashop | Query | 36036 | Sending data | SELECT MAX(c.`date_add`) AS last_visit

This query has locked the database, causing other queries that are attempting to update tables to stack until the maximum user connections limit has been reached.

I have restarted your MySQL process at this time, which has allowed your sites to start displaying once again.

 

I highly recommend finding a developer to a way to prevent the MySQL query mentioned above from causing this issue."

 

 

Can someone please help?

 

* My Prestashop version is 1.4.6.2

 

Thanks!

[vekia]

You've got huge traffic in your store? how many quests you've got online?

This error appears always? Or maybe in certain moments?

[claudio]

Hey Vekia, thanks for the quick reply.

 

We do have a healthy amount of traffic I guess, I just had a look before typing this message and we had 92 guests online. This issue happens every so often, twice this week though but sometimes it'll be weeks before it happens again. Hostgator changed the max users from 50 to 200 abut 3 weeks ago and said that this would prevent the problem from happening again but even so it's happened twice now. About 3 days ago Hostgator replaced my MySQL configuration in attempt to resolve or improve this issue which I don't think has helped (see below);

 

I've replaced your MySQL configuration from

 

root@sto [~]# cat /etc/my.cnf

 

[mysqld]

open_files_limit=9656

query_cache_size=64M

max_connections=125

max_user_connections=200

wait_timeout=20

tmp_table_size=106M

max_heap_table_size=106M

thread_cache_size=64M

key_buffer_size=32M

max_allowed_packet=16M

table_cache=768

table_definition_cache=3072

slow_query_log=1

log_output=TABLE

long_query_time=5

innodb_file_per_table=1

innodb_buffer_pool_size=106M

innodb_additional_mem_pool_size=26M

innodb_log_buffer_size=26M

innodb_thread_concurrency=8

 

to

root@sto [~]# cat /etc/my.cnf

[mysqld]

innodb_file_per_table=1 # Ensure that each innodb table is it's own binary data block just in case there's corruption.

query_cache_size=64M

thread_cache_size=4 # can be increased on servers with large numbers of active users

key_buffer_size=32M

max_allowed_packet=16M # don't change unless required for large blobs

table_cache=1024 # max 2048, can be increased if more Opened tables - SHOW STATUS LIKE 'Opened_tables';

table_definition_cache=8192 # increase by the same factor as table_cache

wait_timeout=20 # can be increased if using persistent connections

max_user_connections=50

open_files_limit=16384

 

#delayed_insert_timeout=20 # Turn on if max_connections being reached due to delayed inserts

#delayed_queue_size=300 # Turn on if max_connections being reached due to delayed inserts

 

myisam_sort_buffer_size=2M # can be increased per sessions if needed for alter tables (indexes, repair)

 

#query_cache_limit=2M # leave at default unless there is a good reason

#join_buffer=2M # leave at default unless there is a good reason

#sort_buffer_size=2M # leave at default unless there is a good reason

#read_rnd_buffer_size=256K # leave at default unless there is a good reason

#read_buffer_size=2M # leave at default unless there is a good reason

 

collation_server=utf8_unicode_ci

character_set_server=utf8

 

#general_log=1

slow_query_log=1

#log-output=TABLE # select * from mysql.general_log order by event_time desc limit 10;

long_query_time=20 # select * from mysql.slow_log order by start_time desc limit 10;

 

innodb_flush_method=O_DIRECT

innodb_buffer_pool_size=128M # check mysql -e "SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';" - free vs total

innodb_thread_concurrency=4 # Number of physical + virtual CPU's, be careful of adding more

 

tmp_table_size = 384M

max_heap_table_size = 384M

max_connections=125 # Should be between 100-150, increase *slowly* because it causes MySQL to consume more memory!

 

#innodb_io_capacity=100

#innodb_flush_log_at_trx_commit=2

#innodb_log_buffer_size=64M

 

This should improve performance.

[vekia]

it all depends on hosting configuration. You can increase max_user_connections configuraiton param, but i think that you should also try with something else.

 

Sometimes robots (spamming crawlers) can effectively block the webservice. When robot visit your store - it is treated as a normal user (robot open mysql connection too). Websites that I manage have a similar problem. Sometimes i've got about 30-40 spamming crawlers online (they trying to post forms). They consume resources and slow down my server.

 

You can try to block the unwanted robots (block their ip addresses).

For this I use apache logs. When I see some ip, which notoriously send the forms on my website - I block this ip on my iptables / htaccess

[claudio]

I see, so in this case I'd need to block those unwanted robots but how would I identify their IP addresses in the first place and won't their IP's change every so often? Unless I block an IP range which I'd want to avoid having to do so that I don't end up blocking legit visitors from accessing my site.

[vekia]

if you have got an access to the apache access log you can check this file. I checked it second ago and what i get there:

 

37.59.49.221 - - [19/May/2013:14:52:21 +0200] "POST /contact-form.php HTTP/1.1" 200 25417

37.59.49.221 - - [19/May/2013:14:52:21 +0200] "GET /supplier.php?id_supplier=1 HTTP/1.1" 200 32789

37.59.49.221 - - [19/May/2013:14:52:23 +0200] "GET /search.php?tag=shuffle&id_lang=2 HTTP/1.1" 200 25506

37.59.49.221 - - [19/May/2013:14:52:23 +0200] "POST /contact-form.php HTTP/1.1" 200 25427

37.59.49.221 - - [19/May/2013:14:52:26 +0200] "GET /contact-form.php HTTP/1.1" 200 25762

37.59.49.221 - - [19/May/2013:14:52:25 +0200] "GET / HTTP/1.1" 200 33319

37.59.49.221 - - [19/May/2013:14:52:28 +0200] "POST /contact-form.php HTTP/1.1" 200 26675

37.59.49.221 - - [19/May/2013:14:52:28 +0200] "GET /supplier.php?id_supplier=1&id_lang=2 HTTP/1.1" 200 34771

37.59.49.221 - - [19/May/2013:14:52:30 +0200] "POST /contact-form.php HTTP/1.1" 200 26677

 

i checked google for this ip: "37.59.49.221"

and what i've got:

http://www.stopforumspam.com/ipcheck/37.59.49.221

this means that this is spammer, so i blocked it on my .htaccess