Langsame Abfragen sind die stillen Killer der Datenbankleistung. Eine einzige nicht optimierte Abfrage, die tausende Male pro Tag ausgeführt wird, kann einen ansonsten gesunden MySQL-Server in die Knie zwingen. Das MySQL Slow Query Log ist Ihre erste Verteidigungslinie — es erfasst jede Abfrage, die einen Zeitschwellenwert überschreitet, und liefert Ihnen die Rohdaten, die Sie zur Identifizierung und Beseitigung von Leistungsengpässen benötigen. In Kombination mit Analysetools wie mysqldumpslow und pt-query-digest können Sie systematisch die schlimmsten Übeltäter in Ihrer Arbeitslast finden, priorisieren und beheben.

Voraussetzungen

  • MySQL 5.7+ oder MySQL 8.0 (Befehle funktionieren bei beiden; kleine Syntaxunterschiede werden erwähnt)
  • Root- oder Administratorzugang zum MySQL-Server
  • Shell-Zugang zum Server, auf dem MySQL läuft
  • Percona Toolkit installiert (für pt-query-digest)
  • Grundlegendes Verständnis von SQL und MySQL-Konfigurationsdateien

Aktivierung des Slow Query Logs

Das Slow Query Log ist in den meisten MySQL-Installationen standardmäßig deaktiviert. Sie können es permanent über die Konfiguration oder temporär zur Laufzeit aktivieren.

Permanente Konfiguration (my.cnf)

Fügen Sie diese Zeilen zu Ihrer MySQL-Konfigurationsdatei hinzu, normalerweise unter /etc/mysql/my.cnf oder /etc/my.cnf:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1
log_output = FILE

Starten Sie MySQL neu:

sudo systemctl restart mysql

Laufzeitkonfiguration (Ohne Neustart)

Aktivieren Sie das Slow Query Log ohne Serverneustart:

SET GLOBAL slow_query_log = 1;
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
SET GLOBAL long_query_time = 1;
SET GLOBAL log_output = 'FILE';

Überprüfen Sie die Einstellungen:

SHOW VARIABLES LIKE 'slow_query%';
SHOW VARIABLES LIKE 'long_query_time';

Erwartete Ausgabe:

+---------------------+-------------------------------+
| Variable_name       | Value                         |
+---------------------+-------------------------------+
| slow_query_log      | ON                            |
| slow_query_log_file | /var/log/mysql/slow.log       |
+---------------------+-------------------------------+

log_output Optionen

MySQL unterstützt drei Ausgabeziele:

  • FILE — schreibt in die durch slow_query_log_file angegebene Datei (Standard, empfohlen)
  • TABLE — schreibt in die Tabelle mysql.slow_log (abfragbar, aber mit zusätzlichem Overhead)
  • NONE — deaktiviert die Protokollierung trotz slow_query_log=1

Verwenden Sie FILE für die Produktion. Die TABLE-Option erzeugt messbaren Schreib-Overhead und lässt das mysql-Schema unvorhersehbar wachsen.

Konfiguration der Log-Parameter

long_query_time

Dieser Schwellenwert bestimmt, welche Abfragen protokolliert werden. Der Standardwert von 10 Sekunden ist für die meisten Arbeitslasten viel zu hoch.

-- Abfragen über 0.5 Sekunden protokollieren
SET GLOBAL long_query_time = 0.5;

-- ALLE Abfragen protokollieren (nützlich für kurze Audits, nicht für Produktion)
SET GLOBAL long_query_time = 0;

Empfohlene Startwerte:

Umgebunglong_query_timeBegründung
Entwicklung0Alles für die Analyse erfassen
Staging0.1Abfragen >100ms erkennen
Produktion1.0Fokus auf eindeutig langsame Abfragen
Aggressiv0.5Balance zwischen Rauschen und Abdeckung

log_queries_not_using_indexes

