Jak włączyć dziennik wolnych zapytań w MySQL i MariaDB

Dziennik wolnych zapytań (slow query log) to wbudowana funkcja diagnostyczna MySQL i MariaDB, która rejestruje każde polecenie SQL, którego czas wykonania przekracza konfigurowalny próg. Przechwytuje czas trwania zapytania, czas blokady, liczbę sprawdzonych wierszy, liczbę wysłanych wierszy oraz pełny tekst SQL — zapewniając administratorom baz danych i programistom precyzyjny, oparty na plikach ślad audytu każdego zapytania, które obniża wydajność aplikacji.

Włączenie go jest jedną z najbardziej efektywnych czynności, jakie można podjąć podczas strojenia wydajności bazy danych. W przeciwieństwie do ogólnych narzędzi monitorujących, dziennik wolnych zapytań wskazuje dokładne polecenia odpowiedzialne za opóźnienia, co czyni go niezbędnym do optymalizacji indeksów, restrukturyzacji zapytań i planowania pojemności na każdym serwerze — od środowiska VPS Hosting z jednym najemcą po wielowęzłowy dedykowany klaster baz danych.

Dlaczego dziennik wolnych zapytań ma znaczenie wykraczające poza podstawowy monitoring

Większość zespołów sięga po EXPLAIN lub SHOW PROCESSLIST reaktywnie, po tym jak użytkownicy zgłaszają spowolnienia. Dziennik wolnych zapytań działa proaktywnie: gromadzi dowody przez godziny lub dni rzeczywistego ruchu, przechwytując sporadycznych winowajców, którzy nigdy nie pojawiają się podczas ręcznego okna inspekcji.

Kluczowe korzyści operacyjne obejmują:

• Izolacja wąskich gardeł — odróżnia pełne skany tabel obciążające CPU od problemów z rywalizacją o blokady przy użyciu współczynników Query_time vs. Lock_time
• Analiza luk w indeksach — flaga log_queries_not_using_indexes ujawnia każde zapytanie wykonujące pełny skan, niezależnie od jego surowego czasu wykonania
• Wykrywanie regresji — porównanie migawek dziennika przed i po wdrożeniu ujawnia, czy nowy kod wprowadził wolniejsze wzorce zapytań
• Dowody do planowania pojemności — wartości Rows_examined o rzędy wielkości wyższe niż Rows_sent wskazują na brakujące lub nieprawidłowo używane indeksy, które nawarstwiają się pod obciążeniem

MySQL vs. MariaDB: porównanie funkcji dziennika wolnych zapytań

Oba silniki współdzielą tę samą podstawową infrastrukturę dziennika wolnych zapytań odziedziczoną po MySQL 5.1, ale MariaDB rozszerzyła ją w kilku istotnych obszarach.

Opcje log_slow_verbosity i log_slow_rate_limit w MariaDB są szczególnie cenne w środowiskach produkcyjnych o wysokiej przepustowości, gdzie samo rejestrowanie każdego wolnego zapytania stałoby się obciążeniem wydajnościowym.

Krok 1: Zlokalizuj plik konfiguracyjny

MySQL i MariaDB odczytują swoją konfigurację z różnych domyślnych ścieżek w zależności od dystrybucji i metody instalacji.

MySQL:

/etc/my.cnf (oparte na RPM: RHEL, CentOS, AlmaLinux, Rocky Linux)
/etc/mysql/my.cnf (Debian/Ubuntu)
/etc/mysql/mysql.conf.d/mysqld.cnf (Ubuntu z pakietem mysql-server)

MariaDB:

/etc/my.cnf.d/server.cnf (oparte na RPM)
/etc/mysql/mariadb.conf.d/50-server.cnf (Debian/Ubuntu)
/etc/mysql/mariadb.cnf (starsze układy Debian)

