Risoluzione iniziale dei problemi di prestazioni di PostgreSQL comuni in RDS per PostgreSQL

Questa guida illustra i quattro problemi di prestazioni più comuni che influiscono RDS per PostgreSQL database: aumento delle tabelle e degli indici, esaurimento delle risorse di interrogazione parallela, elevata pressione di connessione e autenticazione e ottimizzazione automatica. Usa questa guida come lista di controllo diagnostica di primo passaggio in caso di peggioramento delle prestazioni, prima di iniziare un'indagine più approfondita.

Ogni sezione descrive i sintomi che potresti riscontrare, fornisce domande diagnostiche per confermare la causa principale e consiglia passaggi specifici per porvi rimedio.

Comprendere le regressioni delle prestazioni «nulla è cambiato»

I carichi di lavoro PostgreSQL vengono spesso eseguiti senza problemi per settimane o mesi, quindi subiscono un improvviso peggioramento delle prestazioni anche se il codice dell'applicazione e i pattern di query appaiono invariati. Ciò accade perché l'ambiente del database non è mai veramente statico: diversi fattori invisibili cambiano nel tempo e possono innescare modifiche ai piani o conflitti di risorse:

• L'accumulo di bloat è una modifica del carico di lavoro. Il controllo della concorrenza multiversione (MVCC) di PostgreSQL mantiene le vecchie versioni delle righe fino a quando non le recupera con autovacuum. Quando le tuple morte si accumulano più velocemente di quanto autovacuum possa elaborarle, le tabelle e gli indici diventano fisicamente più grandi. Il Query Planner può quindi passare da scansioni efficienti degli indici a scansioni sequenziali perché le stime dei costi cambiano all'aumentare delle dimensioni della tabella. Il tuo codice SQL non è cambiato, ma i dati visualizzati dal planner sì.

• I nuovi valori dei parametri sono una modifica del carico di lavoro. Una query con parametri che offre buone prestazioni per un intervallo di valori può avere prestazioni scadenti quando l'applicazione inizia a utilizzare un intervallo diverso. PostgreSQL può riutilizzare un piano di esecuzione generico che non tiene conto della distorsione dei dati nel nuovo intervallo, oppure le statistiche del pianificatore potrebbero non riflettere accuratamente la distribuzione di tali valori. Quando è presente anche un eccesso di dati, l'impatto si aggrava: un piano non ottimale ora esegue la scansione di una quantità significativamente maggiore di dati non funzionanti.

• Le statistiche possono essere obsolete anche quando l'autovacuum è in esecuzione. Trigger autovacuum ANALYZE in base al numero di righe inserite o aggiornate, non al fatto che la distribuzione dei dati sia cambiata in modo significativo. Se l'applicazione passa a richiedere un intervallo di valori o una finestra temporale diversi, le stime dei costi del pianificatore potrebbero essere imprecise anche se l'autovacuum è stato eseguito di recente.

• La crescita complessiva del database è una modifica del carico di lavoro. Man mano che le tabelle crescono nel tempo, aumenta il volume delle pagine di dati che le query devono scansionare. Le query che hanno ottenuto buoni risultati su tabelle più piccole possono sviluppare latenza all'aumentare delle dimensioni della tabella, anche quando la logica delle query e gli indici rimangono invariati. Monitora FreeStorageSpace per tracciare le tendenze di crescita dello storage.

Quando analizzi le regressioni delle prestazioni in cui «non è cambiato nulla», considera l'accumulo di ingombri eccessivi, i nuovi intervalli di valori dei parametri, la crescita complessiva del database e le statistiche obsolete come le cause principali più probabili. Utilizzate i passaggi diagnostici descritti in questa guida per confermare il fattore applicabile.

Per ulteriori informazioni, consulta gli argomenti seguenti:

• Attività di manutenzione per i database PostgreSQL in Amazon RDS e Amazon Aurora (AWS Guida prescrittiva)

• Ottimizzazione delle prestazioni delle query PostgreSQL (AWS Guida prescrittiva)

• Utilizzo dei parametri sull'istanza database RDS for PostgreSQL

• metriche a livello di istanza di Amazon RDS (monitor FreeStorageSpace per le tendenze di crescita dello storage)

Lista di controllo diagnostica rapida

Utilizza i seguenti passaggi di triage ordinati quando analizzi per la prima volta un problema di prestazioni:

1. Verifica pg_stat_activity. Analizza il numero di connessioni, le sessioni inattive durante le transazioni e le query di lunga durata. Per ulteriori informazioni, consulta Concetti essenziali per l'ottimizzazione di RDS per PostgreSQL.

2. Verifica la presenza di gonfiore. Cerca «high» n_dead_tup nel pg_stat_user_tables e considera l'utilizzo pgstattuple per una misurazione precisa. Per ulteriori informazioni, vedere Rimuovere il gonfiore dalle tabelle con pg_repack.

