HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.
Please upgrade here. These earlier versions are no longer being updated and have security issues.
[GitHub Bug #895] Profile/Activity Pages Extremely Slow
Does anyone else have such an issue, when requesting any users profile page or viewing the activity feed, the pages 10-20s to load. No other pages have the issue. My forum isn't abnormally large, ~7k users with 100-200 online at a given time. Normal discussion and posting does not have this issue so the code/SQL specifically for these pages is doing something very wrong.
I'm not even sure how to debug this while keeping the site up. I don't know what further information would be useful to debug this so any help or queries would be appreciated.
*edit* This is running the latest Vanilla version, 2.0.17.9
bug on github
I'm not even sure how to debug this while keeping the site up. I don't know what further information would be useful to debug this so any help or queries would be appreciated.
*edit* This is running the latest Vanilla version, 2.0.17.9
bug on github
1
Comments
ActivityModel->Get('28', '0', 50) 15.417477s
select a.*, t.FullHeadline as `FullHeadline`, t.ProfileHeadline as `ProfileHeadline`, t.AllowComments as `AllowComments`, t.ShowIcon as `ShowIcon`, t.RouteCode as `RouteCode`, t.Name as `ActivityType`, au.Name as `ActivityName`, au.Gender as `ActivityGender`, au.Photo as `ActivityPhoto`, ru.Name as `RegardingName`, ru.Gender as `RegardingGender` from GDN_Activity a join GDN_ActivityType t on a.ActivityTypeID = t.ActivityTypeID join GDN_User au on a.ActivityUserID = au.UserID left join GDN_User ru on a.RegardingUserID = ru.UserID where a.CommentActivityID is null and (a.ActivityUserID = '28' or a.RegardingUserID = '28') order by a.DateInserted desc limit 50;
ActivityModel->GetCount('28') 6.439407s
select count(a.ActivityID) as `ActivityCount` from GDN_Activity a join GDN_ActivityType t on a.ActivityTypeID = t.ActivityTypeID where a.CommentActivityID is null and (a.ActivityUserID = '28' or a.RegardingUserID = '28');
(a.ActivityUserID = '28' or a.RegardingUserID = '28')
It does not know what index to use here, since no one index covers all matching rows.
Secondly there is no index on RegardingUserID anyway, so a full table scan is done there.
One way to fix this query would be to run it twice (after creating that missing index) - once for a.ActivityUserID = '28' and once for a.RegardingUserID = '28', then union the two results together and count up the rows. This avoids the full table scan.
I'm assuming the GDN_Activity table is massive for the OP.
select a.*, t.FullHeadline as `FullHeadline`, t.ProfileHeadline as `ProfileHeadline`, t.AllowComments as `AllowComments`, t.ShowIcon as `ShowIcon`, t.RouteCode as `RouteCode`, t.Name as `ActivityType`, au.Name as `ActivityName`, au.Gender as `ActivityGender`, au.Photo as `ActivityPhoto`, ru.Name as `RegardingName`, ru.Gender as `RegardingGender` from GDN_Activity a join GDN_ActivityType t on a.ActivityTypeID = t.ActivityTypeID join GDN_User au on a.ActivityUserID = au.UserID left join GDN_User ru on a.RegardingUserID = ru.UserID where a.CommentActivityID is null and (a.ActivityUserID = '28' or a.RegardingUserID = '28') order by a.DateInserted desc limit 50;
The delay here was solved by adding an index to DateInserted, though the query still takes ~.5s which is still rather slow, but much better than the previous 16s
Thanks for your help judgej, I might just shoehorn the raw SQL in if there's no way to do it with vanillas DB API
I tend to put links in between the git and forum posts, so save having to write a lot of background, so it might be worth doing that.