ApsaraDB RDS:Troubleshoot high CPU utilization of ApsaraDB RDS for PostgreSQL

SELECT state, count(*)
FROM pg_stat_activity
WHERE state NOT LIKE '%idle'
GROUP BY state
ORDER BY count DESC;

Método 1: Usar a extensão pg_stat_statements

A extensão pg_stat_statements rastreia estatísticas acumuladas de execução para todas as instruções SQL. Como ela acumula dados ao longo do tempo, redefina os contadores antes de iniciar a solução de problemas para que os resultados reflitam apenas a atividade recente.

1. Se a extensão ainda não estiver instalada, crie-a e redefina os contadores:

   CREATE EXTENSION pg_stat_statements;
   SELECT pg_stat_reset();
   SELECT pg_stat_statements_reset();

2. Aguarde cerca de um minuto para que os contadores acumulem dados.

3. Encontre as consultas que consomem mais tempo total de execução:

   SELECT *
   FROM pg_stat_statements
   ORDER BY total_time DESC
   LIMIT 5;

4. Identifique as consultas que leem mais blocos de buffer compartilhado. Leituras elevadas de buffer geralmente indicam índices ausentes — o banco de dados varre muito mais dados do que o necessário:

   SELECT *
   FROM pg_stat_statements
   ORDER BY shared_blks_hit + shared_blks_read DESC
   LIMIT 5;

Para encontrar consultas lentas por chamada individual (em vez de no total), ordene também por mean_exec_time DESC. Isso destaca consultas com alto tempo médio de execução que podem não aparecer no topo da lista por tempo total.

Após identificar as consultas problemáticas, vá para a Etapa 3 para resolver o problema.

Método 2: Usar a visualização pg_stat_activity

A visualização pg_stat_activity exibe todas as sessões atuais do banco de dados e suas consultas. Execute a consulta a seguir para encontrar consultas em execução há um tempo anormalmente longo:

SELECT datname,
       usename,
       client_addr,
       application_name,
       state,
       backend_start,
       xact_start,
       xact_stay,
       query_start,
       query_stay,
       replace(query, chr(10), ' ') AS query
FROM (
    SELECT pgsa.datname AS datname,
           pgsa.usename AS usename,
           pgsa.client_addr AS client_addr,
           pgsa.application_name AS application_name,
           pgsa.state AS state,
           pgsa.backend_start AS backend_start,
           pgsa.xact_start AS xact_start,
           extract(epoch FROM (now() - pgsa.xact_start)) AS xact_stay,
           pgsa.query_start AS query_start,
           extract(epoch FROM (now() - pgsa.query_start)) AS query_stay,
           pgsa.query AS query
    FROM pg_stat_activity AS pgsa
    WHERE pgsa.state != 'idle'
      AND pgsa.state != 'idle in transaction'
      AND pgsa.state != 'idle in transaction (aborted)'
) idleconnections
ORDER BY query_stay DESC
LIMIT 5;

Após identificar as consultas problemáticas, vá para a Etapa 3 para resolver o problema.

Método 3: Encontrar tabelas com índices ausentes

Sem índices, o banco de dados realiza varreduras sequenciais e lê cada linha para encontrar resultados correspondentes. Por exemplo, se o banco de dados tem 8 GB de memória e 6 GB de dados frequentes residem na memória sem índices, as varreduras sequenciais processam grandes quantidades de dados irrelevantes e aumentam o uso da CPU. Em tabelas com mais de 100.000 linhas, uma única varredura sequencial pode consumir quase toda a CPU disponível, e o problema se agrava quando centenas de conexões executam essas varreduras simultaneamente.

1. Encontre as tabelas com mais varreduras sequenciais:

   SELECT *
   FROM pg_stat_user_tables
   WHERE n_live_tup > 100000
     AND seq_scan > 0
   ORDER BY seq_tup_read DESC
   LIMIT 10;

2. Verifique se há consultas lentas acessando essas tabelas no momento:

   SELECT *
   FROM pg_stat_activity
   WHERE query ILIKE '%<table name>%'
     AND now() - query_start > interval '10 seconds';

SELECT *
FROM pg_stat_statements
WHERE query ILIKE '%<table>%'
ORDER BY shared_blks_hit + shared_blks_read DESC
LIMIT 3;

Após identificar as consultas problemáticas, vá para a Etapa 3 para resolver o problema.

Encerrar consultas lentas

Depois de identificar as consultas problemáticas na Etapa 2, encerre-as para restaurar o serviço normal. O PostgreSQL fornece duas funções para essa finalidade:

• **pg_cancel_backend()**: Cancela uma consulta de forma controlada sem fechar a conexão.

• **pg_terminate_backend()**: Encerra forçosamente todo o processo de backend.

-- Gracefully cancel matching queries
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE query LIKE '%<query text>%'
  AND pid != pg_backend_pid();

-- Forcibly terminate matching backend processes
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE query LIKE '%<query text>%'
  AND pid != pg_backend_pid();

Otimizar consultas lentas

Se as consultas lentas forem necessárias para o seu negócio, otimize-as para evitar que o problema se repita.

1. Atualize as estatísticas da tabela. Execute ANALYZE ou VACUUM ANALYZE nas tabelas envolvidas nas consultas lentas. Estatísticas desatualizadas fazem o planejador de consultas escolher planos de execução subotimizados. Execute esses comandos fora dos horários de pico para minimizar o impacto.

   Nota

   Substitua [$Table] pelo nome da tabela relacionada às consultas lentas.

   ANALYZE [$Table];
   VACUUM ANALYZE [$Table];

2. Revise o plano de execução. Use EXPLAIN para examinar como o banco de dados executa uma consulta e identificar se ele realiza varreduras sequenciais em tabelas grandes que se beneficiariam de um índice.

   • Visualize o plano de execução sem executar a consulta:

     EXPLAIN [$Query_Text];

   • Execute a consulta e visualize estatísticas detalhadas de execução:

     EXPLAIN (BUFFERS TRUE, ANALYZE TRUE, VERBOSE TRUE) [$Query_Text];

   Nota

   Substitua [$Query_Text] pela consulta SQL que você deseja analisar.

   Se o plano de execução mostrar varreduras sequenciais em tabelas grandes, adicione índices nas colunas usadas nas cláusulas WHERE, condições JOIN ou cláusulas ORDER BY.

3. Reescreva consultas ineficientes. Remova subconsultas desnecessárias das consultas lentas. Para melhorar ainda mais o desempenho, considere reescrever operações UNION ALL e usar cláusulas JOIN para sequências de junção fixas.