diagnosing sql connections spike

i’ve been running a small private bbpress forum for about 6 months now. overall things have been working quite smoothly. we have one persistent problem though; our sql connections occasionally spike and max out giving us the “oh dear” page until things settle down. until a few weeks ago i was blaming our host, but now that i’ve been monitoring the server with ‘mysql administrator’ i can see that it’s the connections that are the problem. usually we’ll be running just fine at about 10-20% connection capacity and then wham, spike to 90-100% and down we go for a few minutes (occasionally more).

how can i figure out what’s causing these spikes? any help would be greatly appreciated.

Early in the morning on a shared box may mean your provider doesn’t know what they are doing and is not running the daily backup in “nice” mode, therefore overloading the server.

Either that or you might have a misbehaving neighbor.

bbPress doesn’t naturally cause that kind of problem, unless you are running an older alpha which uses more queries – and some of my plugins like hidden forums had a bug in a previous version that caused extra queries – you only used 10 queries so I think you are running 0.9

If you know what you are doing, even on a shared host you can run a simple PHP program with cron checking the load every minute – the moment it starts to spike, say over 3.0, you can have it email you “ps aux” which will show you which neighbor is misbehaving, even on a shared host.

See this:

bb_init = 0.178 <<<

bbpress’s core is completely loaded at this point,

plugins, everything the amount of time is okay and acceptable – 50 to 100 files have been loaded

bb_index.php_pre_db = 4.966 <<<—- this is kind of a lie, it’s not pre-db, there have been some queries…

It’s using about 8-9 queries so 4.5 seconds is absolutely terrible, almost impossible if the server is working correctly.

If your page time is normally under 1 second for that part, here’s what I think is happening.

I think your host is running a backup at that hour OR one of your neighbors on the same box is doing a remote backup to/from the server, including mysql.

That ties up the cpu and ties up mysql because the disk is thrashing and mysql is not caching at all.

To prove my theory, see if it happen every day at the exact hour.

Even more suspect would be if it happens on the hour or half hour.

By the way, why do you assume it’s “your” mysql connections that have been exhausted – it’s actually the entire server’s pool on a shared host. I suspect you have a misbehaving neighbor who is accidentally making you think it’s you that’s the problem.

i’m no expert when it comes to this stuff. as a matter of fact, i’m a total noob and this forum is my first “adventure” into database land. so i pretty much assume that every problem we have is my fault. i do however suspect that it is our host that SUCKS and i’ve just been trying to build that case (i just want to know it’s their problem before i move the whole forum only to find out otherwise). i’ve been on the phone with them a bunch of times, but they really are useless. they insist that there’s no problem and they won’t escalate the issue to the “server team” unless i can provide them with various logs in lieu of them being able to reproduce the problem.

generally i am under 1 second on the bb_index.php_pre_db. the forum is a bit sluggish when it runs on the high side of 1 second though.

thanks for all the effort explaining this stuff, i really appreciate it.

i suspect something like a backup routine for the morning outage, but the spikes/outages do appear to occur randomly throughout the day as well. they’re usually quick, but frequent enough to be a regular occurrence. one time we went down for like an hour. i called them and the person on the phone actually saw the problem because they couldn’t log into phpmyadmin either. “oh yes i see this now, no connections available”. they kept me on the phone long enough that the problem went away and then they were like “well it looks like it fixed itself so there’s nothing i can do now”.