Php getting too many connections error from MySQL

Question

Hello everyone,

I am using MySQL and PHP with 2 application servers and 1 database server. With the increase of the number of users (around 1000 by now), I'm getting the following error :

SQLSTATE[08004] [1040] Too many connections

The parameter "max_connections" is set to "1000" in my.cnf and "mysql.max_persistent" is set to -1 in php.ini.

There are at most 1500 apache processes running at a time since the "MaxClients" apache parameter is equal to 750 and we have 2 application servers.

• Should I raise the "max_connections" to 1500 as indicated in here?
• Or should I set "mysql.max_persistent" to 750 (we use PDO with persistent connections for performance reasons since the database server is not the same as the application servers)?
• Or should I try something else?

Thanks in advance!

Answer 1

I think your connections aren't closing fast enough and they stack until the default time has reached. I had same problem and with wait_timeout I solved things out.

You can try to setup in my.cnf

set-variable = max_connections=1000 // max connection
set-variable = max_user_connections=100 // max user connection per hour
wait_timeout = 60 // wait timeout for connection in seconds

as will terminate any existing connections after 60 seconds has passed

Answer 2

I think you should check out the PHP code if you can get rid of the persistent connections.

The problem with persistent connections is that the php instance keeps them open even after script exits until the data has been sent to the client and php instance is freed to next customer.

Another problem with persistent connections is that some PHP code might leave the socket with different settings than in the startup, with different locales or with temporary tables.

If you can rewrite the code that for every connection theres only one or few mysql_connects and the database handle is passed to different parts of the code or kept in GLOBAL the performance impact of losing persistent connections is negligible.

And, of course, there's little harm in doubling the max_connections. It's not very useful with PHP anyway as PHP/Apache child exits quite often anyway and closes the handles. The max_connections is more useful in other environments.