Dieser Parameter protokolliert Abfragen, die vollständige Tabellenscans durchführen, unabhängig von der Ausführungszeit:

SET GLOBAL log_queries_not_using_indexes = 1;

Dies ist äußerst wertvoll, um Abfragen zu erkennen, die jetzt schnell sind, aber mit wachsenden Tabellen langsamer werden. Es kann jedoch viele Log-Einträge bei Datenbanken mit vielen kleinen Tabellen erzeugen.

min_examined_row_limit

Filtern Sie triviale Abfragen, indem Sie eine Mindestanzahl untersuchter Zeilen verlangen:

SET GLOBAL min_examined_row_limit = 1000;

Dies verhindert, dass das Log mit schnellen Einzelzeilen-Lookups gefüllt wird, die zufällig keinen Index treffen.

Analyse mit mysqldumpslow

mysqldumpslow wird mit MySQL ausgeliefert und erfordert keine zusätzliche Installation. Es parst das Slow Query Log, abstrahiert Literalwerte und gruppiert ähnliche Abfragen.

Grundlegende Verwendung

# Top 10 Abfragen nach Gesamtzeit
mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

# Top 10 Abfragen nach Häufigkeit
mysqldumpslow -s c -t 10 /var/log/mysql/slow.log

# Top 10 Abfragen nach Durchschnittszeit
mysqldumpslow -s at -t 10 /var/log/mysql/slow.log

# Abfragen nach Muster filtern
mysqldumpslow -s t -t 10 -g "orders" /var/log/mysql/slow.log

Sortieroptionen

FlagSortiert nach
-s tGesamtzeit
-s cHäufigkeit (Anzahl)
-s atDurchschnittszeit
-s lSperrzeit
-s alDurchschnittliche Sperrzeit
-s rUntersuchte Zeilen
-s arDurchschnitt untersuchter Zeilen

Beispielausgabe

Count: 1523  Time=2.45s (3731s)  Lock=0.00s (1s)  Rows=245.3 (373534), root[root]@localhost
  SELECT * FROM orders WHERE customer_id = N AND status = 'S' ORDER BY created_at DESC LIMIT N;

Dies zeigt, dass die Abfrage 1.523 Mal ausgeführt wurde, im Durchschnitt 2,45 Sekunden pro Ausführung brauchte, 245 Zeilen pro Ausführung untersuchte und insgesamt 3.731 Sekunden verbrauchte.

Einschränkungen

mysqldumpslow ist nützlich für schnelle Überprüfungen, hat aber erhebliche Einschränkungen:

  • Keine Perzentilanalyse (P95, P99)
  • Keine Antwortzeitverteilung
  • Kann keine Logs im Binär- oder TABLE-Format lesen
  • Begrenzte Filter- und Berichtsoptionen

Tiefenanalyse mit pt-query-digest

pt-query-digest aus dem Percona Toolkit ist der Industriestandard für die Analyse von Slow Query Logs. Es liefert weit detailliertere Einblicke als mysqldumpslow.

Installation

# Debian/Ubuntu
sudo apt-get install percona-toolkit

# RHEL/CentOS
sudo yum install percona-toolkit

# Aus dem Quellcode
wget https://www.percona.com/downloads/percona-toolkit/LATEST/tarball/percona-toolkit-LATEST.tar.gz
tar xzf percona-toolkit-LATEST.tar.gz
cd percona-toolkit-* && perl Makefile.PL && make && sudo make install

Grundlegende Verwendung

# Vollständiger Bericht
pt-query-digest /var/log/mysql/slow.log

# Bericht der letzten 24 Stunden
pt-query-digest --since '24h' /var/log/mysql/slow.log

# Nach Datenbank filtern
pt-query-digest --filter '$event->{db} eq "myapp"' /var/log/mysql/slow.log

# Ausgabe in Datei
pt-query-digest /var/log/mysql/slow.log > /tmp/slow-report.txt

Berichtsstruktur

