Jak włączyć dziennik powolnych zapytań w MySQL lub MariaDB?

W miarę jak bazy danych rosną i stają się podstawowym składnikiem aplikacji internetowych, strojenie wydajności staje się krytyczne. W MySQL® i MariaDB jednym z najskuteczniejszych sposobów diagnozowania wąskich gardeł wydajności jest włączenie dziennika powolnych zapytań. Dziennik ten pomaga zidentyfikować zapytania, które wykonują się zbyt długo, umożliwiając ich optymalizację i poprawę ogólnej wydajności bazy danych.

W tym przewodniku wyjaśnimy, czym jest dziennik powolnych zapytań, dlaczego jest ważny oraz jak go włączyć i skonfigurować w MySQL i MariaDB.

Czym jest dziennik powolnych zapytań?

Dziennik powolnych zapytań to plik dziennika, który rejestruje zapytania SQL, których wykonanie zajmuje więcej niż określony czas (próg). Domyślnie próg ten jest ustawiony na 10 sekund, ale można go dostosować. Zapytania, które przekraczają ten limit czasu, są rejestrowane wraz z informacjami, takimi jak czas trwania zapytania oraz data i godzina wykonania. Jest to niezbędne narzędzie dla programistów i administratorów baz danych do identyfikacji i optymalizacji wolno działających zapytań, pomagając poprawić wydajność bazy danych.

Dlaczego warto włączyć dziennik powolnych zapytań?

Włączenie dziennika powolnych zapytań pomaga:

Identyfikować wąskie gardła wydajności: Wolne zapytania mogą znacząco wpływać na wydajność aplikacji. Dziennik pomaga zidentyfikować te problematyczne zapytania.
Optymalizację: Analizując powolne zapytania, można zidentyfikować obszary, w których indeksy, restrukturyzacja zapytań lub buforowanie mogą poprawić wydajność.
Monitorowanie wydajności zapytań: W celu bieżącego dostrajania wydajności, dziennik powolnych zapytań zapewnia wgląd w to, jak czasy wykonywania zapytań zmieniają się w czasie.

Jak włączyć dziennik powolnych zapytań w MySQL lub MariaDB

Włączenie dziennika powolnych zapytań wymaga modyfikacji pliku konfiguracyjnego i dostosowania niektórych ustawień bezpośrednio w instancji MySQL/MariaDB. Oto jak to zrobić krok po kroku.

Krok 1: Dostęp do pliku konfiguracyjnego MySQL/MariaDB

Pliki konfiguracyjne MySQL i MariaDB znajdują się zazwyczaj pod adresem:

Dla MySQL: /etc/my.cnf lub /etc/mysql/my.cnf
Dla MariaDB: /etc/my.cnf.d/server.cnf lub /etc/mysql/mariadb.cnf

Użyj preferowanego edytora tekstu, aby otworzyć plik konfiguracyjny. Na przykład za pomocą nano można uruchomić następujące polecenie:

sudo nano /etc/my.cnf

Krok 2: Modyfikacja pliku konfiguracyjnego

W pliku konfiguracyjnym znajdź [mysqld] section. Jeśli nie istnieje, możesz ją utworzyć. Dodaj lub zmodyfikuj następujące wiersze, aby włączyć dziennik powolnych zapytań:

[mysqld]
 slow_query_log = 1
 slow_query_log_file = /var/log/mysql/slow-query.log
 long_query_time = 2

slow_query_log = 1: Włącza dziennik powolnych zapytań.
slow_query_log_file: Określa lokalizację, w której zostanie zapisany plik dziennika powolnych zapytań. Możesz ustawić niestandardową ścieżkę i nazwę pliku, ale upewnij się, że katalog ma odpowiednie uprawnienia do zapisu dla MySQL/MariaDB.
long_query_time = 2: Ustawia próg dla tego, co kwalifikuje się jako “powolne” zapytanie. W tym przypadku, każde zapytanie trwające dłużej niż 2 sekundy będzie rejestrowane. Możesz dostosować tę wartość w zależności od potrzeb wydajnościowych.

Krok 3: Uruchom ponownie usługę MySQL/MariaDB

Po zapisaniu zmian w pliku konfiguracyjnym należy ponownie uruchomić usługę MySQL lub MariaDB, aby zmiany zaczęły obowiązywać.

Dla systemów korzystających z systemd:

sudo systemctl restart mysqld # For MySQL
 sudo systemctl restart mariadb # For MariaDB

