PostgreSQL com CPU alta: investigando I/O, waits e dead tuples antes de aumentar o servidor

Quando PostgreSQL aparece no topo do top e o load sobe, aumentar CPU parece uma resposta natural.

Em uma investigação real, porém, a coleta mostrou que olhar apenas %CPU seria pouco. Havia sessões ativas esperando leitura de dados, outras em espera de buffers e tabelas com volume relevante de dead tuples.

A pergunta deixou de ser:

Quantas CPUs faltam?

E passou a ser:

qual trabalho o banco está fazendo — e esperando — para consumir a infraestrutura atual?

O primeiro sinal útil veio de `pg_stat_activity` #

Uma coleta de sessões ativas mostrou consultas com waits diferentes.

Entre eles:

1
2
wait_event_type = IO
wait_event      = DataFileRead

E também:

1
2
wait_event_type = IPC
wait_event      = BufferIO

Havia consultas com dezenas de segundos de duração, incluindo contagens e joins entre tabelas operacionais.

Isso é mais informativo do que apenas constatar que postgres usa CPU.

Uma consulta simples para começar:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
SELECT
  pid,
  usename,
  datname,
  client_addr,
  state,
  now() - query_start AS duracao,
  wait_event_type,
  wait_event,
  left(query, 500) AS query
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start ASC;

Eu procuro principalmente:

consultas repetidas;
duração;
paralelismo;
wait_event_type;
wait_event;
transações antigas;
bloqueios;
sessões idle in transaction.

`DataFileRead` muda a leitura do incidente #

DataFileRead indica que um backend está esperando leitura de bloco de arquivo de dados.

Isso não significa automaticamente “disco lento”.

Pode existir relação com:

volume de blocos que a consulta precisa ler;
baixa seletividade;
plano inadequado;
cache insuficiente para aquele working set;
concorrência;
storage realmente pressionado.

A diferença é importante.

Se eu vejo:

1
2
3
4
5
CPU alta
+
load alto
+
DataFileRead

não parto direto para scale-up. Primeiro tento entender por que a consulta está precisando ler tanto.

O segundo eixo foi manutenção de tabelas #

Outra coleta usou pg_stat_user_tables para comparar linhas vivas estimadas, dead tuples e histórico de manutenção.

Em um snapshot, três tabelas apresentavam dead tuples relevantes, incluindo valores na ordem de dezenas de milhares em duas delas.

A consulta usada era equivalente a:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
SELECT
  schemaname,
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

O ponto não é usar n_dead_tup como causa raiz.

Ele serve para perguntar:

1
2
3
4
5
a tabela recebe muita atualização/delete?
o autovacuum está acompanhando?
as estatísticas estão atualizadas?
há bloat relevante?
o planner está trabalhando com estimativas ruins?

Dead tuples não significam `VACUUM FULL` #

Uma reação perigosa seria ver dead tuples e executar:

1
VACUUM FULL ...;

em produção.

Isso não é diagnóstico de baixo impacto e pode bloquear a tabela.

Antes, eu verificaria:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_vacuum,
  last_autovacuum,
  last_analyze,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

Depois correlacionaria com taxa de escrita, tamanho da tabela, autovacuum e comportamento das consultas.

Índice existente não significa consulta bem atendida #

A coleta também levantou os índices das tabelas envolvidas.

Havia diversos índices simples, compostos e parciais.

Isso elimina outra conclusão apressada:

Está lento porque não tem índice.

Ter muitos índices não prova que o plano atual utiliza o índice certo.

E criar mais um índice sem olhar o plano pode aumentar custo de escrita sem resolver a consulta crítica.

Quando for seguro reproduzir o SQL, o próximo passo é:

1
2
EXPLAIN (ANALYZE, BUFFERS)
SELECT ...;

Em produção, ANALYZE dentro de EXPLAIN executa a consulta. Então isso só deve ser feito quando o impacto for conhecido.

Quando não for seguro, começar com:

1
2
EXPLAIN
SELECT ...;

já ajuda a entender a estratégia planejada sem executar a consulta.

Concorrência também apareceu na fotografia #

Em outra janela, múltiplas sessões executavam versões da mesma consulta ou consultas muito próximas ao mesmo tempo.

Algumas esperavam DataFileRead; outra aparecia em BufferIO.

Isso levanta uma pergunta que scale-up sozinho não responde:

a aplicação está enviando trabalho duplicado ou concorrente demais para a mesma região de dados?

Nesse ponto eu cruzaria:

