Un utilizzo elevato della CPU influisce negativamente sulle prestazioni dell'istanza. Qualsiasi attività eseguita sull'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 per mitigare i problemi di utilizzo della CPU nell'istanza.
Identificare i colli di bottiglia della CPU
Utilizzare gli approfondimenti sulle query per identificare le query con un consumo elevato della CPU
Query Insights ti consente di rilevare, diagnosticare e per evitare problemi di prestazioni delle query per i database Cloud SQL.
Usa 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, quindi se l'istanza ha un elevato numero l'utilizzo cumulativo potrebbe essere elevato. Usa 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 di eventi di attesa che bloccano l'esecuzione delle query.
Se il numero di connessioni inattive è elevato, esegui questa 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 lunga esecuzione
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 di spiegazione per identificare le query scritte male
Utilizza il PIANO EXPLAIN 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 comando seguente con le approvazioni necessarie.
SELECT pg_cancel_backend(<pid>);
Monitora l'attività del VACUUM
L'attività AUTOVACUUM che cancella le tuple non attive è un'operazione ad alta intensità di CPU. Se l'istanza utilizza PostgreSQL 11 o versioni successive, usa la seguente query per verificare se è in corso un'attività AUTOVACUUM o VACUUM.
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 relativi alle operazioni VACUUM in PostgreSQL.
Aggiungi estensione pg_stat_statements
Configura l'estensione pg_stat_statements per ricevere informazioni avanzate sul dizionario sull'attività dell'istanza.
Controlli 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.
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 ANALISI 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 automaticamente le tabelle periodicamente, quindi esegui il comando seguente per verificare se tutte le tabelle sono state analizzate e se disponi dei metadati più recenti per lo strumento di pianificazione.
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 questa query per controllare gli eventi di attesa e il tipo di evento di attesa per ottenere informazioni sulle prestazioni delle 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
Scansioni sequenziali frequenti su tabelle con più di qualche decina di righe di solito un indice mancante. Quando le scansioni interessano migliaia o persino centinaia di migliaia di righe, può causare un utilizzo eccessivo della CPU.
Scansioni sequenziali frequenti su tabelle con centinaia di migliaia di righe possono causare un utilizzo eccessivo della CPU. Evitare scansioni sequenziali su tali tabelle creando gli indici necessari.
Esegui la seguente query per verificare il numero di volte in cui vengono avviate 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 inattività del database.