Häufige MySQL-Leistungsengpässe und wie man sie behebt

Wenn MySQL langsamer wird, ist das erste Symptom selten "die Datenbank ist langsam". Es ist normalerweise eine Checkout-Seite, die hängt, eine Warteschlange, die nicht mehr abfließt, ein Dashboard, das eine Zeitüberschreitung hat, oder eine API, die plötzlich drei Sekunden für eine Anfrage benötigt, die früher in 80 ms erledigt war.

Der schnellste Weg, Zeit zu verschwenden, ist, zufällige Einstellungen zu optimieren, bevor Sie wissen, wo die Wartezeit liegt. Beginnen Sie mit einer einfachen Frage: Wartet MySQL auf Abfragearbeit, Sperren, Speicher, Festplatte, CPU, Netzwerk oder zu viele Verbindungen? Die Lösung hängt von der Antwort ab.

1. Langsame Abfragen

Langsame Abfragen sind wohl der häufigste Leistungsengpass. Sie können aus verschiedenen Faktoren resultieren, darunter ineffizientes Abfragedesign, fehlende Indizes oder große Tabellenscans. Die Identifizierung dieser Abfragen ist der erste Schritt zur Lösung.

Identifizieren langsamer Abfragen

Das MySQL Slow-Query-Log ist ein unschätzbares Werkzeug zur Identifizierung von Abfragen, die länger als einen bestimmten Schwellenwert zur Ausführung benötigen. Sie können dieses Log in Ihrer my.cnf- (oder my.ini-) Konfigurationsdatei aktivieren und konfigurieren.

Beispiel my.cnf-Konfiguration:

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

In diesem Beispiel:

slow_query_log = 1: Aktiviert das Slow-Query-Log.
slow_query_log_file: Gibt den Pfad zur Logdatei an.
long_query_time = 2: Setzt den Schwellenwert auf 2 Sekunden. Abfragen, die länger dauern, werden protokolliert.
log_queries_not_using_indexes = 1: Protokolliert Abfragen, die keine Indizes verwenden, was oft ein Hauptkandidat für Optimierungen ist.

Nachdem Sie das Log aktiviert haben, können Sie dessen Inhalt analysieren. Tools wie mysqldumpslow können helfen, die Logdatei zusammenzufassen und zu sortieren, was es erleichtert, die problematischsten Abfragen zu identifizieren.

Optimieren langsamer Abfragen

Sobald langsame Abfragen identifiziert wurden, können verschiedene Strategien angewendet werden:

Indizierung: Stellen Sie sicher, dass geeignete Indizes für Spalten erstellt werden, die in WHERE-, JOIN-, ORDER BY- und GROUP BY-Klauseln verwendet werden. Verwenden Sie EXPLAIN, um Abfrageausführungspläne zu analysieren und fehlende Indizes zu identifizieren.
- Beispiel: Wenn eine Abfrage häufig nach user_id in einer großen orders-Tabelle filtert, kann ein Index auf orders(user_id) die Leistung drastisch verbessern.
```
CREATE INDEX idx_user_id ON orders (user_id);
```
Abfrageumschreibung: Manchmal kann eine Abfrage für eine bessere Effizienz umgeschrieben werden. Dies kann das Vereinfachen von Joins, das Vermeiden von SELECT * oder den umsichtigeren Einsatz von Unterabfragen beinhalten.
- Beispiel: Das Ersetzen einer korrelierten Unterabfrage durch einen JOIN könnte eine bessere Leistung bieten.
Datenbankschema-Design: Die Überprüfung des Datenbankschemas auf Normalisierungsprobleme oder auf Möglichkeiten zur (vorsichtigen) Denormalisierung kann ebenfalls helfen.

2. Ineffiziente Indizierung

Während die Indizierung der Schlüssel zur Abfrageleistung ist, können schlecht gestaltete oder übermäßige Indizes ebenfalls zu einem Engpass werden. Indizes verbrauchen Speicherplatz und erhöhen den Aufwand für Schreiboperationen (INSERT, UPDATE, DELETE).

Identifizieren von Indizierungsproblemen

EXPLAIN-Plananalyse: Verwenden Sie immer EXPLAIN vor und nachdem Sie Indizierungsänderungen vornehmen. Achten Sie auf vollständige Tabellenscans (type: ALL) bei großen Tabellen oder auf Zeilen, die untersucht werden und viel höher sind als die zurückgegebenen Zeilen.
```
EXPLAIN SELECT * FROM users WHERE email = '[email protected]';
```
Unbenutzte Indizes: MySQL 5.6+ hat eine Funktion zur Verfolgung der Indexnutzung. Sie können performance_schema.table_io_waits_summary_by_index_usage überprüfen, um Indizes zu identifizieren, die nie oder selten verwendet werden.
Redundante Indizes: Indizes, die dieselben Spalten abdecken oder Präfixe anderer Indizes sind, können redundant sein.

