So aktivieren Sie das Protokoll für langsame Abfragen in MySQL oder MariaDB

Wenn Datenbanken wachsen und zu einer Kernkomponente von Webanwendungen werden, wird die Leistungsoptimierung immer wichtiger. In MySQL® und MariaDB besteht eine der effektivsten Möglichkeiten zur Diagnose von Leistungsengpässen darin, das Slow Query Log zu aktivieren . Dieses Protokoll hilft bei der Identifizierung von Abfragen, deren Ausführung zu lange dauert, und ermöglicht es Ihnen, diese zu optimieren und die Gesamtleistung Ihrer Datenbank zu verbessern.

In diesem Leitfaden wird erklärt, was das Slow Query Log ist, warum es wichtig ist und wie Sie es in MySQL und MariaDB aktivieren und konfigurieren.

Was ist das Slow Query Log?

Das Slow Query Log ist eine Protokolldatei, die SQL-Abfragen aufzeichnet, deren Ausführung länger als eine bestimmte Zeit (der Schwellenwert) dauert. Standardmäßig ist dieser Schwellenwert auf 10 Sekunden festgelegt, er kann jedoch angepasst werden. Abfragen, die diese Zeitgrenze überschreiten, werden zusammen mit Informationen wie der Dauer der Abfrage sowie Datum und Uhrzeit der Ausführung protokolliert. Dies ist ein wichtiges Werkzeug für Entwickler und Datenbankadministratoren, um langsam laufende Abfragen zu identifizieren und zu optimieren und so die Datenbankleistung zu verbessern.

Warum sollte das Protokoll für langsame Abfragen aktiviert werden?

Das Aktivieren des Protokolls für langsame Abfragen hilft:

Identifizierung von Leistungsengpässen: Langsame Abfragen können die Leistung Ihrer Anwendung erheblich beeinträchtigen. Das Protokoll hilft Ihnen, diese problematischen Abfragen zu identifizieren.
Optimieren: Durch die Untersuchung der langsamen Abfragen können Sie Bereiche identifizieren, in denen Indizes, Abfrageumstrukturierungen oder Caching die Leistung verbessern könnten.
Abfrageleistung überwachen: Für die laufende Leistungsoptimierung bietet das Protokoll langsamer Abfragen Einblicke in die Entwicklung der Ausführungszeiten von Abfragen im Laufe der Zeit.

So aktivieren Sie das Protokoll für langsame Abfragen in MySQL oder MariaDB

Um das Slow Query Log zu aktivieren, müssen Sie die Konfigurationsdatei modifizieren und einige Einstellungen direkt in der MySQL/MariaDB-Instanz vornehmen. Im Folgenden wird Schritt für Schritt beschrieben, wie Sie vorgehen.

Schritt 1: Zugriff auf die MySQL/MariaDB-Konfigurationsdatei

Die MySQL- und MariaDB-Konfigurationsdateien befinden sich normalerweise unter:

Für MySQL: /etc/my.cnf oder /etc/mysql/my.cnf
Für MariaDB: /etc/my.cnf.d/server.cnf oder /etc/mysql/mariadb.cnf

Verwenden Sie Ihren bevorzugten Texteditor, um die Konfigurationsdatei zu öffnen. Mit nano können Sie zum Beispiel den folgenden Befehl ausführen:

sudo nano /etc/my.cnf

Schritt 2: Ändern Sie die Konfigurationsdatei

Suchen Sie in der Konfigurationsdatei den Eintrag [mysqld] abschnitt. Wenn er nicht vorhanden ist, können Sie ihn erstellen. Fügen Sie die folgenden Zeilen hinzu oder ändern Sie sie, um das langsame Abfrageprotokoll zu aktivieren:

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

slow_query_log = 1: Aktiviert das langsame Abfrageprotokoll.
slow_query_log_file: Gibt den Ort an, an dem die Slow-Query-Log-Datei gespeichert werden soll. Sie können einen benutzerdefinierten Pfad und Dateinamen angeben, aber stellen Sie sicher, dass das Verzeichnis die richtigen Schreibrechte für MySQL/MariaDB hat.
long_query_time = 2: Legt den Schwellenwert fest, ab dem eine Abfrage als “langsam” gilt. In diesem Fall wird jede Abfrage, die länger als 2 Sekunden dauert, protokolliert. Sie können diesen Wert je nach Ihren Leistungsanforderungen anpassen.

Schritt 3: Starten Sie den MySQL/MariaDB-Dienst neu

Nachdem Sie die Änderungen in der Konfigurationsdatei gespeichert haben, starten Sie den MySQL- oder MariaDB-Dienst neu, damit die Änderungen wirksam werden.

Für Systeme, die systemd verwenden:

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

Für Systeme, die init.d verwenden:

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

Schritt 4: Überprüfen, ob das Slow Query Log aktiviert ist

Um zu überprüfen, ob das Protokoll für langsame Abfragen aktiviert ist, melden Sie sich am MySQL/MariaDB-Client an, indem Sie Folgendes ausführen

mysql -u root -p

Sobald Sie angemeldet sind, führen Sie den folgenden Befehl aus:

SHOW VARIABLES LIKE 'slow_query_log';

Sie sollten etwa so etwas sehen:

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

Dies bestätigt, dass das Protokoll für langsame Abfragen aktiviert ist.

Schritt 5: Analysieren des Protokolls für langsame Abfragen

Das Slow Query Log zeichnet Abfragen in der in der Konfiguration angegebenen Datei auf. Um den Inhalt des Slow Query Logs zu betrachten, können Sie einen Text-Viewer wie less, cat oder tail verwenden:

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

Sie werden Einträge ähnlich den folgenden sehen:

# 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';

Das Protokoll enthält Details wie die Ausführungszeit der Abfrage, die Sperrzeit, die Anzahl der gesendeten und untersuchten Zeilen sowie die eigentliche SQL-Abfrage.

Optionale Konfiguration für das Slow Query Log

Es gibt zusätzliche Konfigurationsoptionen, die Sie einstellen können, um das Protokoll für langsame Abfragen an Ihre speziellen Anforderungen anzupassen.

1. Abfragen ohne Indizes protokollieren

Sie können die Protokollierung auch für Abfragen aktivieren, die keine Indizes verwenden, die oft eine Quelle von Leistungsproblemen sind. Fügen Sie die folgende Zeile zu Ihrer MySQL/MariaDB-Konfiguration hinzu:

log_queries_not_using_indexes = 1

Dies protokolliert alle Abfragen, die keinen Index verwenden, und hilft Ihnen, schlecht funktionierende Abfragen zu identifizieren und zu optimieren.

2. Größe der Protokolldatei begrenzen

Um zu verhindern, dass das langsame Abfrageprotokoll zu groß wird, können Sie die Protokolle rotieren oder ihre Größe begrenzen. Auf Linux-Systemen können Sie logrotate verwenden, um die Größe und Rotation der MySQL/MariaDB-Protokolldateien zu verwalten.

Hier ist ein einfaches Beispiel für /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
 }

Diese Konfiguration rotiert die Protokolldatei täglich und behält die letzten sieben Protokolle, um sicherzustellen, dass das langsame Abfrageprotokoll nicht zu viel Speicherplatz verbraucht.

Schritt 6: Deaktivieren des langsamen Abfrageprotokolls

Wenn Sie Ihre langsamen Abfragen identifiziert und optimiert haben, können Sie das Protokoll für langsame Abfragen deaktivieren, um Ressourcen zu sparen. Bearbeiten Sie dazu die Konfigurationsdatei erneut und setzen Sie slow_query_log auf 0:

[mysqld]
 slow_query_log = 0

Starten Sie den MySQL- oder MariaDB-Dienst neu, um die Änderungen zu übernehmen.

Schlussfolgerung

Das Protokoll für langsame Abfragen ist ein unschätzbares Werkzeug für das Tuning der Datenbankleistung in MySQL und MariaDB. Durch die Protokollierung von Abfragen, deren Ausführung zu lange dauert, können Sie Engpässe identifizieren und die Abfrageleistung optimieren. Unabhängig davon, ob Sie eine kleine Datenbank oder eine große Unternehmensanwendung verwalten, ist die Aktivierung des Protokolls für langsame Abfragen ein proaktiver Weg, um sicherzustellen, dass Ihre Datenbanken schnell und effizient bleiben.

Wenn Sie die Schritte in diesem Leitfaden befolgen, sollten Sie in der Lage sein, das Protokoll für langsame Abfragen zu aktivieren, zu konfigurieren und zu analysieren, um die Leistung Ihrer MySQL- oder MariaDB-Datenbank effektiv zu optimieren.