mysqlsla is interesting tool to analyze slow log query, aggregate same query in one and generate unique sql statement withCount, (max, min, avg) execute time, lock time, Rows sent, Rows examined for each unique one.
you can use it to review indexes and drop unused index, and create another.
Report for slow logs: slowquery1day.txt
791 queries total, 85 unique
Sorted by 't_sum'
Grand Totals: Time 23.05k s, Lock 2.18k s, Rows sent 24.17M, Rows Examined 120.61M
____________________________________________________________ 001 ___
Count : 355 (44.88%)
Time : 7588 s total, 21.374648 s avg, 11 s to 203 s max (32.92%)
95% of Time : 6352 s total, 18.848665 s avg, 11 s to 44 s max
Lock Time (s) : 675 s total, 1.901408 s avg, 0 to 191 s max (30.95%)
95% of Lock : 57 s total, 169.139 ms avg, 0 to 6 s max
Rows sent : 141 avg, 1 to 150 max (0.21%)
Rows examined : 37.17k avg, 24 to 310.07k max (10.94%)
Database : db_test
Users :
FrashaSlvReplic@ 70.84.164.205 : 92.11% (327) of query, 87.74% (694) of all users
Frashat_test@ 70.84.164.205 : 7.89% (28) of query, 5.44% (43) of all users
Query abstract:
SELECT post.postid, post.pagetext, ifnull( user.username , post.username ) AS username, dateline FROM noway_post AS post LEFT JOIN noway_user AS user ON (user.userid = post.userid) WHERE threadid = N AND visible = N AND post.userid NOT IN (N1) ORDER BY dateline ASC LIMIT N,N;
Query sample:
SELECT post.postid, post.pagetext, IFNULL( user.username , post.username ) AS username, dateline
FROM noway_post AS post
LEFT JOIN noway_user AS user ON (user.userid = post.userid)
WHERE threadid = 314734
AND visible = 1
AND post.userid NOT IN (28816)
ORDER BY dateline ASC
LIMIT 300,150;
_____________________________________________________________ 002 ___
Count : 90 (11.38%)
Time : 1840 s total, 20.444444 s avg, 11 s to 128 s max (7.98%)
95% of Time : 1445 s total, 17 s avg, 11 s to 35 s max
Lock Time (s) : 0 total, 0 avg, 0 to 0 max (0.00%)
95% of Lock : 0 total, 0 avg, 0 to 0 max
Rows sent : 25 avg, 0 to 25 max (0.01%)
Rows examined : 13.89k avg, 146 to 26.54k max (1.04%)
Database : db
Users :
FrashaSlvReplic@ 70.84.164.205 : 100.00% (90) of query, 87.74% (694) of all users
Query abstract:
SELECT COUNT(*) AS COUNT, threadid, MAX(dateline) AS lastpost FROM noway_post AS post WHERE post.userid = N AND post.visible = N AND post.threadid IN (N25) GROUP BY threadid;
Query sample:
SELECT COUNT(*) AS count, threadid, MAX(dateline) AS lastpost
FROM noway_post AS post
WHERE post.userid = 268569 AND
post.visible = 1 AND
post.threadid IN (0820005, 636923, 803089, 735659, 735645, 808314, 765143, 688062, 738500, 787405, 633043, 454061, 703866, 707968, 676619, 647881, 645328, 451514, 674289, 650001, 654712, 562233, 645180, 638518, 619261)
GROUP BY threadid;
_____________________________________________________________ 003 ___
Count : 15 (1.90%)
Time : 1574 s total, 104.933333 s avg, 21 s to 282 s max (6.83%)
95% of Time : 1292 s total, 92.285714 s avg, 21 s to 261 s max
Lock Time (s) : 47 s total, 3.133333 s avg, 0 to 42 s max (2.15%)
95% of Lock : 5 s total, 357.143 ms avg, 0 to 5 s max
Rows sent : 84 avg, 10 to 100 max (0.01%)
Rows examined : 69.30k avg, 1.03k to 219.67k max (0.86%)
Database : db
Users :
FrashaSlvReplic@ 70.84.164.205 : 100.00% (15) of query, 87.74% (694) of all users
Query abstract:
SELECT DISTINCT thread.threadid, thread.forumid, post.userid FROM noway_thread AS thread INNER JOIN noway_post AS post ON(thread.threadid = post.threadid ) WHERE MATCH(post.title, post.pagetext) AGAINST ('S') AND thread.forumid NOT IN (N20) AND thread.forumid IN(N1) AND post.visible = N LIMIT N;
Query sample:
SELECT
DISTINCT thread.threadid, thread.forumid, post.userid
FROM noway_thread AS thread
INNER JOIN noway_post AS post ON(thread.threadid = post.threadid )
WHERE MATCH(post.title, post.pagetext) AGAINST ('هذه الدنيا عجايب') AND thread.forumid NOT IN (0,38,41,117,55,94,99,100,72,39,42,43,57,44,37,26,40,27,34,53) AND thread.forumid IN(86) AND post.visible = 1
LIMIT 100;
http://hackmysql.com/mysqlsla
and notice there are 10 days missed, for example:
i try it but i didn't see this case, example:
?
