Database observability: slow query log e métricas

1. Fundamentos da Observabilidade em Banco de Dados

Observabilidade em banco de dados é a capacidade de entender o estado interno do sistema a partir dos dados que ele produz externamente — logs, métricas e traces. Diferente do monitoramento reativo, que apenas alerta quando algo quebra, a observabilidade proativa permite detectar degradações antes que se tornem incidentes.

Os três pilares da observabilidade são:

• Logs: registros textuais de eventos, como o slow query log
• Métricas: valores numéricos coletados ao longo do tempo (latência, throughput)
• Traces: rastreamento do fluxo de uma requisição através de múltiplos sistemas

Neste artigo, focaremos nos dois primeiros pilares aplicados a consultas SQL.

2. Slow Query Log: Configuração e Ativação

O slow query log é o primeiro passo para entender quais consultas estão consumindo mais recursos.

MySQL

Para ativar o slow query log no MySQL:

-- Ativar slow query log globalmente
SET GLOBAL slow_query_log = ON;
SET GLOBAL slow_query_log_file = '/var/log/mysql/mysql-slow.log';
SET GLOBAL long_query_time = 2;  -- consultas com mais de 2 segundos
SET GLOBAL log_queries_not_using_indexes = ON;
SET GLOBAL log_slow_admin_statements = ON;

Para persistir as configurações, adicione ao my.cnf:

[mysqld]
slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log
long_query_time = 2
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1

PostgreSQL

No PostgreSQL, a abordagem é diferente. Use a extensão pg_stat_statements:

-- Adicione ao postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000
track_io_timing = on

-- Após restart, instale a extensão
CREATE EXTENSION pg_stat_statements;

Para consultar as queries mais lentas:

SELECT query, calls, total_exec_time, mean_exec_time, rows
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

Boas práticas de armazenamento

• Configure rotação de logs com logrotate para evitar que logs cresçam indefinidamente
• Defina um long_query_time adequado ao seu ambiente (1-5 segundos para produção)
• Em ambientes de alta carga, considere amostragem (ex: log_slow_rate_limit no MySQL 8.0)

3. Análise de Slow Queries: Identificando Gargalos

Interpretar um slow query log requer atenção a campos específicos. Exemplo de uma entrada típica no MySQL:

# Time: 2025-01-15T10:30:00.123456Z
# User@Host: app_user[app_user] @ localhost []
# Query_time: 12.345678  Lock_time: 0.001234
# Rows_sent: 100  Rows_examined: 500000
SET timestamp=1736922600;
SELECT * FROM orders o
JOIN order_items oi ON o.id = oi.order_id
WHERE o.created_at > '2025-01-01';

Análise: Query_time de 12.3s, Rows_examined de 500.000 para retornar apenas 100 linhas. Isso sugere falta de índice ou filtro ineficiente.

Ferramentas de análise

pt-query-digest (Percona Toolkit):

pt-query-digest /var/log/mysql/mysql-slow.log

Saída típica:

# Profile
# Rank Query ID           Response time    Calls R/Call  V/M
# ==== ================== ================ ===== ======= =====
#    1 0xDEADBEEF12345678  1200.0000 60.0%   100 12.0000  0.00 SELECT orders
#    2 0xCAFEBABE87654321   400.0000 20.0%    50  8.0000  0.00 SELECT users

pgBadger (PostgreSQL):

pgbadger /var/log/postgresql/postgresql-*.log -o report.html

Padrões comuns de queries lentas

1. Falta de índices: varreduras sequenciais em tabelas grandes
2. Joins ineficientes: junção sem índices nas colunas de junção
3. Funções em colunas indexadas: WHERE YEAR(data) = 2025 — use data BETWEEN '2025-01-01' AND '2025-12-31'
4. Uso excessivo de LIKE: LIKE '%termo%' impede uso de índices B-tree

4. Métricas Essenciais de Performance de Consultas

Latência

Métricas de latência devem ser coletadas em percentis para evitar distorções por outliers:

-- No PostgreSQL via pg_stat_statements
SELECT
    query,
    mean_exec_time,
    stddev_exec_time,
    min_exec_time,
    max_exec_time,
    percentile_cont(0.95) WITHIN GROUP (ORDER BY total_exec_time) AS p95
FROM pg_stat_statements
GROUP BY query, mean_exec_time, stddev_exec_time, min_exec_time, max_exec_time;

No MySQL, use a tabela performance_schema.events_statements_summary_by_digest:

SELECT
    DIGEST_TEXT,
    COUNT_STAR,
    AVG_TIMER_WAIT / 1000000000 AS avg_latency_ms,
    MAX_TIMER_WAIT / 1000000000 AS max_latency_ms
FROM performance_schema.events_statements_summary_by_digest
ORDER BY AVG_TIMER_WAIT DESC
LIMIT 20;

Throughput

Queries por segundo (QPS) e transações por segundo (TPS) indicam a carga no banco:

-- MySQL
SHOW GLOBAL STATUS LIKE 'Queries';
SHOW GLOBAL STATUS LIKE 'Com_commit';

-- PostgreSQL
SELECT queries_per_second() FROM pg_stat_database;

Taxa de erros

