| 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: | |
| 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: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".
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;