Best practice per le query parallele in Aurora PostgreSQL RDS per

L'esecuzione parallela delle query è una funzionalità di PostgreSQL che consente di suddividere una singola query SQL in attività più piccole che vengono elaborate contemporaneamente da più processi di lavoro in background. Invece di eseguire una query interamente in un unico processo di backend, PostgreSQL può distribuire parti della query, come scansioni, join, aggregazioni o ordinamento, su più core della CPU. Il processo leader coordina questa esecuzione e raccoglie i risultati dai parallel worker.

Tuttavia, per la maggior parte dei carichi di lavoro di produzione, in particolare i sistemi OLTP ad alta concorrenza, consigliamo di disabilitare l'esecuzione automatica delle query parallele. Sebbene il parallelismo possa accelerare le interrogazioni su set di dati di grandi dimensioni nei carichi di lavoro di analisi o reporting, introduce rischi significativi che spesso superano i vantaggi in ambienti di produzione affollati.

L'esecuzione parallela comporta inoltre un notevole sovraccarico. Ogni parallel worker è un processo backend PostgreSQL completo, che richiede il forking del processo (copia delle strutture di memoria e inizializzazione dello stato del processo) e l'autenticazione (utilizzo degli slot di connessione oltre il limite). max_connections Inoltre, ogni worker utilizza la propria memoria, anche work_mem per le operazioni di ordinamento e hashing, con più worker per query, l'utilizzo della memoria si moltiplica rapidamente (ad esempio, 4 worker × 64 MB = 256 MB per query). work_mem Di conseguenza, le query parallele possono consumare molte più risorse di sistema rispetto alle query a processo singolo. Se non ottimizzati correttamente, possono portare alla saturazione della CPU (più worker sovraccaricano la capacità di elaborazione disponibile), a un aumento del cambio di contesto (il sistema operativo passa spesso da un processo di lavoro all'altro, aggiungendo un sovraccarico e riducendo il throughput) o all'esaurimento della connessione (poiché ogni worker parallelo consuma uno slot di connessione, una singola query con 4 worker utilizza 5 connessioni in totale, 1 leader e 4 worker, che possono esaurire rapidamente il pool di connessioni in caso di elevata concorrenza, impedendo nuove connessioni connessioni client e cause di errori delle applicazioni). Questi problemi sono particolarmente gravi nei carichi di lavoro ad alta concorrenza in cui più query possono tentare l'esecuzione parallela contemporaneamente.

PostgreSQL decide se utilizzare il parallelismo in base alle stime dei costi. In alcuni casi, il pianificatore può passare automaticamente a un piano parallelo se sembra più economico anche se in pratica non è ideale. Ciò può accadere se le statistiche dell'indice sono obsolete o se il bloat rende le scansioni sequenziali più interessanti delle ricerche nell'indice. A causa di questo comportamento, i piani paralleli automatici possono talvolta introdurre regressioni nelle prestazioni delle query o nella stabilità del sistema.

Per ottenere il massimo dalle query parallele in Aurora PostgreSQL , è importante testarle e ottimizzarle in base al carico di lavoro, monitorare l'impatto sul sistema e disabilitare la selezione automatica del piano parallelo a favore del controllo a livello di query.

Parametri di configurazione

PostgreSQL utilizza diversi parametri per controllare il comportamento e la disponibilità delle query parallele. Comprenderli e ottimizzarli è fondamentale per ottenere prestazioni prevedibili:

Parametro	Description	Predefinita
max_parallel_workers	Numero massimo di processi di lavoro in background che possono essere eseguiti in totale	MASSIMO ($ DBInstance VCPU/2,8)
max_parallel_workers_per_gather	Numero massimo di lavoratori per nodo del piano di interrogazione (ad esempio, per) Gather	2
parallel_setup_cost	Costo aggiuntivo del Planner per l'avvio di un'infrastruttura di query parallela	1000
parallel_tuple_cost	Costo per tupla elaborata in modalità parallela (influisce sulla decisione del pianificatore)	0.1
force_parallel_mode	Forza il pianificatore a testare i piani paralleli (off,on,regress)	off

Considerazioni chiave

• max_parallel_workerscontrolla il pool totale di lavoratori paralleli. Se impostato su un valore troppo basso, alcune query potrebbero tornare all'esecuzione seriale.

• max_parallel_workers_per_gatherinfluisce sul numero di lavoratori che una singola query può utilizzare. Un valore più alto aumenta la concorrenza, ma anche l'utilizzo delle risorse.

• parallel_setup_coste parallel_tuple_cost influiscono sul modello di costo del pianificatore. La riduzione di questi può aumentare le probabilità di scegliere piani paralleli.

• force_parallel_modeè utile per i test ma non deve essere utilizzato in produzione a meno che non sia necessario.

Nota

Il valore predefinito del max_parallel_workers parametro viene calcolato dinamicamente in base alla dimensione dell'istanza utilizzando la formulaGREATEST($DBInstanceVCPU/2, 8). Ciò significa che quando si ridimensiona l'istanza dell'istanza Aurora a una dimensione di calcolo maggiore con più vCPUs, il numero massimo di worker paralleli disponibili aumenterà automaticamente. Di conseguenza, le query precedentemente eseguite in modo seriale o con parallelismo limitato possono improvvisamente utilizzare più worker paralleli dopo un'operazione di scalabilità verticale, con conseguenti aumenti imprevisti dell'utilizzo della connessione, dell'utilizzo della CPU e del consumo di memoria. È importante monitorare il comportamento delle query parallele dopo ogni evento di scalabilità del calcolo e, max_parallel_workers_per_gather se necessario, apportare modifiche per mantenere un utilizzo prevedibile delle risorse.

