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

10 minuto de leitura
0

Quero identificar e resolver a causa da alta utilização da CPU no Amazon Relational Database Service (Amazon RDS) ou na edição compatível com Amazon Aurora PostgreSQL.

Breve descrição

Se você perceber que sua carga tem alta utilização da CPU, use uma combinação das ferramentas a seguir para identificar a causa:

Resolução

Métricas do Amazon CloudWatch

Use as métricas do CloudWatch para identificar padrões da CPU por longos períodos. Compare os gráficos WriteIOPs, ReadIOPs, ReadThroughput e WriteThroughput com a utilização da CPU para descobrir os momentos em que a workload causou alta utilização da CPU.

Depois de identificar o intervalo de tempo, analise os dados do monitoramento avançado associados à sua instância de banco de dados. É possível configurar o monitoramento avançado para coletar dados em intervalos de 1, 5, 10, 15, 30 ou 60 segundos. Depois, você pode coletar dados em um nível mais granular do que com o CloudWatch.

Monitoramento avançado

O monitoramento avançado fornece uma visão no nível do sistema operacional (SO). Essa visualização pode ajudar a identificar a causa de uma carga elevada da CPU em um nível granular. Por exemplo, você pode analisar a média de carga, a distribuição da CPU (System% ou Nice%) e a lista de processos do sistema operacional.

Com o monitoramento avançado, é possível verificar os dados de loadAverageMinute em intervalos de 1, 5 e 15 minutos. Uma média de carga maior que o número de vCPUs indica que a instância está sob carga pesada. Se a média de carga for menor que o número de vCPUs para a classe de instância de banco de dados, o controle de utilização da CPU pode não causar a latência da aplicação. Ao diagnosticar a causa da utilização da CPU, verifique a média da carga para evitar falsos positivos.

Por exemplo, suponha que você tenha uma instância de banco de dados que usa uma classe de instância db.m5.2xlarge com 3.000 IOPS provisionadas que atinge o limite da CPU. No exemplo a seguir, a classe de instância tem oito vCPUs associadas a ela. Para a mesma média de carga, exceder 170 indica que a máquina está sob carga pesada durante o período de tempo medido.

Carga média por minuto

Quinze170,25
Cinco391,31
Um596,74

Utilização da CPU

Usuário (%)0,71
Sistema (%)4,9
Nice (%)93,92
Total (%)99,97

Observação: no monitoramento avançado, Nice% representa a quantidade de CPU que sua workload usa no banco de dados.

Depois de ativar o monitoramento avançado, você também pode verificar a lista de processos do sistema operacional associada à instância de banco de dados. O monitoramento avançado mostra no máximo 100 processos e pode ajudá-lo a identificar os processos com maior impacto no desempenho. Você pode combinar os resultados do monitoramento avançado com os resultados de pg_stat_activity para ajudar a identificar o uso de recursos das consultas.

Insights de Performance

Use o Insights de Performance do Amazon RDS para identificar a consulta responsável pela carga do banco de dados. Verifique a guia SQL que corresponde a um intervalo de tempo específico.

Visualização e catálogos nativos do PostgreSQL

No nível do mecanismo do banco de dados, você pode usar pg_stat_activity e pg_stat_statements. Se o problema ocorrer em tempo real, use pg_stat_activity ou pg_stat_statements para agrupar as máquinas, clientes e endereços IP que enviam mais tráfego. Use esses dados para verificar aumentos ao longo do tempo ou aumentos nos servidores de aplicações. Você também pode verificar se um servidor de aplicações tem sessões travadas ou problemas de bloqueio. Para obter mais informações, consulte pg_stat_activity e pg_stat_statements no site do PostgreSQL.

Para ativar pg_stat_statements, modifique o grupo de parâmetros personalizados existente e defina os valores a seguir:

  • Adicione pg_stat_statements a shared_preload_libraries
  • track_activity_query_size = 4096
  • pg_stat_statements.track = TODOS
  • pg_stat_statements.max = 10000

