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!

2»

Comments

  • Thanks, I've added those lines to the Vanilla Config file.

    I'm now also watching a number in the terminal. It's just randomly going 2, 6, 20, 50, 8 ... so I guess it's working?! =)

  • Also the only constant I'm seeing in SHOW PROCESSLIST; still is:

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

    Not sure if it's normal or abnormal to have that there all the time...

  • GDN_Action is used by Yaga. The number of reaction queries has been reduced in the current master on github: https://github.com/hgtonight/Application-Yaga

    Afaik @hgtonight is going to release it when 2.2 goes final.

  • Thanks ... I'll turn it off for awhile and see what it does to the load.

  • Oh one more ... I know on some forum software that particularly large threads can impact load. We have one thread with about 30k comments in it, it's by far the largest thread on the forum. Is it good policy to shut threads after a certain number of posts?

  • LincLinc Detroit Admin
    edited August 2015

    I haven't seen that identified as a performance bottleneck. With memcached enabled, discussion length shouldn't be a factor because it is cached by page. Maybe if it was 300K comments, then maybe? :confounded:

  • @Linc said:
    I haven't seen that identified as a performance bottleneck. With memcached enabled, discussion length shouldn't be a factor because it is cached by page. Maybe if it was 300K comments, then maybe? :confounded:

    Thanks ... would updating MySQL help? Currently running 5.5.42.

    There's been a noticeable reduction of mysql CPU usage since disabling YAGA (which sucks because all we wanted was a like button and it does a great job).

  • R_JR_J Ex-Fanboy Munich Admin

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

    You can look at some nice statistics with this script: http://blog.elijaa.org/index.php?pages/phpMemcachedAdmin-Installation-Guide

  • LincLinc Detroit Admin

    @Simeon_Griggs I bet @hgtonight would appreciate the feedback if you can use the debugger addon to figure out where the database is getting hammered.

  • Hope this helps @hgtonight, thanks @Linc

    So we left YAGA off for 24 hours and mysql's CPU usage dropped, site speed went back up.

    Turned YAGA back on and quickly mysql shot back up to over 100% CPU.

    I've turned on Debugger. Here's an example page...

    78 queries in 7.4525077343s
    Page completed in 8.8246s

    ...with a lot of likes, there's heaps of these calls:

    ===

    ReactionModel->GetList(594979, 'comment')0.114600s

    select a.*, (select count(r.ReactionID) from GDN_Reaction as r where r.ParentID = '594979' and r.ParentType = 'comment' and r.ActionID = a.ActionID) as Count from GDN_Action AS a order by a.Sort;

    ReactionModel->GetRecord(594980, 'comment')0.107786s

    select a.*, r.InsertUserID as UserID, r.DateInserted as DateInserted
    from GDN_Action a
    join GDN_Reaction r on a.ActionID = r.ActionID
    where r.ParentID = '594980'
    and r.ParentType = 'comment'
    order by r.DateInserted asc;

    ===

  • Oh, and disabling YAGA did not immediately bring CPU usage back down, it winds back down slowly.

  • https://github.com/hgtonight/Application-Yaga/archive/6f7bbb00bc6868ef03c6479372359a5d9f93525d.zip

    You could try this revision which is still compatible with 2.1 but executes less queries.

  • Thanks @Bleistivt but I think I was already on version 1.0.3. I'll give it a try anyway.

  • LincLinc Detroit Admin
    edited August 2015

    @Simeon_Griggs said:
    Oh, and disabling YAGA did not immediately bring CPU usage back down, it winds back down slowly.

    I think that's to be expected. The queries are probably stacked up and take a while to clear the queue. A MySQL process viewer is helpful here.

  • Okay so with that version of YAGA above our mysql CPU usage is low again. Hooray. Thanks all.

    @Linc got a process viewer you recommend?

  • (Sorry to keep bumping this but just incase anyone else is reading this in future)

    Another way to reduce load I only just thought of is to turn YAGA off for Guests. It's on by default for all roles.

Sign In or Register to comment.