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

Este artigo fornece uma visão geral do recurso autovacuum para o Banco de Dados do Azure para PostgreSQL e os guias de solução de problemas do recurso, disponíveis para monitorar o inchaço do banco de dados e os bloqueadores de autovacuum. Também traz informações sobre a distância que o banco de dados está em relação a uma 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 – Recupera o espaço nos arquivos do banco de dados removendo tuplas mortas e marcando esse espaço como reutilizável pelo PostgreSQL. Isso não necessariamente reduz o tamanho físico dos arquivos de banco de dados em disco. Para devolver espaço ao sistema operacional, use operações que reescrevam a tabela (por exemplo, VACUUM FULL ou pg_repack), que têm considerações adicionais, como bloqueios exclusivos 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 vácuo automático funcione corretamente, defina o parâmetro do servidor de vácuo automático como ON. 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 lê páginas à procura de tuplas mortas. Se ele não encontra nenhuma tupla morta, o vácuo automático descarta a página. Quando o vácuo automático encontra tuplas inativas, ele as remove. O custo é baseado nos seguintes parâmetros:

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

Em todas as versões atualmente compatíveis do PostgreSQL, o valor autovacuum_vacuum_cost_limit padrão é 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 é autovacuum_vacuum_cost_delay 2 milissegundos nas versões 12 e posteriores do PostgreSQL (eram 20 milissegundos na versão 11).

Limite de uso do buffer (PostgreSQL 16+)

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

Esse parâmetro ajuda a impedir que o VACUUM e o vácuo automático removam 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 shared_bufferse você pode configurá-lo para equilibrar o desempenho do vácuo com as necessidades de operações regulares de banco de dados.

Limite máximo para o vácuo automático (PostgreSQL 18+)

A partir do PostgreSQL versão 18, você pode usar o parâmetro autovacuum_vacuum_max_threshold para definir um limite superior no número de atualizações ou exclusões de tupla que disparam o vácuo automático.

Esse parâmetro é particularmente útil para tabelas grandes em que o gatilho baseado em fator de escala padrão pode fazer com que o vácuo automático aguarde muito tempo antes de ser executado.

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

O Banco de Dados do Azure para PostgreSQL fornece as seguintes métricas para monitorar o vácuo automático.

As métricas de vácuo automático podem ser usadas para monitorar e ajustar o desempenho do vácuo automático no servidor flexível do Azure Database para PostgreSQL. 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 pode dividir e filtrar dados de métricas usando a dimensão DatabaseName.

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

• As métricas de vácuo automático estão desabilitadas 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, a 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 do 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 da 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 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 você a determinar se o vácuo automático está sendo atualizado conforme a atividade da tabela:

Disparar a limpeza automática

Uma ação de vácuo automático (ANALYZE ou VACUUM) é disparada quando o número de tuplas mortas excede um número específico. Esse número depende de dois fatores: a contagem total de linhas em uma tabela, além de um limite fixo. O ANALYZE dispara por padrão quando ocorrem 10% da tabela mais 50 alterações de linha, enquanto VACUUM dispara quando ocorrem 20% da tabela mais 50 alterações de linha. Como o limite VACUUM é duas vezes mais alto que o limite ANALYZE , ANALYZE dispara anteriormente a VACUUM.

Para as versões 13 e posteriores do PostgreSQL, o ANALYZE dispara 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:

• Análise automática = autovacuum_analyze_scale_factor * tuplas + autovacuum_analyze_threshold ou autovacuum_vacuum_insert_scale_factor * tuplas + autovacuum_vacuum_insert_threshold (para o PostgreSQL versões 13 e posteriores)
• Autovacuum = autovacuum_vacuum_scale_factor * tuples + autovacuum_vacuum_threshold

Por exemplo, se você tiver uma tabela com 100 linhas, as seguintes equações mostrarão quando as ações de análise e limpeza são disparadas:

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

Gatilhos de ANALYZE após 60 linhas serem alteradas em uma tabela e gatilhos de VACUUM quando 70 linhas forem alteradas em uma tabela.

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

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

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

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;

Problemas comuns de vácuo automático

Examine a lista a seguir de problemas comuns com o processo de vácuo automático.

Não acompanhar o servidor ocupado

O processo de vácuo 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. 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 é 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 vácuo automático usará o vacuum_cost_limit parâmetro. Se você definir autovacuum_vacuum_cost_limit como um valor maior que -1, o vácuo automático usará o autovacuum_vacuum_cost_limit parâmetro.

Se o autovacuum não está sendo eficiente, considere alterar os seguintes parâmetros:

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

Se o vácuo automático for executado continuamente, ele poderá afetar a utilização de CPU e E/S no servidor. Confira alguns possíveis motivos:

maintenance_work_mem

O daemon de vácuo automático usa autovacuum_work_mem, que é definido -1 como por padrão. Essa configuração padrão significa autovacuum_work_mem usar o mesmo valor que o maintenance_work_mem parâmetro. Este artigo considera que autovacuum_work_mem esteja definido como -1 e que o daemon do vácuo automático use maintenance_work_mem.

Se maintenance_work_mem for baixo, você poderá aumentar 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 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].

