Ajuste de vácuo automático no Banco de Dados do Azure para PostgreSQL – Servidor Flexível

Artigo
12/10/2024

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

Este artigo fornece uma visão geral do recurso de vácuo automático para o servidor flexível do Banco de Dados do Azure para PostgreSQL e os guias de solução de problemas de recursos disponíveis para monitorar o sobrecarga do banco de dados e os bloqueadores de vácuo automático. Ele também fornece informações sobre a distância que o banco de dados está da situação de emergência ou encapsulamento.

O que é o vácuo automático

O vácuo automático é um processo em segundo plano do PostgreSQL que limpa automaticamente as tuplas mortas e atualiza as estatísticas. Ele ajuda a manter o desempenho do banco de dados executando automaticamente duas tarefas importantes de manutenção:

VACUUM — libera espaço em disco removendo tuplas mortas.
ANALYZE — Coleta estatísticas para ajudar o Otimizador do PostgreSQL a escolher os melhores caminhos de execução para consultas.

Para garantir que o vácuo automático funcione corretamente, o parâmetro de servidor de vácuo automático sempre deve ser definido como ATIVADO. Quando habilitado, o PostgreSQL decide automaticamente quando executar VACUUM ou ANALYZE em uma tabela, garantindo que o banco de dados permaneça eficiente e otimizado.

Vácuo automático interno

O vácuo automático faz a leitura das páginas procurando por tuplas mortas e, se nenhuma for encontrada, o vácuo automático descarta a página. Quando o vácuo automático encontra tuplas inativas, ele as remove. O custo é baseado em:

Parâmetro	Descrição
`vacuum_cost_page_hit`	Custo de leitura de uma página que já está em buffers compartilhados e não precisa de uma leitura de disco. O valor padrão é definido como 1.
`vacuum_cost_page_miss`	Custo da busca de uma página que não está em buffers compartilhados. O valor padrão é definido como 10.
`vacuum_cost_page_dirty`	Custo de gravação em uma página quando tuplas mortas são encontradas nela. O valor padrão é definido como 20.

A quantidade de trabalho que o vácuo automático executa depende de dois parâmetros:

Parâmetro	Descrição
`autovacuum_vacuum_cost_limit`	A quantidade de trabalho que o vácuo automático faz de uma só vez.
`autovacuum_vacuum_cost_delay`	Número de milissegundos em que o vácuo automático está em suspensão depois de atingir o limite de custo especificado pelo parâmetro `autovacuum_vacuum_cost_limit`.

Em todas as versões atualmente com suporte do Postgres, o valor padrão para autovacuum_vacuum_cost_limit é 200 (na verdade, definido como -1, o que o torna igual ao valor do vacuum_cost_limit regular, que, por padrão, é 200).

Quanto ao autovacuum_vacuum_cost_delay, na versão 11 do Postgres, o padrão é 20 milissegundos, enquanto nas versões 12 e posteriores do Postgres, o padrão é 2 milissegundos.

O vácuo automático é ativado 50 vezes (50*20 ms=1000 ms) a cada segundo. Toda vez que é ativado, o vácuo automático lê 200 páginas.

Isso significa que, em um segundo, o vácuo automático pode fazer:

~80 MB/S [ (200 páginas/vacuum_cost_page_hit) * 50 * 8 KB por página] se todas as páginas com tuplas inativas forem encontradas em buffers compartilhados.
~8 MB/S [ (200 páginas/vacuum_cost_page_miss) * 50 * 8 KB por página] se todas as páginas com tuplas inativas forem lidas no disco.
~4 MB/S [ (200 páginas/vacuum_cost_page_dirty) * 50 * 8 KB por página] o vácuo automático pode gravar até 4 MB/s.

Monitorar o vácuo automático

Use as seguintes consultas para monitorar o vácuo automático:

select schemaname,relname,n_dead_tup,n_live_tup,round(n_dead_tup::float/n_live_tup::float*100) dead_pct,autovacuum_count,last_vacuum,last_autovacuum,last_autoanalyze,last_analyze from pg_stat_all_tables where n_live_tup >0;

