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 a 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.
Thank you for your time, if theres anything unclear or missing please let me know
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
innodb_additional_mem_pool_size = 64M
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 2
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.