PostgreSQL 9.2: Miglioramenti in pg_stat_statements

pg_stat_statements è un’estensione di PostgreSQL che analizza le istruzioni eseguite da un database e ne raccoglie statistiche. È distribuito come modulo opzionale (contrib).

A differenza di strumenti di analisi del log di Postgres come il popolare pgFouine, questa estensione svolge il proprio lavoro direttamente all’interno del database.

A partire da PostgreSQL 9.2, è stata aggiunta la normalizzazione delle query SQL. Eventuali valori costanti e stringhe all’interno di una query vengono identificati e assimilati a parametri, al fine di determinare l’impronta della query e aggregare le statistiche di utilizzo.

Per vedere all’opera questa novità, è necessario attivare il modulo. Modificare il file di configurazione di Postgres aggiungendo la riga:

shared_preload_libraries = 'pg_stat_statements'

Questo parametro di configurazione necessita del riavvio del server Postgres.

A riavvio avvenuto, collegarsi ad un database utilizzando psql e creare l’estensione pg_stat_statements. Questa operazione necessita dei privilegi di superutente:

db=# CREATE extension pg_stat_statements;

A questo punto creiamo una tabella contenente dati generati casualmente. La utilizzeremo come fonte per raccogliere statistiche.
Eseguire tramite psql il seguente codice SQL:

CREATE TABLE test AS
    SELECT
        x AS id,
        md5(random()::text) AS p,
        (SELECT count(*) FROM pg_statistic) s
    FROM generate_series(1,10000000) x;

Le statistiche vengono raccolte in una vista chiamata pg_stat_statements.

Tra le nuove metriche aggiunte a questa vista troviamo blk_read_time e blk_write_time.
Queste due colonne sono in grado di mostrare il tempo che PostgreSQL impiega in operazioni di lettura e scrittura su disco (in gergo I/O) per ogni istruzione eseguita.

La raccolta di questo tipo di informazioni ha tuttavia un costo per il server in termini di prestazioni.
Per questo motivo è stato introdotto un nuovo parametro di configurazione che serve ad attivare/disattivare il calcolo di I/O: track_io_timing.

Per attivarlo, eseguiamo con privilegi di superutente:

SET track_io_timing = TRUE;

A questo punto, eseguiamo la seguente query 3 volte, cambiando l’identificativo richiesto:

SELECT * FROM test WHERE id = 1000;
SELECT * FROM test WHERE id = 2000;
SELECT * FROM test WHERE id = 3000;

Consultando la vista pg_stat_statements, è possibile scoprire informazioni sull’utilizzo delle query appena eseguite:

db=# SELECT query, calls, total_time, blk_read_time, blk_write_time FROM pg_stat_statements WHERE query ~ 'SELECT \*';
-[ RECORD 1 ]--+---------------------------------
query          | SELECT * FROM test WHERE id = ?;
calls          | 3
total_time     | 2459.014
blk_read_time  | 313.811
blk_write_time | 0

È importante notare che PostgreSQL è stato in grado di identificare la stessa impronta per le 3 query:

SELECT * FROM test WHERE id = ?;

Il carattere ? indica la presenza di un parametro. Il campo calls conterrà in effetti il valore 3, corrispondente al numero di volte in cui la query è stata esguita.

Questo miglioramento nella raccolta di statistiche è un altro passo avanti incontro alle esigenze degli amministratori di database Postgres.