Warum läuft meine Abfrage in Amazon RDS for MySQL langsam?

Kurzbeschreibung

Um die Abfrageleistung zu verbessern, sollten Sie die folgenden Faktoren berücksichtigen:

• Ressourcennutzung (z. B. CPU, Arbeitsspeicher und Speicher)
• Workload-Analyse
• Abfragetuning und -überwachung

Auflösung

Ressourcennutzung (z. B. CPU, Arbeitsspeicher und Speicher)

Um die Ursache von Leistungsproblemen der Datenbank zu verstehen, überprüfen Sie alle serverweiten Ressourcen, die Ihre Instance verwendet. Sie können Ihren Workload überwachen und feststellen, wann die Abfrageleistung normal war und wann die Abfrage zu lange dauerte.

Verwenden Sie Amazon CloudWatch-Metriken, um diese Ressourcen über einen Zeitraum zu überwachen, der auch Tage umfasst, an denen die Leistung als normal angesehen wurde. Sie können auch Performance-Metriken in der Amazon RDS-Konsole anzeigen, um die Datenbankleistung zu überwachen.

Sie können auch den Status Ihrer Instance überprüfen, um andere aktive oder geplante Prozesse zu identifizieren, die die Datenbankleistung beeinträchtigen könnten. Überprüfen Sie in der Amazon RDS-Konsole die Ereignisse, die aufgetreten sind, während Ihre Datenbank schlecht funktionierte.

Workload-Analyse

Um den Workload zu analysieren, die zum Ressourcenverbrauch beiträgt, verwenden Sie Performance Insights. Performance Insights liefert eine grafische Analyse all Ihrer Abfragen und aller Wartezeiten, die zu einem erhöhten Ressourcenverbrauch beitragen.

Performance Insights verwendet den Workload als Hauptmetrik, anstatt die Anzahl der vCPUs einer Instance zu verwenden. Wenn Ihrem aktuellen Workload das vCPU-Limit überschreitet, ist Ihr Server überlastet. Wenn Ihr Server überlastet ist, prüfen Sie die Abfragen, die zu Ihrem Workload beitragen, und ermitteln Sie Möglichkeiten zur Optimierung Ihrer Abfragen. Dann erwägen Sie, Ihre Instance-Klasse zu ändern.

Ihre Performance Insights-Workload kann auch in Warteereignisse unterteilt werden. Untersuchen Sie die ressourcenintensivsten Wartezeiten, indem Sie die DB-Last nach der Anzahl der Warteereignisse aufteilen. Die dickeren Farbbänder im Belastungsdiagramm zeigen die Wartetypen an, die am meisten zum Workload beitragen. Weitere Informationen finden Sie unter Überwachung der DB-Last mit Performance Insights auf Amazon RDS.

Sie können auch das Protokoll für langsame Abfragen verwenden (aktiviert in Ihrer benutzerdefinierten Parametergruppe), um langsam laufende Abfragen zu identifizieren.

Sie können dann die Amazon CloudWatch-Metriken verwenden, um zu überprüfen, ob sich der Arbeitsaufwand für Ihre Instance erhöht hat. Beispiel:

• Datenbank-Verbindungen: Die Anzahl der Client-Sitzungen, die mit der DB-Instance verbunden sind.
• Netzwerk-Empfangsdurchsatz (MB/Sekunde): Die Rate des Netzwerkverkehrs zur und von der DB-Instance.
• Schreib- und Lesedurchsatz: Die durchschnittliche Anzahl der Megabytes, die pro Sekunde von der Festplatte gelesen oder auf die Festplatte geschrieben werden.
• Schreib- und Lese-Latenzzeit: Die durchschnittliche Zeit für einen Lese- oder Schreibvorgang in Millisekunden.
• IOPS (Lesen und Schreiben): Die durchschnittliche Anzahl von Lese- oder Schreibvorgängen pro Sekunde.
• Freier Speicherplatz (MB): Die Menge an Speicherplatz, die derzeit nicht von der DB-Instance verwendet wird.

Die Latenzmetrik gibt die Zeit an, die für den Abschluss von Lese- oder Schreibvorgängen auf der Festplatte benötigt wird. Die Korrelation der Latenzmetriken mit entweder erhöhten Datenbankverbindungen oder Durchsatzmetriken könnte auf den Workload als Grund für die langsame Abfrageausführung hinweisen. Weitere Informationen zur Identifizierung von Nutzungsfaktoren finden Sie unter Wie kann ich anzeigen, was in einer Amazon RDS DB-Instance, auf der MySQL ausgeführt wird, Speicherplatz verwendet?