Selecione Aplicar imediatamente e, em seguida, reinicialize a instância de banco de dados. Depois, execute um comando semelhante ao seguinte no banco de dados que você deseja monitorar:

demo=> select current_database();current_database
------------------
demo
(1 row)     

demo=> CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Observação: o comando anterior instala a extensão no banco de dados de demonstração.

Depois que pg_stat_statements estiver configurado, use um dos métodos a seguir para monitorar a saída.

Para listar as consultas por total_time e ver qual consulta passa mais tempo no banco de dados, execute uma das consultas a seguir:

PostgreSQL versões 12 e anteriores

SELECT total_time, query
FROM pg_stat_statements
ORDER BY total_time DESC LIMIT 10;

PostgreSQL versões 13 e posteriores

SELECT total_plan_time+total_exec_time as total_time, query
FROM pg_stat_statements
ORDER BY 1 DESC LIMIT 10;

Execute uma das consultas a seguir para listar consultas com menor taxa de acertos do cache do buffer:

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_percent
FROM 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 3 DESC LIMIT 10;

Execute a consulta a seguir para listar consultas por execução para exemplos de consultas ao longo do tempo:

PostgreSQL versões 12 e anteriores

SELECT query, calls, total_time/calls as avg_time_ms, rows/calls as avg_rows,temp_blks_read/calls as avg_tmp_read, temp_blks_written/calls as avg_temp_written
FROM pg_stat_statements
WHERE calls != 0
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)/calls as avg_time_ms,
 rows/calls as avg_rows,
temp_blks_read/calls as avg_tmp_read,
 temp_blks_written/calls as avg_temp_written
FROM pg_stat_statements
WHERE calls != 0
ORDER BY 3 DESC LIMIT 10;

Conexões inativas no banco de dados

As conexões inativas no banco de dados podem consumir recursos computacionais, como memória e CPU. Quando sua instância tiver alta utilização da CPU, verifique se há conexões inativas no banco de dados. Para mais informações, consulte Performance impact of idle PostgreSQL connections. Para verificar se há conexões inativas, use o monitoramento avançado para analisar a lista de processos do sistema operacional. Entretanto, essa lista exibe no máximo 100 processos.

Execute as consultas a seguir no nível do banco de dados para verificar se há conexões inativas.

Execute as consultas a seguir para visualizar as sessões atuais que estão inativas e ativas:

SELECT pid, datname, state, current_timestamp-least(query_start,xact_start) age, application_name, usename, queryFROM pg_stat_activity
WHERE query != '<IDLE>
'AND query NOT ILIKE '%pg_stat_activity%'
AND usename!='rdsadmin'
ORDER BY query_start desc;

SELECT application_name,pid,wait_event_type,wait_event,current_timestamp-least(query_start,xact_start) AS runtime, query AS current_query
FROM pg_stat_activity
WHERE not pid=pg_backend_pid()
AND query NOT ILIKE '%pg_stat_activity%'
AND usename!='rdsadmin';

Execute as consultas a seguir para obter as contagens de conexões por nome de usuário e aplicação:

postgres=> SELECT application_name,count(*) FROM pg_stat_activity GROUP BY application_name;
    application_name    | count
------------------------+-------
 psql                   |     1
 PostgreSQL JDBC Driver |     1
                        |     5
(3 rows)

postgres=> SELECT usename,count(*) FROM pg_stat_activity GROUP BY usename;
 usename  | count
----------+-------
 master   |     4
 user1    |     1
 rdsadmin |     2
(3 rows)

Depois de identificar as conexões inativas, execute uma das seguintes consultas para encerrar as conexões:

psql=> SELECT pg_terminate_backend(pid)
   FROM pg_stat_activity
   WHERE usename = 'example-username'
   AND pid <> pg_backend_pid()
   AND state in ('idle');

-ou-

SELECT pg\_terminate\_backend (example-pid);