Die Ausgabe hat drei Abschnitte:

1. Gesamtübersicht

# 2.1M QPS, 1.2x concurrency, 45% of time in query
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time         3731s    100ms     45s   2.45s   4.12s   1.33s   1.85s
# Lock time            1s       0     15ms    10us    22us    38us     8us
# Rows sent        373534       0    1000     245   480.0   180.3   198.0
# Rows examine     2.1M         0   50000    1382    3200    2100     850

2. Profil (Abfrage-Ranking)

# Rank Query ID                         Response time  Calls  R/Call  Item
# ==== ================================ ============== ====== ======= ====
#    1 0xE77769C62EF669AA1A6...          1800.0 48.2%   1523  1.1819  SELECT orders
#    2 0xA4B9D8C1F3E5A7B2C9...           950.3 25.5%    892  1.0654  SELECT products
#    3 0xF1C2D3E4A5B6C7D8E9...           480.1 12.9%   3201  0.1500  SELECT users

3. Detail pro Abfrage — für jede eingestufte Abfrage erhalten Sie den vollständigen Fingerprint, die Zeitverteilung, EXPLAIN-Empfehlungen und eine Beispielabfrage mit Literalwerten.

mysqldumpslow vs pt-query-digest vs MySQL Enterprise Monitor vs PMM

Funktionmysqldumpslowpt-query-digestMySQL Enterprise MonitorPercona PMM
KostenKostenlos (integriert)Kostenlos (Open Source)Kommerzielle LizenzKostenlos (Open Source)
InstallationKeinePercona ToolkitDedizierter ServerDocker/VM
Echtzeit-MonitoringNeinNeinJaJa
Historische AnalyseGrundlegendAusgezeichnetAusgezeichnetAusgezeichnet
Query-FingerprintingGrundlegendFortgeschrittenFortgeschrittenFortgeschritten
PerzentilanalyseNeinJa (P95, P99)JaJa
Visuelles DashboardNeinNeinJaJa (Grafana)
EXPLAIN-IntegrationNeinTeilweiseJaJa
AlarmeNeinNeinJaJa
Ideal fürSchnelle PrüfungenTiefenanalyseEnterprise-TeamsTeams mit kostenloser UI

Optimierung langsamer Abfragen

Nachdem Sie die langsamsten Abfragen identifiziert haben, folgen Sie diesem systematischen Ansatz.

Schritt 1: EXPLAIN der Abfrage

EXPLAIN SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC LIMIT 10;

Wichtige Spalten:

  • type: Sollte ref, range oder const sein. Vermeiden Sie ALL (vollständiger Tabellenscan)
  • key: Welchen Index MySQL gewählt hat. NULL bedeutet kein Index verwendet
  • rows: Geschätzte untersuchte Zeilen. Hohe Zahlen deuten auf fehlende Indizes hin
  • Extra: Achten Sie auf Using filesort und Using temporary

In MySQL 8.0 verwenden Sie EXPLAIN ANALYZE für tatsächliche Ausführungsstatistiken:

EXPLAIN ANALYZE SELECT * FROM orders
WHERE customer_id = 42 AND status = 'pending'
ORDER BY created_at DESC LIMIT 10;

Schritt 2: Fehlende Indizes Hinzufügen

Basierend auf der EXPLAIN-Ausgabe erstellen Sie zusammengesetzte Indizes, die WHERE- und ORDER BY-Klauseln abdecken:

-- Deckt WHERE customer_id AND status, ORDER BY created_at ab
CREATE INDEX idx_orders_customer_status_created
ON orders (customer_id, status, created_at DESC);

Regeln für das Index-Design:

  • Gleichheitsspalten zuerst (customer_id, status)
  • Bereichs-/Sortierspalten zuletzt (created_at)
  • Einschließen von SELECT-Spalten für einen Covering Index
  • Über-Indexierung vermeiden — jeder Index verlangsamt Schreibvorgänge

