Como identifico e soluciono problemas de performance e consultas com execução lenta na minha instância do RDS for PostgreSQL ou do Aurora PostgreSQL?

Última atualização: 02/12/2022

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

Resolução

A performance da sua instância do Amazon RDS para PostgreSQL pode ser afetada por vários motivos, como:

  • Hardware subdimensionado
  • Mudanças de workload
  • Aumento do tráfego
  • Problemas de memória
  • Planos de consulta abaixo do ideal

Identificar a causa

Use uma combinação dessas ferramentas para identificar a causa das consultas com execução lenta:

  • Métricas do Amazon CloudWatch
  • Métricas do Enhanced Monitoring
  • Métricas do Performance Insights
  • Estatísticas do banco de dados
  • Ferramentas de banco de dados nativas

Métricas do CloudWatch

Para identificar gargalos de performance causados por recursos insuficientes, monitore essas métricas comuns do CloudWatch disponíveis para sua instância de banco de dados do Amazon RDS.

  • Utilização da CPU: porcentagem utilizada da capacidade de processamento do computador
  • FreeableMemory: RAM disponível na instância de banco de dados (em megabytes)
  • SwapUsage: espaço de troca usado pela instância de banco de dados (em megabytes)

Uma porcentagem maior de utilização da CPU geralmente indica uma workload ativa na instância e a necessidade de mais recursos de CPU. Uma utilização maior de memória, junto com o consumo de espaço de troca, indica troca frequente devido à baixa disponibilidade de memória para a workload. Isso pode significar que sua instância não consegue acompanhar a workload. A alta utilização de recursos de CPU e memória geralmente é causada por consultas de longa duração, aumento repentino do tráfego ou um grande número de conexões ociosas.

Execute este comando para visualizar as consultas ativas com o tempo de execução:

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

Execute este comando para encontrar o número de conexões ociosas presentes no banco de dados:

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

Em seguida, execute este comando para encerrar conexões ociosas que consomem recursos:

Observação: substitua example-pid pelo PID da conexão ociosa que você obteve com pg_stat_activity:

SELECT pg_terminate_backend(example-pid);

Verifique se o throughput de rede desejado foi atingido na instância do RDS revisando estas métricas:

  • NetworkReceiveThroughput
  • NetworkTransmitThroughput

Essas métricas indicam o tráfego de rede de entrada e saída em bytes/segundo. Classes de instância subdimensionadas ou não otimizadas pelo Amazon Elastic Block Service (Amazon EBS) podem afetar o throughput da rede, resultando em lentidão das instâncias. Um baixo throughput de rede pode resultar em respostas lentas para todas as solicitações de aplicações, independentemente da performance do banco de dados.

Avalie a performance de E/S revisando estas métricas:

  • ReadIOPS e WriteIOPS: a média de operações de leitura ou gravação de disco por segundo
  • ReadLatency e WriteLatency: tempo médio necessário para uma operação de leitura ou gravação em milissegundos
  • ReadThroughput e WriteThroughput: a média de megabytes lidos ou gravados em disco por segundo
  • DiskQueueDepth: número de operações de E/S aguardando para serem gravadas ou lidas no disco

Para obter mais informações, consulte Como soluciono problemas de latência de volumes do Amazon EBS causados por uma limitação de IOPS na minha instância do Amazon RDS?

Métricas do Enhanced Monitoring

Com o Enhanced Monitoring, é possível visualizar métricas no nível do sistema operacional e a lista dos 100 principais processos que consomem alta CPU e memória. Você pode ativar o Enhanced Monitoring por segundo para identificar problemas de performance intermitentes em sua instância do RDS.

É possível avaliar as métricas disponíveis do sistema operacional para diagnosticar problemas de performance que possam estar relacionados a CPU, workload, E/S, memória e rede.

Na lista de processos, identifique o processo com valores altos para CPU% ou Mem%. Em seguida, localize a conexão relacionada no banco de dados.

Por exemplo:

NAME VIRT RES CPU% MEM% VMLIMIT
postgres: postgres postgres 178.2.0.44(42303) SELECT [10322] 250,66 MB 27,7 MB 85,93 2,21 ilimitado

Conecte-se ao banco de dados e execute esta consulta para localizar a conexão e as informações relacionadas à consulta:

SELECT * FROM pg_stat_activity WHERE pid = 10322;

Métricas do Performance Insights

O Performance Insights permite avaliar workloads de banco de dados divididas por esperas, SQL, host ou usuários. Você também pode capturar as métricas de nível de banco de dados e do SQL.

A guia Top SQL no painel do Performance Insights mostra as instruções SQL que mais contribuem para a carga do banco de dados. Uma carga de banco de dados ou carga por esperas (AAS) superior ao valor máximo de vCPU indica workload limitada na classe da instância.

A latência média por chamada em SQL statistics (Estatísticas de SQL) fornece o tempo médio de execução de uma consulta. É comum ver outro SQL como o principal contribuidor da carga de banco de dados em vez do SQL com o maior tempo médio de execução. Isso ocorre porque a lista de SQL principal é baseada no tempo total de execução.

Estatísticas do banco de dados

As estatísticas a seguir podem ajudar a avaliar a performance do banco de dados no PostgreSQL:

  • Estatísticas de distribuição de dados
  • Estatísticas estendidas
  • Estatísticas de monitoramento

Para obter informações sobre como ler e entender essas estatísticas, consulte Understanding statistics in PostgreSQL (Noções básicas sobre estatísticas no PostgreSQL).

Ferramentas de banco de dados nativas

Para identificar consultas lentas, use a ferramenta nativa pgBadger. Para mais informações, consulte Optimizing and tuning queries in Amazon RDS for PostgreSQL based on native and external tools (Otimização e ajuste de consultas no Amazon RDS para PostgreSQL com ferramentas nativas e externas).

