SQL4DBA - SQL per DBA PostgreSQL

Il DBA (DataBase Administrator) utilizza statement SQL particolari per estrarre le informazioni piu' interessanti sui DB che amministra.
Questa pagina raccoglie e descrive gli script SQL piu' utili ed interessanti per la gestione di un database PostgreSQL.

PostgreSQL e' il piu' completo RDBMS Open Source ed il suo SQL e' molto potente. PostgreSQL ha infatti caratteristiche Object Oriented molto complete, piu' complete di altri RDBMS sia Open Source che commerciali.

Il documento e' volutamente breve e pratico con esempi funzionanti di statement SQL.
Informazioni di dettaglio si possono trovare nella relativa documentazione.

Introduzione

PostgreSQL fornisce un SQL ricco di funzionalita' grazie anche al suo disegno Object Relational. All'interno di ogni database viene mantenuto un ricco Catalog che consente di controllare con query SQL gli oggetti presenti nella base dati (eg. pg_database, pg_class, pg_tables, ...) e l'andamento delle attivita' (eg. pg_locks, pg_settings, pg_statistic, pg_stats,...).
Il data dictionary e' molto ampio, anzi ne esistono praticamente due: quello Postgres storico e quello standard SQL: spesso le stesse informazioni si possono ricavare in piu' modi.

Gli statement SQL a disposizione di un DBA PostgreSQL sono quindi moltissimi... in questa pagina faremo una scelta su quelli piu' utili ed interessanti.

Sessioni

Una prima visione sull'utilizzo di una base dati e' quella delle connessioni presenti. PostgreSQL fornisce informazioni sulle connessioni al database anche dal sistema operativo. Un semplice ps -efa | grep postgres consente di riconoscere i processi di sistema, le connessioni presenti e le eventuali sessioni attive.
Maggiori informazioni si possono estrarre con una query SQL sulla base dati:

Le colonne a disposizione nella pg_stat_activity sono parecchie e possono essere tutte utilizzate a seconda dei casi. L'ordinamento per state e' comodo perche' cosi' le sessioni active sono le prime visualizzate!

Lock

Gli oggetti sulla base dati vengono protetti dall'utilizzo concorrente in PostgreSQL mediante la gestione dei lock. In qualche caso possono verificarsi blocchi dovuti a sessioni che non rilasciano i lock e non permettono la modifica dei dati ad altre sessioni. Ecco come individuare i lock attivi (i lock not granted sono in attesa):

Nota: prima di ammazzare qualcuno... e' importante essere certi di quello che si sta facendo!

La query seguente utilizza qualche join per ottenere le query bloccate e da chi:

SELECT blocked_locks.pid AS blocked_pid, blocked_activity.usename AS blocked_user, blocking_locks.pid AS blocking_pid, blocking_activity.usename AS blocking_user, blocked_activity.query AS blocked_statement, blocking_activity.query AS current_statement_in_blocking_process FROM pg_catalog.pg_locks blocked_locks JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid AND blocking_locks.pid != blocked_locks.pid JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid WHERE NOT blocked_locks.GRANTED;

Questa query consente di capire con certezza chi e' il colpevole e chi e' la vittima del lock.
Decidere poi se terminare o cancellare il colpevole o la vittima e' un'altra questione...

Utilizzo di spazio

La gestione dello spazio disco in Postgres si basa sul file system ospite. Ad ogni oggetto (eg. una tabella) corrisponde un file sul file system. Sono utilizzabili diverse organizzazioni dei dati come struttura interna per soddisfare i piu' specifici requisiti di memorizzazione. Tutti gli indici sono trattati come indici secondari: hanno quindi una struttura dedicata ciascuno (un file). I tipi di indice disponibili sono molteplici: B-tree, BRIN, Hash, GiST (Generalized Search Tree), GIN (Generalized Inverted Index), ... Nella pratica i file assegnati alle tabelle ed agli indici sono sempre piu' di uno ma sono dettagli di implementazione. Lo spazio viene liberato con le operazioni di VACUUM. Lo spazio puo' essere organizzato in tablespace, ma generalmente si utilizza il solo tablespace di default a meno di non utilizzare file system differenti.

Un server PostgreSQL ospita piu' database. Quindi un primo punto di vista e' controllare l'occupazione di ogni singolo database:

Analizzando invece gli oggetti contenuti nella base dati e' possibile ottenere i dati di dettaglio. Ecco i 20 oggetti di maggior dimensione:

select relname, case WHEN relkind='r' THEN 'Table' WHEN relkind='i' THEN 'Index' WHEN relkind='t' THEN 'TOAST Table' ELSE relkind||'' end tipo, rolname, to_char(relpages::INT8*8*1024,'999G999G999G999') as size, to_char(reltuples,'999G999G999G999') as tuples from pg_class, pg_roles where relowner=pg_roles.oid order by relpages desc, reltuples desc limit 20;

Poiche' le tabelle TOAST vengono create automaticamente per ospitare il contenuto delle colonne di maggiori dimensioni che non trovano posto nel blocco dati, puo' essere utile riconoscere qual'e' la tabella di cui fanno parte:

select relname from pg_class where reltoastrelid = ( select oid from pg_class where relname = 'pg_toast_69' ); select c.relname as toastname, case WHEN c.relkind='r' THEN 'Table' WHEN c.relkind='i' THEN 'Index' WHEN c.relkind='t' THEN 'TOAST Table' ELSE c.relkind||'' end tipo, b.relname, r.rolname, to_char(c.relpages::INT8*8*1024,'999G999G999G999') as size, to_char(c.reltuples,'999G999G999G999') as tuples from pg_class c, pg_roles r, pg_class b where c.relowner=r.oid and b.reltoastrelid = c.oid order by c.relpages desc, c.reltuples desc limit 20;

In Postgres la gestione della concorrenza e del versionamento dei dati utilizza l'MVCC (Multi-Version Concurrency Control) ed ha alcune caratteristiche specifiche. Le DELETE e le UPDATE non cancellano mai le tuple ma le segnano come dead. Quando il numero di dead tuples e' elevato oppure quando sono presenti ampi bloat (spazi vuoti nei blocchi) e' opportuno svolgere un VACUUM manuale (o un VACUUM FULL che libera piu' spazio ma utilizza un lock).
Il vacuum viene eseguito in automatico dal processo chiamato autovacuum. I parametri di default dell'autovacuum pero' non sono molto aggressivi... se una basi dati molto utilizzata tende a crescere di dimensione e' consigliabile effettuare un tuning specifico. La query seguente effettua un semplice controllo sulle tabelle:

Performance

L'ottimizzazione delle performance e' il leitmotiv di ogni DBA. I punti di vista sono moltissimi: individuare gli statement SQL piu' pesanti, ottimizzare i piani di esecuzione degli statement, determinare eventuali bottleneck, effettuare il tuning sui parametri di configurazione, ...
Ecco qualche query di esempio:

select datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, round(100 * blks_hit / (blks_hit + blks_read), 3) as hit_ratio from pg_stat_database where datname not like 'template%'; select query, calls, total_exec_time, rows, pg_get_userbyid(userid) as user, 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 20; select schemaname,relname,heap_blks_read, heap_blks_hit*100/nullif(heap_blks_read+heap_blks_hit,0) as tb_hit_ratio, idx_blks_hit*100/nullif(idx_blks_read+idx_blks_hit,0) as idx_hit_ratio from pg_statio_user_tables where heap_blks_read>0 order by heap_blks_read desc limit 20; select name, setting, min_val, max_val, short_desc from pg_settings order by name;

La prima query permette di capire il carico presente su ogni database dell'istanza riportando statistiche globali.

La seconda query utilizza l'estensione pg_stat_statements e consente di valutare l'Hit Ratio della cache da parte degli statement SQL. La vista pg_stat_statements e' una delle piu' utili per monitorare le prestazioni di PostgreSQL e' quindi fortemente consigliato installare la relativa EXTENSION.

La terza query determina le tabelle maggiormente accedute e ne valuta l'Hit Ratio sulla Cache. Viene indicato in modo distinto l'Hit Ratio della tabella e degli indici, per questi ultimi l'Hit Ratio dovrebbe essere sempre superiore al 99%.

Postgres ha un numero elevato di parametri di configurazione che possono essere modificati per effettuare il tuning della prestazioni. L'ultima query riporta i parametri presenti e la loro descrizione.

Replication

La replica dei dati con Postgres e' molto efficiente ed affidabile: per questo e' sfruttata in moltissimi database di produzione.
Per controllare lo stato delle repliche e' possibile utilizzare la seguente query sul Primary:

Viene restituita una riga per ogni processo walsender con le informazioni sullo stato del Secondary connesso.

Ulteriori informazioni

Un'introduzione a PostgreSQL si trova in PostgreSQL RDBMS. Maggiori dettagli tecnici sulle diverse versioni di PostgreSQL e le date di rilascio di ogni versione sono riportate in questo documento.

DBA SQL Scripts

SQL per DBA PostgreSQL