Deixe-me lembrá-lo de que começamos com problemas relacionados ao isolamento , fizemos uma digressão sobre a organização de dados em um nível baixo , conversamos detalhadamente sobre as versões de linha e como os instantâneos são obtidos a partir das versões.

Em seguida, analisamos a limpeza na página (e as atualizações HOT), a limpeza regular , mas hoje a limpeza automática.

Limpeza automática (vácuo automático)

Já dissemos que a limpeza comum em condições normais (quando ninguém mantém o horizonte de transação por um longo tempo) deve lidar com seu trabalho. A questão é com que frequência chamá-lo.

Se você limpar uma mesa de troca muito raramente, ela aumentará do que você gostaria. Além disso, para a próxima limpeza, pode levar várias passagens pelos índices se muitas alterações forem acumuladas.

Se você limpar a tabela com muita frequência, em vez de um trabalho útil, o servidor estará constantemente envolvido na manutenção - também não é bom.

Observe que iniciar uma limpeza programada regular não resolve o problema, porque a carga pode mudar com o tempo. Se a tabela começar a ser atualizada mais ativamente, ela deverá ser limpa com mais frequência.

A limpeza automática é exatamente o mecanismo que permite iniciar a limpeza, dependendo da atividade de alterações nas tabelas.

Quando a limpeza automática é ativada (parâmetro de configuração de autovacuum ), o processo do iniciador de autovacuum está sempre presente no sistema, que planeja funcionar, e os fluxos de trabalho do trabalhador de autovacuum estão envolvidos na limpeza real, várias instâncias que podem funcionar em paralelo.

O processo do iniciador de vácuo automático compila uma lista de bancos de dados nos quais há alguma atividade. A atividade é determinada pelas estatísticas e, para ser coletada, o parâmetro track_counts deve ser definido. Nunca desligue o autovacuum e track_counts , caso contrário, a limpeza automática não funcionará.

Uma vez em autovacuum_naptime, o processo do iniciador de autovacuum inicia (usando o processo postmaster) um fluxo de trabalho para cada banco de dados na lista. Em outras palavras, se houver alguma atividade no banco de dados, os fluxos de trabalho entrarão nele com um intervalo de autovacuum_naptime . Para fazer isso, se houver vários bancos de dados ativos (N pieces), os processos de trabalho serão iniciados N vezes mais que o autovacuum_naptime . Mas, ao mesmo tempo, o número total de fluxos de trabalho trabalhando simultaneamente é limitado pelo parâmetro autovacuum_max_workers .

Após o início, o fluxo de trabalho se conecta ao banco de dados especificado por ele e começa criando a lista:

• todas as tabelas, visualizações materializadas e tabelas de brinde que precisam ser limpas,
• todas as tabelas e representações materializadas que requerem análise (as tabelas de brinde não são analisadas porque são sempre acessadas pelo índice).

Além disso, o fluxo de trabalho, por sua vez, limpa e / ou analisa os objetos selecionados e termina quando a limpeza é concluída.

Se o processo não tiver concluído todo o trabalho pretendido para autovacuum_naptime , o processo do iniciador de autovacuum enviará outro fluxo de trabalho para o mesmo banco de dados e eles trabalharão juntos. “Juntos” significa simplesmente que o segundo processo criará sua lista de tabelas e a seguirá. Assim, diferentes tabelas serão processadas em paralelo, mas no nível de uma tabela não há paralelismo - se um dos processos de trabalho já estiver trabalhando na tabela, o outro a ignorará e seguirá em frente.

Há uma discussão sobre a necessidade de processamento paralelo há muito tempo, mas o patch ainda não foi adotado.

Agora, vamos dar uma olhada no que "requer limpeza" e "requer análise".

Quais mesas precisam de limpeza

