Blog Documentation Try Vanilla Cloud
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

GillinghamGillingham ✭
edited March 2012 in Vanilla 2.0 - 2.8
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
Tagged:

Comments

  • GillinghamGillingham ✭
    Enabled debugging and swapped themes so I could see it, got some interesting information:
    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');
  • GillinghamGillingham ✭
    Can anyone provide insight into debugging or solving this?
  • LincLinc Detroit Admin
    @Todd Some feedback on Activities performance you might want to look at.
  • judgejjudgej ✭
    The numbers in the conditions should not be in quotes for a start. I'm not sure how MySQL deals with it, but I have seem this cause big problems in Oracle databases. To do comparisons the RDBMS needs to convert datatypes so they are the same (i.e. compare a string with a string and a number with a number). If it converts the table column to a string, rather than the obvious (to us) conversion of the static string to a number, then it will fail to use the index on that column.
  • judgejjudgej ✭
    However, in this case I think there are two problems. Firstly this:

    (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.
  • GillinghamGillingham ✭
    edited May 2011
    GDN_Activity has 2,046,664 records currently, so largeish. In addition there are 2046377 records where CommentActivityID is NULL.
  • judgejjudgej ✭
    edited May 2011
    This query does the same as the second OP query, but without the full table scan. It works on my system, but I'm not sure which versions of MySQL support the sub-query with the union in. Try it out on your database and see how quickly it executes. Don't forget to add an index to the RegardingUserID column first. 
    select count(ActivityID) as ActivityCount
     from
    (select a.ActivityID
    from GDN_Activity a
    join GDN_ActivityType t on a.ActivityTypeID = t.ActivityTypeID
    where a.CommentActivityID is null
    and a.ActivityUserID = 28
    union
    select a.ActivityID
    from GDN_Activity a
    join GDN_ActivityType t on a.ActivityTypeID = t.ActivityTypeID
    where a.CommentActivityID is null
    and a.RegardingUserID = 28
    ) AS MyUnion
    Something similar for the first query ought to work just as well.
  • GillinghamGillingham ✭
    edited May 2011
    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
  • GillinghamGillingham ✭
    edited May 2011
    This query does the same as the second OP query, but without the full table scan. It works on my system, but I'm not sure which versions of MySQL support the sub-query with the union in. Try it out on your database and see how quickly it executes. Don't forget to add an index to the RegardingUserID column first. 
    select count(ActivityID) as ActivityCount
     from
    (select a.ActivityID
    from GDN_Activity a
    join GDN_ActivityType t on a.ActivityTypeID = t.ActivityTypeID
    where a.CommentActivityID is null
    and a.ActivityUserID = 28
    union
    select a.ActivityID
    from GDN_Activity a
    join GDN_ActivityType t on a.ActivityTypeID = t.ActivityTypeID
    where a.CommentActivityID is null
    and a.RegardingUserID = 28
    ) AS MyUnion
    Something similar for the first query ought to work just as well.
    That did it, the query is now down to ~.01s
  • judgejjudgej ✭
    I wonder if the Vanilla database query class does unions? I suspect not, so implementing this may be a little tricky.
  • GillinghamGillingham ✭
    Gonna move the discussion over to github but I found that my solution for making the profiles faster only works when viewing your own profile. The additional filtering in the WHERE clause for only publicly viewable things slows the query back down even more.

    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
  • judgejjudgej ✭
    Hopefully some of those notes will be useful. Optimisation is always the difficult part, *after* you have got the code working functionally, and it is useful having you around with your two-million actions activity table ;-)

    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.
Sign In or Register to comment.