Monitoramento PostgreSQL

Sabemos que existem diferentes formas de monitorar as atividades no SGBD PostgreSQL. Recentemente, fui solicitado em uma consultoria de PostgreSQL, para uma aplicação escrita em java utilizando hibernate (com muuuiiiittttooooos HQLs), foi solicitado que verificasse os possíveis pontos de gargalo no servidor de banco de dados. Afinal, “o problema sempre (ou quase) é no banco de dados” (Pensamento popular quando o banco de dados “aparentemente” está lento).

Vamos lá!

Verifiquei os parâmetros de configuração do postgresql.conf em conjunto com os parâmetros de configuração do Sistema Operacional (Redhat 7). "Excelentes” DBAs já haviam feito um “excelente” trabalho de tuning e comprovei que os parâmetros de configuração estavam ajustados para a melhor performance de utilização do SGBD, pelo menos para aquela realidade.

Procedimentos

Investigando mais a fundo o motivo, identificamos que algumas transações, oriundas da aplicação, estavam dentro de transações que aguardavam muito tempo por uma ação do usuário, isso quando não perdiam a sessão e a aplicação simplesmente não fechava a conexão. Com isso, foi inevitável o aumento de requisições para novas conexões ativas no SGBD. Bingo! Problema na aplicação que foi mal escrita. Os procedimentos a seguir foram realizados em um servidor Postgres 9.3.10, em Julho/2016, em dois dias de monitoramento.É claro que existem outras formas de realizar esse monitoramento!!! Ao consultar a view pg_stat_activity, observei que haviam muitas requisições cujo estado era idle_in_transaction. O que precisava ser feito? Capturar essas queries e reportar para que a equipe de desenvolvimento pudesse agir pontualmente e buscar uma solução definitiva para o problema.

Consulta utilizada para buscar atividades no SGBD com o estado idle_in_transaction:

SELECT pid, datname, usename, client_addr, query_start, state_change, state, query FROM pg_stat_activity WHERE state = 'idle_in_transaction';

Criação de estrutura no banco de dados para coleta dos dados:

-bash-4.1$ psql Password: psql.bin (9.2.4) Type "help" for help.

?1034hpostgres=# postgres=# postgres=# postgres=#     CREATE ROLE usr_monitoramento LOGIN ENCRYPTED PASSWORD 'md53fed9f02a9f95c16b62bf2f718ce74b0' VALID UNTIL 'infinity'; CREATE ROLE postgres=# postgres=# postgres=# postgres=#  CREATE DATABASE bd_monitoramento WITH ENCODING='UTF8' OWNER=usr_monitoramento CONNECTION LIMIT=-1; CREATE DATABASE postgres=# postgres=# postgres=# postgres=# CREATE TABLE atividades ( postgres=# pid integer, postgres=# datname name, postgres=# usename name, postgres=# client_addr inet, postgres=# query_start timestamp with time zone, postgres=# state_change timestamp with time zone, postgres=# state text, postgres=# query text postgres=#); CREATE TABLE postgres=# postgres=# postgres=# postgres=# ALTER TABLE atividades OWNER TO usr_monitoramento; ALTER TABLE postgres=# postgres=# postgres=# postgres=# CREATE TABLE bloqueio ( postgres=# hora_atual timestamp with time zone, postgres=# pid_bloqueado integer, postgres=# hora_inicio_bloqueada timestamp with time zone, postgres=# hora_mudanca_estado_bloqueada timestamp with time zone, postgres=# usuario_bloqueado name, postgres=# querie_bloqueada text, postgres=# aplicacao_bloqueada text, postgres=# pid_bloqueio integer, postgres=# hora_inicio_bloqueio timestamp with time zone, postgres=# hora_mudanca_estado_bloqueio timestamp with time zone, postgres=# usuario_bloqueio name, postgres=# querie_bloqueio text, postgres=# aplicacao_bloqueio text postgres=#); CREATE TABLE postgres=# postgres=# postgres=# postgres=# ALTER TABLE bloqueio OWNER TO usr_monitoramento; ALTER TABLE

Com a consulta a seguir, pude extrair as queries mais frequentes com estado idle_in_transaction:

SELECT pid, query, COUNT(0) qtde INTO atividades_agrupadas FROM atividades GROUP BY pid, query ORDER BY 3 DESC;

Consulta para capturar as possíveis requisições que estão bloqueando e que são bloqueadas:

SELECT now() hora_atual, atividade_bloqueada.pid pid_bloqueado, atividade_bloqueada.query_start hora_incio_bloqueada, atividade_bloqueada.state_change hora_mudanca_estado_bloqueada, atividade_bloqueada.usename usuario_bloqueado, atividade_bloqueada.query querie_bloqueada, atividade_bloqueada.application_name aplicacao_bloqueada, bloqueio.pid pid_bloqueio, atividade_bloqueio.query_start hora_incio_bloqueio, atividade_bloqueio.state_change hora_mudanca_estado_bloqueio, atividade_bloqueio.usename usuario_bloqueio, atividade_bloqueio.query querie_bloqueio, atividade_bloqueio.application_name aplicacao_bloqueio FROM pg_catalog.pg_locks bloqueados JOIN pg_catalog.pg_stat_activity atividade_bloqueada ON atividade_bloqueada.pid = bloqueados.pid JOIN pg_catalog.pg_locks bloqueio ON bloqueio.locktype = bloqueados.locktype AND bloqueio.DATABASE IS NOT DISTINCT FROM bloqueados.DATABASE AND bloqueio.relation IS NOT DISTINCT FROM bloqueados.relation AND bloqueio.page IS NOT DISTINCT FROM bloqueados.page AND bloqueio.tuple IS NOT DISTINCT FROM bloqueados.tuple AND bloqueio.virtualxid IS NOT DISTINCT FROM bloqueados.virtualxid AND bloqueio.transactionid IS NOT DISTINCT FROM bloqueados.transactionid AND bloqueio.classid IS NOT DISTINCT FROM bloqueados.classid AND bloqueio.objid IS NOT DISTINCT FROM bloqueados.objid AND bloqueio.objsubid IS NOT DISTINCT FROM bloqueados.objsubid AND bloqueio.pid != bloqueados.pid JOIN pg_catalog.pg_stat_activity atividade_bloqueio ON atividade_bloqueio.pid = bloqueio.pid WHERE NOT bloqueados.granted;

Criação do arquivo monitoramento.sh:

#!/bin/sh # Coleta de dados PG_STAT_ACTIVITY e BLOQUEIO # Data de criacao: 2016-07-01 # Criado por: Anderson Abreu # E-mail: andersonabreu@gmail.com
export PGPASSWORD="edb2013" INICIO="Inicio: " FIM="Final: " D=$(date '+%d/%m/%Y %R:%S') echo $INICIO$D >> /tmp/log_atividades.txt
/opt/PostgreSQL/9.2/bin/psql -U postgres -w -d bd_monitoramento -c "insert into atividades select pid, datname, usename, client_addr, query_start, state_change, state, query from pg_stat_activity where state = 'idle_in_transaction';" >> /tmp/log_atividades.txt
/opt/PostgreSQL/9.2/bin/psql -U postgres -w -d bd_monitoramento -c "insert into bloqueio SELECT now() hora_atual, atividade_bloqueada.pid pid_bloqueado, atividade_bloqueada.query_start hora_incio_bloqueada, atividade_bloqueada.state_change hora_mudanca_estado_bloqueada, atividade_bloqueada.usename usuario_bloqueado, atividade_bloqueada.query querie_bloqueada, atividade_bloqueada.application_name aplicacao_bloqueada, bloqueio.pid pid_bloqueio, atividade_bloqueio.query_start hora_incio_bloqueio, atividade_bloqueio.state_change hora_mudanca_estado_bloqueio, atividade_bloqueio.usename usuario_bloqueio, atividade_bloqueio.query querie_bloqueio, atividade_bloqueio.application_name aplicacao_bloqueio FROM pg_catalog.pg_locks bloqueados JOIN pg_catalog.pg_stat_activity atividade_bloqueada ON atividade_bloqueada.pid = bloqueados.pid JOIN pg_catalog.pg_locks bloqueio     ON bloqueio.locktype = bloqueados.locktype     AND bloqueio.DATABASE IS NOT DISTINCT FROM bloqueados.DATABASE     AND bloqueio.relation IS NOT DISTINCT FROM bloqueados.relation     AND bloqueio.page IS NOT DISTINCT FROM bloqueados.page     AND bloqueio.tuple IS NOT DISTINCT FROM bloqueados.tuple     AND bloqueio.virtualxid IS NOT DISTINCT FROM bloqueados.virtualxid     AND bloqueio.transactionid IS NOT DISTINCT FROM bloqueados.transactionid     AND bloqueio.classid IS NOT DISTINCT FROM bloqueados.classid     AND bloqueio.objid IS NOT DISTINCT FROM bloqueados.objid     AND bloqueio.objsubid IS NOT DISTINCT FROM bloqueados.objsubid     AND bloqueio.pid != bloqueados.pid JOIN pg_catalog.pg_stat_activity atividade_bloqueio ON atividade_bloqueio.pid = bloqueio.pid WHERE NOT bloqueados.granted;" >> /tmp/log_atividades.txt D=$(date '+%d/%m/%Y %R:%S') echo $FIM$D >> /tmp/log_atividades.txt

Agendamento da rotina:
Agendei a execução para ocorrer a cada minuto para termos o máximo de dados e suas possíveis repetições.

# crontab -e *   *    *    *   *   /tmp/monitoramento.sh

Report para equipe de desenvolvimento

Feita a coleta de dados, foi realizada a exportação dos dados para um arquivo no formato CSV para que fosse possível a análise da equipe de desenvolvimento.

copy atividades_agrupadas to '/tmp/atividades_agrupadas_idle_in_transaction.csv' DELIMITER ';' CSV HEADER;
copy bloqueio to '/tmp/bloqueio.csv' DELIMITER ';' CSV HEADER;

Anderson Abreu