Best Practices für die Indizierung

Selektiv indizieren: Erstellen Sie Indizes nur dort, wo sie basierend auf Abfragemustern wirklich benötigt werden.
Zusammengesetzte Indizes: Erwägen Sie für Abfragen, die nach mehreren Spalten filtern, zusammengesetzte Indizes. Die Reihenfolge der Spalten in einem zusammengesetzten Index ist wichtig.
Abdeckende Indizes: Streben Sie abdeckende Indizes an, bei denen alle von einer Abfrage benötigten Spalten Teil des Index sind. Dies ermöglicht MySQL, Daten direkt aus dem Index abzurufen, ohne auf die Tabelle zugreifen zu müssen.
Regelmäßige Überprüfung: Überprüfen Sie Ihre Indizes regelmäßig, insbesondere nach Schemaänderungen oder Verschiebungen in der Anwendungsnutzung.

3. Pufferpool und Speicherkonfiguration

Der InnoDB-Pufferpool ist ein kritischer Speicherbereich, in dem InnoDB Daten- und Indexseiten zwischenspeichert. Eine unzureichende Pufferpoolgröße kann zu übermäßigen Festplatten-I/O führen, was Operationen erheblich verlangsamt.

Optimieren des InnoDB-Pufferpools

Der Parameter innodb_buffer_pool_size ist eine der wichtigsten Einstellungen für die InnoDB-Leistung.

Empfehlung: Für dedizierte Datenbankserver ist die Einstellung von innodb_buffer_pool_size auf 50-75 % des verfügbaren RAMs ein üblicher Ausgangspunkt. Einige Systeme können höher laufen, aber nur, wenn das Betriebssystem nicht swapped und der Verbindungsspeicher unter Kontrolle ist.

Beispiel my.cnf-Konfiguration:

[mysqld]
innodb_buffer_pool_size = 8G

Dies setzt den Pufferpool auf 8 Gigabyte.

Überwachung: Beobachten Sie das Lesemuster des Pufferpools. Eine sehr hohe Trefferquote bedeutet oft, dass die meisten Lesevorgänge aus dem Speicher bedient werden, aber es beweist nicht, dass jede Abfrage gesund ist. Sie können dies überwachen mit:

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';

