Dealing with MySQL Too Many Connections error

John Maver
Written by John Maver  ()

php

Doorbell on Bebo is growing steadily, despite some randomness in the Bebo platform uptime. One side effect of its popularity was a new error – Too Many Connections. Here is a quick overview of the cause and a solution.

Too Many Connections can be caused by either a lot of simultaneous connections or by old connections not being released soon enough. There are some simple changes you can make to your PHP code and your MySQL settings to prevent both.

There are two built in ways to connect to MySQL from PHP – permanent or interactive. You get a permanent connection using mysql_pconnect(). This creates a permanent connection to the database (permanent is defined as 8 hours by the MySQL wait_timeout system variable). It will only create a new connection if it cannot find an existing permanent connection to reuse. You need to be careful with mysql_pconnect() to make sure that you don’t run out of connections, since these stay open so long and you cannot close them with mysql_close().

The other way is to use mysql_connect(). This creates a new temporary connection, if the new_link parameter is set to true, or reuses an existing one if it is not. This can be better than mysql_pconnect() because these connections can be short lived, and can be closed when needed with mysql_close(). An important parameter is the CLIENT_INTERACTIVE flag. If this is passed, it will use the MySQL interactive_timeout value instead of wait_timeout. Since wait_timeout defaults to 8 hours, this seems like a great idea. However, surprisingly, interactive_timeout also defaults to 8 hours. You should change this value to something smaller that fits your system.

Using mysql_connect() with new_link set to false, passing the CLIENT_INTERACTIVE flag, and adjusting interactive_timeout will help stop problems with old connections jamming up the works.

To deal with many simultaneous connections, adjust the MySQL max_connections variable. By default, it is set to 100. Increase this value to something that will support what you feel is realistic for your system.

A final thing to do after updating your code and MySQL is to flush the bad connections database with “myqladmin flush-hosts”. This will allow your web server to connect back to MySQL after all those Too Many Connection errors.

Topics: Programming

Follow us:

Enjoy this article? Don't forget to share.

Interested in Working With Us?

Click Here to Find Out More About How We Can Help You