Ajuste de vácuo automático no Banco de Dados do Azure para PostgreSQL

Este artigo fornece uma visão geral da funcionalidade de autovacuo para o Banco de Dados do Azure para PostgreSQL e dos guias de diagnóstico e resolução de problemas disponíveis para monitorizar o inchaço do banco de dados e os bloqueios do autovacuo. Ele também fornece informações sobre o quão longe o banco de dados está de uma situação de emergência ou envolvente.

O que é o autovácuo?

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

• VACUUM - Recupera espaço dentro dos arquivos do banco de dados removendo tuplas mortas e marcando esse espaço como reutilizável pelo PostgreSQL. Ele não reduz necessariamente o tamanho físico dos arquivos de banco de dados no disco. Para retornar espaço ao sistema operacional, use operações que reescrevem a tabela (por exemplo, VACUUM FULL ou pg_repack), que têm considerações adicionais, como fechaduras exclusivas ou janelas de manutenção.
• ANALYZE - Coleta estatísticas de tabela e índice que o planejador de consultas PostgreSQL usa para escolher planos de execução eficientes.

Para garantir que o autovacuum funcione corretamente, defina o parâmetro do servidor de autovacuum como ON. Quando ativado, o PostgreSQL decide automaticamente quando executar VACUUM ou ANALYZE em uma tabela, garantindo que o banco de dados permaneça eficiente e otimizado.

Componentes internos de vácuo automático

O Autovacuum lê páginas à procura de tuplas mortas. Caso não encontre tuplas mortas, o autovacuum descarta a página. Quando o autovácuo encontra tuplas mortas, ele as remove. O custo baseia-se nos seguintes parâmetros:

A quantidade de trabalho que o autovacuum realiza depende de dois parâmetros:

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

O valor padrão para autovacuum_vacuum_cost_delay é 2 milissegundos no PostgreSQL versões 12 e posteriores (era de 20 milissegundos na versão 11).

Limite de uso do buffer (PostgreSQL 16+)

A partir da versão 16 do PostgreSQL, é possível usar o parâmetro vacuum_buffer_usage_limit para controlar o uso de memória durante as operações de VACUUM, ANALYZE e autovacuum.

Esse parâmetro ajuda a evitar que o VACUUM e o autovacuum eliminem muitas páginas úteis de buffers compartilhados, o que pode melhorar o desempenho geral do banco de dados durante as operações de manutenção. O valor padrão normalmente é definido com base em shared_buffers, e você pode configurá-lo para equilibrar o desempenho de vácuo com as necessidades de operações regulares de banco de dados.

Limite máximo para autovacuum (PostgreSQL 18+)

A partir do PostgreSQL versão 18, pode usar o parâmetro autovacuum_vacuum_max_threshold para definir um limite superior no número de atualizações ou eliminações de tuplas que acionam o autovacuum.

Este parâmetro é particularmente útil para tabelas grandes em que o acionamento baseado em fator de escala padrão pode fazer com que o autovacuum aguarde muito tempo antes de ser executado.

O Autovacuum acorda 50 vezes (50*20 ms=1000 ms) a cada segundo. Cada vez que acorda, o autovacuum lê 200 páginas.

Isso significa que em um segundo o autovácuo pode fazer:

• ~80 MB/seg [ (200 páginas/vacuum_cost_page_hit) * 50 * 8 KB por página] se todas as páginas com tuplas mortas forem encontradas em buffers compartilhados.
• ~8 MB/seg [ (200 páginas/vacuum_cost_page_miss) * 50 * 8 KB por página] se todas as páginas com tuplas inativas forem lidas do disco.
• ~4 MB/seg [ (200 páginas/vacuum_cost_page_dirty) * 50 * 8 KB por página] autovacuum pode gravar até 4 MB / seg.

Monitorize o autovácuo

O Banco de Dados do Azure para PostgreSQL fornece as seguintes métricas para monitorar o autovacuum.