Die Trefferquote kann berechnet werden als (Innodb_buffer_pool_read_requests - Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests.

Andere Speichereinstellungen

innodb_log_file_size: Beeinflusst die Schreibleistung und die Wiederherstellungszeit. Größere Dateien können den Schreibdurchsatz verbessern, erhöhen aber die Wiederherstellungszeit nach einem Absturz.
innodb_flush_log_at_trx_commit: Steuert Haltbarkeit versus Leistung. Die Einstellung auf 1 (Standard) gewährleistet vollständige ACID-Konformität, kann aber langsamer sein. Die Einstellung auf 0 oder 2 kann die Leistung verbessern, allerdings auf Kosten einiger Haltbarkeitsgarantien.

4. Sperrprobleme und Parallelität

Sperren sind für die Datenkonsistenz unerlässlich, können aber bei unsachgemäßer Verwaltung zu einem Engpass werden. Übermäßiges Sperren kann zu Abfragekonflikten, Zeitüberschreitungen und Deadlocks führen.

Identifizieren von Sperrproblemen

SHOW ENGINE INNODB STATUS: Dieser Befehl liefert detaillierte Informationen über den internen Zustand von InnoDB, einschließlich aktiver Transaktionen, gehaltener Sperren und Sperrwartezustände.
Performance Schema-Sperrtabellen: Verwenden Sie in MySQL 8.0 Performance Schema-Tabellen wie data_locks und data_lock_waits. Ältere Versionen stellten Sperrinformationen über information_schema-Tabellen bereit.
Überwachungstools: Leistungsüberwachungstools können oft hohe Sperrwartezeiten oder Deadlocks hervorheben.

Beheben von Sperrproblemen

Optimieren Sie Abfragen, die Sperren verursachen: Kürzere, effizientere Abfragen reduzieren die Zeit, in der Sperren gehalten werden.
Transaktionsmanagement: Halten Sie Transaktionen so kurz wie möglich. Vermeiden Sie langlaufende Operationen innerhalb von Transaktionen, die umfangreiche Sperren erfordern.
Sperrgranularität: InnoDB verwendet für die meisten Operationen Sperren auf Zeilenebene, was im Allgemeinen gut für die Parallelität ist. Es ist jedoch wichtig zu verstehen, wie Ihre Abfragen zu Tabellensperren eskalieren könnten (z. B. ALTER TABLE ohne Online-DDL).
Deadlock-Erkennung und -Auflösung: MySQL hat einen Deadlock-Detektor. Wenn ein Deadlock erkannt wird, wird InnoDB typischerweise eine der beteiligten Transaktionen zurückrollen, damit die andere fortfahren kann. Analysieren Sie Deadlock-Informationen aus SHOW ENGINE INNODB STATUS, um die Ursache zu verstehen und die Anwendungslogik oder Abfragereihenfolge anzupassen.

5. Ressourcenkonflikte (CPU, Festplatte, Netzwerk)

Selbst mit optimierten Abfragen und richtiger Konfiguration können unzureichende Hardwareressourcen oder Konflikte um diese Ressourcen die Leistung begrenzen.

Identifizieren von Ressourcenengpässen

CPU-Auslastung: Eine hohe CPU-Auslastung durch den mysqld-Prozess kann auf ineffiziente Abfragen, umfangreiche Sortiervorgänge oder unzureichende Rechenleistung hinweisen.
Festplatten-I/O: Eine hohe Festplatten-Lese-/Schreibaktivität, insbesondere bei niedrigen Pufferpool-Trefferquoten, deutet auf Festplatten-I/O als Engpass hin. Achten Sie auf hohe iowait-Zeiten auf Linux-Systemen.
Netzwerkdurchsatz: Übermäßiger Netzwerkverkehr kann bei der Übertragung großer Ergebnismengen oder einer hohen Anzahl von Client-Verbindungen auftreten.

Beheben von Ressourcenengpässen

Hardware-Upgrades: Manchmal ist die einfachste Lösung, CPU, RAM oder schnelleren Speicher hinzuzufügen. Behandeln Sie dies nur als Lösung, nachdem Sie wissen, dass die Arbeitslast angemessen ist; Hardware kann eine schlechte Abfrage verbergen, aber sie lässt sie selten verschwinden.
Abfrageoptimierung: Reduzieren Sie die Menge der verarbeiteten und übertragenen Daten, was indirekt die CPU-, Festplatten- und Netzwerklast verringert.
Verbindungspooling: Implementieren Sie Verbindungspooling in Ihrer Anwendung, um den Overhead des Aufbaus neuer Verbindungen zu reduzieren und die Anzahl der aktiven Verbindungen effektiv zu verwalten.
Lese-Replikate: Erwägen Sie für leseintensive Arbeitslasten die Einrichtung von Lese-Replikaten, um die Leselast vom primären Server zu verteilen.

Ein Triage-Ablauf, der unter Druck funktioniert

Wenn ein Vorfall aktiv ist, beginnen Sie nicht mit einem vollständigen Optimierungsprojekt. Verschaffen Sie sich zuerst einen schnellen Überblick.

Überprüfen Sie aktive Abfragen:

SHOW FULL PROCESSLIST;

Wenn Sie viele Sitzungen sehen, die an derselben Abfrage hängen, erfassen Sie diese. Wenn Sie viele Sitzungen sehen, die auf Sperren warten, beenden Sie nicht wahllos Dinge; identifizieren Sie zuerst die blockierende Transaktion.

Überprüfen Sie den InnoDB-Status:

SHOW ENGINE INNODB STATUS\G

Achten Sie auf Deadlocks, Sperrwartezustände, Checkpoint-Druck und langlaufende Transaktionen. Eine Transaktion, die seit einer Stunde geöffnet ist, kann die Bereinigungsarbeit zurückhalten und nicht zusammenhängende Abfragen verlangsamen.

Überprüfen Sie, ob der Server gesättigt ist:

top
vmstat 1
iostat -xz 1
ss -s

Hohe CPU bei niedrigem I/O deutet normalerweise auf teure Abfrageausführung, Sortierung, Parsing oder zu viel Parallelität hin. Hohes iowait deutet auf Speicher hin. Swap-Aktivität ist eine rote Flagge; MySQL unter Swap-Druck verhält sich oft unvorhersehbar.

Überprüfen Sie dann das Slow-Query-Log der letzten Minuten, nicht nur die Allzeit-schlechteste Abfrage. Die Abfrage, die den heutigen Vorfall verursacht hat, könnte neu sein, mit einem Deployment oder einem Verkehrsmuster verbunden sein, das nur zur Spitzenzeit auftritt.

Verbindungsstürme

Ein häufiger MySQL-Engpass ist nicht eine einzelne schlechte Abfrage, sondern zu viele Anwendungsverbindungen, die kleine Mengen an Arbeit erledigen. Wenn jeder Web-Worker seine eigene Verbindung öffnet und die App plötzlich skaliert, kann MySQL zu viel Zeit mit der Planung von Sitzungen und der Zuweisung von pro-Verbindung-Speicher verbringen.

Zu den Symptomen gehören:

Threads_connected steigt stark an.
Threads_running bleibt hoch.
Anwendungsfehler wie Too many connections.
CPU steigt ohne eine offensichtliche langsame Abfrage.

Nützliche Überprüfungen:

SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW GLOBAL VARIABLES LIKE 'max_connections';

Die Lösung liegt oft in der Anwendungsschicht: Verwenden Sie Verbindungspooling, setzen Sie vernünftige Pool-Limits und machen Sie Timeouts explizit. Die Erhöhung von max_connections kann Zeit erkaufen, kann aber auch dazu führen, dass der Server härter abstürzt, wenn jede Verbindung Speicher für Joins, Sortierungen und temporäre Tabellen verwendet.

Temporäre Tabellen und Sortierungen

Abfragen mit GROUP BY, ORDER BY, DISTINCT oder großen Joins können temporäre Tabellen erstellen. Einige temporäre Tabellen bleiben im Speicher. Größere werden auf die Festplatte ausgelagert. Temporäre Festplattentabellen sind nicht automatisch eine Katastrophe, aber ein plötzlicher Anstieg erklärt oft Latenzspitzen.

Überprüfen Sie:

SHOW GLOBAL STATUS LIKE 'Created_tmp%';

Überprüfen Sie dann die Abfragepläne. Wenn EXPLAIN Using temporary und Using filesort anzeigt, fragen Sie, ob ein Index den Filter und die Sortierung gemeinsam unterstützen kann. Zum Beispiel:

SELECT customer_id, created_at, total
FROM orders
WHERE status = 'paid'
ORDER BY created_at DESC
LIMIT 50;

Ein Index auf (status, created_at) kann sowohl die Filter- als auch die Sortierarbeit reduzieren. Die Erhöhung von tmp_table_size kann in einigen Fällen helfen, aber es ist ein Pro-Sitzung-Risiko. Wenn viele Sitzungen gleichzeitig große temporäre Tabellen zuweisen, verschwindet der Speicher schnell.

Replikationsverzögerung als Leistungssymptom

Wenn Lesevorgänge zu Replikaten gehen, kann eine Replikationsverzögerung wie ein Datenbankleistungsproblem aussehen, selbst wenn der Primäre in Ordnung ist. Benutzer aktualisieren eine Seite und sehen ihre eigene Änderung nicht. Hintergrundjobs lesen veraltete Zeilen. Berichte stimmen nicht überein.

Überprüfen Sie den Replikatstatus mit dem für Ihre MySQL-Version geeigneten Tool:

SHOW REPLICA STATUS\G

Ältere Versionen verwenden:

SHOW SLAVE STATUS\G

Verzögerung kann durch langsame SQL auf dem Replikat, große Transaktionen vom Primären, unzureichende Replikathardware, zeilenweise Wartungsjobs oder Netzwerkprobleme verursacht werden. Die Lösung kann Abfrageoptimierung, das Aufteilen großer Schreibvorgänge in kleinere Blöcke, die Verbesserung der Replikatressourcen oder die Änderung der Routen für frische Lesevorgänge sein.

Was zuerst ändern

Bevorzugen Sie Lösungen, die die Arbeit reduzieren:

Fügen Sie einen Index hinzu oder passen Sie ihn für eine nachgewiesene heiße Abfrage an.
Schreiben Sie eine Abfrage um, um weniger Zeilen zu lesen.
Verkürzen Sie Transaktionen, die Sperren halten.
Begrenzen Sie die Verbindungspoolgröße, damit MySQL nicht überflutet wird.
Verschieben Sie schwere Berichte vom Primären.

Seien Sie vorsichtiger mit Lösungen, die nur die Kapazität erhöhen:

Erhöhung von max_connections.
Globale Erhöhung von Sortier- und Join-Puffern.
Erhöhung der Grenzwerte für temporäre Tabellen.
Hinzufügen von Replikaten, ohne die Abfrage zu beheben, die sie belastet.

Kapazitätsänderungen haben ihren Platz, aber sie sollten auf Beweisen basieren. Eine gute MySQL-Fehlerbehebungssitzung hinterlässt Ihnen eine geringere Menge an Datenbankarbeit, nicht nur einen größeren Server, der dieselbe verschwenderische Arbeit verrichtet.