L'utilizzo elevato della CPU influisce negativamente sulle prestazioni dell'istanza. Qualsiasi attività eseguita sull'istanza utilizza la CPU. Quindi, 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 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
Utilizza Query Insights per identificare le query con un consumo elevato di CPU
Query Insights consente di rilevare, diagnosticare e prevenire 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.
Utilizzare le query
Identificare le connessioni attive per stato
Ogni connessione attiva al database richiede una certa quantità di CPU; pertanto, 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 numero di connessioni attive è elevato, verifica la presenza di query a lunga esecuzione o eventi di attesa che bloccano l'esecuzione delle query.
Se il numero di connessioni inattive è elevato, esegui la query seguente 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 durata
Ecco un esempio di query che restituisce query a lunga esecuzione. In questo caso, puoi identificare le query che sono state attive per 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 Spiega per identificare le query scritte male
Utilizza il PIANO ESPLICATIVO per esaminare una query scritta male e riscrivere la query, 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>);
Monitora l'attività VACUUM
L'attività AUTOVACUUM che cancella le tuple morte è un'operazione che richiede molta CPU. Se l'istanza utilizza PostgreSQL 11 o versioni 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.
Aggiungi estensione pg_stat_statements
Configura l'estensione pg_stat_statements per ricevere informazioni avanzate del dizionario sull'attività dell'istanza.
Checkpoint frequenti
I checkpoint 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.
Raccogli le 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, lo strumento di pianificazione delle query utilizza queste statistiche per determinare i piani di esecuzione più efficienti per le query. Il processo AUTOVACUUM analizza le tabelle periodicamente in modo automatico, quindi esegui il comando seguente per verificare se tutte le tabelle sono state analizzate e se lo strumento di pianificazione dispone dei metadati più recenti.
SELECT
relname,
last_autovacuum,
last_autoanalyze
FROM
pg_stat_user_tables;
Impostazioni di sistema inadeguate
Esistono altri fattori e le impostazioni dei flag o fattori di sistema che influiscono sulle 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
Monitoraggio delle scansioni sequenziali
Scansioni sequenziali frequenti su tabelle contenenti più di alcune decine di righe di solito indicano un indice mancante. Quando le scansioni toccano migliaia o persino centinaia di migliaia di righe, possono causare un utilizzo eccessivo della CPU.
Scansioni sequenziali frequenti su tabelle con centinaia di migliaia di righe possono causare un utilizzo eccessivo della CPU. Evita scansioni sequenziali su queste tabelle creando gli indici necessari.
Esegui la query seguente per verificare il numero di volte in cui le scansioni sequenziali vengono avviate 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 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 inattività del database.