Vroom Posted July 9, 2015 Posted July 9, 2015 After upgrading to 4.0 having some server load problems. My server is running fine most of the time. Then this query came up today and the server was close to death: | 1063478 | localhost | DATABASE | Execute | 7344 | Sending data | /*IPS\Patterns\_ActiveRecordIterator::count:246*/ SELECT SQL_CALC_FOUND_ROWS forums_posts.*, forums_topics.* FROM `forums_posts` LEFT JOIN `forums_topics` ON forums_posts.topic_id=forums_topics.tid LEFT JOIN `core_permission_index` ON core_permission_index.app='forums' AND core_permission_index.perm_type='forum' AND core_permission_index.perm_type_id=forums_topics.forum_id LEFT JOIN `forums_forums` ON forums_topics.forum_id=forums_forums.id WHERE ( NULLIF(forums_topics.moved_to, '') IS NULL ) AND ( forums_forums.password IS NULL OR ( ( FIND_IN_SET(2,forums_forums.password_override) ) ) ) AND ( forums_forums.can_view_others=1 OR forums_topics.starter_id IS NULL ) AND forums_forums.min_posts_view<=0 AND queued=0 AND forums_topics.approved=1 AND (( ( FIND_IN_SET(2,perm_2) ) ) OR perm_2='*' ) ORDER BY post_date desc LIMIT 0,25 It ran for 6 hours, and spawned 5 or 6 identical queries, which further killed the server. Finally I had to kill them off because server load was at 10 and I/O was at 100% util.Then everything back to normal and everything working fine again. After that this query didn't come back and server load is back to 1.Anyone can tell what this query is related to? Is it part of the upgrade background processes? Will it go away or keep coming back forever to haunt me?Forum has 15 million posts and is on a dedicated server.
AutoItScript Posted July 9, 2015 Posted July 9, 2015 It's looks like Activity Stream->PostsI've got it disabled on my forum for that reason. Definitely disable it for guests at least (System->Applications->System->Activity Stream).If all your tables are Innodb it should complete and other users should be OK (it will be slow though, mine is 30 seconds on 1.5million posts).If all your tables are MyISAM it will completely lock some tables and other users will hang.
Vroom Posted July 9, 2015 Author Posted July 9, 2015 Thanks for the reply. I will disable it and see.I plan to upgrade the server to SSD drives and 32GB ram. But I never needed to do that in the past with any other forum I ran even with much much more active forums.
ctrlbrk Posted July 9, 2015 Posted July 9, 2015 Thanks for the reply. I will disable it and see.I plan to upgrade the server to SSD drives and 32GB ram. But I never needed to do that in the past with any other forum I ran even with much much more active forums.This will help, but are your tables InnoDB or MyISAM?
Vroom Posted July 9, 2015 Author Posted July 9, 2015 The largest tables are InnoDB. I havent had time to convert all the small tables.
genrobgen Posted July 9, 2015 Posted July 9, 2015 I've had this exact same problem for two months ever since upgrading.I submitted a support ticket, but it never went anywhere.I've since upgraded MySQL 5.5 to 5.6, changed my tables to InnoDB, switched on memcache, tweaked the my.cnf settings and the IPS settings.All to no avail.In the end I wrote a script on a cron to automatically kill these queries after 30 seconds:https://github.com/kill-slow-queries-ips-4-0-4.php
Vroom Posted July 10, 2015 Author Posted July 10, 2015 Thanks. I will have a look at your script and may have to use it.But it raises the question, if this is happening to multiple people then isn't something wrong with the software? I have ran more than a dozen different forum softwares over the last 15 years. I never had this problem where a single query would kill a server. I know I can add more ram, change to SSD, etc., but should we really have to do all that for something so simple?
AutoItScript Posted July 10, 2015 Posted July 10, 2015 I wouldn't use the script personally - the root problem needs fixing and not hiding. That sql query you posted is definitely Activity Stream and that has been reported and bugged (but ignored to be honest, but Activity Stream is being rewritten so...). The Topic Feed widget also has a similar query that causes issues for many (also bugged and marked as fixed for 4.0.10 but we'll see). if you see a long query that has a "limit 5" at the end of it it's a Topics Feed widget, if you see "limit 0,25" it's likely Activity Stream/New Content.People with small boards won't see it at all. You need to go all Innodb to "mask" the issue at least so that other users don't get locked when it runs. Even though you've converted a couple of tables to Innodb you might be getting locks on some of the tables in that query which are:forums_postsforums_topicscore_permission_indexforums_forumsEven though you've converted the forums_posts table (I'm guessing). If the forums_topics table is locked then NOONE can view your board at the same time.If you want to verify which it is then I did this:Disable Topic Feed widgets, set Activity Stream so that only Administrators can accessOpen 3 browser tabs, two on the forum, one in phpmyadminIn phpmyadmin goto Server -> Status -> Processes. Enable 2 second autorefreshOn forum tab 1 do Activity Stream -> Posts - this should take a whileOn forum tab 2 try and browse to a topicIn phpmyadmin you should see the long query running - check it's the same one you posted above and monitor itIf it's what I think it is your forum tab 2 session should hang and you will see a sessions queueing up "waiting for table lock"
The.Reaper Posted July 10, 2015 Posted July 10, 2015 Also suffering from slow loads, let hope it's fixed in the next update!http://ancient-fury.com/Forum/
genrobgen Posted July 10, 2015 Posted July 10, 2015 That sql query you posted is definitely Activity StreamMore specifically, it's the Activity Stream when filtered to show topics or posts only, particularly posts.As a temporary work-around on my forum I've hidden that filter panel.The activity stream still shows, but users can't find any link to filter. The default activity at URL "/activity/" loads reasonably fast.On this here forum the "Activity posts" is also quite slow, takes about 5 seconds.https://community.invisionpower.com/activity/?type=forums_topic_post&change_section=1But as others have mentioned, it sounds like this is being worked on and will be fixed or changed in a forthcoming version.
Vroom Posted July 10, 2015 Author Posted July 10, 2015 I disabled activity stream as recommended above and haven't had any problems since.
Lab Rats Rule Posted August 11, 2015 Posted August 11, 2015 Well, I have two nearly 1000 member forums, one with 3.34 and the other with 4.0, and my speed is wonderful. . In fact flash lightning... BUT, I changed my mem limits for PHP to -1. I have 4 GB memory.So there you have it, you needs lots of dedicated memory. I like the activity stream, and I want another on my forum page in a side block if possible,.
DD&DD Posted November 27, 2015 Posted November 27, 2015 I've had this exact same problem months ever since upgrading.AutoItScript thank you very much to explain.
Ohio Riders Posted May 15, 2016 Posted May 15, 2016 On November 26, 2015 at 5:38 PM, mrbanjo said: Was this ever addressed by ips? Nope. Still slow.
Recommended Posts
Archived
This topic is now archived and is closed to further replies.