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

Lösung

Ressourcen- und Datenbankleistung überwachen

Verwende Amazon CloudWatch, um zu ermitteln, wann die Abfrage die Ressourcenauslastung erhöht.

Überwache die folgenden CloudWatch-Metriken:

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

Verwende die Amazon RDS-Konsole, um zu ermitteln, wann die Abfrage die Datenbankleistung beeinträchtigt. Überprüfe außerdem den Status der RDS-DB-Instance, um aktive oder geplante Prozesse zu identifizieren, die sich auf die Datenbankleistung auswirken könnten. Prüfe die Amazon RDS-Ereignisse, die auftreten, wenn die Datenbankleistung schlecht ist.

Verwende Enhanced Monitoring, um die Liste der Betriebssysteme (OSs) in der Workload und die Systemmetriken anzuzeigen. Standardmäßig beträgt das Intervall für Enhanced Monitoring 60 Sekunden. Es hat sich bewährt, für detailliertere Datenpunkte ein Intervall von 1–5 Sekunden festzulegen.

Überprüfung der Workload, die zur Ressourcenauslastung beiträgt

Nachdem du die Zeiten festgelegt hast, zu denen die Abfrage die Ressourcenauslastung erhöht oder die Leistung beeinträchtigt, aktiviere Performance Insights, um die Abfragen zu identifizieren, die du optimieren musst.

Wichtig: Performance Insights wird am 30. November 2025 sein End of Life erreichen. Du kannst vor dem 30. November 2025 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 „Standard“ findest du unter Einschalten des Modus „Erweitert“ von Database Insights für Amazon RDS und Einschalten des Modus „Erweitert“ von Database Insights für Amazon Aurora.

Gehe wie folgt vor, um die Protokolle der Workload zu überprüfen:

1. Greife im Performance Insights-Dashboard auf das Datenbankbelastungsdiagramm zu.
2. Überprüfe die Spalte „Auslastung nach Wartezeiten“ (AAS), um die Workload zu ermitteln, die zu dem Zeitpunkt, zu dem die Ressourcenauslastung steigt oder die Abfrage die Leistung beeinträchtigt, die meisten Warteereignisse verwendet.
   Hinweis: Größere Farbbänder im Belastungsdiagramm zeigen die Wartearten an, die am meisten zu der Workload beitragen. Wenn die Workload ständig die maximale vCPU überschreitet, ändere die Instance-Klasse.
3. Suche auf der Registerkarte „Top SQL“ nach den einzelnen Abfragen, die zur Workload beitragen.

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

Verbesserung der Abfrageleistung

EXPLAIN-Anweisung verwenden

Überprüfe den Abfrage-Operationsplan, um festzustellen, ob die Abfrage die entsprechenden Indizes verwendet. Verwende EXPLAIN, um die Abfrage zu optimieren und Details darüber anzuzeigen, wie MySQL die Abfrage ausführt. Weitere Informationen findest du unter Optimieren von Abfragen mit EXPLAIN auf der MySQL-Website.

Um vollständige Tabellenscans zu vermeiden, führe die EXPLAIN-Anweisung aus, um zu ermitteln, ob die Abfrage einen Index verwendet. Überprüfe in der EXPLAIN-Ausgabe die Tabellennamen, die Schlüssel, die verwendet werden und die Anzahl der Zeilen, die für die Abfrage durchsucht werden. Weitere Informationen findest du unter EXPLAIN-Anweisung auf der MySQL-Website.

Wenn die Ausgabe keine verwendeten Schlüssel anzeigt, erstelle einen Index für die in der WHERE (WO)-Klausel verwendeten Spalten. Wenn die Tabelle über die erforderliche Indizierung verfügt, überprüfe, ob die Tabellenstatistiken aktuell sind. Wenn die Statistiken aktuell sind, verwendet der Abfrageoptimierer die selektivsten Indizes mit der richtigen Kardinalität. Infolgedessen verbessert sich die Abfrageleistung. Weitere Informationen findest du unter Die Tabelle INFORMATION_SCHEMA STATISTICS auf der MySQL-Website.

