Il meccanismo del VACUUM e' molto importante per un DBA Postgres
perche', in alcuni casi, puo' essere fonte di problemi anche bloccanti.
Nel tempo le diverse versioni di PostgreSQL hanno introdotto miglioramenti
ed automatismi, quindi in molti casi non e' necessario un
intervento manuale e neppure il tuning dei parametri di configurazione.
E' pero' sempre opportuno il controllo del vacuuming e, quando le basi dati sono
utilizzate in modo significativo, anche un tuning ed una gestione specifici.
Una base dati Postgres su cui il vacuum non viene eseguito correttamente
cresce di dimensioni e le sue prestazioni peggiorano progressivamente nel tempo
mentre una base dati gestita in modo opportuno resta sempre ben performante.
Il VACUUM e' quindi molto importante la gestione di Postgres.
In questa pagina cercheremo di vedere i principali aspetti,
teorici e pratici del VACUUM in Postgres.
Alcuni dei contenuti di questa pagina sono rivolti ad un pubblico adulto,
o almeno competente su Postgres, per un'introduzione alle funzioni di base
si puo' leggere questo documento.
Questa pagina e' stata preparata utilizzando la versione 15 di PostgreSQL ma i concetti sono validi anche per le altre versioni e, quando utile, sono riportate eventuali differenze.
La comprensione dell'implementazione del MVCC (Multiversion Concurrency Control) in Postgres e' fondamentale per comprendere l'importanza dell'attivita' di VACUUM.
Ogni riga in Postgres ha un'indicazione della transazione che ha eseguito l'INSERT iniziale (t_xmin)
e della transazione che l'ha, eventualmente, cancellata (t_xmax).
Dato un id di transazione la transazione corrente e' il presente, le transazioni con un valore piu' alto sono il futuro
e quelle con un valore piu' basso sono il passato.
Naturalmente non e' possibile vedere righe che verranno inserite nel futuro o righe che sono gia' state cancellate dalle altre transazioni. Il controllo dell'MVCC di Postgres opera confrontando la transazione attuale, txid_current(), con l'indicatore della transazione che ha eseguito l'INSERT xmin della riga e della transazione che ha eventualmente eseguito la DELETE xmax. In questo modo e' possibile vedere solo le righe su cui e' stato eseguito il commit e corrette per ogni transazione. L'isolation level di Postgres e' Read Committed, quanto appena descritto vale per le transazioni su cui e' stato eseguito il commit.
Come vengono trattate le UPDATE in Postgres?
Praticamente come una DELETE seguita da una INSERT con i nuovi dati.
Viene infatti indicata la cancellazione della vecchia tupla impostando il campo t_xmax
con la transazione corrente
e viene inserita una nuova tupla con valore t_xmin sempre impostato con il valore della transazione corrente.
Dovrebbe essere chiaro quindi che in Postgres le cancellazioni e le update non sono fisiche ma solo logiche e che in generale lo spazio per mantenere i dati di una tabella sottoposta a modifiche puo' crescere in modo significativo.
Tutto questo avviene per consentire all'MVCC di conoscere la storia di ogni riga e rispondere correttamente
a tutte le query.
Ma un database non puo' crescere in modo indefinito perche' esegue solo cancellazioni logiche...
continuate a leggere!
Naturalmente non e' necessario mantenere per sempre le righe cancellate. Dopo un certo periodo di tempo tutte le transazioni che erano attive prima che la riga fosse stata cancellata termineranno ed i vecchi dati (dead tuples) possono essere cancellati liberando cosi' spazio per nuove righe.
Ad occuparsi della cancellazione fisica in PostgreSQL c'e' il VACUUM. Quando il vacuum viene eseguito su una tabella scandisce tutti i blocchi, controlla la presenza di righe cancellate in modo logico e l'assenza di transazioni che possano accedere ai vecchi dati; se tutti i controlli sono positivi la riga viene cancellata fisicamente dal blocco e lo spazio viene liberato per i futuri inserimenti di dati sulla stessa tabella.
Il vacuum normalmente aggiorna solo le strutture interne dei puntatori dei blocchi. Il VACUUM ANALYZE esegue un VACUUM e quindi ANALYZE sulle tabelle selezionate. E' una combinazione molto utile di queste due routine di manutenzione che si avvantaggia di un'esecuzione congiunta e quindi e' utilizzata molto spesso.
Il normale vacuum ed il vacuum analyze possono operare in parallelo con le normali attivita' di lettura e scrittura sulle tabelle. Infatti non richiede alcun lock esclusivo. L'unica avvertenza e' quella di non eseguirlo in modo massivo nei periodi di maggiore attivita' della base dati perche' fa un uso intensivo dell'I/O.
Come controllare se una tabella ha dead tuples?
Come lanciare il vacuum su una tabella?
Naturalmente sarebbe noioso per un DBA eseguire il vacuum di tutte le tabelle... continuate a leggere!
Dalla versione 8.1 di Postgres [NdA 2005-11] il DBA non ha piu' la necessita' di eseguire il vacuum manualmente: e' attivo il processo autovacuum!
L'autovacuum controlla le modifiche occorse sulle tabelle e se le modifiche sono state significative esegue un VACUUM.
L'autovacuum e' un processo coscenzioso ma anche gentile e pigro... L'autovacuum analizza ciclicamente ogni tabella di ogni database dell'istanza postgres ed esegue tutti i task di manutenzione necessari. Vengono trattate solo le tabelle che hanno avuto un numero significativo di modifiche [NdA per default almeno il 10% per effettuare l'ANALYZE ed almeno il 20% per effettuare il VACUUM]. Inoltre se una tabella e' in uso l'autovacuum ripassa piu' tardi a controllarla. Lavorare meno, lavorare tutti: l'autovacuum puo' eseguire piu' thread in parallelo, ma il limite di lavoro effettuato e' il totale complessivo. Infine l'autovacuum, dopo aver fatto un po' di lavoro, si prende un po' di tempo per un sonnellino. In questo modo le attivita' di autovacuum non pesano mai troppo e sono praticamente trascurabili come impatto per le prestazioni del sistema.
Come controllare se vengono eseguiti i vacuum e gli autovacuum?
Lanciato manualmente o in automatico le attivita' del vacuum sono molto importanti in Postgres... continuate a leggere!
I compiti del VACUUM, comunque sia stato attivato, sono diversi ed e' ora importante riportarli in modo piu' completo:
Sono tutti compiti importanti e necessari per la manutezione di una base dati. Il vacuum quindi non e' solo necessario per ripulire la base dati ma anche per mantenerla sempre ottimizzata ed e' quindi importante quanto il tuning della base dati e l'ottimizzazione dell'SQL.
Fino ad ora abbiamo parlato solo di heap, ovvero dei blocchi utilizzati per le tabelle. In realta' il vacuum deve operare anche su tutti gli indici e sui TOAST. Tecnicamente l'attivita' e' piu' complessa ma il concetto di base e' lo stesso: vanno eliminate le dead tuples, gli eventuali TOAST ed i riferimenti contenuti negli indici.
La cancellazione fisica delle dead tuples da parte del normale vacuum non libera spazio a livello del file system perche'... continuate a leggere!
L'esecuzione di un vacuum effettua fisicamente la cancellazione delle righe non piu' necessarie all'MVCC; il vacuum viene eseguito all'interno dei singoli blocchi dati di 8KB. Poiche' lo spazio liberato e' all'interno di un file molto piu' ampio, che contiene tutti i dati di una tabella, in realta' la dimensione dei file di heap o di indice non diminuiscono quasi mai. Semplicemente viene liberato spazio per la futura crescita della tabella all'interno del file.
In genere questo non e' un problema finche' l'overhead resta limitato.
Vi sono pero' casi in cui diventa importante liberare lo spazio anche al sistema operativo.
In questo caso e' necessario lanciare il vacuum in modalita' FULL
che opera in modo differente: vengono creati di nuovo tutti i file che ospiteranno la tabella
e gli indici associati, i dati vengono ricaricati in modo ottimizzato ed infine i file precedenti
vengono cancellati liberando tutto lo spazio possibile.
Poiche' le righe vengono spostate dal VACUUM FULL e' possibile che venga modificato il ctid
(Current Tuple ID)
[NdA la colonna di sistema ctid e' un coppia di valori
(block number, tuple index within block) che indentifica la posizione fisica di una riga nella tabella]
che infatti con PostgreSQL non e' un riferimento assoluto ma e' variabile nel tempo.
L'esecuzione del VACUUM FULL richiede piu' tempo, richiede piu' spazio, utilizza un lock esclusivo sulle tabelle coinvolte ed e' piu' pesante in termini di I/O rispetto ad un normale VACUUM. Su una base dati di produzione si cerca quindi di evitare l'esecuzione dei vacuum full limitandoli ai soli casi realmente necessari.
Come lanciare il vacuum full su una tabella? Attenzione utilizza un lock esclusivo!
Ora che ne abbiamo visto tutte le funzionalita' e' opportuno vedere la sintassi completa del comando SQL di VACUUM che e':
VACUUM [ ( option [, ...] ) ] [ table_and_columns [, ...] ]
VACUUM [ FULL ] [ FREEZE ] [ VERBOSE ] [ ANALYZE ] [ table_and_columns [, ...] ]
where option can be one of:
FULL [ boolean ]
FREEZE [ boolean ]
VERBOSE [ boolean ]
ANALYZE [ boolean ]
DISABLE_PAGE_SKIPPING [ boolean ]
SKIP_LOCKED [ boolean ]
INDEX_CLEANUP { AUTO | ON | OFF }
PROCESS_TOAST [ boolean ]
TRUNCATE [ boolean ]
PARALLEL integer
Se non vengono indicate tabelle/colonne il vacuum viene lanciato sull'intero DB corrente.
Per eseguire il VACUUM bisogna essere proprietari dell'oggetto o amministratori
[NdA per il momento].
Oltre al comando VACUUM SQL esiste un comando a livello di sistema operativo
che fa... esattamente le stesse cose!
Il comando e' vacuumdb e si rimanda alla
documentazione ufficiale
per tutti i dettagli.
Le opzioni del comando da sistema operativo sono simili a quelle dell'istruzione SQL
con l'aggiunta di --all che semplicemente agisce su tutti i database.
In questo breve capitolo vedremo l'impatto che il vacuum ha sulle applicazioni e, ancora piu' importante, l'impatto che possono avere le applicazioni sul vacuum.
Su una base dati in cui il vacuum non viene eseguito a sufficienza
le applicazioni risultano rallentate.
Le ragioni sono due: la lettura dei dati richiede piu' tempo perche'
vanno acceduti piu' blocchi e l'ottimizzatore puo' non scegliere
l'execution plan migliore perche' le statistiche non sono affidabili.
A volte anche le letture da tabelle "praticamente vuote" risultano di lunga durata.
Le applicazioni di solito non hanno altri impatti dovuti alle dead rows
perche' semplicemente non le vedono!
L'esecuzione dell'autovacuum ed, in generale, anche quella del vacuum lanciato da SQL
tipicamente non ha impatti sulle applicazioni: su un sistema correttamente
dimensionato non si hanno rallentamenti percettibili.
Il VACUUM FULL invece ha impatto sulle applicazioni perche' richiede un lock
esclusivo sulle tabelle interessate. E' quindi possibile che l'esecuzione
del vacuum full debba attendere il rilascio di lock da parte delle applicazioni
e viceversa il vacuum full puo' bloccare gli accessi in scrittura alle tabelle.
Un'eventuale lancio di VACUUM FULL va quindi eseguito in modo coordinato
con l'utilizzo delle applicazioni eventualmente in una finestra di manutenzione.
Ancora piu' importanti sono pero' gli impatti che le applicazioni possono avere sulla corretta esecuzione del vacuum.
La prima condizione che non deve verificarsi e' che siano presenti a lungo sessioni nello stato
Idle in transaction.
Se le sessioni restano in tale stato perche' hanno iniziato una transazione
ma non l'hanno confermata con COMMIT, il vacuum non potra' mai eliminare
le righe cancellate dopo tale transazione.
Lo stato Idle in transaction deve essere quindi solo temporaneo per le applicazioni
perche' ha impatti sia sulle altre sessioni applicative (perche' mantiene attivi eventuali lock)
che sulla corretta esecuzione del vacuum.
La presenza continuativa di tale stato nelle sessioni utente e' da considerare un errore con Postgres.
Anche le transazioni e gli statement di lunga durata impediscono il vacuum...
In questo caso pero' non e' possibile agire sulle applicazioni se non ottimizzando l'SQL.
Piu' evidente e' il caso dei lock. Quando sono presenti lock l'autovacuum non puo' operare
e quando vengono richiesti l'autovacuum gentilmente interrompe
le attivita' sulla tabella in oggetto
[NdA segnalando sul log ERROR: canceling autovacuum task].
In tutti questi casi e' opportuno modificare le applicazioni o la loro schedulazione
in modo da consentire all'autovacuum di operare.
Un'altra particolare condizione applicativa che deve essere evitata e' quella di transazioni 2PC (Two Phase Commit) non confermate. Per eseguire in modo sicuro transazioni tra istanze diverse viene utilizzato il protocollo 2PC che prevede fasi specifiche per la sua corretta esecuzione. Eventuali transazioni 2PC sospese impediscono l'esecuzione del VACUUM.
Alcune logiche applicative a volte generano un numero elevato di dead tuples.
Per esempio creare una riga con una INSERT e modificarla successivamente con piu'
UPDATE non e' il modo piu' efficiente per Postgres ed andrebbe evitato.
Se comunque le update sono necessarie e' possibile impostare il parametro di storage
fillfactor per lasciare spazio sufficiente per la modifiche successive.
Se possibile sarebbe meglio effettuare le UPDATE solo su colonne non indicizzate:
in questo modo e' possibile effettuare delle HOT Updates (Heap Only Tuple Updates)
che sono molto piu' veloci e non richiedono un vacuum successivo.
Un ultimo suggerimento per gli applicativi...
la gestione dinamica delle DDL, le reindex, le analyze, sono tutte molto
utili ma sarebbe opportuno venissero concordate o gestite dai DBA.
A volte per eccesso di zelo si rischia di peggiorare
le cose impedendo per esempio l'esecuzione dell'autovacuum.
Come controllare la presenza di transazioni attive da tempo?
Come controllare la presenza di transazioni 2PC?
Che si fa di eventuali sessioni che bloccano il vacuum?
Ovviamente si ammazzano! Basta un pg_terminate_backend()
Un poco piu' difficile e' terminare le prepared transactions
perche' persistono anche ad un riavvio dell'istanza.
In questo caso bisogna utilizzare il comando
ROLLBACK PREPARED.
I parametri relativi al VACUUM ed all'AUTOVACUUM sono parecchi:
select name, setting, unit, source, context, min_val, max_val --short_desc
from pg_settings
where name like '%vacuum%'
order by name;
name | setting | unit | source | context | min_val | max_val
---------------------------------------+------------+------+---------+------------+---------+------------
autovacuum | on | | default | sighup | |
autovacuum_analyze_scale_factor | 0.1 | | default | sighup | 0 | 100
autovacuum_analyze_threshold | 50 | | default | sighup | 0 | 2147483647
autovacuum_freeze_max_age | 200000000 | | default | postmaster | 100000 | 2000000000
autovacuum_max_workers | 3 | | default | postmaster | 1 | 262143
autovacuum_multixact_freeze_max_age | 400000000 | | default | postmaster | 10000 | 2000000000
autovacuum_naptime | 60 | s | default | sighup | 1 | 2147483
autovacuum_vacuum_cost_delay | 2 | ms | default | sighup | -1 | 100
autovacuum_vacuum_cost_limit | -1 | | default | sighup | -1 | 10000
autovacuum_vacuum_insert_scale_factor | 0.2 | | default | sighup | 0 | 100
autovacuum_vacuum_insert_threshold | 1000 | | default | sighup | -1 | 2147483647
autovacuum_vacuum_scale_factor | 0.2 | | default | sighup | 0 | 100
autovacuum_vacuum_threshold | 50 | | default | sighup | 0 | 2147483647
autovacuum_work_mem | -1 | kB | default | sighup | -1 | 2147483647
log_autovacuum_min_duration | -1 | ms | default | sighup | -1 | 2147483647
vacuum_cost_delay | 0 | ms | default | user | 0 | 100
vacuum_cost_limit | 200 | | default | user | 1 | 10000
vacuum_cost_page_dirty | 20 | | default | user | 0 | 10000
vacuum_cost_page_hit | 1 | | default | user | 0 | 10000
vacuum_cost_page_miss | 2 | | default | user | 0 | 10000
vacuum_defer_cleanup_age | 0 | | default | sighup | 0 | 1000000
vacuum_failsafe_age | 1600000000 | | default | user | 0 | 2100000000
vacuum_freeze_min_age | 50000000 | | default | user | 0 | 1000000000
vacuum_freeze_table_age | 150000000 | | default | user | 0 | 2000000000
vacuum_multixact_failsafe_age | 1600000000 | | default | user | 0 | 2100000000
vacuum_multixact_freeze_min_age | 5000000 | | default | user | 0 | 1000000000
vacuum_multixact_freeze_table_age | 150000000 | | default | user | 0 | 2000000000
(27 rows)
maintenance_work_mem | 65536 | kB | default | user | 1024 | 2147483647
Oltre ai parametri di vacuum e' riportata la maintenance_work_mem perche', quando autovacuum_work_mem e' impostato a -1, viene utilizzato tale valore.
La configurazione di default di Postgres, come e' noto, e' molto conservativa
e questo vale anche per il VACUUM.
In particolare l'autovacuum e' configurato per dare un impatto minimo al sistema.
Ad esempio per default l'autovacuum si attiva solo quando una tabella viene modificata piu'
del 20% e per piu' di 50 righe. Per tabelle di grandi dimensioni un 20% di dead tuples potrebbe
corrispondere a parecchio spazio...
La prima indicazione, nel caso in cui la base dati sia modificata di frequente,
e' quella di rendere piu' frequenti ed aggressivi i job di AUTOVACUUM
con autovacuum_vacuum_scale_factor = 0.05 e
con autovacuum_vacuum_cost_limit = 2000.
Ancora piu' forte e' l'impostazione autovacuum_vacuum_cost_delay = 1
[NdA in particolare con le versioni precedenti alla 12 in cui il default era 20ms].
Poiche' il default di autovacuum_vacuum_cost_limit e' -1 il valore
utilizzato e' il vacuum_cost_limit che ha context user ed e' quindi piu' veloce da modificare
se non si puo' riavviare l'istanza.
Se il numero di tabelle e' molto elevato
e' possibile aumentare il numero di job di vacuum che vengono eseguiti in parallelo
agendo sul parametro autovacuum_max_workers; ma bisogna tenere conto che va aumentato anche
autovacuum_vacuum_cost_limit perche' il costo e' suddiviso tra tutti i job
[NdA viene applicato il principio sindacale: lavorare meno, lavorare tutti].
Dalla versione 13 sono disponibili ulteriori parametri di tuning dell'autovacuum come
autovacuum_vacuum_insert_scale_factor ed automatismi per evitare il
wraparound... pero' sono utili solo con database molto pesantemente utilizzati.
Nelle versioni precedenti, per ovviare a questi problemi,
si agiva lanciando semplici script di vacuum manuali,
come vedremo nel seguito.
Oltre che a livello di database e' possibile impostare i parametri di VACUUM
in modo specifico per le tabelle piu' significative. Ad esempio:
alter table mytable set (autovacuum_vacuum_cost_delay = 0);
Vi sono ulteriori impostazioni di storage definibili a livello di tabella che possono essere utilizzate
(eg. fillfactor) come descritto nella
documentazione ufficiale.
Per evitare che le sessioni appese blocchino il vacuum e' possibile agire su due parametri: statement_timeout e idle_in_transaction_session_timeout. Si tratta di impostazioni che si effettuano solo quando non e' possibile agire altrimenti sulle applicazioni/utenti, i due parametri sono disponibili da sempre [NdA dalla 7.3 e dalla 9.6 rispettivamente], ma hanno entrambe default a 0, quindi sono giustamente disabilitati; se necessario modificarli e' preferibile agire a livello di sessione o di utente anziche' di istanza.
Ora che abbiamo visto i principali elementi sui comandi ed i parametri di vacuum si puo' elaborare una strategia per i database in gestione. Qual'e' la tecnica migliore? Dipende...
Ci sono diverse casistiche che si possono presentare:
Oltre al VACUUM non vanno dimenticate le altre attivita' di gestione come i backup. PosgtreSQL fornisce una serie di strumenti per eseguire backup logici e fisici, per archiviare i WAL, per eseguire eventuali PITR... Ovviamente la strategia di vacuum deve tenere conto degli SLA del database, dei periodi di carico, delle modalita' di backup e di tutte le altre attivita' di gestione.
Come controllare se il database e' tranquillo? Lo sappiamo gia' fare: basta controllare che il rapporto delle dead tuples rispetto alle tuple attive non cresca nel tempo e vengano correttamente eseguiti gli autovacuum ed autoanalyze:
Il contenuto pg_stat_all_tables e' cumulativo e puo' essere resettato con la funzione pg_stat_reset() [NdA pg_stat_reset() ha un impatto prestazionale perche' fa dimenticare le precedenti esecuzioni del vacuum e quindi fa ripartire l'autovacuum su tutte le tabelle. Puo' avere senso inserirlo come attivita' di manutenzione periodica annuale o mensile seguita da un VACUUM ANALYZE dell'intero DB].
In realta' la query riportata riporta una visione parziale del problema perche' tratta solo le dead tuples ma non analizza il free space o bloat che potrebbe essere liberato con un VACUUM FULL. Per ottenere questo e' possibile utilizzare l'extension pgstattuple:
La pgstattuple e' molto precisa ma... anche piuttosto lenta su tabelle di grandi dimensioni
[NdA puo' aiutare in alcuni casi la funzione pgstattuple_approx(), aggiunta dalla 9.5 all'extension pgstattuple].
In alternativa si possono utilizzare query approssimate ma molto piu' veloci
per valutare gli spazi occupati e gli sprechi (bloat) come la
seguente
[NdA vedi anche sotto]:
SELECT current_database(), schemaname, tblname, bs*tblpages AS real_size,
(tblpages-est_tblpages)*bs AS extra_size,
CASE WHEN tblpages > 0 AND tblpages - est_tblpages > 0
THEN 100 * (tblpages - est_tblpages)/tblpages::float
ELSE 0
END AS extra_pct, fillfactor,
CASE WHEN tblpages - est_tblpages_ff > 0
THEN (tblpages-est_tblpages_ff)*bs
ELSE 0
END AS bloat_size,
CASE WHEN tblpages > 0 AND tblpages - est_tblpages_ff > 0
THEN 100 * (tblpages - est_tblpages_ff)/tblpages::float
ELSE 0
END AS bloat_pct, is_na
FROM (
SELECT ceil( reltuples / ( (bs-page_hdr)/tpl_size ) ) + ceil( toasttuples / 4 ) AS est_tblpages,
ceil( reltuples / ( (bs-page_hdr)*fillfactor/(tpl_size*100) ) ) + ceil( toasttuples / 4 ) AS est_tblpages_ff,
tblpages, fillfactor, bs, tblid, schemaname, tblname, heappages, toastpages, is_na
FROM (
SELECT
( 4 + tpl_hdr_size + tpl_data_size + (2*ma)
- CASE WHEN tpl_hdr_size%ma = 0 THEN ma ELSE tpl_hdr_size%ma END
- CASE WHEN ceil(tpl_data_size)::int%ma = 0 THEN ma ELSE ceil(tpl_data_size)::int%ma END
) AS tpl_size, bs - page_hdr AS size_per_block, (heappages + toastpages) AS tblpages, heappages,
toastpages, reltuples, toasttuples, bs, page_hdr, tblid, schemaname, tblname, fillfactor, is_na
FROM (
SELECT
tbl.oid AS tblid, ns.nspname AS schemaname, tbl.relname AS tblname, tbl.reltuples,
tbl.relpages AS heappages, coalesce(toast.relpages, 0) AS toastpages,
coalesce(toast.reltuples, 0) AS toasttuples,
coalesce(substring(
array_to_string(tbl.reloptions, ' ')
FROM 'fillfactor=([0-9]+)')::smallint, 100) AS fillfactor,
current_setting('block_size')::numeric AS bs,
CASE WHEN version()~'mingw32' OR version()~'64-bit|x86_64|ppc64|ia64|amd64' THEN 8 ELSE 4 END AS ma,
24 AS page_hdr,
23 + CASE WHEN MAX(coalesce(s.null_frac,0)) > 0 THEN ( 7 + count(s.attname) ) / 8 ELSE 0::int END
+ CASE WHEN bool_or(att.attname = 'oid' and att.attnum < 0) THEN 4 ELSE 0 END AS tpl_hdr_size,
sum( (1-coalesce(s.null_frac, 0)) * coalesce(s.avg_width, 0) ) AS tpl_data_size,
bool_or(att.atttypid = 'pg_catalog.name'::regtype)
OR sum(CASE WHEN att.attnum > 0 THEN 1 ELSE 0 END) <> count(s.attname) AS is_na
FROM pg_attribute AS att
JOIN pg_class AS tbl ON att.attrelid = tbl.oid
JOIN pg_namespace AS ns ON ns.oid = tbl.relnamespace
LEFT JOIN pg_stats AS s ON s.schemaname=ns.nspname
AND s.tablename = tbl.relname AND s.inherited=false AND s.attname=att.attname
LEFT JOIN pg_class AS toast ON tbl.reltoastrelid = toast.oid
WHERE NOT att.attisdropped
AND tbl.relkind in ('r','m')
GROUP BY 1,2,3,4,5,6,7,8,9,10
ORDER BY 2,3
) AS s
) AS s2
) AS s3
ORDER BY real_size desc, schemaname, tblname;
Se il tuning dell'autovacuum non e' sufficiente una soluzione molto semplice
e' quella di creare e lanciare uno script con il vacuum manuale delle hot tables.
Come si prepara uno script di vacuum?
Naturalmente l'esempio riportato va personalizzato a seconda delle esigenze
ma copre gia' molti casi:
crea uno script di vacuum analyze per le tabelle che hanno piu' di 1.000 dead tuples se
queste superano il 5% della dimensione della tabella ed a queste aggiunge
il trattamento delle 10 tabelle piu' grandi.
La frequenza di lancio dello script dipende dal tipo di carico e dalla durata del vacuumm.
Se si opta per un vacuum dell'intero DB, che presenta anche altri vantaggi,
una tipica schedulazione e' quella settimanale:
# min hour wday month mday command-to-run 17 02 6 * * psql -c "vacuum verbose analyze" mydb > /tmp/vacuum_mydb.log
Alcuni consigliano riavvii periodici del database... ma questo non mi trova d'accordo su PostgreSQL. Certamente un riavvio periodico della base dati interrompe le sessioni di lunga durata ed annulla eventuali memory leak. Ma PostgreSQL e' un database molto robusto ed un riavvio periodico non e' necessario e neanche utile per il vacuum. Piuttosto nella politica di gestione e' opportuno prevedere il controllo delle sessioni Idle in transaction, delle prepared transactions e l'aggiornamento delle minor releases [NdA che sono quattro ogni anno].
I lettori piu' attenti avranno notato che le situazioni elencate non riportano tutti i casi possibili: se il carico e' sempre elevato, se le hot tables sono centinaia o migliaia, se l'autovacuum non riesce a tenere il passo delle cancellazioni o delle transazioni, ... insomma se il gioco si fa duro bisogna rispondere in modo adeguato: i controlli, le modifiche sulle applicazioni, il tuning dei parametri, la configurazione di storage delle tabelle, il lancio di script vanno preparati ed eseguiti con tutta la cura necessaria utilizzando ulteriori parametri rispetto a quelli indicati.
In un paragrafo precedente abbiamo citato la legge di Pareto... ma se siete fortunati quanto lo sono io, dovrete intevenire anche quando e' entrata in gioco la famosa legge di Murphy!
Se abbiamo impostato una corretta strategia di VACUUM per i nostri database teoricamente non avremo mai problemi... ma se dobbiamo intervenire velocemente su una base dati con grossi problemi qualche consiglio tattico puo' essere utile!
La prima avvertenza e' quella di diagnosticare in modo tempestivo
i problemi di insufficienza di vacuum. Piu' il tempo passa piu' la base dati rallenta
e piu' lunga e pesante sara' la cura necessaria.
Le query gia' viste nei paragrafi precedenti possono essere utilizzate
per una rapida diagnosi...
Se liberare lo spazio sul file system e' un'esigenza importante
sara' necessario eseguire un VACUUM FULL.
Tuttavia va tenuto conto che il VACUUM FULL richiede un lock esclusivo
e quindi non puo' essere eseguito durante la normale attivita' applicativa.
La modalita' FULL inoltre richiede spazio durante la sua esecuzione
perche' ogni tabella viene riscritta su nuovi file prima di cancellare
i vecchi file.
Percio' spesso si esegue un VACUUM FULL dopo avere eseguito un VACUUM normale
e, se lo spazio a disposizione e' limitato, si opera una tabella alla volta
verificando di avere sempre disponibile lo spazio per ricreare la tabella corrente.
Per massimizzare i vantaggi e ridurre i tempi di esecuzione spesso si esegue
il VACUUM FULL ordinando in modo decrescente le tabelle per quantita' di spazio
liberabile. Quando finisce il tempo a disposizione della finestra di manutenzione
sara' stato liberato il massimo dello spazio anche se il FULL non e' stato
eseguito sull'intero database.
Abbiamo visto i casi piu' importanti da gestire con il VACUUM ma ci sono ancora alcuni casi particolari da vedere... continuate a leggere!
In generale l'esecuzione del vacuum non ha particolari differenze sulle architetture che utilizzano la replica Postgres: il vacuum viene eseguito sul primary e la sua applicazione viene replicata sui secondary.
In realta' c'e' qualche impatto del vacuum sulla replica, si tratta di casi particolari ma abbastanza frequenti.
Quando su un Secondary l’applicazione di un WAL va in conflitto con una query quest'ultima viene abortita.
Se sul Primary il vacuum cancella fisicamente una riga perche' sul Primary non vi sono query che lo impediscano,
la cancellazione viene replicata sul Secondary
senza tenere conto di eventuali query attive appunto sul Secondary.
E' un comportamento voluto perche' la precedenza viene data alla sicurezza dei dati e non alle query eseguite sullo standby.
Tuttavia questo potrebbe essere fastidioso quando il secondario viene utilizzato per query in Hot Standby
o per eseguire i backup liberando il primary da questo carico.
A seconda delle configurazioni e delle versioni e' possibile
evitare il problema eseguendo un tuning specifico su differenti parametri:
max_standby_streaming_delay, hot_standby_feedback, ...
oppure interrompendo l'applicazione dei WAL con pg_wal_replay_pause().
Una gestione piu' completa, consigliabile solo a partire dalla versione 13, si effettua con i replication slot.
Non ha impatto sul vacuum ma
se si utilizzano i replication slot e' consigliabile impostare il parametro max_slot_wal_keep_size
per evitare che il blocco della replica su un secondario non faccia bloccare il primario
per mancanza di spazio.
Quando si utilizzano le repliche vanno eseguiti ulteriori controlli sugli XID delle transazioni oltre a quello gia' visto sulla tabella pg_stat_activity:
Maggiori dettagli sul tuning della replica sono riportati in questa pagina.
Abbiamo visto diversi casi particolari da gestire con il vacuum ma c'e' ancora un problema gravissimo da analizzare... continuate a leggere!
L'orizzonte degli eventi in Postgres e' posto a 2 miliardi di transazioni in avanti ed indietro nel tempo.
Ogni riga in Postgres ha un'indicazione della transazione che ha eseguito l'INSERT
e della transazione che l'ha, eventualmente, cancellata.
Gli identificativi delle transazioni in Postgres sono numeri senza segno a 32 bit.
La transazione corrente
[NdA txid_current()] e' il presente, le transazioni con un valore piu' alto (fino a +2*2^30) sono il futuro
e quelle con un valore piu' basso sono il passato (fino a -2*2^30).
Questo non limita la vita di un database Postgres ad un numero massimo di transazioni
perche' l'orizzonte e' mobile e quindi c'e' sempre un futuro!
Naturalmente non e' possibile vedere righe che verranno inserite nel futuro
o righe che sono gia' state cancellate.
La verifica e' molto semplice inserendo e cancellando i dati su una tabella
con sessioni e transazioni diverse ed quindi eseguendo questa query:
SELECT ctid, xmin, xmax, * FROM mytable;
Come abbiamo gia' detto non ci sono problemi sul numero di transazioni, quando si raggiunge il limite massimo semplicemente si riparte ed e' sempre presente un futuro ed un passato... Via via che il valore della transazione attuale aumenta il futuro avanza ed il passato ci rincorre: l'aritmetica sul TXID viene eseguita in modo circolare in wraparound. Il problema pero' e' sulle righe con xmin iniziale basso che col tempo potrebbero diventare sempre piu' vecchie, piu' vecchie, ... fino a passare nel futuro e quindi non essere piu' visibili! Questo e' il problema del Transaction ID Wraparound.
Prima che questo avvenga bisogna impostare un ID di transazione particolare (freeze o 10 in binario) nel campo t_xmin che serve ad indicare che la riga e' nel passato qualunque sia l'ID della transazione corrente. Questa impostazione e' uno dei compiti del vacuum.
A differenza del bloat di una tabella il problema del wraparound dell'ID delle transazioni e' un problema molto piu' grave perche' per evitare la perdita dei dati Postgres si blocca. In realta' quando Postgres si avvicina a tale limite prima si lamenta parecchio nel log, poi con l'autovacuum fa di tutto per risolverlo, ... ma se anche in questo modo non si risolve il problema alla fine Postgres si blocca non consentendo piu' transazioni che potrebbero portare a perdita di dati. A questo punto il DBA deve eseguire una procedura precisa che effettua il freeze delle righe e ripristina il funzionamento della base dati.
Naturalmente questo puo' succedere solo su una base dati con un elevato carico transazionale e su cui le normali attivita' di VACUUM non vengono eseguite, automaticamente o meno, in modo adeguato.
Come si controlla se ci si sta avvicinando troppo al Transaction ID Wraparound?
Se ci si avvicina troppo al limite si puo' eseguire un vacuum manuale con: vacuumdb --all --freeze --jobs=2 --echo
Si rimanda alla documentazione ufficiale per tutti i dettagli.
I comandi DROP, TRUNCATE, CLUSTER, REINDEX ed anche il partitioning sono, in senso lato, potenziali alternative al VACUUM. Sono tutti da tenere in considerazione come possibilita' per liberare velocemente gli spazi allocati.
Un altro script per determinare il bloat delle tabelle e' questo!
Vi sono molte extensions che sono utili per analizzare in dettaglio l'utilizzo degli spazi occupati ed eventualmente liberabili dal vacuum. Oltre a pgstattuple, di cui abbiamo gia' visto un esempio, possiamo ricordare: pg_freespacemap, pg_visibility, pageinspect, pg_surgery, ...
Un hacking sul database?
postgres=# create extension pg_surgery;
CREATE EXTENSION
postgres=# select ctid, xmin, xmax, * from tab1;
ctid | xmin | xmax | c1
-------+---------+------+--------
(0,1) | 856 | 0 | ciao
(0,2) | 1818150 | 0 | Riciao
postgres=# select heap_force_freeze('tab1'::regclass, ARRAY['(0, 1)']::tid[]);
postgres=# select ctid, xmin, xmax, * from tab1;
ctid | xmin | xmax | c1
-------+---------+------+--------
(0,1) | 2 | 0 | ciao
(0,2) | 1818150 | 0 | Riciao
Dove 2 e' il FrozenTransactionId... Non usate pg_surgery se non sapete esattamente cosa state facendo.
Ad ogni versione Postgres ha inserito nuove funzionalita' sul VACUUM.
Nella versione 8.1 e' stato inserito il processo di autovacuum;
nella 8.4 e' stata aggiunta la visibility map;
dalla 9.0 sono state vi sono state molteplici ottimizzazioni sul vacuum
[NdA in precedenza il VACUUM FULL aumentava il bloat negli indici anziche' diminuirlo];
il parallelismo del vacuum sulle heap e' stato aggiunto nella 9.5;
nella versione 12 sono stati cambiati gli algoritmi del vacuum
rendendolo piu' aggressivo in alcuni casi,
e' stato aumentato il valore di default di autovacuum_vacuum_cost_delay;
nella 13 il parallelismo viene utilizzato anche per il vacuum degli indici;
dalla 14 e' stato introdotto l'emergency mode (failsafe_age) e reso piu' aggressivo l'autovacuum;
ulteriori novita'
erano previste nella versione PG16, ma in realta' sono state procastinate,
per consentirne l'esecuzione del VACUUM anche da parte di utenti non superuser
o non proprietari con uno specifico role, ...
Su questa pagina sono riportati maggiori dettagli sulle versioni di Postgres.
Da ultimo la legge del vacuum:
Vacuuming rule: If it hurts, you’re not doing it often enough!
Titolo: PostgreSQL VACUUM
Livello: Avanzato
Data: 1 Aprile 2020
Versione: 1.0.3 -
31 Ottobre 2023
Autore:
mail [AT] meo.bogliolo.name