Wie identifiziere und behebe ich Leistungsprobleme und Abfragen mit langsamer Ausführung in meiner Amazon RDS für PostgreSQL- oder Aurora PostgreSQL-kompatiblen DB-Instance?

Lösung

Unterdimensionierte Hardware, Änderungen der Workload, erhöhter Datenverkehr, Speicherprobleme oder nicht optimierte Abfragen beeinträchtigen die Leistung der DB-Instance. Gehe wie folgt vor, um Leistungsprobleme zu beheben.

Identifizieren der Ursache

Überprüfung der CloudWatch-Metriken

Um Leistungsengpässe zu identifizieren, die durch unzureichende Ressourcen verursacht werden, überwache die Amazon CloudWatch-Metriken CPUUtilization, FreaableMemory und SwapUsage.

Wenn die CPU-Auslastung hoch ist, benötigt eine aktive Workload auf der DB-Instance mehr CPU-Ressourcen. Eine geringe Speicherverfügbarkeit für die Workload führt zu einer häufigen Auslagerung, was zu einer hohen Speicherauslastung und einer hohen Swap-Speichernutzung führt. Abfragen mit langer Ausführung, plötzlich erhöhter Datenverkehr oder eine große Anzahl inaktiver Verbindungen können zu einer hohen CPU-Auslastung und hohen Speicherressourcen führen.

Führe den folgenden 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ühre den folgenden Befehl aus, um die inaktiven Verbindungen in der Datenbank anzuzeigen:

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

Führe den folgenden Befehl aus, um inaktive Verbindungen zu beenden:

SELECT pg_terminate_backend(example-pid);

Hinweis: Ersetze example-pid durch die Prozess-ID der inaktiven Verbindung.

Um zu überprüfen, ob die DB-Instance den erwarteten Netzwerkdurchsatz erreicht, überprüfe die Metriken NetworkReceiveThroughput und NetworkTransmitThroughput. Unterdimensionierte oder nicht optimierte Amazon Elastic Block Service (Amazon EBS)-Instance-Klassen können den Netzwerkdurchsatz beeinträchtigen und zu langsamen Instances führen. Ein niedriger Netzwerkdurchsatz kann unabhängig von der Datenbankleistung zu langsamen Antworten auf alle Anwendungsanforderungen führen.

Um die E/A-Leistung zu bewerten, überprüfe die Metriken ReadIOPS, WriteIOPS, ReadLatency, WriteLatency, ReadThroughput, WriteThroughput und DiskQueueDepth. Weitere Informationen findest du unter Wie behebe ich die Latenz von Amazon EBS-Volumes, die durch einen IOPS-Engpass in meiner Amazon RDS-Instance verursacht wird?

Verwendung von Enhanced Monitoring

Verwende Enhanced Monitoring, um Metriken auf Betriebssystem (OS)-Ebene anzuzeigen und die 100 wichtigsten Prozesse aufzulisten, die viel CPU und Arbeitsspeicher beanspruchen. Aktiviere Enhanced Monitoring, wobei die Granularität auf 1 gesetzt ist, um zeitweilige Leistungsprobleme auf der DB-Instance zu identifizieren.

Beurteile die verfügbaren Betriebssystem-Metriken, um Leistungsprobleme im Zusammenhang mit CPU, Workload, E/A, Arbeitsspeicher und Netzwerk zu diagnostizieren. Identifiziere in der Prozessliste Prozesse mit hohen Werten für CPU% oder Mem%.

Beispiel:

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

Stelle eine Verbindung zur Datenbank her, und führe dann die folgende Abfrage aus, um die Verbindung mit hoher CPU in der Datenbank zu finden:

SELECT * FROM pg_stat_activity WHERE pid = 10322;

Hinweis: Ersetze 10322 durch die Prozess-ID der Verbindung.

Leistungserkenntnis-Metriken überprüfen

Verwende Leistungserkenntnisse, um Datenbank-Workloads nach Wartezeiten, SQL, Host oder Benutzern zu bewerten. Du kannst auch die Metriken auf Datenbank- und SQL-Ebene abrufen.

Verwende die Registerkarte Top SQL im Performance Insights-Dashboard, um die SQL-Anweisungen anzuzeigen, die am meisten zur DB-Auslastung beitragen. Wenn die DB-Auslastung oder die Auslastung nach Wartezeiten (AAS) höher ist als die Max vCPU, wird die Workload der DB-Instance-Klasse gedrosselt.

Verwende die durchschnittliche Latenz pro Aufruf in den SQL-Statistiken, um die durchschnittliche Laufzeit einer Abfrage anzuzeigen. Top SQL basiert auf der Gesamtlaufzeit. Daher unterscheidet sich die SQL mit der höchsten Laufzeit oft von der SQL, die am häufigsten zur DB-Auslastung beiträgt.

Überprüfung der Datenbankstatistiken

Um die Datenbankleistung in PostgreSQL zu bewerten, überprüfe die Datenverteilungsstatistiken, erweiterten Statistiken und Überwachungsstatistiken. Informationen zu den Statistiken findest du unter Grundlegendes zu Statistiken in PostgreSQL.

Überprüfung der nativen Datenbanktools