As seguintes colunas ajudam a determinar se o vácuo automático está alcançando a atividade da tabela:

Parâmetro	Descrição
`dead_pct`	Porcentagem de tuplas mortas em comparação com as tuplas vivas.
`last_autovacuum`	A data da última vez que houve vácuo automático na tabela.
`last_autoanalyze`	A data da última vez que a tabela foi analisada automaticamente.

Disparar a limpeza automática

Uma ação de vácuo automático (ANALYZE ou VACUUM) é disparada quando o número de tuplas inativas excede um número específico que depende de dois fatores: a contagem total de linhas em uma tabela, além de um limite fixo. ANALYZE, por padrão, é disparado quando 10% da tabela mais 50 linhas são alteradas, enquanto VACUUM é disparado quando 20% da tabela mais 50 linhas são alteradas. Como o limite VACUUM é duas vezes mais alto que o limite ANALYZE, ANALYZE é disparado anteriormente ao VACUUM. Para versões PG >=13; ANALYZE, por padrão, dispara quando 20% da tabela mais 1000 linhas são inseridas.

As equações exatas para cada ação são:

Autoanalyze = autovacuum_analyze_scale_factor * tuples + autovacuum_analyze_threshold or autovacuum_vacuum_insert_scale_factor * tuples + autovacuum_vacuum_insert_threshold (para versões PG >= 13)
Autovacuum = autovacuum_vacuum_scale_factor * tuples + autovacuum_vacuum_threshold

Por exemplo, se tivermos uma tabela com 100 linhas. A equação a seguir fornece as informações sobre quando a análise e o vácuo disparam:

Para Atualizações/exclusões: Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70

Analise gatilhos após 60 linhas serem alteradas em uma tabela e gatilhos de vácuo quando 70 linhas forem alteradas em uma tabela.

Para Inserções: Autoanalyze = 0.2 * 100 + 1000 = 1020

Analisar gatilhos após 1.020 linhas serem inseridas em uma tabela

Essa é a descrição dos parâmetros usados na equação:

Parâmetro	Descrição
`autovacuum_analyze_scale_factor`	Porcentagem de inserções/atualizações/exclusões que dispara ANALYZE na tabela.
`autovacuum_analyze_threshold`	Especifica o número mínimo de tuplas inseridas/atualizadas/excluídas para usar ANALYZE em uma tabela.
`autovacuum_vacuum_insert_scale_factor`	Porcentagem de inserções que dispara ANALYZE na tabela.
`autovacuum_vacuum_insert_threshold`	Especifica o número mínimo de tuplas inseridas para usar ANALYZE em uma tabela.
`autovacuum_vacuum_scale_factor`	Porcentagem de atualizações/exclusões que dispara VACUUM na tabela.

Use a consulta a seguir para listar as tabelas em um banco de dados e identificar as tabelas que se qualificam para o processo de salvamento automático:

 SELECT *
      ,n_dead_tup > av_threshold AS av_needed
      ,CASE
        WHEN reltuples > 0
          THEN round(100.0 * n_dead_tup / (reltuples))
        ELSE 0
        END AS pct_dead
    FROM (
      SELECT N.nspname
        ,C.relname
        ,pg_stat_get_tuples_inserted(C.oid) AS n_tup_ins
        ,pg_stat_get_tuples_updated(C.oid) AS n_tup_upd
        ,pg_stat_get_tuples_deleted(C.oid) AS n_tup_del
        ,pg_stat_get_live_tuples(C.oid) AS n_live_tup
        ,pg_stat_get_dead_tuples(C.oid) AS n_dead_tup
        ,C.reltuples AS reltuples
        ,round(current_setting('autovacuum_vacuum_threshold')::INTEGER + current_setting('autovacuum_vacuum_scale_factor')::NUMERIC * C.reltuples) AS av_threshold
        ,date_trunc('minute', greatest(pg_stat_get_last_vacuum_time(C.oid), pg_stat_get_last_autovacuum_time(C.oid))) AS last_vacuum
        ,date_trunc('minute', greatest(pg_stat_get_last_analyze_time(C.oid), pg_stat_get_last_autoanalyze_time(C.oid))) AS last_analyze
      FROM pg_class C
      LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
      WHERE C.relkind IN (
          'r'
          ,'t'
          )
        AND N.nspname NOT IN (
          'pg_catalog'
          ,'information_schema'
          )
        AND N.nspname !~ '^pg_toast'
      ) AS av
    ORDER BY av_needed DESC ,n_dead_tup DESC;

