Come posso identificare e risolvere i problemi di prestazioni e di query a esecuzione lenta nella mia istanza RDS per PostgreSQL o Aurora PostgreSQL?

Ultimo aggiornamento: 02/12/2022

La mia istanza Amazon Relational Database Service (Amazon RDS) per PostgreSQL è lenta. Desidero identificare e risolvere i problemi legati alle query a esecuzione lenta.

Risoluzione

Le prestazioni della tua istanza Amazon RDS per PostgreSQL potrebbero essere influenzate per diversi motivi, ad esempio:

  • Hardware sottodimensionato
  • Modifiche nel carico di lavoro
  • Aumento del traffico
  • Problemi di memoria
  • Piani di interrogazione non ottimali

Identifica la causa

Utilizza una combinazione di questi strumenti per identificare la causa delle query lente:

  • Parametri di Amazon CloudWatch
  • Parametri di Enhanced Monitoring
  • Parametri di Performance Insights
  • Statistiche del database
  • Strumenti di database nativi

Parametri di CloudWatch

Per identificare i colli di bottiglia delle prestazioni causati da risorse insufficienti, monitora questi parametri di CloudWatch comuni disponibili per la tua istanza database di Amazon RDS.

  • CPUUtilization: la percentuale della capacità di elaborazione del computer utilizzata
  • FreeableMemory: la RAM disponibile sull'istanza database (in megabyte)
  • SwapUsage: lo spazio di swap utilizzato dall'istanza database (in megabyte)

Una percentuale più elevata di utilizzo della CPU in genere indica un carico di lavoro attivo sull'istanza e la necessità di maggiori risorse della CPU. Un maggiore utilizzo della memoria e il consumo di spazio di swap indicano frequenti scambi dovuti alla scarsa disponibilità di memoria per il carico di lavoro. Ciò potrebbe significare che l'istanza non è in grado di tenere il passo con il carico di lavoro. Un elevato utilizzo della CPU e delle risorse di memoria è in genere causato da query a esecuzione prolungata, un aumento improvviso del traffico e un numero elevato di connessioni inattive.

Esegui questo comando per visualizzare le query attive con il runtime:

SELECT pid, usename, age(now(),xact_start) query_time, query FROM pg_stat_activity WHERE state='active';

Esegui questo comando per trovare il numero di connessioni inattive presenti nel database:

SELECT count(*) FROM pg_stat_activity WHERE state='idle';

Quindi, esegui questo comando per terminare le connessioni inattive che consumano risorse.

Nota: assicurati di sostituire example-pid con il PID della connessione inattiva ottenuta da pg_stat_activity:

SELECT pg_terminate_backend(example-pid);

Verifica se la velocità di trasmissione effettiva di rete desiderata viene raggiunta sull'istanza RDS utilizzando i seguenti parametri:

  • NetworkReceiveThroughput
  • NetworkTransmitThroughput

Questi parametri indicano il traffico di rete in entrata e in uscita in byte al secondo. Classi di istanze sottodimensionate o non ottimizzate per Amazon Elastic Block Service (Amazon EBS) possono influire sul throughput di rete, con conseguente rallentamento delle istanze. Una velocità di trasmissione effettiva di rete bassa può comportare risposte lente per tutte le richieste dell'applicazione indipendentemente dalle prestazioni del database.

Valuta le prestazioni di I/O utilizzando i seguenti parametri:

  • ReadIOPS e WriteIOPS: il numero medio di operazioni di lettura o scrittura su disco al secondo
  • ReadLatency e WriteLatency: il tempo medio impiegato per un'operazione di lettura o scrittura in millisecondi
  • ReadThroughput e WriteThroughput: il numero medio di megabyte letti o scritti su disco al secondo
  • DiskQueueDepth: il numero di operazioni di I/O in attesa di essere scritte o lette dal disco

Per ulteriori informazioni, consulta Come posso risolvere la latenza dei volumi Amazon EBS causata da un collo di bottiglia IOPS nella mia istanza Amazon RDS?

Parametri di Enhanced Monitoring

Con il monitoraggio avanzato, puoi visualizzare i parametri a livello di sistema operativo e l'elenco dei 100 processi principali che consumano parecchia CPU e memoria. È possibile attivare il monitoraggio avanzato al secondo in modo da identificare i problemi di prestazioni intermittenti sull'istanza RDS.

