Cum să activați jurnalul de interogări lente în MySQL și MariaDB

Jurnalul de interogări lente (slow query log) este o funcție de diagnosticare integrată în MySQL și MariaDB care înregistrează fiecare instrucțiune SQL al cărei timp de execuție depășește un prag configurabil. Acesta captează durata interogării, timpul de blocare, rândurile examinate, rândurile trimise și textul SQL complet — oferind administratorilor de baze de date și dezvoltatorilor o pistă de audit precisă, bazată pe fișiere, pentru fiecare interogare care degradează performanța aplicației.

Activarea acestuia este una dintre acțiunile cu cel mai mare impact pe care le puteți întreprinde în timpul optimizării performanței bazei de date. Spre deosebire de instrumentele generice de monitorizare, jurnalul de interogări lente identifică exact instrucțiunile responsabile de latență, fiind indispensabil pentru optimizarea indexurilor, restructurarea interogărilor și planificarea capacității pe orice server — de la un mediu VPS Hosting cu un singur utilizator până la un cluster de baze de date dedicat cu mai multe noduri.

De ce contează jurnalul de interogări lente dincolo de monitorizarea de bază

Majoritatea echipelor apelează la EXPLAIN sau SHOW PROCESSLIST în mod reactiv, după ce utilizatorii raportează lentoare. Jurnalul de interogări lente funcționează proactiv: acumulează dovezi pe parcursul orelor sau zilelor de trafic real, capturând vinovații intermitenti care nu apar niciodată în timpul unei ferestre de inspecție manuale.

Principalele beneficii operaționale includ:

• Izolarea blocajelor — distinge scanările complete ale tabelelor legate de CPU de problemele de contention la blocare folosind rapoartele Query_time față de Lock_time
• Analiza lacunelor de index — indicatorul log_queries_not_using_indexes evidențiază fiecare interogare care efectuează o scanare completă, indiferent de timpul de execuție brut
• Detectarea regresiilor — compararea instantaneelor de jurnal înainte și după o implementare relevă dacă noul cod a introdus tipare de interogări mai lente
• Dovezi pentru planificarea capacității — valorile Rows_examined care sunt cu ordine de mărime mai mari decât Rows_sent indică indexuri lipsă sau utilizate incorect, care se amplifică sub sarcină

MySQL față de MariaDB: Comparație a funcțiilor jurnalului de interogări lente

Ambele motoare partajează aceeași infrastructură de bază pentru jurnalul de interogări lente, moștenită din MySQL 5.1, dar MariaDB a extins-o în mai multe moduri semnificative.

Opțiunile log_slow_verbosity și log_slow_rate_limit din MariaDB sunt deosebit de valoroase în mediile de producție cu debit ridicat, unde jurnalizarea fiecărei interogări lente ar deveni ea însăși o sarcină pentru performanță.

Pasul 1: Localizați fișierul de configurare

MySQL și MariaDB citesc configurația din căi implicite diferite, în funcție de distribuție și metoda de instalare.

MySQL:

/etc/my.cnf (bazat pe RPM: RHEL, CentOS, AlmaLinux, Rocky Linux)
/etc/mysql/my.cnf (Debian/Ubuntu)
/etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu cu pachetul mysql-server)

MariaDB:

/etc/my.cnf.d/server.cnf (bazat pe RPM)
/etc/mysql/mariadb.conf.d/50-server.cnf (Debian/Ubuntu)
/etc/mysql/mariadb.cnf (layout-uri mai vechi Debian)

Dacă nu sunteți sigur care fișier este activ, interogați procesul în execuție:
mysqld --verbose --help 2>/dev/null | grep -A1 "Default options"
Aceasta afișează lista exactă ordonată a fișierelor pe care daemonul le citește la pornire, inclusiv orice directoare !includedir.
Deschideți fișierul de configurare principal cu editorul preferat:
sudo nano /etc/my.cnf
Pasul 2: Adăugați directivele pentru jurnalul de interogări lente în [mysqld]
Toți parametrii pentru jurnalul de interogări lente aparțin secțiunii [mysqld]. Dacă secțiunea nu există, creați-o la începutul fișierului.
[mysqld]
# Core slow query log settings
slow_query_log          = 1
slow_query_log_file     = /var/log/mysql/slow-query.log
long_query_time         = 1

