Come posso risolvere i problemi di utilizzo elevato della CPU nelle istanze di Amazon RDS per PostgreSQL o Amazon Aurora compatibile con PostgreSQL?

7 minuti di lettura

Desidero risolvere il problema relativo all'utilizzo elevato della CPU nella mia istanza Amazon Relational Database Service (Amazon RDS) per PostgreSQL o Amazon Aurora edizione compatibile con PostgreSQL.

Risoluzione

Per determinare la causa dell'elevato utilizzo della CPU, effettua le seguenti azioni. Quindi, riduci l'utilizzo della CPU dell'istanza database.

Esamina le metriche delle istanze database

Per identificare quando il carico di lavoro causa un elevato utilizzo della CPU, utilizza Amazon CloudWatch per confrontare le metriche WriteIOPS, ReadIOPS, ReadThroughput e WriteThroughput con la metrica CPUUtilization. Per Aurora compatibile con PostgreSQL, puoi anche confrontare la metrica BufferCacheHitRatio. Se i valori di tali metriche sono elevati come la metrica CPUUtilization, il carico di lavoro potrebbe causare l'elevato utilizzo della CPU.

Utilizza Monitoraggio avanzato

Utilizza Monitoraggio avanzato per esaminare il sistema operativo relativo all'istanza database. Per raccogliere dati dettagliati, imposta la proprietà Granularity su un intervallo di 1, 5, 10, 15, 30 o 60 secondi.

Per risolvere la causa dell'elevato utilizzo della CPU, controlla la metrica del sistema operativo LoadAverageMinute. Se il carico medio è superiore al numero delle vCPU, l'istanza ha un utilizzo elevato. Se il carico medio è inferiore al numero di vCPU per la classe di istanza database, la limitazione (della larghezza di banda della rete) della CPU potrebbe non essere la causa della latenza dell'applicazione.

Puoi anche controllare l'elenco dei processi del sistema operativo per l'istanza database. Monitoraggio avanzato è in grado di identificare un massimo di 100 processi che influiscono sulle prestazioni dell'istanza. Per identificare l'utilizzo delle risorse delle query, utilizza i risultati di per eseguire una query PostgreSQL pg_stat_activity.

Utilizza CloudWatch Database Insights

Attiva Amazon CloudWatch Database Insights per identificare la query responsabile del carico del database. Quindi, controlla la scheda Top SQL nel grafico di carico del database per il periodo specifico in cui l'utilizzo della CPU aumenta.

Controlla le viste e i cataloghi nativi di PostgreSQL

Per problemi in tempo reale, attiva pg_stat_activity o pg_stat_statements per raggruppare le macchine, i client e gli indirizzi IP che inviano più traffico. Per ulteriori informazioni, consulta pg_stat_activity e pg_stat_statements sul sito web PostgreSQL.

Attiva pg_stat_statements

Completa i seguenti passaggi:

Modifica i seguenti valori del gruppo di parametri del database personalizzato:
Aggiungi pg_stat_statements a shared_preload_libraries.
Imposta track_activity_query_size su 4096.
Imposta pg_stat_statements.track su ALL.
Imposta pg_stat_statements.max su 10000.
Scegli Applica immediatamente, quindi riavvia l'istanza database.

Seleziona il database da monitorare

Esegui questa query:

select current_database();

Installa l'estensione sul database corrente

Esegui questo comando:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Visualizza le query con il maggior tempo di esecuzione nel database

Esegui questa query relativa alla versione di PostgreSQL in uso.

PostgreSQL versioni 12 e precedenti:

SELECT total_time, query FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

PostgreSQL versioni 13 e successive:

SELECT total_plan_time+total_exec_time as total_time, query FROM pg_stat_statements ORDER BY 1 DESC LIMIT 10;

Crea un elenco di query con una bassa percentuale di riscontri nella cache del buffer

Esegui questa query relativa alla versione di PostgreSQL in uso.

PostgreSQL versioni 12 e precedenti:

SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;

PostgreSQL versioni 13 e successive:

SELECT query, calls, total_plan_time+total_exec_time as total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit +shared_blks_read, 0) AS hit_percent FROM pg_stat_statements ORDER BY 3 DESC LIMIT 10;

Esempi di query nel tempo

Esegui questa query relativa alla versione di PostgreSQL in uso.

PostgreSQL versioni 12 e precedenti:

SELECT query, calls, total_time/calls as avg_time_ms, rows/calls as avg_rows,temp_blks_read/calls as avg_tmp_read, temp_blks_written/calls as avg_temp_written FROM pg_stat_statements WHERE calls != 0

ORDER BY total_time DESC LIMIT 10;

PostgreSQL versioni 13 e successive:

SELECT query,calls,  (total_plan_time+total_exec_time as total_time)/calls as avg_time_ms,   
 rows/calls as avg_rows,  
