SQL4DBA - SQL per DBA PostgreSQL

Importante: Qui trovate la versione piu' recente di questa pagina.
Questa pagina e' stata mantenuta perche' fa riferimento statement utili con versioni precedenti di PostgreSQL anche se non piu' supportate.

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: a volte le stesse informazioni sul contenuto della base dati 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 le connessioni presenti. Maggiori informazioni si possono estrarre con una query SQL sulla base dati:

Nella versione 9.2 e' cambiata un poco la logica del contenuto di alcune colonne. Anziche' la query corrente (che risultava essere IDLE nella maggior parte dei casi) e' riportata nella tabella PG_STAT_ACTIVITY l'ultima query eseguita, mentre la colonna STATE riporta se lo statement e' ancora in esecuzione oppure IDLE. Ecco la selezione aggiornata alla versione 9.2:

Dalla versione 9.6 sono state aggiunte maggiori informazioni sullo stato di attesa. Poiche' la colonna waiting e' stata rimossa anche questo cambiamento non e' compatibile con la query precedente. Ecco la selezione aggiornata all'ultima versione di PostgreSQL:

Nella versione 10 e' stata aggiunta la colonna backend_type.
Le colonne a disposizione nell 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. Decidere poi se punire 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: B-tree, Hash, GiST (Generalized Search Tree), GIN (Generalized Inverted Index), ... In realta' i file sono sempre piu' di uno ma sono dettagli di implementazione. Lo spazio viene liberato con le operazioni di VACUUM (automatiche nelle versioni piu' recenti). Lo spazio puo' essere organizzato in tablespace, ma generalmente si utilizza il solo tablespace di default.

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 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;

La gestione della concorrenza e del versionamento dei dati utilizza l'MVCC (Multi-Version Concurrency Control) in Postgres 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).
Nelle versioni piu' recenti il vacuum viene eseguito in automatico: 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.
Attenzione che le colonne della pg_stat_statements vengono cambiate spesso... nelle versioni precedenti alla 13 al posto di total_exec_time va utilizzato total_time.

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. Il sito PostgreSQL contiene tutta la documentazione ufficiale.

DBA SQL Scripts

SQL per DBA PostgreSQL