O alto uso da CPU afeta negativamente o desempenho da instância. Qualquer atividade realizada na instância usa CPU. Portanto, se houver um aviso de alta utilização da CPU, você precisa primeiro identificar a causa raiz do problema, seja consultas mal escritas, transações de longa duração ou qualquer outra atividade no banco de dados.
Neste documento, descrevemos as maneiras de identificar os gargalos da CPU em uma instância e atenuar os problemas de uso da CPU na instância.
Identificar os gargalos da CPU
Usar 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 ter saídas do sistema operacional que fornecem informações de utilização da CPU por processo.
Usar consultas
Identificar as conexões ativas por estado
Cada conexão ativa com o banco de dados ocupa uma parte da CPU. Portanto, se a instância tiver um grande número de conexões, o uso cumulativo pode ser alto. Use a consulta a seguir para ver 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 longa duração ou eventos de espera que estão impedindo a execução das consultas.
Se a contagem de conexões inativas for alta, execute a consulta a seguir para encerrar as conexões, depois de 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 conseguir o PID de pg_stat_activity.
Identificar as conexões de longa duração
Veja o exemplo de uma 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;
Revisar 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. Outra opção é cancelar a consulta de longa duração com o comando a seguir e fazer as aprovações necessárias.
SELECT pg_cancel_backend(<pid>);
Monitorar a atividade VACUUM
A atividade AUTOVACUUM que limpa as tuplas inativas é uma operação que consome muita CPU. Se a instância usar o PostgreSQL versão 11 ou posterior, use a consulta a seguir para verificar se há alguma atividade ativa AUTOVACUUM ou VACUUM 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 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 extensão pg_stat_statements
Configure a extensão pg_stat_statements para receber informações do dicionário aprimoradas sobre a atividade da instância.
Checkpoints frequentes
Checkpoints frequentes degradam o desempenho. 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 as tabelas para escolher o melhor plano para as 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 as 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 do sistema inadequadas
Há outros fatores e configurações de flag ou de sistema que influenciam o desempenho da consulta. Execute a consulta a seguir para verificar os eventos de espera e o tipo de evento de espera para ver 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
As verificações sequenciais frequentes em tabelas com mais de algumas dezenas de linhas geralmente indicam um índice ausente. Quando as verificações tocam em milhares ou até mesmo centenas de milhares de linhas, ele pode causar uso excessivo da CPU.
Verificações sequenciais frequentes em tabelas com centenas de milhares de linhas podem causar uso excessivo de CPU. Evite verificações sequenciais nessas tabelas criando índices necessários.
Execute esta consulta para verificar o número de vezes que 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 essas consultas são tráfego legítimo, aumente os recursos da CPU na instância para evitar falhas ou inatividade do banco de dados.