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 inchaço do banco de dados, bloqueadores de autovácuo. Ele também fornece informações sobre a distância que o banco de dados está de situações de emergência ou de envolvimento.

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:

VÁCUO - Libera espaço em disco removendo tuplas mortas.
ANALYZE - Coleta estatísticas para ajudar o Otimizador PostgreSQL a escolher os melhores caminhos de execução para consultas.

Para garantir que o autovacuum funcione corretamente, o parâmetro do servidor de autovacuum deve ser sempre definido 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 e, se nenhuma for encontrada, o autovacuum descarta a página. Quando o autovácuo encontra tuplas mortas, ele as remove. O custo baseia-se nos seguintes elementos:

Parâmetro	Description
`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 de buscar uma página que não esteja em buffers compartilhados. O valor padrão é definido como 10.
`vacuum_cost_page_dirty`	Custo de escrever em uma página quando tuplas mortas são encontradas nela. O valor padrão é definido como 20.

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

Parâmetro	Description
`autovacuum_vacuum_cost_limit`	A quantidade de trabalho que o autovácuo faz de uma só vez.
`autovacuum_vacuum_cost_delay`	Número de milissegundos que o `autovacuum_vacuum_cost_limit` autovacuum está adormecido depois de atingir o limite de custo especificado pelo parâmetro.

Em todas as versões atualmente suportadas 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 regular vacuum_cost_limit, que por padrão, é 200).

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

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

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á alcançando a atividade da tabela:

Parâmetro	Description
`dead_pct`	Percentagem de tuplas mortas quando comparadas com tuplas vivas.
`last_autovacuum`	A data da última vez que a mesa foi autoaspirada.
`last_autoanalyze`	A data da última vez que a tabela foi analisada automaticamente.

Acionamento do autovácuo

Uma ação de autovácuo ( ANALYZE ou VACUUM) é acionada quando o número de tuplas mortas excede um número específico que depende de dois fatores: a contagem total de linhas em uma tabela, mais um limite fixo. ANALYZE, por padrão, é acionado quando 10% da tabela mais 50 linhas mudam, enquanto VACUUM é acionado quando 20% da tabela mais 50 linhas mudam. Como o limiar VACUUM é duas vezes maior que o limiar ANALYZE, ANALYZE é acionado mais cedo do que VACUUM. Para versões >PG =13; ANALYZE por padrão, aciona quando 20% da tabela mais 1000 linhas são inseridas.

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 versões >PG = 13)
Autovácuo = autovacuum_vacuum_scale_fator * tuplas + 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 são acionados:

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

Analise gatilhos depois que 60 linhas forem alteradas em uma tabela e Acionadores de vácuo quando 70 linhas forem alteradas em uma tabela.

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

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

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

Parâmetro	Description
`autovacuum_analyze_scale_factor`	Porcentagem de inserções/atualizações/exclusões que aciona ANALYZE na tabela.
`autovacuum_analyze_threshold`	Especifica o número mínimo de tuplas inseridas/atualizadas/excluídas para ANALISAR uma tabela.
`autovacuum_vacuum_insert_scale_factor`	Porcentagem de inserções que aciona ANLYZE na tabela.
`autovacuum_vacuum_insert_threshold`	Especifica o número mínimo de tuplas inseridas para ANALISAR uma tabela.
`autovacuum_vacuum_scale_factor`	Percentagem de atualizações/eliminações que aciona o VACUUM na tabela.

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;

Nota

A consulta não leva em consideração que o autovacuum pode ser configurado por tabela usando o comando DDL "alter table".

Problemas comuns de autovácuo

Analise a seguinte lista de possíveis 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 realiza 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 do vácuo automático é o mesmo valor do parâmetro vacuum_cost_limit, que assume como padrão 200. vacuum_cost_limit é o custo de um vácuo manual.

Se autovacuum_vacuum_cost_limit estiver definido como -1, então o autovacuum usa o vacuum_cost_limit parâmetro, mas se autovacuum_vacuum_cost_limit ele próprio estiver definido como maior do que -1 então autovacuum_vacuum_cost_limit o parâmetro é considerado.

Caso o autovacuum não esteja acompanhando, os seguintes parâmetros podem ser alterados:

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

Nota

O autovacuum_vacuum_cost_limit valor é distribuído proporcionalmente entre os trabalhadores de autovácuo em execução, de modo que, se houver mais de um, a soma dos limites para cada trabalhador não exceda o autovacuum_vacuum_cost_limit valor do parâmetro.
autovacuum_vacuum_scale_factor é outro parâmetro que pode desencadear vácuo em uma mesa 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 individuais da tabela.

Autovacuum em funcionamento constante

A execução contínua do autovacuum pode afetar a utilização da CPU e da E/S no servidor. Aqui estão algumas das possíveis razões:

`maintenance_work_mem`

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

Se maintenance_work_mem estiver baixo, ele pode 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 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].

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

Erros de falta de memória

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

O autovácuo é muito perturbador

Se o autovacuum estiver consumindo mais recursos, as seguintes ações podem ser feitas:

Parâmetros de vácuo automático

Avalie os parâmetros autovacuum_vacuum_cost_delay, autovacuum_vacuum_cost_limit, 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 definido acima do padrão de 200.
Reduza o número de autovacuum_max_workers se definido maior do que o 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. Ter um número elevado de trabalhadores de autovácuo não é recomendado.

Aumentar o número de trabalhadores de autovácuo resulta em mais consumo de memória e, dependendo do valor de , pode causar degradação do maintenance_work_mem 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 o número de trabalhadores for aumentado, autovacuum_vacuum_cost_limit também deve ser aumentado e/ou autovacuum_vacuum_cost_delay deve ser diminuído para tornar o processo de vácuo mais rápido.

No entanto, se definirmos o parâmetro no nível autovacuum_vacuum_cost_delay da tabela ou autovacuum_vacuum_cost_limit parâmetros, os trabalhadores em execução nessas tabelas estão isentos de serem 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 é executado em proteção wraparound de ID de transação, uma mensagem de erro como o seguinte erro pode 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.

Nota

Esta mensagem de erro é uma lacuna de longa data. Normalmente, não é necessário mudar para o modo de utilizador único. Em alternativa, pode executar os comandos VACUUM necessários e realizar a otimização para que o VACUUM seja executado rapidamente. Embora não seja possível executar qualquer 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 não removidas pelo autovácuo.

Possíveis razões para esse problema podem ser qualquer uma das seguintes:

Carga de trabalho pesada

A carga de trabalho pode causar muitas tuplas mortas em um curto período que dificulta 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 tuplas mortas sejam removidas enquanto o autovacuum está em execução. 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 declarações preparadas que não sejam cometidas, elas evitarão que tuplas mortas sejam removidas.
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 PREPARED ou ROLLBACK PREPARED 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

Os parâmetros de vácuo automático podem ser definidos para tabelas individuais. É especialmente importante 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 são alteradas (conforme calculado anteriormente). Se essa tabela for atualizada com frequência, você poderá ver centenas de operações de autovácuo por dia, impedindo que o autovacuum mantenha outras tabelas nas quais a porcentagem de alterações não é tão significativa. 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 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 autovacuum não é executado em tabelas com uma carga de trabalho somente de inserção, pois não há tuplas mortas e nenhum 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 disso 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

Versões <do Postgres = 13

Usando a extensão pg_cron , um trabalho cron pode ser configurado 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 passo a passo usando o pg_cron, revise Extensões.

Postgres 13 e versões superiores

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

Guias de resolução de problemas

Usando os guias de solução de problemas de recursos disponíveis no portal de 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, juntamente com a identificação de bloqueadores potenciais para o processo de vácuo automático. Dois guias de solução de problemas estão disponíveis: o primeiro é o monitoramento de vácuo automático que pode ser usado para monitorar o inchaço no banco de dados ou no nível do esquema individual. O segundo guia de solução de problemas são os bloqueadores de vácuo automático e wraparound, que ajudam a identificar potenciais bloqueadores de autovácuo. Ele também fornece informações sobre a distância que os bancos de dados no servidor estão de wraparound ou situação de emergência. Os guias de solução de problemas também compartilham recomendações para mitigar possíveis problemas. Como configurar os guias de solução de problemas para usá-los siga os guias de solução de problemas de configuração.

Recomendações do Assistente do Azure

As recomendações do Consultor do Azure são uma maneira proativa de identificar se um servidor tem uma alta taxa de inchaço ou se o servidor está se aproximando do 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, fazendo com que o servidor se torne somente leitura. Assim, uma recomendação é acionada quando vemos que o servidor ultrapassa o limite de 1 bilhão de transações.

Partilhe as suas sugestões e bugs com a equipa de produto da Base de Dados do Azure para PostgreSQL.

Partilhar via

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

O que é o autovácuo

Componentes internos de vácuo automático

Monitorize o autovácuo

Acionamento do autovácuo

Problemas comuns de autovácuo

Não acompanhar o servidor ocupado

Autovacuum em funcionamento constante

`maintenance_work_mem`

Grande número de bases de dados

Erros de falta de memória

O autovácuo é muito perturbador

Parâmetros de vácuo automático

Demasiados trabalhadores de autovácuo

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

Carga de trabalho pesada

Transações de longa duração

Declaraçõ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 superiores

Guias de resolução de problemas

Recomendações do Assistente do Azure

Comentários

Recursos adicionais

Partilhar via

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

O que é o autovácuo

Componentes internos de vácuo automático

Monitorize o autovácuo

Acionamento do autovácuo

Problemas comuns de autovácuo

Não acompanhar o servidor ocupado

Autovacuum em funcionamento constante

maintenance_work_mem

Grande número de bases de dados

Erros de falta de memória

O autovácuo é muito perturbador

Parâmetros de vácuo automático

Demasiados trabalhadores de autovácuo

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

Carga de trabalho pesada

Transações de longa duração

Declaraçõ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 superiores

Guias de resolução de problemas

Recomendações do Assistente do Azure

Conteúdos relacionados

Comentários

Recursos adicionais

`maintenance_work_mem`