Schritt 3: Problematische Muster Umschreiben

Häufige Umschreibungen zur Leistungsverbesserung:

-- SCHLECHT: Subquery pro Zeile ausgeführt
SELECT * FROM orders WHERE customer_id IN (
  SELECT id FROM customers WHERE region = 'US'
);

-- GUT: JOIN wird einmal ausgeführt
SELECT o.* FROM orders o
INNER JOIN customers c ON o.customer_id = c.id
WHERE c.region = 'US';

-- SCHLECHT: SELECT * holt ungenutzte Spalten
SELECT * FROM orders WHERE id = 42;

-- GUT: Nur benötigte Spalten auswählen
SELECT id, status, total, created_at FROM orders WHERE id = 42;

-- SCHLECHT: Funktion auf indizierter Spalte verhindert Indexnutzung
SELECT * FROM orders WHERE YEAR(created_at) = 2025;

-- GUT: Bereichsscan nutzt den Index
SELECT * FROM orders
WHERE created_at >= '2025-01-01' AND created_at < '2026-01-01';

Praxisszenario

Sie verwalten die Datenbank einer E-Commerce-Plattform. Benutzer berichten, dass Produktseiten während der Spitzenzeiten (14-18 Uhr) langsam laden, aber der Rest der Website normal funktioniert. Servermetriken zeigen CPU-Spitzen, die mit den Beschwerden korrelieren, aber Speicher und Disk-I/O sind normal.

Schritt 1: Aktivieren Sie das Slow Query Log mit long_query_time=0.5 während der Spitzenzeiten.

Schritt 2: Nach 2 Stunden Protokollierung führen Sie pt-query-digest aus:

pt-query-digest --since '2h' /var/log/mysql/slow.log

Schritt 3: Der Bericht zeigt, dass die Hauptabfrage 62% der Gesamtzeit verbraucht:

SELECT p.*, c.name as category_name,
       (SELECT AVG(rating) FROM reviews WHERE product_id = p.id) AS avg_rating,
       (SELECT COUNT(*) FROM reviews WHERE product_id = p.id) AS review_count
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE p.category_id = 15 AND p.active = 1
ORDER BY created_at DESC LIMIT 20;

Schritt 4: EXPLAIN zeigt, dass die korrelierten Subqueries einmal pro Produktzeile ausgeführt werden (2.400 Mal pro Seitenladevorgang). Korrektur durch Umschreiben als JOIN:

SELECT p.*, c.name as category_name,
       COALESCE(r.avg_rating, 0) AS avg_rating,
       COALESCE(r.review_count, 0) AS review_count
FROM products p
JOIN categories c ON p.category_id = c.id
LEFT JOIN (
  SELECT product_id, AVG(rating) as avg_rating, COUNT(*) as review_count
  FROM reviews GROUP BY product_id
) r ON r.product_id = p.id
WHERE p.category_id = 15 AND p.active = 1
ORDER BY p.created_at DESC LIMIT 20;

Schritt 5: Fügen Sie einen zusammengesetzten Index hinzu:

CREATE INDEX idx_products_category_active_created
ON products (category_id, active, created_at DESC);

Ergebnis: Die Abfragezeit sinkt von 2,4 Sekunden auf 12 Millisekunden. Die Seitenladezeit kehrt zur Normalität zurück.

