[GitHub Bug #895] Profile/Activity Pages Extremely Slow

Gillingham · May 2011

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

Gillingham · May 2011

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');

Gillingham · May 2011

Can anyone provide insight into debugging or solving this?

Lincoln · May 2011

@Todd Some feedback on Activities performance you might want to look at.

judgej · May 2011

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.

judgej · May 2011

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.

Gillingham · May 2011

GDN_Activity has 2,046,664 records currently, so largeish. In addition there are 2046377 records where CommentActivityID is NULL.

judgej · 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.

Gillingham · 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

Gillingham · 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

judgej · May 2011

I wonder if the Vanilla database query class does unions? I suspect not, so implementing this may be a little tricky.

Gillingham · May 2011

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

judgej · May 2011

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.

Howdy, Stranger!

Categories

In this Discussion

[GitHub Bug #895] Profile/Activity Pages Extremely Slow

Comments