Acredita-se que a limpeza seja necessária se o número de versões "mortas", ou seja, irrelevantes, de cadeias exceder um valor limite definido. O número de versões mortas é constantemente coletado pelo coletor de estatísticas e armazenado na tabela pg_stat_all_tables. E o limite é definido por dois parâmetros:

• autovacuum_vacuum_threshold define o valor absoluto (em partes),
• autovacuum_vacuum_scale_factor determina a proporção de linhas em uma tabela.

A fórmula final é: a limpeza é necessária se pg_stat_all_tables.n_dead_tup> = autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * pg_class.reltupes.

As configurações padrão definem autovacuum_vacuum_threshold = 50 e
autovacuum_vacuum_scale_factor = 0.2. O principal parâmetro aqui, é claro, é autovacuum_vacuum_scale_factor - é importante para tabelas grandes (a saber, possíveis problemas estão associados a elas). O valor de 20% parece ser muito alto, provavelmente precisará ser reduzido significativamente.

Os valores ideais dos parâmetros podem variar para diferentes tabelas, dependendo do tamanho e da natureza das alterações. Faz sentido estabelecer, no geral, valores adequados e - se necessário - configurar especialmente os parâmetros no nível de algumas tabelas usando os parâmetros de armazenamento:

• autovacuum_vacuum_threshold e toast.autovacuum_vacuum_threshold ,
• autovacuum_vacuum_scale_factor e toast.autovacuum_vacuum_scale_factor .

Para não ficar confuso, isso deve ser feito apenas para um pequeno número de tabelas que se destacam, entre outras, pelo volume ou pela intensidade das alterações e somente se os valores definidos globalmente não forem adequados.

Além disso, a limpeza automática pode ser desativada no nível da mesa (embora seja difícil pensar em uma razão pela qual isso seria necessário):

• autovacuum_enabled e toast.autovacuum_enabled .

Por exemplo, na última vez em que criamos uma tabela vac com a limpeza automática desativada, para - para fins de demonstração - gerenciar a limpeza manual. O parâmetro de armazenamento pode ser alterado da seguinte maneira:

=> ALTER TABLE vac SET (autovacuum_enabled = off); 

Para formalizar todas as opções acima, criaremos uma exibição mostrando quais tabelas atualmente precisam ser limpas. Ele usará uma função que retorna o valor atual do parâmetro, pois ele pode ser substituído no nível da tabela:

 => CREATE FUNCTION get_value(param text, reloptions text[], relkind "char") RETURNS float AS $$ SELECT coalesce( --    ,    (SELECT option_value FROM pg_options_to_table(reloptions) WHERE option_name = CASE --  toast-    WHEN relkind = 't' THEN 'toast.' ELSE '' END || param ), --      current_setting(param) )::float; $$ LANGUAGE sql; 

E aqui está a visão:

 => CREATE VIEW need_vacuum AS SELECT st.schemaname || '.' || st.relname tablename, st.n_dead_tup dead_tup, get_value('autovacuum_vacuum_threshold', c.reloptions, c.relkind) + get_value('autovacuum_vacuum_scale_factor', c.reloptions, c.relkind) * c.reltuples max_dead_tup, st.last_autovacuum FROM pg_stat_all_tables st, pg_class c WHERE c.oid = st.relid AND c.relkind IN ('r','m','t'); 

Quais tabelas precisam de análise

Com a análise automática, a situação é aproximadamente a mesma. Acredita-se que a análise seja necessária para as tabelas para as quais o número de versões alteradas (desde a última análise) das linhas excede o valor limite especificado por dois parâmetros semelhantes: pg_stat_all_tables.n_mod_since_analyze> = autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * pg_class.reltupes.

As configurações de análise automática padrão são ligeiramente diferentes: autovacuum_analyze_threshold = 50 e autovacuum_analyze_scale_factor = 0.1. Eles também podem ser definidos no nível dos parâmetros de armazenamento para tabelas individuais:

• autovacuum_analyze_threshold ,
• autovacuum_analyze_scale_factor

