How to find which is the really slow query?

Question

I am testing a web page (wordpress) , I have this log but I cant understand which is the really slow query here is the link to read the queries of my website , please find time to tell me what is the dangerous query in this log that kills my CPU

http://balkanweb.com/mysql_slow.log

Answer 1

The best free tool to use to work with the slow query log is pt-query-digest. By default, this produces a report showing you where your aggregate response time is going, broken down by query fingerprint.

I downloaded your log and ran it through pt-query-digest. Here's part of the output, which is a ranked list of queries and some statistics about how much time they take:

# Profile
# Rank Query ID           Response time    Calls R/Call   V/M   Item
# ==== ================== ================ ===== ======== ===== ==========
#    1 0xC52F6DEC37D30D44 15065.0357 51.6%    80 188.3129  3.18 SELECT bwp_options
#    2 0x4876D9B1B390C783  4989.7583 17.1%    28 178.2057  7.91 SELECT bwp_options
#    3 0xE6593111E942BBD2  2337.6846  8.0%    10 233.7685  2.10 UPDATE bwp_options
#    4 0xF25CBDE8A6D65826  1784.7358  6.1%     8 223.0920  1.23 SELECT bwp_postmeta
#    5 0xC19DB36FFE85363C  1225.1890  4.2%     5 245.0378  0.09 SELECT bwp_terms bwp_term_taxonomy
#    6 0x7AC4A4CA8054E73D   981.1785  3.4%     4 245.2946  0.10 SELECT bwp_terms bwp_term_taxonomy bwp_term_relationships
#    7 0x3C55C7D48BD1B7C5   714.0035  2.4%     3 238.0012  0.00 SELECT bwp_term_relationships bwp_term_taxonomy
#    8 0x3A5E09EFF2C9AA73   487.4629  1.7%     2 243.7314  0.08 UPDATE bwp_postmeta
#    9 0x6545AED3996807BC   460.0886  1.6%     2 230.0443  0.22 SELECT bwp_postmeta
#   13 0x7AEDF19FDD3A33F1   207.8139  0.7%    30   6.9271  1.11 SELECT wp_options
# MISC 0xMISC               962.7022  3.3%    25  38.5081   0.0 <12 ITEMS>

You can see that the #1 query is not the slowest at 188 sec on average, but the number of calls (80) is higher than others, so it accounts for the most total response time. That #1 query is the following:

SELECT option_value FROM bwp_options WHERE option_name = 'ossdl_https' LIMIT 1

I would recommend that you need an index on the option_name column on that table.

Several other queries are very long-running, ranging between 178 and 245 seconds on average. You should optimize all of these queries with indexes. In fact, they are so slow that I wonder if you simply have a server with inadequate resources. Are you swapping?

Here are a few nice blog posts that introduce you to pt-query-digest:

• Identifying the load with the help of pt-query-digest and Percona Server
• High-load problems? Investigate them with pt-query-digest
• Tools and tips for analysis of MySQL’s Slow Query Log

pt-query-digest is part of Percona Toolkit, a free, open-source collection of script tools for working with MySQL and Percona Server.

Answer 2

Time taking queries are,

Count: 1  Time=2.90s (2s)  Lock=0.00s (0s)  Rows=1.0 (1), balkanwe_site[balkanwe_site]@localhost
  SELECT   bwp_posts.* FROM bwp_posts  WHERE N=N  AND (((bwp_posts.post_title LIKE 'S') OR (bwp_posts.post_content LIKE 'S')) AND ((bwp_posts.post_title LIKE 'S') OR (bwp_posts.post_content LIKE 'S')))  AND (bwp_posts.post_password = 'S')  AND bwp_posts.post_type IN ('S', 'S', 'S', 'S', 'S') AND (bwp_posts.post_status = 'S')  ORDER BY (CASE WHEN bwp_posts.post_title LIKE 'S' THEN N WHEN bwp_posts.post_title LIKE 'S' AND bwp_posts.post_title LIKE 'S' THEN N WHEN bwp_posts.post_title LIKE 'S' OR bwp_posts.post_title LIKE 'S' THEN N WHEN bwp_posts.post_content LIKE 'S' THEN N ELSE N END), bwp_posts.post_date DESC

Count: 1  Time=2.96s (2s)  Lock=0.00s (0s)  Rows=1.0 (1), balkanwe_site[balkanwe_site]@localhost
  SELECT SQL_CALC_FOUND_ROWS  bwp_posts.ID FROM bwp_posts  WHERE N=N  AND (((bwp_posts.post_title LIKE 'S') OR (bwp_posts.post_content LIKE 'S')) AND ((bwp_posts.post_title LIKE 'S') OR (bwp_posts.post_content LIKE 'S')))  AND (bwp_posts.post_password = 'S')  AND bwp_posts.post_type IN ('S', 'S', 'S', 'S', 'S') AND (bwp_posts.post_status = 'S')  ORDER BY (CASE WHEN bwp_posts.post_title LIKE 'S' THEN N WHEN bwp_posts.post_title LIKE 'S' AND bwp_posts.post_title LIKE 'S' THEN N WHEN bwp_posts.post_title LIKE 'S' OR bwp_posts.post_title LIKE 'S' THEN N WHEN bwp_posts.post_content LIKE 'S' THEN N ELSE N END), bwp_posts.post_date DESC LIMIT N, N

