Melhore a performance do seu banco Postgres com o PGbadger

O seu banco Postgres está lento?
Você já alterou diversas configurações e mesmo assim não conseguiu identificar os problemas de performance?
Pois bem, há uma luz no fim do tunel!

Você já experimentou o PGbadger?

O que é o PGbadger

O pgBadger é um software livre, desenvolvido por Gilles Darold, e atua como um analisador de log do PostgreSQL robusto, com relatórios e gráficos analíticos sobre a sua instância de Postgres.

A documentação está disponível em https://pgbadger.darold.net/.

Através dele é possível analisar logs do postgres, desde logs minúsculos até logs na casa de Gigabytes, o que seria humanamente impossível.

Configuração do PostgreSQL

Apesar de se tratar de um analisador de log, nem todos os logs podem ser analisados. Para que o pgbadger funcione, é necessário alterar alguns parâmetros.

Os dois principais parâmetros são os seguintes: log_min_duration_statement e log_line_prefix. Este primeiro parâmetro determina o tempo mínimo, em milissegundos, para que uma query seja registrada no log. O valor padrão é -1, o que significa que nenhuma query será logada no log do Postgres.

Em ambientes críticos de performance, o ideal é que este valor seja igual ou próximo de 0, para que todas, ou o máximo possível de queries sejam logadas.

Esta alteração pode ser crítica no ambiente e gerar mais logs do que o esperado, podendo causar o enchimento da partição reservada para log.

Um valor interessante para configuração é de 5000, logando as queries com mais de 5 segundos, o que normalmente retorna uma massa razoável de dados sem estourar a partição de logs.

Recomendamos a monitoração manual do ambiente após a alteração, por pelo menos 1 hora, para evitar comportamentos indesejáveis. Caso a quantidade de logs gerados seja grande, é necessário aumentar este parâmetro para evitar um estouro de recursos. Caso contrário, não sejam logadas queries suficientes, é necessário diminuir este parâmetro.

Outro parâmetro fundamental é o log_line_prefix.

Ele determina qual será o prefixo do log, ou seja, como será o “cabeçalho” do log. O mais recomendado (padrão) é:

log_line_prefix = ‘%t [%p]: user=%u,db=%d,app=%a,client=%h ‘

Na documentação do github há também outros exemplos de configuração.

Os outros parâmetros não obrigatórios são:

log_checkpoints = on # útil para analisar o comportamento dos checkpoints;
log_connections = on # útil para analisar o comportamento das conexões;
log_disconnections = on # útil para analisar o comportamento dos conexões;
log_lock_waits = on # útil para analisar o comportamento dos locks;
log_temp_files = 0 # útil para identificar e analisar queries que usam arquivos temporários;
log_autovacuum_min_duration = 0 # útil para analisar o comportamento do autovacuum;
log_error_verbosity = default # Normalmente já é o padrão;
lc_messages=’C’ # Retira o suporte do log a uma localidade.

Estes parâmetros, a partir da versão 10 do postgres, podem ser alterados com um simples reload do serviço. Dependendo da sua versão do postgres, pode ser necessário um restart do serviço.

Instalação

A grande vantagem do pgbadger é que não é necessário realizar qualquer instalação no seu servidor de produção.
Como a fonte de dados é o log do postgres, é possível copiar os logs, por exemplo, para o seu computador pessoal, e realizar a análise pelo mesmo.

A instalação pode ser realizada de duas maneiras:

1. Compilando através do código fonte do github (Não é nada complicado): https://github.com/darold/pgbadger/releases

tar xzf pgbadger-11.x.tar.gz
cd pgbadger-11.x/
perl Makefile.PL
make && sudo make install

2. Através dos repositórios oficiais da PGDG (PostgreSQL Development Group), disponíveis para Debian, Ubutu e Red Hat.

https://www.postgresql.org/download/linux/debian/
https://www.postgresql.org/download/linux/redhat/
https://www.postgresql.org/download/linux/ubuntu/

Utilizando o pgbadger

A partir da configuração do postgres, só é necessário apontar para os devidos logs.

Basta utilizar o seguinte comando:

pgbadger arquivo_de_log -o saida.html

Para analisar mais de um log, é possível utilizar os caracteres especiais do Linux:

pgbadger arquivo_de_log* -o saida.html

Caso você queria especificar um início e fim, é possível especificar a partir dos parâmetros –begin e –end:

pgbadger –begin “2022-04-25 12:00:00” –end “2022-04-25 17:00:00” arquivo_de_log -o saida_parcial.html

