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

Lesedauer: 9 Minute

Ich möchte die Ursache der hohen CPU-Auslastung in Amazon Relational Database Service (Amazon RDS) oder Amazon Aurora PostgreSQL kompatibler Edition identifizieren und beheben.

Kurzbeschreibung

Wenn Sie feststellen, dass Ihre Last eine hohe CPU-Auslastung hat, verwenden Sie eine Kombination der folgenden Tools, um die Ursache zu ermitteln:

Amazon CloudWatch-Metriken
Enhanced Monitoring-Metriken
Performance Insights-Metriken
Native PostgreSQL-Ansicht und -Kataloge, wie z. B. pg_stat_statements, pg_stat_activity und pg_stat_user_tables
Inaktive Verbindungen in der Datenbank
ANALYZE-Befehl
PostgreSQL-Protokoll-Parameter zur Protokollierung von lang andauernden Abfragen, Autovacuum, Sperrwartezeiten sowie Verbindungs- und Trennungsanfragen

Behebung

Amazon CloudWatch-Metriken

Verwenden Sie CloudWatch-Metriken, um CPU-Muster über längere Zeiträume zu identifizieren. Vergleichen Sie die Grafiken WriteIOPs, ** ReadIOPs**, ReadThroughput und WriteThroughput mit der CPU-Auslastung, um die Zeiten zu ermitteln, zu denen die Workload zu einer hohen CPU-Auslastung geführt hat.

Nachdem Sie den Zeitrahmen identifiziert haben, überprüfen Sie die Enhanced Monitoring-Daten, die Ihrer DB-Instance zugeordnet sind. Sie können Enhanced Monitoring so einrichten, dass Daten in Intervallen von 1, 5, 10, 15, 30 oder 60 Sekunden erfasst werden. Dann können Sie Daten auf einer detaillierteren Ebene als CloudWatch sammeln.

Enhanced Monitoring

Enhanced Monitoring bietet einen Überblick auf Betriebssystemebene (OS). Diese Ansicht kann helfen, die Ursache einer hohen CPU-Last auf einer granularen Ebene zu identifizieren. Sie können beispielsweise den Lastdurchschnitt, die CPU-Verteilung (System% oder Nice%) und die Liste der Betriebssystemprozesse überprüfen.

Mit Enhanced Monitoring können Sie die loadAverageMinute-Daten in Intervallen von 1, 5 und 15 Minuten überprüfen. Ein Lastdurchschnitt, der über der Anzahl der vCPUs liegt, weist darauf hin, dass die Instance stark ausgelastet ist. Wenn der Lastdurchschnitt unter der Anzahl der vCPUs für die DB-Instance-Klasse liegt, wird die Anwendungslatenz wahrscheinlich nicht durch CPU-Drosselung verursacht. Überprüfen Sie bei der Diagnose der Ursache der CPU-Auslastung den Lastdurchschnitt, um Fehlalarme zu vermeiden.

Angenommen, Sie haben eine DB-Instance, die eine db.m5.2xlarge-Instance-Klasse mit 3000 bereitgestellten IOPS verwendet, die das CPU-Limit erreicht. Im folgenden Beispiel sind der Instance-Klasse acht vCPUs zugeordnet. Bei gleichem Lastdurchschnitt bedeutet ein Wert von mehr als 170, dass der Rechner während des gemessenen Zeitrahmens stark belastet ist.

Lastdurchschnitt (Minute)


Fünfzehn	170,25
Fünf	391,31
Eins	596,74

CPU-Auslastung


Benutzer (%)	0,71
System (%)	4,9
Nice (%)	93,92
Insgesamt (%)	99,97

Hinweis: In Enhanced Monitoring steht Nice% für die Menge an CPU, die Ihr Workload für die Datenbank verwendet.

Nachdem Sie Enhanced Monitoring aktiviert haben, können Sie auch die Liste der Betriebssystemprozesse überprüfen, die der DB-Instance zugeordnet ist. Enhanced Monitoring zeigt maximal 100 Prozesse und kann Ihnen helfen, die Prozesse mit den größten Auswirkungen auf die Leistung zu identifizieren. Sie können die Ergebnisse von Enhanced Monitoring mit pg_stat_activity-Ergebnissen kombinieren, um die Ressourcennutzung von Abfragen zu ermitteln.

Performance Insights

Verwenden Sie Erkenntnisse zur Amazon-RDS-Leistung, um die Abfrage zu identifizieren, die für die Datenbanklast verantwortlich ist. Überprüfen Sie die Registerkarte SQL, die einem bestimmten Zeitrahmen entspricht.

Native PostgreSQL-Ansicht und -Kataloge

Auf Datenbank-Engine-Ebene können Sie pg_stat_activity und pg_stat_statements verwenden. Wenn das Problem in Echtzeit auftritt, verwenden Sie pg_stat_activity oder pg_stat_statements, um die Computer, Clients und IP-Adressen zu gruppieren, die den meisten Datenverkehr senden. Verwenden Sie diese Daten, um nach Zunahmen im Laufe der Zeit oder der Anzahl der Anwendungsserver zu suchen. Sie können auch überprüfen, ob auf einem Anwendungsserver festsitzende Sitzungen oder Sperrprobleme auftreten. Weitere Informationen finden Sie unter pg_stat_activity und pg_stat_statements auf der PostgreSQL-Website.