Count: 2  Time=1.57s (3s)  Lock=0.00s (0s)  Rows=20.0 (40), balkanwe_wp[balkanwe_wp]@localhost
  (SELECT n.IDNotizia, n.Titolo, n.IDCategoria, n.Data, n.Descrizione, N AS Notizia FROM notizie AS n, notizie_legami AS nl WHERE n.IDNotizia=nl.IDNotizia AND n.Online='S' AND (n.Pubblicail='S' OR n.Pubblicail IS NULL) AND  (n.Titolo LIKE 'S' OR n.Sottotitolo LIKE 'S' OR n.Descrizione LIKE 'S' OR n.Notizia like 'S')  GROUP BY nl.IDCollegamento) UNION (SELECT IDNews, Titolo, IDCategoria, Data, News, N AS Notizia FROM news WHERE id_ext!='S' AND  (Titolo LIKE 'S' OR News LIKE 'S') ) ORDER BY Data DESC LIMIT N

Count: 1  Time=1.88s (1s)  Lock=0.00s (0s)  Rows=16.0 (16), root[root]@localhost
  SELECT TABLE_SCHEMA as DB,SUM(DATA_LENGTH)+SUM(INDEX_LENGTH) AS SPACEUSED from information_schema.tables GROUP BY TABLE_SCHEMA

Count: 1  Time=1.06s (1s)  Lock=0.00s (0s)  Rows=20.0 (20), balkanwe_wp[balkanwe_wp]@localhost
  (SELECT n.IDNotizia, n.Titolo, n.IDCategoria, n.Data, n.Descrizione, N AS Notizia FROM notizie AS n, notizie_legami AS nl WHERE n.IDNotizia=nl.IDNotizia AND n.Online='S' AND (n.Pubblicail='S' OR n.Pubblicail IS NULL) AND  ((n.Titolo LIKE 'S' OR n.Sottotitolo LIKE 'S' OR n.Descrizione LIKE 'S' OR n.Notizia LIKE 'S') AND (n.Titolo LIKE 'S' OR n.Sottotitolo LIKE 'S' OR n.Descrizione LIKE 'S' OR n.Notizia LIKE 'S'))  GROUP BY nl.IDCollegamento) UNION (SELECT IDNews, Titolo, IDCategoria, Data, News, N AS Notizia FROM news WHERE id_ext!='S' AND  ((Titolo LIKE 'S' OR News LIKE 'S') AND (Titolo LIKE 'S' OR News LIKE 'S')) ) ORDER BY Data DESC LIMIT N

Count: 1  Time=1.21s (1s)  Lock=0.00s (0s)  Rows=34.0 (34), balkanwe_wp[balkanwe_wp]@localhost
  SELECT n.IDNotizia FROM notizie AS n, notizie_legami AS nl WHERE n.IDNotizia=nl.IDNotizia AND n.Online='S' AND (n.Pubblicail='S' OR n.Pubblicail IS NULL) AND  ((n.Titolo LIKE 'S' OR n.Sottotitolo LIKE 'S' OR n.Descrizione LIKE 'S' OR n.Notizia LIKE 'S') AND (n.Titolo LIKE 'S' OR n.Sottotitolo LIKE 'S' OR n.Descrizione LIKE 'S' OR n.Notizia LIKE 'S'))  GROUP BY nl.IDCollegamento

Count: 4  Time=1.82s (7s)  Lock=0.01s (0s)  Rows=199.8 (799), balkanwe_wp[balkanwe_wp]@localhost
  SELECT n.IDNotizia FROM notizie AS n, notizie_legami AS nl WHERE n.IDNotizia=nl.IDNotizia AND n.Online='S' AND (n.Pubblicail='S' OR n.Pubblicail IS NULL) AND  (n.Titolo LIKE 'S' OR n.Sottotitolo LIKE 'S' OR n.Descrizione LIKE 'S' OR n.Notizia like 'S')  GROUP BY nl.IDCollegamento

Count: 5  Time=1.52s (7s)  Lock=0.00s (0s)  Rows=19.4 (97), balkanwe_site[balkanwe_site]@localhost
  SELECT ID, post_title, post_type, post_status FROM bwp_posts WHERE ( post_title REGEXP 'S' OR post_content REGEXP 'S' ) AND post_type = 'S'  AND ID != N  AND post_status NOT IN ('S', 'S') ORDER BY post_date DESC LIMIT N

You can check the slowest queries by running the command ,

mysqldumpslow -s -t 5 slow.log

this will show top 5 slow queries.