Como identifico e soluciono problemas de desempenho e consultas de execução lenta em minha instância de banco de dados do Amazon RDS para PostgreSQL ou compatível do Aurora PostgreSQL?

9 minuto de leitura
0

Minha instância de banco de dados Amazon Relational Database Service (Amazon RDS) para PostgreSQL ou compatível com Amazon Aurora PostgreSQL está lenta. Quero identificar e solucionar as consultas de execução lenta.

Resolução

Hardware subdimensionado, mudanças no workload, aumento do tráfego, problemas de memória ou consultas não otimizadas afetam o desempenho da instância de banco de dados. Para resolver problemas de desempenho, execute as seguintes ações.

Identifique a causa

Verificar métricas do CloudWatch

Para identificar gargalos de desempenho causados por recursos insuficientes, monitore as métricas CPUUtilization, FreaableMemory e SwapUsage do Amazon CloudWatch.

Quando a utilização da CPU é alta, um workload ativo na instância de banco de dados exige mais recursos de CPU. A baixa disponibilidade de memória para o workload causa trocas frequentes que resultam em alta utilização de memória e alto uso de espaço de troca. Consultas de longa duração, aumento repentino do tráfego ou um grande número de conexões ociosas podem causar alta utilização da CPU e altos recursos de memória.

Para visualizar as consultas ativas com runtime, execute o seguinte comando:

SELECT pid, usename, age(now(),xact_start) query_time, query FROM pg_stat_activity WHERE state='active';

Para visualizar as conexões inativas no banco de dados, execute o seguinte comando:

SELECT count(*) FROM pg_stat_activity WHERE state='idle';

Para encerrar conexões inativas, execute o seguinte comando:

SELECT pg_terminate_backend(example-pid);

Observação: Substitua example-pid pelo ID do processo da conexão inativa.

Para verificar se sua instância de banco de dados atinge o throughput de rede esperado, verifique as métricas NetworkReceiveThroughput e NetworkTransmitThroughput. Classes de instâncias subdimensionadas ou não otimizadas do Amazon Elastic Block Service (Amazon EBS) podem afetar o throughput da rede e resultar em instâncias lentas. Um baixo throughput de rede pode resultar em respostas lentas para todas as solicitações de aplicações, independentemente do desempenho do banco de dados.

Para avaliar o desempenho de E/S, verifique as métricas ReadIOPS, WriteIOPS, ReadLatency, WriteLatency, ReadThroughput, WriteThroughput e DiskQueueDepth. Para obter mais informações, consulte Como soluciono problemas de latência dos volumes do Amazon EBS causada por um gargalo de IOPS na minha instância do Amazon RDS?

Usar monitoramento avançado

Use o Monitoramento Avançado para visualizar métricas no nível do sistema operacional (SO) e listar os 100 principais processos que usam alta CPU e memória. Ative o monitoramento aprimorado com granularidade definida como 1 para identificar problemas intermitentes de desempenho em sua instância de banco de dados.

Avalie as métricas de sistema operacional disponíveis para diagnosticar problemas de desempenho relacionados à CPU, workload, E/S, memória e rede. Na lista de processos, identifique processos com valores altos para CPU% ou Mem%.

Exemplo:

NAMEVIRTRESCPU%MEM%VMLIMIT
postgres: postgres postgres 178.2.0.44(42303) SELECT [10322]250,66 MB27,7 MB85,932,21ilimitado

Conecte-se ao banco de dados e, em seguida, execute a consulta a seguir para encontrar a conexão com alta CPU no banco de dados:

SELECT * FROM pg_stat_activity WHERE pid = 10322;

Observação: Substitua 10322 pelo ID do processo da conexão.

Verificar métricas do Performance Insights

Use o Performance Insights para avaliar as cargas de trabalho do banco de dados por espera, SQL, host ou usuários. Você também pode obter o banco de dados e as métricas em nível SQL.

Use a guia Top SQL no painel do Performance Insights para visualizar as instruções SQL que mais contribuem para a carga do banco de dados. Se a carga de banco de dados ou a carga por espera (AAS) for maior que a vCPU máxima, o workload na classe da instância de banco de dados será limitada.

Use a latência média por chamada nas estatísticas SQL para visualizar o tempo médio de execução de uma consulta. O Top SQL se baseia no tempo total de execução. Como resultado, o SQL com o maior tempo de execução geralmente é diferente do SQL que é o principal contribuidor de carga de banco de dados.

Verifique as estatísticas do banco de dados

Para avaliar o desempenho do banco de dados no PostgreSQL, verifique as estatísticas de distribuição de dados, estatísticas estendidas e estatísticas de monitoramento. Para obter informações sobre estatísticas, consulte Entendendo estatísticas no PostgreSQL.

Verifique as ferramentas de banco de dados nativas

Para identificar consultas lentas, use a ferramenta nativa pgbadger no site do GitHub. Para obter mais informações, consulte Otimização e ajuste de consultas no Amazon RDS para PostgreSQL com base em ferramentas nativas e externas.

Otimize o desempenho

Ajuste as configurações de memória

Você pode definir o parâmetro shared_buffers como um valor que ajuda a melhorar o desempenho da consulta.

Os parâmetros work_mem e maintenance_work_mem definem a quantidade de memória usada para processos de back-end. Para obter mais informações, consulte 20.4 Consumo de recursos no site do PostgreSQL. Se você costuma experimentar um alto uso de memória na instância de banco de dados, reduza os valores dos parâmetros no grupo de parâmetros personalizados anexado à sua instância.