As métricas de autovacuum podem ser usadas para monitorizar e ajustar o desempenho do autovacuum para o Azure Database for PostgreSQL - servidor flexível. Cada métrica é emitida em um intervalo de 30 minutos e tem até 93 dias de retenção. Você pode criar alertas para métricas específicas e dividir e filtrar dados de métricas usando a DatabaseName dimensão.

Como ativar métricas de vácuo automático

• As métricas de vácuo automático são desativadas por padrão.
• Para habilitar essas métricas, defina o parâmetro do servidor metrics.autovacuum_diagnostics como ON.
• Esse parâmetro é dinâmico, portanto, uma reinicialização da instância não é necessária.

Lista de métricas de vácuo automático

Considerações sobre o uso de métricas de vácuo automático

• As métricas de vácuo automático que usam a dimensão DatabaseName têm um limite de 30 bancos de dados .
• Na SKU Burstable, o limite é de 10 bancos de dados para métricas que usam a dimensão DatabaseName.
• O limite de dimensão DatabaseName é aplicado na coluna OID, que reflete a ordem de criação do banco de dados.

Para obter mais informações, consulte Métricas de vácuo automático.

Use as seguintes consultas para monitorar o autovacuum:

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 colunas a seguir ajudam a determinar se o autovacuum está acompanhando a atividade da tabela.

Acionamento do autovácuo

Uma ação de autovácuo (ANALYZE ou VACUUM) é acionada quando o número de tuplas mortas excede um determinado número. Esse número depende de dois fatores: a contagem total de linhas em uma tabela, mais um limite fixo. ANALYZE aciona por padrão quando ocorrem 10% da tabela mais 50 alterações de linha, enquanto VACUUM é acionado quando ocorrem 20% da tabela mais 50 alterações de linha. Como o valor limite VACUUM é duas vezes maior que o valor limite ANALYZE, ANALYZE é acionado mais cedo do que VACUUM.

Para as versões 13 e posteriores do PostgreSQL, ANALYZE é acionado por padrão quando ocorrem 20% da tabela mais 1.000 inserções de linha.

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

• Autoanalyze = autovacuum_analyze_scale_fator * tuplas + autovacuum_analyze_threshold ou autovacuum_vacuum_insert_scale_fator * tuplas + autovacuum_vacuum_insert_threshold (Para PostgreSQL versões 13 e posteriores)
• Autovácuo = autovacuum_vacuum_scale_fator * tuplas + autovacuum_vacuum_threshold

Por exemplo, se você tiver uma tabela com 100 linhas, as equações a seguir mostram quando as ações de análise e vácuo são acionadas:

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

Dependente do número de linhas alteradas em uma tabela, o ANALYZE é acionado após 60 alterações, enquanto o VACUUM é acionado quando ocorrem 70 alterações.

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

ANALYZE é acionado após a inserção de 1.020 linhas numa tabela.

Aqui está a descrição dos parâmetros usados na equação:

Use a seguinte consulta para listar as tabelas em um banco de dados e identificar as tabelas que se qualificam para o processo de autovácuo:

 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;

Problemas comuns de autovácuo

Analise a seguinte lista de problemas comuns com o processo de autovácuo.

Não acompanhar o servidor ocupado

O processo de autovácuo estima o custo de cada operação de E/S, acumula um total para cada operação que executa e pausa quando o limite superior do custo é atingido. O processo usa dois parâmetros de servidor: autovacuum_vacuum_cost_delay e autovacuum_vacuum_cost_limit.

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

Se você definir autovacuum_vacuum_cost_limit como -1, o autovacuum usará o vacuum_cost_limit parâmetro. Se você definir autovacuum_vacuum_cost_limit como um valor maior que -1, o autovacuum usará o autovacuum_vacuum_cost_limit parâmetro.

Se o autovacuum não estiver a acompanhar, considere alterar os seguintes parâmetros:

Autovacuum em funcionamento constante

Se o autovacuum for executado continuamente, isso pode afetar a utilização da CPU e I/O no servidor. Aqui estão algumas razões possíveis:

maintenance_work_mem

