Compartilhar via

Solucionar problemas de alta utilização de IOPS no Banco de Dados do Azure para PostgreSQL

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 do IOPS ao usar o Banco de Dados do Azure para PostgreSQL.

Neste artigo, você aprenderá como:

  • Sobre guias de solução de problemas para identificar e obter recomendações para atenuar as causas raiz.
  • Use ferramentas para identificar alta utilização de E/S (entrada/saída), como Métricas do Azure, Repositório de Consultas e pg_stat_statements.
  • Identifique causas raiz, como consultas de longa duração, horários de ponto de verificação, processos de daemon do Autovacuum interrompidos e alta utilização de armazenamento.
  • Resolva a alta utilização de E/S usando o Explain Analyze e ajuste os parâmetros do servidor relacionados ao ponto de verificação e o daemon do Autovacuum.

Guias de solução de problemas

Usando os guias de solução de problemas de recursos que estão disponíveis no portal do Banco de Dados do Azure para PostgreSQL, a causa raiz provável e as recomendações para atenuar o cenário de utilização de IOPS alta podem ser encontradas. Para saber como configurar os guias de solução de problemas para usá-los, siga configurar os guias de solução de problemas.

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

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

Métricas do Azure

As Métricas do Azure são um bom ponto de partida para verificar a utilização de E/S em uma data e um 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, taxa de transferência de leitura e taxa de transferência de gravação para descobrir os horários em que a carga de trabalho está causando alta utilização de E/S. Para monitoramento proativo, você pode configurar alertas nas métricas. Para obter diretrizes passo a passo, consulte Métricas do Azure.

Repositório de Consultas

O recurso do Repositório de Consultas captura automaticamente o histórico de consultas e estatísticas de runtime e os retém para revisão. Ele divide os dados por tempo para ver os 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 diretrizes passo a passo, confira Monitorar o desempenho com o Repositório de Consultas.

Use a seguinte instrução 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;

Extensão pg_stat_statements

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

Use a seguinte instrução 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 Repositório de Consultas ou pg_stat_statements para preencher as colunas blk_read_time e blk_write_time, é necessário habilitar o parâmetro do servidor track_io_timing. Para saber mais sobre track_io_timing, revise os Parâmetros do servidor.

Identificar causas raiz

Se os níveis de consumo de E/S forem altos no geral, as seguintes causas raiz podem ser o motivo:

Transações de longa execuçã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 a seguir 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

A E/S alta também pode ser vista em cenários em que um ponto de verificação está ocorrendo com muita frequência. Uma forma de identificar isso é verificando o arquivo de log da instância do servidor flexível do Banco de Dados do Azure para PostgreSQL para o seguinte texto de log: “LOG: pontos de verificação estão ocorrendo com muita frequência”.

Também é possível investigar usando uma abordagem que salva instantâneos periódicos de pg_stat_bgwriter com um carimbo de data/hora. Com os instantâneos salvos, é possível calcular o intervalo médio do ponto 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 autovacuum 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 tabela passou pela ação do Autovacuum.
  • autoanalyze_count: o número de vezes que a tabela foi analisada.

Resolver a alta utilização de E/S

Para resolver a alta utilização de E/S, é possível 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 a consulta e ajustá-la. Para saber mais sobre o comando EXPLAIN ANALYZE, revise o Plano EXPLAIN.

Encerrar transações de longa duração

Há a opção de encerrar uma transação de longa duração.

Para encerrar a PID (ID de processo) de uma sessão, detecte-a com 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;

Também é possível filtrar por outras propriedades, como usename (nome de usuário) ou datname (nome do banco de dados).

Com a PID da sessão, é possível encerrá-la usando a seguinte consulta:

SELECT pg_terminate_backend(pid);

Ajustar os parâmetros do servidor

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

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

    1. Execute a seguinte consulta para obter o LSN atual do WAL e observe o resultado:

      select pg_current_wal_lsn();

    2. Aguarde checkpoint_timeout segundos. Execute a seguinte consulta para obter o LSN atual do WAL e observe o resultado:

      select pg_current_wal_lsn();

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

      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 como 0,9. Como exemplo, um valor de 0,9 para um checkpoint_timeout de cinco minutos indica que o objetivo de conclusão de um ponto de verificação é 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 pode resultar em um aumento da carga de E/S no servidor.

  • checkpoint_timeout: é possível aumentar o valor padrão de checkpoint_timeout definido no servidor. Ao aumentar esse valor, leve em consideração que isso também aumenta o tempo de recuperação de falhas.

Sintonizar o Autovacuum para diminuir as interrupções

Para saber mais sobre o monitoramento e o ajuste em cenários em que o Autovacuum é muito prejudicial, confira Ajuste do Autovacuum.

Aumente o armazenamento

Aumentar o armazenamento é benéfico ao adicionar mais IOPS ao servidor. Para saber mais sobre o armazenamento e os IOPS associados, confira Opções de computação e armazenamento.

Conteúdo relacionado

  • Last updated on