Como as tabelas de brinde não são analisadas, não há parâmetros correspondentes para elas.

Vamos criar uma visão para análise:

 => CREATE VIEW need_analyze AS SELECT st.schemaname || '.' || st.relname tablename, st.n_mod_since_analyze mod_tup, get_value('autovacuum_analyze_threshold', c.reloptions, c.relkind) + get_value('autovacuum_analyze_scale_factor', c.reloptions, c.relkind) * c.reltuples max_mod_tup, st.last_autoanalyze FROM pg_stat_all_tables st, pg_class c WHERE c.oid = st.relid AND c.relkind IN ('r','m'); 

Exemplo

Para experimentos, definimos os seguintes valores de parâmetro:

 => ALTER SYSTEM SET autovacuum_naptime = '1s'; --     => ALTER SYSTEM SET autovacuum_vacuum_scale_factor = 0.03; -- 3% => ALTER SYSTEM SET autovacuum_vacuum_threshold = 0; => ALTER SYSTEM SET autovacuum_analyze_scale_factor = 0.02; -- 2% => ALTER SYSTEM SET autovacuum_analyze_threshold = 0; 

 => SELECT pg_reload_conf(); 

  pg_reload_conf ---------------- t (1 row) 

Agora crie uma tabela semelhante à que usamos na última vez e insira mil linhas nela. A limpeza automática está desativada no nível da mesa e nós a ativamos. Se isso não for feito, os exemplos não serão reproduzíveis, pois a limpeza automática pode funcionar no momento errado.

 => CREATE TABLE autovac( id serial, s char(100) ) WITH (autovacuum_enabled = off); => INSERT INTO autovac SELECT g.id,'A' FROM generate_series(1,1000) g(id); 

Aqui está o que nossa exibição de limpeza mostrará:

 => SELECT * FROM need_vacuum WHERE tablename = 'public.autovac'; 

  tablename | dead_tup | max_dead_tup | last_autovacuum ----------------+----------+--------------+----------------- public.autovac | 0 | 0 | (1 row) 

Há dois pontos nos quais você deve prestar atenção. Em primeiro lugar, max_dead_tup = 0, embora 3% de 1000 linhas sejam 30 linhas. O fato é que ainda não temos estatísticas sobre a tabela, uma vez que o próprio INSERT não a atualiza. Até que nossa tabela seja analisada, os zeros permanecerão, pois pg_class.reltuples = 0. No entanto, vamos dar uma olhada na segunda visualização para análise:

 => SELECT * FROM need_analyze WHERE tablename = 'public.autovac'; 

  tablename | mod_tup | max_mod_tup | last_autoanalyze ----------------+---------+-------------+------------------ public.autovac | 1000 | 0 | (1 row) 

Como a tabela mudou (adicionou) 1000 linhas e isso é mais que zero, a análise automática deve funcionar. Verifique isto:

 => ALTER TABLE autovac SET (autovacuum_enabled = on); 

Após uma breve pausa, vemos que a tabela é analisada e, em vez de zeros em max_mod_tup, vemos as 20 linhas corretas:

 => SELECT * FROM need_analyze WHERE tablename = 'public.autovac'; 

  tablename | mod_tup | max_mod_tup | last_autoanalyze ----------------+---------+-------------+------------------------------- public.autovac | 0 | 20 | 2019-05-21 11:59:48.465987+03 (1 row) 

 => SELECT reltuples, relpages FROM pg_class WHERE relname = 'autovac'; 

  reltuples | relpages -----------+---------- 1000 | 17 (1 row) 

Vamos voltar à limpeza automática:

 => SELECT * FROM need_vacuum WHERE tablename = 'public.autovac'; 

  tablename | dead_tup | max_dead_tup | last_autovacuum ----------------+----------+--------------+----------------- public.autovac | 0 | 30 | (1 row) 

