pg_stat_statements

Una potente estensione di PostgreSQL e' pg_stat_statements che consente di individuare gli statement SQL piu' significativi eseguiti sulla base dati.
Il dettaglio delle informazioni ottenute e' notevole ed e' quindi molto opportuno configurare pg_stat_statements sugli tutti gli ambienti che abbiano un uso significativo di PostgreSQL.

A mio avviso l'estensione pg_stat_statements andrebbe sempre configurata in ogni ambiente PostgreSQL di produzione... quidi continuate a leggere!

Introduzione

Con Postgres per controllare lo stato delle connessioni presenti e delle query in corso si utilizza la vista pg_stat_activity, ad esempio:

select pid, datname, usename, client_addr, wait_event is not null as waiting, wait_event, wait_event_type, state, query from pg_stat_activity order by state, pid;

Questo pero' riporta solo lo stato corrente e non e' presente in PostgreSQL nessuna vista con la storia delle esecuzioni precedenti.
Per colmare questa lacuna e' stata introdotto un potente modulo di PostgreSQL: l'estensione pg_stat_statements che consente di monitorare gli statement SQL piu' significativi eseguiti sulla base dati.

Configurando questa estensione vengono mantenuti per ogni statement una serie di contatori statistici: quante volte e' stato eseguito, la durata minima/media/massima, la durata totale, il numero di righe restituite, la quantita' di WAL scritti, ... e naturalmente il testo della query stessa; insomma tutte le informazioni necessarie per analizzare singolarmente ogni comando SQL eseguito.

Configurazione

pg_stat_statements generalmente non richiede alcuna installazione perche' fa parte delle core extensions. Ha una semplice configurazione e, naturalmente, va creata l'extension in ogni database in cui deve essere utilizzata. Vediamo i dettagli...

La configurazione deve essere effettuata nel file postgresql.conf e richiede un riavvio (poiche' deve caricare una shared library). In particolare essere aggiunto il parametro pg_stat_statements all'impostazione shared_preload_libraries in postgresql.conf. E' possibile specificare altri parametri (eg. pg_stat_statements.max) anche se i valori di default sono generalmente adatti per una normale installazione.
Ecco un esempio di configurazione completo:

shared_preload_libraries = 'pg_stat_statements' # (change requires restart)

pg_stat_statements.max = 10000
pg_stat_statements.track = all
pg_stat_statements.save = on

Effettuato il riavvio della base dati i dati sugli statement eseguiti vengono immediatamente collezionati. Ma per poter visualizzare i dati raccolti e' necessario creare le viste di sistema con il comando:
create extension pg_stat_statements;
Naturalmente l'estensione va creata su tutti i database da cui si vogliono interrogare le statistiche.

Riassumendo: l'impostazione della shared_preload_libraries si esegue una sola volta per tutta l'istanza e richiede un riavvio; per poter leggere i dati sugli statement catturati deve invece essere creata l'extension su ogni database interessato.

Utilizzo

Una volta attivata l'estensione pg_stat_statements vengono mantenuti per ogni statement SQL una serie di contatori statistici: quante volte e' stato eseguito, la durata minima/media/massima, la durata totale, il numero di righe restituite, la quantita' di WAL scritti, ...
Ogni statement SQL eseguito viene normalizzato, vengono considerate variabili tutti i valori e non viene tenuto conto dell'execution plan: se due statement sono identici dal punto di vista sintattico, a meno delle variabili, verranno raccolti in una sola riga statistica. Al testo della query normalizzato viene associato un queryid univoco [NdA potenzialmente soggetto a collisioni ma e' caso quasi impossibile].

Le statistiche raccolte dal modulo vengono riportate nella system view pg_stat_statements, nello schema public, che puo' essere normalmente interrogata con una select SQL. Ad esempio per ottenere le 10 query di maggior durata:

select query, calls, total_exec_time, rows from pg_stat_statements order by total_exec_time desc limit 10;

Il comando per azzerare i dati raccolti, eseguibile solo da un superuser, e': SELECT pg_stat_statements_reset();

Il reset della pg_stat_statements non ha impatti prestazionali e puo' essere effettuato alla bisogna (eg. prima di effettuare il lancio di un batch applicativo di cui si vuole studiare il comportamento).
L'utilizzo o meno di pg_stat_statements non ha impatti sull'ottimizzatore, sui processi di autovacuum, ... vi e' un minimo overhead per la registrazione dello statement in memoria ed un eventuale reset e' immediato [NdA a differenza dello pg_stat_reset() che ha un impatto sul vacuum e che quindi non va mai utilizzato se non in modo controllato].

Nel tempo sono state aggiunte sempre piu' informazioni nella vista pg_stat_statements; ecco una selezione piu' completa degli statement nella TOP10 del database:

select pg_get_userbyid(userid) as user, query, calls, rows, total_exec_time/1000 AS total_exec_time_sec, total_exec_time/calls/1000 AS avg_exec_time_sec, max_exec_time/1000 AS max_exec_time_sec, wal_bytes/(1024*1024) AS wal_mbytes, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent from pg_stat_statements order by total_exec_time desc limit 10;

Parametri

Vediamo i principali parametri utilizzabili con pg_stat_statements:

Parametro	Datatype	Context	Descrizione
shared_preload_libraries	string	postmaster	Indica le librerie caricate all'avvio dell'istanza. E' una lista separata con virgola con i nomi delle librerie da includere. La lista deve contenere pg_stat_statements.
pg_stat_statements.max	integer	postmaster	Numero massimo di statements tracciati. Il default 5000 puo' essere modificato solo con un riavvio.
pg_stat_statements.save	boolean	sighup	Se true indica che le statistiche vanno mantenute al reboot.
pg_stat_statements.track	enum	superuser	Con top vengono tracciati gli statement richiesti dai client, con `all` vengono tracciati tutti gli statement, compresi quelli richiamati all'interno di function e stored procedure. La colonna toplevel della vista consente di distinguere i comandi top da quelli nested.
pg_stat_statements.track_planning	boolean	superuser	Se false non vengono raccolte le statistiche di planning. Generalmente non si abilita perche' e' molto oneroso. Questo parametro e' disponibile da PG13.
pg_stat_statements.track_utility	boolean	superuser	Se true indica che debbono essere tracciati tutti i comandi SQL; SELECT, INSERT, UPDATE, DELETE e MERGE sono sempre tracciati.

Come tutte le extension anche pg_stat_statements ha un costo dal punto di vista delle performance e dell'occupazione di memoria.
Tuttavia con le impostazioni di default l'overhead di pg_stat_statements e' trascurabile mentre le informazioni ottenibili sono spesso fondamentali per diagnosticare problemi e migliorare le performances della base dati.

Nella maggior parte dei casi si utilizzano i parametri di default.
Per istanze che utilizzano un grande numero di statement SQL differenti si puo' aumentare il parametro pg_stat_statements.max;
Se e' attivo il parametro track_io_timing le statistiche raccolte riportano anche i tempi di accesso all'I/O (il default di track_io_timing e' false anche perche' ha un costo prestazionale significativo).

Le statistiche pg_stat_statements persistono al riavvio del DB (se e' impostato il pg_stat_statements.save=true come per default). Per garantire la persistenza le statistiche vengono salvate su file nella data directory di PostgreSQL in /pg_stat [NdA storicamente era utilizzata la /global].

Variazione parametri online

La configurazione iniziale di pg_stat_statements richiede il riavvio del DB perche' deve allocare la memoria necessaria caricando shared_preload_libraries, ma altri parametri possono essere modificati dinamicamente. Ad esempio puo' risultare molto utile tracciare gli statement SQL lanciati all'interno di funzioni e stored procedure. E' possibile modificare l'impostazione senza riavviare la base dati:

ALTER SYSTEM SET pg_stat_statements.track = 'all'; select pg_reload_conf(); SHOW pg_stat_statements.track;

L'altra tipica operativa online e' quella del reset delle statistiche che, come abbiamo gia' visto, si effettua con la query:
SELECT pg_stat_statements_reset();

Storia

L'estensione pg_stat_statements e' stata introdotta con la 8.4 (2009) ed e' sempre stata aggiornata ed estesa ad ogni nuova release di PostgreSQL.

Alcuni degli aggiornamenti piu' significativi:

introduzione di pg_stat_statements dalla versione 8.4 [NdA in realta' alcuni commit precedenti avevano preparato questo rilascio]
dalla versione 9.1 e' disponibile come extension
nella versione 9.5 sono disponibili ulteriori dati statistici
dalla versione 11 il queryid e' stato allargato a 64bit per ridurre le collisioni
dalla versione 12 la funzione di reset puo' essere selettiva (userid, dbid, queryid) e puo' essere concessa in GRANT agli utenti non superuser
dalla versione 13 sono disponibili anche i tempi di plan [NdA attenzione: sono state aggiunte nuove colonne ed altre sono state rinominate (eg. al posto di total_time va utilizzato total_exec_time)]
nella versione 14 e' stata aggiunta la vista pg_stat_statements_info con ulteriori informazioni come stats_reset [NdA finalmente!!!], aggiunta la colonna toplevel per distinguere i nested statements; nella pg_stat_activity e' stato aggiunto il query_id consentendo il join tra le due viste
nella versione 15 sono state aggiunte nuove colonne relative al JIT ed ai temporary
dalla versione 16 vengono normalizzati anche gli statement di utility
nella versione 17 sono disponibili le ulteriori colonne: jit_deform_count, jit_deform_time, local_blk_read_time, local_blk_write_time, stats_since, minmax_stats_since; inoltre vengono trattati come costanti: savepoint names, 2PC GIDs, names of DEALLOCATE, CALL parameters
nella versione 18 verranno trattati come costanti: values of SET, ...

Maggiori dettagli si trovano nella utilissima pgPedia.

Varie ed eventuali

La vista pg_stat_statements e' creata nello schema public quando viene creata l'extension. Gli utenti normali possono vedere i dettagli solo delle proprie query; i superuser e gli utenti con il privilegio pg_read_all_stats possono vedere le query ed queryid di tutti gli statement.

Come sempre la documentazione ufficiale PostgreSQL riporta tutti i dettagli... come l'elenco delle quasi 50 colonne disponibili nella vista.

Titolo: pg_stat_statements
Livello: Intermedio (2/5)
Data: 14 Febbraio 2023 ❤️
Versione: 1.0.2 - 31 Ottobre 2024 🎃
Autore: mail [AT] meo.bogliolo.name