Solução inicial de problemas de desempenho comuns do PostgreSQL no Aurora PostgreSQL

Este guia aborda os quatro problemas de desempenho mais comuns que afetam os bancos de dados Aurora PostgreSQL: sobrecarga de tabelas e índices, esgotamento de recursos de consultas paralelas, alta pressão de conexão e autenticação e ajuste de autovacuum. Antes de iniciar uma investigação mais aprofundada, use este guia como uma lista de verificação de diagnóstico inicial quando houver degradação de desempenho.

Cada seção descreve os sintomas que você pode observar, oferece consultas de diagnóstico para confirmar a causa raiz e recomenda etapas específicas de correção.

Lista de verificação de diagnóstico rápido

Use as seguintes etapas de triagem ordenada ao investigar pela primeira vez um problema de desempenho:

Sobrecarga de tabelas e índices

A sobrecarga de tabelas e índices ocorre quando tuplas mortas se acumulam nas tabelas mais depressa do que o autovacuum consegue recuperá-las. Com o tempo, isso degrada gradualmente o desempenho das consultas, aumenta o uso do armazenamento e gera planos de consulta abaixo do ideal.

Sintomas

Diagnóstico

É possível estimar a sobrecarga em todas as tabelas consultando o catálogo do sistema. Esta abordagem não requer nenhuma extensão:

SELECT schemaname, relname,
       n_dead_tup,
       n_live_tup,
       ROUND(n_dead_tup::numeric / GREATEST(n_live_tup, 1) * 100, 2) AS dead_pct,
       last_autovacuum,
       last_autoanalyze
FROM pg_stat_user_tables
WHERE n_dead_tup > 10000
ORDER BY n_dead_tup DESC
LIMIT 20;

Para lidar com a sobrecarga, você pode usar a extensão pg_repack para reorganizar tabelas e índices com o mínimo de bloqueio. Para ter mais informações, consulte Reduzir o inchaço em tabelas e índices com a extensão pg_repack e Remove bloat from Amazon Aurora and RDS for PostgreSQL with pg_repack.

Esgotamento dos recursos de consulta paralela

O PostgreSQL pode executar consultas paralelamente para melhorar o desempenho de grandes verificações sequenciais e agregações. No entanto, cada operador paralelo é um processo de backend completo é contabilizado em relação a max_worker_processes (e ao sublimite max_parallel_workers) e aloca seu próprio work_mem. Uma única consulta com quatro operadores paralelos pode consumir centenas de megabytes de memória e uma quantidade significativa de CPU. Em condições de alta simultaneidade, o paralelismo excessivo pode esgotar a CPU e a memória rapidamente.

Os sintomas comuns incluem picos repentinos de CPU, alto uso de memória por consulta e aumento de DatabaseConnections no CloudWatch sem alterações na aplicação. Você também pode observar eventos de espera, como IPC:BgWorkerStartup, IPC:ExecuteGather e IPC:ParallelFinish. Para obter mais informações sobre esses eventos de espera IPC: eventos de espera paralelos.

Para a maioria das workloads de OLTP e de produção de alta simultaneidade, desabilite o paralelismo automático configurando max_parallel_workers_per_gather = 0 em seu grupo de parâmetros de banco de dados. Em seguida, você pode habilitar seletivamente o paralelismo para sessões específicas de analytics ou geração de relatórios definindo o parâmetro por sessão ou por perfil.

Para ver orientações detalhadas sobre como diagnosticar e controlar o comportamento de consultas paralelas, consulte Práticas recomendadas para consultas paralelas no Aurora PostgreSQL.

Alta pressão de conexão e autenticação

A rotatividade de conexão (abertura e fechamento frequentes de conexões de banco de dados sem agrupamento) cria sobrecarga de autenticação e pode esgotar os slots de conexão disponíveis. As conexões ociosas que permanecem abertas também consomem slots sem realizar trabalhos úteis.

Sintomas

Diagnóstico

Execute a seguinte consulta para verificar o estado da conexão atual:

SELECT
  setting::int AS max_connections,
  (SELECT count(*) FROM pg_stat_activity) AS current_connections,
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle') AS idle_connections,
  (SELECT count(*) FROM pg_stat_activity WHERE state = 'idle in transaction') AS idle_in_txn
FROM pg_settings
WHERE name = 'max_connections';

Um número alto de conexões idle ou idle in transaction em relação a max_connections indica que as conexões não estão sendo liberadas adequadamente. As conexões ociosas na transação são especialmente problemáticas porque bloqueiam e impedem que o autovacuum recupere tuplas mortas.

Correção

Usar eventos de espera do Insights de Performance para solução de problemas

O Insights de Performance captura eventos de espera que mostram onde o banco de dados está gastando tempo. Ao investigar problemas de desempenho, os eventos de espera ajudam a identificar se o gargalo é de CPU, E/S, bloqueio, rede ou comunicação entre processos. As categorias comuns de eventos de espera que aparecem durante os problemas descritos neste guia incluem:

Para ver uma referência completa dos eventos de espera que geralmente indicam problemas de desempenho e as respectivas ações corretivas recomendadas, consulte Eventos de espera do Aurora PostgreSQL.

Ajuste do autovacuum

O autovacuum é o processo em segundo plano que recupera tuplas mortas, evita a sobrecarga de tabelas e índices, atualiza as estatísticas do planejador e impede o encapsulamento de IDs de transação. As configurações padrão de autovacuum são conservadoras e projetadas para bancos de dados pequenos. Workloads de produção com alto volume de gravação quase sempre exigem ajustes.

Quando o autovacuum não consegue acompanhar a workload de gravação, a sobrecarga aumenta, as estatísticas do planejador ficam obsoletas e o risco de encapsulamento de IDs de transação aumenta. Se age(relfrozenxid) se aproximar de 2 bilhões, o banco de dados será encerrado para evitar a corrupção de dados.

Para obter orientações detalhadas sobre como ajustar parâmetros de autovacuum, monitorar a atividade de vacuum e configurar substituições por tabela, consulte Trabalhar com o autovacuum do PostgreSQL no Aurora PostgreSQL.

Informações relacionadas