I'd like to ask your help on a longstanding issue with php/mysql connections.
Every time I execute a "SHOW PROCESSLIST" command it shows me about 400 idle (Status: Sleep) connections to the database Server emerging from our 5 Webservers.
That never was much of a problem (and I didn't find a quick solution) until recently traffic numbers increased and since then MySQL reports the "to many connections" Problems repeatedly, even so 350+ of those connections are in "sleep" state. Also a server can't get a MySQL connection even if there are sleeping connection to that same server.
All those connections vanish when an apache server is restated.
The PHP Code used to create the Database connections uses the normal "mysql" Module, the "mysqli" Module, PEAR::DB and Zend Framework Db Adapter. (Different projects). NONE of the projects uses persistent connections.
Raising the connection-limit is possible but doesn't seem like a good solution since it's 450 now and there are only 20-100 "real" connections at a time anyways.
Why are there so many connections in sleep state and how can I prevent that?
The Number of Apache requests running at a time never exceeds 50 concurrent requests, so i guess there is a problem with closing the connection or apache keeps the port open without a phpscript attached or something (?)
my.cnf in case it's helpful:
innodb_buffer_pool_size = 1024M max_allowed_packet = 5M net_buffer_length = 8K read_buffer_size = 2M read_rnd_buffer_size = 8M query_cache_size = 512M myisam_sort_buffer_size = 128M max_connections = 450 thread_cache = 50 key_buffer_size = 1280M join_buffer_size = 16M table_cache = 2048 sort_buffer_size = 64M tmp_table_size = 512M max_heap_table_size = 512M thread_concurrency = 8 log-slow-queries = /daten/mysql-log/slow-log long_query_time = 1 log_queries_not_using_indexes innodb_additional_mem_pool_size = 64M innodb_log_file_size = 64M innodb_log_buffer_size = 8M innodb_flush_log_at_trx_commit = 2 innodb_file_per_table
Basically, you get connections in the Sleep state when :
So, you generally end up with many processes in a Sleep state when you have a lot of PHP processes that stay connected, without actually doing anything on the database-side.
A basic idea, so : make sure you don't have PHP processes that run for too long -- or force them to disconnect as soon as they don't need to access the database anymore.
Another thing, that I often see when there is some load on the server :
A solution that can help is to reduce the time your queries take -- optimizing the longest ones.
Before increasing the max_connections variable, you have to check how many non-interactive connection you have by running show processlist command.
If you have many sleep connection, you have to decrease the value of the "wait_timeout" variable to close non-interactive connection after waiting some times.
SHOW SESSION VARIABLES LIKE 'wait_timeout';
| Variable_name | Value |
| wait_timeout | 28800 |
the value is in second, it means that non-interactive connection still up to 8 hours.
SET session wait_timeout=600; Query OK, 0 rows affected (0.00 sec)
After 10 minutes if the sleep connection still sleeping the mysql or MariaDB drop that connection.