Como resolvo a causa da alta utilização da CPU nas instâncias do Amazon RDS para PostgreSQL ou da edição compatível com Amazon Aurora PostgreSQL?

Resolução

Para determinar a causa da alta utilização da CPU, realize as seguintes ações. Em seguida, reduza a utilização da CPU da sua instância de banco de dados (DB).

Analise as métricas da instância de banco de dados

Para identificar quando sua worload causa alta utilização da CPU,use o Amazon CloudWatch para comparar as métricas de WriteIOPs, ReadIOPs, ReadThroughput e WriteThroughput com a métrica de CPUUtilization. Para o Aurora compatível com PostgreSQL, compare também a métrica BufferCacheHitRatio. Se os valores da métrica forem tão altos quanto a métrica de CPUUtilization, sua carga de trabalho poderá causar a alta utilização de CPU.

Use monitoramento avançado

Use o monitoramento avançado para analisar o sistema operacional (SO) da sua instância de banco de dados. Para coletar dados detalhados, defina a propriedade Granularity para um intervalo de 1, 5, 10, 15, 30 ou 60 segundos.

Para resolver a causa da alta utilização da CPU, verifique a métrica de sistema operacional LoadAverageMinute. Se a carga média for maior que o número de vCPUs, indica que a instância tem alta utilização. Se a carga média 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.

Também é possível verificar a lista de processos do sistema operacional da instância de banco de dados. O monitoramento avançado pode identificar no máximo 100 processos que afetam o desempenho de sua instância. Para identificar a utilização do recursos de consultas, use os resultados do monitoramento avançado para executar uma consulta pg_stat_activity do PostgreSQL.

Use o CloudWatch Database Insights

Ative o Amazon CloudWatch Database Insights para identificar a consulta responsável pela carga do banco de dados. Em seguida, verifique a guia Top SQL no gráfico de carga do banco de dados para ver o tempo específico em que a utilização da CPU aumenta.

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

Para problemas em tempo real, ative as declarações pg_stat_activity ou pg_stat_statements para agrupar máquinas, clientes e endereços IP que enviam mais tráfego. Para obter mais informações, consulte pg_stat_activity e pg_stat_statements no site do PostgreSQL.

Ativar pg_stat_statements

Conclua as etapas a seguir:

1. Modifique os seguintes valores do seu grupo de parâmetros de banco de dados personalizados:
   Em seguida, adicione pg_stat_statements a shared_preload_libraries.
   Defina track_activity_query_size para 4096.
   Defina pg_stat_statements.track para ALL.
   Defina pg_stat_statements.max para 10000.
2. E então, selecione Aplicar Imediatamente e reinicialize a instância de banco de dados logo em seguida.

Selecione o banco de dados a ser monitorado

Execute a seguinte consulta:

select current_database();

Instale a extensão no banco de dados atual

Execute o seguinte comando:

CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

Visualize as consultas que passam a maior parte do tempo no banco de dados

Execute a seguinte consulta para sua versão do PostgreSQL.

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;

Liste as consultas com uma taxa de acerto de cache de buffer menor.

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

Exemplos de consultas ao longo do tempo

Execute a seguinte consulta para 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_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;

Cheque por conexões inativas no banco de dados

Se você tiver conexões ociosas no banco de dados, a instância de banco de dados poderá fazer alta utilização da CPU. Para resolver esse problema, verifique e encerre as conexões ociosas. Para mais informações, consulte Performance impact of idle PostgreSQL connections (Impacto no desempenho por conexões inativas do PostgreSQL).

Para verificar as sessões que estão ociosas por mais de 10 minutos, execute a seguinte consulta:

SELECT * FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled','active')
AND state_change < current_timestamp - INTERVAL '10' MINUTE
AND usename != 'rdsadmin';

Para encerrar somente as sessões que estão em estado ocioso por mais de 10 minutos, execute a seguinte consulta:

