CODEWITHSUNDEEP
phpmysqlmysql-error-1040
uzioriluzan
uzioriluzan

Reputation: 41

Php getting too many connections error from MySQL

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.

Thanks in advance!

Upvotes: 4

Views: 15246

Answers (2)

Antti Rytsölä
Antti Rytsölä

Reputation: 1553

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.

Upvotes: 1

Mihai Iorga
Mihai Iorga

Reputation: 39724

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

Upvotes: 3

Related Questions