1
2
3
4
5
6
7
query
application_name
client_addr
horário
duração
frequência
endpoint/job que dispara a consulta

pg_stat_statements, quando habilitado, é especialmente útil para sair da fotografia instantânea e enxergar frequência, tempo acumulado e número de chamadas.

A investigação precisa separar alívio de correção #

Em um incidente, cancelar uma query pode ser necessário para recuperar capacidade.

Por exemplo:

1
SELECT pg_cancel_backend(<pid>);

ou, em situações específicas e bem avaliadas:

1
SELECT pg_terminate_backend(<pid>);

Mas isso responde somente:

consigo aliviar o ambiente agora?

Não responde:

por que a query ficou cara?

Ação emergencial e solução estrutural precisam ser registradas como coisas diferentes.

O host ainda precisa ser medido #

PostgreSQL não existe isolado do sistema operacional.

Eu correlacionaria a coleta SQL com:

1
2
3
4
uptime
free -m
vmstat 1 10
iostat -xz 1 10

E processos:

1
2
ps -eo pid,ppid,stat,%cpu,%mem,etime,cmd \
  --sort=-%cpu | head -40

A ideia é diferenciar cenários como:

1
2
3
4
5
6
CPU realmente saturada
I/O wait alto
memória pressionada
swap
storage com latência
muitos workers concorrentes

Dois hosts com “load 10” podem ter problemas completamente diferentes.

Quando `ANALYZE` entra na conversa #

Se as estatísticas estão antigas ou houve muita modificação desde o último analyze, atualizar estatísticas pode ajudar o planner.

1
ANALYZE VERBOSE nome_da_tabela;

Mas há uma regra editorial e operacional importante:

executar ANALYZE e depois ver melhora não prova sozinho que ele era a causa do incidente.

Para afirmar causalidade eu precisaria comparar plano, estatísticas e comportamento antes/depois de forma controlada.

O que posso afirmar com segurança é que manter estatísticas coerentes faz parte da saúde do planner.

Um roteiro read-only antes do scale-up #

1. Estado do host #

1
2
3
4
uptime
free -m
vmstat 1 10
iostat -xz 1 10

2. Sessões ativas #

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
SELECT
  pid,
  state,
  wait_event_type,
  wait_event,
  now() - query_start AS duracao,
  left(query, 500)
FROM pg_stat_activity
WHERE state <> 'idle'
ORDER BY query_start;

3. Manutenção das tabelas #

1
2
3
4
5
6
7
8
SELECT
  relname,
  n_live_tup,
  n_dead_tup,
  last_autovacuum,
  last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;

4. Índices #

1
2
3
SELECT tablename, indexname, indexdef
FROM pg_indexes
ORDER BY tablename, indexname;

5. Tamanho #

1
2
3
4
5
SELECT
  relname,
  pg_size_pretty(pg_total_relation_size(relid)) AS total
FROM pg_catalog.pg_statio_user_tables
ORDER BY pg_total_relation_size(relid) DESC;

6. Histórico de consultas #

Se disponível, consultar pg_stat_statements.

Quando aumentar servidor faz sentido #

Scale-up continua sendo uma ferramenta válida.

Eu consideraria quando a evidência mostra que:

workload cresceu de verdade;
consultas críticas já foram revisadas;
manutenção está saudável;
planos são coerentes;
storage ou CPU são limitantes confirmados;
a capacidade necessária não cabe na janela operacional atual.

O que eu evitaria é usar mais hardware como substituto para a coleta.

O que a fonte deste caso prova — e o que não prova #

A coleta técnica recuperada prova:

PostgreSQL 14 em operação;
consultas ativas com DataFileRead;
consulta em BufferIO;
concorrência entre consultas;
dead tuples relevantes em tabelas operacionais;
inventário de índices das tabelas analisadas.

Ela não é usada aqui para afirmar uma causa raiz única.

Também retirei do título original a expressão “milhões de registros”, porque não consegui religar esse número específico à mesma coleta com o nível de evidência que quero manter no site.

O principal aprendizado #

CPU alta é sintoma.

DataFileRead é pista.

Dead tuples são contexto.

Índices existentes são contexto.

O diagnóstico aparece quando esses sinais são correlacionados com o SQL e com o estado do host.

Antes de comprar mais CPU, vale descobrir qual trabalho está obrigando o PostgreSQL a pedir mais dela.

O primeiro sinal útil veio de pg_stat_activity #

DataFileRead muda a leitura do incidente #