At the weekend we kept getting the ‘too many connections’ error. I look at the open processes and it shows about 250 processes sleeping for long periods.
Id | User | Host | db | Command | Time | State | Info |
+----------+--------+-----------+------+---------+------+-------+-----
| 15160505 | myuser | localhost | mydb | Sleep | 7308 | | |
etc… about 250 of them. Why is this? Is it a problem with mysql (maybe the configuration) or the php layer not closing connections? Things are normally absolutely fine. Could this be some kind of attack on our server? Seems unlikely as the open processes aren’t actually doing anything.
PHP will normally close all database connections as the end of a scripts execution, unless your using permanent connections. Are you opening many database connections in a certain script?
We’re not using persistent connections, no. And each script opens only 1 database connection (I know it’s only once as there’s other stuff in the same include, which will cause an error if it’s called twice).
Maybe it’s a one-off freak occurence, but I’d like to figure out why it was.
Check the PHP error log, Apache error log and system logs around the same time and see if anything interesting turns up. This is a completely random guess, but if PHP was crashing with some kind of fatal error for some reason (like the kernel killing threads because the system is out of memory?), maybe the connections could be left hanging?
You can minimize the impact of a connection not being closed for some reason by setting a low wait_timeout in MySQL.
http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#sysvar_wait_timeout
Thanks for the responses. Sorry I really should have given more information. I think I panicked a bit!
Yes I checked the php logs and they didn’t yield any abnormal errors other than the failure to connect to the database. There were a fairly large number of occurences where the error was associated with a particular ip address. So first I though it was a DoS attack. But the connections are empty, and anyway the requests from this ip are only about 1 per second so that doesn’t sound like DoS. More likely a robot that happened to stop by while the error was occurring (most other visitors would just leave when the see the site isn’t working).
I will check the wait_timeout value, though.
A few days after posting this the hard drives on my server failed in a catastrophic way. The first sign of trouble was that database connections were being left open. The server hosts are denying that the initial connection problems were caused by a problemative drive, blaming it on programming error.
But I’m told that each connection on a linux server is a file. So if the hard drive was having problems, couldn’t this also mean that files couldn’t be removed and connections closed? Maybe I’m being fobbed off here. Anyone able to weigh in on this?
That seems highly unlikely. I’m more inclined to agree with your host that they are unrelated.