# Log queries that skip index usage entirely
log_queries_not_using_indexes = 1

# Avoid flooding the log with index warnings on low-traffic tables
min_examined_row_limit  = 100

# Log slow administrative statements (ALTER TABLE, OPTIMIZE TABLE, etc.)
log_slow_admin_statements = 1
Descrierea parametrilor:

slow_query_log = 1 — activează funcția; setați la 0 pentru a dezactiva fără a elimina blocul
slow_query_log_file — calea absolută către fișierul jurnal; utilizatorul procesului MySQL/MariaDB (mysql) trebuie să aibă acces de scriere în directorul părinte
long_query_time = 1 — pragul în secunde, acceptă valori zecimale (de ex., 0.5 pentru 500 ms); valoarea implicită de 10 secunde este aproape întotdeauna prea permisivă pentru aplicațiile web
log_queries_not_using_indexes — jurnalizează interogările cu scanare completă indiferent de long_query_time; combinați cu min_examined_row_limit pentru a suprima zgomotul de la tabelele mici
min_examined_row_limit — o interogare trebuie să examineze cel puțin atâtea rânduri înainte de a se califica pentru jurnalizare sub log_queries_not_using_indexes; previne ca căutările triviale pe un singur rând să polueze jurnalul
log_slow_admin_statements — captează operațiunile la nivel de schemă care blochează tabelele și sunt frecvent trecute cu vederea ca surse de latență

Adăugiri specifice MariaDB care merită activate în producție:
# MariaDB only — extended per-query statistics in the log
log_slow_verbosity      = query_plan,explain

# MariaDB only — log only 1 in every N qualifying queries (rate limiting)
log_slow_rate_limit     = 10
log_slow_verbosity = query_plan,explain adaugă planul de execuție al optimizatorului direct în fiecare intrare din jurnal, eliminând necesitatea de a rula manual EXPLAIN ulterior — o economie semnificativă de timp la diagnosticarea interogărilor care apar doar în condițiile de trafic din producție.
Pasul 3: Creați fișierul jurnal și setați permisiunile
Dacă directorul țintă nu există, creați-l și atribuiți proprietatea înainte de a reporni serviciul. Omiterea acestui pas este unul dintre cele mai frecvente motive pentru care jurnalul de interogări lente nu se activează în mod silențios.
sudo mkdir -p /var/log/mysql
sudo touch /var/log/mysql/slow-query.log
sudo chown mysql:mysql /var/log/mysql/slow-query.log
sudo chmod 640 /var/log/mysql/slow-query.log
Pe sistemele cu SELinux activ (RHEL, CentOS, AlmaLinux), contextul fișierului trebuie de asemenea setat corect:
sudo semanage fcontext -a -t mysqld_log_t "/var/log/mysql(/.*)?"
sudo restorecon -Rv /var/log/mysql
Nesetarea contextului SELinux corect determină daemonul să pornească cu succes, dar să omită în mod silențios scrierea în fișierul jurnal — un caz limită frustrant care nu produce nicio eroare evidentă în /var/log/messages.
Pasul 4: Reporniți serviciul de baze de date
Aplicați modificările de configurare repornind serviciul. Pe distribuțiile bazate pe systemd (standard pe orice server Linux modern):
# MySQL
sudo systemctl restart mysqld

# MariaDB
sudo systemctl restart mariadb
Pe sistemele mai vechi bazate pe init.d:
# MySQL
sudo service mysqld restart

# MariaDB
sudo service mariadb restart
După repornire, verificați că serviciul a pornit corect:
sudo systemctl status mysqld    # or mariadb
sudo journalctl -u mysqld -n 50 --no-pager
Orice configurare greșită în my.cnf va împiedica pornirea și va apărea în ieșirea jurnalului.
Pasul 5: Activați jurnalul de interogări lente la runtime (fără repornire)
Pentru serverele de producție unde o repornire este perturbatoare, MySQL și MariaDB suportă activarea dinamică a jurnalului de interogări lente prin SET GLOBAL. Modificările efectuate în acest mod intră în vigoare imediat, dar nu persistă după o repornire a serviciului dacă nu sunt scrise și în my.cnf.
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow-query.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 1;
SET GLOBAL min_examined_row_limit = 100;
Aceasta este abordarea corectă pentru diagnosticarea de urgență pe un sistem live — activați-l, capturați un eșantion de 15–30 de minute în timpul traficului de vârf, apoi dezactivați-l fără a atinge fișierul de configurare sau a reporni daemonul.
Pasul 6: Verificați configurația
Conectați-vă la clientul MySQL sau MariaDB:
mysql -u root -p
Apoi rulați o potrivire de tipare față de tabelul de variabile de sistem:
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
Ieșire așteptată pentru o instanță configurată corect:
+-------------------------------+-------------------------------+
| Variable_name                 | Value                         |
+-------------------------------+-------------------------------+
| slow_query_log                | ON                            |
| slow_query_log_file           | /var/log/mysql/slow-query.log |
+-------------------------------+-------------------------------+