Verwende das native pgbadger-Tool auf der GitHub-Website, um langsame Abfragen zu identifizieren. Weitere Informationen findest du unter Optimierung und Abstimmung von Abfragen in Amazon RDS für PostgreSQL auf der Grundlage nativer und externer Tools.

Leistung optimieren

Speichereinstellungen optimieren

Du kannst den Parameter shared_buffers auf einen Wert setzen, der zur Verbesserung der Abfrageleistung beiträgt.

Die Parameter work_mem und maintenance_work_mem definieren die Speichermenge, die für Backend-Prozesse verwendet wird. Weitere Informationen findest du unter 20.4 Resource consumption (20.4 Ressourcenverbrauch) auf der PostgreSQL-Website. Wenn du in der DB-Instance häufig eine hohe Speichernutzung feststellst, senke die Parameterwerte in der benutzerdefinierten Parametergruppe, die an deine Instance angefügt ist.

Verwendung der Aurora PostgreSQL-kompatiblen Abfrageplanverwaltung

Verwende die Aurora PostgreSQL-kompatible Abfrageplanverwaltung, um zu kontrollieren, wie und wann sich Abfrage-Ausführungspläne ändern. Weitere Informationen findest du unter Bewährte Methoden für die Verwaltung von Aurora PostgreSQL-Abfrageplänen.

Problembehandlung bei Abfragen mit langsamer Ausführung

Infrastrukturprobleme, eine nicht optimierte Abfrageplanung oder eine hohe Gesamtressourcennutzung führen zu Abfragen mit langsamer Ausführung. Der PostgreSQL-Abfrageplaner verwendet Tabellenstatistiken, um Abfragepläne zu erstellen. Schemaänderungen und alte Statistiken könnten sich auf die Pläne auswirken. Überladene Tabellen und Indizes können auch zu langsamen Abfragen führen.

Wenn eine Tabelle den Schwellenwert für tote Tupel erreicht, erstellt der Selbstbereinigungs-Daemon Selbstbereinigungs-Arbeitsprozesse, die tote Tupel aus der Tabelle entfernen. Der Selbstbereinigungs-Daemon führt auch die ANALYZE-Operation aus, die die Statistiken der Tabelle aktualisiert.

Führe die folgende Abfrage aus, um nach toten Tupeln und Selbstbereinigungs- oder Bereinigungs-Operationen und Selbstanalyse- oder ** Analyseausführungen zu suchen**:

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;

Verwende die Ansicht pg_stat_activity, um Daten zu finden, die sich auf aktuelle Aktivitäten beziehen, z. B. eine Backend-Prozess-ID oder eine Abfrage. Führe die folgende Abfrage aus, um Abfragen mit langer Ausführungszeit zu finden:

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;

Abfragen, die auf Sperren warten, sind möglicherweise langsam. Führe die folgende Abfrage aus, um zu überprüfen, ob eine Abfrage auf Sperren wartet:

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;

Verwende pg_stat_statements, um die Statistiken von Abfragen anzuzeigen. Bevor du die Erweiterung pg_stat_statements erstellst, füge den Eintrag pg_stat_statements zu shared_preload_libraries hinzu. Führe die folgende Abfrage aus, um die Erweiterung pg_stat_statements in der Datenbank zu erstellen:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Hinweis: Du kannst Parameter für pg_stats_statements nur ändern, wenn eine benutzerdefinierte Parametergruppe an deine DB-Instance angefügt ist.

Führe die folgenden Abfragen aus, um die SQL-Abfragen zu identifizieren, die sich auf die Leistung der DB-Instance auswirken.

PostgreSQL Versionen 12 und früher:

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

PostgreSQL Versionen 13 und höher:

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;

Führe die folgenden Abfragen aus, um Abfragen mit einer niedrigeren Trefferquote im Pufferspeicher zu finden.

PostgreSQL Versionen 12 und früher:

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

PostgreSQL Versionen 13 und höher:

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 Abfragen mit langer Ausführungszeit oder Abfragepläne in Datenbankfehlerprotokollen zu finden, konfiguriere den Parameter log_min_duration_statement für die DB-Instance und verwende dann das auto_explain-Modul.

Du kannst auch die Befehle explain und explain analyze verwenden, um den Abfrageplan abzurufen. Verwende das Modul auto_explain oder die Befehle explain, um zu ermitteln, wie du die Abfrageoptimierung optimieren kannst. Weitere Informationen findest du unter 14.1 Using EXPLAIN (14.1 EXPLAIN verwenden) und F3. auto_explain - log execution plans of slow queries (F3. auto_explain – Ausführungspläne langsamer Abfragen protokollieren) auf der PostgreSQL-Website.

Wenn du dein System optimiert hast und immer noch Leistungsprobleme auftreten, empfiehlt es sich, die DB-Instance-Klasse hochzuskalieren. Wenn du die DB-Instance hochskalierst, weise mehr Rechen- und Speicherressourcen zu.

Ähnliche Informationen

Wie kann ich eine hohe CPU-Auslastung für Amazon RDS oder Amazon Aurora PostgreSQL beheben?

Arbeiten mit Parametern auf der RDS für PostgreSQL-DB-Instance

Warum verwendet eine Instance in Amazon RDS DB Swap-Speicher, obwohl ich über ausreichend Speicher verfüge?