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

score 92 · Accepted Answer · answered Mar 09 '10 at 11:50

92

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.

answered Mar 09 '10 at 11:50

Pascal MARTIN

395,085
80
655
663

1

Thanks a lot for the comment ! The Problem however is that even with all webservers combined there are nowhere near 400 requests running. The "Sleep" state continues way after the request that created the connection is finished. E.g. at midnight where there is no more than 1 pageview per second the problem also emerges. – edorian Mar 09 '10 at 12:04
4

That is odd... oh, maybe an idea : are you using permanent connections ? (with the `mysql_pconnect` function, for instance) – Pascal MARTIN Mar 09 '10 at 12:10
No, we verbosely checked for that and killed all code that even possibled used "*pconnect*" or similar stuff. – edorian Mar 09 '10 at 12:35
1

Oh :-( too bad :-( ;; out of curiosity, do you have "ghost" httpd process that are still there, doing nothing, when this happens ? – Pascal MARTIN Mar 09 '10 at 12:40
Our (main) apache is running with "MinServers 200" and has 260 httpd processes. This machine currently keeps 164 sleeping connections to the database. – edorian Mar 09 '10 at 13:14
2

I woul think you need to look again for persistent connection, neither Zend nor PDO use a syntax with '*pconnect*' in it for a persistent connection. – symcbean Mar 09 '10 at 14:36
1

Just as a heads up: PDO based drivers can also pconnect, but they don't call it this. It's an option set on the database handle with the setOption method. – aredridel Jun 12 '12 at 14:50
close PHP connection with mysqli_close(iDB:$conn); – Jan Bludau May 25 '19 at 14:35
it's worth mentioning here what I mentioned here - https://stackoverflow.com/a/14776530/528020. Sometimes webroot on shared filesystem can cause sleep connections to pile up. We had that happen when Apache webroot was on shared filesystem via GlusterFS v7 and that caused sleep connections to pop up. Updating to glusterfs v8 helped in our particular case. What happened internally is a mystery, because it seemed that glusterfs kept files open and __destruct in php was never called. – seven Jul 08 '21 at 11:04

score 9 · Answer 2 · answered Oct 07 '19 at 03:24

9

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.

answered Oct 07 '19 at 03:24

Marcelo Agimóvel

1,668
2
20
25

The query above will increase the wait_timeout only for the current session. Changing the settings on my.cnf file is needed, but would require the MySQL service to be restarted. To check/change the GLOBAL value (server-wide), these should be used instead: SHOW GLOBAL VARIABLES LIKE "wait_timeout"; SET @@GLOBAL.wait_timeout=600; I also see one setting is 600 and the others are 300 (probably a typo). Last, "interactive_timeout" affects only interactive connections to the database. I would not touch it since it usually affects the database administrator only. – Luis Talora Jun 22 '23 at 14:13
A more detailed version of this answer is at [https://dba.stackexchange.com/a/1559/](https://dba.stackexchange.com/a/1559/) – joeljpa Jul 11 '23 at 10:22

score 7 · Answer 3 · answered Mar 21 '19 at 13:48

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.

The suggestion above is considering only the current session. For this to be effective for all future connections, these commands should be used instead: SHOW GLOBAL VARIABLES LIKE "wait_timeout"; SET @@GLOBAL.wait_timeout=600; — Luis Talora, Apr 14 '23 at 14:18

score 6 · Answer 4 · answered Sep 19 '18 at 10:10

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

score 0 · Answer 5 · answered May 21 '15 at 06:08

0

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..

<?php
$result = mysql_query("SHOW processlist");
while ($myrow = mysql_fetch_assoc($result)) {
if ($myrow['Command'] == "Sleep") {
mysql_query("KILL {$myrow['Id']}");}
}
?>

answered May 21 '15 at 06:08

Mahboob I

79
9

9

This is a bad idea - how do you know that the php script has finished using the mysql connection? it may of done a query, gone into sleep to do some php and then needs another query later on. – CᴴᵁᴮᴮʸNᴵᴺᴶᴬ Sep 09 '15 at 09:23
Have you tested it? Coz if it isn't plugins, then wordpress does not need any sleeping processes... Test it and let me know. – Mahboob I Sep 18 '15 at 12:51
2

Shell version : `mysql -e "show full processlist;" -ss | grep Sleep | awk '{print "KILL "$1";"}' | mysql` – Fedir RYKHTIK Jun 01 '16 at 17:44
1

Have you found out a way to track the source of the sleep commands? Your code is great BTW, thanks – andreszs Mar 09 '17 at 18:35
This is a bad idea. A single connection may execute multiple queries in it's lifetime during the PHP scripts execution. During any point between those queries, the connection may go into sleep state, even if just for a millisecond. A sleep state just means it's waiting on the script to continue giving it something to do. You very well may, or may not, kill properly, existing running connections. – Jarrod Christman Dec 04 '18 at 20:31
3

If you're going to do this, instead of finding the root cause (a much better route), I'd recommend taking into account how long the connection has been sleeping, that way you're a lot less likely to hit connections that are legitimately running. – Jarrod Christman Dec 04 '18 at 20:32

score 0 · Answer 6 · answered Jun 23 '17 at 15:02

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.

score 0 · Answer 7 · answered May 23 '22 at 22:06

Look into persistent MySQL connections: I connected using mysqli('p:$HOSTNAME') and had Laravel database.php settings like:

'options'   => [
    PDO::ATTR_PERSISTENT => true,
],

For some reason, for some time, I believed it was smart to keep connections persistent as I thought my applications would share them. They didn't. They just opened connections and left them unused until they timed out.

After I removed my mad dream of persistency I went from 120-150+ connections from several hosts to only a handful, most of the time actually just one (being the one that runs SHOW PROCESSLIST).

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

7 Answers7

Linked