Tuning in PostgreSQL

PostgreSQL e' un potente DBMS relazionale Open Source noto per la robustezza e la ricchezza di funzionalita'.
Questo documento descrive gli elementi di interessanti per il tuning in Postgresql sia nell'ottimizzazione di singoli statement SQL che dell'intero sistema.
Il documento fa riferimento alla versione 9.4 di PostgreSQL su Linux ma e', mutatis mutandis, valido anche per le altre versioni.
Vengono riportati elementi utili all'ottimizzazione SQL ed al tuning PostgreSQL: Ottimizzazione delle prestazioni, Tuning SQL, Tuning PostgreSQL, ...

Statistiche prestazionali in PostgreSQL e' sicuramente il documento piu' utile per monitorare le prestazioni di PostgreSQL. Un documento introduttivo su PostgreSQL e' Introduzione a PostgreSQL, un documento piu' completo e' Qualcosa in piu' su PostgreSQL.

Ottimizzazione e tuning

Le attivita' di ottimizzazione e tuning di sistemi database sono complesse e richiedono esperienze specifiche. Entrambe sono rivolte al miglioramento delle prestazioni. L'attivita' di ottimizzazione viene svolta in fase di progettazione e di sviluppo (eg. scrittura degli statement SQL). Con il tuning invece si cerca di trovare il compresso migliore tra tutti gli elementi configurabili del sistema.

E' possibile intervenire a diversi livelli:

Nel seguito ci limiteremo solo ad alcuni argomenti specifici: l'SQL ed il tuning di PostgreSQL.

Ottimizzazione SQL

Il linguaggio SQL e' apparentemente molto semplice, tuttavia per sfruttare appieno le possibilita' che offre e' necessario conoscerne le particolarita' e gli elementi specifici che ogni diversa implementazione presenta. PostgreSQL offre diverse estensioni del linguaggio SQL che comprendono nuove clausole, funzioni di utilita' ed un forte orientamento agli oggetti.

