4

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?

1

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

3
  • Thanks! My settings are set to defaults (151, 0), so 3 sleeping processes shouldn't block the db. Do you have any guess what could be the reason of this problem? – george Sep 28 '14 at 8:54
  • @jurer: I suspect it may not be an issue with the MySQL server, it may be a networking issue. It's possible the MySQL server was temporarily stopped (restarted), or it could just a temporary issue on the switch or router. (On our MySQL servers, we add skip-name-resolve option to the my.cnf. That disables DNS hostname lookups, and removes the dependency on the DNS sevice... if DNS hostname lookups are left enabled, then an "outage" on the DNS service can prevent connections to MySQL. On our servers, all of the database users are identified by IP address rather than hostname.) – spencer7593 Sep 28 '14 at 19:30
  • Thanks for the response. I think it's not networking issue - it happened to me today and I was able to connect to the mysql server from remote shell, but it wasn't working for my website (django based). There were 2 sleeping processes, once I killed one it started working instantly. – george Oct 6 '14 at 15:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.