O daemon de vácuo automático usa autovacuum_work_mem, que é definido como -1 por padrão. Essa configuração padrão significa que autovacuum_work_mem usa o mesmo valor que o maintenance_work_mem parâmetro. Este artigo pressupõe que autovacuum_work_mem está definido como -1 e que o autovacuum daemon usa maintenance_work_mem.

Se maintenance_work_mem estiver baixo, você pode aumentá-lo até 2 GB em uma instância de servidor flexível do Banco de Dados do Azure para PostgreSQL. Uma regra geral é alocar 50 MB para maintenance_work_mem cada 1 GB de RAM.

Grande número de bases de dados

O Autovacuum tenta iniciar um trabalhador em cada banco de dados a cada autovacuum_naptime segundo.

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

Se houver mais bases de dados num cluster, aumente autovacuum_naptime. Ao mesmo tempo, tornar o processo de autovácuo mais agressivo, aumentando o parâmetro autovacuum_cost_limit e diminuindo o parâmetro autovacuum_cost_delay. Você também pode aumentar autovacuum_max_workers do padrão de 3 para 4 ou 5.

Erros de falta de memória

Valores maintenance_work_mem excessivamente agressivos podem periodicamente causar erros de falta de memória no sistema. Entenda a RAM disponível no servidor antes de alterar o maintenance_work_mem parâmetro.

O autovácuo é muito perturbador

Se o autovacuum consumir muitos recursos, tente as seguintes ações:

Parâmetros de vácuo automático

Avalie os parâmetros autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limite autovacuum_max_workers. A configuração inadequada dos parâmetros de vácuo automático pode levar a cenários em que o autovácuo se torna muito perturbador.

Se o autovácuo for muito perturbador, considere as seguintes ações:

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

Demasiados trabalhadores de autovácuo

Aumentar o número de trabalhadores de autovácuo não aumenta a velocidade do vácuo. Não use um grande número de trabalhadores de autovácuo.

Aumentar o número de trabalhadores de autovácuo resulta em mais consumo de memória. Dependendo do valor de maintenance_work_mem, pode causar degradação do desempenho.

Cada processo de trabalho de autovácuo recebe apenas (1/autovacuum_max_workers) do total autovacuum_cost_limit, portanto, ter um alto número de trabalhadores faz com que cada um vá mais devagar.

Se aumentar o número de trabalhadores, aumente autovacuum_vacuum_cost_limit e/ou diminua autovacuum_vacuum_cost_delay para tornar o processo de vácuo mais rápido.

No entanto, se você definir o parâmetro no nível de tabela com os parâmetros autovacuum_vacuum_cost_delay ou autovacuum_vacuum_cost_limit, os processos em execução nessas tabelas não serão considerados no algoritmo de balanceamento [autovacuum_cost_limit/autovacuum_max_workers].

Proteção envolvente de identificação de transação de vácuo automático (TXID)

Quando um banco de dados encontra a proteção de wraparound do ID de transação, aparece uma mensagem de erro como a seguinte:

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

O problema de encapsulamento ocorre quando o banco de dados não é aspirado ou quando o autovacuum não remove muitas tuplas mortas.

Possíveis razões para esse problema incluem os seguintes motivos:

Carga de trabalho pesada

Uma carga de trabalho pesada causa muitas tuplas mortas em um curto período, dificultando a recuperação do autovácuo. As tuplas mortas no sistema se somam ao longo de um período, levando à degradação do desempenho da consulta e levando à situação de wraparound. Uma razão 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ão acompanhando um servidor ocupado.

Transações de longa duração

Qualquer transação de longa duração no sistema não permite que o autovacuum remova tuplas mortas. Eles são um bloqueador para o processo de vácuo. A remoção das transações de longa duração libera tuplas mortas para exclusão quando o vácuo automático é executado.

Transações de longa duração podem ser detetadas 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;

Declarações preparadas

Se houver instruções preparadas que não são confirmadas, elas impedem o autovacuum de remover tuplas mortas. A consulta a seguir ajuda a encontrar instruções preparadas não confirmadas:

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