temp_blks_read/calls as avg_tmp_read,  
 temp_blks_written/calls as avg_temp_written  
FROM pg_stat_statements  
WHERE calls != 0  
ORDER BY 3 DESC LIMIT 10;

Verifica le connessioni inattive nel database

Se nel database sono presenti connessioni inattive, l'istanza database potrebbe utilizzare molta CPU. Per risolvere il problema, controlla se sono presenti connessioni inattive e interrompile. Per ulteriori informazioni, consulta Performance impact of idle PostgreSQL connections (Impatto sulle prestazioni delle connessioni PostgreSQL inattive).

Per controllare le sessioni inattive per più di 10 minuti, esegui questa query:

SELECT * FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled','active')
AND state_change < current_timestamp - INTERVAL '10' MINUTE
AND usename != 'rdsadmin';

Per terminare solo le sessioni che rimangono inattive per più di 10 minuti, esegui questa query:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
AND state_change < current_timestamp - INTERVAL '10' MINUTE
AND usename != 'rdsadmin';

Per terminare tutte le connessioni inattive, esegui una di queste query:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'example-username'  
AND pid <> pg_backend_pid()  
AND state in ('idle');

Nota: sostituisci example-user-name con il tuo nome utente.

In alternativa:

SELECT pg_terminate_backend (example-pid);

Nota: sostituisci example-pid con il PID della tua query.

Se l'applicazione crea troppe connessioni al database, riduci il numero di connessioni. Oppure, usa un pool di connessioni come PgBouncer. Per ulteriori informazioni, consulta PgBouncer sul sito web PgBouncer. Per configurare i pool di connessioni, puoi inoltre utilizzare Server proxy per Amazon RDS.

Verifica i blocchi del database

Se i blocchi del database causano un accumulo delle query e un aumento del loro tempo di esecuzione, nell'istanza database si potrebbe verificare un aumento dell'utilizzo della CPU. Per risolvere i problemi relativi ai blocchi, in Database Insights controlla gli eventi di attesa, come Lock:Relation, Lock:tuple e Lock:transactionid, o altri eventi relativi ai blocchi.

Per identificare la causa del blocco della query, consulta Come posso identificare cosa ha bloccato una query nella mia istanza database Amazon RDS per PostgreSQL o Aurora compatibile con PostgreSQL?

Se le sessioni di blocco non sono nello stato ACTIVE, utilizza Database Insights per identificare le query bloccate. Per risolvere questo problema, termina tutte le sessioni bloccate.

Esegui il comando ANALYZE

Quando non esegui frequentemente il comando ANALYZE sulle tabelle del database, le query potrebbero utilizzare più risorse di elaborazione a causa della presenza di statistiche obsolete nel sistema. Per ulteriori informazioni, consulta ANALYZE sul sito web PostgreSQL.

Autovacuum rimuove lo spazio inutilizzato dalle tabelle e recupera spazio nel database. Il daemon autovacuum esegue anche il comando ANALYZE per aggiornare regolarmente le statistiche delle tabelle quando raggiungi la soglia delle versioni precedenti che hai configurato.

Per sapere quando autovacuum e autoanalyze sono stati eseguiti l'ultima volta sulle tabelle, esegui questa query:

SELECT relname, last_autovacuum, last_autoanalyze FROM pg_stat_user_tables;

Per evitare problemi di prestazioni dopo un aggiornamento della versione principale del motore, esegui il comando ANALYZE per aggiornare la tabella pg_statistic per ogni database nell'istanza database.

Per evitare problemi di prestazioni dovuti a un maggiore utilizzo delle risorse, esegui il seguente comando senza parametri per rigenerare tutte le statistiche:

ANALYZE VERBOSE;

Controlla i log degli errori di PostgreSQL

Attiva la registrazione delle query in Amazon RDS per PostgreSQL. Quindi controlla i log degli errori di PostgreSQL per verificare di aver impostato i parametri log_min_duration_statement e log_statement. Per ulteriori informazioni, consulta Error reporting and logging (Segnalazione e registrazione degli errori) sul sito web PostgreSQL.

Riduci l'utilizzo della CPU

Per ridurre l'utilizzo della CPU, effettua le seguenti azioni:

Utilizza EXPLAIN e EXPLAIN ANALYZE per identificare i modi per ottimizzare i piani di query. Per ulteriori informazioni, consulta Utilizzo di EXPLAIN sul sito web PostgreSQL.
Se identifichi sessioni di blocco non necessarie, utilizza il PID della sessione di blocco per terminare le sessioni.
Se esegui ripetutamente una determinata query, usa le istruzioni preparate per ridurre l'uso della CPU. Per ulteriori informazioni, consulta PREPARE sul sito web PostgreSQL.