Um pg_stat_statements zu aktivieren, ändern Sie die vorhandene benutzerdefinierte Parametergruppe und legen Sie die folgenden Werte fest:

Fügen Sie pg_stat_statements zu shared_preload_libraries hinzu
track_activity_query_size = 4096
pg_stat_statements.track = ALL
pg_stat_statements.max = 10000

Wählen Sie Sofort anwenden aus und starten Sie dann die DB-Instance neu. Führen Sie dann in der Datenbank, die Sie überwachen möchten, einen Befehl ähnlich dem folgenden aus:

demo=> select current_database();current_database
------------------
demo
(1 row)     

demo=> CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Hinweis: Mit dem vorherigen Befehl wird die Erweiterung in der Demo-Datenbank installiert.

Nachdem pg_stat_statements eingerichtet ist, verwenden Sie eine der folgenden Methoden, um die Ausgabe zu überwachen.

Führen Sie eine der folgenden Abfragen aus, um Abfragen nach total_time aufzuführen und zu sehen, welche Abfrage die meiste Zeit in der Datenbank verbringt:

PostgreSQL Versionen 12 und früher

SELECT total_time, query
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 10;

PostgreSQL Versionen 13 und höher

SELECT total_plan_time+total_exec_time as total_time, query
FROM pg_stat_statements
ORDER BY 1 DESC LIMIT 10;

Führen Sie eine der folgenden Abfragen aus, um Abfragen mit einer geringeren Trefferquote im Puffer-Cache aufzulisten:

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_percent
FROM 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 3 DESC LIMIT 10;

Führen Sie die folgende Abfrage aus, um Abfragen pro Ausführung als Beispielabfragen im Zeitverlauf aufzulisten:

PostgreSQL Versionen 12 und früher

SELECT query, calls, total_time/calls as avg_time_ms, rows/calls as avg_rows,temp_blks_read/calls as avg_tmp_read, temp_blks_written/calls as avg_temp_written
FROM pg_stat_statements
WHERE calls != 0
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)/calls as avg_time_ms,
 rows/calls as avg_rows,
temp_blks_read/calls as avg_tmp_read,
 temp_blks_written/calls as avg_temp_written
FROM pg_stat_statements
WHERE calls != 0
ORDER BY 3 DESC LIMIT 10;

Inaktive Verbindungen in der Datenbank

Inaktive Verbindungen in der Datenbank können Rechenressourcen wie Arbeitsspeicher und CPU verbrauchen. Wenn Ihre Instance eine hohe CPU-Auslastung hat, suchen Sie nach inaktiven Verbindungen in der Datenbank. Weitere Informationen finden Sie unter Auswirkungen von PostgreSQL-Verbindungen im Leerlauf auf die Leistung. Um nach inaktiven Verbindungen zu suchen, verwenden Sie Enhanced Monitoring, um die Liste der Betriebssystemprozesse zu überprüfen. Diese Liste zeigt jedoch maximal 100 Prozesse.

Führen Sie die folgenden Abfragen auf Datenbankebene aus, um nach inaktiven Verbindungen zu suchen.

Führen Sie die folgenden Abfragen aus, um aktuelle Sitzungen anzuzeigen, die inaktiv und aktiv sind:

SELECT pid, datname, state, current_timestamp-least(query_start,xact_start) age, application_name, usename, queryFROM pg_stat_activity
WHERE query != '<IDLE>
'AND query NOT ILIKE '%pg_stat_activity%'
AND usename!='rdsadmin'
ORDER BY query_start desc;

SELECT application_name,pid,wait_event_type,wait_event,current_timestamp-least(query_start,xact_start) AS runtime, query AS current_query
FROM pg_stat_activity
WHERE not pid=pg_backend_pid()
AND query NOT ILIKE '%pg_stat_activity%'
AND usename!='rdsadmin';

Führen Sie die folgenden Abfragen aus, um die Anzahl der Verbindungen pro Benutzer und Anwendungsname abzurufen:

postgres=> SELECT application_name,count(*) FROM pg_stat_activity GROUP BY application_name;
    application_name    | count
------------------------+-------
 psql                   |     1
 PostgreSQL JDBC Driver |     1
                        |     5
(3 rows)

postgres=> SELECT usename,count(*) FROM pg_stat_activity GROUP BY usename;
 usename  | count
----------+-------
 master   |     4
 user1    |     1
 rdsadmin |     2
(3 rows)

Nachdem Sie die inaktiven Verbindungen identifiziert haben, führen Sie eine der folgenden Abfragen aus, um die Verbindungen zu beenden:

psql=> SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE usename = 'example-username'
   AND pid <> pg_backend_pid()
   AND state in ('idle');

-oder-

SELECT pg\_terminate\_backend (example-pid);

