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?
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?
@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?
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).
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;
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;
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 asDateInsertedfrom 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.
My themes: pure | minusbaseline - My plugins: CSSedit | HTMLedit | InfiniteScroll | BirthdayModule | [all] - PM me about customizations
VanillaSkins.com - Plugins, Themes and Graphics for Vanillaforums OS
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?
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?
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).
You can look at some nice statistics with this script: http://blog.elijaa.org/index.php?pages/phpMemcachedAdmin-Installation-Guide
@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 asDateInsertedfrom 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.
My themes: pure | minusbaseline - My plugins: CSSedit | HTMLedit | InfiniteScroll | BirthdayModule | [all] - PM me about customizations
VanillaSkins.com - Plugins, Themes and Graphics for Vanillaforums OS
Thanks @Bleistivt but I think I was already on version 1.0.3. I'll give it a try anyway.
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.