PostgreSQL utilizza un ottimizzatore cost-based. Quando viene sottomesso uno statement SQL l'ottimizzatore determina il query tree da utilizzare con un algoritmo genetico basato sulle statistiche. L'algoritmo genetico e' utilizzato per ridurre il numero delle combinazioni dei possibili percorsi di ricerca.
E' naturalmente molto importante che le statistiche su cui si basa l'ottimizzatore siano aggiornate. PostgreSQL esegue in automatico [NdE nelle release piu' recenti] le attivita' di analyze (raccolta delle statistiche necessarie all'ottimizzatore) e di vacuum (cancellazione dei blocchi non piu' necessari al MVCC). Il livello di dettaglio dell'analyze e' determinato dal parametro default_statistics_target (default: 100), per tabelle con distribuzioni di dati particolari tale valore puo' essere modificiato con ALTER TABLE SET STATISTICS.
L'ottimizzatore puo' essere parametrizzato con una serie di impostazioni nel file postgresql.conf.

Per verificare i tempi di esecuzione di uno statement SQL da psql basta utilizzare il comando \timing. Per ottenere dettagli su come l'ottimizzatore ha pianificato l'esecuzione di una query si utilizza la clausola EXPLAIN:

EXPLAIN SELECT * FROM tenk1 WHERE unique1 < 100 AND stringu1 = 'xxx'; QUERY PLAN ------------------------------------------------------------------------------ Bitmap Heap Scan on tenk1 (cost=5.04..229.43 rows=1 width=244) Recheck Cond: (unique1 < 100) Filter: (stringu1 = 'xxx'::name) -> Bitmap Index Scan on tenk1_unique1 (cost=0.00..5.04 rows=101 width=0) Index Cond: (unique1 < 100)

Vengono visualizzati gli algormitmi di accesso ai dati scelti dall'ottimizzatore per eseguire la query. Oltre alle tabelle interessate sono riportati gli eventuali indici: fondamentali per un accesso efficiente.
Con EXPLAIN ANALYZE la query viene anche eseguita e quindi riportati i dettagli sui tempi effettivamente impegnati da ogni passo scelto dall'ottimizzatore.

E' anche possibile modificare alcuni parametri dell'ottimizzatore nella sessione corrente ed analizzare le eventuali differenze sul query plan:

SET enable_seqscan=off; SET random_page_cost = 2.0; SET work_mem = '16MB'; explain select ...

Naturalmente un corretto disegno logico e disegno fisico della base dati sono necessari perche' gli statement SQL possano essere eseguiti con buone prestazioni. In particolare la presenza degli indici e' fondamentale per una corretta esecuzione delle query (anche se vanno definiti i soli indici necessari per non rallentare le esecuzioni degli statement di DML).
Il disegno di una base dati puo' essere molto differente a seconda del tipo di utilizzo. Con un OLTP il disegno e' normalizzato, se necessario overnormalizzato, e sono presenti i soli indici necessari; per un DWH il disegno e' fortemente denormalizzato, sono tipicamente presenti tabelle/viste aggregate e molteplici indici di ricerca. Con un disegno opportuno Postgres e' in grando di ospitare entrambe le tipologie di database anche se e' tecnicamente piu' adatto ad un uso OLTP.

PostgreSQL crea automaticamente gli indici per le primary key e quando viene definito uno unique constraint. Attenzione: gli indici per le foreign key debbono invece essere creati esplicitamente.
In generale si mettono sempre tutte le PK e unique (e' automatico), tutte le FK (non e' automatico in Postgres), gli indici composti delle condizioni utilizzate nelle query piu' frequenti partendo dalla colonna piu' selettiva. Gli indici composti "valgono" anche come indici di grado inferiore se non sono presenti le colonne piu' a destra. Gli indici possibili su una tabella di 10 colonne sono oltre 4 milioni (4! +10*3!...): vanno definiti solo gli indici necessari!
PostgreSQL consente la creazione di indici basati su espressioni o su funzioni (purche' dichiarate IMMUTABLE); vanno usati con cautela ma possono consentire un accesso molto efficiente se i richiami nelle applicazioni utilizzano in tal modo le ricerche.
In caso di caricamento massivo dei dati di una relazione e' conveniente non avere gli indici e crearli solo alla fine.

Un indice BTREE non puo' essere utilizzato con l'operatore LIKE quanto il collate e' diverso da "C". Per utilizzare l'indice anche con altri collate e' necessario crearlo con uno specifico operator class:
 CREATE INDEX emp_surname ON scott.emp (surname bpchar_pattern_ops);

L'apertura di nuove sessioni in PostgreSQL ha un peso e richiede un certo tempo... per questo sono molto utilizzati i connection pool. Nell'universo Postgres e' piu' noto e' PgBouncer ma i piu' diffusi sono ospitati sugli applicativi. Un corretto dimensionamento dei pool e' molto importante per le prestazioni.

Utilizzare le Stored Functions consente spesso buoni vantaggi prestazionali evitando traffico ed accessi al DB. PostgreSQL consente di scrivere Stored Functions [NdA e le Stored Procedures dalla v.11] in diversi linguaggi anche se il piu' utilizzato e' il PL/pgSQL. Le statistiche sull'utilizzo e la durata delle Stored Functions sono riportate nella vista pg_stat_user_functions [NdA che viene popolata impostando il parametro track_functions = all | pl ].
Le funzioni possono essere dichiarate STABLE (il risultato non cambia nella stessa transazione) o IMMUTABLE (il risultato non cambia a fronte degli stessi parametri); queste due impostazioni possono far risparmiare richiami rispetto al default VOLATILE che deve essere eseguito ogni volta.
Quando e' possibile utilizzare il linguaggio sql l'overhead e' minore e puo' diventare sensibile quando il numero di chiamate e' elevato. Nei casi delle funzioni piu' semplici, se realizzate in SQL, le Stored Functions possono essere inserite inline nell'SQL chiamante e non vengono neanche tracciate dalla vista pg_stat_user_functions.

Un'ultima importante segnalazione: le applicazioni debbono effettuare le commit! Se non vengono effettuare le commit i lock non vengono rilasciati e, se questo avviene per un lungo periodo, i processi di autovacuum non possono liberare lo spazio. Determinare le sessioni che non hanno fatto commit e' molto semplice perche' sono in stato "Idle in Transaction" anziche' "Idle".

Cosa ottimizare?

Tutti gli statement SQL eseguiti su una base dati dovrebbero essere stati ottimizzati in fase di sviluppo... Ma questo non e' sempre possibile: vediamo quindi velocemente come determinare quali sono gli statement SQL che incidono maggiormente sulle prestazioni.

Sicuramente la prima cosa da controllare e' la vista la pg_stat_activity che riporta lo stato di tutte le connessioni alla base dati. pg_stat_activity riporta l'ultima query eseguita e l'indicazione se e' ancora attiva o meno. Controllando quali sono gli statement attivi nei momenti di picco di utilizzo della base dati e concentrando l'attenzione su questi si ottengono velocemente i primi risultati.

La seconda tecnica utilizzabile e' l'impostazione del parametro log_min_duration_statement di postgresql.conf che registra sul log gli statement di durata maggiore del parametro indicato (in millisecondi). Il parametro e' dinamico ed e' sufficiente un pg_ctl reload per modificarlo.

Le modalita' precedenti non sono in grado di rilevare statement di breve durata ma eseguiti centinaia o migliaia di volte che possono sono quindi fondamentali per le prestazioni nel loro complesso.
Per analizzare i tempi di ogni statement SQL viene utilizzata l'extension pg_stat_statements. Ecco le prime 20 query ordinate per durata complessiva:

SELECT query, pg_get_userbyid(userid), calls, round((total_time::numeric / calls::numeric)/1000,3) AS avg_exec_time, round(total_time) AS total_time, rows, round((100.0*shared_blks_hit/nullif(shared_blks_hit+shared_blks_read,0)),2) AS hit_pct FROM pg_stat_statements ORDER BY total_time DESC LIMIT 20;

Maggiori dettagli sono riportati nel documento Statistiche prestazionali in PostgreSQL ( Logging, pg_stat_statements, ...).

Tuning PostgreSQL

Il tuning di PostgreSQL si esegue monitorando le prestazioni generali della base dati e modificando i parametri contenuti nel file postgresql.conf. I parametri a disposizione del DBA sono centinaia... vedremo solo i piu' significativi.

La dimensione della buffer cache allocata e' impostata con shared_buffers. Naturalmente il valore va impostato a secondo della quantita' di RAM disponibile sul server: tipicamente si inizia il 25% della RAM. Quindi si monitorano l'Hit ratio e l'Hit count.
L'hit ratio degli indici dovrebbe raggiungere il 100% e lo stesso per le tabelle di dimensioni minori e piu' utilizzate: se non e' cosi' la cache va aumentata per migliorare le prestazioni. Un hit count basso e' invece indice che la cache puo' essere diminuita. Naturalmente il sistema ospite non deve entrare in paginazione/swapping ed e' quindi importante monitorarne il comportamento (ed eventualmente limitare l'impatto con vm.swappiness=1).
L'impostazione della effective_cache_size non comporta alcuna allocazione ma permette all'ottimizzatore un calcolo piu' preciso delle cache disponibili. Si imposta al 50% della RAM senza ulteriore tuning.

La work_mem e' la quantita' di RAM utilizzata dalle singole sessioni per il sort (ed altre funzioni interne). Il valore di default e' basso e si preferisce alzarlo per evitare sort su disco. La work_mem viene allocata solo quando serve ma e' difficile valutare quanti sort possano occorrere su tutte le connessioni presenti. Quello che e' certo e' che il numero di connessioni non puo' superare max_connections! La maintenance_work_mem e' la quantita' di memoria riservata ai processi di ANALYZE e VACUUM; il default e' un po' basso: 64MB adatto solo se non vi sono molti DML, comunque si imposta sempre al di sotto del 5% della RAM.

La scrittura dei WAL utilizza i wal_buffers come cache. Anche se possono avere una dimensione inferiore non c'e' ragione ad non allocare almeno 16MB poiche' l'impatto sulla memoria e' limitato [NdA il parametro da impostare e' il numero di buffer ciascuno di 8K] [NdE dalla 9.1 con l'impostazione di default -1 viene allocato il 3% del parametro shared_buffers che generalmente e' un'impostazione piu' ragionevole dei 64KB utilizzati in precedenza come default].
Il numero di checkpoint_segments impostato per default e' basso [NdA vale 3] e viene generalmente aumentato per rendere piu' morbido l'utilizzo dell'I/O; generalmente si imposta a 16 per salire fino a 256 (che corriponde a 4GB) [NdE dalla 9.5 l'impostazione checkpoint_segments e' stata sostituita con max_wal_size che ha come un default ragionevole di 1GB]. Il checkpoint_timeout di default e' impostato a 5 minuti, un valore generalmente ragionevole ma che puo' essere aumentato (eg. 30min). Viene invece spesso variato per un tuning efficace il parametro checkpoint_completion_target impostandolo tra 0.5 (default) e 0.9 (massimo consigliabile). Il tuning viene effettuato aumentando il valore di checkpoint_segments se i checkpoint avvengono con troppa frequenza; nel contempo e' importante monitorare anche l'I/O del sistema [NdA dalla versione 9.5 il parametro checkpoint_segments e' stato sostituito dalla coppia min_wal_size e max_wal_size con impostazioni di default molto piu' ragionevoli].

