Regitze Birketoft
Customer
hey
I have a MAJOR problem....
app. 160 times a day i certain query is being called and it returns more than 500.000 results each time... its literally killing my entire DB - everything STOPS meanwhile... i have an avg of 59 sec. 160 times a day where the forum is useless..
i need to know right now how to fix this, because i am loosing users because of the speed... noone wants to wait an avg of 59 secs to post or to do anything else...
i turned on the slow Q log to get the exact query that is causing my problem.... please tell me how to fix my problem!!!
heres the result of the slow query log
# Query 1: 0.00 QPS, 0.08x concurrency, ID 0x043CEB5223529471 at byte 3885737
# This item is included in the report because it matches --limit.
# Scores: Apdex = 0.00 [1.0]*, V/M = 44.22
# Query_time sparkline: | ^|
# Time range: 2011-09-12 15:58:30 to 2011-09-13 08:12:25
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 42 79
# Exec time 68 4561s 11s 229s 58s 130s 51s 32s
# Lock time 2 16ms 0 16ms 197us 0 2ms 0
# Rows sent 2 1.53k 14 20 19.86 19.46 0.83 19.46
# Rows examine 91 227.47M 1.27M 3.05M 2.88M 3.03M 311.82k 3.03M
# Query size 1 76.07k 986 986 986 986 0 986
# String:
# Databases kattegale
# Hosts dumpe.wns.dk
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s
# 10s+ ################################################################
# Tables
# SHOW TABLE STATUS FROM `kattegale` LIKE 'vb_dbtech_thanks_entry'\G
# SHOW CREATE TABLE `kattegale`.`vb_dbtech_thanks_entry`\G
# SHOW TABLE STATUS FROM `kattegale` LIKE 'vb_user'\G
# SHOW CREATE TABLE `kattegale`.`vb_user`\G
# SHOW TABLE STATUS FROM `kattegale` LIKE 'vb_post'\G
# SHOW CREATE TABLE `kattegale`.`vb_post`\G
# SHOW TABLE STATUS FROM `kattegale` LIKE 'vb_thread'\G
# SHOW CREATE TABLE `kattegale`.`vb_thread`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT
post.postid,
post.title AS posttitle,
thread.title AS threadtitle,
thread.title AS title,
thread.threadid,
entry.varname,
entry.dateline,
entry.userid,
entry.receiveduserid,
user.username,
user.usergroupid,
user.infractiongroupid,
user.displaygroupid,
receiveduser.username AS receivedusername,
receiveduser.usergroupid AS receivedusergroupid,
receiveduser.infractiongroupid AS receivedinfractiongroupid,
receiveduser.displaygroupid AS receiveddisplaygroupid
FROM vb_dbtech_thanks_entry AS entry
LEFT JOIN vb_user AS user USING(userid)
LEFT JOIN vb_user AS receiveduser ON(receiveduser.userid = entry.receiveduserid)
LEFT JOIN vb_post AS post ON(post.postid = entry.contentid)
LEFT JOIN vb_thread AS thread ON(thread.threadid = post.threadid)
WHERE thread.forumid IN(46,47,48,6,23,19,50,17,16,20,8,52,24,53,7,26,27,33,34,39,36,5,40,15,28,10,12,29,30,35,3,14,61,0)
ORDER BY entry.dateline DESC, entry.userid
LIMIT 515240, 20\G
I have a MAJOR problem....
app. 160 times a day i certain query is being called and it returns more than 500.000 results each time... its literally killing my entire DB - everything STOPS meanwhile... i have an avg of 59 sec. 160 times a day where the forum is useless..
i need to know right now how to fix this, because i am loosing users because of the speed... noone wants to wait an avg of 59 secs to post or to do anything else...
i turned on the slow Q log to get the exact query that is causing my problem.... please tell me how to fix my problem!!!
heres the result of the slow query log
# Query 1: 0.00 QPS, 0.08x concurrency, ID 0x043CEB5223529471 at byte 3885737
# This item is included in the report because it matches --limit.
# Scores: Apdex = 0.00 [1.0]*, V/M = 44.22
# Query_time sparkline: | ^|
# Time range: 2011-09-12 15:58:30 to 2011-09-13 08:12:25
# Attribute pct total min max avg 95% stddev median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count 42 79
# Exec time 68 4561s 11s 229s 58s 130s 51s 32s
# Lock time 2 16ms 0 16ms 197us 0 2ms 0
# Rows sent 2 1.53k 14 20 19.86 19.46 0.83 19.46
# Rows examine 91 227.47M 1.27M 3.05M 2.88M 3.03M 311.82k 3.03M
# Query size 1 76.07k 986 986 986 986 0 986
# String:
# Databases kattegale
# Hosts dumpe.wns.dk
# Query_time distribution
# 1us
# 10us
# 100us
# 1ms
# 10ms
# 100ms
# 1s
# 10s+ ################################################################
# Tables
# SHOW TABLE STATUS FROM `kattegale` LIKE 'vb_dbtech_thanks_entry'\G
# SHOW CREATE TABLE `kattegale`.`vb_dbtech_thanks_entry`\G
# SHOW TABLE STATUS FROM `kattegale` LIKE 'vb_user'\G
# SHOW CREATE TABLE `kattegale`.`vb_user`\G
# SHOW TABLE STATUS FROM `kattegale` LIKE 'vb_post'\G
# SHOW CREATE TABLE `kattegale`.`vb_post`\G
# SHOW TABLE STATUS FROM `kattegale` LIKE 'vb_thread'\G
# SHOW CREATE TABLE `kattegale`.`vb_thread`\G
# EXPLAIN /*!50100 PARTITIONS*/
SELECT
post.postid,
post.title AS posttitle,
thread.title AS threadtitle,
thread.title AS title,
thread.threadid,
entry.varname,
entry.dateline,
entry.userid,
entry.receiveduserid,
user.username,
user.usergroupid,
user.infractiongroupid,
user.displaygroupid,
receiveduser.username AS receivedusername,
receiveduser.usergroupid AS receivedusergroupid,
receiveduser.infractiongroupid AS receivedinfractiongroupid,
receiveduser.displaygroupid AS receiveddisplaygroupid
FROM vb_dbtech_thanks_entry AS entry
LEFT JOIN vb_user AS user USING(userid)
LEFT JOIN vb_user AS receiveduser ON(receiveduser.userid = entry.receiveduserid)
LEFT JOIN vb_post AS post ON(post.postid = entry.contentid)
LEFT JOIN vb_thread AS thread ON(thread.threadid = post.threadid)
WHERE thread.forumid IN(46,47,48,6,23,19,50,17,16,20,8,52,24,53,7,26,27,33,34,39,36,5,40,15,28,10,12,29,30,35,3,14,61,0)
ORDER BY entry.dateline DESC, entry.userid
LIMIT 515240, 20\G
