Ottimizzazione del numero di worker e dei costi di Gather

PostgreSQL può suddividere una singola query tra più core della CPU usando i parallel worker. Il processo leader avvia i worker di supporto, ognuno dei quali esegue la scansione di una porzione dei dati; i risultati vengono poi riuniti in un nodo Gather.

Il parallelismo non è gratuito. L'avvio dei worker, la copia delle tuple attraverso la memoria condivisa e la sincronizzazione nel punto di raccolta richiedono tutti tempo. Per insiemi di risultati piccoli, l'overhead può superare il guadagno di velocità.

• I carichi di lavoro CPU-bound (grandi scansioni sequenziali, aggregazioni, hash join) sono quelli che ne traggono maggior beneficio.
• Le query brevi sensibili alla latenza generalmente no.

Questa lezione illustra i parametri che determinano quanti worker vengono eseguiti e quando il planner ritiene che il parallelismo sia conveniente.

Un piano parallelo ha una struttura caratteristica. Sotto il nodo Gather (o Gather Merge) si trovano le operazioni partial eseguite in parallelo dai worker; sopra di esso, tutto viene eseguito in modo seriale dal leader.

Legga il piano con EXPLAIN (ANALYZE, VERBOSE) e cerchi Workers Planned e Workers Launched. Una differenza tra i due valori indica che, al momento dell'esecuzione, il sistema ha esaurito gli slot worker disponibili.

Il parametro più importante per singola query è max_parallel_workers_per_gather. Stabilisce il numero massimo di worker che un singolo nodo Gather può richiedere. Il valore predefinito è 2.

Impostarlo a 0 disabilita completamente il parallelismo per quella sessione. Aumentarlo consente alle scansioni di grandi dimensioni di usare più core, ma solo entro i limiti imposti dalla dimensione della tabella e dagli altri parametri.

• È un limite per Gather, non per query. Una query con due nodi Gather può usare fino al doppio di questo numero di worker.
• Può essere modificato per sessione con SET, così può ottimizzare un singolo report pesante senza modificare la configurazione globale.

Il numero di worker è limitato da tre impostazioni annidate. Il numero effettivo di worker è il minimo tra tutti e tre i limiti e la stima basata sulla dimensione calcolata dal planner.

• max_parallel_workers_per_gather — per nodo Gather (predefinito: 2).
• max_parallel_workers — pool a livello dell'intero cluster dedicato alle query parallele (predefinito: 8).
• max_worker_processes — numero totale di processi worker in background per l'intero server, condiviso con estensioni e replica (predefinito: 8).

Se aumenta il limite per Gather ma lascia basso max_parallel_workers, le query concorrenti entreranno in competizione e molte verranno eseguite con meno worker di quelli pianificati.

Anche con limiti elevati, il planner dimensiona il numero di worker in base alla dimensione della relazione, usando una regola logaritmica. Una tabella deve essere grande almeno quanto min_parallel_table_scan_size (predefinito: 8MB) prima che venga preso in considerazione anche un solo worker parallelo. In linea di massima, ogni aumento di 3× della dimensione aggiunge un worker.

• Da 8MB a 24MB, poi 72MB, poi 216MB... → 1, 2, 3... worker.
• Il risultato viene quindi limitato da max_parallel_workers_per_gather.

Per questo una tabella molto piccola non viene mai elaborata in parallelo, indipendentemente da quanto aumenti i limiti, e a volte è necessario ridurre min_parallel_table_scan_size per testare i piani paralleli su dati di piccole dimensioni.

parallel_setup_cost rappresenta il costo una tantum dell'avvio dei worker e della configurazione della memoria condivisa. Il valore predefinito è 1000, un numero elevato nelle unità di costo del planner, scelto intenzionalmente affinché l'ottimizzatore eviti il parallelismo per le query poco costose.

Se il Suo hardware avvia rapidamente i worker e nota che piani paralleli validi vengono rifiutati, riducendo questo valore il planner sarà più propenso a usare il parallelismo. Lo aumenti per scoraggiare il parallelismo su un server OLTP molto carico.

