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

Gillingham

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

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

Can anyone provide insight into debugging or solving this?

Linc

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

judgej

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

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

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

judgej

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

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

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

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

Gillingham

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

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.