+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| long_query_time | 1.000 |
+-----------------+-------+

+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | ON    |
+-------------------------------+-------+
Puteți confirma de asemenea că jurnalul este scris verificând contorul de interogări lente:
SHOW GLOBAL STATUS LIKE 'Slow_queries';
Acest contor se incrementează de fiecare dată când o interogare depășește long_query_time, indiferent dacă jurnalizarea în fișier este activă — util pentru a confirma că interogările lente chiar au loc înainte de a petrece timp analizând un fișier jurnal gol.
Pasul 7: Citirea și interpretarea jurnalului brut
Utilizați tail pentru a monitoriza jurnalul în timp real în timpul unui test de încărcare sau al unei ferestre de trafic de vârf:
sudo tail -f /var/log/mysql/slow-query.log
O intrare tipică în jurnal arată astfel:
# Time: 2024-10-11T12:45:23.489187Z
# User@Host: app_user[app_user] @ 10.0.1.45 []  Id: 1042
# Query_time: 4.561529  Lock_time: 0.000115  Rows_sent: 1  Rows_examined: 847293
# Bytes_sent: 512
SET timestamp=1697030723;
SELECT * FROM orders WHERE customer_email = 'user@example.com' ORDER BY created_at DESC;
Ce vă spune fiecare câmp:

Query_time — timpul total de execuție în secunde (timp de ceas)
Lock_time — timpul petrecut așteptând blocări de tabel sau rând; un raport ridicat al Lock_time față de Query_time indică contention, nu un index lipsă
Rows_sent — rândurile returnate clientului
Rows_examined — rândurile pe care motorul de stocare le-a scanat pentru a produce rezultatul; un raport Rows_examined / Rows_sent peste 100:1 este un semnal puternic al unui index lipsă sau slab selectiv
Bytes_sent — prezent în verbozitatea extinsă MariaDB; util pentru identificarea interogărilor care returnează seturi de rezultate inutil de mari

În exemplul de mai sus, interogarea a examinat 847.293 de rânduri pentru a returna 1 rând. Adăugarea unui index pe customer_email ar reduce Rows_examined la aproximativ 1, reducând timpul de execuție de la 4,5 secunde la sub milisecundă.
Pasul 8: Analizați jurnalul cu mysqldumpslow și pt-query-digest
Citirea fișierului jurnal brut este impractică la scară. Două instrumente agregează și clasifică interogările lente după impactul total.
Utilizarea mysqldumpslow (inclus cu MySQL/MariaDB)
# Top 10 queries by total execution time
sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow-query.log

# Top 10 queries by average execution time
sudo mysqldumpslow -s at -t 10 /var/log/mysql/slow-query.log

# Top 10 queries by rows examined
sudo mysqldumpslow -s r -t 10 /var/log/mysql/slow-query.log
mysqldumpslow normalizează parametrii interogărilor (înlocuind valorile literale cu N sau S) astfel încât interogările structural identice cu valori de parametri diferite să fie grupate împreună — esențial pentru identificarea tiparelor cu frecvență ridicată.
Utilizarea pt-query-digest (Percona Toolkit — Recomandat pentru producție)
# Install Percona Toolkit (Debian/Ubuntu)
sudo apt-get install percona-toolkit

# Install Percona Toolkit (RHEL/CentOS/AlmaLinux)
sudo yum install percona-toolkit

# Generate a full digest report
sudo pt-query-digest /var/log/mysql/slow-query.log

# Show only the top 5 queries by total time
sudo pt-query-digest --limit 5 /var/log/mysql/slow-query.log