Wenn Ihre Anwendung zu viele Verbindungen verursacht, ändern Sie sie so, dass Speicher- und CPU-Ressourcen nicht für die Verwaltung dieser Verbindungen aufgewendet werden. Sie können entweder die Anwendung ändern, um die Anzahl der Verbindungen zu begrenzen, oder einen Verbindungspooler wie PgBouncer verwenden. Sie können auch Amazon-RDS-Proxy verwenden, einen verwalteten Dienst, mit dem Sie Verbindungspooling einrichten können.

ANALYZE-Befehl

Der Befehl ANALYZE sammelt Statistiken über den Inhalt von Tabellen in der Datenbank und speichert die Ergebnisse im Systemkatalog pg_statistic. Anschließend verwendet der Abfrageplaner diese Statistiken, um die effizientesten Ausführungspläne für Abfragen zu ermitteln. Wenn Sie ANALYZE nicht regelmäßig für Tabellen in Ihrer Datenbank ausführen, verbrauchen die Abfragen möglicherweise mehr Rechenressourcen. Die Abfragen verbrauchen mehr Ressourcen, da im System veraltete Statistiken für die Beziehungen, auf die Sie zugreifen, vorhanden sind. Diese Probleme treten unter den folgenden Bedingungen auf:

Autovakuum läuft nicht regelmäßig.
ANALYZE wurde nach dem Upgrade der Hauptversion nicht ausgeführt.

Autovacuum läuft nicht regelmäßig: Autovacuum ist ein Daemon, der die Ausführung von VACUUM- und ANALYZE-Befehlen automatisiert. Autovacuum sucht in der Datenbank nach aufgeblähten Tabellen und gibt den Speicherplatz für die Wiederverwendung frei. Der Autovacuum-Daemon stellt sicher, dass die Tabellenstatistiken regelmäßig aktualisiert werden, indem er die ANALYZE-Operation immer dann ausführt, wenn der eingestellte Schwellenwert für Tupel tot ist. Dann kann der Abfrageplaner den effizientesten Abfrageplan verwenden, der auf aktuellen Statistiken basiert. Wenn Autovacuum nicht ausgeführt wird, erstellt der Abfrageplaner möglicherweise suboptimale Abfragepläne, was zu einem höheren Ressourcenverbrauch der Abfragen führt. Weitere Informationen finden Sie in den folgenden Ressourcen:

Führen Sie die folgende Abfrage aus, um Informationen darüber zu erhalten, wann Autovacuum und Autoanalyze zuletzt für die Tabellen ausgeführt wurden:

SELECT relname, last\_autovacuum, last\_autoanalyze FROM pg\_stat\_user\_tables;

ANALYZE wurde nach dem Upgrade der Hauptversion nicht ausgeführt: PostgreSQL-Datenbanken stoßen normalerweise nach einem größeren Engine-Versionsupgrade auf Leistungsprobleme. Ein häufiger Grund für diese Probleme ist, dass der ANALYZE-Vorgang nach dem Upgrade zur Aktualisierung der pg_statistic-Tabelle nicht ausgeführt wird. Führen Sie den ANALYZE-Vorgang für jede Datenbank in Ihrer RDS für PostgreSQL DB-Instance aus. Optimizer-Statistiken werden während eines Hauptversions-Upgrades nicht übertragen. Generieren Sie daher alle Statistiken neu, um Leistungsprobleme aufgrund einer höheren Ressourcenauslastung zu vermeiden.

Um Statistiken für alle regulären Tabellen in der aktuellen Datenbank nach einem größeren Versionsupgrade zu generieren, führen Sie den folgenden Befehl ohne Parameter aus:

ANALYZE VERBOSE

PostgreSQL-Protokollierungsparameter

Verwenden Sie Amazon RDS für PostgreSQL, um die Abfrageprotokollierung zu aktivieren. Überprüfen Sie anschließend die PostgreSQL-Fehlerprotokolle, um sicherzustellen, dass Ihre Parameter log_min_duration_statement und log_statement auf die entsprechenden Werte gesetzt sind. Weitere Informationen finden Sie unter Fehlerberichterstattung und Protokollierung auf der PostgreSQL-Website.

Verringern der CPU-Auslastung

Nachdem Sie die Abfragen identifiziert haben, die die hohe CPU-Auslastung verursachen, verwenden Sie die folgenden Methoden, um die CPU-Auslastung weiter zu verringern:

Um Möglichkeiten zur Optimierung zu finden, verwenden Sie EXPLAIN und EXPLAIN ANALYZE, um die Vorbehalte zu identifizieren. Weitere Informationen finden Sie unter EXPLAIN auf der PostgreSQL-Website.
Wenn eine Abfrage wiederholt ausgeführt wird, verwenden Sie vorbereitete Anweisungen, um den Druck auf Ihre CPU zu verringern. Durch wiederholtes Ausführen vorbereiteter Anweisungen wird der Abfrageplan zwischengespeichert. Da sich der Plan bereits für weitere Läufe im Cache befindet, ist die Zeit für die Planung viel kürzer.