Mysql – Unresponsive database, sleeping processes

connectionsconnectivityMySQL

my mysql database suddenly became unresponsive:

OperationalError: (2003, "Can't connect to MySQL server on '<IP_address>' (110)")

After killing the processes:

+-------+-------+----------+-----------+---------+------+-------+------------------+
| Id    | User  | Host     | db        | Command | Time | State | Info             |
+-------+-------+----------+-----------+---------+------+-------+------------------+
| 11162 | pydev | ip:6984  | django_db | Sleep   | 3279 |       | NULL             |
| 11178 | pydev | ip:6985  | django_db | Sleep   | 1995 |       | NULL             |
| 11182 | pydev | ip:6986  | django_db | Sleep   | 1405 |       | NULL             |
| 11185 | pydev | ip:1168  | NULL      | Query   |    0 | NULL  | show processlist |
+-------+-------+----------+-----------+---------+------+-------+------------------+

It started working again. It there a limit max amount of sleeping processes?

Best Answer

Q: It there a limit max amount of sleeping processes?

A: There's a limit on the number of connections to the MySQL server, that applies to all connections, not just connections in "Sleep" state.

The limit is specified in the max_connections user variable. (The default value is 151.)

There's also a limit on the number of connections from a single user account, either a non-zero value specified on the user account, otherwise derived from the max_user_connections system variable.

SHOW VARIABLES LIKE 'max%connections'

Variable_name         Value
--------------------  --------
max_connections       1000
max_user_connections  200

(On the Windows platform, there may be some additional limitations, beyond those imposed by MySQL itself, e.g. if you "churn" connections, you can temporarily exhaust the available ports, until Windows returns ports that are no longer being used.)

Reference: http://dev.mysql.com/doc/refman/5.5/en/server-system-variables.html#sysvar_max_connections

Related Question