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
  • Pingback: Leandro Farland
  • Pingback: natural sunscreen
  • Pingback: valentine gift
  • Pingback: valentine pillow
  • Pingback: 1chairman
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: no code robotics
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: Click Here
  • Pingback: buy-domains
  • Pingback: realtor advertise
  • Pingback: top startup books
  • Pingback: Google reviews
  • Pingback: What is a ici?
  • Pingback: 2023 Books
  • Pingback: funeral director
  • Pingback: rip
  • Pingback: find a grave
  • Pingback: burial
  • Pingback: Faculty expertise
  • Pingback: attendance
  • Pingback: Dr. Khaled Azazy
  • Pingback: Marketing program
  • Pingback: MBA in FUE
  • Pingback: MBA tuition fees
  • Pingback: Finance courses
  • Pingback: Accounting program
  • Pingback: grandpashabet
  • Pingback: meritking giriş
  • Pingback: madridbet giriş
  • Pingback: Character building
  • Pingback: اقتصاد
  • Pingback: meritking
  • Pingback: Centrifuges
  • Pingback: meritking
  • Pingback: meritking
  • Pingback: Prof. Ebada Sarhan
  • Pingback: meritking
  • Pingback: grandpashabet
  • Pingback: fuck google
  • Pingback: fuck
  • Pingback: porn
  • Pingback: challenge sportif
  • Pingback: squat avec barre
  • Pingback: pull-ups
  • Pingback: neuropure
  • Pingback: porn
  • Pingback: Fiverr Earn
  • Pingback: Fiverr Earn
  • Pingback: Fiverr Earn
  • Pingback: Fiverr Earn
  • Pingback: Fiverr Earn
  • Pingback: Fiverr Earn
  • Pingback: Fiverr Earn
  • Pingback: Fiverr Earn
  • Pingback: fiverrearn.com
  • Pingback: fiverrearn.com
  • Pingback: fiverrearn.com
  • Pingback: fiverrearn.com
  • Pingback: fiverrearn.com
  • Pingback: fiverrearn.com
  • Pingback: fiverrearn.com
  • Pingback: fiverrearn.com
  • Pingback: fiverrearn.com
  • Pingback: fiverrearn.com
  • Pingback: ikaria juice buy
  • Pingback: fiverrearn.com
  • Pingback: fiverrearn.com
  • Pingback: fiverrearn.com
  • Pingback: fiverrearn.com
  • Pingback: fiverrearn.com
  • Pingback: fiverrearn.com
  • Pingback: designer dogs
  • Pingback: bernedoodles
  • Pingback: jute rugs
  • Pingback: seo in Bahrain
  • Pingback: ooohd3.ru
  • Pingback: sitemap
  • Pingback: Yo.fan approval
  • Pingback: bitcoin
  • Pingback: fbofficial.ru
  • Pingback: swimsuit for women
  • Pingback: film
  • Pingback: i.8ua.ru
  • Pingback: french bulldog
  • Pingback: technology
  • Pingback: best Samsung
  • Pingback: future university
  • Pingback: future university
  • Pingback: batmanapollo.ru
  • Pingback: LORD FILM
  • Pingback: taurus medallion
  • Pingback: slot nexus
  • Pingback: wix website
  • Pingback: wix marketplace
  • Pingback: wix seo
  • Pingback: kin
  • Pingback: kinnit
  • Pingback: jz
  • Pingback: nlp
  • Pingback: time
  • Pingback: porn
  • Pingback: site
  • Pingback: rasshifrovka karti
  • Pingback: konsultaciya
  • Pingback: more
  • Pingback: Fiverr
  • Pingback: Fiverr
  • Pingback: 9999
  • Pingback: dapoxetine?
  • Pingback: french bulldog
  • Pingback: Lean
  • Pingback: 7
  • Pingback: Warranty
  • Pingback: Piano trade-in
  • Pingback: FUE
  • Pingback: FUE
  • Pingback: FUE
  • Pingback: FUE
  • Pingback: FUE
  • Pingback: House moving
  • Pingback: Storage solutions
  • Pingback: Office relocation
  • Pingback: citi.com/activate
  • Pingback: 777
  • Pingback: wlw.su
  • Pingback: vxi.su
  • Pingback: nlpvip.ru
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: manipulyation
  • Pingback: Coach
  • Pingback: psykholog
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: pupuk cair
  • Pingback: pupuk organik cair
  • Pingback: pupuk anorganik
  • Pingback: partners
  • Pingback: endo pump
  • Pingback: cortexi order
  • Pingback: arteris plus
  • Pingback: Globalization
  • Pingback: neuro rise
  • Pingback: french bulldog
  • Pingback: Tips
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: live sex cams
  • Pingback: live sex cams
  • Pingback: Azithromycin
  • Pingback: live sex cams
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: 354
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: serialebi qaerulad
  • Pingback: Fresh
  • Pingback: Link
  • Pingback: Lightroom Presets
  • Pingback: wix seo services
  • Pingback: christmas
  • Pingback: personalized
  • Pingback: Kuliah Termurah
  • Pingback: FiverrEarn
  • Pingback: psy
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: FiverrEarn
  • Pingback: kiino4k.ru
  • Pingback: depresiya
  • Pingback: new 2024
  • Pingback: sikiş
  • Pingback: film2024
  • Pingback: 123 Movies
  • Pingback: poxet 60
  • Pingback: 000
  • Pingback: samorazvitiepsi
  • Pingback: stromectol
  • Pingback: cheap sex cams
  • Pingback: kamagra store
  • Pingback: tadalista 20 mg
  • Pingback: fullersears.com
  • Pingback: fullersears.com
  • Pingback: fullersears.com
  • Pingback: vilitra online
  • Pingback: androgel price
  • Pingback: canine probiotics
  • Pingback: revatio online
  • Pingback: cialis black
  • Pingback: cenforce 50
  • Pingback: androgel for sale
  • Pingback: grandpashabet
  • Pingback: child porn
  • Pingback: porn
  • Pingback: live sex cams
  • Pingback: live sex cams
  • Pingback: live sex cams
  • Pingback: vilitra pills
  • Pingback: freeze dried
  • Pingback: freeze dried candy
  • Pingback: rare breed-trigger
  • Pingback: atorvastatin
  • Pingback: grandpashabet
  • Pingback: revatio cost
  • Pingback: grandpashabet
  • Pingback: rent a car Dubai
  • Pingback: 늑대닷컴
  • Pingback: One Peace AMV
  • Pingback: One Peace AMV
  • Pingback: superslot
  • Pingback: allgame
  • Pingback: 918kiss
  • Pingback: หวย24
  • Pingback: bulldog in clothes
  • Pingback: pg slot
  • Pingback: grandpashabet
  • Pingback: porno izle
  • Pingback: child porn
  • Pingback: carte uno reverse
  • Pingback: cybersécurité
  • Pingback: grandpashabet
  • Pingback: health supplements
  • Pingback: buy priligy
  • Pingback: cratosroyalbet
  • Pingback: megagame
  • Pingback: apply evisa online
  • Pingback: weight drops
  • Pingback: 44-40 ammo
  • Pingback: 6.5 grendel ammo
  • Pingback: 38/40 ammo
  • Pingback: fildena
  • Pingback: cenforce
  • Pingback: cost lasix
  • Pingback: stromectol dosage
  • Pingback: itsMasum.Com
  • Pingback: itsMasum.Com
  • Pingback: dapoxetine 30
  • Pingback: itsMasum.Com
  • Pingback: itsMasum.Com
  • Pingback: o/s informatique
  • Pingback: Skywhip tanks
  • Pingback: nangs Sydney
  • Pingback: spisok
  • Pingback: chauffe eau Tours
  • Pingback: itsmasum.com
  • Pingback: chat avenue
  • Pingback: talk to strangers
  • Pingback: chat with people
  • Pingback: itsmasum.com
  • Pingback: itsmasum.com
  • Pingback: child porn
  • Pingback: lipitor australia
  • Pingback: lipitor 20mg
  • Pingback: vidalista black 80
  • Pingback: vidalista 60
  • Pingback: list
  • Pingback: cenforce FM
  • Pingback: buy vidalista
  • Pingback: vidalista
  • Pingback: porn
  • Pingback: vidalista 60
  • Pingback: fildena 150
  • Pingback: buy plaquenil
  • Pingback: joker gaming
  • Pingback: cenforce 200 mg
  • Pingback: 50 mg clomid
  • Pingback: toronto jobs
  • Pingback: tokyo jobs
  • Pingback: houston jobs
  • Pingback: clomid off brand
  • Pingback: Buy clomid
  • Pingback: clomid
  • Pingback: buy priligy usa
  • Pingback: priligy
  • Pingback: dapox
  • Pingback: duratia
  • Pingback: kamagra 50mg
  • Pingback: russian-federation
  • Pingback: human design
  • Pingback: child porn
  • Pingback: vidalista
  • Pingback: vidalista
  • Pingback: vidalista ervaring
  • Pingback: vidalista 20
  • Pingback: child porn
  • Pingback: child porn
  • Pingback: vidalista 20 mg
  • Pingback: kamagra 50mg
  • Pingback: advairdiskus
  • Pingback: advair generic
  • Pingback: sex historie
  • Lascia un commento