Resolver problemas de utilização elevada de IOPS no Base de Dados do Azure para PostgreSQL flexible server

Este artigo mostra como identificar rapidamente a causa raiz da alta utilização de IOPS (operações de entrada/saída por segundo) e fornece ações corretivas para controlar a utilização de IOPS quando você estiver usando o Banco de Dados do Azure para PostgreSQL.

Neste artigo, você aprenderá a:

  • Sobre guias de solução de problemas para identificar e obter recomendações para mitigar as causas raiz.
  • Utilize ferramentas para identificar uma elevada utilização de entrada/saída (E/S), como o Azure Metrics, o Query Store e o pg_stat_statements.
  • Identifique as causas principais, como consultas de longa execução, tempos de ponto de verificação, um processo de daemon de autovácuo com interrupções e alta utilização do armazenamento.
  • Resolve a elevada utilização de E/S usando o Explain Analyze, ajusta parâmetros relacionados com checkpoints e ajusta o daemon de autovácuo.

Guias de solução de problemas

Usando os guias de solução de problemas de recursos disponíveis no portal do Banco de Dados do Azure para PostgreSQL, é possível encontrar a causa raiz provável e as recomendações para mitigar o cenário de alta utilização de IOPS. Como configurar os guias de resolução de problemas para os utilizar: siga os guias de resolução de problemas de configuração.

Ferramentas para identificar alta utilização de E/S

Considere as seguintes ferramentas para identificar a alta utilização de E/S.

Azure Metrics

O Azure Metrics é um bom ponto de partida para verificar a utilização de E/S para uma data e período definidos. As métricas fornecem informações sobre o tempo durante o qual a utilização de E/S é alta. Compare os gráficos de IOPs de gravação, IOPs de leitura, débito de leitura e débito de gravação para identificar os momentos em que a carga de trabalho está a causar uma elevada utilização de E/S. Para monitoramento proativo, você pode configurar alertas nas métricas. Para obter orientação passo a passo, consulte Métricas do Azure.

Query Store (Arquivo de Consultas)

O recurso Repositório de Consultas captura automaticamente o histórico de consultas e estatísticas de tempo de execução e as retém para sua revisão. Ele divide os dados por tempo para ver padrões de uso temporal. Os dados de todos os usuários, bancos de dados e consultas são armazenados em um banco de dados chamado azure_sys na instância de servidor flexível do Banco de Dados do Azure para PostgreSQL. Para obter orientação passo a passo, consulte Monitorar o desempenho com o Repositório de Consultas.

Use a instrução a seguir para exibir as cinco principais instruções SQL que consomem E/S:

select * from query_store.qs_view qv where is_system_query is FALSE
order by blk_read_time + blk_write_time  desc limit 5;

A extensão pg_stat_statements

A pg_stat_statements extensão ajuda a identificar consultas que consomem E/S no servidor.

Use a instrução a seguir para exibir as cinco principais instruções SQL que consomem E/S:

SELECT userid::regrole, dbid, query
FROM pg_stat_statements
ORDER BY blk_read_time + blk_write_time desc
LIMIT 5;

Observação

Ao usar o query store ou o pg_stat_statements, para que as colunas blk_read_time e blk_write_time sejam preenchidas, é necessário ativar o parâmetro track_io_timing. Para mais informações sobre track_io_timing, consulte Parâmetros.

Identificar as causas profundas

Se os níveis de consumo de E/S forem altos em geral, as seguintes podem ser as causas principais:

Transações de longa duração

Transações de longa duração podem consumir E/S, o que pode levar a uma alta utilização de E/S.

A consulta seguinte ajuda a identificar as conexões que estão em execução há mais tempo:

SELECT pid, usename, datname, query, now() - xact_start as duration
FROM pg_stat_activity
WHERE pid <> pg_backend_pid() and state IN ('idle in transaction', 'active')
ORDER BY duration DESC;

Horários dos pontos de verificação

Também se pode observar E/S elevada em cenários em que um ponto de controlo está a ocorrer com demasiada frequência. Uma maneira de identificar isso é verificando o arquivo de log da instância de servidor flexível do Banco de Dados do Azure para PostgreSQL em busca do seguinte texto de log: "LOG: os pontos de verificação estão ocorrendo com muita frequência".