-- PostgreSQL: deadlocks
SELECT datname, deadlocks
FROM pg_stat_database
WHERE datname NOT IN ('template0', 'template1');

-- MySQL: timeouts
SHOW GLOBAL STATUS LIKE 'Handler_read_rnd_next';

5. Métricas de Recursos do Banco de Dados

Cache hit ratio

Indica a eficiência do cache de dados:

-- PostgreSQL
SELECT
    'buffer_hit' AS metric,
    (blks_hit::numeric / (blks_hit + blks_read) * 100) AS ratio
FROM pg_stat_database
WHERE datname = current_database();

-- MySQL InnoDB
SHOW ENGINE INNODB STATUS\G
-- Procure por "Buffer pool hit rate"

Conexões ativas

-- PostgreSQL
SELECT count(*) AS active_connections
FROM pg_stat_activity
WHERE state = 'active';

-- MySQL
SHOW PROCESSLIST;
SHOW STATUS LIKE 'Threads_connected';

I/O de disco

-- PostgreSQL
SELECT schemaname, tablename,
       seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch
FROM pg_stat_user_tables
ORDER BY seq_scan DESC;

6. Integração com Sistemas de Observabilidade

Prometheus + Exporters

postgres_exporter:

# docker-compose.yml
services:
  postgres_exporter:
    image: prometheuscommunity/postgres-exporter
    environment:
      DATA_SOURCE_NAME: "postgresql://user:pass@localhost:5432/db?sslmode=disable"
    ports:
      - "9187:9187"

mysqld_exporter:

mysqld_exporter --config.my-cnf=/etc/mysql_exporter/.my.cnf --web.listen-address=:9104

Envio de logs para Elasticsearch/Loki

Com Filebeat e Logstash:

# filebeat.yml
filebeat.inputs:
- type: log
  paths:
    - /var/log/mysql/mysql-slow.log
  multiline.pattern: '^# Time:'
  multiline.negate: true
  multiline.match: after

output.elasticsearch:
  hosts: ["localhost:9200"]

Alertas no Grafana

Exemplo de alerta para latência alta:

ALERT HighQueryLatency
  IF avg(rate(mysql_global_status_queries[5m])) > 1000
  FOR 5m
  LABELS { severity = "critical" }
  ANNOTATIONS {
    summary = "Alta latência de queries detectada"
  }

7. Técnicas de Otimização Baseadas em Observabilidade

Index tuning

Identifique índices ausentes analisando o slow query log:

-- PostgreSQL: índices sugeridos pelo sistema
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0
ORDER BY pg_relation_size(indexrelid) DESC;

Rewriting de queries

Antes (lento):

SELECT * FROM orders
WHERE EXTRACT(YEAR FROM created_at) = 2025;

Depois (rápido):

SELECT * FROM orders
WHERE created_at >= '2025-01-01'
  AND created_at < '2026-01-01';

Ajuste de parâmetros

Com base em métricas históricas:

-- PostgreSQL: work_mem
-- Se muitas queries usam disco para ordenação (temp_files > 0)
SELECT datname, temp_files, temp_bytes
FROM pg_stat_database;

-- MySQL: innodb_buffer_pool_size
-- O ideal é 70-80% da RAM disponível
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

8. Práticas Avançadas e Automação

Dashboard de slow queries por schema

Crie uma view no PostgreSQL:

CREATE VIEW slow_queries_dashboard AS
SELECT
    schemaname,
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    rows
FROM pg_stat_statements
JOIN pg_stat_user_tables ON TRUE
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY total_exec_time DESC;

Automação com scripts

Script para análise periódica:

#!/bin/bash
# analyze_slow_queries.sh
pt-query-digest /var/log/mysql/mysql-slow.log > /reports/slow_$(date +%Y%m%d).txt
pgbadger /var/log/postgresql/postgresql-*.log -o /reports/pgbadger_$(date +%Y%m%d).html

Redução de ruído

Filtre queries triviais:

-- MySQL: configure long_query_time por sessão
SET SESSION long_query_time = 5;

-- PostgreSQL: ignore queries de sistema
SELECT query, calls, total_exec_time
FROM pg_stat_statements
WHERE query NOT LIKE '%pg_%'
  AND query NOT LIKE '%information_schema%'
  AND query NOT LIKE '%pg_catalog%';

Agrupe por fingerprint para identificar padrões:

SELECT
    queryid,
    query,
    calls,
    total_exec_time
FROM pg_stat_statements
ORDER BY total_exec_time DESC;

Referências

• MySQL Slow Query Log Documentation — Documentação oficial MySQL sobre configuração e uso do slow query log
• PostgreSQL pg_stat_statements Documentation — Documentação oficial da extensão de estatísticas de queries do PostgreSQL
• Percona Toolkit pt-query-digest — Guia completo da ferramenta de análise de slow query logs
• pgBadger Documentation — Repositório oficial com documentação do analisador de logs PostgreSQL
• Prometheus PostgreSQL Exporter — Projeto oficial do exporter PostgreSQL para métricas Prometheus
• Grafana Dashboard for MySQL — Dashboard oficial de monitoramento MySQL no Grafana
• Elasticsearch Slow Query Log Analysis — Tutorial da Elastic sobre análise de slow queries com Elastic Stack