Observação

A consulta não leva em consideração que o vácuo automático pode ser configurado por tabela usando o comando DDL "alter table".

Problemas comuns de vácuo automático

Analise a seguinte lista de possíveis problemas comuns com o processo de vácuo automático.

Não acompanhar o servidor ocupado

O processo de salvamento automático estima o custo de cada operação de E/S, acumula um total para cada operação executada e pausa quando o limite superior do custo é atingido. autovacuum_vacuum_cost_delay e autovacuum_vacuum_cost_limit são os dois parâmetros de servidor que são usados no processo.

Por padrão, autovacuum_vacuum_cost_limit é definido como –1, o que significa que o limite de custo de vácuo automático é o mesmo valor que o parâmetro vacuum_cost_limit, que usa 200 como padrão. vacuum_cost_limit é o custo de um vácuo manual.

Se autovacuum_vacuum_cost_limit estiver definido como -1, o vácuo automático usará o parâmetro vacuum_cost_limit, mas se o próprio autovacuum_vacuum_cost_limit estiver definido como maior do que -1, o parâmetro autovacuum_vacuum_cost_limit será considerado.

Caso o vácuo automático não esteja funcionando corretamente, os seguintes parâmetros poderão ser alterados:

Parâmetro	Descrição
`autovacuum_vacuum_cost_limit`	Padrão: `200`. O limite de custos pode ser aumentado. A utilização de E/S e CPU no banco de dados deve ser monitorada antes e depois de fazer alterações.
`autovacuum_vacuum_cost_delay`	Versão 11 do Postgres - Padrão: `20 ms`. O parâmetro pode ser reduzido para `2-10 ms`. Versões do Postgres 12 e superiores - Padrão: `2 ms`.

Observação

O valor autovacuum_vacuum_cost_limit é distribuído proporcionalmente entre os trabalhos de vácuo automático em execução, de modo que, se houver mais de um, a soma dos limites para cada trabalho não excederá o valor do parâmetro autovacuum_vacuum_cost_limit.
autovacuum_vacuum_scale_factor é outro parâmetro que pode disparar vácuo em uma tabela com base no acúmulo de tupla morta. Padrão: 0.2, Intervalo permitido: 0.05 - 0.1. O fator de escala é específico da carga de trabalho e deve ser definido dependendo da quantidade de dados nas tabelas. Antes de alterar o valor, investigue a carga de trabalho e os volumes de tabela individuais.

Vácuo automático em constante execução

Executar continuamente o vácuo automático pode afetar a utilização da CPU e de E/S no servidor. Veja alguns dos possíveis motivos:

`maintenance_work_mem`

O daemon de vácuo automático usa autovacuum_work_mem o que é, por padrão, definido como -1 significando que autovacuum_work_mem teria o mesmo valor que o parâmetro maintenance_work_mem. Este documento pressupõe que autovacuum_work_mem está definido como -1 e maintenance_work_mem é usado pelo daemon de vácuo automático.

Se maintenance_work_mem for baixo, poderá ser aumentado para até 2 GB no servidor flexível do Banco de Dados do Azure para PostgreSQL. Uma regra geral é alocar 50 MB para maintenance_work_mem para cada 1 GB de RAM.

Grande quantidade de bancos de dados

O vácuo automático tenta iniciar um trabalho em cada banco de dados a cada autovacuum_naptime segundos.

Por exemplo, se um servidor tiver 60 bancos de dados e autovacuum_naptime for definido como 60 segundos, o trabalho de vácuo automático será iniciado a cada segundo [autovacuum_naptime/Número de bancos de dados].

É uma boa ideia aumentar autovacuum_naptime se houver mais bancos de dados em um cluster. Ao mesmo tempo, o processo de vácuo automático pode se tornar mais agressivo aumentando o autovacuum_cost_limit e diminuindo os parâmetros autovacuum_cost_delay e aumentando o autovacuum_max_workers do padrão de 3 para 4 ou 5.