Dla systemów używających init.d:

sudo service mysqld restart # For MySQL
 sudo service mariadb restart # For MariaDB

Krok 4: Sprawdzenie, czy dziennik powolnych zapytań jest włączony

Aby zweryfikować, czy dziennik powolnych zapytań jest włączony, zaloguj się do klienta MySQL/MariaDB, uruchamiając:

mysql -u root -p

Po zalogowaniu uruchom następujące polecenie:

SHOW VARIABLES LIKE 'slow_query_log';

Powinieneś zobaczyć coś takiego:

+-----------------+-------+
 | Variable_name   | Value |
 +-----------------+-------+
 | slow_query_log  | ON    |
 +-----------------+-------+

Potwierdza to, że dziennik powolnych zapytań jest włączony.

Krok 5: Analiza dziennika powolnych zapytań

Dziennik powolnych zapytań zapisuje zapytania w pliku określonym w konfiguracji. Aby wyświetlić zawartość dziennika powolnych zapytań, można użyć przeglądarki tekstu, takiej jak less, cat lub tail:

sudo less /var/log/mysql/slow-query.log

Zobaczysz wpisy podobne do poniższych:

# Time: 2024-10-11T12:45:23.489187Z
 # User@Host: root[root] @ localhost []
 # Query_time: 4.561529 Lock_time: 0.000115 Rows_sent: 1 Rows_examined: 50000
 SET timestamp=1697030723;
 SELECT * FROM large_table WHERE column = 'value';

Dziennik zawiera szczegółowe informacje, takie jak czas wykonania zapytania, czas blokady, liczba wysłanych i sprawdzonych wierszy oraz rzeczywiste zapytanie SQL.

Opcjonalna konfiguracja dziennika powolnych zapytań

Istnieją dodatkowe opcje konfiguracyjne, które można ustawić, aby dostosować dziennik powolnych zapytań do konkretnych potrzeb.

1. Dziennik zapytań bez indeksów

Można również włączyć rejestrowanie zapytań, które nie korzystają z indeksów, które często są źródłem problemów z wydajnością. Dodaj następującą linię do konfiguracji MySQL/MariaDB:

log_queries_not_using_indexes = 1

Spowoduje to rejestrowanie każdego zapytania, które nie używa indeksu, pomagając zidentyfikować i zoptymalizować słabo działające zapytania.

2. Ograniczenie rozmiaru pliku dziennika

Aby zapobiec zbyt dużemu wzrostowi dziennika powolnych zapytań, można rotować dzienniki lub ograniczyć ich rozmiar. W systemach Linux można użyć logrotate do zarządzania rozmiarem i rotacją plików dziennika MySQL/MariaDB.

Oto podstawowy przykład dla /etc/logrotate.d/mysql:

/var/log/mysql/slow-query.log {
 daily
 rotate 7
 missingok
 notifempty
 compress
 delaycompress
 postrotate
 /usr/bin/systemctl reload mysql >/dev/null 2>&1 || true
 endscript
 }

Ta konfiguracja obraca plik dziennika codziennie i zachowuje siedem ostatnich dzienników, zapewniając, że powolny dziennik zapytań nie zajmuje zbyt dużo miejsca na dysku.

Krok 6: Wyłączenie dziennika powolnych zapytań

Po zidentyfikowaniu i zoptymalizowaniu powolnych zapytań, możesz wyłączyć dziennik powolnych zapytań, aby oszczędzać zasoby. W tym celu należy ponownie edytować plik konfiguracyjny i ustawić wartość slow_query_log na 0:

[mysqld]
 slow_query_log = 0

Uruchom ponownie usługę MySQL lub MariaDB, aby zastosować zmiany.

Wnioski

Dziennik powolnych zapytań jest nieocenionym narzędziem do dostrajania wydajności bazy danych w MySQL i MariaDB. Rejestrując zapytania, których wykonanie trwa zbyt długo, można zidentyfikować wąskie gardła i zoptymalizować wydajność zapytań. Niezależnie od tego, czy zarządzasz małą bazą danych, czy dużą aplikacją korporacyjną, włączenie dziennika powolnych zapytań jest proaktywnym sposobem na zapewnienie szybkości i wydajności baz danych.

Wykonując kroki opisane w tym przewodniku, powinieneś być w stanie włączyć, skonfigurować i przeanalizować dziennik powolnych zapytań, aby skutecznie zoptymalizować wydajność bazy danych MySQL lub MariaDB.