Slow query in MySQL: come analizzarle

Slow query

MySQL – Slow query

Oggi parleremo di come affrontare in pratica un problema piuttosto comune: le slow query

Nel caso ci si accorga di una lentezza sul database o, comunque, sull’esecuzione di alcune query, possiamo analizzare il problema andando ad esaminare il log apposito.

Per prima cosa, reperiamo il path del file di log

[root@myweb ~]# grep slow /etc/my.cnf
log-slow-queries                        = /var/lib/mysql/myweb/logs/slow.log

Analizzare direttamente lo stesso può risultare poco pratico

[root@myweb ~]# tail -20 /var/lib/mysql/myweb/logs/slow.log
# User@Host: webroot[webroot] @ web-nt28.web-inet.local [172.31.233.115]
# Query_time: 51.533791  Lock_time: 0.000121 Rows_sent: 13586  Rows_examined: 27172
use webschema;
SET timestamp=1484113854;
SELECT 'default', 'WS', str_to_date(data_iscrizione_abbonamento,'%y%m%d'),codice, cap, presso, '-', cognome_nome, ditta, email, indirizzo, '-', cap, citta, substr(provincia,1,2), 'Italy', telefono, cellulare, fax, '-', professione, '-', sesso, str_to_date(data_di_nascita,'%y%m%d'), '-', codice_fiscale, partita_iva, 'n', 'n', 'n', categoria, str_to_date(data_iniziale_abbonamento,'%y%m%d'), str_to_date(data_prolungamento_abbonamento,'%y%m%d'), str_to_date(data_finale_abbonamento,'%y%m%d') FROM ws_abbonati ORDER BY codice;
# Time: 170111  7:20:36
# User@Host: MY_MON[MY_MON] @ localhost []
# Query_time: 134.089119  Lock_time: 0.000026 Rows_sent: 77698217  Rows_examined: 77698217
use mf_most_read;
SET timestamp=1484115636;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `storico`;
# Time: 170111  7:36:33
# User@Host: MY_MON[MY_MON] @ localhost []
# Query_time: 58.483696  Lock_time: 0.000032 Rows_sent: 1243025  Rows_examined: 1243025
use mf_supercontent;
SET timestamp=1484116593;
SELECT /*!40001 SQL_NO_CACHE */ * FROM `content`;
/usr/sbin/mysqld, Version: 5.5.23-log (MySQL Community Server (GPL)). started with:
Tcp port: 3306  Unix socket: /var/lib/mysql/myweb/pfile/myweb.sock
Time                 Id Command    Argument

Per cui suggerisco di utilizzare il seguente comando

[root@myweb ~]# mysqldumpslow -t 5 -s at /var/lib/mysql/myweb/logs/slow.log

Reading mysql slow query log from /var/lib/mysql/myweb/logs/slow.log
Count: 22  Time=130.21s (2864s)  Lock=0.00s (0s)  Rows=75819375.8 (1668026268), BT_MON[BT_MON]@localhost
  SELECT /*!N SQL_NO_CACHE */ * FROM `storico`

Count: 21  Time=121.07s (2542s)  Lock=0.00s (0s)  Rows=0.0 (0), webroot[webroot]@web-nt29.web-inet.local
  select * from ws_content where categoria=N and stato='S' and cod_rivista in ('S') and tipo_cod in ('S') and data_scadenza>'S' and match(titolo) against ('S' in boolean mode) order by giorni_reverse limit N,N

Count: 2  Time=121.07s (242s)  Lock=0.00s (0s)  Rows=0.0 (0), webroot[webroot]@web-nt29.web-inet.local
  select * from ws_content where categoria=N and stato='S' and cod_rivista in ('S','S') and tipo_cod in ('S','S') and giorni_reverse<=datediff('S','S') and data_scadenza>'S' order by giorni_reverse, pagina, codice limit N,N

Count: 53  Time=121.07s (6416s)  Lock=0.00s (0s)  Rows=0.0 (0), webroot[webroot]@web-nt29.web-inet.local
  select * from ws_content where categoria=N and stato='S' and cod_rivista in ('S') and giorni_reverse<=datediff('S','S') and data_scadenza>'S' order by giorni_reverse, pagina, codice limit N,N

Count: 29  Time=121.07s (3510s)  Lock=0.00s (0s)  Rows=0.0 (0), webroot[webroot]@web-nt29.web-inet.local
  select * from ws_content where categoria=N and stato='S' and cod_rivista in ('S') and tipo_cod in ('S','S') and data_scadenza>'S' order by giorni_reverse, pagina, codice limit N,N

L’output del comando ha un baco, ovvero non visualizza correttamente le hint, come nell’esempio di seguito

SELECT /*!N SQL_NO_CACHE */ * FROM `storico`;
[root@myweb ~]# grep "`storico`" /var/lib/mysql/myweb/logs/slow.log|sort -u
SELECT /*!40001 SQL_NO_CACHE */ * FROM `storico`;

Attenzione allo step successivo, ovvero l’analisi dell’execution plan, perché nel log non è indicato il database, a meno che non sia esplicato nella query
Nel caso, bisogna reperire l’informazione e modificare la query, come nell’esempio in calce

mysql> explain SELECT 'default', 'WS', str_to_date(data_iscrizione_abbonamento,'%y%m%d'),codice, cap, presso, '-', cognome_nome, ditta, email, indirizzo, '-', cap, citta, substr(provincia,1,2), 'Italy', telefono, cellulare, fax, '-', professione, '-', sesso, str_to_date(data_di_nascita,'%y%m%d'), '-', codice_fiscale, partita_iva, 'n', 'n', 'n', categoria, str_to_date(data_iniziale_abbonamento,'%y%m%d'), str_to_date(data_prolungamento_abbonamento,'%y%m%d'), str_to_date(data_finale_abbonamento,'%y%m%d') FROM ws_abbonati ORDER BY codice;
ERROR 1046 (3D000): No database selected
mysql> select `TABLE_SCHEMA` from `INFORMATION_SCHEMA`.`TABLES` where `TABLE_NAME`='ws_abbonati';
+--------------+
| TABLE_SCHEMA |
+--------------+
| webschema    |
+--------------+
1 row in set (0.01 sec)

mysql> explain SELECT 'default', 'WS', str_to_date(data_iscrizione_abbonamento,'%y%m%d'),codice, cap, presso, '-', cognome_nome, ditta, email, indirizzo, '-', cap, citta, substr(provincia,1,2), 'Italy', telefono, cellulare, fax, '-', professione, '-', sesso, str_to_date(data_di_nascita,'%y%m%d'), '-', codice_fiscale, partita_iva, 'n', 'n', 'n', categoria, str_to_date(data_iniziale_abbonamento,'%y%m%d'), str_to_date(data_prolungamento_abbonamento,'%y%m%d'), str_to_date(data_finale_abbonamento,'%y%m%d') FROM webschema.ws_abbonati ORDER BY codice;
+----+-------------+-------------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table       | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+----+-------------+-------------+------+---------------+------+---------+------+-------+----------------+
|  1 | SIMPLE      | ws_abbonati | ALL  | NULL          | NULL | NULL    | NULL | 13587 | Using filesort |
+----+-------------+-------------+------+---------------+------+---------+------+-------+----------------+
1 row in set (0.00 sec)

Ref: Manuale MySQL – Slow query

giugno 6, 2017

Tag:,
  • Pingback: 3independence
  • Lascia un commento