Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

MySQL CPU Usage off the charts!

We've been using Vanilla Forums for awhile now without any real issues.

The only thing we've changed recently is our theme -- to Bootstrap -- but that was weeks ago. Now our server is constantly slowing down. Looking in WHM the problem is mysql is consuming huge amounts of CPU. Today 180%, yesterday over 300%.

We've had no meaningful increase in traffic numbers. We use CloudFlare on the domain to thrwart attacks.

Any ideas on what we could do to fix this? It appears to have come out of no where.

«1

Comments

  • whu606whu606 I'm not a SuperHero; I just like wearing tights... MVP

    I'd start by looking in the mysql slow queries log, to see if there is a specific part of the table causing an issue.

  • R_JR_J Ex-Fanboy Munich Admin

    Try SHOW PROCESSLIST; to see what's going on right at the moment when you experience the high CPU usage

  • @R_J said:
    Try SHOW PROCESSLIST; to see what's going on right at the moment when you experience the high CPU usage

    I've just run it a few times, this entry keeps appearing:

    Copying to tmp table select a.*, r.InsertUserID as UserID, r.DateInserted as DateInserted
    from GDN_Action a
    join GDN_

  • @whu606 said:
    I'd start by looking in the mysql slow queries log, to see if there is a specific part of the table causing an issue.

    ...is it normal for the slow queries log to be over 300MB? Downloading now to take a look.

  • Activity table?

    grep is your friend.

  • @x00 said:
    Activity table?

    Errrr what about it?

  • I is usually large, activity is mostly crap you can purge it periodically.

    in crontab add this

    00 3 * * * mysql -u username -p pass forumdb -e "DELETE FROM GDN_Activity WHERE ActivityTypeID NOT IN ( SELECT ActivityTypeID FROM GDN_ActivityType WHERE Name = 'WallComment' ) AND TIMESTAMPDIFF(MONTH,DateInserted,NOW())>2"
    

    grep is your friend.

  • @whu606 Slow log has heaps of different slow queries logged, what should I be looking for?

  • @x00 said:
    I is usually large, activity is mostly crap you can purge it periodically.

    in crontab add this

    00 3 * * * mysql -u username -p pass forumdb -e "DELETE FROM GDN_Activity WHERE ActivityTypeID NOT IN ( SELECT ActivityTypeID FROM GDN_ActivityType WHERE Name = 'WallComment' ) AND TIMESTAMPDIFF(MONTH,DateInserted,NOW())>2"
    

    Wow really?

    GDN_Activity ~642,897 293.4 MiB

  • 350339 rows deleted. (Query took 110.6879 sec)

    !!

  • LincLinc Detroit Admin
    edited August 2015

    Performance limitations I'm aware of related to MySQL:

    • 500K+ Activity records. (solution: prune as suggested - on our list for a better fix)
    • Search on 1M+ post sites (solution: Sphinx)
    • 3M+ users (solution: already mitigated in core for 2.2, but something to keep an eye on wrt plugins)
    • 300+ categories (solution: cache sharding is the only one currently; needs more work)
  • LincLinc Detroit Admin
    edited August 2015

    Also: MySQL can hit a tipping point without comparative increase in traffic. Once queries start backing up, it can stack very quickly and bring down everything. So you might also look at APC/Zend OPcache, memcached and Varnish if you're not currently using those. memcached is probably the lowest fruit :: biggest MySQL performance gain of those.

    Also note that I'm mentioning all this with no insight to what your traffic numbers and hardware are, so these are mostly blind suggestions. There's a hundred variables to tracking down something like this.

  • edited August 2015

    Thanks for the extra info. Rough numbers of our traffic...

    • 25,000 posts/month
    • 1.5m pageviews/month
    • 10,000 users
    • 20 categories

    ...but it sounds like this may be no simple fix. Those fixes you've suggested are 'above my pay grade'. I'll have to investigate them, or find someone who knows more about them.

    It's just very annoying that site worked fine for so long and now has this uncontrollable issue.

  • LincLinc Detroit Admin

    Nice pageview count. I really do think you could install memcached and configure it in Vanilla without it being a major project. I'd start looking at Sphinx only if you see search queries hanging.

  • vrijvlindervrijvlinder Papillon-Sauvage MVP

    The plugin ActivityPurge helps you keep the place cleaner...

  • @Linc said:
    Nice pageview count. I really do think you could install memcached and configure it in Vanilla without it being a major project. I'd start looking at Sphinx only if you see search queries hanging.

    Thanks, I'll look into it. Though it's CPU, and not memory, that mysql is now consuming.

    @vrijvlinder said:
    The plugin ActivityPurge helps you keep the place cleaner...

    Ta, I'll grab that.

  • Thanks, I'll look into it. Though it's CPU, and not memory, that mysql is now consuming.

    Memory caching will reduce the dependence on quering which uses up cpu, as well as storage i/o

    grep is your friend.

  • Oh, and would upgrading MySQL help? Currently running 5.5.42.

  • @x00 said:

    Great. I've just installed it ... is there a particularly good way to monitor how it's 'working'?

    Running WHM on CENTOS.

  • Make sure you have installed php-memcahed not php-memcache enable caching in vanilla:

    $Configuration['Cache']['Enabled'] = TRUE;
    $Configuration['Cache']['Method'] = 'memcached';
    

    that is all.

    You can check it is running with

    watch "echo stats | nc 127.0.0.1 11211"

    Ctrl+C to exit.

    grep is your friend.

Sign In or Register to comment.