Skip to:
Content
Pages
Categories
Search
Top
Bottom

diagnosing sql connections spike


  • josh_
    Member

    @josh_

    hi all,

    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.

Viewing 12 replies - 1 through 12 (of 12 total)

  • bobbyh
    Member

    @bobbyh

    Are you running anything else, or just bbPress? Is this your own server or a shared host?


    josh_
    Member

    @josh_

    we only have bbpress running on a shared host.


    _ck_
    Participant

    @_ck_

    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


    _ck_
    Participant

    @_ck_

    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…

    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


    _ck_
    Participant

    @_ck_

    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:

    http://ckon.wordpress.com/2008/12/18/some-tech-tips-to-find-a-better-wordpress-or-bbpress-host/

    Who’s your host?


    Ben L.
    Member

    @nightgunner5

    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”.


    _ck_
    Participant

    @_ck_

    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

Viewing 12 replies - 1 through 12 (of 12 total)

You must be logged in to reply to this topic.