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

9 minuti di lettura

Desidero identificare e risolvere le cause dell'utilizzo elevato della CPU nella mia istanza Amazon Relational Database Service (Amazon RDS) o Amazon Aurora compatibile con PostgreSQL.

Breve descrizione

Per determinare la causa dell'utilizzo elevato della CPU, utilizza i seguenti strumenti:

Metriche di Amazon CloudWatch
Metriche di Enhanced Monitoring
Metriche di Approfondimenti sulle prestazioni
Visualizzazioni e cataloghi PostgreSQL nativi, come pg_stat_statements, pg_stat_activity e pg_stat_user_tables
Connessioni inattive nel database
Comando ANALYZE sul sito web PostgreSQL
Parametri di registrazione PostgreSQL per registrare query di lunga durata, autovacuum, lock wait e richieste di connessione e disconnessione

Risoluzione

Esamina le metriche di CloudWatch

Utilizza le metriche di CloudWatch per identificare modelli di utilizzo della CPU su periodi di tempo prolungati. Confronta i grafici WriteIOPs, ReadIOPs, ReadThroughput e WriteThroughput con l'utilizzo della CPU per trovare i momenti in cui il carico di lavoro causa un aumento dell'utilizzo della CPU.

Dopo aver identificato l'intervallo di tempo, esamina i dati di Monitoraggio avanzato associati all'istanza database. Puoi impostare Monitoraggio avanzato su intervalli di raccolta di 1, 5, 10, 15, 30 o 60 secondi per raccogliere dati a un livello più granulare.

Utilizza Monitoraggio avanzato

Monitoraggio avanzato fornisce una visualizzazione a livello di sistema operativo. Ad esempio, puoi esaminare il carico di lavoro medio, la distribuzione della CPU (System% o Nice%) e l'elenco dei processi del sistema operativo. Per ulteriori informazioni, consulta Monitoraggio del sistema operativo.

Puoi controllare i dati loadAverageMinute a intervalli di 1, 5 e 15 minuti. Quando il carico medio è superiore al numero di vCPU, l'istanza è sottoposta a un carico 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 causare la latenza dell'applicazione. Quando risolvi la causa dell'utilizzo elevato della CPU, controlla il carico medio per evitare falsi positivi.

Ad esempio, hai un'istanza database che utilizza una classe di istanza db.m5.2xlarge con una capacità di IOPS allocata pari a 3.000 che raggiunge la quota della CPU. Alla classe di istanza sono associate otto vCPU. Se lo stesso carico medio supera 170, il computer è sottoposto a un carico pesante durante l'intervallo di tempo misurato.

Carico medio al minuto:

Quindici: 170,25
Cinque: 391,31
Uno: 596,74

Utilizzo della CPU:

Utente (%): 0,71
Sistema (%): 4,9
Nice (%): 93,92
Totale (%): 99,97

Nota: in Monitoraggio avanzato, Nice% è la quantità di CPU utilizzata dal carico di lavoro rispetto al database.

Dopo aver attivato Monitoraggio avanzato, puoi anche controllare l'elenco dei processi del sistema operativo associato all'istanza database. Monitoraggio avanzato consente di identificare al massimo 100 processi che influiscono sulle prestazioni. Puoi combinare i risultati di Monitoraggio avanzato con i risultati pg_stat_activity per identificare l'utilizzo delle risorse da parte delle query.

Utilizza Approfondimenti sulle prestazioni

Utilizza Approfondimenti sulle prestazioni di Amazon RDS per identificare la query responsabile del carico del database. Controlla la scheda SQL che corrisponde a un intervallo di tempo specifico.

Controlla le visualizzazioni e i cataloghi nativi di PostgreSQL

A livello di motore di database, puoi utilizzare pg_stat_activity e pg_stat_statements. Se il problema si verifica in tempo reale, utilizza pg_stat_activity o pg_stat_statements per raggruppare i computer, i client e gli indirizzi IP che inviano più traffico.

Utilizza i dati per verificare gli aumenti nel tempo o gli aumenti nei server delle applicazioni. Puoi anche verificare se un server delle applicazioni ha sessioni bloccate o problemi di blocco. Per ulteriori informazioni, consulta pg_stat_activity e pg_stat_statements sul sito web PostgreSQL.

Per attivare pg_stat_statements, completa i seguenti passaggi:

Modifica il gruppo di parametri database personalizzato esistente.
Aggiungi pg_stat_statements a shared_preload_libraries.
Imposta track_activity_query_size su 4.096.
Imposta pg_stat_statements.track su ALL.
Imposta pg_stat_statements.max su 10.000.
Scegli Applica immediatamente, quindi riavvia l'istanza database.

Sul database che desideri monitorare, esegui questo comando:

demo=> select current_database();current_database------------------  
demo  
(1 row)  

demo=> CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Nota: il comando precedente installa l'estensione nel database demo.

Dopo aver impostato pg_stat_statements, utilizza uno dei seguenti metodi per monitorare l'output. Puoi visualizzare la query che trascorre il maggior tempo nel database, ha la minore frequenza di accesso al buffer in memoria rispetto al disco rigido o viene considerata sulla base dell’esecuzione.

Per visualizzare le query trascorrono il maggior tempo nel database, esegui questa query per la versione di PostgreSQL in uso.

PostgreSQL versioni 12 e precedenti:

