Wie behebe ich eine langsame Abfrage und verbessere ihre Leistung in Amazon RDS für MySQL?

Kurzbeschreibung

In Amazon RDS können die folgenden Probleme zu einer langsamen Abfrageleistung führen:

• Probleme mit der Workload- und Ressourcenauslastung, z. B. unzureichende Indizierung und ineffiziente Nutzung des Puffer-Pools
• Ineffizienter Abfrageausführungsplan
• Ressourcenkonflikt
• Transaktionen blockieren

Um diese Probleme zu lösen, überprüfe Amazon CloudWatch-Metriken, Performance Insights, Database Insights und Enhanced Monitoring, um Leistungsengpässe zu identifizieren. Behebe dann das Engpassproblem und optimiere deine Abfrageleistung.

Lösung

Wichtig: Performance Insights wird am 30. November 2026 das Ende seiner Lebensdauer erreichen. Du kannst vor dem 30. Juni 2026 ein Upgrade auf den Modus „Erweitert“ von Database Insights durchführen. Wenn du kein Upgrade durchführst, verwenden DB-Cluster, die Performance Insights verwenden, standardmäßig den Modus „Standard“ von Database Insights. Nur der Modus „Erweitert“ von Database Insights unterstützt Ausführungspläne und On-Demand-Analysen. Wenn die Cluster standardmäßig auf den Modus „Standard“ eingestellt sind, kannst du diese Funktionen möglicherweise nicht auf der Konsole verwenden. Informationen zum Aktivieren des Modus „Erweitert“ findest du unter Aktivieren des Modus „Erweitert“ von Database Insights für Amazon RDS und Aktivieren des Modus „Erweitert“ von Database Insights für Amazon Aurora.

Hinweis: Wenn du beim Ausführen von AWS Command Line Interface (AWS CLI)-Befehlen Fehlermeldungen erhältst, findest du weitere Informationen dazu unter Problembehandlung bei der AWS CLI. Stelle außerdem sicher, dass du die neueste Version der AWS CLI verwendest.

Ressourcen- und Datenbankleistung überwachen

Um Probleme mit deiner Abfrageleistung zu beheben, überprüfe die Amazon CloudWatch-Metriken, um die Ursache des Problems zu ermitteln. Um festzustellen, wann eine Abfrage die Nutzung einer bestimmten Ressource erhöht oder die DB-Leistung verringert, verwende die CloudWatch-Konsole oder die AWS-CLI, um die folgenden Metriken zu überwachen:

• DatabaseConnections
• NetworkReceiveThroughput
• WriteThroughput
• ReadThroughput
• WriteLatency
• ReadLatency
• WriteIOPS
• ReadIOPS
• FreeStorageSpace
• BurstBalance

Wenn deine DB-Leistung schlecht ist, überprüfe den Status der RDS-DB-Instance auf aktive oder geplante Prozesse, die die Leistung beeinträchtigen könnten. Überprüfe auch deine Amazon RDS-Ereignisse auf Ereignisse, die sich auf die DB-Leistung auswirken können.

Überprüfe deine Arbeitslast- und Ressourcenauslastung

Wenn deine Abfrageleistung langsam ist, überprüfe die anderen Abfragen in deinem Workload, um festzustellen, ob sie sich auf deine Abfrageleistung auswirken. Um die Abfragen zu identifizieren, die du optimieren musst, kannst du den erweiterten Modus von Database Insights für Amazon RDS oder Amazon Aurora aktivieren.

Wenn deine Instance neu gestartet wird, verliert deine DB-Instance möglicherweise zwischengespeicherte Daten und kann die Abfrageleistung beeinträchtigen. Um dieses Cold-Cache-Problem zu vermeiden, konfiguriere die folgenden Parameter, um den Aufwärmpuffer-Pool nach einem Neustart zu beschleunigen:

• innodb_buffer_pool_dump_at_shutdown
• innodb_buffer_pool_load_at_startup
• innodb_buffer_pool_dump_pct

Um die Abfrageleistung zu optimieren, empfiehlt es sich, zu überwachen, wie stark deine DB-Instance den InnoDB-Puffer-Pool nutzt. Weitere Informationen findest du unter Puffer-Pool auf der MySQL-Website. Um den Status des InnoDB-Puffer-Pools zu überwachen, überprüfe die folgenden Datenbankzähler von Performance Insights:

