Bug #55023 Many minor_page_faults in "large" queries
Submitted: 6 Jul 2010 10:33 Modified: 13 Nov 2010 20:08
Reporter: Rustam Gumerov Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.46 OS:Linux (gentoo amd64)
Assigned to: CPU Architecture:Any
Tags: join, minor_page_faults, sending data

[6 Jul 2010 10:33] Rustam Gumerov
Description:
Periodically, we encounter a problem a long running query on stage Sending data. When you turn on profiling, see what happens multiple context switching as voluntary or involuntary, and more events minor_pages_faults.

//The text translated by Google Translate

How to repeat:
Query:

SELECT searchcore.searchgroupid, searchgroup.dateline
FROM searchcore AS searchcore INNER JOIN thread AS thread ON (searchcore.contenttypeid = 1 AND searchcore.groupid = thread.threadid) JOIN searchcore_text
AS searchcore_text ON (searchcore_text.searchcoreid = searchcore.searchcoreid) JOIN searchgroup AS searchgroup ON (searchgroup.searchgroupid =
 searchcore.searchgroupid)
 WHERE MATCH(searchcore_text.title, searchcore_text.keywordtext) AGAINST ('а�аЕаЛаЕаЗаНб�аЙ б�аЕаЛаОаВаЕаК 2') AND thread.forumid IN (339) AND
 searchgroup.contenttypeid IN ('2')
 LIMIT 500;
[6 Jul 2010 10:35] Rustam Gumerov
my.cnf

Attachment: my.cnf (application/octet-stream, text), 2.34 KiB.

[6 Jul 2010 10:35] Rustam Gumerov
Profiling problem query

Attachment: profiling.txt (text/plain), 10.43 KiB.

[6 Jul 2010 10:37] Rustam Gumerov
Used tables specification

Attachment: Tables DDL and STATUS.txt (text/plain), 4.76 KiB.

[6 Jul 2010 10:42] Alexey L. Vasilchenko
the problem is sporadic, but due to the fact that you are using MyISAM tables in queues requests interrupting the service.
[6 Jul 2010 11:56] Valeriy Kravchuk
Please, send the results of EXPLAIN for the problematic query.
[6 Jul 2010 12:13] Alexey L. Vasilchenko
mysql> EXPLAIN SELECT searchcore.searchgroupid, searchgroup.dateline FROM searchcore AS searchcore INNER JOIN thread AS thread ON (searchcore.contenttypeid = 1 AND searchcore.groupid = thread.threadid) JOIN searchcore_text AS searchcore_text ON (searchcore_text.searchcoreid = searchcore.searchcoreid) JOIN searchgroup AS searchgroup ON (searchgroup.searchgroupid =  searchcore.searchgroupid)  WHERE MATCH(searchcore_text.title, searchcore_text.keywordtext) AGAINST ('а�аЕаЛаЕаЗаНб�аЙ б�аЕаЛаОаВаЕаК 2') AND thread.forumid IN (339) AND  searchgroup.contenttypeid IN ('2')  LIMIT 500\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: searchcore_text
         type: fulltext
possible_keys: PRIMARY,text
          key: text
      key_len: 0
          ref: 
         rows: 1
        Extra: Using where
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: searchcore
         type: eq_ref
possible_keys: PRIMARY,contentunique,searchgroupid
          key: PRIMARY
      key_len: 4
          ref: forum_vbulletin.searchcore_text.searchcoreid
         rows: 1
        Extra: Using where
*************************** 3. row ***************************
           id: 1
  select_type: SIMPLE
        table: searchgroup
         type: eq_ref
possible_keys: PRIMARY,groupunique
          key: PRIMARY
      key_len: 4
          ref: forum_vbulletin.searchcore.searchgroupid
         rows: 1
        Extra: Using where
*************************** 4. row ***************************
           id: 1
  select_type: SIMPLE
        table: thread
         type: eq_ref
possible_keys: PRIMARY,forumid,forumid_lastpost
          key: PRIMARY
      key_len: 4
          ref: forum_vbulletin.searchcore.groupid
         rows: 1
        Extra: Using where
[7 Jul 2010 7:50] Anil Alpati
PLEASE Send me details for your Hardware configuations and disable the logs for slow queries this will reduce performance
[7 Jul 2010 8:57] Rustam Gumerov
Server hardware configuration

Attachment: lshw.html (text/html), 116.64 KiB.

[7 Jul 2010 8:59] Rustam Gumerov
Slow-logs turn off, in the late afternoon reported the results.
[8 Jul 2010 10:40] Alexey L. Vasilchenko
unfortunately off slowlog nothing changed, errors still repeated
[14 Jul 2010 16:05] Rustam Gumerov
up.
[27 Jul 2010 6:51] Alexey L. Vasilchenko
have a good day, tell me if there are any progress on the issue?
[12 Oct 2010 15:52] Sveta Smirnova
Thank you for the feedback.

Have you tried to optimize used tables? If not please try now and if problem still exists provide DDL for searchgroup table.
[12 Oct 2010 17:04] Rustam Gumerov
Optimization had not helped.
searchgroup ddl and status  in attach
[12 Oct 2010 17:04] Rustam Gumerov
searchgroup_ddl

Attachment: searchgroup_ddl.txt (text/plain), 516 bytes.

[12 Oct 2010 17:05] Rustam Gumerov
searchgroup table status

Attachment: searchgroup_status.txt (text/plain), 569 bytes.

[13 Oct 2010 20:08] Sveta Smirnova
Thank you for the feedback.

I still can not repeat described behavior with test data. Please try with current verison 5.1.51 and if problem still exists try to create minimal dataset problem is consistently repeatable with. Please also try if setting query_cache_size to 0 and/or commenting innodb_flush_method solves the problem.
[14 Nov 2010 0:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".