Max_dead_tup, como vemos, já foi corrigido. O segundo ponto a ser observado é dead_tup = 0. As estatísticas mostram que não há versões mortas de linhas na tabela ... e isso é verdade. Ainda não há nada para limpar em nossa mesa. Portanto, qualquer tabela usada apenas no modo somente anexado não será limpa e, portanto, o mapa de visibilidade não será atualizado para ela. E isso torna impossível o uso exclusivo de varredura de índice (varredura apenas de índice).

(Da próxima vez, veremos que a limpeza, mais cedo ou mais tarde, chegará à tabela somente de acréscimo, mas isso acontecerá muito raramente.)

Conclusão prática: se é importante usar apenas a digitalização de índice, pode ser necessário solicitar uma limpeza manual.

Agora desative a limpeza automática novamente e atualize a linha 31 - uma a mais que o valor limite.

 => ALTER TABLE autovac SET (autovacuum_enabled = off); => UPDATE autovac SET s = 'B' WHERE id <= 31; => SELECT * FROM need_vacuum WHERE tablename = 'public.autovac'; 

  tablename | dead_tup | max_dead_tup | last_autovacuum ----------------+----------+--------------+----------------- public.autovac | 31 | 30 | (1 row) 

Agora, a condição para acionar a limpeza automática está satisfeita. Ligue a limpeza automática e, após uma breve pausa, veremos que a tabela foi processada:

 => ALTER TABLE autovac SET (autovacuum_enabled = on); => SELECT * FROM need_vacuum WHERE tablename = 'public.autovac'; 

  tablename | dead_tup | max_dead_tup | last_autovacuum ----------------+----------+--------------+------------------------------- public.autovac | 0 | 30 | 2019-05-21 11:59:52.554571+03 (1 row) 

Regulamento de carga

A limpeza não bloqueia outros processos, porque funciona página por página, mas cria uma carga no sistema e pode ter um efeito perceptível no desempenho.

Regulamento para limpeza regular

Para poder controlar a intensidade da limpeza e, consequentemente, seu efeito no sistema, o processo alterna entre trabalho e expectativa. A limpeza executa aproximadamente unidades de trabalho convencionais vacuum_cost_limit e depois adormece em vacuum_cost_delay ms.

As configurações padrão configuram vacuum_cost_limit = 200, vacuum_cost_delay = 0. O último zero realmente significa que a limpeza (normal) não adormece, portanto, o valor específico de vacuum_cost_limit não desempenha nenhum papel. Isso é feito pelo motivo de que, se o administrador tiver que iniciar o VACUUM manualmente, ele provavelmente desejará executar a limpeza o mais rápido possível.

No entanto, se você ainda definir o tempo de suspensão, a quantidade de trabalho especificada em vacuum_cost_limit consistirá no custo de trabalhar com páginas no cache do buffer. Cada acesso à página é avaliado da seguinte maneira:

• se a página foi encontrada no cache do buffer, vacuum_cost_page_hit = 1;
• se não for encontrado, vacuum_cost_page_miss = 10;
• se você não conseguiu encontrá-lo e precisou empurrar a página suja para fora do buffer, vacuum_cost_page_dirty = 20.

Ou seja, com as configurações vacuum_cost_limit por padrão, 200 páginas do cache ou 20 páginas do disco ou 10 páginas com extrusão podem ser processadas de uma só vez. É claro que estes são números bastante arbitrários, mas não faz sentido selecioná-los com mais precisão.

Regulamento para limpeza automática

O controle de carga durante a limpeza automática funciona da mesma forma que na limpeza regular. Mas, para que a limpeza manual e a limpeza automática funcionem com diferentes intensidades, o autoprocessamento possui seus próprios parâmetros: autovacuum_vacuum_cost_limit e autovacuum_vacuum_vacuum_cost_delay . Se esses parâmetros receberem o valor -1, o valor de vacuum_cost_limit e / ou vacuum_cost_delay será usado .