• Die Anzahl der logischen Leseanforderungen findest du im Zähler Innodb_buffer_pool_read_requests.
• Die Anzahl der logischen Lesevorgänge, die InnoDB aus dem Puffer-Pool nicht ausführen kann und die direkt von der Festplatte gelesen werden mussten, findest du unter Innodb_buffer_pool_reads.
• Verwende Innodb_buffer_pool_hit_ratio für den Prozentsatz der Lesevorgänge, die InnoDB aus dem Puffer-Pool erfüllen kann.
• Überprüfe Innodb_buffer_pool_usage für den Prozentsatz des InnoDB-Puffer-Pools, der Datenseiten enthält.

Um langsam laufende Abfragen zu identifizieren, kannst du auch slow_query_log in deiner Parametergruppe aktivieren und die Protokolle dann in CloudWatch Logs veröffentlichen.

Abfrageleistung optimieren

Um deine Abfrageleistung zu optimieren, führe die folgenden Befehle entsprechend den Anforderungen deines Abfrageausführungsplans aus. Weitere Informationen findest du unter EXPLAIN-Ausgabeformat auf der MySQL-Website.

EXPLAIN verwenden, um deine Abfragen zu optimieren

Führe den EXPLAIN-Befehl aus, um Details zur Abfrageleistung anzuzeigen und zu erfahren, warum die Abfrage möglicherweise verzögert wird. Weitere Informationen findest du unter Optimieren von Abfragen mit EXPLAIN auf der MySQL-Website.

Um zu ermitteln, ob deine Abfrage einen Index verwendet, führe die EXPLAIN-Abfrage aus. Überprüfe in der EXPLAIN-Ausgabe die Tabellennamen, die verwendeten Schlüssel und die Anzahl der Zeilen, die die Abfrage gescannt hat. Weitere Informationen findest du unter EXPLAIN-Anweisung auf der MySQL-Website. Prüfe die Ausgabe und ergreife dann die folgenden Maßnahmen:

• Wenn die Ausgabe die verwendeten Schlüssel nicht anzeigt, erstelle einen Index für die Spalten in der WHERE-Klausel.
• Wenn die Tabelle über die erforderliche Indizierung verfügt, stelle sicher, dass die Tabellenstatistiken aktuell sind. Weitere Informationen findest du unter Die Tabelle INFORMATION_SCHEMA STATISTICS auf der MySQL-Website.

ANALYZE TABLE verwenden, um deine Abfragestatistiken zu aktualisieren

Wenn deine Tabellenstatistiken nicht aktuell sind, kann die Abfrage eine schlechte Leistung aufweisen. Führe den ANALYZE TABLE-Befehl aus, um deine Abfragestatistiken zu aktualisieren. Weitere Informationen findest du unter ANALYZE TABLE statement (ANALYZE TABLE-Anweisung) auf der MySQL-Website.

EXPLAIN ANALYZE verwenden, um zu sehen, wie deine Abfragen Zeit einteilen

Um zu ermitteln, welcher Teil der Abfrageausführung langsam ist, führe die EXPLAIN ANALYZE-Abfrage aus, um zu sehen, wie MySQL deiner Abfrage Zeit zuweist. Wenn die Abfrage abgeschlossen ist, druckt die EXPLAIN ANALYZE-Abfrage den Plan und seine Maße aus. Weitere Informationen findest du unter Informationen mit EXPLAIN ANALYZE abrufen auf der MySQL-Website. Du kannst SHOW PROFILE auch verwenden, um deine langsameren Abfragen zu profilieren und den Status zu ermitteln, in dem die Sitzung die meiste Zeit verbringt. Weitere Informationen findest du unter SHOW PROFILE statement (SHOW PROFILE-Anweisung) auf der MySQL-Website.

SHOW FULL PROCESSLIST und Enhanced Monitoring verwenden, um Vorgänge zu überprüfen

Führe den SHOW FULL PROCESSLIST-Befehl aus, um die Liste der Vorgänge anzuzeigen, die auf dem Datenbankserver ausgeführt werden. Du kannst auch Enhanced Monitoring verwenden, um diese Liste zu überprüfen. Weitere Informationen findest du unter SHOW PROCESSLIST-Anweisung auf der MySQL-Website.