Também pode investigar utilizando uma abordagem em que são guardados instantâneos periódicos de pg_stat_bgwriter com um registo de data/hora. Usando os instantâneos salvos, você pode calcular o intervalo médio de pontos de verificação, o número de pontos de verificação solicitados e o número de pontos de verificação cronometrados.

Processo de daemon de autovácuo disruptivo

Execute a seguinte consulta para monitorar o autovacuum:

SELECT schemaname, relname, n_dead_tup, n_live_tup, autovacuum_count, last_vacuum, last_autovacuum, last_autoanalyze, autovacuum_count, autoanalyze_count FROM pg_stat_all_tables WHERE n_live_tup > 0;

A consulta é usada para verificar com que frequência as tabelas no banco de dados estão sendo aspiradas.

  • last_autovacuum: A data e a hora em que o último autovacuum foi executado na tabela.
  • autovacuum_count: O número de vezes que a mesa foi aspirada.
  • autoanalyze_count: O número de vezes que a tabela foi analisada.

Resolver a elevada utilização de E/S

Para resolver a alta utilização de E/S, você pode usar qualquer um dos três métodos a seguir.

O comando EXPLAIN ANALYZE

Depois de identificar a consulta que está consumindo E/S alta, use EXPLAIN ANALYZE para investigar mais a consulta e ajustá-la. Para obter mais informações sobre o comando EXPLAIN ANALYZE, consulte o plano EXPLAIN.

Encerrar transações de longa duração

Você pode considerar matar uma transação de longa duração como uma opção.

Para encerrar a ID de processo (PID) de uma sessão, você precisa detetar o PID usando a seguinte consulta:

SELECT pid, usename, datname, query, now() - xact_start as duration
FROM pg_stat_activity
WHERE pid <> pg_backend_pid() and state IN ('idle in transaction', 'active')
ORDER BY duration DESC;

Você também pode filtrar por outras propriedades, como usename (nome de usuário) ou datname (nome do banco de dados).

Depois de ter o PID da sessão, você pode encerrá-lo usando a seguinte consulta:

SELECT pg_terminate_backend(pid);

Parâmetros de afinação

Se observar que o ponto de controlo ocorre com demasiada frequência, aumente o parâmetro max_wal_size até que a maioria dos pontos de controlo seja acionada pelo tempo, em vez de ser solicitada. Eventualmente, 90% ou mais devem ser baseados no tempo, e o intervalo entre dois pontos de verificação deve ser próximo ao checkpoint_timeout valor definido no servidor.

  • max_wal_size: O horário comercial de pico é um bom momento para chegar a um max_wal_size valor. Para chegar a um valor, faça o seguinte:

    1. Execute a seguinte consulta para obter o LSN WAL atual e, em seguida, anote o resultado:

      select pg_current_wal_lsn();

    2. Aguarde alguns checkpoint_timeout segundos. Execute a seguinte consulta para obter o LSN WAL atual e, em seguida, anote o resultado:

      select pg_current_wal_lsn();

    3. Execute a seguinte consulta, que usa os dois resultados, para verificar a diferença, em gigabytes (GB):

      select round (pg_wal_lsn_diff ('LSN value when run second time', 'LSN value when run first time')/1024/1024/1024,2) WAL_CHANGE_GB;

  • checkpoint_completion_target: Uma boa prática seria definir o valor para 0,9. Por exemplo, um valor de 0,9 para um checkpoint_timeout de 5 minutos indica que o objetivo para concluir um ponto de controlo é de 270 segundos (0,9 * 300 segundos). Um valor de 0,9 fornece uma carga de E/S bastante consistente. Um valor agressivo de checkpoint_completion_target poderá resultar num aumento da carga de E/S no servidor.

  • checkpoint_timeout: Você pode aumentar o checkpoint_timeout valor a partir do valor padrão definido no servidor. Ao aumentar o valor, tenha em consideração que isso também aumentará o tempo de recuperação após falha.

Ajuste o autovacuum para diminuir interrupções

Para obter mais informações sobre monitoramento e ajuste em cenários em que o autovacuum é muito perturbador, consulte Autovacuum tuning.

Aumentar o armazenamento

Aumentar o armazenamento ajuda quando você adiciona mais IOPS ao servidor. Para obter mais informações sobre armazenamento e IOPS associadas, consulte Opções de computação e armazenamento.

Conteúdo relacionado

  • Last updated on