Use COMMIT PREPARADO ou ROLLBACK PREPARADO para confirmar ou reverter essas declarações.

Slots de replicação não utilizados

Os slots de replicação não utilizados impedem que o autovacuum reivindique tuplas mortas. 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 entrar em proteção wraparound de ID de transação, verifique se há bloqueadores, conforme mencionado anteriormente, e remova os bloqueadores manualmente para que o autovacuum continue e seja concluído. Você também pode aumentar a velocidade do autovacuum definindo autovacuum_cost_delay como 0 e aumentando o autovacuum_cost_limit para um valor maior que 200. No entanto, as alterações a estes parâmetros não se aplicam aos trabalhadores de vácuo automático existentes. Reinicie o banco de dados ou mate os trabalhadores existentes manualmente para aplicar alterações de parâmetro.

Requisitos específicos da tabela

Você pode definir parâmetros de vácuo automático para tabelas individuais. Essas configurações são especialmente importantes para mesas pequenas e grandes. Por exemplo, para uma tabela pequena que contém apenas 100 linhas, o autovacuum aciona a operação VACUUM quando 70 linhas mudam (conforme calculado anteriormente). Se atualizar esta tabela com frequência, poderá ver centenas de operações de vácuo automático por dia. Essas operações impedem que o autovácuo mantenha outras tabelas onde a porcentagem de alterações não é tão relevante. Como alternativa, uma tabela contendo um bilhão de linhas precisa alterar 200 milhões de linhas para acionar operações de autovácuo. A definição de parâmetros de vácuo automático previne adequadamente tais cenários.

Para definir as configurações de vácuo automático para cada tabela, altere os parâmetros do servidor conforme mostrado nos exemplos a seguir:

    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);
    -- For PostgreSQL 16 and later:
    ALTER TABLE <table name> SET (vacuum_buffer_usage_limit = 'xx MB');

Cargas de trabalho somente inserção

Nas versões 13 e anteriores do PostgreSQL, o autovacuum não é executado em tabelas com uma carga de trabalho de apenas inserção, pois não há tuplas mortas nem espaço livre que precise ser recuperado. No entanto, a análise automática é executada para cargas de trabalho somente de inserção, uma vez que há novos dados. As desvantagens desse comportamento são:

• O mapa de visibilidade das tabelas não é atualizado e, portanto, o desempenho da consulta, especialmente onde há verificações apenas de índice, começa a sofrer com o tempo.
• O banco de dados pode ser executado em proteção wraparound de ID de transação.
• Os bits de dica não estão definidos.

Soluções

PostgreSQL versões 13 e anteriores

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

Para obter orientação, consulte considerações especiais sobre como usar pg_cron no Banco de Dados do Azure para PostgreSQL.

PostgreSQL 13 e versões posteriores

O Autovacuum é executado em tabelas com uma carga de trabalho somente de inserção. Dois parâmetros de servidor, autovacuum_vacuum_insert_threshold e autovacuum_vacuum_insert_scale_factor, ajudam a definir quando o autovacuum pode ser acionado em tabelas de apenas inserção.

Guias de resolução de problemas

O servidor flexível do Banco de Dados do Azure para PostgreSQL fornece guias de solução de problemas no portal que ajudam a monitorar o inchaço no banco de dados ou no nível do esquema individual e identificar possíveis bloqueadores para o processo de vácuo automático.

Dois guias de solução de problemas estão disponíveis:

• Monitoramento de vácuo automático - Use este guia para monitorar o inchaço no banco de dados ou no nível do esquema individual.
• Bloqueadores de vácuo automático e wraparound - Este guia ajuda a identificar potenciais bloqueadores de vácuo automático e fornece informações sobre a distância entre os bancos de dados no servidor e situações de emergência.

Os guias de solução de problemas também compartilham recomendações para mitigar possíveis problemas. Para obter informações sobre como configurar e usar os guias de solução de problemas, consulte os guias de solução de problemas de configuração.

Terminando o processo de vácuo automático: pg_signal_autovacuum_worker função

