Wie behebe ich eine langsam ausgeführte Abfrage und verbessere meine Abfrageleistung in Amazon RDS für MySQL?

Lösung

Gehe wie folgt vor, um Probleme mit einer langsam ausgeführten Abfrage zu beheben und die Abfrageleistung zu verbessern:

Überprüfung der Ressourcenauslastung

Gehe wie folgt vor, um die Ressourcenauslastung zu überwachen und festzustellen, wann die Abfrageleistung abnimmt:

• Verwende Amazon CloudWatch-Metriken, um die Ressourcen über einen bestimmten Zeitraum zu überwachen.
• Um die Datenbankleistung zu überwachen, verwende die Amazon RDS-Konsole, um die Leistungsmetriken anzuzeigen.
• Überprüfe den Instance-Status, um aktive oder geplante Prozesse zu identifizieren, die sich auf die Datenbankleistung auswirken könnten. Stelle sicher, dass du die Amazon RDS-Ereignisse überprüfst, die aufgetreten sind, als die Datenbankleistung schlecht war.

Überprüfung der Workload, die zum Ressourcenverbrauch beiträgt

Verwende Performance Insights auf Amazon RDS, um den Workload zu überprüfen, der zum Ressourcenverbrauch beiträgt. Wenn die aktuelle Workload das vCPU-Limit überschreitet, ist dein Server überlastet. Wenn der Server überlastet ist, überprüfe die Abfragen, die zu der Workload beitragen, und optimiere deine Abfragen. Ändere dann die Instance-Klasse.

Um herauszufinden, welche Ressource die meisten Warteereignisse verbraucht, teile die DB-Last nach der Anzahl der Warteereignisse auf. Dickere Farbbänder im Belastungsdiagramm zeigen die Wartearten an, die am meisten zu der Workload beitragen. Weitere Informationen findest du unter Überwachen der DB-Belastung mit Performance Insights auf Amazon RDS.

Um langsame Abfragen zu identifizieren, aktiviere slow_query_log in der Parametergruppe. Um zu überprüfen, ob die Workload der Instance gestiegen ist, überprüfe anhand der CloudWatch-Metriken Folgendes:

• Datenbankverbindungen: Die Anzahl der Client-Sitzungen, die mit der DB-Instance verbunden sind.
• Netzwerkempfangsdurchsatz (MB/Sekunde): Die Rate des Netzwerkdatenverkehrs zur und von der DB-Instance.
• Schreib- und Lesedurchsatz: Die durchschnittliche Anzahl von Megabyte, die pro Sekunde von der Festplatte gelesen oder auf die Festplatte geschrieben werden.
• Schreib- und Leselatenz: Die durchschnittliche Zeit für einen Lese- oder Schreibvorgang in Millisekunden. Die Korrelation von Latenzmetriken mit erhöhten Datenbankverbindungen oder Durchsatzmetriken könnte darauf hindeuten, dass die Workload die Ursache für langsame Abfragen ist. Weitere Informationen findest du unter Wie behebe ich Fehler bei einer RDS für MySQL- oder MariaDB-Instance, bei der „storage full“ (voller Speicher) angezeigt wird?
• IOPS (lesen und schreiben): Die durchschnittliche Anzahl von Lese- oder Schreibvorgängen auf der Festplatte pro Sekunde.
• Freier Speicherplatz (MB): Die Menge an Festplattenspeicher, die derzeit nicht von der DB-Instance verwendet wird.
• Burst-Balance (%) – Der Prozentsatz des verfügbaren Burst-Bucket-E/A-Guthabens für Allzweck-SSDs (gp2)

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

Prüfen, ob Abfragen einen Index haben oder vollständige Tabellenscans durchführen

Wenn deine Abfrage einen Index hat oder vollständige Tabellenscans durchführt, wird die Abfrage langsam ausgeführt.

Um zu überprüfen, ob die Abfrage einen Index verwendet, führe die EXPLAIN (ERKLÄR)-Abfrage aus. Weitere Informationen findest du unter EXPLAIN Statement (ERKLÄR-Anweisung) auf der MySQL-Website. Überprüfe in der EXPLAIN-Ausgabe die Tabellennamen, die verwendeten Schlüssel und die Anzahl der Zeilen, die für die Abfrage durchsucht werden. 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. Weitere Informationen findest du unter The INFORMATION_SCHEMA STATISTICS Table (Die INFORMATION_SCHEMA STATISTICS-Tabelle) auf der MySQL-Website. Wenn die Statistiken aktuell sind, verwendet der Abfrageoptimierer die selektivsten Indizes mit der richtigen Kardinalität. Infolgedessen verbessert sich die Abfrageleistung.

