Para diagnosticar e resolver gargalos de desempenho em bases de dados PostgreSQL, a inspeção e o exame aprofundado dos logs de consultas são indispensáveis. Ao analisar esses registros, é possível identificar consultas que consomem recursos excessivamente ou demoram para executar, permitindo otimizações direcionadas.

Habilitando o Registro de Conslutas

O primeiro passo para coletar informações de desempenho é configurar o PostgreSQL para registrar as operações SQL. Isso é feito editando o arquivo de configuração principal, geralmente localizado em postgresql.conf. As seguintes configurações são essenciais para ativar e gerenciar os logs:

logging_collector = on              # Ativa o coletor de logs
log_directory = 'pg_log'            # Define o diretório para os arquivos de log
log_filename = 'postgresql-%Y-%m-%d_%H%M.log' # Formato do nome do arquivo de log
log_statement = 'all'               # Nível de log: 'none', 'ddl', 'mod', 'all'

• logging_collector: Deve ser definido como on para que o PostgreSQL colete e grave os logs.
• log_directory: Especifica o subdiretório (relativo ao PGDATA) onde os logs serão armazenados.
• log_filename: Define o padrão de nome para os arquivos de log, facilitando a organização por data e hora.
• log_statement: Controla o que será registrado. Para uma análise completa, 'all' registra todas as instruções SQL. Para cenários mais específicos, 'ddl' (Data Definition Language) e 'mod' (modificações de dados) são opções.

Gerenciamento de Rotação de Logs

Para evitar que os arquivos de log cresçam indefinidamente e consumam muito espaço em disco, é importante configurar a rotação:

log_rotation_age = 1440    # Novo arquivo de log a cada 1440 minutos (24 horas). 0 desativa.
log_rotation_size = 10240    # Novo arquivo de log após 10240 KB (10 MB). 0 desativa.
log_truncate_on_rotation = on # Reutiliza arquivos de log com o mesmo nome após a rotação.

Após quaisquer modificações no postgresql.conf, é mandatório reiniciar o serviço do PostgreSQL para que as novas configurações entrem em vigor.

Análise de Dados dos Logs

Uma vez que os logs estão sendo gerados, a próxima etapa é analisá-los para identificar gargalos de desempenho e padrões problemáticos.

Identificação de Consultas Lentas

Consultas com tempos de execução prolongados são frequentemente as principais causas de desempenho degradado. Ferramentas de linha de comando como grep e awk são extremamente úteis para filtrar e inspecionar esses registros.

Para encontrar consultas que levaram mais de 1 segundo (1000 milissegundos) para serem executadas, você pode usar os seguintes comandos, assumindo que você está no diretório $PGDATA/pg_log:

cd $PGDATA/pg_log
grep 'duration:' postgresql-*.log | awk '$4 > 1000 { print }'

Este comando filtra as linhas do log que contêm a string 'duration:' e, em seguida, usa awk para exibir apenas aquelas onde o quarto campo (assumindo que seja o tempo de duração em milissegundos) é maior que 1000. A análise dos resultados permite focar nas otimizações mais impactantes.

Avaliação de Consumo de Recursos

Além do tempo de execução, é benéfico correlacionar as informações dos logs com métricas de uso de recursos do sistema operacional, como I/O de disco, CPU e memória. Isso ajuda a entender se as lentidões são causadas por contenção de recursos ou por ineficiências na própria consulta.

Estratégias de Otimização

Uma vez identificadas as consultas problemáticas, as ações de otimização podem incluir:

• Revisão de Instruções SQL: Aprimorar a lógica da consulta, utilizando JOINS mais eficientes, evitando subconsultas desnecessárias ou simplificando expressões complexas.
• Gerenciamento de Índices: Criar novos índices ou ajustar os existentes para acelerar o acesso aos dados utilizados pelas consultas lentas.
• Ajustes de Configuração: Modificar parâmetros de configuração do banco de dados (por exemplo, work_mem, shared_buffers) para otimizar o uso de memória e I/O.

Registro Seletivo de Consultas Lentas

Para um monitoramento mais focado em desempenho, é possível configurar o PostgreSQL para registrar apenas consultas que excedem um certo limite de tempo, ignorando consultas rápidas que não são de interesse para otimização.

log_statement = 'none'                  # Desativa o log de todas as declarações
log_min_duration_statement = 5000       # milissegundos: Registra declarações com 5 segundos ou mais de duração.

Importante: Se log_statement estiver definido como 'all', ele anulará log_min_duration_statement, resultando no registro de todas as declarações, independentemente de sua duração. Defina log_statement como 'none' ou outra opção mais restritiva para que log_min_duration_statement tenha efeito.

Aplicação e Verificação das Configurações

Após ajustar esses parâmetros, você pode recarregar as configurações sem reiniciar o servidor e verificar o valor atual:

SELECT pg_reload_conf();
SHOW log_min_duration_statement;

Configuração por Banco de Dados ou Usuário

É possível aplicar a configuração log_min_duration_statement a um banco de dados específico ou a um usuário, o que permite um controle mais granular sobre quais logs são gerados:

ALTER DATABASE meu_banco_de_dados SET log_min_duration_statement = 5000;
-- ALTER USER meu_usuario SET log_min_duration_statement = 5000; -- Exemplo para usuário

Identificação de Consultas Ativas Lentas e Bloqueios

A visão do sistema pg_stat_activity é uma ferramenta poderosa para identificar consultas que estão atualmente em execução lenta ou causando bloqueios no banco de dados.

Consultas Ativas que Excedem um Tempo Específico

Para listar todas as consultas que não estão ociosas e que estão em execução há mais de 10 segundos:

SELECT pid, datname, usename, client_addr, application_name, backend_start, query_start, state, backend_xid, backend_xmin, query
FROM pg_stat_activity
WHERE state <> 'idle'
 AND now() - query_start > interval '10 seconds'
ORDER BY query_start;

Identificação de Bloqueios no Banco de Dados

Para identificar quais processos estão sendo bloqueados e por quais outros processos, juntamente com a tabela envolvida:

SELECT
   blocker.pid AS blocking_pid,
   blocker.usename AS blocking_user,
   blocker.query AS blocking_query,
   blocked.pid AS blocked_pid,
   blocked.usename AS blocked_user,
   blocked.query AS blocked_query,
   pg_locks.mode AS lock_mode,
   pg_locks.relation::regclass AS locked_table
FROM pg_catalog.pg_locks AS pg_locks
JOIN pg_catalog.pg_stat_activity AS blocker ON pg_locks.pid = blocker.pid AND pg_locks.granted
JOIN pg_catalog.pg_stat_activity AS blocked ON pg_blocking_pid(blocked.pid) = blocker.pid
WHERE blocked.wait_event_type = 'Lock';