Identifica l'utilizzo delle query parallele

Le query possono passare a piani paralleli basati sulla distribuzione dei dati o sulle statistiche. Esempio:

SELECT count(*) FROM customers WHERE last_login < now() - interval '6 months';

Questa query potrebbe utilizzare un indice per i dati recenti, ma passare a una scansione sequenziale parallela per i dati storici.

È possibile registrare i piani di esecuzione delle query caricando il auto_explain modulo. Per ulteriori informazioni, consulta Registrazione dei piani di esecuzione delle query nel AWS Knowledge Center.

È possibile monitorare i piani di esecuzione delle query nell'istanza DB Aurora PostgreSQL per rilevare i piani di esecuzione che contribuiscono al carico corrente del database e tenere traccia delle statistiche sulle prestazioni dei piani di esecuzione nel tempo utilizzando il parametro. aurora_compute_plan_id Per ulteriori informazioni, consulta Monitoraggio dei piani di esecuzione delle query e della memoria di picco per Aurora PostgreSQL

È possibile monitorare gli eventi di attesa relativi a CloudWatch Database Insights for Parallel Query. Per ulteriori informazioni sugli eventi di attesa correlati a Parallel Query, consulta IPC:Parallel wait events

Dalla versione 18 di PostgreSQL, puoi monitorare l'attività dei lavoratori paralleli utilizzando nuove colonne in e: pg_stat_databasepg_stat_statements

• parallel_workers_to_launch: Numero di lavoratori paralleli previsto per il lancio

• parallel_workers_launched: Numero di lavoratori paralleli effettivamente lanciati

Queste metriche aiutano a identificare le discrepanze tra il parallelismo pianificato e quello effettivo, il che può indicare vincoli di risorse o problemi di configurazione. Usa le seguenti query per monitorare l'esecuzione parallela:

Per le metriche dei lavoratori paralleli a livello di database:

SELECT datname, parallel_workers_to_launch, parallel_workers_launched
FROM pg_stat_database
WHERE datname = current_database();

Per le metriche dei lavoratori paralleli a livello di query

SELECT query, parallel_workers_to_launch, parallel_workers_launched
FROM pg_stat_statements
ORDER BY parallel_workers_launched;

Come controllare il parallelismo

Esistono diversi modi per controllare il parallelismo delle interrogazioni, ciascuno progettato per scenari e requisiti diversi.

Per disabilitare il parallelismo automatico a livello globale, modifica il gruppo di parametri modifica il gruppo di :

max_parallel_workers_per_gather = 0;

Per le impostazioni persistenti e specifiche dell'utente, il comando ALTER ROLE consente di impostare parametri che verranno applicati a tutte le sessioni future per un determinato utente.

Esempio:

ALTER ROLE username SET max_parallel_workers_per_gather = 4;assicura che ogni volta che questo utente si connette al database, le sue sessioni utilizzeranno questa impostazione parallel worker quando richiesto.

Il controllo a livello di sessione può essere ottenuto utilizzando il comando SET, che modifica i parametri per la durata della sessione corrente del database. Ciò è particolarmente utile quando è necessario modificare temporaneamente le impostazioni senza influire sugli altri utenti o sulle sessioni future. Una volta impostati, questi parametri rimangono validi fino al ripristino esplicito o fino al termine della sessione. I comandi sono semplici:

SET max_parallel_workers_per_gather = 4;
-- Run your queries
RESET max_parallel_workers_per_gather;

Per un controllo ancora più granulare, SET LOCAL consente di modificare i parametri per una singola transazione. È ideale quando è necessario regolare le impostazioni per un insieme specifico di query all'interno di una transazione, dopodiché le impostazioni tornano automaticamente ai valori precedenti. Questo approccio aiuta a prevenire effetti indesiderati su altre operazioni all'interno della stessa sessione.

Utilizzare Query Plan Management (QPM)

In Aurora PostgreSQL, la funzionalità Query Plan Management (QPM) è progettata per garantire l’adattabilità e la stabilità del piano indipendentemente dalle modifiche apportate all’ambiente di database che potrebbero causare la regressione del piano di query. Per ulteriori informazioni, vedere Panoramica sulla gestione del piano di query PostgreSQL di Aurora. La gestione del piano di query fornisce un certo controllo sull'ottimizzatore. Esaminare i piani approvati in QPM per assicurarsi che siano in linea con le attuali impostazioni di parallelismo. Aggiornare o rimuovere i piani obsoleti che potrebbero forzare un’esecuzione parallela non ottimale.

È anche possibile correggere i piani utilizzando pg_hint_plan. Per ulteriori informazioni, consulta Correzione dei piani utilizzando pg_hint_plan. È possibile utilizzare l’hint denominato Parallel per imporre l’esecuzione parallela. Per maggiori informazioni, consulta la pagina relativa agli hint per i piani paralleli.

Diagnosi del comportamento delle interrogazioni parallele

Utilizzare EXPLAIN (ANALYZE, VERBOSE) per confermare se una query utilizza l'esecuzione parallela:

• Cerca nodi come GatherGather Merge, oParallel Seq Scan.

• Confronta i piani con e senza parallelismo.

Per disabilitare temporaneamente il parallelismo per il confronto:

SET max_parallel_workers_per_gather = 0;
EXPLAIN ANALYZE <your_query>;
RESET max_parallel_workers_per_gather;