Un utilizzo elevato della CPU influisce negativamente sulle prestazioni dell'istanza. Qualsiasi attività eseguita nell'istanza utilizza la CPU. Pertanto, se viene visualizzata una notifica relativa a un 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 attenuare i problemi di utilizzo della CPU nell'istanza.
Identifica i colli di bottiglia della CPU
Utilizzare gli approfondimenti sulle query per identificare le query con un consumo elevato della 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
Identifica 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 in esecuzione prolungata 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.
Identifica le connessioni a lungo termine
Di seguito è riportato un esempio di query che restituisce query di lunga durata. 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 male
Utilizza EXPLAIN PLAN per esaminare una query scritta male e riscriverla, se necessario. Se vuoi, puoi annullare la query di lunga durata con il seguente comando e le approvazioni necessarie.
SELECT pg_cancel_backend(<pid>);
Monitorare l'attività di VACUUM
L'attività AUTOVACUUM che cancella le tuple non valide è un'operazione che richiede molta CPU. Se la tua 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.
Controlli frequenti
I controlli frequenti riducono le prestazioni. Valuta la possibilità di modificare il flag checkpoint_timeout se il log degli avvisi PostgreSQL segnala l'avviso checkpoint occurring too frequently.
Raccogliere 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 le statistiche sui contenuti delle tabelle nel database e archivia i risultati nel catalogo di sistema pg_statistic. Successivamente, lo strumento di pianificazione delle 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 dispongono dei metadati più recenti disponibili per il pianificatore.
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 influiscono sul rendimento 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 analisi sequenziali
Le scansioni sequenziali frequenti di tabelle con più di alcune decine di righe in genere indicano un indice mancante. Quando le scansioni toccano migliaia o addirittura centinaia di migliaia di righe, possono causare un utilizzo eccessivo della CPU.
Le ricerche sequenziali frequenti in 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 le scansioni sequenziali in 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 arresti anomali o tempi di riposo del database.