[PHP] MySql Proccesslist filled with "Sleep" Entries leading to "To many Connections" ?

Question

Hi,

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.

My question:

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

-- Update:

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

Answer 1

Maybe this article can help you...

Answer 2

Basically, you get connections in the Sleep state when :

• a PHP script connects to MySQL
• some queries are executed
• then, the PHP script does some stuff that takes time
  • without disconnecting from the DB
• and, finally, the PHP script ends
  • which means it disconnects from the MySQL server

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 :

• There are more and more requests coming to Apache
  • which means many pages to generate
• Each PHP script, in order to generate a page, connects to the DB and does some queries
• These queries take more and more time, as the load on the DB server increases
• Which means more processes keep stacking up

A solution that can help is to reduce the time your queries take -- optimizing the longest ones.