# Output to a file for later review
sudo pt-query-digest /var/log/mysql/slow-query.log > /tmp/slow_query_report.txt
pt-query-digest produce un raport clasificat care arată amprenta fiecărei interogări, timpul total de execuție, timpul mediu, numărul de apeluri și distribuția percentilelor. Este semnificativ mai puternic decât mysqldumpslow și este instrumentul standard utilizat de DBA-urile profesioniste pentru analiza jurnalelor lente.
Pasul 9: Configurați rotația jurnalului cu logrotate
Fără rotație, jurnalul de interogări lente crește indefinit. Pe un server ocupat cu long_query_time setat la 1 secundă, fișierul poate ajunge la câțiva gigabytes în câteva zile.
Creați o configurație dedicată logrotate:
sudo nano /etc/logrotate.d/mysql-slow
/var/log/mysql/slow-query.log {
    daily
    rotate 14
    missingok
    notifempty
    compress
    delaycompress
    sharedscripts
    postrotate
        /usr/bin/mysqladmin flush-logs 2>/dev/null || true
    endscript
}
Directive cheie explicate:

rotate 14 — păstrează 14 zile de arhive comprimate; ajustați în funcție de bugetul de disc și cerințele de audit
compress / delaycompress — comprimă fișierele rotite cu gzip, dar întârzie compresia cu un ciclu pentru a evita comprimarea unui fișier pe care daemonul îl poate avea încă deschis
postrotate — rulează mysqladmin flush-logs după rotație, care semnalează daemonului să închidă handle-ul curent al fișierului jurnal și să deschidă unul nou; fără aceasta, MySQL/MariaDB continuă să scrie în fișierul redenumit până la următoarea repornire

Forțați o rotație manuală pentru a testa configurația:
sudo logrotate -f /etc/logrotate.d/mysql-slow
Pasul 10: Dezactivați jurnalul de interogări lente când nu mai este necesar
Jurnalizarea continuă a interogărilor lente la un prag scăzut (de ex., 0,5 secunde) pe un server cu trafic ridicat adaugă o suprasarcină I/O măsurabilă. Dezactivați-l odată ce ați colectat date suficiente:
Prin fișierul de configurare (persistent):
[mysqld]
slow_query_log = 0
Apoi reporniți serviciul:
sudo systemctl restart mysqld   # or mariadb
Prin variabilă runtime (imediată, non-persistentă):
SET GLOBAL slow_query_log = 'OFF';
Metoda runtime este preferabilă în orele de producție — intră în vigoare în milisecunde fără niciun timp de nefuncționare.
Avansat: Utilizarea performance_schema ca complement
Jurnalul de interogări lente captează interogările care depășesc un prag de timp. Tabelul performance_schema events_statements_summary_by_digest captează statistici agregate pentru fiecare tipar distinct de interogare, indiferent de timpul de execuție. Utilizarea ambelor împreună oferă o imagine completă.
SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    ROUND(SUM_TIMER_WAIT / 1e12, 3)     AS total_time_sec,
    ROUND(AVG_TIMER_WAIT / 1e12, 6)     AS avg_time_sec,
    SUM_ROWS_EXAMINED,
    SUM_ROWS_SENT
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
Această interogare evidențiază cele mai consumatoare 10 tipare de interogări din întregul istoric al instrucțiunilor — inclusiv interogările rapide care rulează de milioane de ori și domină colectiv timpul CPU, pe care jurnalul de interogări lente nu le-ar captura niciodată.
Considerații privind mediul de hosting
Pragul optim long_query_time depinde în mare măsură de rolul serverului și profilul de resurse:

Medii de hosting partajat — de obicei fără acces direct la my.cnf; utilizați SET GLOBAL dacă furnizorul de hosting acordă privilegiul SUPER sau SYSTEM_VARIABLES_ADMIN, sau solicitați acces la jurnalul lent prin panoul de control
Medii VPS — accesul root complet înseamnă control deplin asupra tuturor parametrilor de configurare; o instalare VPS cu cPanel expune setările jurnalului de interogări lente prin MySQL Configuration Editor din WHM, care scrie direct în my.cnf