Überprüfen der Länge der Verlaufsliste

Das InnoDB-Transaktionssystem unterhält die Multi-Version Concurrency Control (MVCC). Wenn die Workload mehrere offene Transaktionen oder Transaktionen mit langer Ausführungszeit erfordert, musst du mit einer langen Verlaufsliste in der Datenbank rechnen. Es ist eine bewährte Methode, offene oder lang andauernde Transaktionen in der Datenbank zu vermeiden. Weitere Informationen findest du unter Die Länge der InnoDB-Verlaufsliste wurde erheblich erhöht.

Wenn du die Länge deiner Verlaufsliste nicht überwachst, nimmt deine Leistung mit der Zeit ab. Eine lange Verlaufsliste kann auch zu einer hohen Ressourcenauslastung, einer langsamen und inkonsistenten SELECT-Leistung und einer Erhöhung des Speicherplatzes führen.

Hinweis: Transaktionen mit langer Ausführungszeit sind nicht die einzige Ursache für Spitzen in der Länge der Verlaufsliste. Wenn Purge-Threads mit den Änderungen in der Datenbank nicht übereinstimmen, bleibt die Verlaufsliste sehr lang. In extremen Fällen kann es auch zu einem Datenbankausfall kommen.

Der Befehl SHOW ENGINE INNODB STATUS zeigt Informationen zur Transaktionsverarbeitung, zu Warteereignissen und Deadlocks an. Weitere Informationen findest du unter SHOW ENGINE-Anweisung auf der MySQL-Website. Führe die SHOW ENGINE INNODB STATUS-Abfrage aus, um die Länge deiner Verlaufsliste zu überprüfen:

SHOW ENGINE INNODB STATUS;

Beispielausgabe:

\------------ TRANSACTIONS ------------Trx id counter 26368570695  Purge done for
 trx's n:o < 26168770192 undo n:o < 0 state: running but idle History list length 1839

Gehe wie folgt vor, um mithilfe von Performance Insights die Länge deiner Verlaufsliste zu überprüfen:

1. Öffne die Amazon RDS-Konsole.
2. Wähle im Navigationsbereich Performance Insights und wähle dann die Datenbank aus, für die du Metriken anzeigen möchtest.
3. Wähle Metriken aus.
4. Wähle auf der Seite „Metrik-Dashboard“ die Option Benutzerdefiniertes Dashboard aus.
5. Wähle Widget hinzufügen und dann die Metrik Trx Rseg History Len aus.
6. Wähle Widget hinzufügen aus.

Hinweis: Wenn DML-Schreibvorgänge (Data Manipulation Language) dazu führen, dass die Länge der Verlaufsliste zunimmt, bitte deinen Datenbankadministrator, ](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/mysql-stored-proc-ending.html)die Schreibabfragen zu beenden[.

Blockierte Abfragen auflösen

Wenn deine Abfrage über einen längeren Zeitraum ausgeführt wird, blockiert möglicherweise eine andere Abfrage deine Abfrage. In MySQL 8.0 findest du Sperrwartezeiten im Leistungsschema der Tabelle data_lock_waits. Weitere Informationen findest du unter Verwenden von InnoDB-Transaktions- und Sperrinformationen auf der MySQL-Website. Führe die folgende Abfrage aus, um blockierende Transaktionen zu identifizieren:

SELECT
  r.trx\_id waiting\_trx\_id,    
  r.trx\_mysql\_thread\_id waiting\_thread,      
  r.trx\_query waiting\_query,    
  b.trx\_id blocking\_trx\_id,    
  b.trx\_mysql\_thread\_id blocking\_thread,    
  b.trx\_query blocking\_query    
FROM       performance\_schema.data\_lock\_waits w    
INNER JOIN information\_schema.innodb\_trx b    
  ON b.trx\_id = w.blocking\_engine\_transaction\_id    
INNER JOIN information\_schema.innodb\_trx r    
  ON r.trx\_id = w.requesting\_engine\_transaction\_id;

Ähnliche Informationen

Wie behebe ich Fehler bei einer RDS für MySQL- oder MariaDB-Instance, bei der „storage full“ angezeigt wird?

Warum wurde eine Abfrage an meine Amazon RDS für MySQL-DB-Instance blockiert, obwohl es keine andere aktive Sitzung gibt?