Share via

Solucionar problemas de alta utilização de IOPS do Banco de Dados do Azure para PostgreSQL - Servidor Flexível

  • Artigo

APLICA-SE A: Banco de Dados do Azure para PostgreSQL – Servidor Flexível

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 o servidor flexível 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 servidor flexível do Banco de Dados do Azure para PostgreSQL, a provável causa raiz e as recomendações para o cenário de mitigação de alta utilização de IOPS 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 do 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 maneira de identificar isso é verificando o arquivo de log do servidor flexível do Banco de Dados do Azure para PostgreSQL para o seguinte texto de log: "LOG: os 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 alta E/S, use EXPLAIN ANALYZE para investigar melhor 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 você observar que o ponto de verificação está acontecendo com muita frequência, aumente o parâmetro do servidor até que a max_wal_size maioria dos pontos de verificação seja controlada pelo 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