Dacă stiva dvs. de aplicații include un frontend web gestionat printr-un Panou de Control VPS, corelarea marcajelor de timp din jurnalul de interogări lente cu marcajele de timp din jurnalul de acces HTTP al aplicației este o metodă eficientă pentru a urmări latența bazei de date până la cererile specifice ale utilizatorilor.

Matrice de decizie practică: Alegerea pragului potrivit

Listă de verificare tehnică și concluzii cheie

Înainte de a încheia o investigație privind interogările lente, verificați fiecare dintre următoarele:

• Secțiunea [mysqld] din my.cnf conține slow_query_log = 1, o cale validă slow_query_log_file și un long_query_time adecvat pentru profilul dvs. de trafic
• Fișierul jurnal și directorul său părinte sunt deținute de utilizatorul de sistem mysql cu permisiuni de scriere; pe sistemele SELinux, contextul fișierului este setat la mysqld_log_t

SHOW VARIABLES LIKE '%slow_query%' confirmă slow_query_log = ON și calea corectă a fișierului după repornirea serviciului
SHOW GLOBAL STATUS LIKE 'Slow_queries' arată un contor nenul și în creștere, confirmând că interogările eligibile chiar au loc
log_queries_not_using_indexes este activat și asociat cu min_examined_row_limit pentru a preveni ca căutările triviale pe un singur rând să inunde jurnalul
log_slow_admin_statements este activat pentru a captura ALTER TABLE, OPTIMIZE TABLE și operațiuni DDL similare care sunt surse frecvente de blocări neașteptate ale tabelelor
O configurație logrotate este în vigoare cu un hook postrotate care apelează mysqladmin flush-logs

Întrebări frecvente

Activarea jurnalului de interogări lente afectează performanța bazei de date?

La un prag de 1 secundă sau mai mare pe o sarcină de lucru tipică de producție, suprasarcina este neglijabilă — de obicei sub 1% din timpul total de execuție al interogărilor. Suprasarcina devine măsurabilă doar când long_query_time este setat sub 0,1 secunde sau când log_queries_not_using_indexes este activat pe o schemă cu multe tabele mici neindexate. Utilizați log_slow_rate_limit (MariaDB) sau ridicați min_examined_row_limit pentru a atenua acest lucru.

Pot activa jurnalul de interogări lente fără a reporni MySQL sau MariaDB?

Da. Utilizați SET GLOBAL slow_query_log = 'ON' și SET GLOBAL long_query_time = 1 din orice sesiune client MySQL cu privilegiul SUPER sau SYSTEM_VARIABLES_ADMIN. Modificarea intră în vigoare imediat. Scrieți aceleași valori în my.cnf pentru a le face persistente după reporniri.

Care este diferența dintre Query_time și Lock_time în jurnalul de interogări lente?

Query_time este timpul total scurs (timp de ceas) de când serverul a primit interogarea până când a trimis ultimul rând clientului. Lock_time este porțiunea din acel total petrecută așteptând să obțină blocări de tabel sau rând. O interogare cu Lock_time aproape de Query_time este o problemă de contention la blocare, nu o problemă de index — soluția implică proiectarea tranzacțiilor sau reducerea domeniului de blocare, nu adăugarea de indexuri.

De ce fișierul meu jurnal de interogări lente este gol chiar dacă slow_query_log = ON?

Cele mai frecvente cauze sunt: (1) nicio interogare nu a depășit efectiv long_query_time încă — verificați cu SHOW GLOBAL STATUS LIKE 'Slow_queries'; (2) calea fișierului jurnal nu există sau utilizatorul mysql nu are permisiune de scriere; (3) pe sistemele SELinux, contextul fișierului este incorect; (4) variabila slow_query_log_file indică o cale diferită față de fișierul pe care îl inspectați — confirmați cu SHOW VARIABLES LIKE 'slow_query_log_file'.

Cum găsesc cea mai dăunătoare interogare din jurnalul de interogări lente?

Rulați pt-query-digest și sortați după R/Call (rânduri examinate per apel) sau Response time (timp cumulativ total). Interogarea din vârful clasamentului Response time consumă cel mai mult timp agregat al bazei de date și ar trebui să fie prima țintă pentru analiza EXPLAIN și optimizarea indexurilor. Dacă pt-query-digest nu este disponibil, utilizați mysqldumpslow -s t -t 1 pentru a extrage interogarea cu cel mai mare timp total.