Monitoração de Comandos com PG_STAT_STATEMENTS

Na versão 8.4, foram acrescentados novos recursos de monitoramento de banco que podem ser bastante úteis para se identificar que consultas têm consumido mais tempo, retornam mais dados e quais são executadas com maior freqüência. A pg_stat_statements é uma biblioteca que monitora e coleta estas informações para o usuário.

Para monitorar o que acontece no sgbd, é necessário manter em memória uma rotina que realize essa atividade. Para colocar esta rotina em execução, deve ser alterado o arquivo de configuração, mais precisamente a variável “shared_preload_libraries”, e reiniciado o servidor. Acrescente no arquivo de configuração postgresql.conf a linha abaixo e reinicie o serviço do banco:

shared_preload_libraries = '$libdir/pg_stat_statements'

Para visualizar se a biblioteca realmente foi colocada na memória pode ser usado o comando show:

Show shared_preload_libraries

Execute algumas consultas para que o sgbd armazene valores monitorados. Serão guardados os códigos dos comandos, o número de vezes em que os mesmos foram executados e os tempos de execução.

O próximo passo é executar o script do arquivo “contrib/pg_stat_statements/pg_stat_statements.sql” que se encontra na pasta de contribs para criar uma visão que mostrará os dados monitorados chamada pg_stat_statements. Abaixo, coloco a consulta padrão aos dados de monitoramento e alguns exemplos adicionais de consultas por número de chamadas ao comando, pelo tempo total e pelo número de linhas retornado.

1 - Consulta padrão

select * from pg_stat_statements;

2 - Consultas ordenadas pelo número de chamadas

select * from pg_stat_statements order by calls desc;

3 - Consultas ordenadas pelo tempo total utilizado

select * from pg_stat_statements order by total_time desc;

4 - Consultas ordenadas pelo número de linhas retornado

select * from pg_stat_statements order by rows desc;

Caso a quantidade de dados retornados seja muito grande, utilize a função pg_stat_statements_reset para limpar os dados coletados:

1 – Limpando dados coletados

select pg_stat_statements_reset();