Otimizar o alto uso da CPU nas instâncias

A alta utilização da CPU afeta negativamente o desempenho da sua instância. Qualquer atividade realizada na instância usa a CPU. Portanto, se houver uma notificação de alta utilização da CPU, primeiro identifique a causa raiz do problema, seja consultas mal escritas, transações de longa duração ou qualquer outra atividade do banco de dados.

Este documento descreve as maneiras de identificar gargalos de CPU em uma instância e mitigar os problemas de uso da CPU na instância.

Identificar os gargalos da CPU

As seções a seguir discutem diferentes cenários de CPU.

Usar os insights de consulta para identificar consultas com alto consumo de CPU

O Query Insights ajuda a detectar, diagnosticar e evitar problemas de desempenho relacionados aos bancos de dados do Cloud SQL.

Usar a extensão pg_proctab

Use a extensão pg_proctab com a combinação do utilitário pg_top para receber saídas do sistema operacional que fornecem informações de uso da CPU por processo.

Usar consultas

As seções a seguir discutem diferentes consultas que você pode usar.

Identificar as conexões ativas por estado

Cada conexão ativa com o banco de dados consome uma certa quantidade de CPU. Portanto, se a instância tiver um grande número de conexões, a utilização cumulativa poderá ser alta. Use a consulta a seguir para conferir as informações sobre o número de conexões por estado.

SELECT
  state,
  usename,
  count(1)
FROM
  pg_stat_activity
WHERE
  pid <> pg_backend_pid()
group by
  state,
  usename
order by
  1;

A saída será assim:


        state        |    usename    | count
---------------------+---------------+-------
 active              | ltest         |   318
 active              | sbtest        |    95
 active              |               |     2
 idle                | cloudsqladmin |     2
 idle in transaction | ltest         |    32
 idle in transaction | sbtest        |     5
                     | cloudsqladmin |     3
                     |               |     4
(8 rows)

Se a contagem de conexões ativas for alta, verifique se há consultas de execução longa ou eventos de espera que estão bloqueando a execução das consultas.

Se a contagem de conexões inativas for alta, execute a consulta a seguir para encerrar as conexões após receber as aprovações necessárias.

SELECT
  pg_terminate_backend(pid)
FROM
  pg_stat_activity
WHERE
  usename = 'sbtest'
  and pid <> pg_backend_pid()
  and state in ('idle');

Também é possível encerrar as conexões individualmente com pg_terminate_backend usando a seguinte consulta:

SELECT pg_terminate_backend (<pid>);

Aqui, você pode receber o PID de pg_stat_activity.

Identificar as conexões de longa duração

Confira um exemplo de consulta que retorna consultas de longa duração. Nesse caso, é possível identificar as consultas que estão ativas há mais de cinco minutos.

SELECT
  pid,
  query_start,
  xact_start,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM
  pg_stat_activity
WHERE
  (
    now() - pg_stat_activity.query_start
  ) > interval '5 minutes' order by 4 desc;

Analisar o plano de explicação para identificar consultas mal escritas

Use o EXPLAIN PLAN para analisar uma consulta mal escrita e reescrevê-la, se necessário. Se preferir, cancele a consulta de execução longa com o comando a seguir e as aprovações necessárias.

SELECT pg_cancel_backend(<pid>);

Monitorar a atividade do VACUUM

A atividade AUTOVACUUM que limpa as tuplas inativas é uma operação que exige muito da CPU. Se a instância usar a versão 11 ou mais recente do PostgreSQL, use a consulta a seguir para verificar se há alguma atividade AUTOVACUUM ou VACUUM ativa em andamento.

SELECT
  relid :: regclass,
  pid,
  phase,
  heap_blks_total,
  heap_blks_scanned,
  heap_blks_vacuumed,
  index_vacuum_count,
  max_dead_tuples,
  num_dead_tuples
FROM
  pg_stat_progress_vacuum;

Verifique se há uma atividade do VACUUM em andamento em uma instância usando a seguinte consulta:

SELECT
  pid,
  datname,
  usename,
  query
FROM
  pg_stat_activity
WHERE
  query like '%vacuum%';

Além disso, é possível otimizar e resolver problemas de operações VACUUM no PostgreSQL.

Adicionar a extensão pg_stat_statements

Configure a extensão pg_stat_statements para receber informações aprimoradas do dicionário sobre a atividade da instância.

Checkpoints frequentes

Pontos de verificação frequentes diminuem a performance. Ajuste o flag checkpoint_timeout se o registro de alerta do PostgreSQL informar o aviso checkpoint occurring too frequently.

Coletar estatísticas

Verifique se o planejador de consultas tem as estatísticas mais recentes sobre tabelas para escolher o melhor plano para consultas. A operação ANALYZE coleta estatísticas sobre o conteúdo de 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 consultas. O processo AUTOVACUUM será analisado automaticamente nas tabelas periodicamente. Por isso, execute o comando a seguir para verificar se todas as tabelas foram analisadas e se os metadados mais recentes estão disponíveis para o planejador.

SELECT
  relname,
  last_autovacuum,
  last_autoanalyze
FROM
  pg_stat_user_tables;

Configurações inadequadas do sistema

Há outros fatores e configurações de flag ou de sistema que influenciam o desempenho da consulta. Execute a consulta abaixo para verificar os eventos de espera e o tipo de evento de espera e receber insights sobre o desempenho de outras configurações do sistema.

SELECT
  datname,
  usename,
  (
    case when usename is not null then state else query end
  ) AS what,
  wait_event_type,
  wait_event,
  backend_type,
  count(*)
FROM
  pg_stat_activity
GROUP BY
  1,
  2,
  3,
  4,
  5,
  6
ORDER BY
  1,
  2,
  3,
  4 nulls first,
  5,
  6;

A saída é semelhante a:


 ..  | .. | what           | wait_event_type |      wait_event      | ..    | count
-..--+-..-+----------------+-----------------+----------------------+-..----+------
 ..
 ..  | .. | active         | IO              | CommitWaitFlush      | ..    |   750
 ..  | .. | idle           | IO              | CommitWaitFlush      | ..    |   360
 ..  | .. | active         | LWLock          | BufferMapping        | ..    |   191

Monitorar verificações sequenciais

Varreduras sequenciais frequentes em tabelas com mais de algumas dezenas de linhas geralmente indicam um índice ausente. Quando as verificações tocam milhares ou até centenas de milhares de linhas, elas podem causar uso excessivo da CPU.

Varreduras sequenciais frequentes em tabelas com centenas de milhares de linhas podem causar uso excessivo da CPU. Crie os índices necessários para evitar verificações sequenciais nessas tabelas.

Execute a consulta a seguir para verificar o número de vezes que as verificações sequenciais são iniciadas em qualquer tabela.

SELECT
  relname,
  idx_scan,
  seq_scan,
  n_live_tup
FROM
  pg_stat_user_tables
WHERE
  seq_scan > 0
ORDER BY
  n_live_tup desc;

Por fim, se a CPU ainda estiver alta e você achar que as consultas são tráfego legítimo, aumente os recursos da CPU na instância para evitar falha ou inatividade do banco de dados.

A seguir