MySQL Slow Query Log: teure Queries finden

Wenn dein Gameserver oder deine Webanwendung ins Stocken gerät, sind die Schuldigen meist eine Handvoll teurer SQL-Queries, und sie mit bloßem Auge zu finden ist unmöglich. Genau hier zahlt sich das MySQL slow query log aus: Es protokolliert leise jede Query, die länger als ein von dir festgelegter Schwellenwert läuft, sodass du auf Daten statt auf Vermutungen blickst. In diesem Artikel zeige ich Schritt für Schritt, wie du das Log korrekt aktivierst, die richtigen Einstellungen wählst und deine teuersten Queries systematisch herausziehst.

Was das Slow Query Log eigentlich ist

Das Slow Query Log ist eine MySQL-Funktion, die jede SQL-Anweisung aufzeichnet, die länger als long_query_time Sekunden läuft. "Langsam" ist relativ: Eine Charakter-Abfrage von 0,5 Sekunden auf einem Metin2-Server ist eine Katastrophe, während 2 Sekunden auf einem Reporting-Bildschirm in Ordnung sein können. Jede Zeile speichert die Query-Zeit, die Lock-Zeit, die untersuchten Zeilen und die gesendeten Zeilen. Die wahre Stärke liegt in diesen Metadaten: Sehr oft ist nicht die Query selbst das Problem, sondern dass sie Millionen von Zeilen scannt, um nur zehn zurückzugeben.

Das Log aktivieren: persistente Konfiguration

Der robusteste Weg ist, in die my.cnf zu schreiben (unter Debian/Ubuntu meist /etc/mysql/mysql.conf.d/mysqld.cnf). Füge unter dem Block [mysqld] Folgendes hinzu:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_queries_not_using_indexes = 1
min_examined_row_limit = 100

Starte anschließend den Dienst neu: sudo systemctl restart mysql. Das bewirkt jede Option:

• long_query_time — der Schwellenwert in Sekunden. Er akzeptiert Dezimalwerte, du kannst also 0.5 schreiben.
• log_queries_not_using_indexes — protokolliert jede Query, die keinen Index nutzt, selbst schnelle. In der Entwicklung unbezahlbar, kann aber viel Rauschen erzeugen.
• min_examined_row_limit — ignoriert Queries, die weniger als diese Anzahl Zeilen untersuchen, und hält so kleine Tabellen aus dem Log heraus.

Im laufenden Betrieb aktivieren, ohne Neustart

Kannst du einen Produktionsserver nicht neu starten, erlaubt MySQL dir, die meisten dieser Variablen zur Laufzeit zu ändern. Verbinde dich als root und führe aus:

SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_queries_not_using_indexes = 'ON';

Ein Vorbehalt: long_query_time wird pro Session gelesen, daher übernehmen nur neue Verbindungen den Wert; dein bestehender Connection-Pool nutzt weiterhin den alten Schwellenwert. Außerdem gehen mit SET GLOBAL vorgenommene Änderungen beim Neustart verloren — für Persistenz brauchst du weiterhin die my.cnf.

Die teuersten Queries herausziehen: pt-query-digest

Das rohe Log von Hand zu lesen ist eine Qual; dieselbe Query wiederholt sich hunderte Male. Das Werkzeug pt-query-digest aus dem Percona Toolkit normalisiert diese Zeilen (es ersetzt literale Werte durch ?) und gruppiert identische Queries unter einem einzigen "Fingerabdruck". Installation unter Debian/Ubuntu:

sudo apt install percona-toolkit
pt-query-digest /var/log/mysql/slow.log > bericht.txt

Der Bericht ordnet die Queries oben nach insgesamt verbrauchter Zeit — eine Query mit 50 ms, die 200-mal pro Sekunde läuft, zählt genauso viel wie eine, die einmal 5 Sekunden dauert. Pro Gruppe erhältst du die Aufrufanzahl, die Gesamt- und Durchschnittszeit sowie eine Beispiel-Query. Meist verursachen die Top-drei-Queries den Großteil der Last; konzentriere dich darauf, statt blind zu optimieren.

Eine Query lesen: EXPLAIN und Indizes

Hast du den Schuldigen gefunden, stelle EXPLAIN voran, um den Plan von MySQL zu sehen:

EXPLAIN SELECT * FROM player WHERE account_id = 4210 ORDER BY level DESC;

Diese Felder solltest du in der Ausgabe beachten:

• type — siehst du ALL, liegt ein Full Table Scan vor, was schlecht ist; du willst ref oder range.
• key — ist er NULL, wird überhaupt kein Index genutzt.
• rows — die geschätzte Anzahl Zeilen, die MySQL zu scannen plant; ist sie weit größer als dein Ergebnis, steckt das Problem hier.
• Extra — Using filesort oder Using temporary bedeutet zusätzliche Kosten für Sortierung oder Gruppierung.

Im obigen Beispiel lässt ein Index auf account_id den Scan zusammenbrechen: CREATE INDEX idx_player_account ON player (account_id);. Für Spalten, die häufig gemeinsam gefiltert werden, denke über einen zusammengesetzten (Composite-)Index nach — aber jeder unnötige Index erhöht die Schreibkosten, also miss, bevor du hinzufügst.

Das Log unter Kontrolle halten

Das Slow Query Log wächst mit der Zeit; in der Produktion musst du es rotieren. Die meisten Distributionen liefern /etc/logrotate.d/mysql-server mit, und falls nicht, genügt eine einfache Regel. Ist die Diagnose abgeschlossen, schalte log_queries_not_using_indexes aus; diese Option kann die Datei mit kleinen, aber index-losen Queries füllen. Auf ausgelasteten Servern, wo Disk-I/O kostbar ist, aktiviere das Log nur während der Untersuchung und halte long_query_time die übrige Zeit auf einem sinnvollen Schwellenwert (etwa 1).

Häufige Fragen

Verlangsamt das Slow Query Log die Leistung?

Mit einem vernünftigen Schwellenwert ist die Auswirkung vernachlässigbar, da nur Queries geschrieben werden, die ihn überschreiten. Die echten Kosten entstehen mit aktiviertem log_queries_not_using_indexes: Auf einem Server mit viel Traffic kann das die Logdatei und die Schreibvorgänge schnell aufblähen. Lass es außerhalb der Diagnose ausgeschaltet.

Auf welchen Wert sollte ich long_query_time setzen?

Beginne mit 1 Sekunde. Wird nichts erfasst, senke ihn schrittweise (0.5, dann 0.2). Ein zu niedriger Wert füllt das Log mit Rauschen; das Ziel ist, die teuersten Queries zu sehen, nicht jede Query.

Kann ich in eine Tabelle statt in eine Datei loggen?

Ja, mit log_output = 'TABLE' landen die Einträge in der Tabelle mysql.slow_log und lassen sich mit SQL abfragen. Aber die Dateiausgabe ist mit pt-query-digest weitaus praktischer, daher ist in den meisten Fällen eine Datei vorzuziehen.

Wenn dein Server langsamer wird und du nicht weißt, wo du anfangen sollst, können wir gemeinsam das Slow Query Log aktivieren und deinen ersten pt-query-digest-Bericht interpretieren. Für ein MySQL-Performance-Audit und Query-Optimierung nimm Kontakt mit mir auf.