Estratégias de monitoramento de queries lentas em produção

1. Fundamentos do monitoramento de queries lentas

Em ambientes de produção, uma query lenta é definida como qualquer consulta que excede um limite aceitável de tempo de execução, impactando diretamente a experiência do usuário e a estabilidade do sistema. O impacto vai além da latência: queries lentas consomem recursos de CPU, memória e I/O, podendo causar efeitos cascata como timeout de conexões, filas de espera e degradação geral do banco.

As métricas-chave para monitoramento incluem:
- Tempo de execução: duração total da query no banco
- Latência de rede: tempo de transmissão entre aplicação e banco
- Bloqueios e contenção: tempo que a query espera por locks ou recursos
- Linhas examinadas vs. retornadas: indicador de eficiência do plano de execução

A diferença entre monitoramento reativo e preventivo é crucial. O reativo atua após o problema ocorrer (ex.: alerta de timeout), enquanto o preventivo identifica padrões de degradação antes do impacto ao usuário (ex.: aumento gradual no tempo médio de execução).

2. Configuração de logs e parâmetros do banco de dados

A ativação do log de queries lentas é o primeiro passo. No PostgreSQL, configure no postgresql.conf:

# postgresql.conf
log_min_duration_statement = 1000  # logs queries que levam mais de 1 segundo
log_connections = on
log_disconnections = on
log_duration = on

No MySQL, a configuração equivalente é:

# my.cnf
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1

Para evitar sobrecarga de disco, implemente rotação de logs com logrotate:

# /etc/logrotate.d/postgresql
/var/log/postgresql/*.log {
    daily
    rotate 7
    compress
    delaycompress
    missingok
    notifempty
    create 640 postgres postgres
    sharedscripts
    postrotate
        /usr/bin/pg_ctl reload > /dev/null
    endscript
}

3. Ferramentas de análise e visualização em tempo real

O PostgreSQL oferece pg_stat_statements para coleta de estatísticas detalhadas:

-- Habilitar extensão
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Consultar queries mais lentas
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

No MySQL, utilize sys.schema_unused_indexes:

-- Identificar índices não utilizados
SELECT * FROM sys.schema_unused_indexes
WHERE object_schema NOT IN ('mysql', 'sys', 'performance_schema');

Para visualização em tempo real, integre com Prometheus e Grafana:

# prometheus.yml - scrape config para postgres_exporter
scrape_configs:
  - job_name: 'postgres'
    static_configs:
      - targets: ['localhost:9187']
    metrics_path: /metrics

Crie dashboards no Grafana com métricas como:
- Tempo médio de execução por query
- Número de queries lentas por minuto
- Bloqueios ativos e deadlocks

4. Identificação de gargalos comuns em produção

Queries sem índices ou com índices subutilizados: utilize EXPLAIN para identificar scans completos:

EXPLAIN ANALYZE
SELECT * FROM orders WHERE customer_id = 12345 AND status = 'pending';
-- Output mostra "Seq Scan on orders" - indica falta de índice

Bloqueios e deadlocks: monitore locks ativos:

-- PostgreSQL: consultar locks ativos
SELECT pid, locktype, relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted;

Estatísticas desatualizadas: verifique a cardinalidade:

-- PostgreSQL: verificar últimas análises
SELECT schemaname, tablename, last_analyze, last_autoanalyze
FROM pg_stat_user_tables
WHERE last_analyze IS NULL OR last_analyze < now() - interval '1 day';

5. Estratégias de alerta e escalonamento

Defina thresholds por ambiente:

# Configuração de alertas no Prometheus
groups:
  - name: slow_queries
    rules:
      - alert: HighQueryLatency
        expr: avg(rate(pg_stat_activity_max_query_duration_seconds[5m])) > 2
        for: 5m
        labels:
          severity: critical
        annotations:
          summary: "Tempo médio de queries acima de 2s"

Integre com sistemas de notificação:

# Alertmanager config
receivers:
  - name: 'slack-notifications'
    slack_configs:
      - channel: '#alerts-database'
        api_url: 'https://hooks.slack.com/services/...'
        send_resolved: true
  - name: 'pagerduty'
    pagerduty_configs:
      - routing_key: 'your-routing-key'

Estabeleça roteamento claro:
- Time de banco: queries lentas com impacto em infraestrutura (CPU, I/O, locks)
- Time de aplicação: queries lentas relacionadas a mudanças recentes em código

6. Otimização baseada em dados coletados

Use EXPLAIN ANALYZE para simular e entender o plano de execução:

EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT c.name, SUM(o.total) as total_spent
FROM customers c
JOIN orders o ON c.id = o.customer_id
WHERE o.created_at > now() - interval '30 days'
GROUP BY c.name
ORDER BY total_spent DESC
LIMIT 10;

Refatore queries problemáticas. Exemplo de query ineficiente:

-- Query original (lenta)
SELECT * FROM products
WHERE category_id IN (
    SELECT id FROM categories WHERE active = 1
)
AND price > 100
ORDER BY created_at DESC;

Versão otimizada com índice composto:

-- Criar índice composto
CREATE INDEX idx_products_category_price_created
ON products(category_id, price, created_at DESC);

-- Query otimizada (usa o índice)
SELECT p.*
FROM products p
JOIN categories c ON p.category_id = c.id
WHERE c.active = 1
AND p.price > 100
ORDER BY p.created_at DESC;

7. Monitoramento contínuo e ciclo de feedback

Implemente revisões periódicas em sprints de performance:

# Script para extrair top 10 queries lentas da última semana
SELECT
    query,
    calls,
    mean_time,
    total_time / 1000 / 60 as total_minutes
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_stat%'
ORDER BY total_time DESC
LIMIT 10;

Testes de carga com dados mascarados:

# Comando pgbench para teste de carga
pgbench -h localhost -p 5432 -U postgres -d mydb \
    -c 50 -j 4 -T 300 \
    -f /path/to/test_queries.sql \
    --log-prefix=pgbench_slow

Documente playbooks para resposta rápida:

# Playbook: Query lenta identificada
1. Identificar PID da query:
   SELECT pid, query, state FROM pg_stat_activity WHERE state = 'active';
2. Analisar plano de execução:
   EXPLAIN (ANALYZE, BUFFERS) <query>;
3. Ações corretivas:
   - Cancelar query: SELECT pg_cancel_backend(pid);
   - Terminar query: SELECT pg_terminate_backend(pid);
   - Criar índice faltante
   - Atualizar estatísticas: ANALYZE <table>;
4. Registrar incidente e agendar revisão pós-mortem

O ciclo de feedback contínuo garante que o monitoramento evolua com o sistema, identificando novos padrões de lentidão antes que impactem os usuários finais.

Referências