Vroom Posted August 13, 2015 Share Posted August 13, 2015 Last two days my server is crashing. What happens is there are some rarely ran queries that completely freeze my server. This particular forum has 15 million posts. Usually there is no problem at all, but when this query comes up it runs for a very long time, and the IOSTAT Util% goes to 100% causing everything else to start to queue up being unable to access the harddisk. Even visiting other webpages on the server becomes impossible. I know the best solution would be to upgrade to SSD drives, but that isn't immediately possible.Can anyone identify what this query is doing and is it something I can disable? I have already disabled activity stream. | 539 | localhost | Execute | 430 | Creating sort index | /*IPS\Patterns\_ActiveRecordIterator::count:251*/ SELECT SQL_CALC_FOUND_ROWS forums_topics.*, author.*, last_commenter.*, core_tags_cache.tag_cache_text FROM `forums_topics` LEFT JOIN `core_tags_cache` ON tag_cache_key=MD5(CONCAT('forums;forums;',forums_topics.tid)) LEFT JOIN `core_members` AS `author` ON author.member_id = forums_topics.starter_id LEFT JOIN `core_members` AS `last_commenter` ON last_commenter.member_id = forums_topics.last_poster_id WHERE forums_topics.forum_id=24 AND forums_topics.approved=1 ORDER BY forums_topics.pinned DESC, last_post desc LIMIT 100,25 | Link to comment Share on other sites More sharing options...
AutoItScript Posted August 13, 2015 Share Posted August 13, 2015 Looks like one of the Activity Stream queries. Turn it off altogether for a while to verify. (System->Applications->System->Activity Stream) Link to comment Share on other sites More sharing options...
Vroom Posted August 13, 2015 Author Share Posted August 13, 2015 Activity Stream is already disabled, but still this query came. It doesnt come often enough for me to track. It won't come all day, then when I sleep it will come and crash the server. Link to comment Share on other sites More sharing options...
Adriano Faria Posted August 13, 2015 Share Posted August 13, 2015 I would blame tags: LEFT JOIN `core_tags_cache` ON tag_cache_key=MD5(CONCAT('forums;forums;',forums_topics.tid) Link to comment Share on other sites More sharing options...
AutoItScript Posted August 13, 2015 Share Posted August 13, 2015 If you run the query manually (from mysql or phpmyadmin) sometimes the results you get gives you an idea of what was happening. The limit 25 says to me that it's something search/activity related because that's when you get 25 results per page.This bit "WHERE forums_topics.forum_id=24 AND forums_topics.approved=1 ORDER BY forums_topics.pinned" makes me think it's a post feed widget type query, but that wouldn't be limit 25, it would be 5 or something. Order by "pinned" makes me think it's just a forum view for forum id 24, but that's really weird it would take so long. Is it always forum id 24? Link to comment Share on other sites More sharing options...
RevengeFNF Posted August 13, 2015 Share Posted August 13, 2015 Run that query with "explain" to check what it does.I suspect that probably will create a very large temp table that hangs your disk. Link to comment Share on other sites More sharing options...
Adriano Faria Posted August 13, 2015 Share Posted August 13, 2015 Probably forum view. Link to comment Share on other sites More sharing options...
Vroom Posted August 13, 2015 Author Share Posted August 13, 2015 25 is my forum view results. But when I browse the forum views, it always works fine within a couple seconds.I will try filtering the results by different things and see if anything slows down.Edit: Tried all the varieties of sorting and nothing was slow. I will try to catch the next time server has the problem and see if anything else is happening at the same time. Link to comment Share on other sites More sharing options...
Vroom Posted August 13, 2015 Author Share Posted August 13, 2015 In PHPMyAdmin the query took only 4 seconds. So there must have been some other factors involved at the time when it was slow. I will keep watching and try to catch what is causing it. I never had problems with 3.4. Only after upgrading I have this trouble. Link to comment Share on other sites More sharing options...
RevengeFNF Posted August 13, 2015 Share Posted August 13, 2015 In PHPMyAdmin the query took only 4 seconds. So there must have been some other factors involved at the time when it was slow. I will keep watching and try to catch what is causing it. I never had problems with 3.4. Only after upgrading I have this trouble.If you run the same exacty query, it might be getting the result in cache? Link to comment Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.