Jeśli nie masz pewności, który plik jest aktywny, zapytaj działający proces:
mysqld --verbose --help 2>/dev/null | grep -A1 "Default options"
Wyświetla to dokładną, uporządkowaną listę plików odczytywanych przez demona podczas uruchamiania, w tym wszelkie katalogi !includedir.
Otwórz główny plik konfiguracyjny w preferowanym edytorze:
sudo nano /etc/my.cnf
Krok 2: Dodaj dyrektywy dziennika wolnych zapytań do [mysqld]
Wszystkie parametry dziennika wolnych zapytań należą do sekcji [mysqld]. Jeśli sekcja nie istnieje, utwórz ją na początku pliku.
[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
Opis parametrów:

slow_query_log = 1 — aktywuje funkcję; ustaw na 0, aby wyłączyć bez usuwania bloku
slow_query_log_file — bezwzględna ścieżka do pliku dziennika; użytkownik procesu MySQL/MariaDB (mysql) musi mieć dostęp do zapisu w katalogu nadrzędnym
long_query_time = 1 — próg w sekundach, akceptuje wartości dziesiętne (np. 0.5 dla 500 ms); domyślna wartość 10 sekund jest prawie zawsze zbyt permisywna dla aplikacji webowych
log_queries_not_using_indexes — rejestruje zapytania z pełnym skanem niezależnie od long_query_time; połącz z min_examined_row_limit, aby wyeliminować szum z małych tabel
min_examined_row_limit — zapytanie musi sprawdzić co najmniej tyle wierszy, aby kwalifikować się do rejestrowania w ramach log_queries_not_using_indexes; zapobiega zaśmiecaniu dziennika przez trywialne wyszukiwania pojedynczych wierszy
log_slow_admin_statements — przechwytuje operacje na poziomie schematu, które blokują tabele i są często pomijane jako źródła opóźnień

Dodatki specyficzne dla MariaDB warte włączenia w środowisku produkcyjnym:
# 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 dołącza plan wykonania optymalizatora bezpośrednio do każdego wpisu dziennika, eliminując potrzebę ręcznego ponownego uruchamiania EXPLAIN po fakcie — znaczna oszczędność czasu przy diagnozowaniu zapytań, które pojawiają się tylko pod produkcyjnymi wzorcami obciążenia.
Krok 3: Utwórz plik dziennika i ustaw uprawnienia
Jeśli docelowy katalog nie istnieje, utwórz go i przypisz własność przed ponownym uruchomieniem usługi. Pominięcie tego kroku jest jednym z najczęstszych powodów, dla których dziennik wolnych zapytań po cichu nie aktywuje się.
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
W systemach z wymuszonym SELinux (RHEL, CentOS, AlmaLinux) kontekst pliku musi być również ustawiony poprawnie:
sudo semanage fcontext -a -t mysqld_log_t "/var/log/mysql(/.*)?"
sudo restorecon -Rv /var/log/mysql
Brak ustawienia prawidłowego kontekstu SELinux powoduje, że demon uruchamia się pomyślnie, ale po cichu pomija zapisywanie do pliku dziennika — frustrujący przypadek brzegowy, który nie generuje żadnego oczywistego błędu w /var/log/messages.
Krok 4: Uruchom ponownie usługę bazy danych
Zastosuj zmiany konfiguracji, ponownie uruchamiając usługę. W dystrybucjach opartych na systemd (standard na każdym nowoczesnym serwerze Linux):
# MySQL
sudo systemctl restart mysqld

# MariaDB
sudo systemctl restart mariadb
W starszych systemach opartych na init.d:
# MySQL
sudo service mysqld restart

# MariaDB
sudo service mariadb restart
Po ponownym uruchomieniu sprawdź, czy usługa uruchomiła się poprawnie:
sudo systemctl status mysqld    # or mariadb
sudo journalctl -u mysqld -n 50 --no-pager
Każda błędna konfiguracja w my.cnf uniemożliwi uruchomienie i pojawi się w danych wyjściowych dziennika.
Krok 5: Włącz dziennik wolnych zapytań w czasie działania (bez ponownego uruchomienia)
W przypadku serwerów produkcyjnych, gdzie ponowne uruchomienie jest uciążliwe, MySQL i MariaDB obsługują dynamiczne włączanie dziennika wolnych zapytań za pomocą SET GLOBAL. Zmiany wprowadzone w ten sposób wchodzą w życie natychmiast, ale nie są trwałe po ponownym uruchomieniu usługi, chyba że zostaną również zapisane w 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;
Jest to właściwe podejście do awaryjnej diagnostyki na działającym systemie — włącz go, zbierz próbkę przez 15–30 minut podczas szczytowego ruchu, a następnie wyłącz bez dotykania pliku konfiguracyjnego lub ponownego uruchamiania demona.
Krok 6: Zweryfikuj konfigurację
Połącz się z klientem MySQL lub MariaDB:
mysql -u root -p
Następnie uruchom dopasowanie wzorca względem tabeli zmiennych systemowych:
SHOW VARIABLES LIKE '%slow_query%';
SHOW VARIABLES LIKE 'long_query_time';
SHOW VARIABLES LIKE 'log_queries_not_using_indexes';
Oczekiwane dane wyjściowe dla poprawnie skonfigurowanej instancji:
+-------------------------------+-------------------------------+
| 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    |
+-------------------------------+-------+
Możesz również potwierdzić, że dziennik jest zapisywany, sprawdzając licznik wolnych zapytań:
SHOW GLOBAL STATUS LIKE 'Slow_queries';
Ten licznik zwiększa się za każdym razem, gdy zapytanie przekracza long_query_time, niezależnie od tego, czy rejestrowanie do pliku jest aktywne — przydatne do potwierdzenia, że wolne zapytania rzeczywiście występują, zanim poświęcisz czas na analizę pustego pliku dziennika.
Krok 7: Odczytywanie i interpretowanie surowego dziennika
Użyj tail, aby monitorować dziennik w czasie rzeczywistym podczas testu obciążenia lub okna szczytowego ruchu:
sudo tail -f /var/log/mysql/slow-query.log
Typowy wpis dziennika wygląda następująco:
# 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;
Co mówi każde pole:

Query_time — całkowity czas wykonania (czas zegarowy) w sekundach
Lock_time — czas spędzony na oczekiwaniu na blokady tabel lub wierszy; wysoki współczynnik Lock_time do Query_time wskazuje na rywalizację, a nie brakujący indeks
Rows_sent — wiersze zwrócone do klienta
Rows_examined — wiersze przeskanowane przez silnik pamięci masowej w celu uzyskania wyniku; współczynnik Rows_examined / Rows_sent powyżej 100:1 jest silnym sygnałem brakującego lub słabo selektywnego indeksu
Bytes_sent — obecny w rozszerzonej szczegółowości MariaDB; przydatny do identyfikowania zapytań zwracających niepotrzebnie duże zestawy wyników

W powyższym przykładzie zapytanie sprawdziło 847 293 wiersze, aby zwrócić 1 wiersz. Dodanie indeksu na customer_email zmniejszyłoby Rows_examined do około 1, skracając czas wykonania z 4,5 sekundy do poniżej milisekundy.
Krok 8: Analizuj dziennik za pomocą mysqldumpslow i pt-query-digest
Odczytywanie surowego pliku dziennika jest niepraktyczne na dużą skalę. Dwa narzędzia agregują i klasyfikują wolne zapytania według całkowitego wpływu.
Używanie mysqldumpslow (dołączone do 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 normalizuje parametry zapytań (zastępując wartości literalne przez N lub S), dzięki czemu strukturalnie identyczne zapytania z różnymi wartościami parametrów są grupowane razem — niezbędne do identyfikowania wzorców o wysokiej częstotliwości.
Używanie pt-query-digest (Percona Toolkit — zalecane dla środowisk produkcyjnych)
# 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 generuje uszeregowany raport pokazujący odcisk palca każdego zapytania, całkowity czas wykonania, średni czas, liczbę wywołań i rozkład percentylowy. Jest znacznie potężniejszy niż mysqldumpslow i jest standardowym narzędziem używanym przez profesjonalnych administratorów baz danych do analizy dziennika wolnych zapytań.
Krok 9: Skonfiguruj rotację dziennika za pomocą logrotate
Bez rotacji dziennik wolnych zapytań rośnie w nieskończoność. Na zajętym serwerze z long_query_time ustawionym na 1 sekundę plik może osiągnąć kilka gigabajtów w ciągu kilku dni.
Utwórz dedykowaną konfigurację 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
}
Wyjaśnienie kluczowych dyrektyw:

rotate 14 — zachowuje 14 dni skompresowanych archiwów; dostosuj w zależności od budżetu dyskowego i wymagań audytu
compress / delaycompress — kompresuje rotowane pliki za pomocą gzip, ale opóźnia kompresję o jeden cykl, aby uniknąć kompresowania pliku, który demon może nadal mieć otwarty
postrotate — uruchamia mysqladmin flush-logs po rotacji, co sygnalizuje demonowi zamknięcie bieżącego uchwytu pliku dziennika i otwarcie nowego; bez tego MySQL/MariaDB kontynuuje zapisywanie do przemianowanego pliku aż do następnego ponownego uruchomienia

Wymuś ręczną rotację, aby przetestować konfigurację:
sudo logrotate -f /etc/logrotate.d/mysql-slow
Krok 10: Wyłącz dziennik wolnych zapytań, gdy nie jest już potrzebny
Ciągłe rejestrowanie wolnych zapytań przy niskim progu (np. 0,5 sekundy) na serwerze o dużym ruchu dodaje mierzalne obciążenie I/O. Wyłącz je po zebraniu wystarczających danych:
Przez plik konfiguracyjny (trwałe):
[mysqld]
slow_query_log = 0
Następnie uruchom ponownie usługę:
sudo systemctl restart mysqld   # or mariadb
Przez zmienną środowiskową (natychmiastowe, nietrwałe):
SET GLOBAL slow_query_log = 'OFF';
Metoda środowiskowa jest preferowana w godzinach produkcyjnych — wchodzi w życie w milisekundach bez żadnych przestojów.
Zaawansowane: używanie performance_schema jako uzupełnienia
Dziennik wolnych zapytań przechwytuje zapytania przekraczające próg czasowy. Tabela performance_schema events_statements_summary_by_digest przechwytuje zagregowane statystyki dla każdego odrębnego wzorca zapytania, niezależnie od czasu wykonania. Używanie obu razem daje pełny obraz.
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;
To zapytanie ujawnia 10 najbardziej czasochłonnych wzorców zapytań w całej historii poleceń — w tym szybkie zapytania uruchamiane miliony razy, które łącznie dominują w czasie CPU, a których dziennik wolnych zapytań nigdy by nie przechwycił.
Uwagi dotyczące środowiska hostingowego
Optymalny próg long_query_time zależy w dużej mierze od roli serwera i profilu zasobów:

Środowiska hostingu współdzielonego — zazwyczaj brak bezpośredniego dostępu do my.cnf; użyj SET GLOBAL, jeśli dostawca hostingu przyznaje uprawnienie SUPER lub SYSTEM_VARIABLES_ADMIN, lub poproś o dostęp do dziennika wolnych zapytań przez panel sterowania
Środowiska VPS — pełny dostęp root oznacza pełną kontrolę nad wszystkimi parametrami konfiguracyjnymi; instalacja VPS z cPanel udostępnia ustawienia dziennika wolnych zapytań przez Edytor konfiguracji MySQL w WHM, który zapisuje bezpośrednio do my.cnf

Jeśli Twój stos aplikacji zawiera frontend webowy zarządzany przez Panel Sterowania VPS, korelowanie znaczników czasu dziennika wolnych zapytań ze znacznikami czasu dziennika dostępu HTTP aplikacji jest skuteczną metodą śledzenia opóźnień bazy danych z powrotem do konkretnych żądań użytkowników.

Praktyczna macierz decyzyjna: wybór właściwego progu

Lista kontrolna techniczna i kluczowe wnioski

Przed zakończeniem dochodzenia w sprawie wolnych zapytań zweryfikuj każdy z poniższych punktów:

• Sekcja [mysqld] w my.cnf zawiera slow_query_log = 1, prawidłową ścieżkę slow_query_log_file i long_query_time odpowiednie dla Twojego profilu ruchu
• Plik dziennika i jego katalog nadrzędny są własnością użytkownika systemowego mysql z uprawnieniami do zapisu; w systemach SELinux kontekst pliku jest ustawiony na mysqld_log_t

SHOW VARIABLES LIKE '%slow_query%' potwierdza slow_query_log = ON i prawidłową ścieżkę pliku po ponownym uruchomieniu usługi
SHOW GLOBAL STATUS LIKE 'Slow_queries' pokazuje niezerowy i rosnący licznik, potwierdzając, że kwalifikujące się zapytania rzeczywiście występują
log_queries_not_using_indexes jest włączone i sparowane z min_examined_row_limit, aby zapobiec zalewaniu dziennika przez trywialne wyszukiwania pojedynczych wierszy
log_slow_admin_statements jest włączone, aby przechwytywać ALTER TABLE, OPTIMIZE TABLE i podobne operacje DDL, które są częstymi źródłami nieoczekiwanych blokad tabel
Konfiguracja logrotate jest na miejscu z hookiem postrotate wywołującym mysqladmin flush-logs

FAQ

Czy włączenie dziennika wolnych zapytań wpływa na wydajność bazy danych?

Przy progu 1 sekundy lub wyższym przy typowym obciążeniu produkcyjnym narzut jest pomijalny — zazwyczaj poniżej 1% całkowitego czasu wykonania zapytań. Narzut staje się mierzalny tylko wtedy, gdy long_query_time jest ustawiony poniżej 0,1 sekundy lub gdy log_queries_not_using_indexes jest włączone w schemacie z wieloma małymi, nieindeksowanymi tabelami. Użyj log_slow_rate_limit (MariaDB) lub podnieś min_examined_row_limit, aby to złagodzić.

Czy mogę włączyć dziennik wolnych zapytań bez ponownego uruchamiania MySQL lub MariaDB?

Tak. Użyj SET GLOBAL slow_query_log = 'ON' i SET GLOBAL long_query_time = 1 z dowolnej sesji klienta MySQL z uprawnieniem SUPER lub SYSTEM_VARIABLES_ADMIN. Zmiana wchodzi w życie natychmiast. Zapisz te same wartości do my.cnf, aby były trwałe po ponownym uruchomieniu.

Jaka jest różnica między Query_time a Lock_time w dzienniku wolnych zapytań?

Query_time to całkowity upływający czas zegarowy od momentu otrzymania zapytania przez serwer do wysłania ostatniego wiersza do klienta. Lock_time to część tego czasu spędzona na oczekiwaniu na uzyskanie blokad tabel lub wierszy. Zapytanie z Lock_time zbliżonym do Query_time jest problemem rywalizacji o blokady, a nie problemem z indeksem — rozwiązanie polega na projektowaniu transakcji lub zmniejszeniu zakresu blokad, a nie na dodawaniu indeksów.

Dlaczego mój plik dziennika wolnych zapytań jest pusty, mimo że slow_query_log = ON?

Najczęstsze przyczyny to: (1) żadne zapytania faktycznie nie przekroczyły jeszcze long_query_time — zweryfikuj za pomocą SHOW GLOBAL STATUS LIKE 'Slow_queries'; (2) ścieżka pliku dziennika nie istnieje lub użytkownik mysql nie ma uprawnień do zapisu; (3) w systemach SELinux kontekst pliku jest nieprawidłowy; (4) zmienna slow_query_log_file wskazuje na inną ścieżkę niż plik, który sprawdzasz — potwierdź za pomocą SHOW VARIABLES LIKE 'slow_query_log_file'.

Jak znaleźć najbardziej szkodliwe zapytanie w dzienniku wolnych zapytań?

Uruchom pt-query-digest i sortuj według R/Call (sprawdzonych wierszy na wywołanie) lub Response time (całkowity skumulowany czas). Zapytanie na szczycie rankingu Response time zużywa najwięcej zagregowanego czasu bazy danych i powinno być pierwszym celem analizy EXPLAIN i optymalizacji indeksów. Jeśli pt-query-digest nie jest dostępny, użyj mysqldumpslow -s t -t 1, aby wyodrębnić pojedyncze zapytanie o najwyższym całkowitym czasie.