We're running a website spread across three severs. Two of them are load-balanced web servers, and the last is a dedicated mysql server. The mysql server is running RHEL5 64-bit using a 2.6.18-92.1.6.el5 #1 SMP kernel and MySQL 5.0.45. It's a pretty beefy server too, with a Xeon L5420 and 8 gigs of RAM. Our web server's php pages are configured to use mysqli.
Normally we don't really utilize all the resources we're given, we do 20-25 queries a second in the daytime. However, every so often, we'll smack headfirst into the max database connections limit and with that our site croaks. What's more, it's seems to happen at night, where the site's traffic should be at a minimum.
We started out at 100 max connections, we upped it to 300 and yet it still occurs. If it makes a difference, we notice that sometimes there are tons of sleeping MySQL processes, yet nothing that connects to the database utilizes persistent connections. It does not happen every night, we had some issues where it would croak every night and then it was fine for about a week and a half until today.
We don't have any monster queries that would tie up the database for minutes at a time. We've tried glancing over the SLOW_QUERY log. We have a few queries that show up in there, but generally they don't last for more than 1 or 2 seconds and those are quite infrequent.
Does that sound like anything in particular? How would we proceed from here in terms of diagnosing the problem?