Erros de memória insuficiente

Valores de maintenance_work_mem excessivamente agressivos podem periodicamente causar erros fora da memória no sistema. É importante entender a RAM disponível no servidor antes de fazer qualquer alteração no parâmetro maintenance_work_mem.

O vácuo automático é muito disruptivo

Se o vácuo automático estiver consumindo mais recursos, as seguintes ações poderão ser executadas:

Parâmetros de vácuo automático

Avalie os parâmetros autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit. autovacuum_max_workers. Definir incorretamente parâmetros de vácuo automático pode levar a cenários em que o vácuo automático se torna muito disruptivo.

Se o vácuo automático estiver muito disruptivo, considere as seguintes ações:

Aumente autovacuum_vacuum_cost_delay e reduza autovacuum_vacuum_cost_limit se definido acima do padrão de 200.
Reduza o número de autovacuum_max_workers se for definido acima do padrão de 3.

Muitos trabalhos do vácuo automático

Aumentar o número de trabalhadores de vácuo automático não necessariamente aumentará a velocidade do vácuo. Não é recomendável ter um alto número de trabalhos de vácuo automático.

Aumentar o número de trabalhos de vácuo automático resultará em mais consumo de memória e, dependendo do valor de maintenance_work_mem, poderá causar degradação de desempenho.

Cada processo de trabalho de vácuo automático só obtém (1/autovacuum_max_workers) do autovacuum_cost_limit total e, portanto, ter um alto número de trabalhos faz com que cada um fique mais lento.

Se o número de trabalhos for aumentado, autovacuum_vacuum_cost_limit também deverá ser aumentado e/ou autovacuum_vacuum_cost_delay deverá ser reduzido para acelerar o processo de vácuo.

No entanto, se definirmos o parâmetro no nível de tabela autovacuum_vacuum_cost_delay ou parâmetros autovacuum_vacuum_cost_limit, os trabalhadores em execução nessas tabelas estarão isentos de serem considerados no algoritmo de balanceamento [autovacuum_cost_limit/autovacuum_max_workers].

Proteção contra solução alternativa de ID de transação (TXID) de vácuo automático

Quando um banco de dados é executado na proteção contra encapsulamento da ID da transação, uma mensagem de erro como a seguinte poderá ser observada:

Database isn't accepting commands to avoid wraparound data loss in database 'xx'
Stop the postmaster and vacuum that database in single-user mode.

Observação

Essa mensagem de erro é um descuido de longa data. Normalmente, você não precisa mudar para o modo de usuário único. Em vez disso, você pode executar os comandos VACUUM necessários e executar o ajuste para que o VACUUM seja executado rapidamente. Embora não seja possível executar nenhuma linguagem de manipulação de dados (DML), você ainda pode executar VACUUM.

O problema de encapsulamento ocorre quando o banco de dados não é limpo ou há muitas tuplas mortas que não foram removidas pela limpeza automática.

Os possíveis motivos para esse problema podem ser um dos seguintes:

Carga de trabalho pesada

A carga de trabalho pode causar muitas tuplas inativas em um breve período, o que dificulta a recuperação automática. As tuplas inativas no sistema se somam em um período que leva à degradação do desempenho da consulta e leva à situação de solução alternativa. Uma das razões para essa situação surgir pode ser porque os parâmetros de vácuo automático não estão definidos adequadamente e não está acompanhando um servidor ocupado.

Transações de longa execução

Qualquer transação de execução prolongada no sistema não permitirá que tuplas mortas sejam removidas enquanto o vácuo automático estiver em execução. Elas são um bloqueador ao processo de vácuo. A remoção das transações de execução prolongada libera tuplas inativas para exclusão quando o vácuo automático é executado.

Transações de longa execução podem ser detectadas usando a seguinte consulta:

    SELECT pid, age(backend_xid) AS age_in_xids,
    now () - xact_start AS xact_age,
    now () - query_start AS query_age,
    state,
    query
    FROM pg_stat_activity
    WHERE state != 'idle'
    ORDER BY 2 DESC
    LIMIT 10;

