Ottimizza l'utilizzo elevato della CPU nelle istanze

L'utilizzo elevato della CPU influisce negativamente sulle prestazioni dell'istanza. Qualsiasi attività eseguita sull'istanza utilizza la CPU. Pertanto, se viene visualizzata una notifica di utilizzo elevato della CPU, devi prima identificare la causa principale del problema, che si tratti di query scritte male, transazioni a lunga esecuzione o qualsiasi altra attività del database.

Questo documento descrive i modi per identificare i colli di bottiglia della CPU in un'istanza e mitigare i problemi di utilizzo della CPU nell'istanza.

Identificare i colli di bottiglia della CPU

Le sezioni seguenti descrivono diversi scenari della CPU.

Utilizzare Query Insights per identificare le query con un consumo elevato di CPU

Query Insights consente di rilevare, diagnosticare e prevenire i problemi di prestazioni delle query per i database Cloud SQL.

Utilizzare l'estensione pg_proctab

Utilizza l'estensione pg_proctab in combinazione con l'utilità pg_top per ottenere output del sistema operativo che forniscono informazioni sull'utilizzo della CPU per processo.

Utilizzare le query

Le sezioni seguenti descrivono le diverse query che puoi utilizzare.

Identificare le connessioni attive per stato

Ogni connessione attiva al database richiede una certa quantità di CPU, quindi se l'istanza ha un numero elevato di connessioni, l'utilizzo cumulativo potrebbe essere elevato. Utilizza la seguente query per ottenere informazioni sul numero di connessioni per stato.

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

L'output è simile al seguente:


        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)

Se il numero di connessioni attive è elevato, controlla se sono presenti query a lunga esecuzione o eventi di attesa che bloccano l'esecuzione delle query.

Se il numero di connessioni inattive è elevato, esegui la seguente query per terminare le connessioni, dopo aver ottenuto le approvazioni necessarie.

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

Puoi anche terminare le connessioni singolarmente con pg_terminate_backend utilizzando la seguente query:

SELECT pg_terminate_backend (<pid>);

Qui puoi ottenere il PID da pg_stat_activity.

Identificare le connessioni a lunga esecuzione

Ecco un esempio di query che restituisce query a lunga esecuzione. In questo caso, puoi identificare le query attive da più di 5 minuti.

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;

Esaminare il piano di spiegazione per identificare le query scritte male

Utilizza EXPLAIN PLAN per esaminare una query scritta male e riscriverla, se necessario. Facoltativamente, valuta la possibilità di annullare la query a lunga esecuzione con il seguente comando con le approvazioni necessarie.

SELECT pg_cancel_backend(<pid>);

Monitorare l'attività VACUUM

L'attività AUTOVACUUM che cancella le tuple non attive è un'operazione che richiede un utilizzo intensivo della CPU. Se la tua istanza utilizza PostgreSQL versione 11 o successive, utilizza la seguente query per verificare se è in corso un'attività AUTOVACUUM o VACUUM attiva.

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;

Verifica se è in corso un'attività VACUUM in un'istanza utilizzando la seguente query:

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

Inoltre, puoi ottimizzare e risolvere i problemi delle operazioni VACUUM in PostgreSQL.

Aggiungere l'estensione pg_stat_statements

Configura l'estensione pg_stat_statements per ottenere informazioni avanzate sul dizionario sull'attività dell'istanza.

Checkpoint frequenti

I checkpoint frequenti peggiorano le prestazioni. Valuta la possibilità di modificare il flag checkpoint_timeout se il log di avviso di PostgreSQL segnala l'avviso checkpoint occurring too frequently.

Raccogliere statistiche

Assicurati che il pianificatore di query disponga delle statistiche più recenti sulle tabelle per scegliere il piano migliore per le query. L'operazione ANALYZE raccoglie statistiche sui contenuti delle tabelle nel database e archivia i risultati nel catalogo di sistema pg_statistic. Successivamente, il pianificatore di query utilizza queste statistiche per determinare i piani di esecuzione più efficienti per le query. Il processo AUTOVACUUM analizza automaticamente le tabelle periodicamente, quindi esegui il seguente comando per verificare se tutte le tabelle sono state analizzate e se il pianificatore dispone dei metadati più recenti.

SELECT
  relname,
  last_autovacuum,
  last_autoanalyze
FROM
  pg_stat_user_tables;

Impostazioni di sistema inadeguate

Esistono altri fattori e impostazioni di flag o fattori di sistema che influenzano le prestazioni della query. Esegui la seguente query per controllare gli eventi di attesa e il tipo di evento di attesa per ottenere informazioni sulle prestazioni di altre impostazioni di sistema.

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;

L'output è simile al seguente:


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

Monitorare le scansioni sequenziali

Le scansioni sequenziali frequenti su tabelle di più di qualche decina di righe di solito indicano un indice mancante. Quando le scansioni toccano migliaia o addirittura centinaia di migliaia di righe, possono causare un utilizzo eccessivo della CPU.

Le scansioni sequenziali frequenti su tabelle con centinaia di migliaia di righe possono causare un utilizzo eccessivo della CPU. Evita le scansioni sequenziali su queste tabelle creando gli indici necessari.

Esegui la seguente query per verificare il numero di volte in cui vengono avviate le scansioni sequenziali su una tabella.

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

Infine, se l'utilizzo della CPU è ancora elevato e ritieni che queste query siano traffico legittimo, valuta la possibilità di aumentare le risorse della CPU nell'istanza per evitare arresti anomali o tempi di inattività del database.

Passaggi successivi