Puoi valutare i parametri del sistema operativo disponibili per diagnosticare i problemi di prestazioni che potrebbero essere correlati a CPU, carico di lavoro, I/O, memoria e rete.

Dall'elenco dei processi, identifica il processo con valori elevati per CPU% o Mem%. Quindi, trova la connessione correlata dal database.

Ad esempio:

NAME VIRT RES CPU% MEM% VMLIMIT
postgres: postgres postgres 178.2.0.44(42303) SELECT [10322] 250,66 MB 27,7 MB 85,93 2,21 illimitato

Connettiti al database, quindi esegui questa query per trovare la connessione e le informazioni relative alla query:

SELECT * FROM pg_stat_activity WHERE pid = 10322;

Parametri di Performance Insights

Performance Insights consente di valutare i carichi di lavoro del database suddivisi per attese, SQL, host e utenti. È inoltre possibile acquisire i parametri a livello di database e SQL.

La scheda Top SQL (Istruzioni SQL principali) del pannello di controllo di Performance Insights mostra le istruzioni SQL che contribuiscono maggiormente al carico del database. Un carico del database o un valore di load by waits (AAS) superiore al valore Max vCPU indica un carico di lavoro limitato sulla classe di istanza.

La latenza media per chiamata nelle statistiche SQL fornisce il tempo medio di esecuzione di una query. È normale che un SQL diverso sia il principale contributore del carico del database rispetto a quello con il tempo di esecuzione medio più elevato. Questo perché l'elenco principale di SQL si basa sul tempo di esecuzione totale.

Statistiche del database

Le seguenti statistiche possono aiutarti a valutare le prestazioni del database in PostgreSQL:

  • Statistiche sulla distribuzione dei dati
  • Statistiche estese
  • Monitoraggio delle statistiche

Per informazioni su come leggere e comprendere queste statistiche, consulta Comprendere le statistiche in PostgreSQL.

Strumenti di database nativi

Per identificare le query lente, usa lo strumento nativo pgBadger. Per ulteriori informazioni, consulta Ottimizzazione e regolazione delle query in Amazon RDS per PostgreSQL in base a strumenti nativi ed esterni.

Ottimizza le prestazioni

Regola le impostazioni della memoria

Il server database PostgreSQL assegna una determinata area di memoria per l'intera durata alla memorizzazione nella cache dei dati in modo da migliorare gli accessi in lettura e scrittura. Questa area di memoria rappresenta i buffer condivisi. La quantità di memoria utilizzata dal database per i buffer di memoria condivisa è controllata dai parametri shared_buffers.

Oltre all'area di memoria condivisa, ogni processo di backend consuma memoria per eseguire operazioni all'interno di un server DB. La quantità di memoria utilizzata si basa sui valori impostati per i parametri work_mem e maintenance_work_mem. Per ulteriori informazioni, consulta la documentazione di PostgreSQL per la configurazione del server.

Se osservi continuamente un'elevata pressione di memoria sull'istanza di database, considera la possibilità di ridurre i valori di questi parametri. È possibile ridurre i valori di questi parametri nel gruppo di parametri personalizzato collegato all'istanza di database.

Gestione del piano di interrogazione Aurora PostgreSQL

Usa la gestione dei piani di query di Amazon Aurora PostgreSQL Edition compatibile per controllare come e quando i piani di esecuzione delle query cambiano. Per ulteriori informazioni, consulta Procedure ottimali per la gestione dei piani di query compatibile con Aurora PostgreSQL.

Risoluzione dei problemi relativi alle query lente

In genere le query sono a esecuzione lenta quando si verificano problemi di infrastruttura o il consumo complessivo di risorse è particolarmente elevato. Le query a esecuzione lenta potrebbero anche essere il risultato di una pianificazione delle query non ottimale da parte del pianificatore. Il pianificatore di query PostgreSQL utilizza le statistiche create per la tabella per creare i piani di query. Questi piani potrebbero essere influenzati a causa delle modifiche dello schema e delle statistiche non aggiornate. Anche l'aumento della tabella e degli indici potrebbe causare query a esecuzione lenta.

Il daemon autovacuum è responsabile della creazione di processi di lavoro di autovacuum che rimuovono le tuple inattive dalle tabelle ogni volta che viene raggiunta la soglia di tupla inattive. Il daemon autovacuum è responsabile anche dell'esecuzione dell'operazione ANALYZE che aggiorna le statistiche memorizzate per una determinata tabella.