Use o gerenciamento do plano de consulta compatível com o Aurora PostgreSQL

Use o gerenciamento do plano de consulta compatível com o Aurora PostgreSQL para controlar como e quando os planos de execução de consultas são alterados. Para obter mais informações, consulte Práticas recomendadas para o gerenciamento do plano de consulta do Aurora PostgreSQL.

Solucionar problemas de consultas de execução lenta

Problemas de infraestrutura, planejamento de consultas não otimizado ou alto uso geral de recursos causam lentidão nas consultas. O planejador de consultas do PostgreSQL usa estatísticas de tabela para criar planos de consulta. Mudanças no esquema e estatísticas antigas podem afetar os planos. Tabelas e índices sobrecarregados também podem resultar em consultas lentas.

Quando uma tabela atinge o limite de tuplas mortas, o daemon de autovacuum cria processos de trabalho de autovacuum que removem tuplas mortas da tabela. O daemon de autovacuum também executa a operação ANALYZE que atualiza as estatísticas da tabela.

Execute a consulta a seguir para verificar se há tuplas mortas e operações de autovacuum ou vacuum e execuções autoanalyze ou analyze:

SELECT schemaname, relname, n_live_tup,n_dead_tup, last_autoanalyze, last_analyze, last_autovacuum, last_vacuum,autovacuum_count+vacuum_count vacuum_count, analyze_count+autoanalyze_count analyze_count
FROM pg_stat_user_tables
ORDER BY 5 DESC;

Use a visualização pg_stat_activity para encontrar dados relacionados às atividades atuais, como uma ID ou consulta de processo de back-end. Para encontrar consultas de longa duração, execute a seguinte consulta:

SELECT pid, datname, query, extract(epoch from now()) - extract(epoch from xact_start) AS duration, case
WHEN wait_event IS NULL THEN 'CPU'
ELSE wait_event_type||':'||wait_event end wait FROM pg_stat_activity
WHERE query!=current_query() AND xact_start IS NOT NULL ORDER BY 4 DESC;

As consultas que estão aguardando bloqueios podem ser lentas. Para verificar se uma consulta está aguardando bloqueios, execute a seguinte consulta:

SELECT pid, virtualtransaction AS vxid, locktype AS lock_type, mode AS lock_mode, granted,fastpath,CASE
WHEN virtualxid IS NOT NULL AND transactionid IS NOT NULL
THEN virtualxid || ' ' || transactionid
WHEN virtualxid::text IS NOT NULL
THEN virtualxid
ELSE transactionid::text
END AS xid_lock, relname, page, tuple, classid, objid, objsubid
FROM pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE -- do not show our view's locks
pid != pg_backend_pid() AND
virtualtransaction IS DISTINCT FROM virtualxid
ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7;

Use pg_stat_statements para visualizar as estatísticas das consultas. Antes de criar a extensão pg_stat_statements, adicione a entrada pg_stat_statements a shared_preload_libraries. Para criar a extensão pg_stat_statements no banco de dados, execute a seguinte consulta:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Observação: Você pode modificar parâmetros para pg_stats_statements somente quando um grupo de parâmetros personalizado está anexado à sua instância de banco de dados.

Para identificar as consultas SQL que afetam o desempenho da sua instância de banco de dados, execute as seguintes consultas.

PostgreSQL versões 12 e anteriores:

SELECT query, calls, total_time, mean_time FROM pg_stat_statements ORDER BY 3 DESC;

PostgreSQL versões 13 e posteriores:

SELECT query, calls, total_plan_time+total_exec_time AS total_time, mean_plan_time + mean_exec_time AS mean_time FROM pg_stat_statements ORDER BY 3 DESC;

Para encontrar consultas com uma taxa de acerto de cache de buffer menor, execute as consultas a seguir.

PostgreSQL versões 12 e anteriores:

SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percentFROM pg_stat_statements ORDER BY total_time
DESC LIMIT 10;

PostgreSQL versões 13 e posteriores:

SELECT query, calls, total_plan_time+total_exec_time as total_time, rows, 100.0 * shared_blks_hit / nullif(shared_blks_hit +shared_blks_read, 0) AS hit_percent
FROM pg_stat_statements ORDER BY 5 ASC
LIMIT 10;

Para encontrar consultas ou planos de consulta de longa duração nos logs de erros do banco de dados, configure o parâmetro log_min_duration_statement para sua instância de banco de dados e use o módulo auto_explain.

Você também pode usar os comandos explain e explain analyze para obter o plano de consulta. Use o módulo auto_explain ou os comandos explain para identificar como é possível ajustar seu ajuste de consulta. Para obter mais informações, consulte 14.1 Usando EXPLAIN e F3. auto_explain - planos de execução de logs de consultas lentas no site do PostgreSQL.

Se você otimizou seu sistema e ainda enfrenta problemas de desempenho, é uma prática recomendada aumentar a escala verticalmente da classe de instância de banco de dados. Ao aumentar verticalmente a escala da instância de banco de dados, você aloca mais recursos de computação e memória.

Informações relacionadas

Como posso solucionar problemas de alta utilização da CPU para o Amazon RDS ou o Amazon Aurora PostgreSQL?

Trabalhar com parâmetros na instância de banco de dados do RDS para PostgreSQL

Por que minha instância de banco de dados do Amazon RDS está usando memória de troca quando eu tenho memória suficiente?