Slow query in MySQL: come analizzarle
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)
