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.
Are you running anything else, or just bbPress? Is this your own server or a shared host?
Install bb-benchmark but that won’t show why the sheer connection number has increased.
bbPress only uses one connection per page load, but the number of queries can vary.
well, i installed bb-benchmark but i’m not sure how that can help. when my connections spike i can’t access the database so….
there’s definitely a pattern here though. the spikes usually occur in the early morning at the same time.
here’s the benchmark info from when the spike was coming down:
08:01:47 up 71 days, 5:08, 0 users, load average: 1.47, 1.43, 1.39
total page time: 5.009 seconds.
time to reach each section:
bb_underscore_plugins_loaded = 0.170
bb_plugins_loaded = 0.178
bb_init = 0.178
bb_index.php_pre_db = 4.966
front-page.php = 4.967
header.php = 4.968
logged-in.php = 4.971
footer.php = 5.008
total query count: 10
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.
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…
so in 4.5 seconds or so, here’s what’s been done:
$forums = get_forums();
$topics = get_latest_topics();
$super_stickies = get_sticky_topics();
it’s actually loaded all the forums and gotten the latest topics
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.
wow, great info man, thanks.
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.
btw; here’s a typical page load when viewing the forums. i’d say this is pretty much on par with how the forum performs most of the time:
10:16:46 up 71 days, 7:45, 0 users, load average: 3.23, 3.20, 3.12
total page time: 0.395 seconds.
time to reach each section:
bb_underscore_plugins_loaded = 0.237
bb_plugins_loaded = 0.253
topic.php = 0.293
header.php = 0.295
logged-in.php = 0.302
post.php = 0.379
post-form.php = 0.386
footer.php = 0.391
total query count: 11
Finding well managed shared hosting that’s affordable is difficult.
Any host can slip up and end up with bad neighbors on a shared box.
Loads as high as you are reporting though should be automatically firing off alerts to the host though, so they either
1. aren’t getting alerts
2. getting them but ignoring them because they know it’s the overloading backup routine each morning
bbPress doesn’t have a page cache yet like WordPress’s wp-cache but in theory doesn’t actually need one unless you have a very active forum (or a bad host).
As far as finding a “better” host, it’s not easy but here’s some ideas:
It looks like other people have had problems with your host as well: http://www.mywot.com/en/scorecard/interland.net
our host is web.com, formerly interland, formerly website pros, formerly hostpro.
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”.
yeah they are either ignorant or lazy
- you either have a bad neighbor on the box or they oversold capacity
it’s not your fault the connections are exhausted, it exhausted for the entire server from all the accounts combined…
they need to fix their mysql settings or move the overly active accounts to a new box
it might be time for you to shop for a better host, it all depends how they handle this
You must be logged in to reply to this topic.