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

Breve descrição

Se quiser determinar o que está causando o alto uso da CPU, utilize as seguintes ferramentas:

• Métricas do Amazon CloudWatch
• Métricas do monitoramento avançado
• Métricas de Insights de Performance
• Visualização e catálogos nativos do PostgreSQL, como pg_stat_statements, pg_stat_activity e pg_stat_user_tables
• Conexões inativas no banco de dados
• Comando ANALYZE no site do PostgreSQL
• Os parâmetros de log do PostgreSQL registram consultas de longa duração, autovacuum, esperas de bloqueio, como também solicitações de conexão e desconexão

Resolução

Analise as métricas do CloudWatch

Utilize as métricas do CloudWatch para ajudar a identificar os padrões da CPU durante longos períodos. Compare os grafos WriteIOPs, ReadIOPs, ReadThroughput e WriteThroughput com a utilização da CPU, a fim de descobrir os momentos em que o workload causa a alta utilização da CPU.

Após identificar o intervalo de tempo, analise os dados do Monitoramento Avançado associados à sua instância de banco de dados. É possível definir o Monitoramento Avançado em intervalos de coleta de 1, 5, 10, 15, 30 ou 60 segundos para coletar dados em um nível mais granular.

Utilizar Monitoramento Avançado

O Monitoramento Avançado fornecerá uma visão em nível do sistema operacional (SO). Por exemplo, é possível analisar o workload médio, a distribuição da CPU (System% ou Nice%) e a lista de processos do sistema operacional. Para obter mais informações, consulte Monitoramento do Sistema Operacional.

É possível verificar os dados loadAverageMinute em intervalos de 1, 5 e 15 minutos. Quando a média de carga for maior que o número de vCPUs, a instância estará enfrentando uma 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, é bem provável que o controle de utilização da CPU não cause a latência da aplicação. Quando estiver solucionando o que causa do alto uso da CPU, verifique a média de carga, assim evitará falsos positivos.

Por exemplo, suponha que tenhamos uma instância de banco de dados utilizando uma classe de instância db.m5.2xlarge com 3000 de IOPS provisionadas, e que esteja atingindo a cota da CPU. Oito vCPUs estarão associadas à classe da instância. Se a mesma média de carga exceder 170, isso indicará que a máquina está enfrentando uma carga pesada durante o período medido.

Média de Carga por Minuto:

• Quinze: 170,25
• Cinco: 391,31
• Um: 596,74

Utilização da CPU:

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

Observação: no Monitoramento Avançado, Nice% representa a quantidade de CPU que seu workload está utilizando no banco de dados.

Após ativar o Monitoramento Avançado, também é possível verificar a lista de processos do sistema operacional associada à instância de banco de dados. O Monitoramento Avançado ajudará a identificar no máximo 100 processos que estão afetando o desempenho. É possível combinar os resultados do Monitoramento Avançado com os resultados da pg_stat_activity para ajudar a identificar o consumo de recursos causado pelas consultas.

Utilizar Insights de Performance

Utilize o Insights de Performance do Amazon RDS a fim de identificar qual é a consulta responsável pela carga do banco de dados. Verifique a guia SQL que corresponde a um período de tempo específico.

Também, verifique visualizações e catálogos nativos do PostgreSQL

No nível do mecanismo do banco de dados, é possível utilizar o pg_stat_activity e também o pg_stat_statements. Se o problema ocorrer em tempo real, utilize então o pg_stat_activity ou pg_stat_statements para agrupar as máquinas, clientes e endereços IP que enviam mais tráfego.

Em seguida, utilize esses dados para verificar aumentos ao longo do tempo ou aumentos nos servidores de aplicações. Também é possível verificar se um servidor de aplicações tem ou não, 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, conclua as seguintes etapas:

1. Primeiro, modifique o grupo personalizado de parâmetros de banco de dados existente.

2. Em seguida, adicione pg_stat_statements a shared_preload_libraries.

3. Defina track_activity_query_size para 4096.

4. Defina pg\ _stat_statements.track para ALL.

5. Defina pg_stat_statements.max para 10000.

6. E então, selecione Aplicar Imediatamente e reinicialize a instância de banco de dados logo em seguida.

7. No banco de dados que deseja monitorar, execute o seguinte comando:

   demo=> select current_database();current_database------------------  
   demo  
   (1 row)  
   
   demo=> CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

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

