Wie identifiziere und behebe ich Leistungsprobleme und langsam laufende Abfragen in meiner RDS-for-PostgreSQL- oder Aurora-PostgreSQL-Instance?

Lösung

Die Leistung Ihrer Amazon RDS for PostgreSQL-Instance kann aus mehreren Gründen beeinträchtigt werden, z. B.:

• Unterdimensionierte Hardware
• Änderungen der Arbeitslast
• Zunehmender Verkehr
• Speicherprobleme
• Suboptimale Abfragepläne

Identifizieren Sie die Ursache

Verwenden Sie eine Kombination dieser Tools, um die Ursache für langsam laufende Abfragen zu ermitteln:

• Amazon CloudWatch-Metriken
• Enhanced-Monitoring-Metriken
• Performance-Insights-Metriken
• Datenbankstatistiken
• Native Datenbanktools

CloudWatch-Metriken

Um Leistungsengpässe zu identifizieren, die durch unzureichende Ressourcen verursacht werden, überwachen Sie diese allgemeinen CloudWatch-Metriken, die für Ihre Amazon RDS-DB-Instance

• CPU-Auslastung – Prozentsatz der genutzten Computerverarbeitungskapazität
• FreeableMemory – Verfügbarer RAM auf der DB-Instance (in Megabyte)
• SwapUsage – SwapSpace, der von der DB-Instance verwendet wird (in Megabyte)

Ein höherer Prozentsatz der CPU-Auslastung weist im Allgemeinen auf eine aktive Arbeitslast auf der Instance und den Bedarf an mehr CPU-Ressourcen hin. Eine höhere Speicherauslastung zusammen mit dem Verbrauch von Swap-Speicherplatz deutet auf häufiges Austauschen aufgrund der geringen Speicherverfügbarkeit für den Workload hin. Dies könnte bedeuten, dass Ihre Instanz mit der Arbeitslast nicht Schritt halten kann. Lange laufende Abfragen, plötzliche Spitzen im Datenverkehr oder eine hohe Anzahl von Leerlaufverbindungen sind häufig die Ursache für einen übermäßigen CPU- und Speicherverbrauch.

Führen Sie diesen Befehl aus, um die aktiven Abfragen mit Laufzeit anzuzeigen:

SELECT pid, usename, age(now(),xact_start) query_time, query FROM pg_stat_activity WHERE state='active';

Führen Sie diesen Befehl aus, um die Anzahl der in der Datenbank vorhandenen Verbindungen im Leerlauf zu ermitteln:

SELECT count(*) FROM pg_stat_activity WHERE state='idle';

Führen Sie dann diesen Befehl aus, um Verbindungen im Leerlauf zu beenden, die Ressourcen verbrauchen.

Hinweis: Achten Sie darauf, example-pid durch die PID der inaktiven Verbindung zu ersetzen, die Sie von pg_stat_activity erhalten haben:

SELECT pg_terminate_backend(example-pid);

Überprüfen Sie diese Indikatoren, um sicherzustellen, dass die RDS-Instanz den erforderlichen Netzwerkdurchsatz erreicht:

• NetworkReceiveThroughput
• NetworkTransmitThroughput

Diese Metriken zeigen den eingehenden und ausgehenden Netzwerkverkehr in Byte/Sekunde an. Unterdimensionierte oder nicht für Amazon Elastic Block Service (Amazon EBS) optimierte Instance-Klassen können den Netzwerkdurchsatz beeinflussen und zu langsamen Instances führen. A low network throughput can result in slow responses for all the application requests irrespective of the database performance.

Bewerten Sie die E/A-Leistung, indem Sie diese Metriken überprüfen:

• ReadIOPS und WriteIOPS – Durchschnittliche Anzahl von Lese- oder Schreibvorgängen pro Sekunde.
• ReadLatency und WriteLatency – Durchschnittliche Zeit für einen Lese- oder Schreibvorgang in Millisekunden
• ReadThroughput und WriteThroughput – Durchschnittliche Anzahl der Megabytes, die pro Sekunde von der Festplatte gelesen oder auf die Festplatte geschrieben werden.
• DiskQueueDepth – Anzahl der E/A-Vorgänge, die darauf warten, auf die Festplatte geschrieben oder von ihr gelesen zu werden

