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

Solution 1:

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.

Solution 2:

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.

Solution 3:

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

Solution 4:

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.