parallel_tuple_cost rappresenta il costo del trasferimento di ogni tupla da un worker al leader attraverso la coda di raccolta. Il valore predefinito è 0.1 per tupla.

Questo è il motivo principale per cui il parallelismo perde il vantaggio nelle query che restituiscono molte righe: ogni tupla restituita comporta un costo. I piani paralleli sono vantaggiosi quando i worker riducono i dati, filtrandoli in modo significativo o aggregandoli, così poche tuple attraversano il nodo Gather.

• Molte righe in uscita da Gather + parallel_tuple_cost elevato → il planner preferisce l'esecuzione seriale.
• Lo riduca con cautela se il trasferimento delle tuple nella memoria condivisa è effettivamente poco costoso.

Può fissare il numero di worker per una tabella specifica con il parametro di storage parallel_workers. Questo sostituisce la formula del planner basata sulla dimensione per le scansioni di quella tabella, pur restando soggetto ai limiti globali.

È utile per una tabella dei fatti molto utilizzata e sottoposta ad aggregazioni pesanti, quando desidera sempre, ad esempio, 6 worker indipendentemente dal valore predefinito calcolato con la regola logaritmica.

L'ottimizzazione è empirica. Esegua la stessa query con numeri diversi di worker e confronti il tempo effettivo di esecuzione, non solo il costo del planner. Il guadagno di velocità è sublineare e alla fine si stabilizza o diminuisce con l'aumentare dell'overhead di coordinamento.

• Controlli Workers Launched: se è inferiore a Workers Planned, il pool è esaurito e aumentare il limite per Gather non sarà utile.
• Rendimenti decrescenti: passare da 4 a 8 worker spesso offre un guadagno molto inferiore rispetto al passaggio da 1 a 2.

Il pool dei worker paralleli è condiviso dall'intero cluster. In condizioni di concorrenza, molte query pianificate con 4 worker ciascuna possono richiedere complessivamente più di quanto consentito da max_parallel_workers; di conseguenza, alcune vengono eseguite con un numero ridotto di worker o senza worker.

Nei sistemi con carichi OLTP elevati, questa è una caratteristica utile: si desidera che le transazioni brevi abbiano la priorità sulla CPU, invece di perderla a causa di una query di report che ha riservato 8 worker. Strategie:

• Mantenga max_parallel_workers_per_gather su valori moderati (2-4) a livello globale.
• Lo aumenti per sessione solo per i job batch o di report noti.
• Si assicuri che max_worker_processes sia abbastanza alto da lasciare spazio anche a estensioni e replica.

Riunendo tutti gli elementi per un carico di lavoro analitico CPU-bound su un server con 8 core:

• Impostare max_worker_processes = 16 (per lasciare margine alle estensioni).
• Impostare max_parallel_workers = 8 (uno per core).
• Mantenere globale max_parallel_workers_per_gather = 2 per proteggere la latenza OLTP.
• Per le sessioni dei report notturni, usare SET max_parallel_workers_per_gather = 6.
• Ridurre parallel_setup_cost / parallel_tuple_cost solo dopo che EXPLAIN ANALYZE ha dimostrato che piani validi vengono rifiutati.

Convalidi sempre i risultati con tempi reali e imposti valori per tabella solo per tabelle molto utilizzate, stabili e ben comprese.

Ora dispone di un modello mentale per ottimizzare le query parallele:

• Il budget dei worker è stratificato: max_parallel_workers_per_gather ≤ max_parallel_workers ≤ max_worker_processes, con un'ulteriore riduzione in base alla dimensione della tabella.
• I costi determinano la scelta: parallel_setup_cost è il costo fisso di avvio; parallel_tuple_cost applica un costo a ogni tupla che attraversa Gather, quindi il parallelismo è vantaggioso quando i worker riducono i dati.
• È essenziale saper leggere il piano: confronti Workers Planned e Workers Launched per distinguere un problema di pianificazione da un problema del pool durante l'esecuzione.
• Ottimizzi per sessione, non globalmente, per proteggere la latenza OLTP consentendo ai job batch di usare i core.

Misuri con EXPLAIN ANALYZE e tempi reali; non si affidi mai soltanto al costo del planner.