Warum läuft die WÄHLEN-Abfrage auf meinem Amazon Aurora MySQL DB Cluster langsam?

Kurzbeschreibung

Es gibt mehrere Gründe, warum SELECT-Abfragen auf Ihrem Aurora MySQL-kompatiblen DB Cluster langsam ausgeführt werden können:

• Ihre Amazon Relational Database Service (Amazon RDS) Systemressourcen werden überbeansprucht. Dies kann auf eine überlastete CPU, wenig Arbeitsspeicher oder eine Arbeitslast zurückzuführen sein, die das übersteigt, was Ihr DB-Instance-Typ bewältigen kann.
• Die Datenbank hängt sich auf und die daraus resultierenden Warteereignisse führen dazu, dass SELECT-Abfragen schlecht funktionieren.
• Die SELECT-Abfrage führt vollständige Tabellenscans für große Tabellen durch, oder der Abfrage fehlen die erforderlichen Indizes.
• Die Länge Ihrer InnoDB-Verlaufsliste (HLL) ist aufgrund lang andauernder Transaktionen erheblich gestiegen.

Behebung

Überwachen Sie Ihre Amazon-RDS-Systemressourcen mithilfe von Metriken

Es ist eine bewährte Methode, Ihre CPU-Auslastung und Ihren freien Speicher in Ihrem Amazon-Aurora-Cluster stets zu überwachen. Gelegentliche CPU-Spitzen sind zwar normal, aber eine konstant hohe CPU-Auslastung über einen längeren Zeitraum kann dazu führen, dass Ihre SELECT-Abfragen langsam ausgeführt werden. Verwenden Sie die folgenden Tools, um festzustellen, wie und wo Sie Ihre CPU verwenden:

1.Amazon-CloudWatch-Metriken sind die einfachste Möglichkeit, Ihre CPU-Auslastung zu überwachen. Weitere Informationen darüber, welche Metriken für Aurora verfügbar sind, finden Sie unter CloudWatch-Metriken für Aurora.

2. Die erweiterte Überwachung bietet einen detaillierten Überblick über Metriken auf Betriebssystemebene mit geringerer Granularität. Die detaillierte Aufschlüsselung zeigt, wie die Prozesse Ihre CPU nutzen.

3.Performance Insights bestimmt Ihre DB-Auslastung genau. Schalten Sie Performance Insights für Ihre DB Instance ein und überprüfen Sie dann, ob Ihre Last die maximale vCPU-Auslastung übersteigt. Sie können Ihre lasttragenden Abfragen und SQLs auch anhand von Wartezeiten überwachen und die Benutzer ermitteln, die die maximale Wartezeit verursachen.

SELECT-Abfragen können auch aufgrund von Festplattensuchen langsam ausgeführt werden. Um die Festplatten-I/O zu minimieren, versucht die Datenbank-Engine, den von der Festplatte gelesenen Block zwischenzuspeichern. Das bedeutet, dass, wenn die Datenbank das nächste Mal denselben Datenblock benötigt, dieser Block aus dem Speicher und nicht von der Festplatte abgerufen wird.

Verwenden Sie diese Metriken, um zu überprüfen, ob Sie eine bestimmte Abfrage von der Festplatte oder vom Arbeitsspeicher aus bearbeiten:

• VolumeReadsIOPS: Bei dieser Metrik handelt es sich um die Anzahl der abgerechneten [Festplattenlesevorgänge] auf Volume-Ebene. Es hat sich bewährt, sicherzustellen, dass dieser Wert so niedrig wie möglich ist.
• BufferCacheHitRatio: Diese Metrik gibt den Prozentsatz der Anfragen an, die der Puffercache bedient. Es hat sich bewährt, sicherzustellen, dass dieser Wert so hoch wie möglich ist. Wenn BufferCacheHitRatio sinkt und Ihre SELECT-Anweisung langsam ist, verarbeiten Sie die Abfrage von den zugrunde liegenden Volumes.