Fallstricke und Sonderfälle

  • Log-Rotation ist unverzichtbar. Ohne Rotation kann das Slow Log den gesamten verfügbaren Speicherplatz belegen. Verwenden Sie logrotate oder den MySQL-Befehl FLUSH SLOW LOGS mit einem Cron-Job.
  • Speicherplatzüberwachung. Das Setzen von long_query_time=0 auf einem ausgelasteten Server kann Gigabytes an Log-Daten pro Stunde erzeugen. Überwachen Sie immer die Festplattennutzung beim Senken des Schwellenwerts.
  • Replikationsverzögerung durch Logging. Auf Replikas mit log_slow_replica_statements=1 (MySQL 8.0.26+) kann das Protokollieren replizierter Abfragen die Verzögerung erhöhen. Überwachen Sie Seconds_Behind_Source.
  • Überflutung durch log_queries_not_using_indexes. Kleine Lookup-Tabellen (Länder, Status) lösen diesen Flag aus, obwohl vollständige Scans auf 50-Zeilen-Tabellen optimal sind. Verwenden Sie min_examined_row_limit zum Filtern.
  • Prepared Statements. Standardmäßig werden Prepared Statements nicht protokolliert. Setzen Sie log_slow_extra=1 (MySQL 8.0.14+), um zusätzliche Statistiken zu erfassen.
  • Laufzeitänderungen bleiben nicht bestehen. SET GLOBAL-Änderungen gehen beim Neustart verloren. Aktualisieren Sie immer auch die my.cnf zusätzlich zu den Laufzeitbefehlen.

Fehlerbehebung

Slow Query Log-Datei wird nicht erstellt: Überprüfen Sie, ob MySQL Schreibrechte auf das Log-Verzeichnis hat:

sudo mkdir -p /var/log/mysql
sudo chown mysql:mysql /var/log/mysql
sudo chmod 750 /var/log/mysql

Log zeigt null Abfragen trotz slow_query_log=ON: Überprüfen Sie, ob long_query_time nicht zu hoch eingestellt ist:

SELECT @@global.long_query_time;

Wenn 10.000000 angezeigt wird, müssen Abfragen über 10 Sekunden dauern, um protokolliert zu werden. Senken Sie auf 1.

pt-query-digest meldet “No events processed”: Die Log-Datei ist möglicherweise leer oder das Format wird nicht erkannt. Stellen Sie sicher, dass log_output=FILE (nicht TABLE). Überprüfen Sie die Berechtigungen:

ls -la /var/log/mysql/slow.log
file /var/log/mysql/slow.log

mysqldumpslow zeigt “Permission denied”: Das Tool benötigt Lesezugriff auf die Log-Datei. Führen Sie es mit sudo aus oder fügen Sie Ihren Benutzer zur Gruppe mysql hinzu:

sudo mysqldumpslow -s t -t 10 /var/log/mysql/slow.log

Abfragen erscheinen im Slow Log, aber EXPLAIN zeigt schnelle Ausführung: Dies bedeutet in der Regel Lock-Contention. Prüfen Sie das Feld Lock_time im Log-Eintrag. Hohe Sperrzeiten deuten auf Sperrenprobleme auf Tabellen- oder Zeilenebene hin, nicht auf Probleme bei der Abfrageoptimierung.

Zusammenfassung

  • Aktivieren Sie das Slow Query Log mit slow_query_log=1 und setzen Sie long_query_time auf 1 Sekunde oder weniger
  • Verwenden Sie log_queries_not_using_indexes, um Abfragen zu erkennen, die mit wachsenden Daten langsamer werden
  • Beginnen Sie mit mysqldumpslow für schnelle Analysen; wechseln Sie zu pt-query-digest für Produktionsanalysen
  • Konzentrieren Sie sich auf die Gesamtzeit (Häufigkeit × Durchschnittszeit) statt nur auf die langsamste Einzelausführung
  • Verwenden Sie EXPLAIN und EXPLAIN ANALYZE (MySQL 8.0), um Ausführungspläne vor der Indexerstellung zu verstehen
  • Entwerfen Sie zusammengesetzte Indizes mit Gleichheitsspalten zuerst und Bereichs-/Sortierspalten zuletzt
  • Schreiben Sie korrelierte Subqueries als JOINs um für Verbesserungen um eine Größenordnung
  • Implementieren Sie immer Log-Rotation und überwachen Sie den Speicherplatz beim Einsatz des Slow Query Logs in der Produktion

Verwandte Artikel