Instruções preparadas

Se houver instruções preparadas que não sejam confirmadas, elas evitarão que tuplas mortas sejam removidas.
A consulta a seguir ajuda a localizar instruções preparadas não confirmadas:

    SELECT gid, prepared, owner, database, transaction
    FROM pg_prepared_xacts
    ORDER BY age(transaction) DESC;

Use COMMIT PREPARED ou ROLLBACK PREPARED para confirmar ou reverter essas instruções.

Slots de replicação não utilizados

Slots de replicação não utilizados impedem que o vácuo automático reclame tuplas inativas. A consulta a seguir ajuda a identificar slots de replicação não utilizados:

    SELECT slot_name, slot_type, database, xmin
    FROM pg_replication_slots
    ORDER BY age(xmin) DESC;

Use pg_drop_replication_slot() para excluir slots de replicação não utilizados.

Quando o banco de dados for executado na proteção contra encapsulamento da ID da transação, verifique se há bloqueadores, conforme mencionado anteriormente, e remova os bloqueadores manualmente para que o vácuo automático continue e seja concluído. Você também pode aumentar a velocidade do vácuo automático definindo autovacuum_cost_delay como 0 e aumentando autovacuum_cost_limit para um valor maior que 200. No entanto, as alterações nesses parâmetros não se aplicam aos trabalhadores de limpeza automática existentes. Reinicie o banco de dados ou mate os trabalhos existentes manualmente para aplicar alterações de parâmetros.

Requisitos específicos da tabela

Parâmetros de vácuo automático podem ser definidos para tabelas individuais. É especialmente importante para tabelas pequenas e grandes. Por exemplo, para uma tabela pequena que contém apenas 100 linhas, o vácuo automático dispara a operação VACUUM quando 70 linhas são alteradas (conforme calculado anteriormente). Se essa tabela for atualizada com frequência, você poderá ver centenas de operações de vácuo automático por dia, impedindo que o vácuo automático mantenha outras tabelas nas quais o percentual de alterações não é tão significativo. Como alternativa, uma tabela que contenha um bilhão de linhas precisa alterar 200 milhões de linhas para disparar operações de vácuo automático. A definição de parâmetros de vácuo automático impede adequadamente esses cenários.

Para definir a configuração de vácuo automático por tabela, altere os parâmetros do servidor como os seguintes exemplos:

    ALTER TABLE <table name> SET (autovacuum_analyze_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_analyze_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_scale_factor = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_threshold = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_delay = xx);
    ALTER TABLE <table name> SET (autovacuum_vacuum_cost_limit = xx);

Cargas de trabalho somente inserção

Nas versões do PostgreSQL <= 13, o vácuo automático não é executado em tabelas com uma carga de trabalho somente inserção, pois não há tuplas mortas e nenhum espaço livre que precise ser recuperado. No entanto, a autoanálise será executada para cargas de trabalho de somente inserção, pois há novos dados. As desvantagens dessa arquitetura são:

O mapa de visibilidade das tabelas não é atualizado e, portanto, o desempenho da consulta, especialmente onde há Apenas Verificações de Índice, começa a sofrer ao longo do tempo.
O banco de dados pode ser executado na proteção de solução alternativa da ID da transação.
Os bits de dica não estão definidos.

Soluções

Versões do Postgres <=13

Usando a extensão pg_cron, um trabalho cron pode ser configurado para agendar uma análise periódica de vácuo na tabela. A frequência do trabalho cron depende da carga de trabalho.

Para obter diretrizes passo a passo usando pg_cron, confira Extensões.

Postgres 13 e versões posteriores

O vácuo automático será executado em tabelas com uma carga de trabalho de somente inserção. Dois novos parâmetros de servidor autovacuum_vacuum_insert_threshold e autovacuum_vacuum_insert_scale_factor ajudam a controlar quando o vácuo automático puder ser disparado em tabelas somente inserção.

Guias de solução de problemas