ANALYZE TABLE verwenden

Halte die Abfragestatistiken mit ANALYZE TABLE auf dem neuesten Stand. Weitere Informationen findest du unter ANALYZE TABLE-Anweisung auf der MySQL-Website.

EXPLAIN ANALYZE verwenden

Verwende für MySQL 8.0 EXPLAIN ANALYZE. Die EXPLAIN ANALYZE-Anweisung zeigt, wo MySQL der Abfrage Zeiten zuweist und warum die Zeit zugewiesen wird. Wenn die Abfrage abgeschlossen ist, druckt EXPLAIN ANALYZE den Plan und seine Messungen aus. Weitere Informationen findest du unter Informationen mit EXPLAIN ANALYZE abrufen auf der MySQL-Website.

Überprüfen der Länge der Verlaufsliste

InnoDB verwendet Multiversion Concurrency Control (MVCC). MVCC verwaltet mehrere Kopien desselben Datensatzes, um die Lesekonsistenz zu wahren. Die Länge der Verlaufsliste ist die Gesamtzahl der Undo-Protokolle, die Modifikationen in der Verlaufsliste enthalten. Wenn eine Transaktion mit langer Ausführungszeit Daten schreibt oder liest, erhöht sich die Länge der Verlaufsliste, bis die Transaktion abgeschlossen ist oder ein Rollback erfolgt. Außerdem erhöht sich die Länge der Verlaufsliste, wenn andere Transaktionen die Tabellen ändern, die die Transaktion mit langer Laufzeit verwendet.

Es ist eine bewährte Methode, offene oder lang andauernde Transaktionen in der Datenbank zu vermeiden. Übergib die Daten stattdessen in kleinen Batches. Wenn die Workload jedoch mehrere offene oder Transaktionen mit langer Ausführungszeit erfordert, musst du mit einer langen Verlaufsliste in der Datenbank rechnen.

Wenn du die Länge der Verlaufsliste nicht überwachst, kann die Leistung im Laufe der Zeit abnehmen. Eine sehr lange Verlaufsliste kann auch zu einem hohen Ressourcenauslastung, einer langsamen und inkonsistenten SELECT-Anweisung und einer Erhöhung des Speichers 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.

Führe den folgenden Befehl aus, um die Länge der Verlaufsliste zu überprüfen:

SHOW ENGINE INNODB STATUS;

Hinweis: Die Anweisung SHOW ENGINE INNODB STATUS ruft Informationen über die Transaktionsverarbeitung, Warteereignisse und Deadlocks ab. Weitere Informationen findest du unter SHOW ENGINE-Anweisung auf der MySQL-Website.

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 der 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 die Registerkarte Metriken,
4. Wähle im Menü des Metriken-Dashboards die Option Benutzerdefiniertes Dashboard.
5. Wähle Widget hinzufügen und suche dann nach der Metrik Trx Rseg History Len und wähle sie aus.
6. Wähle Widget hinzufügen.

Wenn DML-Schreibvorgänge dazu führen, dass die Länge der Verlaufsliste zunimmt, bitte den Datenbankadministrator, die Schreibanweisungen zu beenden.

Hinweis: Es dauert lange, die Aktualisierungen für die unterbrochene Transaktion rückgängig zu machen. 

Blockierte Abfragen auflösen

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.

Beispiel:

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;

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

Die Anweisung SHOW PROFILE verwenden

Profiliere die langsameren Abfragen, um den Status zu ermitteln, in dem die Sitzung die meiste Zeit verbringt. Weitere Informationen findest du unter SHOW PROFILE-Anweisung auf der MySQL-Website.

Die Anweisung SHOW FULL PROCESSLIST verwenden

Führe die Anweisung SHOW FULL PROCESSLIST aus und verwende Enhanced Monitoring, um die Liste der Vorgänge zu überprüfen, die du derzeit auf dem Datenbankserver ausführst. Weitere Informationen findest du unter SHOW PROCESSLIST-Anweisung auf der MySQL-Website.

Ähnliche Informationen

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