Otimize a performance

Ajustar configurações de memória

O servidor de banco de dados PostgreSQL aloca determinada área de memória durante toda a sua vida útil para armazenar dados em cache a fim de melhorar os acessos de leitura e gravação. Essa área de memória é chamada de buffers compartilhados. A quantidade de memória que o banco de dados usa para buffers de memória compartilhada é controlada pelos parâmetros shared_buffers.

Além da área de memória compartilhada, cada processo de back-end consome memória para realizar as operações em um servidor de banco de dados. A quantidade de memória usada é baseada nos valores definidos para os parâmetros work_mem e maintenance_work_mem. Para obter mais informações, consulte a documentação do PostgreSQL para configuração do servidor.

Se você observar continuamente uma alta pressão de memória na instância de banco de dados, considere reduzir os valores desses parâmetros. Você pode reduzir os valores desses parâmetros no grupo de parâmetros personalizados anexado à sua instância de banco de dados.

Gerenciamento do plano de consulta do Aurora PostgreSQL

Use o gerenciamento do plano de consulta do Amazon Aurora Edição compatível com PostgreSQL para controlar como e quando os planos de execução de consultas mudam. Para obter mais informações, consulte Best practices for Aurora PostgreSQL-Compatible query plan management (Práticas recomendadas para gerenciamento de planos de consulta do Aurora compatível com PostgreSQL).

Solucionar problemas de consultas com execução lenta

Normalmente, enfrenta-se consultas com execução lenta quando há problemas de infraestrutura ou quando o consumo geral de recursos é alto. As consultas com execução lenta também podem ser resultado de um planejamento de consulta abaixo do ideal pelo planejador de consultas. O planejador de consultas do PostgreSQL usa estatísticas criadas para a tabela para criar os planos de consulta. Esses planos podem ser afetados por alterações de esquema e estatísticas obsoletas. A sobrecarga na tabela e nos índices também pode resultar em consultas com execução lenta.

O daemon autovacuum é responsável por criar processos de trabalho de autovacuum que removem as tuplas inativas das tabelas sempre que o limite de tuplas inativas é atingido. O daemon autovaucum também é responsável por executar a operação ANALYZE que atualiza as estatísticas armazenadas para uma tabela específica.

Execute a consulta a seguir para encontrar informações sobre:

  • Tuplas inativas
  • Número de operações de autovacuum ou vacuum
  • Número de execuções de autoanalyze ou analyze
  • Quando essas operações foram executadas pela última vez
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;

Utilize a visualização pg_stat_activity para localizar dados relacionados às atividades atuais. Essa visualização fornece o PID do backend, a consulta e outros detalhes. Para localizar consultas de longa duração, execute esta 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 ficar lentas. Portanto, execute esta consulta para verificar se a consulta está aguardando bloqueios:

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;

Sua instância do RDS para PostgreSQL permite criar a extensão pg_stat_statements de dentro do banco de dados:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Usando pg_stat_statements, é possível visualizar as estatísticas das consultas. Adicione a entrada pg_stat_statements a shared_preload_libraries antes de criar a extensão.

Observação: é possível modificar parâmetros para este módulo somente quando um grupo de parâmetros personalizado é anexado à instância de banco de dados.

Use essas consultas para identificar as principais consultas de SQL que afetam a performance da instância.

Para encontrar consultas que permanecem por mais tempo no banco de dados, execute esta consulta para o PostgreSQL 12 e versões anteriores:

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

Execute esta consulta para o PostgreSQL 13 e versões 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 acertos de cache de buffer menor, execute esta consulta para o PostgreSQL 12 e versões anteriores:

SELECT query, calls, 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 total_time
DESC LIMIT 10;

Execute esta consulta para o PostgreSQL 13 e versões 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 capturar consultas de longa duração ou planos de consulta em logs de erros do banco de dados, defina corretamente o parâmetro log_min_duration_statement para sua instância e, em seguida, use o módulo auto_explain. O parâmetro log_min_duration_statement faz com que a duração de cada instrução concluída seja registrada se a instrução tiver sido executada por, pelo menos, o período de tempo especificado. Por exemplo, se você definir esse parâmetro como 250 ms, todas as instruções de SQL executadas por 250 ms ou mais serão registradas. O módulo auto_explain permite capturar o plano de explicação das consultas que são executadas no banco de dados.

Também é possível capturar o plano usando os comandos explain e explain analyze. Identifique oportunidades de ajuste de consulta com base no módulo auto_explain ou nos comandos explain para a consulta. Para obter mais informações, consulte a documentação do PostgreSQL para saber sobre como usar o EXPLAIN.

Se seu sistema estiver bem ajustado e você ainda estiver enfrentando controles de utilização de recursos, considere ampliar a classe da instância. Amplie a classe da instância para alocar sua instância de banco de dados com mais recursos de computação e memória. Para obter mais informações, consulte Hardware specifications for DB instance classes (Especificações de hardware para classes de instância de banco de dados).


How can I troubleshoot high CPU utilization for Amazon RDS or Amazon Aurora PostgreSQL-Compatible Edition? (Como posso solucionar o problema de alto uso da CPU para o Amazon RDS ou o Amazon Aurora edição compatível com o PostgreSQL?)

Working with parameters on your RDS for PostgreSQL DB instance (Trabalhar com parâmetros na instância de banco de dados do RDS for PostgreSQL)

Why is my Amazon RDS DB instance using swap memory when I have sufficient memory? (Por que minha instância do Amazon RDS está usando a troca de memória se eu tenho memória suficiente?)

Este artigo ajudou?


Precisa de ajuda com faturamento ou suporte técnico?