Ü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 nimmt die Länge der Verlaufsliste zu, während andere Transaktionen die Tabellen ändern, die von der Transaktion mit langer Ausführungszeit verwendet werden.

Wenn die Workload 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 Ressourcenverbrauch, einer langsamen und inkonsistenten SELECT-Anweisung (AUSWAHL-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 Schritt halten können, 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;

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

Wenn für die Amazon RDS für MySQL-Instances Performance Insights aktiviert ist, gehe wie folgt vor, um die RollbackSegmentHistoryListLength zu überprüfen:

1. Navigiere zum Writer Performance Insight.
2. Wähle Metriken verwalten und dann Datenbank-Metriken aus.
3. Wähle die Metrik trx_rseg_history_len und wähle dann Diagramm aktualisieren aus.

Gehe wie folgt vor, um Probleme zu beheben, die zu einer längeren Verlaufsliste führen:

• Wenn DML-Schreibvorgänge zu einer Erhöhung der Länge der Verlaufsliste führen, brich die Schreibanweisungen ab oder halte sie an. Dies beinhaltet ein Rollback der unterbrochenen Transaktion und nimmt viel Zeit in Anspruch, um die Updates rückgängig zu machen.
• Wenn ein READ (Schreibvorgang) die Länge der Verlaufsliste erhöht, verwende mysql.rds_kill_query, um die Abfrage anzuhalten.
• Wenn die Abfrage lange ausgeführt wird, prüfe gemeinsam mit deinem DBA, ob du die Abfrage anhalten kannst.

Hinweis: Es hat sich bewährt, offene Transaktionen oder solche mit langer Ausführungszeit in der Datenbank zu vermeiden und die Daten in kleinen Batches zu übertragen.

Verbesserung der Abfrageleistung

Verwende die folgenden bewährten Methoden, um die Abfrageleistung zu verbessern:

• Um die Zustände zu finden, in denen die meiste Zeit verbracht wird, profiliere deine langsameren Abfragen. Weitere Informationen findest du unter SHOW PROFILE statement (PROFIL ANZEIGEN-Anweisung) auf der MySQL-Website.

• Führe den Befehl SHOW FULL PROCESSLIST zusammen mit Enhanced Monitoring aus. Wenn sie zusammen verwendet werden, kannst du die Liste der Operationen überprüfen, die derzeit auf dem Datenbankserver ausgeführt werden.

• Verwende den Befehl SHOW ENGINE INNODB STATUS, um Informationen über die Transaktionsverarbeitung, Warteereignisse und Deadlocks abzurufen.

• Suche und löse blockierende Abfragen. 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?

• Veröffentliche MySQL-Protokolle in CloudWatch Logs. Die Protokolle rotieren jede Stunde, um den Schwellenwert von 2 % des zugewiesenen Speicherplatzes beizubehalten. Protokolle, die älter als zwei Wochen sind oder deren Gesamtgröße den Schwellenwert von 2 % überschreitet, werden entfernt.

• Richte einen CloudWatch-Alarm ein, um die Ressourcennutzung zu überwachen und sich benachrichtigen zu lassen, wenn Schwellenwerte überschritten werden.

• Suche den Abfrage-Operationsplan und überprüfe, 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 Optimizing Queries with EXPLAIN (Optimieren von Abfragen mit EXPLAIN) auf der MySQL-Website.

• Halte die Abfragestatistiken mit ANALYZE TABLE (Tabellenstatistiken) auf dem neuesten Stand. Weitere Informationen findest du unter ANALYZE TABLE Statement (TABELLENSTATISTIKEN-Anweisung) auf der MySQL-Website.

• MySQL 8.0 verwendet EXPLAIN ANALYZE. Die EXPLAIN ANALYZE-Anweisung zeigt, wo MySQL der Abfrage Zeiten zuweist und warum diese Zeit zugewiesen wird. Wenn die Abfrage abgeschlossen ist, druckt EXPLAIN ANALYZE den Plan und seine Messungen aus. Weitere Informationen findest du unter Obtaining Information with EXPLAIN ANALYZE (Informationen mit EXPLAIN ANALYZE abrufen) auf der MySQL-Website.

• In MySQL 8.0 sind Sperrwartezeiten im Leistungsschema der Tabelle data_lock_waits aufgeführt. Weitere Informationen findest du unter Using InnoDB Transaction and Locking Information (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;