Dealing with MySQL Too Many Connections error

by on February 18, 2008

in Programming

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.

Comments

  1. Zan Sule says:

    Very useful post I found it useful in resolving a similar problem at work. Thanks John and Cappy

  2. Nirmal Natarajan says:

    Nice little post. This helped me understand the basics of wait_timeout and interactive_timeout.

  3. Scott says:

    I think newer mysql has a default of 151 connections.

  4. Scott says:

    I think newer mysql has a default of 151 connections.

  5. Neeraj says:

    Thanks for your time, it’s nice article.

    We are using simple mysql_connect(DBname, DBuser, DBpass) instead $con=mysql_connect(DBname, DBuser, DBpass) in our config.php. We are using command for close mysql connection mysql_close(); instead mysql_close($con);. Then why we facing “Too many connection on our server”. Kindly can you tell me.

    Thanks
    neeraj@librawebsolutions.com

Previous post:

Next post: