MariaDB / Slow Query Log einrichten und auswerten

Das Slow Query Log von MariaDB zeichnet SQL auf, das einen Zeitgrenzwert überschreitet oder andere aktivierte Protokollierungsbedingungen erfüllt. Für eine Dateiausgabe aktivieren Sie slow_query_log, nehmen FILE in log_output auf und prüfen Grenzwert und Filter der ausführenden Verbindung. Laufzeitwerte lassen sich per SQL ohne Neustart ändern; dauerhafte Einstellungen gehören in eine Optionsdatei.

Was wird aufgezeichnet und was benötigen Sie?

Das Log dient zur Untersuchung der SQL-Ausführungszeit auf dem Server und der untersuchten Zeilen. Seine Aktivierung beschleunigt Abfragen nicht automatisch. Nutzen Sie die Daten zur Prüfung von Ausführungsplänen und Indizes. Die Antwortzeit einer Webseite umfasst zusätzlich Netzwerk und Anwendung und ist daher nicht mit der SQL-Zeit gleichzusetzen.

Der übliche Grenzwert ist long_query_time in Sekunden: 1 bedeutet eine Sekunde. Eine aktivierte Bedingung für Abfragen ohne Index kann auch schnelle Abfragen erfassen. Zeilenlimits, Filter und Stichproben können dagegen Abfragen oberhalb des Grenzwerts auslassen. Die MariaDB-Übersicht zum Slow Query Log beschreibt diese Bedingungen.

Die Anleitung gilt für einen selbst verwalteten Linux-Server. Sie benötigen ein administratives SQL-Konto und Betriebssystemrechte zur Bearbeitung der Konfiguration. Globale Variablen erfordern die entsprechende Berechtigung Ihrer Version. Verwenden Sie bei Berechtigungsfehlern ein Administrationskonto, statt dem Anwendungskonto umfassende Rechte zu geben. Verwaltete Datenbankdienste können eigene Parametergruppen und Logansichten verlangen.

Seit MariaDB 10.11 haben mehrere Variablen neue Namen; die älteren Namen bleiben als Aliase verfügbar. Die Beispiele verwenden diese vertrauten Namen, deren Unterstützung auf dem tatsächlichen Server zu prüfen ist. Details zu Version und Geltungsbereich enthält die Referenz der MariaDB-Servervariablen.

Ohne Neustart aktivieren und mit einer Testabfrage prüfen

Aktuelle und ursprüngliche Werte festhalten

Bei einer üblichen Ubuntu-Installation mit lokaler Socket-Authentifizierung verbinden Sie sich mit sudo mariadb. Andernfalls verwenden Sie die eingerichtete Anmeldung, etwa mariadb -u Administrationskonto -p. Dokumentieren Sie zuerst Version, Datenverzeichnis und globale Werte. Ein Alias kann in der Ausgabe unter seinem neuen Namen erscheinen.

SELECT VERSION(), @@GLOBAL.datadir;
SHOW GLOBAL VARIABLES WHERE Variable_name IN (
  'slow_query_log', 'log_slow_query',
  'slow_query_log_file', 'log_slow_query_file',
  'long_query_time', 'log_slow_query_time',
  'log_output', 'general_log', 'log_slow_rate_limit',
  'min_examined_row_limit', 'log_queries_not_using_indexes',
  'log_slow_filter', 'log_slow_disabled_statements'
);

Für einen vorübergehenden Versuch speichern Sie die folgenden globalen Werte in derselben administrativen Verbindung. Die Benutzervariablen @saved_... existieren nur dort und gehen beim Trennen verloren. Halten Sie die Ausgabe deshalb zusätzlich in Arbeitsnotizen fest und führen Sie das spätere Wiederherstellungs-SQL in derselben Verbindung aus.

SET @saved_slow = @@GLOBAL.slow_query_log,
    @saved_file = @@GLOBAL.slow_query_log_file,
    @saved_output = @@GLOBAL.log_output,
    @saved_time = @@GLOBAL.long_query_time;

Dateiausgabe aktivieren und Sitzungswerte unterscheiden

Das Beispiel verwendet eine Datei. Ein relativer Name wie mariadb-slow.log liegt unter @@GLOBAL.datadir. Betreiben Sie bereits ein Slow Log, können Sie dessen bisherigen Pfad beibehalten.

SET GLOBAL slow_query_log_file = 'mariadb-slow.log';
SET GLOBAL log_output = 'FILE';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log = ON;

log_output steuert auch das General Query Log. Falls bisher TABLE genutzt wird, prüfen Sie diesen Bedarf vor der Änderung. Für beide Ziele ist FILE,TABLE möglich. Mit NONE erfolgt trotz aktivierter Protokollierung keine Ausgabe.

Globale Werte sind Vorgaben für neue Verbindungen und ändern nicht automatisch den Grenzwert jeder bestehenden Sitzung. Vergleichen Sie beide Bereiche wie folgt. Ein Verbindungspool kann ältere Verbindungen weiterverwenden; planen Sie daher neue Verbindungen oder gezielte Sitzungsänderungen. Siehe GLOBAL und SESSION bei SHOW VARIABLES.

SELECT @@GLOBAL.long_query_time AS global_seconds,
       @@SESSION.long_query_time AS session_seconds,
       @@GLOBAL.slow_query_log AS global_log,
       @@SESSION.slow_query_log AS session_log;

Kontrollierten Test ausführen und Sitzung zurücksetzen

Der folgende Test sichert die Sitzungswerte und setzt vorübergehend den Grenzwert auf eine Sekunde, die Mindestzeilenzahl auf null und die Stichprobe auf eins. Filter werden nur in dieser Sitzung geleert. SLEEP untersucht keine Tabellenzeilen und könnte durch eine positive Mindestzahl ausgeschlossen werden. Prüfen Sie auch, ob ausgeschlossene Anweisungstypen SELECT unterdrücken.

SET @session_log = @@SESSION.slow_query_log,
    @session_time = @@SESSION.long_query_time,
    @session_rows = @@SESSION.min_examined_row_limit,
    @session_rate = @@SESSION.log_slow_rate_limit,
    @session_filter = @@SESSION.log_slow_filter;
SET SESSION slow_query_log = ON;
SET SESSION long_query_time = 1;
SET SESSION min_examined_row_limit = 0;
SET SESSION log_slow_rate_limit = 1;
SET SESSION log_slow_filter = '';
SELECT SLEEP(1.2) AS slow_log_probe_13708;

Bei normalem Abschluss ist der Rückgabewert 0. Danach sollte slow_log_probe_13708 im Log stehen, da die Abfrage den Grenzwert überschreitet. Die tatsächliche Dauer hängt von der Last ab. Dies prüft den Protokollierungsweg und ist kein Leistungsbenchmark oder Indextest.

Nach Prüfung der Datei stellen Sie die ursprünglichen Sitzungswerte in derselben SQL-Verbindung wieder her. Für die anschließende reale Erfassung prüfen Sie separat eine Verbindung mit den beabsichtigten globalen Vorgaben, etwa eine neu geöffnete Sitzung.

SET SESSION slow_query_log = @session_log;
SET SESSION long_query_time = @session_time;
SET SESSION min_examined_row_limit = @session_rows;
SET SESSION log_slow_rate_limit = @session_rate;
SET SESSION log_slow_filter = @session_filter;

Unter Ubuntu oder Rocky Linux dauerhaft konfigurieren

SET GLOBAL ersetzt keine dauerhafte Optionsdatei. Ermitteln Sie zunächst die vom Dienst tatsächlich gelesenen Dateien und Startargumente. Prüfen Sie Ausgaben auf sensible Einstellungen, bevor Sie sie weitergeben.

sudo systemctl cat mariadb
sudo mariadbd --help --verbose
sudo mariadbd --print-defaults

1. Konfiguration identifizieren und sichern. Typische Ubuntu-Pakete verwenden /etc/mysql/mariadb.conf.d/, typische Rocky-Pakete /etc/my.cnf.d/. Der Rocky-Linux-Leitfaden zu MariaDB beschreibt ebenfalls den dortigen Konfigurationsort. Nach Prüfung des Include-Pfads können Sie unter Ubuntu eine 60-slow-log.cnf oder unter Rocky eine slow-query.cnf anlegen. Prüfen Sie vorhandene gleichnamige Dateien vor dem Bearbeiten.
2. Die folgenden Serveroptionen eintragen. Kontrollieren Sie spätere Dateien und Startargumente auf Überschreibungen und lösen Sie widersprüchliche Doppeleinträge auf. Eine Sekunde ist ein Ausgangspunkt, kein universeller Zielwert.

[mariadb]
slow_query_log=ON
slow_query_log_file=mariadb-slow.log
log_output=FILE
long_query_time=1
log_slow_rate_limit=1
min_examined_row_limit=0

3. Im Wartungsfenster neu starten und Werte prüfen. Stellen Sie mit mariadbd --print-defaults fest, ob die Datei gelesen wird, und starten Sie dann wie folgt neu. Passen Sie den letzten Befehl an die lokale Anmeldung und den tatsächlichen Dienstnamen an.

sudo systemctl restart mariadb
sudo systemctl status mariadb --no-pager
sudo mariadb -e "SHOW GLOBAL VARIABLES LIKE 'slow_query_log%'; SHOW GLOBAL VARIABLES LIKE 'long_query_time'; SHOW GLOBAL VARIABLES LIKE 'log_output';"

Öffnen Sie eine neue Clientverbindung, kontrollieren Sie Pfad, Zeitgrenze, Ausgabeziel und Sitzungswerte und wiederholen Sie den Test. Ein anderer absoluter Pfad erfordert Schreibrechte des Serverkontos und eine Prüfung von AppArmor unter Ubuntu beziehungsweise SELinux unter Rocky. Ein relativer Dateiname im bestehenden Datenverzeichnis vermeidet zunächst Richtlinienänderungen für neue Ordner. Die Suchreihenfolge erläutert MariaDB zu Optionsdateien.

Logdatei oder Logtabelle lesen

Datei prüfen und nach Zeit oder Häufigkeit zusammenfassen

Verwenden Sie den per SQL bestätigten Pfad. Ersetzen Sie /var/lib/mysql/, falls Ihr Datenverzeichnis anders liegt. tail -F verfolgt den Dateinamen und zeigt neue Einträge; mit Strg+C beenden Sie die Anzeige.

sudo tail -n 80 /var/lib/mysql/mariadb-slow.log
sudo tail -F /var/lib/mysql/mariadb-slow.log

Suchen Sie die Testabfrage oder eine andere Anweisung im Original und betrachten Sie Query_time, Lock_time, Rows_sent und Rows_examined gemeinsam. mariadb-dumpslow gruppiert ähnliche SQL-Anweisungen in größeren Logs. Fehlt das Werkzeug, prüfen Sie die Pakete Ihrer Distribution. Es kann auch unter dem früheren Namen mysqldumpslow installiert sein.

sudo mariadb-dumpslow -s t -t 10 /var/lib/mysql/mariadb-slow.log
sudo mariadb-dumpslow -s at -t 10 /var/lib/mysql/mariadb-slow.log
sudo mariadb-dumpslow -s c -t 10 /var/lib/mysql/mariadb-slow.log

-s t sortiert nach kumulierter Abfragezeit, -s at nach durchschnittlicher Zeit und -s c nach Anzahl. -t 10 fordert die zehn obersten Gruppen an. Zahlen und Zeichenketten werden standardmäßig abstrahiert. Priorisieren Sie anhand der Zusammenfassung und prüfen Sie anschließend Original-SQL und Ausführungsplan.

Bei konfigurierter TABLE-Ausgabe

Enthält log_output den Wert TABLE oder FILE,TABLE, kann ein berechtigtes Konto mysql.slow_log abfragen. Mit dem reinen FILE-Beispiel oben bedeutet eine leere Tabelle keinen Fehler der Dateiausgabe.

SELECT start_time, query_time, lock_time,
       rows_sent, rows_examined, sql_text
FROM mysql.slow_log
ORDER BY start_time DESC
LIMIT 20;

Tabellen- und Dateiausgabe unterscheiden sich in Funktion und Aufwand. Erweiterte EXPLAIN-Ausgaben gibt es beispielsweise nur bei Dateiausgabe. Ändern Sie das gemeinsame Ausgabeziel nicht unüberlegt; lesen Sie MariaDB-Logs in Tabellen schreiben.

Welche Abfragen sollten Sie zuerst untersuchen?

Berücksichtigen Sie Häufigkeit und kumulierte Zeit, statt nur die langsamste Einzelabfrage auszuwählen. Fünf Sekunden bei einem Aufruf und 0,2 Sekunden bei tausend Aufrufen ergeben unterschiedliche Prioritäten nach Mittelwert und Gesamtdauer. Ein Grenzwert von einer Sekunde würde den zweiten Fall über die normale Zeitbedingung allerdings nicht erfassen. Ein gefiltertes Log repräsentiert nicht jede Datenbanklast.

Erstellen Sie einen Index nicht allein wegen eines Logeintrags. Ein vollständiger Scan kann bei kleinen Tabellen sinnvoll sein; Indizes kosten Schreibarbeit und Speicher. Vergleichen Sie Pläne unter realistischen, gleichen Bedingungen vor und nach einer Änderung. Diagnosen wie ANALYZE können Anweisungen tatsächlich ausführen: Prüfen Sie vorher Last und mögliche Änderungen.

Fehlende oder zu viele Einträge untersuchen

• Aktiviert, aber leer: Prüfen Sie NONE, Datei- oder Tabellenziel, die verbundene Instanz sowie Aktivierung und Grenzwert der Sitzung. Eine laufende Anweisung kann erst nach Abschluss erscheinen.
• Testabfrage fehlt: Prüfen Sie Mindestzeilenzahl, log_slow_filter, ausgeschlossene Anweisungstypen und log_slow_rate_limit. 1000 bedeutet nicht, dass jede passende Anweisung erfasst wird; verwenden Sie im kontrollierten Test 1.
• Schnelle Abfragen erscheinen: Kontrollieren Sie log_queries_not_using_indexes oder not_using_index im Filter. Ein höherer Zeitgrenzwert beseitigt diese Bedingung nicht unbedingt.
• Dateizugriffsfehler: Prüfen Sie Serverkonto, übergeordnete Verzeichnisse, AppArmor-/SELinux-Ablehnungen und freien Speicher. Die Dateirechte des Clientbenutzers unterscheiden sich von denen des Serverprozesses.

Passen Sie diese Linux-Prüfbefehle an die realen Pfade an. Bei Startfehlern untersuchen Sie verweigerte Pfade und unbekannte Optionen, statt die gesamte Sicherheitsrichtlinie abzuschalten.

sudo journalctl -u mariadb -b -n 100 --no-pager
sudo journalctl -k -b --no-pager
sudo ls -ld /var/lib/mysql
sudo ls -l /var/lib/mysql/mariadb-slow.log
df -h /var/lib/mysql

Erfassung stoppen, Einstellungen wiederherstellen und Logs verwalten

Die folgenden Anweisungen deaktivieren die Erfassung, löschen aber keine vorhandenen Einträge. Ist die Protokollierung in einer Optionsdatei aktiviert, wird sie beim nächsten Neustart wieder eingeschaltet. Passen Sie deshalb auch die dauerhafte Konfiguration an.

SET GLOBAL slow_query_log = OFF;
SET SESSION slow_query_log = OFF;

Ist die ursprüngliche administrative Verbindung noch geöffnet, stellen Sie deren gespeicherte globale Werte wie folgt wieder her. Nach einer Trennung verwenden Sie nicht die verschwundenen Benutzervariablen, sondern die separat dokumentierten Originalwerte. Bestehende Anwendungssitzungen und Optionsdateien sind gesondert zu prüfen.

SET GLOBAL slow_query_log = OFF;
SET GLOBAL slow_query_log_file = @saved_file;
SET GLOBAL log_output = @saved_output;
SET GLOBAL long_query_time = @saved_time;
SET GLOBAL slow_query_log = @saved_slow;

Bei längerem Betrieb überwachen Sie Aufbewahrungsdauer, Dateigrößen und freien Speicher. Nach bloßem Umbenennen kann der Server über den alten Dateihandle weiterschreiben. Prüfen Sie die vorhandene logrotate-Konfiguration des Pakets und das erneute Öffnen. FLUSH SLOW LOGS schließt und öffnet das Slow Log, erstellt aber keine Archivierungs-, Komprimierungs- oder Löschrichtlinie. Dafür ist die entsprechende RELOAD-Berechtigung erforderlich.

FLUSH SLOW LOGS;

Orientieren Sie sich an MariaDB zur Logrotation unter Linux und der FLUSH-Referenz. Kontrollieren Sie, ob ein Log im Datenverzeichnis vom vorhandenen Rotationsplan erfasst wird. SQL-Texte können personenbezogene Daten oder Geheimnisse enthalten: Beschränken Sie den Zugriff auf Logs und Backups und entfernen Sie sensible Werte vor dem Weitergeben.