3. Verifica pg_stat_user_tables. Cerca «high» n_dead_tup valori e obsoleti last_autovacuum timestamp. Per ulteriori informazioni, vedere Utilizzo di PostgreSQL autovacuum su Amazon RDS.

4. Recensione EXPLAIN ANALYZE sulle domande lente. Cerca piani paralleli e scansioni sequenziali su tavoli di grandi dimensioni. Per ulteriori informazioni, vedere Le migliori pratiche per le interrogazioni parallele in RDS per PostgreSQL.

5. Verifica CloudWatch e metriche di Performance Insights. Esamina l'utilizzo della CPU, il numero di connessioni, gli IOPS e la memoria liberabile. Per ulteriori informazioni, vedere Monitoraggio di Amazon RDS. Per gli eventi di attesa e le azioni correttive più comuni, vedi Eventi di attesa RDS per PostgreSQL.

6. Rivedi il tuo gruppo di parametri DB. Verifica max_parallel_workers_per_gather e impostazioni di autovacuum. Per ulteriori informazioni, consulta Utilizzo dei parametri sull'istanza database RDS for PostgreSQL.

Ingrandisci della tabella e dell'indice

L'ingrandimento delle tabelle e degli indici si verifica quando le tuple morte si accumulano nelle tabelle più velocemente di quanto l'autovacuum possa recuperarle. Nel tempo, ciò causa un graduale peggioramento delle prestazioni delle query, un maggiore utilizzo dello storage e piani di query non ottimali.

Caratteristiche

• Degrado graduale delle prestazioni delle query nel corso di settimane o mesi

• L'utilizzo dello storage è in crescita nonostante un volume di dati stabile

• Il pianificatore di query sceglie le scansioni sequenziali rispetto alle scansioni degli indici a causa di statistiche obsolete

• Alto dead_tuple_count nelle statistiche della tabella

Diagnosi

È possibile stimare il gonfiore in tutte le tabelle interrogando il catalogo di sistema. Questo approccio non richiede alcuna estensione:

SELECT schemaname, relname,
       n_dead_tup,
       n_live_tup,
       ROUND(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

Per risolvere il problema del gonfiore, puoi usare pg_repack estensione per riorganizzare tabelle e indici con un blocco minimo. Per ulteriori informazioni, vedere Rimuovere il gonfiore dalle tabelle con pg_repack e Rimuovi il gonfiore da Amazon Aurora e RDS per PostgreSQL con pg_repack.

Importante

Anziché affidarti alla manutenzione manuale, assicurati che l'autovacuum sia abilitato e ottimizzato correttamente per il tuo carico di lavoro. Vedi Sintonizzazione automatica per consigli di ottimizzazione.

Esaurimento delle risorse di interrogazione parallela

PostgreSQL può eseguire query in parallelo per migliorare le prestazioni per scansioni e aggregazioni sequenziali di grandi dimensioni. Tuttavia, ogni lavoratore parallelo è un processo di backend completo che conta contro max_worker_processes (e il limite secondario max_parallel_workers) e assegna i propri work_mem. Una singola query con 4 lavoratori paralleli può consumare centinaia di megabyte di memoria e una notevole quantità di CPU. In condizioni di elevata concorrenza, un parallelismo eccessivo può esaurire rapidamente CPU e memoria.

I sintomi più comuni includono picchi improvvisi della CPU, utilizzo elevato della memoria per query e aumento DatabaseConnections nel CloudWatch senza modifiche all'applicazione. È inoltre possibile osservare eventi di attesa come IPC:BgWorkerStartup, IPC:ExecuteGather, e IPC:ParallelFinish. Per ulteriori informazioni su questi eventi di attesa, vedi IPC:parallel eventi di attesa.

Per la maggior parte dei carichi di lavoro di produzione OLTP e ad alta concorrenza, disabilita il parallelismo automatico impostando max_parallel_workers_per_gather = 0 nel tuo gruppo di parametri DB. È quindi possibile abilitare selettivamente il parallelismo per sessioni di analisi o reporting specifiche impostando il parametro per sessione o per ruolo.

Per una guida dettagliata sulla diagnosi e il controllo del comportamento delle query parallele, vedi Le migliori pratiche per le interrogazioni parallele in RDS per PostgreSQL.

Elevata pressione di connessione e autenticazione

L'abbandono delle connessioni, ossia l'apertura e la chiusura frequenti delle connessioni al database senza raggruppamento, comporta un sovraccarico dell'autenticazione e può esaurire gli slot di connessione disponibili. Le connessioni inattive che rimangono aperte occupano anche degli slot senza svolgere attività utili.

Caratteristiche

• Elevato total_auth_attempts nel monitoraggio di Performance Insights. Per ulteriori informazioni, vedere Non-native contatori per RDS per PostgreSQL.

• Tempi di instaurazione della connessione lenti

• FATAL: too many connections for role o remaining connection slots are reserved sbagli

• picchi di CPU correlati all'interruzione della connessione

Diagnosi

Esegui la seguente query per verificare lo stato attuale della connessione:

SELECT
  setting::int AS max_connections,
  (SELECT count(*) FROM pg_stat_activity) AS current_connections,
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle') AS idle_connections,
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_settings
WHERE name = 'max_connections';

Un numero elevato di idle o idle in transaction connessioni relative a max_connections indica che le connessioni non vengono rilasciate correttamente. Idle-in-transaction le connessioni sono particolarmente problematiche perché bloccano i blocchi e impediscono all'autovacuum di recuperare le tuple morte.

Correzione

• Implementa il pool di connessioni. Usa PgBouncer o Proxy Amazon RDS per ridurre il numero di connessioni dirette al database. Il pool di connessioni riutilizza le connessioni esistenti anziché crearne di nuove per ogni richiesta.

• Impostare idle_in_transaction_session_timeout. Questo parametro termina automaticamente le sessioni che rimangono inattive in una transazione oltre la durata specificata. Ciò impedisce che le transazioni inattive di lunga durata mantengano blocchi e blocchino l'autovacuum.

• Esamina la gestione delle connessioni delle applicazioni. Assicurati che l'applicazione chiuda tempestivamente le connessioni e non mantenga aperte le transazioni più a lungo del necessario.

Nota

Gli operatori di interrogazione parallela consumano CPU e memoria. Se osservi l'esaurimento delle risorse insieme all'attività di interrogazione parallela, vedi Esaurimento delle risorse di interrogazione parallela per indicazioni sul controllo dell'utilizzo dei lavoratori paralleli.

Utilizzo degli eventi di attesa di Performance Insights per la risoluzione dei problemi

Performance Insights acquisisce gli eventi di attesa che mostrano dove il database sta trascorrendo il tempo. Quando indaghi sui problemi di prestazioni, gli eventi di attesa ti aiutano a identificare se il collo di bottiglia è la CPU, I/O, blocco, comunicazione di rete o tra processi. Le categorie di eventi di attesa più comuni che compaiono durante i problemi descritti in questa guida includono:

• CPU — La sessione è attiva sulla CPU o è in attesa della CPU. Gli elevati eventi di attesa della CPU sono spesso correlati a un parallelismo eccessivo o a piani di interrogazione inefficienti che eseguono la scansione di tabelle gonfie.

• IPC (comunicazione tra processi) — Eventi di attesa come IPC:BgWorkerStartup, IPC:ExecuteGather, e IPC:ParallelFinish indicare le spese generali di coordinamento delle interrogazioni parallele.

• IO — Attendi eventi come IO:DataFileRead indicano che le query stanno leggendo i dati dalla memoria perché le pagine richieste non sono nella memoria condivisa. Ciò è comune quando le tabelle gonfie superano la cache del buffer.

• Blocca — Attendi eventi come Lock:transactionid e Lock:tuple indicare una contesa tra le sessioni. Idle-in-transaction le connessioni possono contenere blocchi che bloccano altre interrogazioni e l'autovacuum.

• Cliente — Attendi eventi come Client:ClientRead indicare che il database è in attesa dell'invio dei dati da parte dell'applicazione. Gli eventi di attesa elevati del client possono indicare un'interruzione della connessione o una latenza di rete.

Per un riferimento completo agli eventi di attesa che in genere indicano problemi di prestazioni e alle relative azioni correttive consigliate, vedere Eventi di attesa RDS per PostgreSQL.

Sintonizzazione automatica

L'autovacuum è il processo in background che recupera le tuple morte, previene l'ingrossamento di tabelle e indici, aggiorna le statistiche del planner e protegge dalla sovrapposizione degli ID delle transazioni. Le impostazioni di autovacuum predefinite sono conservative e progettate per database di piccole dimensioni. High-write i carichi di lavoro di produzione richiedono quasi sempre un'ottimizzazione.

Quando l'autovacuum non riesce a tenere il passo con il carico di lavoro di scrittura, si accumula un ingombro eccessivo, le statistiche di Planner diventano obsolete e aumenta il rischio che l'ID delle transazioni venga annullato. Se age(relfrozenxid) si avvicina a 2 miliardi, il database si chiude per prevenire il danneggiamento dei dati.

Per una guida dettagliata sull'ottimizzazione dei parametri dell'autovacuum, sul monitoraggio dell'attività del vuoto e sulla configurazione delle sostituzioni per tabella, consulta Lavorare con PostgreSQL autovacuum su Amazon RDS.

Informazioni correlate

• Le migliori pratiche per le interrogazioni parallele in RDS per PostgreSQL

• Gestione delle connessioni non funzionanti in PostgreSQL

• Lavorare con PostgreSQL autovacuum su Amazon RDS

• Attività DBA comuni per RDS per PostgreSQL

• Utilizzo di PostgreSQL autovacuum su Amazon RDS

• Informazioni sull'autovacuum in ambienti Amazon RDS per PostgreSQL