Usando os guias de solução de problemas dos recursos disponíveis no portal do Servidor flexível do Banco de Dados do Azure para PostgreSQL, é possível monitorar a sobrecarga no banco de dados ou no nível de esquema individual, juntamente com a identificação de potenciais bloqueadores para o processo de vácuo automático. Dois guias de solução de problemas estão disponíveis primeiro; um é o monitoramento de vácuo automático, que pode ser usado para monitorar o bloat no banco de dados ou no nível de esquema individual. O segundo guia de solução de problemas são os bloqueadores de vácuo automático e o encapsulamento, o que ajudará a identificar potenciais bloqueadores de vácuo automático. Ele também fornece informações sobre o quão longe os bancos de dados no servidor estão da situação de encapsulamento ou de emergência. Os guias de solução de problemas também compartilham recomendações para atenuar possíveis problemas. Para saber como configurar os guias de solução de problemas para usá-los, siga configurar os guias de solução de problemas.

Recomendações do Assistente do Azure

As recomendações do Assistente do Azure são uma maneira proativa de identificar se um servidor tem uma alta taxa de bloat ou se o servidor está se aproximando do cenário de encapsulamento de transação. Você também pode criar alertas do Assistente do Azure para as recomendações.

As recomendações são:

Alta Taxa de Sobrecarga: uma alta taxa de sobrecarga pode afetar o desempenho do servidor de várias maneiras. Um problema significativo é que o Otimizador de Mecanismo do PostgreSQL pode ter dificuldades para selecionar o melhor plano de execução, levando a um desempenho deficiente da consulta. Portanto, uma recomendação será disparada quando a porcentagem de sobrecarga em um servidor atingir um determinado limite para evitar esses problemas de desempenho.
Encapsulamento da transação: esse cenário é um dos problemas mais sérios que um servidor pode encontrar. Quando o servidor estiver nesse estado, ele poderá parar de aceitar mais transações, fazendo com que o servidor se torne somente leitura. Portanto, uma recomendação será disparada quando vermos que o servidor ultrapassou o limite de 1 bilhão de transações.

Compartilhe suas sugestões e bugs com a equipe de produtos do Banco de Dados do Azure para PostgreSQL.

Compartilhar via

Ajuste de vácuo automático no Banco de Dados do Azure para PostgreSQL – Servidor Flexível

O que é o vácuo automático

Vácuo automático interno

Monitorar o vácuo automático

Disparar a limpeza automática

Problemas comuns de vácuo automático

Não acompanhar o servidor ocupado

Vácuo automático em constante execução

`maintenance_work_mem`

Grande quantidade de bancos de dados

Erros de memória insuficiente

O vácuo automático é muito disruptivo

Parâmetros de vácuo automático

Muitos trabalhos do vácuo automático

Proteção contra solução alternativa de ID de transação (TXID) de vácuo automático

Carga de trabalho pesada

Transações de longa execução

Instruções preparadas

Slots de replicação não utilizados

Requisitos específicos da tabela

Cargas de trabalho somente inserção

Soluções

Versões do Postgres <=13

Postgres 13 e versões posteriores

Guias de solução de problemas

Recomendações do Assistente do Azure

Comentários

Recursos adicionais

Compartilhar via

Ajuste de vácuo automático no Banco de Dados do Azure para PostgreSQL – Servidor Flexível

O que é o vácuo automático

Vácuo automático interno

Monitorar o vácuo automático

Disparar a limpeza automática

Problemas comuns de vácuo automático

Não acompanhar o servidor ocupado

Vácuo automático em constante execução

maintenance_work_mem

Grande quantidade de bancos de dados

Erros de memória insuficiente

O vácuo automático é muito disruptivo

Parâmetros de vácuo automático

Muitos trabalhos do vácuo automático

Proteção contra solução alternativa de ID de transação (TXID) de vácuo automático

Carga de trabalho pesada

Transações de longa execução

Instruções preparadas

Slots de replicação não utilizados

Requisitos específicos da tabela

Cargas de trabalho somente inserção

Soluções

Versões do Postgres <=13

Postgres 13 e versões posteriores

Guias de solução de problemas

Recomendações do Assistente do Azure

Conteúdo relacionado

Comentários

Recursos adicionais

`maintenance_work_mem`