Weitere Informationen finden Sie unter Wie behebe ich Fehler der Latenz von Amazon-EBS-Volumes, die durch einen IOPS-Engpass in meiner Amazon-RDS-Instance verursacht wird?

Enhanced-Monitoring-Metriken

Mit Enhanced Monitoring können Sie Metriken auf Betriebssystemebene und die Liste der 100 wichtigsten Prozesse anzeigen, die viel CPU und Arbeitsspeicher verbrauchen. Sie können Enhanced Monitoring pro Sekunde aktivieren, um zeitweise auftretende Leistungsprobleme auf Ihrer RDS-Instance zu erkennen.

Sie können die verfügbaren Betriebssystemmetriken auswerten, um Leistungsprobleme zu diagnostizieren, die möglicherweise mit CPU, Workload, I/O, Arbeitsspeicher und Netzwerk zusammenhängen.

Identifizieren Sie in der Prozessliste den Prozess mit hohen Werten für CPU% oder Mem%. Suchen Sie dann die zugehörige Verbindung aus der Datenbank.

Beispiel:

NAME	VIRT	RES	CPU%	MEM%	VMLIMIT
postgres: postgres postgres 178.2.0.44(42303) AUSWÄHLEN [10322]	250,66 MB	27,7 MB	85,93	2,21	unbegrenzt

Stellen Sie eine Verbindung mit der Datenbank her, und führen Sie dann diese Abfrage aus, um die Verbindung und abfragebezogene Informationen zu finden:

SELECT * FROM pg_stat_activity WHERE pid = 10322;

Performance-Insights-Metriken

Mit Performance Insights können Sie Datenbank-Workloads aufgeschlüsselt nach Wartezeiten, SQL, Host oder Benutzern auswerten. Sie können auch die Metriken auf Datenbank- und SQL-Ebene erfassen.

Auf der Registerkarte Top SQL im Performance-Insights-Dashboard werden die SQL-Anweisungen angezeigt, die am meisten zur DB-Auslastung beitragen. Eine DB-Last oder Last nach Wartezeiten (AAS), die höher als der Max. vCPU-Wert ist, weist auf eine gedrosselte Arbeitslast der Instance-Klasse hin.

Die durchschnittliche Latenz pro Aufruf in der SQL-Statistik gibt die durchschnittliche Laufzeit einer Abfrage an. Es ist üblich, dass eine andere SQL den größten Beitrag zur DB-Last leistet als diejenige mit der höchsten durchschnittlichen Laufzeit. Dies liegt daran, dass die Top-SQL-Liste auf der Gesamtlaufzeit basiert.

Datenbankstatistiken

Die folgenden Statistiken können Ihnen helfen, die Datenbankleistung in PostgreSQL zu bewerten:

• Statistiken zur Datenverteilung
• Erweiterte Statistiken
• Statistiken überwachen

Informationen zum Lesen und Verstehen dieser Statistiken finden Sie unter Grundlegendes zu Statistiken in PostgreSQL.

Native Datenbanktools

Verwenden Sie das native Tool pgBadger, um langsame Abfragen zu identifizieren. Weitere Informationen finden Sie unter Optimieren und Optimieren von Abfragen in Amazon RDS für PostgreSQL auf der Grundlage nativer und externer Tools.

Leistung optimieren

Speichereinstellungen anpassen

Der PostgreSQL-DB-Server weist während seiner gesamten Lebensdauer einen bestimmten Speicherbereich zum Zwischenspeichern von Daten zu, um Lese- und Schreibzugriffe zu verbessern. Dieser Speicherbereich wird als Shared Buffer bezeichnet. Die Speichermenge, die Datenbank für Shared-Memory-Puffer verwendet, wird durch die Parameter shared_buffers gesteuert.