Se sua aplicação causar muitas conexões, altere-a para que os recursos de memória e CPU não sejam gastos gerenciando essas conexões. Você pode alterar a aplicação para limitar o número de conexões ou usar um pooler de conexões, como o PGBouncer. Também é possível usar o Amazon RDS Proxy, um serviço gerenciado que permite configurar o pooling de conexões.

Comando ANALYZE

O comando ANALYZE coleta estatísticas sobre o conteúdo das tabelas no banco de dados e armazena os resultados no catálogo do sistema pg_statistic. Em seguida, o planejador de consultas usa essas estatísticas para ajudar a determinar os planos de execução mais eficientes para as consultas. Se você não executar ANALYZE com frequência nas tabelas do seu banco de dados, as consultas poderão consumir mais recursos computacionais. As consultas consomem mais recursos devido às estatísticas obsoletas presentes no sistema para as relações que você acessa. Esses problemas ocorrem nas seguintes condições:

  • O autovacuum não está sendo executado com frequência.
  • O ANALYZE não foi executado após a atualização da versão principal.

O autovacuum não está sendo executado com frequência: o Autovacuum é um daemon que automatiza a execução dos comandos VACUUM e ANALYZE. O autovacuum verifica se há tabelas inchadas no banco de dados e recupera o espaço para reutilização. O daemon autovacuum garante que as estatísticas da tabela sejam atualizadas regularmente executando a operação ANALYZE sempre que o valor limite definido de tuplas estiver inoperante. Em seguida, o planejador de consultas pode usar o plano de consulta mais eficiente com base nas estatísticas recentes. Se o autovacuum não estiver sendo executado, o planejador de consultas poderá criar planos de consulta abaixo do ideal e levar a um maior consumo de recursos pelas consultas. Consulte os recursos a seguir para obter mais informações:

Para informações sobre quando o autovacuum e o autoanalyze foram executados pela última vez nas tabelas, execute a seguinte consulta:

SELECT relname, last\_autovacuum, last\_autoanalyze FROM pg\_stat\_user\_tables;

O ANALYZE não foi executado após a atualização da versão principal: os bancos de dados do PostgreSQL geralmente encontram problemas de desempenho após qualquer atualização da versão principal do mecanismo. Uma causa comum para esses problemas é a operação ANALYZE não ser executada após a atualização para atualizar a tabela pg_statistic. Execute a operação ANALYZE para cada banco de dados em sua instância de banco de dados do RDS para PostgreSQL. As estatísticas do otimizador não são transferidas durante uma atualização da versão principal. Portanto, para evitar problemas de desempenho devido à maior utilização de recursos, regenere todas as estatísticas.

Para gerar estatísticas para todas as tabelas regulares no banco de dados atual após uma atualização da versão principal, execute o seguinte comando sem nenhum parâmetro:

ANALYZE VERBOSE

Parâmetros de registro do PostgreSQL

Use o Amazon RDS para PostgreSQL para ativar o registro de consultas em log. Em seguida, verifique os logs de erros do PostgreSQL para confirmar se os parâmetros log_min_duration_statement e log_statement estão definidos com os valores apropriados. Para mais informações, consulte Error reporting and logging no site do PostgreSQL.

Diminua o uso da CPU

Depois de identificar as consultas que causam a alta utilização da CPU, use os métodos a seguir para reduzir ainda mais a utilização da CPU:

  • Para encontrar oportunidades de ajuste, use EXPLAIN e EXPLAIN ANALYZE para identificar os sinais de alerta. Para obter mais informações, consulte EXPLAIN no site do PostgreSQL.
  • Se houver uma consulta em execução repetida, use declarações preparadas para diminuir a pressão sobre a CPU. A execução repetida de declarações preparadas armazena o plano de consulta em cache. Como o plano já está armazenado em cache para execuções futuras, o tempo de planejamento é muito menor.

Informações relacionadas

Práticas recomendadas para trabalhar com PostgreSQL

AWS OFICIAL
AWS OFICIALAtualizada há 3 meses