Hohe CPU-Nutzung in Instanzen optimieren

Eine hohe CPU-Auslastung beeinträchtigt die Leistung Ihrer Instanz. Jede Aktivität, die auf der Instanz ausgeführt wird, beansprucht CPU. Bei einer hohen CPU-Auslastung sollten Sie daher zuerst die Ursache des Problems ermitteln. Dabei kann es sich um falsch geschriebene Abfragen, lang andauernde Transaktionen oder andere Datenbankaktivitäten handeln.

In diesem Dokument wird beschrieben, wie Sie Engpässe in einer Instanz ermitteln und Probleme mit der CPU-Auslastung in der Instanz minimieren.

CPU-Engpässe identifizieren

Abfragestatistiken verwenden, um Abfragen mit hohem CPU-Verbrauch zu identifizieren

Mit Query Insights können Sie Probleme bei der Abfrageleistung in Cloud SQL-Datenbanken ermitteln, diagnostizieren und verhindern.

Erweiterung pg_proctab verwenden

Verwenden Sie die Erweiterung pg_proctab in Kombination mit dem Dienstprogramm pg_top, um Betriebssystemausgaben abzurufen, die Informationen zur CPU-Auslastung pro Prozess liefern.

Abfragen verwenden

Aktive Verbindungen nach Status identifizieren

Jede aktive Verbindung zur Datenbank beansprucht eine gewisse Menge an CPU, sodass die kumulative Auslastung hoch sein kann, wenn die Instanz eine hohe Anzahl von Verbindungen hat. Verwenden Sie die folgende Abfrage, um die Informationen zur Anzahl der Verbindungen nach Status abzurufen.

SELECT 
  state, 
  usename, 
  count(1) 
FROM 
  pg_stat_activity 
WHERE 
  pid <> pg_backend_pid() 
group by 
  state, 
  usename 
order by 
  1;

Die Ausgabe sieht dann ungefähr so aus:


        state        |    usename    | count 
---------------------+---------------+-------
 active              | ltest         |   318
 active              | sbtest        |    95
 active              |               |     2
 idle                | cloudsqladmin |     2
 idle in transaction | ltest         |    32
 idle in transaction | sbtest        |     5
                     | cloudsqladmin |     3
                     |               |     4
(8 rows)

Wenn die Anzahl der aktiven Verbindungen hoch ist, prüfen Sie auf lang andauernde Abfragen oder Wartezeiten, die die Ausführung der Abfragen blockieren.

Wenn die Anzahl der inaktiven Verbindungen hoch ist, führen Sie die folgende Abfrage aus, um die Verbindungen zu beenden, nachdem Sie die erforderlichen Genehmigungen erhalten haben.

SELECT 
  pg_terminate_backend(pid) 
FROM 
  pg_stat_activity 
WHERE 
  usename = 'sbtest' 
  and pid <> pg_backend_pid() 
  and state in ('idle');

Mit der folgenden Abfrage können Sie die Verbindungen auch einzeln mit pg_terminate_backend beenden:

SELECT pg_terminate_backend (<pid>);

Hier finden Sie die PID von pg_stat_activity.

Lang andauernde Verbindungen identifizieren

Das folgende Beispiel zeigt eine Abfrage, die Abfragen mit langer Ausführungszeit zurückgibt. In diesem Fall können Sie die Abfragen ermitteln, die länger als fünf Minuten aktiv sind.

SELECT 
  pid, 
  query_start, 
  xact_start, 
  now() - pg_stat_activity.query_start AS duration, 
  query, 
  state 
FROM 
  pg_stat_activity 
WHERE 
  (
    now() - pg_stat_activity.query_start
  ) > interval '5 minutes' order by 4 desc;

Erläuterungen lesen, um schlecht geschriebene Abfragen zu ermitteln

Verwenden Sie den EXPLAIN PLAN, um eine schlecht geschriebene Abfrage zu untersuchen und die Abfrage bei Bedarf neu zu schreiben. Optional können Sie die lang andauernde Abfrage mit dem folgenden Befehl mit den erforderlichen Genehmigungen abbrechen.

SELECT pg_cancel_backend(<pid>);

VACUUM-Aktivität überwachen

Die AUTOVACUUM-Aktivität, die die toten Tupel löscht, ist ein CPU-intensiver Vorgang. Wenn Ihre Instanz PostgreSQL Version 11 oder höher verwendet, prüfen Sie mit der folgenden Abfrage, ob aktive AUTOVACUUM- oder VACUUM-Aktivitäten ausgeführt werden.