Eine weitere wichtige Ressource zur Identifizierung langsamer SELECT-Anweisungen ist das langsame Abfrageprotokoll. Aktivieren Sie die langsame Abfrageprotokollierung für Ihren DB Cluster, um diese Abfragen aufzuzeichnen und später Maßnahmen zu ergreifen. Verwenden Sie für die mit MySQL 5.6 kompatible Version das My SQL Performance Schema, um die Abfrageleistung kontinuierlich zu überwachen.

Identifizieren Sie Blockaden und Warteereignisse

Amazon RDS sperrt die Daten in Ihrer Datenbank, sodass jeweils nur eine Benutzersitzung eine Zeile schreiben oder aktualisieren kann. Jede andere Transaktion, für die diese Zeile erforderlich ist, wird in der Warteschleife gehalten. In einer gemeinsamen Sperre werden die Schreib- und Aktualisierungstransaktionen in der Warteschleife gehalten, während Lesetransaktionen die Daten lesen. Wenn eine Abfrage darauf wartet, auf eine Zeile zuzugreifen, die durch eine andere Abfrage gesperrt ist, kann dies zu einer Blockade führen.

Um Blockaden in Ihrer Datenbank zu identifizieren, aktivieren Sie den Parameter innodb_print_all_deadlocks in Ihren Parametergruppen. Überwachen Sie dann die Datei mysql-error.log von der RDS-Konsole/CLI/API aus.

Oder melden Sie sich mit einem Administratorkonto bei MySQL an und führen Sie dann diesen Befehl aus, um Blockaden anhand der Befehlsausgabe im Abschnitt Latest Detected Deadlock zu identifizieren:

mysql> SHOW ENGINE INNODB STATUS\G;

Prüfen Sie, ob Ihre Abfrage einen Index verwendet

Wenn eine Abfrage keinen Index hat oder vollständige Tabellenscans durchführt, wird die Abfrage langsamer ausgeführt. Indizes helfen dabei, SELECT-Abfragen zu beschleunigen.

Um zu überprüfen, ob Ihre Abfrage einen Index verwendet, verwenden Sie die EXPLAIN-Abfrage. Dies ist ein hilfreiches Tool zur Behebung langsamer Abfragen. Überprüfen Sie in der EXPLAIN-Ausgabe die Tabellennamen, den verwendeten Schlüssel und die Anzahl der während der Abfrage gescannten Zeilen. Wenn die Ausgabe keine verwendeten Schlüssel anzeigt, erstellen Sie einen Index für die in der WHERE-Klausel verwendeten Spalten.

Wenn die Tabelle über die erforderliche Indizierung verfügt, überprüfen Sie, ob die Tabellenstatistiken aktuell sind. Wenn Sie sicherstellen, dass die Statistiken korrekt sind, verwendet der Abfrageoptimierer die selektivsten Indizes mit der richtigen Kardinalität. Dies verbessert die Abfrageleistung.

Überprüfen Sie die Länge der Verlaufsliste (HLL)

InnoDB verwendet ein Konzept namens Multi-version-Concurrency Control (MVCC). MVCC verwaltet mehrere Kopien desselben Datensatzes, um die Lesekonsistenz zu wahren. Das heißt, wenn Sie eine Transaktion ausführen, löscht InnoDB die älteren Kopien. Wenn eine Transaktion jedoch aufgrund der Zunahme der Undo-Segmente über einen längeren Zeitraum nicht bestätigt wird, erhöht sich die Länge der Historie (HLL). Die Länge der InnoDB-Verlaufsliste gibt die Anzahl der Änderungen an, die nicht gelöscht wurden.

Wenn Ihr Workload mehrere offene oder lang andauernde Transaktionen erfordert, können Sie mit einem hohen HLL in der Datenbank rechnen.

**Hinweis:**Langfristige Transaktionen sind nicht die einzige Ursache für HLL-Spitzen. Selbst wenn die Purge-Threads nicht in der Lage sind, mit den Änderungen in der Datenbank Schritt zu halten, kann HLL hoch bleiben.

Wenn Sie die Größe der HLL nicht überwachen, nimmt die Leistung im Laufe der Zeit ab. Eine zunehmende Größe der HLL kann auch zu einem höheren Ressourcenverbrauch, einer langsameren und inkonsistenten Leistung der SELECT-Anweisung und einem Anwachsen des Speichers führen. In extremen Fällen kann dies zu einem Datenbankausfall führen.

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