SELECT total_time, queryFROM 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, queryFROM pg_stat_statements  
ORDER BY 1 DESC LIMIT 10;

Per visualizzare le query con la minore frequenza di accesso al buffer in memoria rispetto al disco rigido, esegui questa query per la 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_percentFROM 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_percentFROM pg_stat_statements   
ORDER BY 3 DESC LIMIT 10;

Per visualizzare le query sulla base dell'esecuzione e campionare le query nel tempo, esegui questa query per la 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_writtenFROM 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

Le connessioni inattive nel database potrebbero utilizzare risorse di elaborazione, come memoria e CPU. Quando l'istanza ha un utilizzo elevato della CPU, verifica la presenza di connessioni inattive nel database. Per ulteriori informazioni, consulta Performance impact of idle PostgreSQL connections (Impatto sulle prestazioni delle connessioni PostgreSQL inattive).

Per esaminare l'elenco dei processi del sistema operativo per le connessioni inattive, puoi utilizzare Monitoraggio avanzato. Tuttavia, l'elenco mostra al massimo 100 processi. Per verificare la presenza di connessioni inattive a livello di database, esegui queste query.

Visualizza le sessioni correnti che sono inattive e attive:

SELECT pid, datname, state, current_timestamp-least(query_start,xact_start) age, application_name, usename, queryFROM pg_stat_activityWHERE query != '<IDLE>  
'AND query NOT ILIKE '%pg_stat_activity%'  
AND usename!='rdsadmin'  
ORDER BY query_start desc;  
SELECT application_name,pid,wait_event_type,wait_event,current_timestamp-least(query_start,xact_start) AS runtime, query AS current_query  
FROM pg_stat_activity  
WHERE not pid=pg_backend_pid()  
AND query NOT ILIKE '%pg_stat_activity%'  
AND usename!='rdsadmin';

Ottieni il numero di connessioni per ogni nome utente e applicazione:

postgres=> SELECT application_name,count(*) FROM pg_stat_activity GROUP BY application_name;    application_name    | count   
------------------------+-------  
 psql                   |     1  
 PostgreSQL JDBC Driver |     1   
                        |     5  
(3 rows)  
postgres=> SELECT usename,count(*) FROM pg_stat_activity GROUP BY usename;  
 usename  | count  
----------+-------  
 master   |     4   
 user1    |     1  
 rdsadmin |     2  
(3 rows)

Dopo aver identificato le connessioni inattive, esegui una di queste query per terminarle:

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

-oppure-

SELECT pg_terminate_backend (example-pid);

Se l'applicazione causa troppe connessioni, modificala in modo che le risorse di memoria e CPU non le gestiscano. Puoi limitare il numero di connessioni o utilizzare un pool di connessioni come PGBouncer. Puoi anche utilizzare Server proxy per Amazon RDS per configurare pool di connessioni.

Esegui il comando ANALYZE

Il comando ANALYZE raccoglie statistiche sul contenuto delle tabelle del database e memorizza i risultati nel catalogo di sistema pg_statistic. Dopodiché, il pianificatore di query utilizza le statistiche per determinare i piani di esecuzione più efficienti per le query. Quando non esegui frequentemente il comando ANALYZE sulle tabelle del database, le query potrebbero utilizzare più risorse di elaborazione per la presenza di statistiche obsolete nel sistema.

Le statistiche diventano obsolete per i seguenti motivi:

Autovacuum non viene eseguito spesso.
Non è stata eseguita l'operazione ANALYZE dopo l'aggiornamento della versione principale.

Autovacuum verifica la presenza di tabelle eccessivamente grandi nel database e recupera lo spazio per il riutilizzo. Per assicurarti che le statistiche della tabella vengano aggiornate regolarmente, il daemon autovacuum esegue il comando ANALYZE quando la soglia impostata per le tuple inutilizzate (dead) viene raggiunta.

Per ulteriori informazioni, consulta le seguenti risorse:

Informazioni sull'autovacuum in Amazon RDS per ambienti PostgreSQL
A Case Study of Tuning Autovacuum in Amazon RDS for PostgreSQL (Caso di studio sull'ottimizzazione di autovacuum in Amazon RDS per PostgreSQL)

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. Esegui il comando ANALYZE per ogni database nell'istanza database RDS per PostgreSQL.

Per evitare problemi di prestazioni dovuti al maggiore utilizzo delle risorse, rigenera tutte le statistiche. Per generare statistiche per tutte le tabelle regolari del database corrente dopo un aggiornamento della versione principale, esegui questo comando senza parametri:

ANALYZE VERBOSE

Controlla i log degli errori di PostgreSQL

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

Riduci l'utilizzo della CPU

Dopo aver identificato le query che causano un aumento dell'utilizzo della CPU, utilizza i seguenti metodi per ridurlo ulteriormente:

Utilizza EXPLAIN e EXPLAIN ANALYZE per identificare i modi per ottimizzare i piani di query. Per ulteriori informazioni, consulta Using EXPLAIN (Utilizzo di EXPLAIN) sul sito web PostgreSQL.
Se una query viene eseguita ripetutamente, utilizza le istruzioni preparate per ridurre la pressione sulla CPU. Le istruzioni preparate eseguite ripetutamente memorizzano nella cache il piano di query. Quando il piano è già nella cache per ulteriori esecuzioni, il tempo per la pianificazione della query è inferiore.