Depois que pg_stat_statements estiver configurado, utilize um dos seguintes métodos para monitorar a saída. É possível visualizar a consulta que passa mais tempo no banco de dados, a que tem a menor taxa de acertos do cache do buffer ou a que é feita por execução.

Para ver quais consultas passam mais tempo no banco de dados, execute a seguinte consulta compatível com a sua versão do PostgreSQL.

PostgreSQL versões 12 e anteriores:

SELECT total_time, queryFROM 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, queryFROM pg_stat_statements  
ORDER BY 1 DESC LIMIT 10;

Para listar consultas com menor taxa de acertos do cache do buffer, execute a seguinte consulta para sua versão do PostgreSQL.

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_percentFROM pg_stat_statements   
ORDER BY 3 DESC LIMIT 10;

Para listar as consultas com base em cada execução, a fim de testá-las ao longo do tempo, execute a seguinte consulta compatível com a sua versão do PostgreSQL.

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_writtenFROM 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;

Cheque por conexões inativas no banco de dados

As conexões inativas presentes no banco de dados poderão consumir recursos de computação, como memória e CPU. Quando a instância tiver alta utilização da CPU, verifique se há conexões inativas no banco de dados. Para mais informações, consulte Impacto no desempenho por conexões inativas do PostgreSQL.

A fim de verificar se há conexões inativas, utilize o Monitoramento Avançado para analisar a lista de processos do sistema operacional. No entanto, a lista mostrará apenas um máximo de 100 processos. Execute as seguintes consultas no nível do banco de dados para verificar se há conexões inativas.

Veja 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_activityWHERE 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';

Obtenha as contagens de conexões para cada 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)

Após 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 a 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. É possível limitar o número de conexões ou utilizar um grupo de conexões, como o PgBouncer. Também é possível utilizar o Amazon RDS Proxy para configurar grupos de conexão.

Execute o comando ANALYZE

O comando ANALYZE coletará estatísticas sobre o conteúdo das tabelas no banco de dados e armazenará os resultados no catálogo do sistema pg_statistic. Em seguida, o planejador de consultas utilizará essas estatísticas para ajudar a determinar os planos de execução mais eficientes para as consultas. Se não executar o ANALYZE com frequência nas tabelas do banco de dados, as consultas poderão utilizar mais recursos de computação, devido às estatísticas obsoletas do sistema.

Estatísticas obsoletas ocorrem pelos seguintes motivos:

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

O autovacuum verificará se há tabelas inchadas no banco de dados e recuperará o espaço para reutilização. A fim de garantir que as estatísticas da tabela sejam atualizadas regularmente, o daemon autovacuum executará o comando ANALYZE quando o limite definido de tuplas estiver obsoleto.

Consulte os seguintes recursos para obter mais informações:

• Entender o autovacuum em ambientes Amazon RDS para PostgreSQL
• Um estudo de caso sobre o ajuste do autovacuum no Amazon RDS para PostgreSQL

Para saber 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;

Se quiser prevenir problemas de desempenho após uma atualização importante da versão do mecanismo, execute o comando ANALYZE para atualizar a tabela pg\ _statistic. Execute o comando ANALYZE para cada instância de banco de dados do RDS para PostgreSQL.

Portanto, se quiser prevenir problemas de desempenho devido à maior utilização de recursos, regenere todas as estatísticas. A fim de gerar estatísticas para todas as tabelas regulares no banco de dados atual após uma atualização importante da versão, execute o seguinte comando sem nenhum parâmetro:

ANALYZE VERBOSE

Verifique os logs de erros do PostgreSQL

Utilize o Amazon RDS para ativar o log de consultas para o PostgreSQL. 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 Relatórios e registro de erros no site do PostgreSQL.

Reduzir o uso da CPU

Após identificar as consultas que causam a alta utilização da CPU, utilize os métodos a seguir para reduzir ainda mais a utilização:

• Utilize EXPLAIN e EXPLAIN ANALYZE para identificar as melhores formas de ajustar os planos de consulta. Para obter mais informações, consulte Using EXPLAIN no site do PostgreSQL.
• Se houver uma consulta em execução repetida, utilize declarações preparadas para diminuir a pressão sobre a CPU. Instruções preparadas que são executadas repetidamente armazenarão em cache o plano de consulta. Quando o plano já estiver em cache para futuras execuções, haverá menos tempo para planejar a consulta.

Informações relacionadas

Práticas recomendadas para trabalhar com PostgreSQL