Blog Documentation Book a Demo
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

Disable Activity Functions

GillinghamGillingham ✭
edited July 2011 in Feedback
I know Vanilla is trying to go modular in design, are there any plans to remove the activity stuff from the core?
The Activity SQL queries are really slow on a very large forum. Someone simply viewing another persons profile page can take up to 30-60s due to not having indexes and querying a huge amount of data in the activity table. The activity table alone is nearly as large as the discussions table.
Being able to simply disable activity functionality, which is not something I really want in a forum product, would hugely boost vanillas performance for large sites.
Tagged:
«1

Comments

  • lucluc ✭✭
    It's coming in the next version, it was added 3 days ago.

    https://github.com/vanillaforums/Garden/commit/747436011860d4f4fc99b3546f751b463547a480
  • GillinghamGillingham ✭
    edited August 2011
    I backported that into my install and that solved some issues, but there's still huge slowdowns. Are there plans to have an option to disable activity functionality entirely?
    Now even people viewing/updating activity via other methods still causes timeouts.

    # Query_time: 15.467383 Lock_time: 0.000221 Rows_sent: 0 Rows_examined: 932072 SET timestamp=1312835570; 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`, au.Email as `ActivityEmail`, 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.ActivityTypeID in ('9', '11') and RegardingUserID = '583' and a.ActivityID > '2477995' and t.Notify = '1' order by a.ActivityID desc limit 5;
    # Query_time: 12.991998 Lock_time: 0.048630 Rows_sent: 1 Rows_examined: 3423942 SET timestamp=1312835677; 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 = '849' or a.RegardingUserID = '849') and t.Public = '1';
  • UnderDogUnderDog MVP
    2 questions:
    How did you find out which SQL queries were executed? Is it some kind of trace tool?
    Second : In the first query there's a left join on the user table, do you have many users? (is the user table used in many columns?)

    There was an error rendering this rich post.

  • GillinghamGillingham ✭
    That's just the last two entries from the mysql slow query log, any query that takes a long time is logged to that file.

    The forum has ~6k Users.
  • lucluc ✭✭
    Is your mysql tuned correctly?
    Have you tried mysqltuner?

    Is mysql running from an SSD?
  • GillinghamGillingham ✭
    I'll double check mysqltuner results after it's been running awhile.

    Mysql is not running from an SSD.

    Even after further optimization I expect vanilla to continue doing some really slow queries, mysqld hasn't been up for 24 hours even and it's already done 5000 joins without indexes. There's also a large number of full table scans and large temporary tables. I notice slowness in simple page loads even on these forums.
  • UnderDogUnderDog MVP
    Ok, my MySQL skills aren't that great that I could give real advice, but I can think of 2 things:
    1) Change the left outer join to an inner join (for testing if it goes quicker)
    2) Try to execute the query in phpMyAdmin and use "explain" to figure out if the query uses any indexes etc
    I wish I could help better, but maybe those steps gives some info

    There was an error rendering this rich post.

  • GillinghamGillingham ✭
    Going through mysqltuner the only values its recommending increasing is join_buffer_size and the table_cache. join_buffer is only recommended because of the huge amount of table joins that aren't using indexes properly.
  • lucluc ✭✭
    If you really have performance issue and could lay hands on an SSD, it will surely help you until something could be done regarding the requests.
    Or until 2.0.18 is out, and you could try memcache with it.

    As for the request, sorry I am unable to help.
  • GillinghamGillingham ✭
    edited September 2011
    This is still a big issue, random pages requesting things from the activity table are leading to an effective ddos of mysql.
    # Query_time: 343.120299 Lock_time: 0.000123 Rows_sent: 0 Rows_examined: 3898003 SET timestamp=1315801220; 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`, au.Email as `ActivityEmail`, 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 = '892' and t.Public = '1' order by a.DateInserted desc limit 50;
    I've disabled the activity tab in profiles but some other page is doing these ridiculous queries without using any sort of indexes.
  • lucluc ✭✭
    @Todd: maybe worth having a look.
  • sahotataransahotataran ✭✭✭
    may be you need to do something like 
    $Configuration['Garden']['Profile']['ShowActivities'] 			= FALSE;
    to disable to show the activity

    There was an error rendering this rich post.

  • GillinghamGillingham ✭
    That only disables the linking of the activity on users profile pages and what was also directly mentioned in lucs post, the 2nd on this page.
  • sahotataransahotataran ✭✭✭
    sorry i didnt check it there :D

    There was an error rendering this rich post.

  • AnonymooseAnonymoose ✭✭
    edited February 2012

    Totally support the above. Please add an option to completely stop activities from accessing the database. Activity feature really should be just an addon. Activity tracking isn't really basic forum stuff.

  • x00x00 MVP
    edited February 2012

    Agreed, it should be able to fully disabled. I requested it in git hub.

    grep is your friend.

  • AnonymooseAnonymoose ✭✭

    Thank you.

  • AnonymooseAnonymoose ✭✭
    edited February 2012

    Gillingham said:
    This is still a big issue, random pages requesting things from the activity table are leading to an effective ddos of mysql.

    Any idea which specific pages those are, so they can be hacked out from the source?

  • 50sQuiff50sQuiff ✭✭

    @Gillingham Did you find out which pages were making requests from the activity table in the end? I'd quite like to hack them out of the source as well.

  • GillinghamGillingham ✭

    Mostly the activity page itself which I just removed from the templates so it wouldn't be linked if I am remembering correctly.

Sign In or Register to comment.