Abgesehen vom gemeinsamen Speicherbereich verbraucht jeder Backend-Prozess Speicher für die Ausführung von Vorgängen innerhalb eines DB-Servers. Die Menge des verwendeten Speichers basiert auf den Werten, die für die Parameter work_mem und maintenance_work_mem festgelegt wurden. Weitere Informationen finden Sie in der PostgreSQL-Dokumentation zur Serverkonfiguration.

Wenn Sie ständig eine hohe Speicherbelastung der DB-Instance beobachten, sollten Sie erwägen, die Werte dieser Parameter zu senken. Sie können die Werte dieser Parameter in der benutzerdefinierten Parametergruppe senken, die an Ihre DB-Instance angehängt ist.

Aurora PostgreSQL Abfrageplanverwaltung

Verwenden Sie die Abfrageplanverwaltung der Amazon Aurora PostgreSQL-Compatible Edition, um zu steuern, wie und wann sich die Pläne zur Abfrageausführung ändern. Weitere Informationen finden Sie unter Bewährte Methoden für die Verwaltung von Aurora PostgreSQL-kompatiblen Abfrageplänen.

Problembehandlung bei langsam laufenden Abfragen

In der Regel treten langsam ausgeführte Abfragen auf, wenn Infrastrukturprobleme auftreten oder der Gesamtressourcenverbrauch hoch ist. Langsam ausgeführte Abfragen können auch das Ergebnis einer suboptimalen Abfrageplanung durch den Abfrageplaner sein. Der PostgreSQL-Abfrageplaner verwendet für die Tabelle erstellte Statistiken, um Abfragepläne zu erstellen. Diese Pläne können aufgrund von Schemaänderungen und veralteten Statistiken betroffen sein. Ein Aufblähen der Tabelle und der Indizes kann auch zu langsam laufenden Abfragen führen.

Der Autovakuum-Daemon ist für die Erstellung von Autovakuum-Worker-Prozessen verantwortlich, die tote Tupel aus Tabellen entfernen, wenn der Schwellenwert für totes Tupel erreicht wird. Die ANALYZE-Prozedur, die für eine bestimmte Tabelle geführten Statistiken aktualisiert, wird ebenfalls vom Autovacuum-Daemon ausgeführt.

Führen Sie die folgende Abfrage aus, um Informationen zu finden über:

• Tote Tupel
• Anzahl der Autovakuum- oder Vakuumvorgänge
• Anzahl der Autoanalyse- oder Analyseläufe
• Wann diese Operationen zuletzt ausgeführt wurden

SELECT schemaname, relname, n_live_tup,n_dead_tup, last_autoanalyze, last_analyze, last_autovacuum, last_vacuum,
autovacuum_count+vacuum_count vacuum_count, analyze_count+autoanalyze_count analyze_count 
FROM pg_stat_user_tables
ORDER BY 5 DESC;

Sie können die Ansicht pg_stat_activity verwenden, um Daten zu aktuellen Aktivitäten zu finden. Diese Ansicht enthält die Backend-PID, Abfrage und andere Details. Um Abfragen mit langer Laufzeit zu finden, führen Sie diese Abfrage aus:

SELECT pid, datname, query, extract(epoch from now()) - extract(epoch from xact_start) AS duration, case
WHEN wait_event IS NULL THEN 'CPU' 
ELSE wait_event_type||':'||wait_event end wait FROM pg_stat_activity
WHERE query!=current_query() AND xact_start IS NOT NULL ORDER BY 4 DESC;

Beachten Sie, dass Abfragen, die auf Sperren warten, langsam sein können. Prüfen Sie daher, ob die Abfrage auf Sperren wartet, indem Sie diese Abfrage ausführen:

SELECT pid, virtualtransaction AS vxid, locktype AS lock_type, mode AS lock_mode, granted,fastpath,
CASE
WHEN virtualxid IS NOT NULL AND transactionid IS NOT NULL
THEN virtualxid || ' ' || transactionid
WHEN virtualxid::text IS NOT NULL
THEN virtualxid
ELSE transactionid::text
END AS xid_lock, relname, page, tuple, classid, objid, objsubid
FROM pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE -- do not show our view’s locks
pid != pg_backend_pid() AND
virtualtransaction IS DISTINCT FROM virtualxid
ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7;

Ihre RDS for PostgreSQL-Instanz ermöglicht es Ihnen, die Erweiterung pg_stat_statements aus der Datenbank heraus zu erstellen:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Mit pg_stat_statements können Sie die Statistiken von Abfragen anzeigen. Stellen Sie sicher, dass Sie den Eintrag pg_stat_statements zu shared_preload_libraries hinzufügen, bevor Sie die Erweiterung erstellen.

Hinweis: Sie können Parameter für dieses Modul nur ändern, wenn eine benutzerdefinierte Parametergruppe an Ihre DB-Instance angehängt ist.

Verwenden Sie diese Abfragen, um die wichtigsten SQL-Abfragen zu identifizieren, die sich auf die Leistung Ihrer Instance auswirken.

Um Abfragen zu finden, die mehr Zeit in der Datenbank verbringen, führen Sie diese Abfrage für PostgreSQL Version 12 und früher aus:

SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY 3 DESC;

Führen Sie diese Abfrage für PostgreSQL Version 13 und höher aus:

SELECT query, calls, total_plan_time+total_exec_time AS total_time, mean_plan_time + mean_exec_time AS mean_time FROM pg_stat_statements ORDER BY 3 DESC;

Um Abfragen mit einer niedrigeren Puffer-Cache-Trefferquote zu finden, führen Sie diese Abfrage für PostgreSQL-Versionen 12 und früher aus:

SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY total_time
DESC LIMIT 10;

Führen Sie diese Abfrage für PostgreSQL Version 13 und höher aus:

SELECT query, calls, total_plan_time+total_exec_time as total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit +
shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY 5 ASC
LIMIT 10;

Um langlaufende Abfragen oder Abfragepläne in Datenbankfehlerprotokollen zu erfassen, setzen Sie den Parameter log_min_duration_statement für Ihre Instanz entsprechend und verwenden Sie dann das Modulauto_explain. Der Parameter log_min_duration_statement bewirkt, dass die Dauer jeder abgeschlossenen Anweisung protokolliert wird, wenn die Anweisung mindestens die angegebene Zeit lang ausgeführt wurde. Wenn Sie diesen Parameter beispielsweise auf 250 ms festlegen, werden alle SQL-Anweisungen protokolliert, die 250 ms oder länger ausgeführt werden. Mit dem Modul auto_explain können Sie den erklärenden Plan von Abfragen erfassen, die in der Datenbank ausgeführt werden.

Sie können den Plan auch mit den Befehlen explain und explain analyze erfassen. Identifizieren Sie Abfrageoptimierungsmöglichkeiten basierend auf dem Modul auto_explainoder explain für die Abfrage. Weitere Informationen finden Sie in der PostgreSQL-Dokumentation zu Verwendung von EXPLAIN.

Wenn Ihr System gut abgestimmt ist und Sie immer noch mit Ressourceneinschränkungen konfrontiert sind, sollten Sie erwägen, die Instance-Klasse nach oben zu skalieren. Skalieren Sie die Instance-Klasse nach oben, um Ihrer DB-Instance mehr Rechen- und Speicherressourcen zuzuweisen. Weitere Informationen finden Sie unter Hardwarespezifikationen für DB-Instance-Klassen.

---

Relevante Informationen

Wie kann ich eine hohe CPU-Auslastung bei Amazon RDS oder Amazon Aurora PostgreSQL-Compatible Edition beheben?

Arbeiten mit Parametern auf Ihrer RDS-for-PostgreSQL-DB-Instance

Warum nutzt meine Amazon RDS-DB-Instance Auslagerungsspeicher, obwohl ausreichend Arbeitsspeicher verfügbar ist?