Un utilizzo elevato della CPU influisce negativamente sulle prestazioni dell'istanza. Qualsiasi attività eseguita sull'istanza utilizza la CPU. Pertanto, se ricevi 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.
Identifica i colli di bottiglia della CPU
Le sezioni seguenti illustrano diversi scenari di utilizzo della CPU.
Utilizza 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.
Utilizza l'estensione pg_proctab
Utilizza l'estensione pg_proctab con la combinazione dell'utilità pg_top per ottenere output del sistema operativo che forniscono informazioni sull'utilizzo della CPU per processo.
Utilizza le query
Le sezioni seguenti descrivono le diverse query che puoi utilizzare.
Identifica le connessioni attive per stato
Ogni connessione attiva al database utilizza 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, verifica la presenza di query a lunga esecuzione o di 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.
Identifica le connessioni con esecuzione prolungata
Ecco un esempio di query che restituisce query con tempi di esecuzione lunghi. 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;
Esamina il piano di spiegazione per identificare le query scritte in modo errato
Utilizza EXPLAIN PLAN per esaminare una query scritta in modo errato e riscriverla, se necessario. Se vuoi, puoi annullare la query a lunga esecuzione con il seguente comando, previa approvazione.
SELECT pg_cancel_backend(<pid>);
Monitora l'attività VACUUM
L'attività AUTOVACUUM che cancella le tuple inattive è un'operazione che richiede un uso intensivo della CPU. Se l'istanza utilizza PostgreSQL versione 11 o successive, utilizza la seguente query per verificare se sono in corso attività AUTOVACUUM o VACUUM attive.
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;
Controlla 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.
Aggiungi l'estensione pg_stat_statements
Configura l'estensione pg_stat_statements per ottenere informazioni avanzate del dizionario sull'attività dell'istanza.
Checkpoint frequenti
I checkpoint frequenti riducono le prestazioni. Se il log degli avvisi di PostgreSQL segnala l'avviso checkpoint occurring too frequently, valuta la possibilità di modificare il flag checkpoint_timeout.
Raccogli statistiche
Assicurati che lo strumento di pianificazione delle 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 Query Planner 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 comando seguente per verificare se tutte le tabelle sono state analizzate e se dispongono dei metadati più recenti disponibili per il planner.
SELECT
relname,
last_autovacuum,
last_autoanalyze
FROM
pg_stat_user_tables;
Impostazioni di sistema inadeguate
Esistono altri fattori e impostazioni dei 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 insight 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
Monitora le scansioni sequenziali
Le scansioni sequenziali frequenti su tabelle con più di qualche decina di righe di solito indicano un indice mancante. Quando le scansioni interessano 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 scansioni sequenziali su qualsiasi 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 la CPU è ancora elevata e ritieni che queste query siano traffico legittimo, valuta la possibilità di aumentare le risorse della CPU nell'istanza per evitare l'arresto anomalo o il tempo di inattività del database.