SELECT pg_terminate_backend(pid) FROM pg_stat_activity
WHERE pid <> pg_backend_pid()
AND state in ('idle', 'idle in transaction', 'idle in transaction (aborted)', 'disabled')
AND state_change < current_timestamp - INTERVAL '10' MINUTE
AND usename != 'rdsadmin';

Para encerrar todas as conexões ociosas, execute uma das seguintes consultas:

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

Observação: substitua example-username pelo seu nome de usuário.

-ou-

SELECT pg_terminate_backend (example-pid);

Observação: substitua example-pid pelo PID da sua consulta.

Se seu aplicativo criar muitas conexões de banco de dados, reduza o número de conexões. Ou use um pooler de conexão como o PgBouncer. Para obter mais informações, consulte PgBouncer no site do PgBouncer. Também é possível utilizar o Amazon RDS Proxy para configurar grupos de conexão.

Verifique se há bloqueios de banco de dados

Se os bloqueios de seu banco de dados fizerem com que as consultas se acumulem e demorem mais para serem executadas, poderá haver um aumento na utilização da CPU na sua instância de banco de dados. Para resolver problemas relacionados a bloqueios, verifique o Database Insights em busca de eventos em espera, como Lock:Relation, Lock:tuple, Lock:transactionid ou outros eventos relacionados a bloqueios.

Para identificar o que bloqueou sua consulta, consulte Como identifico o que bloqueou uma consulta na minha instância de banco de dados compatível com PostgreSQL do Aurora ou do Amazon RDS para PostgreSQL?

Se as sessões de bloqueio não estiverem ATIVAS, use o Database Insights para identificar consultas bloqueadas. Para resolver esse problema, encerre todas as sessões bloqueadas.

Execute o comando ANALYZE

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. Para mais informações, consulte ANALYZE no site do PostgreSQL.

O autovacuum remove o espaço não utilizado das tabelas e recupera espaço em seu banco de dados. O daemon de autovacuum também executa o comando ANALYZE para atualizar regularmente as estatísticas da tabela quando você atinge o limite das versões anteriores que você configurou.

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

Para evitar problemas de desempenho após uma atualização importante da versão do mecanismo, execute o comando ANALYZE para atualizar a tabela pg_statistic de cada banco de dados em sua instância de banco de dados.

Para evitar problemas de desempenho devido ao uso mais intenso de recursos, execute o seguinte comando sem parâmetros para regenerar todas as estatísticas:

ANALYZE VERBOSE;

Verifique os logs de erros do PostgreSQL

Ative o registro em log de consultas no Amazon RDS para PostgreSQL. Em seguida, verifique os logs de erro do PostgreSQL para confirmar se você configurou os parâmetros log_min_duration_statement e log_statement. Para mais informações, consulte Error reporting and logging (Relatórios e registro de erros) no site do PostgreSQL.

Reduza o uso da CPU

Para reduzir o uso da CPU, realize as seguintes ações:

• Utilize EXPLAIN e EXPLAIN ANALYZE para identificar as melhores formas de ajustar os planos de consulta. Para obter mais informações, consulte Using EXPLAIN (Como usar EXPLAIN) no site do PostgreSQL.
• Se você identificar sessões de bloqueio que não são necessárias, use o PID da sessão de bloqueio para encerrá-la.
• Se houver uma consulta que você executa repetidamente, use instruções preparadas para diminuir o uso da CPU. Para mais informações, consulte PREPARE no site do PostgreSQL.

Informações relacionadas

Práticas recomendadas para trabalhar com PostgreSQL

Monitoramento do sistema operacional

Understanding autovacuum in Amazon RDS for PostgreSQL environments (Entender o autovacuum em ambientes Amazon RDS para PostgreSQL)

A case study of tuning autovacuum in Amazon RDS for PostgreSQL (Um estudo de caso sobre o ajuste do autovacuum no Amazon RDS para PostgreSQL)