Nelle versioni recenti di PostgreSQL i thread di vacuum vengono eseguiti automaticamente... pero' nel caso in cui il carico sia elevato e le tabelle avessero lock potrebbero essere escluse con un notevole peggioramento delle prestazioni [NdA senza che si liberari spazio]. La verifica sulle tabelle si effettua con la funzione pgstattuple(); nel caso in cui le percentuali dead_tuple_percent OR free_percent siano elevati e' opportuno eseguire un VACUUM ANALYZE sulle tabelle piu' importanti o su tutto il DB [NdA oppure VACUUM FULL ANALYZE che libera piu' spazio per il SO ma richiede un lock esclusivo]. Un'alternativa, ma generalmente si fanno entrambe le cose, e' quella di rendere piu' frequenti ed aggressivi i thread di VACUUM con autovacuum_vacuum_scale_factor = 0.05 (anche meno se le tabelle sono particolarmente grandi) e con autovacuum_vacuum_cost_limit = 2000. Vacuuming rule: If it hurts, you’re not doing it often enough.

Solo in casi molto particolari si modificano i valori di default dei parametri dell'ottimizzatore; ad esempio: random_page_cost, enable_seqscan, enable_bitmapscan, ...

Riassumendo quanto riportato in questo documento, una configurazione iniziale per un sistema dedicato a PostgreSQL con 4GB di RAM da cui partire per effettuare un tuning specifico, e' la seguente:

listen_addresses = '*'
max_connections = 100                   
shared_buffers = 1024MB
effective_cache_size = 2048MB               
work_mem = 2MB 
maintainance_work_mem = 20MB                       
wal_buffers = 16MB                     
checkpoint_segments = 16           
checkpoint_completion_target = 0.9
log_min_duration_statement=5000
log_statement=ddl
shared_preload_libraries = 'pg_stat_statements'

Attenzione: se utilizzate una versione 9.5 o superiore non impostate i checkpoint_segments: il parametro non esiste piu' (ed i default dei parametri sostitutivi sono adatti: si modificano solo se vengono segnalati troppi checkpoint nel log).


Titolo: Tuning PostgreSQL
Livello: Esperto (4/5)
Data: 14 Febbraio 2015 ❤️
Versione: 1.0.5 - 14 Febbraio 2021
Autore: mail [AT] meo.bogliolo.name