Caso você tenha personalizado o log_line_prefix, basta chamar com o parâmetro –prefix.

Atenção: Nem todos os log_line_prefix são compatíveis, consulte a documentação oficial!

O arquivo de saída

O arquivo de saída do pgbadger é um arquivo .html autossuficiente.

Você pode visualizar este arquivo através do seu navegador no desktop.

Caso o procedimento tenha sido realizado no servidor, é possível realizar o download apenas do arquivo HTML, ou utilizar um serviço apache, por exemplo, para publicar este html no servidor.

O menu do pgbadger é composto pelas seguinte sessões:

• Overview -> Informações globais e sumarizadas do servidor.
• Connections -> Informações a respeito das conexões, por database, por usuário, por host e etc.
• Sessions -> Informações a respeito das sessões, duração, databases, usuários e hosts.
• Checkpoints -> Causas dos checkpoints (tempo ou wal), duração do checkpoint e bytes escritos
• Temp Files -> Tamanho dos arquivos temporários no dia, queries que geraram temp files e etc.
• Vacuums -> Distribuição de vacuums e analyzes no decorrer do período de log, tuplas e páginas removidas.
• Locks -> Distribuição dos locks, queries que mais aguardaram e por quanto tempo aguardaram.
• Queries -> Distribuição de queries (UPDATE, SELECT, INSERT, DELETE), por database, por usuário e etc.
• Top -> Esta é a página mais importante do pgbadger e será detalhado abaixo
• Events -> Informações a respeito de logs e eventos ocorridos no banco (sumarizadas de detalhadas)
• Símbolo i -> Informações sobre os logs analisados e processamento do pgbadger.

O menu top

Este é o menu mais importante do pgbadger, pois te da em detalhes quais foram as queries mais lentas e quais foram as queries mais frequentes com o tempo gasto.

* Slowest individual queries

Nesta área são disponibilizadas quais as 20 queries que tiveram a maior duração nos arquivos de log. Muito útil para detectar casos isolados que precisam de atenção

* Time consuming queries (N)

Nesta área são disponibilizadas as 20 queries que consumiram mais tempo do servidor. Esta é uma análise da soma das execuções de cada query, normalizadas (Independente do parâmetro).

Esta é a visão que já salvou algumas madrugadas trabalhando em ambientes lentos. Por aqui é possível descobrir que uma query que possivelmente tenha uma duração “aceitável” se torne lenta pela quantidade de vezes que foi executada.

O pgbadger fornece exemplos reais destas queries, então é possível verificar o plano de execução, e a partir do plano criar algum índice caso necessário.

Caso realmente não tenha o que ser otimizado do lado do banco, as queries podem ser enviadas à equipe de desenvolvimento para análise.

* Most frequent queries (N)

Esta é uma visão muito semelhante a anterior. O que muda é a ordenação, que passa a ser pela quantidade de vezes que a query foi executada.

* Normalized slowest queries (N)

Visão semelhante às demais, porém ordenado agora pela média de duração das queries.

Utilizando o pgbadger no Amazon RDS

Também é totalmente possível utilizar o pgbadger para analisar o seu ambiente postgres na AWS como serviço;

Tudo que você precisa fazer é alterar as configurações mencionadas anteriormente, mas não é necessário alterar o log_line_prefix.

Uma vez configurado, basta baixar os logs da aws com o AWS cli, e então passar a opção -f rds, conforme o exemplo abaixo:

pgbadger -f rds arquivo_de_log -o saida.html

Considerações

O pgbadger é um analisador de logs muito rico, e a riqueza da saída pode ser totalmente controlada por você.

Quanto mais informações forem disponibilizadas, maiores serão as análises geradas.
Nem todas são obrigatórias, como por exemplo se não estiverem habilitados os parâmetros log_connections e log_disconnections, os gráficos do menu “Connections” e “Sessions” não estarão disponíveis.

Considerando um cenário mais crítico, quanto mais informações foram logadas no log, melhor.
Agora com um ambiente estabilizado, algumas informações podem ser suprimidas, o que levará a um log menor, porém ainda sim útil.

É possível também habilitar o pgbader de forma incremental, com o parâmetro –incremental, e criar rotinas para atualizações automáticas, disponibilizando num servidor web!

Com certeza o pgbadger irá te surpreender!

Líder em Treinamento e serviços de Consultoria, Suporte e Implantação para o mundo open source. Conheça nossas soluções:

CURSOSCONSULTORIA