View a markdown version of this page

Risoluzione iniziale dei problemi di prestazioni di PostgreSQL più comuni in PostgreSQL RDS per PostgreSQL - Amazon Relational Database Service
Elenco di controllo diagnostico rapidoIncremento delle tabelle e degli indiciEsaurimento delle risorse di interrogazione parallelaElevata pressione di connessione e autenticazioneUtilizzo degli eventi di attesa di Performance Insights per la risoluzione dei problemiOttimizzazione automatica del vuotoInformazioni correlate

Le traduzioni sono generate tramite traduzione automatica. In caso di conflitto tra il contenuto di una traduzione e la versione originale in Inglese, quest'ultima prevarrà.

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

Questa guida illustra i quattro problemi di prestazioni più comuni che riguardano : aumento di tabelle e indici, esaurimento delle risorse di query parallele, elevata pressione di connessione e autenticazione e ottimizzazione automatica del vuoto. Usa questa guida come checklist diagnostica di primo passaggio in caso di peggioramento delle prestazioni, prima di iniziare un'indagine più approfondita.

Ogni sezione descrive i sintomi che potreste osservare, fornisce domande diagnostiche per confermare la causa principale e consiglia misure correttive specifiche.

Comprendere le regressioni prestazionali del tipo «nulla è cambiato»

I carichi di lavoro PostgreSQL spesso vengono eseguiti senza problemi per settimane o mesi, quindi subiscono un improvviso peggioramento delle prestazioni anche se il codice dell'applicazione e i modelli di query rimangono 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 ingombri è una modifica del carico di lavoro. Il controllo simultaneo multiversion (MVCC) di PostgreSQL mantiene le vecchie versioni di riga fino a quando autovacuum non le recupera. Quando le tuple morte si accumulano più velocemente di quanto l'autovacuum sia in grado di elaborarle, le tabelle e gli indici diventano fisicamente più grandi. Il pianificatore di query può quindi passare da scansioni efficienti degli indici a scansioni sequenziali perché le stime dei costi cambiano all'aumentare delle dimensioni della tabella. Il 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 funziona bene 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 aumento del volume, l'impatto si aggrava: un piano non ottimale ora analizza una quantità significativamente maggiore di dati non funzionanti.

  • Le statistiche possono essere obsolete anche quando funziona l'autovacuum. Autovacuum ANALYZE si attiva 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 interrogare un intervallo di valori o una finestra temporale diversi, le stime dei costi fornite dal pianificatore potrebbero essere imprecise anche se 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 e gli indici delle query rimangono invariati. Monitora per tenere traccia delle tendenze di crescita dello storage.

Quando analizzi le regressioni delle prestazioni in cui «non è cambiato nulla», considera l'accumulo di ingrossamenti, 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:

Elenco di controllo diagnostico rapido

Utilizzate i seguenti passaggi di valutazione ordinati quando esaminate per la prima volta un problema di prestazioni:

  1. Controllatepg_stat_activity. Guarda il numero di connessioni, le sessioni inattive nelle transazioni e le query di lunga durata. Per ulteriori informazioni, consulta

  2. Controlla il gonfiore. Cerca un valore alto n_dead_tup pg_stat_user_tables e valuta la possibilità di utilizzarlo pgstattuple per una misurazione precisa. Per ulteriori informazioni, vedi dalle tabelle con pg_repack.

  3. Controlla. pg_stat_user_tables Cerca n_dead_tup valori alti e last_autovacuum timestamp obsoleti. Per ulteriori informazioni, consulta Lavorare con PostgreSQL autovacuum su Amazon PostgreSQL.

  4. Revisione delle query lente. EXPLAIN ANALYZE Cerca piani paralleli e scansioni sequenziali su tabelle di grandi dimensioni. Per ulteriori informazioni, consulta per le query parallele in RDS per PostgreSQL.

  5. Metriche Check CloudWatch and Performance Insights. Verifica l'utilizzo della CPU, il numero di connessioni, gli IOPS e la memoria liberabile. Per ulteriori informazioni, consulta . Per gli eventi di attesa comuni e le azioni correttive, consulta wait events.

  6. Rivedi il gruppo di parametri del database. Controllo max_parallel_workers_per_gather e impostazioni dell'autovacuum. Per ulteriori informazioni, consulta Ottimizzazione dei parametri PostgreSQL in Amazon RDS e Amazon Aurora.

Incremento delle tabelle e degli indici

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

Caratteristiche

  • Graduale peggioramento delle prestazioni delle query nell'arco di settimane o mesi

  • L'utilizzo dello storage cresce nonostante un volume di dati stabile

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

  • dead_tuple_countStatistiche tabellari ad alto contenuto

Diagnosi

