help... this query is killing my server

[Vroom]

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 |

 

[AutoItScript]

Looks like one of the Activity Stream queries. Turn it off altogether for a while to verify. (System->Applications->System->Activity Stream)

[Vroom]

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.

[Adriano Faria]

I would blame tags:

 LEFT JOIN `core_tags_cache` ON tag_cache_key=MD5(CONCAT('forums;forums;',forums_topics.tid)

[AutoItScript]

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?

[RevengeFNF]

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.

[Adriano Faria]

Probably forum view.

[Vroom]

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.

[Vroom]

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.

[RevengeFNF]

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?