SHOW ENGINE INNODB STATUS;

Ausgabe:

------------ 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

Für Aurora MySQL ist die Länge der Verlaufslisten aufgrund der Beschaffenheit der Volumes des gemeinsam genutzten Speichers auf Cluster-Ebene und nicht auf Ebene der einzelnen Instances. Stellen Sie eine Verbindung zu Ihrem Schreiber her und führen Sie die folgende Abfrage aus:

SELECT server_id, IF(session_id = 'master_session_id', 'writer', 'reader') AS ROLE, replica_lag_in_msec,
       oldest_read_view_trx_id , oldest_read_view_lsn
       from mysql.ro_replica_status;

Diese Abfrage hilft Ihnen, die Replikatverzögerung zwischen den Leserknoten und dem Schreiberknoten zu verstehen. Es beschreibt auch das älteste LSN, das von der DB Instance zum Lesen aus dem Speicher verwendet wird, und die älteste Lese-View-TRX-ID der DB Instance. Verwenden Sie diese Informationen, um zu überprüfen, ob einer der Leser eine alte Leseansicht hat (im Vergleich zum InnoDB-Engine-Status auf dem Schreiber).

Hinweis: Ab Aurora MySQL 1.19 und 2.06 können Sie HLL mithilfe der RollbackSegmentHistoryListLength-Metrik in CloudWatch überwachen. Oder verwenden Sie in älteren Versionen trx_rseg_history_len, um HLL mit dem folgenden Befehl zu überprüfen:

select NAME AS RollbackSegmentHistoryListLength,
COUNT from INFORMATION_SCHEMA.INNODB_METRICS where NAME = 'trx_rseg_history_len';

Wenn Performance Insights für Ihre Aurora MySQL Instances aktiviert ist, können Sie die RollbackSegmentHistoryListLength überprüfen. Navigieren Sie zum Performance Insight des Writer und gehen Sie wie folgt vor:

1. Wählen Sie Metriken verwalten und dann Datenbankmetriken aus.

2.Wählen Sie die Metrik trx_rseg_history_len und wählen Sie dann Grafik aktualisieren aus.

Verwenden Sie die folgenden Methoden, um Probleme mit dem HL-Wachstum zu lösen:

• Wenn DML (schreibt) das HL-Wachstum verursachen: Wenn Sie diese Anweisung stornieren oder beenden, wird die unterbrochene Transaktion rückgängig gemacht. Dies nimmt viel Zeit in Anspruch, da alle bis zu diesem Zeitpunkt vorgenommenen Aktualisierungen rückgängig gemacht werden.
• Wenn ein READ das HL-Wachstum verursacht: Beenden Sie die Abfrage mit mysql.rds_kill_query.
• Je nachdem, wie lange die Abfrage läuft, überprüfen Sie gemeinsam mit Ihrem DBA, ob Sie die Abfrage mithilfe der gespeicherten Prozedur beenden können.

Es ist eine bewährte Methode, Wachstum zu vermeiden, indem die HLL mithilfe dieser Methoden überwacht wird, und offene oder lang andauernde Transaktionen in der Datenbank zu vermeiden. Außerdem empfiehlt es sich, die Daten in kleineren Batches zu übertragen.

**Wichtig:**Starten Sie den DB Cluster oder die Instance nicht neu. Es ist effizienter, die HLL zu löschen, wenn sie auf die Daten im Speicher im Pufferpool zugreifen kann. Wenn Sie die Datenbank neu starten, geht der Survivable Page Cache möglicherweise verloren. In diesem Fall müssen Datenseiten aus dem Cluster Volume gelesen werden, um die HLL zu löschen. Dies ist langsamer als im Arbeitsspeicher und verursacht zusätzliche I/O-Abrechnungskosten.

---

Weitere Informationen

Protokolle von Amazon Aurora MySQL, Amazon RDS für MySQL und MariaDB mit Amazon CloudWatch überwachen