PostgreSQL 9.2 introduce alcune nuove statistiche alla vista del catalogo pg_stat_database.

Le statistiche aggiunte sono effettivamente presenti anche nei log di Postgres, ma inserendole nel catalogo risultano molto più semplici da consultare.

Una di queste è il numero di deadlock rilevati per ogni database.

L’altra informazione statistica aggiunta riguarda la creazione di file temporanei. Postgres utilizza dei file temporanei su disco ogni volta che la memoria non è sufficiente per eseguire operazioni come ordinamenti o join. La dimensione massima di memoria utilizzabile da Postgres è specificata dal parametro di configurazione work_mem.

Le colonne aggiunte a pg_stat_database sono denominate temp_files e temp_bytes ed indicano rispettivamente il numero di file temporanei creati e la loro dimensione totale. Questi valori non sono istantanei ma vengono incrementati nel tempo.

Ecco un esempio di come consultare le nuove statistiche:

SELECT datname, deadlocks, temp_files, temp_bytes FROM pg_stat_database
    WHERE datname='nome_del_database';

E come appare il risultato:

       datname      | deadlocks | temp_files | temp_bytes
 -------------------+-----------+------------+------------
  nome_del_database |         3 |          2 |  280000000
 (1 row)

Inserendo queste statiatiche direttamente nel catalogo, è stata facilitata l’integrazione con strumenti di monitoraggio di sistema. Risulterà semplice, ad esempio, produrre grafici per tenere traccia dei deadlock e dei file temporanei.

Questa vista espone vari campi, si veda la documentazione ufficiale per maggiori dettagli sul significato di ognuno di essi.

Il campo oggetto del miglioramento è current_query. A partire da PostgreSQL 9.2, questo campo è stato sostituito da due campi distinti, uno chiamato state (che indica lo stato della query corrente), l’altro denominato query (che riporta la query vera e propria).

Per capire meglio come questo cambiamento si riflette nel monitoraggio delle attività del database, vediamo come questa vista si comporta in PostgreSQL 9.1 e come è stata modificata in PostgreSQL 9.2.

Ecco una serie di operazioni da eseguire assumendo che ci sia un server PostgreSQL 9.1 in esecuzione sulla porta 5491 ed un server 9.2 in esecuzione sulla porta 5492.

Collegatevi ad entrambi i server con psql. Avviate una transazione (senza chiuderla!) ed ottenete l’ID del processo (PID o pid):

BEGIN;
SELECT pg_backend_pid();

Nota: usare l’opzione -p per specificare la porta relativa al server a cui connettersi.

Possiamo pensare a queste transazioni aperte come ad applicazioni che stanno “lavorando” sul database. Il nostro scopo è monitorare ciò che stanno facendo.

Su PostgreSQL 9.1:

$ psql -p 5491 -tqc "SELECT current_query FROM pg_stat_activity WHERE procpid=13788"

Su PostgreSQL 9.2:

$ psql -p 5492 -tqc "SELECT query,state FROM pg_stat_activity WHERE pid=13465"

Nota: sostituire i PID nelle clausole WHERE con quelli ottenuti precedentemente con SELECT pg_backend_pid().

In PostgreSQL 9.1 il valore di current_query è usato per indicare anche le transazioni in stato idle (<IDLE>).

In PostgreSQL 9.2 il campo query contiene invece l’ultima query eseguita dal backend, mentre state contiene lo stato (molto più dettagliato che in precedenza, con ben 6 stati).

Importante: notare come la colonna procpid sia stata rinominata in un più standard pid.

Gli amminsitratori di database PostgreSQL saranno felicissimi di questa migliorìa, che agevola non poco il compito di monitoring dell’attività del database.