MySql Proccesslist filled with Sleep Entries leading to Too many Connections

Question

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   My question    Why are there so many connections in sleep state and how can I prevent that   -- 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

User · Answer

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    To show the wait timeout value    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    To change the value of  wait timeout  variable    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

User · Answer

So I was running 300 PHP processes simulatenously and was getting a rate of between 60 - 90 per second  my process involves 3x queries   I upped it to 400 and this fell to about 40-50 per second  I dropped it to 200 and am back to between 60 and 90   So my advice to anyone with this problem is experiment with running less than more and see if it improves  There will be less memory and CPU being used so the processes that do run will have greater ability and the speed may improve

User · Answer

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

User · Answer

Increasing number of max-connections will not solve the problem   We were experiencing the same situation on our servers  This is what happens  User open a page view  that connect to the database  query the database  still query queries  were not finished and user leave the page or move to some other page  So the connection that was open  will remains open  and keep increasing number of connections  if there are more users connecting with the db and doing something similar   You can set interactive timeout MySQL  bydefault it is 28800  8hours  to 1 hour  SET interactive timeout 3600

User · Answer

Alright so after trying every solution out there to solve this exact issues on a wordpress blog  I might have done something either really stupid or genius    With no idea why there s an increase in Mysql connections  I used the php script below in my header to kill all sleeping processes     So every visitor to my site helps in killing the sleeping processes      lt  php  result   mysql query  SHOW processlist    while   myrow   mysql fetch assoc  result     if   myrow  Command       Sleep     mysql query  KILL   myrow  Id            gt

User · Answer

The above solutions like run a query   SET session wait timeout 600    Will only work until mysql is restarted  For a persistant solution  edit mysql conf and add after  mysqld    wait timeout 300 interactive timeout   300   Where 300 is the number of seconds you want

[php] MySql Proccesslist filled with "Sleep" Entries leading to "Too many Connections"?

Examples related to php

Examples related to mysql