Error “too many connections” on CodeIgniter websit

问题:

I've already read all the questions / answers about this subject here on stack overflow but unfortunately none have resolved my problem.

In the last few days the mysql error "too many connections" keeps showing up on the website logs and it hangs the entire website for every client. In fact it hangs all the websites on the server.

So here are my questions / remarks:

• there are about 50 different client databases, besides 2 which are common to all clients
• pconnect is already = FALSE for all connections
• On the file "php.ini" the variable "allowpersistent" is ON. Does this make the mysql connections permanent even if I write pconnect = FALSE? (I can't change the "allowpersistent" variable, would have to ask the hosting company)
• There are 3 files that load databases, one loads the client's DB and the other two load common databases for all clients, they are called on the construct method of every model, but CI supposedly should close the mysql connections after its done with them AND ignore any "load->database" already loaded
• "db->close" apparently does nothing, because this->db->database keeps its value after I close it :P
• Threads_connected are up to 1000 as I write this and the website is down :(
• mysql configuration has max_connections = 1000, can it be further increased? I see no change in free memory, so what can happen?
• Should I change to PDO? I'm using dbdriver "mysqli"
• Should I ask the hosting company to lower the mysql variable 'wait_timeout', so as to rapidly close the DB connection?
• Should I update CodeIgniter? I have version 3.1.4 and it's now at 3.1.9

Many thanks for your help!

回答1:

In our case the solution was lowering the mysql variable "wait_timeout" from 8 hours (the default, wtf!?) to 180 seconds. And it can still be lowered more if needed. This had to be done by the hosting company, as we do not have root access to our server.

All the other solutions I mentioned in the question were not working, like "pconnect = false" and "db->close".

"Threads_connected" are now always under 100 or 200, instead of the almost 1000 from before this fix.

My team wrestled with this problem for two days, and there's lots of people on the Web asking for solutions but without any (working) answers.

Cheers :)

回答2:

I also encounter the same problem. When I check the current number of active connections in MySQL with this query:

show processlist

There are so many connections in sleep mode. After searching around, I found out that:

When a database connection is created, a session is also created on the database server simultaneously, but if that connection and session is not closed properly, then the query goes into sleep mode after the wait time gets over.

To resolve this problem, I do as @Pedro, change the wait_ timeout to 180 by running this command in mysql:

SET GLOBAL wait_timeout = 180;

With this method, you do not need to restart mysql service to take effect.

You can read more here: https://www.znetlive.com/blog/what-is-sleep-query-in-mysql-database/