O autovacuum é um processo em segundo plano importante porque ajuda com o armazenamento eficiente e a manutenção do desempenho no banco de dados. No processo normal de vácuo automático, ele cancela-se após o deadlock_timeout. Se um usuário executar uma instrução DDL em uma tabela, o usuário pode ter que esperar até o deadlock_timeout intervalo. O Autovacuum não permite executar leituras ou gravações na tabela solicitadas por diferentes solicitações de conexão, aumentando a latência na transação.

Introduzimos uma nova função pg_signal_autovacuum_worker do PostgreSQL, que permite que membros não superusuários encerrem uma tarefa de vácuo automático em andamento. A nova função ajuda os usuários a obter acesso seguro e controlado ao processo de vácuo automático. Os não-superusuários podem cancelar o processo de vácuo automático assim que receberem a pg_signal_autovacuum_worker função usando o pg_terminate_backend comando. A função pg_signal_autovacuum_worker está disponível no Banco de Dados do Azure para PostgreSQL nas versões 15 e posteriores do PostgreSQL.

Abordagem recomendada para trabalhadores com autovácuo repetitivo

Em cenários raros, como o autovácuo anti-wraparound, os trabalhadores podem reiniciar imediatamente após o término porque são essenciais para evitar o esgotamento do ID da transação. Para minimizar conflitos repetidos, siga estes passos:

• Coloque em fila a operação DDL antes do encerramento do sistema.

  • Sessão 1: Prepare e execute a instrução DDL.

  • Sessão 2: Encerrar o processo de autovácuo.

    Importante

    Essas duas etapas devem ser executadas consecutivamente. Se a instrução DDL permanecer bloqueada por muito tempo, poderá manter bloqueios e impedir outras operações DML no servidor.

• Termine o autovacuum e execute DDL: Se o DDL tiver de ser executado imediatamente:

  • Encerre o processo de autovácuo usando pg_terminate_backend().
  • Execute a instrução DDL logo após o término.

Passos para evitar conflitos repetidos:

1. Conceder função ao usuário

   GRANT pg_signal_autovacuum_worker TO app_user;
   

   1. Identificar o ID do processo de vácuo automático

   SELECT pid, query FROM pg_stat_activity WHERE query LIKE '%autovacuum%' and pid!=pg_backend_pid();
   

2. Terminar o autovácuo

   SELECT pg_terminate_backend(<pid>);
   

3. Execute a instrução DDL imediatamente

   ALTER TABLE my_table ADD COLUMN new_col TEXT;
   

Recomendações do Azure Advisor

As recomendações do Consultor do Azure identificam proativamente se um servidor tem uma alta taxa de inchaço ou se o servidor está se aproximando de um cenário de encapsulamento de transação. Você também pode criar alertas do Azure Advisor para as recomendações.

As recomendações são as seguintes:

• Alta taxa de inchaço: uma alta taxa de inchaço pode afetar o desempenho do servidor de várias maneiras. Um problema significativo é que o PostgreSQL Engine Optimizer pode ter dificuldades para selecionar o melhor plano de execução, levando a um desempenho de consulta degradado. Portanto, uma recomendação é acionada quando a porcentagem de inchaço em um servidor atinge um determinado limite para evitar esses problemas de desempenho.

• Encapsulamento de transações: este 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, resultando no servidor se tornando em modo somente leitura. Assim, uma recomendação é acionada quando o servidor ultrapassa o limite de 1 bilhão de transações.

Conteúdos relacionados

• Compressão total usando pg_repack no Azure Database PostgreSQL
• Solucionar problemas de alta utilização da CPU no Banco de Dados do Azure para PostgreSQL
• Solucionar problemas de alta utilização de memória no Banco de Dados do Azure para PostgreSQL
• Solucionar problemas de alta utilização de IOPS no Banco de Dados do Azure para PostgreSQL
• Solucionar problemas e identificar consultas de execução lenta no Banco de Dados do Azure para PostgreSQL
• Parâmetros de servidor no Banco de Dados do Azure para PostgreSQL