SELECT 
  relid :: regclass, 
  pid, 
  phase, 
  heap_blks_total, 
  heap_blks_scanned, 
  heap_blks_vacuumed, 
  index_vacuum_count, 
  max_dead_tuples, 
  num_dead_tuples 
FROM 
  pg_stat_progress_vacuum;

Prüfen Sie mit der folgenden Abfrage, ob eine VACUUM-Aktivität in einer Instanz ausgeführt wird:

SELECT 
  pid, 
  datname,
  usename, 
  query 
FROM 
  pg_stat_activity 
WHERE 
  query like '%vacuum%';

Außerdem können Sie VACUUM-Vorgänge in PostgreSQL optimieren und beheben.

pg_stat_statements-Erweiterung hinzufügen

Richten Sie die Erweiterung pg_stat_statements ein, um erweiterte Wörterbuchinformationen zur Instanzaktivität abzurufen.

Häufige Prüfpunkte

Häufige Prüfpunkte beeinträchtigen die Leistung. Sie können das Flag checkpoint_timeout anpassen, wenn das PostgreSQL-Benachrichtigungslog die Warnung checkpoint occurring too frequently meldet.

Statistiken erfassen

Der Abfrageplaner enthält die neuesten Statistiken zu Tabellen, um den besten Plan für Abfragen auszuwählen. Der ANALYZE-Vorgang erfasst Statistiken zum Inhalt von Tabellen in der Datenbank und speichert die Ergebnisse im pg_statistic-Systemkatalog. Anschließend werden diese Statistiken vom Abfrageplaner verwendet, um die effizientesten Ausführungspläne für Abfragen zu ermitteln. Der AUTOVACUUM-Prozess analysiert automatisch die Tabellen in regelmäßigen Abständen. Führen Sie daher den folgenden Befehl aus, um zu prüfen, ob alle Tabellen analysiert wurden und die neuesten Metadaten für den Planer verfügbar sind.

SELECT 
  relname, 
  last_autovacuum, 
  last_autoanalyze 
FROM 
  pg_stat_user_tables;

Unzureichende Systemeinstellungen

Es gibt weitere Faktoren und Flag-Einstellungen oder Systemfaktoren, die sich auf die Leistung Ihrer Abfrage auswirken. Führen Sie die folgende Abfrage aus, um die Warteereignisse und den Warteereignistyp zu prüfen, um Informationen zur Leistung anderer Systemeinstellungen zu erhalten.

SELECT 
  datname, 
  usename, 
  (
    case when usename is not null then state else query end
  ) AS what, 
  wait_event_type, 
  wait_event, 
  backend_type, 
  count(*) 
FROM 
  pg_stat_activity 
GROUP BY 
  1, 
  2, 
  3, 
  4, 
  5, 
  6 
ORDER BY 
  1, 
  2, 
  3, 
  4 nulls first, 
  5, 
  6;

Die Ausgabe sieht etwa so aus:

  
 ..  | .. | what           | wait_event_type |      wait_event      | ..    | count
-..--+-..-+----------------+-----------------+----------------------+-..----+------
 ..
 ..  | .. | active         | IO              | CommitWaitFlush      | ..    |   750
 ..  | .. | idle           | IO              | CommitWaitFlush      | ..    |   360
 ..  | .. | active         | LWLock          | BufferMapping        | ..    |   191
  

Sequenzielle Scans überwachen

Häufige sequentielle Scans über Tabellen mit mehr als ein paar Dutzend Zeilen deuten normalerweise auf einen fehlenden Index hin. Wenn die Scans Tausende oder sogar Hunderttausende von Zeilen betreffen, kann dies zu einer übermäßigen CPU-Auslastung führen.

Häufige sequenzielle Scans auf Tabellen mit Hunderttausenden von Zeilen können zu einer übermäßigen CPU-Auslastung führen. Vermeiden Sie sequenzielle Scans für solche Tabellen. Erstellen Sie dazu die erforderlichen Indexe.

Führen Sie die folgende Abfrage aus, um zu prüfen, wie häufig sequenzielle Scans für eine Tabelle initiiert werden.

SELECT 
  relname, 
  idx_scan,  
  seq_scan, 
  n_live_tup 
FROM 
  pg_stat_user_tables 
WHERE 
  seq_scan > 0 
ORDER BY 
  n_live_tup desc;

Wenn die CPU weiterhin hoch ist und Sie glauben, dass diese Abfragen legitimer Traffic sind, sollten Sie die CPU-Ressourcen in Ihrer Instanz erhöhen, um Datenbankabstürze oder Ausfallzeiten zu vermeiden.