L'elevato utilizzo della CPU influisce negativamente sulle prestazioni dell'istanza. Qualsiasi attività eseguita sull'istanza utilizza la CPU. Pertanto, se viene visualizzato un avviso di utilizzo elevato della CPU, devi prima identificare la causa principale del problema, che si tratti di query scritte male, transazioni a esecuzione prolungata 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 di CPU.
Utilizza Query Insights per identificare le query con un consumo elevato di CPU
Query Insights ti aiuta a 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 con la combinazione dell'utilità pg_top
per ottenere output del sistema operativo che forniscono informazioni sull'utilizzo della CPU per processo.
Utilizzare le query
Le sezioni seguenti illustrano diverse query che puoi utilizzare.
Identificare 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 le 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 conteggio delle connessioni attive è elevato, verifica la presenza di query a lunga esecuzione o eventi di attesa che impediscono l'esecuzione delle query.
Se il conteggio delle 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 l'ID processo da pg_stat_activity.
Identifica le connessioni a esecuzione prolungata
Ecco un esempio di query che restituisce query a esecuzione prolungata. 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 esecuzione per identificare le query scritte male
Utilizza EXPLAIN PLAN per esaminare una query scritta male e riscriverla, se necessario. Se vuoi, puoi annullare la query a esecuzione prolungata con il seguente comando con le approvazioni necessarie.
SELECT pg_cancel_backend(<pid>);
Monitorare l'attività di ASPIRAZIONE
L'attività AUTOVACUUM che cancella le tuple non valide è un'operazione che richiede un utilizzo elevato 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;
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.
Aggiungere l'estensione pg_stat_statements
Configura l'estensione pg_stat_statements per ottenere informazioni avanzate sul dizionario relative all'attività dell'istanza.
Checkpoint frequenti
I checkpoint frequenti peggiorano le prestazioni. Valuta la possibilità di modificare il flag checkpoint_timeout se il log degli avvisi di PostgreSQL segnala l'avviso checkpoint occurring too frequently.
Raccogliere statistiche
Assicurati che il query planner 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 memorizza 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 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 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 informazioni sul rendimento 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 controllare 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 l'utilizzo della CPU è ancora elevato e ritieni che queste query siano traffico legittimo, valuta la possibilità di aumentare le risorse della CPU nella tua istanza per evitare arresti anomali o tempi di inattività del database.