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

Artigo
04/27/2024

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

Este artigo fornece uma visão geral do recurso de autovácuo 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 inchaço do banco de dados, bloqueadores de autovácuo e também informações sobre a distância do banco de dados em situações de emergência ou de encapsulamento.

O que é o vácuo automático

A consistência de dados interna no PostgreSQL baseia-se no mecanismo MVCC (Controle de Simultaneidade de Várias Versões), que permite que o mecanismo de banco de dados mantenha várias versões de uma linha e forneça maior simultaneidade com bloqueio mínimo entre os diferentes processos.

Os bancos de dados PostgreSQL precisam de manutenção apropriada. Por exemplo, quando uma linha é excluída, ela não é removida fisicamente. Em vez disso, a linha é marcada como "morta". Assim como para atualizações, a linha é marcada como "inativa" e uma nova versão da linha é inserida. Essas operações deixam para trás registros mortos, chamados tuplas inativas, mesmo depois da conclusão de todas as transações que podem ver essas versões. A menos que estejam limpas, as tuplas inativas permanecem consumindo espaço em disco e sobrecarregando tabelas e índices que resultam em desempenho de consulta lento.

O PostgreSQL usa um processo chamado de vácuo automático para limpar automaticamente tuplas mortas.

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:

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 inativas são encontradas nela. O valor padrão é definido como 20.

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

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 padrão para autovacuum_vacuum_cost_limit é 200 (na verdade, ele é 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:

dead_pct: porcentagem de tuplas mortas em comparação com as tuplas vivas.
last_autovacuum: a data da última vez que a tabela foi aspirada automaticamente.
last_autoanalyze: a data da última vez que a tabela foi analisada automaticamente.

Quando o PostgreSQL dispara o vácuo automático

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.

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

Autoanalyze = autovacuum_analyze_scale_factor * tuples + autovacuum_analyze_threshold
Autovacuum = autovacuum_vacuum_scale_factor * tuples + autovacuum_vacuum_threshold

Por exemplo, analise gatilhos após a alteração de 60 linhas em uma tabela que contém 100 linhas e gatilhos de vácuo quando 70 linhas são alteradas na tabela, usando as seguintes equações:

Autoanalyze = 0.1 * 100 + 50 = 60
Autovacuum = 0.2 * 100 + 50 = 70

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_scale_factor`	Padrão: `0.2`, alcance: `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.
`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 excede o valor do parâmetro autovacuum_vacuum_cost_limit

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. Os seguintes podem ser 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, ele poderá ser aumentado para até 2 GB no Banco de Dados do Azure para servidor flexível 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/Number of DBs].

É 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 muitos recursos, o seguinte poderá ser feito:

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 o seguinte:

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 ele 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, 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 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 alterarmos o nível autovacuum_vacuum_cost_delay ou autovacuum_vacuum_cost_limit da tabela, os parâmetros, os trabalhos em execução nessas tabelas serã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 solução alternativa 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 é aspirado ou há muitas tuplas mortas que não puderam ser removidas pelo vácuo automático. Os erros podem ser causados pelos seguintes motivos:

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 solução alternativa da ID da transação, verifique se há bloqueadores conforme mencionado anteriormente e remova-os manualmente para que o salvamento 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 serão aplicadas aos trabalhadores de vácuo automático 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 salvamento automático por dia. Isso impede que o vácuo automático mantenha outras tabelas nas quais o percentual de alterações não é tão grande. 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 anteriores à 13, o vácuo automático não será executado em tabelas com uma carga de trabalho somente inserção, pois se não houver atualizações ou exclusões, não haverá 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 serão definidos.

Soluções

Versões do Postgres anteriores à 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 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 de recursos, que estão disponíveis no portal do servidor flexível do Banco de Dados do Azure para PostgreSQL, é possível monitorar o inchaço no nível do banco de dados ou do esquema individual, além de identificar possíveis bloqueadores para o processo de autovácuo. 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 a solução alternativa, que ajuda a identificar possíveis bloqueadores de vácuo automático, juntamente com informações sobre o quão longe os bancos de dados no servidor estão da solução alternativa ou da situação 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.

Share 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

Quando o PostgreSQL dispara o vácuo automático

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 anteriores à 13

Postgres 13 e versões posteriores

Guias de solução de problemas

Recursos adicionais

Share 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

Quando o PostgreSQL dispara o vácuo automático

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 anteriores à 13

Postgres 13 e versões posteriores

Guias de solução de problemas

Conteúdo relacionado

Recursos adicionais

`maintenance_work_mem`