È possibile stimare il gonfiore su 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 bloat, puoi utilizzare l'pg_repackestensione per riorganizzare tabelle e indici con un blocco minimo. Per ulteriori informazioni, consulta Rimuovere il bloat dalle tabelle con pg_repack Rimuovere il bloat dalle tabelle con pg_repack .

Importante

Piuttosto che affidarti alla manutenzione manuale, assicurati che l'autovacuum sia abilitato e ottimizzato correttamente per il tuo carico di lavoro. Consulta i consigli di ottimizzazione. Ottimizzazione automatica del vuoto

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 worker parallelo è un processo di backend completo che conta contro max_worker_processes (oltre al limite secondariomax_parallel_workers) e ne alloca i propri. work_mem Una singola query con 4 worker paralleli può consumare centinaia di megabyte di memoria e una notevole quantità di CPU. In caso di concorrenza elevata, 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 accessi elevati senza modifiche all'applicazione. DatabaseConnections CloudWatch È inoltre possibile osservare eventi di attesa come IPC:BgWorkerStartupIPC:ExecuteGather, e. IPC:ParallelFinish Per ulteriori informazioni su questi eventi di attesa, consulta di IPC:parallel attesa.

Per la maggior parte dei carichi di lavoro OLTP e di produzione ad alta concorrenza, disabilita il parallelismo automatico max_parallel_workers_per_gather = 0 impostandolo nel gruppo di parametri DB. Puoi quindi 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, consulta .

Elevata pressione di connessione e autenticazione

L'abbandono delle connessioni, ossia l'apertura e la chiusura frequenti delle connessioni al database senza raggruppamento, crea un sovraccarico di autenticazione e può esaurire gli slot di connessione disponibili. Le connessioni inattive che rimangono aperte consumano anche gli slot senza eseguire operazioni utili.

Caratteristiche

  • Elevato livello di monitoraggio total_auth_attempts di Performance Insights. Per ulteriori informazioni, vedere RDS for Non-native PostgreSQL.

  • Tempi di creazione della connessione lenti

  • FATAL: too many connections for roleo remaining connection slots are reserved errori

  • I picchi della CPU erano correlati all'abbandono 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 idle di 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 e impediscono all'autovacuum di recuperare le tuple morte.

Correzione

  • Implementa il pooling delle connessioni. Usa PgBouncer il Amazon RDS per ridurre il numero di connessioni dirette al tuo 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. In questo modo si evita che le transazioni inattive di lunga durata blocchino e blocchino l'autovacuum.

  • Rivedi la gestione della connessione dell'applicazione. Assicurati che l'applicazione chiuda tempestivamente le connessioni e non mantenga aperte le transazioni più a lungo del necessario.

Nota

I Query Worker paralleli consumano CPU e memoria. Se osservi l'esaurimento delle risorse insieme all'attività di interrogazione parallela, consulta 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 trascorre il tempo. Quando si esaminano i problemi di prestazioni, gli eventi di attesa aiutano a identificare se il punto debole è dovuto alla CPU, al blocco I/O, alla rete o alla comunicazione 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 eventi di attesa della CPU elevati sono spesso correlati a un parallelismo eccessivo o a piani di interrogazione inefficienti che scansionano tabelle ingombranti.

  • IPC (comunicazione tra processi): attendi eventi come IPC:BgWorkerStartupIPC:ExecuteGather, e indica il sovraccarico di coordinamento delle query IPC:ParallelFinish parallele.

  • IO — Eventi di attesa, ad esempio, che IO:DataFileRead indicano che le query stanno leggendo i dati dallo storage perché le pagine richieste non si trovano nella memoria condivisa. Questo è comune quando le tabelle gonfie superano la cache del buffer.

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

  • Client: eventi di attesa, ad esempio che Client:ClientRead indicano che il database è in attesa dell'invio dei dati da parte dell'applicazione. Gli eventi di attesa elevati del client possono indicare l'interruzione della connessione o la latenza della rete.

Per un riferimento completo degli eventi di attesa che indicano comunemente problemi di prestazioni e le relative azioni correttive consigliate, consulta wait events.

Ottimizzazione automatica del vuoto

Autovacuum è il processo in background che recupera le tuple non funzionanti, previene il sovraccarico di tabelle e indici, aggiorna le statistiche del planner e protegge dall'alterazione degli ID delle transazioni. Le impostazioni predefinite di autovacuum sono conservative e progettate per database di piccole dimensioni. High-write i carichi di lavoro di produzione richiedono quasi sempre una regolazione.

Quando autovacuum non riesce a tenere il passo con il carico di lavoro di scrittura, si accumula un eccesso di dati, le statistiche del planner diventano obsolete e aumenta il rischio che gli ID delle transazioni vengano alterati. Se si age(relfrozenxid) avvicina ai 2 miliardi, il database si chiude per evitare il danneggiamento dei dati.

Informazioni correlate