Por padrão, autovacuum_vacuum_cost_limit = -1 (ou seja, o valor vacuum_cost_limit = 200 é usado) e autovacuum_vacuum_cost_delay = 20ms. Em equipamentos modernos com esses números, a limpeza automática funcionará muito, muito lentamente.

Na versão 12, o valor de autovacuum_vacuum_cost_delay será reduzido para 2ms, o que pode ser considerado uma primeira aproximação mais apropriada.

Além disso, deve-se notar que o limite estabelecido por esses parâmetros é comum a todos os processos de trabalho. Em outras palavras, conforme o número de processos de trabalho simultâneos muda, a carga total permanecerá constante. Portanto, se a tarefa é aumentar o desempenho da limpeza automática , ao adicionar fluxos de trabalho, vale a pena aumentar autovacuum_vacuum_cost_limit .

Uso e monitoramento de memória

Na última vez, vimos como a limpeza usa memória RAM do tamanho maintenance_work_mem para armazenar os identificadores de versão das linhas a serem limpas.

A limpeza automática faz exatamente o mesmo. Mas pode haver muitos processos simultâneos se você definir autovacuum_max_workers com um valor alto. Além disso, toda a memória é alocada imediata e completamente, e não por necessidade. Portanto, para o fluxo de trabalho de limpeza automática , você pode definir sua própria restrição usando o parâmetro autovacuum_work_mem . Por padrão, esse parâmetro é -1, ou seja, não é usado.

Como já mencionado, a limpeza pode funcionar com uma quantidade mínima de memória. Mas se os índices forem criados na tabela, um pequeno valor maintenance_work_mem poderá levar a verificações repetidas do índice. O mesmo vale para a limpeza automática. Idealmente, você deve selecionar um valor mínimo de autovacuum_work_mem no qual as verificações repetidas não ocorram.

Vimos que, para monitorar a limpeza, você pode usar o parâmetro VERBOSE (mas não pode ser especificado para limpeza automática) ou a visualização pg_stat_progress_vacuum (mas mostra apenas as informações atuais). Portanto, a principal maneira de monitorar a limpeza automática é o parâmetro log_autovacuum_min_duration , que exibe informações no log de mensagens do servidor. Por padrão, está desativado (definido como -1). Há uma razão para ativar esse parâmetro (no valor 0, informações sobre todas as partidas de limpeza automática serão exibidas) e observe os números.

Aqui está a aparência da saída:

 => ALTER SYSTEM SET log_autovacuum_min_duration = 0; => SELECT pg_reload_conf(); 

  pg_reload_conf ---------------- t (1 row) 

 => UPDATE autovac SET s = 'C' WHERE id <= 31; 

 student$ tail -n 7 /var/log/postgresql/postgresql-11-main.log 

 2019-05-21 11:59:55.675 MSK [9737] LOG: automatic vacuum of table "test.public.autovac": index scans: 0 pages: 0 removed, 18 remain, 0 skipped due to pins, 0 skipped frozen tuples: 31 removed, 1000 remain, 0 are dead but not yet removable, oldest xmin: 4040 buffer usage: 78 hits, 0 misses, 0 dirtied avg read rate: 0.000 MB/s, avg write rate: 0.000 MB/s system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s 2019-05-21 11:59:55.676 MSK [9737] LOG: automatic analyze of table "test.public.autovac" system usage: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s 

Toda a informação necessária está presente aqui.

Lembre-se de que muitas vezes você não deve aumentar o tamanho da memória, mas diminuir o limite de limpeza para que menos dados sejam processados ​​por vez.

Também pode fazer sentido monitorar o comprimento da lista de tabelas que precisam ser limpas usando as visualizações acima. Um aumento no comprimento da lista indicará que a limpeza automática não tem tempo para fazer seu trabalho e que as configurações precisam ser alteradas.

Para ser continuado .