Sie können auch Enhanced Monitoring verwenden, um die Liste der an Ihrem Workload beteiligten Betriebssysteme und die zugrunde liegenden Systemmetriken abzurufen. Standardmäßig beträgt das Überwachungsintervall für die erweiterte Überwachung 60 Sekunden. Es ist eine bewährte Methode, diese Einstellung auf 1-5-Sekunden-Intervalle zu setzen, um genauere Datenpunkte zu erhalten.

Abfrageoptimierung

Nachdem die langlaufende Abfrage durch ein langsames Abfrageprotokoll oder Performance Insights identifiziert wurde, sollten Sie überlegen, wie Sie die Abfrageleistung verbessern können. Um eine Abfrage abzustimmen, sollten Sie die folgenden Ansätze in Betracht ziehen:

• Um die Zustände zu ermitteln, in denen die meiste Zeit verbracht wird, erstellen Sie ein Profil Ihrer langsameren Abfragen. Weitere Informationen finden Sie unter SHOW PROFILE Statement auf der MySQL-Website.
• Führen Sie den Befehl SHOW FULL PROCESSLIST zusammen mit Enhanced Monitoring aus. Bei gemeinsamer Verwendung können Sie die Liste der Operationen einsehen, die derzeit auf dem Datenbankserver durchgeführt werden.
• Verwenden Sie den Befehl SHOW ENGINE INNODB STATUS, um Informationen über Transaktionsverarbeitung, Wartezeiten und Deadlocks zu erhalten.
• Finden Sie blockierende Abfragen und lösen Sie die Blockierung auf. Weitere Informationen finden Sie unter Warum wurde eine Abfrage an meine Amazon RDS for MySQL DB-Instance blockiert, wenn keine andere aktive Sitzung vorhanden ist?
• Veröffentlichen von MySQL-Protokollen auf Amazon CloudWatch. Die Protokolle werden stündlich rotiert, um den Schwellenwert von 2 % des zugewiesenen Speicherplatzes einzuhalten. Sie werden dann gelöscht, wenn sie mehr als zwei Wochen alt sind oder wenn ihre Gesamtgröße den Schwellenwert von 2 % überschreitet.
• Legen Sie einen Amazon CloudWatch-Alarm fest, damit Sie Ihre Ressourcennutzung überwachen können und bei Überschreitung von Schwellenwerten benachrichtigt werden.
• Suchen Sie den Ausführungsplan für die Abfrage und prüfen Sie, ob die Abfrage geeignete Indizes verwendet. Sie können Ihre Abfrage mit Hilfe des EXPLAIN-Plans optimieren und Details darüber prüfen, wie MySQL die Abfrage ausführt.
• Halten Sie Ihre Abfragestatistiken mit der Tabellenanweisung ANALYZE auf dem neuesten Stand. Abfrageoptimierer können aufgrund veralteter Statistiken manchmal schlechte Ausführungspläne wählen. Dies kann zu einer schlechten Leistung einer Abfrage führen, da die Kardinalität sowohl der Tabelle als auch der Indizes ungenau geschätzt wird.
• MySQL 8.0 verwendet jetzt eine EXPLAIN ANALYZE-Anweisung. Die EXPLAIN ANALYZE-Anweisung ist ein Profiling-Werkzeug für Ihre Abfragen, das Ihnen zeigt, wo MySQL Zeit für Ihre Abfrage aufwendet und warum. Mit EXPLAIN ANALYZE plant, instrumentiert und führt MySQL die Abfrage aus, während es die Zeilen zählt und die an verschiedenen Punkten des Ausführungsplans verbrachte Zeit misst. Wenn die Abfrage abgeschlossen ist, druckt EXPLAIN ANALYZE den Plan und seine Messungen anstelle des Abfrageergebnisses.
• In MySQL Version 8 ist zu beachten, dass Sperrsperren im Leistungsschema der Tabelle data_lock_waits aufgeführt sind. 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 finden Sie unter Verwendung von InnoDB-Transaktionen und Sperrinformationen auf der MySQL-Website.

---

Relevante Informationen

Abstimmung von Amazon RDS for MySQL mit Performance Insights

Wie behebe ich eine übermäßig hohe CPU-Auslastung in meinen Instances von Amazon RDS for MySQL, MariaDB oder Aurora for MySQL?