Esegui la seguente query per trovare informazioni su:

  • Tuple morte
  • Numero di operazioni di autovacuum o vuoto
  • Numero di esecuzioni di autoanalisi o analisi
  • Quando queste operazioni sono state eseguite l'ultima volta
SELECT schemaname, relname, n_live_tup,n_dead_tup, last_autoanalyze, last_analyze, last_autovacuum, last_vacuum,
autovacuum_count+vacuum_count vacuum_count, analyze_count+autoanalyze_count analyze_count 
FROM pg_stat_user_tables
ORDER BY 5 DESC;

Puoi utilizzare la vista pg_stat_activity per trovare i dati relativi alle attività correnti. Questa vista fornisce il PID di backend, la query e altri dettagli. Per individuare le query a esecuzione prolungata, emetti questa query:

SELECT pid, datname, query, extract(epoch from now()) - extract(epoch from xact_start) AS duration, case
WHEN wait_event IS NULL THEN 'CPU' 
ELSE wait_event_type||':'||wait_event end wait FROM pg_stat_activity
WHERE query!=current_query() AND xact_start IS NOT NULL ORDER BY 4 DESC;

Tieni presente che le query in attesa di blocchi potrebbero essere lente. Pertanto, verifica se la query è in attesa di blocchi emettendo questa query:

SELECT pid, virtualtransaction AS vxid, locktype AS lock_type, mode AS lock_mode, granted,fastpath,
CASE
WHEN virtualxid IS NOT NULL AND transactionid IS NOT NULL
THEN virtualxid || ' ' || transactionid
WHEN virtualxid::text IS NOT NULL
THEN virtualxid
ELSE transactionid::text
END AS xid_lock, relname, page, tuple, classid, objid, objsubid
FROM pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE -- do not show our view’s locks
pid != pg_backend_pid() AND
virtualtransaction IS DISTINCT FROM virtualxid
ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7;

L'istanza di RDS per PostgreSQL consente di creare l'estensione pg_stat_statements dall'interno del database:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Utilizzando pg_stat_statements, potrai visualizzare le statistiche delle query. Prima di creare l'estensione, assicurati di aggiungere la voce pg_stat_statements a shared_preload_libraries.

Nota: è possibile modificare i parametri per questo modulo solo quando un gruppo di parametri personalizzato è collegato all'istanza database.

Utilizza queste query per identificare le principali query SQL che influiscono sulle prestazioni dell'istanza.

Per trovare le query che passano più tempo nel database, esegui questa query per PostgreSQL versione 12 e precedenti:

SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY 3 DESC;

Esegui questa query per PostgreSQL versione 13 e successive:

SELECT query, calls, total_plan_time+total_exec_time AS total_time, mean_plan_time + mean_exec_time AS mean_time FROM pg_stat_statements ORDER BY 3 DESC;

Per trovare le query con un rapporto di accessi alla cache di buffer inferiore, esegui questa query per PostgreSQL versione 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;

Esegui questa query per PostgreSQL versione 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 5 ASC
LIMIT 10;

Inoltre, puoi acquisire query o piani di query a esecuzione prolungata nei registri degli errori del database impostando in modo appropriato il parametro log_min_duration_statement per l'istanza e utilizzando il modulo auto_explain. Il parametro log_min_duration_statement determina la durata di ogni istruzione completata da registrare se l'istruzione è stata eseguita per almeno il periodo di tempo specificato. Ad esempio, se imposti questo parametro su 250 ms, allora saranno registrate tutte le istruzioni SQL eseguite per 250 ms o più. Il modulo auto_explain consente di acquisire il piano di spiegazione delle query eseguite nel database.

Puoi acquisire il piano anche utilizzando i comandi explain ed explain analyze. Identifica le opportunità di ottimizzazione delle query in base al modulo auto_explain o i comandi explain per la query. Per ulteriori informazioni, consulta Utilizzo di EXPLAIN nella documentazione di PostgreSQL.

Se il tuo sistema è ben ottimizzato e stai ancora affrontando una limitazione delle risorse, prendi in considerazione la possibilità di scalare la classe di istanze verso l'alto. Scala la classe dell'istanza verso l'alto per allocare l'istanza di database con più risorse di calcolo e memoria. Per ulteriori informazioni, consulta Specifiche hardware per le classi di istanze di database.