Se houver mais bancos de dados em um cluster, aumente autovacuum_naptime. Ao mesmo tempo, torne o processo de vácuo automático mais agressivo aumentando o autovacuum_cost_limit e diminuindo os parâmetros autovacuum_cost_delay. Você também pode aumentar 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 de memória insuficiente no sistema. Entenda a RAM disponível no servidor antes de alterar o maintenance_work_mem parâmetro.

O vácuo automático é muito disruptivo

Se o vácuo automático consumir muitos recursos, tente as seguintes ações:

Parâmetros de vácuo automático

Avaliar os parâmetros autovacuum_vacuum_cost_delaye autovacuum_vacuum_cost_limitautovacuum_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 caso os defina acima do padrão de 200.
• Reduza o número de autovacuum_max_workers caso o tenha definido maior do que o 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 use um grande número de trabalhadores de vácuo automático.

Aumentar o número de trabalhos de vácuo automático resulta em mais consumo de memória. Dependendo do valor de maintenance_work_mem, isso pode 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 você 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 na tabela autovacuum_vacuum_cost_delay ou parâmetros autovacuum_vacuum_cost_limit, os trabalhos 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, você poderá ver 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 passa por vácuo ou quando o vácuo automático não remove um número significativo de tuplas mortas.

Os possíveis motivos para esse problema incluem os seguintes motivos:

Carga de trabalho pesada

Uma carga de trabalho pesada gera muitas tuplas mortas em um breve período, dificultando a atualização do vácuo automático. 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 permite que o vácuo automático remova tuplas mortas. 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 são confirmadas, elas impedem que o vácuo automático remova as tuplas mortas. 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

Você pode definir parâmetros de vácuo automático para tabelas individuais. Essas configurações são especialmente importantes 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 você atualizar frequentemente esta tabela, poderá ver centenas de operações de vácuo automático por dia. Essas operações impedem que o autovacuum mantenha outras tabelas em que o percentual de mudanças não seja 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 as configurações de vácuo automático para cada tabela, altere os parâmetros do servidor, conforme mostrado nos 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);
    -- For PostgreSQL 16 and later:
    ALTER TABLE <table name> SET (vacuum_buffer_usage_limit = 'xx MB');

Cargas de trabalho somente inserção

No PostgreSQL versões 13 e anteriores, 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 desse comportamento 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 13 e anteriores do PostgreSQL

Usando a extensão pg_cron , você pode configurar um trabalho cron 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, consulte considerações especiais sobre como usar pg_cron no Banco de Dados do Azure para PostgreSQL.

PostgreSQL 13 e versões posteriores

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

Guias de solução de problemas

O servidor flexível do Azure Database para PostgreSQL fornece guias de solução de problemas no portal que ajudam a monitorar o inchaço tanto no nível do banco de dados quanto no nível de esquemas individuais e a identificar possíveis bloqueadores para o processo de autovacuum.

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

• Monitoramento de vácuo automático: use este guia para monitorar a sobrecarga no banco de dados ou no esquema individual.
• Bloqueadores e encapsulamento de vácuo automático: esse guia ajuda a identificar possíveis bloqueadores de vácuo automático e traz informações sobre a distância dos bancos de dados no servidor em relação a situações de encapsulamento e emergência.

Os guias de solução de problemas também compartilham recomendações para atenuar 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 instalação.

Encerramento do processo de vácuo automático: pg_signal_autovacuum_worker role

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

Apresentamos uma nova função pg_signal_autovacuum_worker do PostgreSQL, que permite ao membro que não é superusuário encerrar 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 depois de receberem a função pg_signal_autovacuum_worker usando o comando pg_terminate_backend. 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 trabalhos repetitivos de vácuo automático

Em cenários raros, como o vácuo automático de anti-encapsulamento, os trabalhos podem reiniciar imediatamente após o encerramento porque são críticos para evitar o esgotamento da ID de transação. Para minimizar conflitos repetidos, siga estas etapas:

• Enfileire a operação de DDL antes do encerramento:

  • Sessão 1: Preparar e executar a instrução DDL.

  • Sessão 2: Encerrar o processo de vácuo automático.

    Importante

    Essas duas etapas devem ser executadas em sequência. Se a instrução DDL permanecer bloqueada por muito tempo, ela poderá reter bloqueios e bloquear outras operações de DML no servidor.

• Encerrar o vácuo automático e executar o DDL: se o DDL precisar ser executado imediatamente:

  • Encerre o processo de vácuo automático usando pg_terminate_backend().
  • Execute a instrução DDL logo após o encerramento.

Etapas 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. Encerrar o vácuo automático

   SELECT pg_terminate_backend(<pid>);
   

3. Executar instrução DDL imediatamente

   ALTER TABLE my_table ADD COLUMN new_col TEXT;
   

Recomendações do Assistente do Azure

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

As recomendações são:

• Alta taxa de bloat: uma alta taxa de bloat 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 mau desempenho 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 se torne somente leitura. Portanto, uma recomendação é disparada quando o servidor ultrapassa o limite de 1 bilhão de transações.

Conteúdo relacionado

• Vácuo completo usando pg_repack no Banco de Dados do Azure para 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