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

Na última vez em que conversamos sobre atualizações HOT e limpeza na página, hoje veremos o conhecido vulgar de limpeza comum a vácuo . Sim, já foi escrito tanto sobre ela que é improvável que eu diga algo novo, mas a integridade da imagem exige sacrifício. Seja paciente.

Limpeza normal (vácuo)

O que a limpeza faz

A limpeza dentro da página é rápida, mas libera apenas uma fração do espaço. Ele funciona na mesma página tabular e não afeta os índices.

A principal limpeza "normal" é realizada pelo comando VACUUM e a chamaremos de limpeza simples (e falaremos sobre limpeza automática separadamente).

Portanto, a limpeza processa a tabela completamente. Ele limpa não apenas versões desnecessárias de strings, mas também referências a eles de todos os índices.

O processamento ocorre em paralelo com outras atividades no sistema. Nesse caso, a tabela e os índices podem ser usados ​​da maneira usual, tanto para leitura quanto para alteração (no entanto, a execução simultânea de comandos como CREATE INDEX, ALTER TABLE e alguns outros será impossível).

Somente as páginas nas quais ocorreu alguma atividade são exibidas na tabela. Para isso, é usado um mapa de visibilidade (lembro que as páginas que contêm apenas versões bastante antigas de linhas que são garantidas como visíveis em todos os instantâneos de dados estão marcadas nele). Somente páginas que não estão marcadas no mapa são processadas, enquanto o próprio mapa é atualizado.

No processo, o mapa de espaço livre é atualizado para refletir o espaço livre que aparece nas páginas.

Como de costume, crie uma tabela:

=> CREATE TABLE vac( id serial, s char(100) ) WITH (autovacuum_enabled = off); => CREATE INDEX vac_s ON vac(s); => INSERT INTO vac(s) VALUES ('A'); => UPDATE vac SET s = 'B'; => UPDATE vac SET s = 'C'; 

Usando o parâmetro autovacuum_enabled, desativamos a limpeza automática. Falaremos sobre isso na próxima vez, mas por enquanto - para experimentos - é importante gerenciarmos a limpeza manualmente.

Existem três versões da linha na tabela agora e cada uma está vinculada a partir de um índice:

 => SELECT * FROM heap_page('vac',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+----------+-----+-----+-------- (0,1) | normal | 4000 (c) | 4001 (c) | | | (0,2) (0,2) | normal | 4001 (c) | 4002 | | | (0,3) (0,3) | normal | 4002 | 0 (a) | | | (0,3) (3 rows) 

 => SELECT * FROM index_page('vac_s',1); 

  itemoffset | ctid ------------+------- 1 | (0,1) 2 | (0,2) 3 | (0,3) (3 rows) 

Após a limpeza, as versões "mortas" desaparecem e existe apenas uma, relevante. E o índice também tem um link restante:

 => VACUUM vac; => SELECT * FROM heap_page('vac',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+-------+-----+-----+-------- (0,1) | unused | | | | | (0,2) | unused | | | | | (0,3) | normal | 4002 (c) | 0 (a) | | | (0,3) (3 rows) 

 => SELECT * FROM index_page('vac_s',1); 

  itemoffset | ctid ------------+------- 1 | (0,3) (1 row) 

Observe que os dois primeiros ponteiros receberam o status sem uso e não morto, como seria com a limpeza dentro da página.

E novamente sobre o horizonte de transações

Como o PostgreSQL determina quais versões de linha podem ser consideradas "inoperantes"? Já consideramos o conceito de horizonte de transação quando falamos sobre instantâneos de dados , mas esse é um tópico tão importante que não é pecado repeti-lo.

Vamos começar a experiência anterior novamente.

 => TRUNCATE vac; => INSERT INTO vac(s) VALUES ('A'); => UPDATE vac SET s = 'B'; 

Mas antes de atualizar a linha novamente, deixe outra transação começar (mas não terminar). Em nosso exemplo, ele funcionará no nível Read Committed, mas deverá obter um número de transação real (não virtual). Por exemplo, ele pode alterar ou até mesmo bloquear algumas linhas em qualquer tabela, não necessariamente em vac:

 | => BEGIN; | => SELECT s FROM t FOR UPDATE; 

 | s | ----- | FOO | BAR | (2 rows) 

 => UPDATE vac SET s = 'C'; 

Existem três linhas na tabela e três links no índice. O que acontece após a limpeza?

 => VACUUM vac; => SELECT * FROM heap_page('vac',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+----------+-----+-----+-------- (0,1) | unused | | | | | (0,2) | normal | 4005 (c) | 4007 (c) | | | (0,3) (0,3) | normal | 4007 (c) | 0 (a) | | | (0,3) (3 rows) 

 => SELECT * FROM index_page('vac_s',1); 

  itemoffset | ctid ------------+------- 1 | (0,2) 2 | (0,3) (2 rows) 

Existem duas versões da linha restantes na tabela: a limpeza decidiu que a versão (0.2) ainda não podia ser excluída. O motivo, é claro, está no horizonte de transação do banco de dados, que em nosso exemplo é determinado por uma transação incompleta:

 | => SELECT backend_xmin FROM pg_stat_activity WHERE pid = pg_backend_pid(); 

 | backend_xmin | -------------- | 4006 | (1 row) 

Você pode solicitar a limpeza para falar sobre o que acontece:

 => VACUUM VERBOSE vac; 

 INFO: vacuuming "public.vac" INFO: index "vac_s" now contains 2 row versions in 2 pages DETAIL: 0 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: "vac": found 0 removable, 2 nonremovable row versions in 1 out of 1 pages DETAIL: 1 dead row versions cannot be removed yet, oldest xmin: 4006 There were 1 unused item pointers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM 

Observe:

• 2 versões de linha não removíveis - foram encontradas 2 versões na tabela que não podem ser excluídas,
• 1 versão de linha morta ainda não pode ser removida - incluindo 1 "morta",
• xmin mais antigo mostra o horizonte atual.

Repetimos a conclusão mais uma vez: a presença de transações de longa duração no banco de dados (não concluídas ou muito demoradas) pode levar à expansão de tabelas (inchadas), independentemente da frequência com que a limpeza é realizada. Portanto, no PostgreSQL, as cargas de trabalho OLTP e OLAP são pouco combinadas em um banco de dados: os relatórios que são executados por horas não permitem que as tabelas atualizadas com frequência sejam limpas a tempo. Uma solução possível seria criar uma réplica separada de "relatório".

Após a conclusão de uma transação aberta, o horizonte muda e a situação é corrigida:

 | => COMMIT; 

 => VACUUM VERBOSE vac; 

 INFO: vacuuming "public.vac" INFO: scanned index "vac_s" to remove 1 row versions DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: "vac": removed 1 row versions in 1 pages DETAIL: CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s INFO: index "vac_s" now contains 1 row versions in 2 pages DETAIL: 1 index row versions were removed. 0 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: "vac": found 1 removable, 1 nonremovable row versions in 1 out of 1 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 4008 There were 1 unused item pointers. Skipped 0 pages due to buffer pins, 0 frozen pages. 0 pages are entirely empty. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. VACUUM 

Agora a página possui apenas a versão atual mais recente da linha:

 => SELECT * FROM heap_page('vac',0); 

  ctid | state | xmin | xmax | hhu | hot | t_ctid -------+--------+----------+-------+-----+-----+-------- (0,1) | unused | | | | | (0,2) | unused | | | | | (0,3) | normal | 4007 (c) | 0 (a) | | | (0,3) (3 rows) 

Também há apenas uma entrada no índice:

 => SELECT * FROM index_page('vac_s',1); 

  itemoffset | ctid ------------+------- 1 | (0,3) (1 row) 

O que está acontecendo por dentro

A limpeza deve processar a tabela e os índices ao mesmo tempo e fazê-lo de maneira a não bloquear a operação de outros processos. Como ela faz isso?

Tudo começa com uma varredura de tabela (levando em consideração o mapa de visibilidade, como já observado). Nas páginas lidas, versões desnecessárias de strings são determinadas e seus identificadores (tid) são gravados em uma matriz especial. A matriz está localizada na memória local do processo de limpeza; um fragmento do tamanho maintenance_work_mem é alocado para ele. O valor padrão para este parâmetro é 64 MB. Observe que essa memória é alocada imediatamente na íntegra e não conforme necessário. Verdadeiro, se a tabela for pequena, o fragmento será alocado menos.

A seguir, uma das duas coisas: chegaremos ao final da tabela ou a memória alocada para a matriz terminará. Nos dois casos, a fase de limpeza do índice é iniciada. Para fazer isso, cada um dos índices criados na tabela é completamente verificado na busca de registros que fazem referência a versões armazenadas de linhas. Os registros encontrados são limpos das páginas de índice.

Nesse ponto, temos a seguinte imagem: nos índices, não há mais links para versões desnecessárias de linhas, mas elas ainda existem na tabela. Isso não contradiz nada: ao executar uma consulta, não chegamos a versões mortas de linhas (com acesso ao índice) ou as marcamos ao verificar a visibilidade (ao verificar uma tabela).

Depois disso, a fase de limpeza da mesa começa. A tabela é digitalizada novamente para ler as páginas necessárias, limpar as versões armazenadas das linhas e liberar os ponteiros. Podemos fazer isso porque não há mais links de índices.

Se a tabela não foi lida completamente na primeira passagem, a matriz é limpa e tudo é repetido do local em que paramos.

Desta forma:

• a tabela é sempre digitalizada duas vezes;
• se tantas versões de linha forem excluídas durante a limpeza que todas elas não caberão na memória maintenance_work_mem , todos os índices serão varridos completamente quantas vezes forem necessárias.

Em tabelas grandes, isso pode levar uma quantidade considerável de tempo e criar uma carga significativa no sistema. Obviamente, as solicitações não serão bloqueadas, mas a E / S "extra" também é desagradável.

Para acelerar o processo, faz sentido solicitar a limpeza com mais frequência (para que um número não muito grande de versões de linha seja limpo a cada vez) ou alocar mais memória.

Observo entre parênteses que, a partir da versão 11, o PostgreSQL pode pular as verificações de índice, se isso não for absolutamente necessário. Isso deve facilitar a vida dos proprietários de tabelas grandes nas quais as linhas são adicionadas apenas (mas não alteradas).

Monitoramento

Como entender que a limpeza não lida com o trabalho de uma só vez?

Já vimos o primeiro método: você pode chamar o comando VACUUM com VERBOSE. Em seguida, as informações sobre as fases do trabalho serão exibidas no console.

Em segundo lugar, a partir da versão 9.6, existe uma visão pg_stat_progress_vacuum, que também contém todas as informações necessárias.

(Existe uma terceira maneira - exibir informações no log de mensagens, mas isso funciona apenas para a limpeza automática, que será discutida na próxima vez.)

Inseriremos mais linhas na tabela para que o processo de limpeza leve um tempo notável e atualizaremos todas elas para que haja algo a ver com a limpeza.

 => TRUNCATE vac; => INSERT INTO vac(s) SELECT 'A' FROM generate_series(1,500000); => UPDATE vac SET s = 'B'; 

Reduza o tamanho da memória alocada para a matriz identificadora:

 => ALTER SYSTEM SET maintenance_work_mem = '1MB'; => SELECT pg_reload_conf(); 

Começamos a limpar e, enquanto funciona, voltaremos à visualização pg_stat_progress_vacuum várias vezes:

 => VACUUM VERBOSE vac; 

 | => SELECT * FROM pg_stat_progress_vacuum \gx 

 | -[ RECORD 1 ]------+------------------ | pid | 6715 | datid | 41493 | datname | test | relid | 57383 | phase | vacuuming indexes | heap_blks_total | 16667 | heap_blks_scanned | 2908 | heap_blks_vacuumed | 0 | index_vacuum_count | 0 | max_dead_tuples | 174762 | num_dead_tuples | 174480 

 | => SELECT * FROM pg_stat_progress_vacuum \gx 

 | -[ RECORD 1 ]------+------------------ | pid | 6715 | datid | 41493 | datname | test | relid | 57383 | phase | vacuuming indexes | heap_blks_total | 16667 | heap_blks_scanned | 5816 | heap_blks_vacuumed | 2907 | index_vacuum_count | 1 | max_dead_tuples | 174762 | num_dead_tuples | 174480 

Aqui vemos em particular:

• nome da fase atual (fase) - falamos sobre três fases principais, mas em geral existem mais ;
• número total de páginas da tabela (heap_blks_total);
• o número de páginas rastreadas (heap_blks_scanned);
• o número de páginas já limpas (heap_blks_vacuumed);
• o número de passes por índice (index_vacuum_count).

O progresso geral é determinado pela proporção de heap_blks_vacuumed e heap_blks_total, mas lembre-se de que esse valor não muda suavemente, mas "de maneira instável" devido às verificações de índice. No entanto, a principal atenção deve ser dada ao número de ciclos de limpeza - um valor maior que 1 significa que a memória alocada não foi suficiente para concluir a limpeza de uma só vez.

A saída do comando VACUUM VERBOSE, concluída neste momento, mostrará o quadro geral:

 INFO: vacuuming "public.vac" 

 INFO: scanned index "vac_s" to remove 174480 row versions DETAIL: CPU: user: 0.50 s, system: 0.07 s, elapsed: 1.36 s INFO: "vac": removed 174480 row versions in 2908 pages DETAIL: CPU: user: 0.02 s, system: 0.02 s, elapsed: 0.13 s 

 INFO: scanned index "vac_s" to remove 174480 row versions DETAIL: CPU: user: 0.26 s, system: 0.07 s, elapsed: 0.81 s INFO: "vac": removed 174480 row versions in 2908 pages DETAIL: CPU: user: 0.01 s, system: 0.02 s, elapsed: 0.10 s 

 INFO: scanned index "vac_s" to remove 151040 row versions DETAIL: CPU: user: 0.13 s, system: 0.04 s, elapsed: 0.47 s INFO: "vac": removed 151040 row versions in 2518 pages DETAIL: CPU: user: 0.01 s, system: 0.02 s, elapsed: 0.08 s 

 INFO: index "vac_s" now contains 500000 row versions in 17821 pages DETAIL: 500000 index row versions were removed. 8778 index pages have been deleted, 0 are currently reusable. CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s. INFO: "vac": found 500000 removable, 500000 nonremovable row versions in 16667 out of 16667 pages DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 4011 There were 0 unused item pointers. 0 pages are entirely empty. CPU: user: 1.10 s, system: 0.37 s, elapsed: 3.71 s. VACUUM 

Aqui você pode ver que, no total, houve três passagens pelos índices, cada uma das quais limpou 174.480 ponteiros para versões mortas de strings. De onde vem esse número? Um link (tid) ocupa 6 bytes e 1024 * 1024/6 = 174762 é o número que vemos em pg_stat_progress_vacuum.max_dead_tuples. Na verdade, ele pode ser usado um pouco menos: é garantido que, ao ler a próxima página, todos os indicadores para as versões "inativas" caberão exatamente na memória.

Análise

A análise ou, em outras palavras, a coleta de informações estatísticas para o planejador de consultas, não está formalmente conectada à limpeza. No entanto, podemos realizar a análise não apenas com a equipe ANALYZE, mas também combinar a limpeza com a análise: ANÁLISE DE VÁCUO. Nesse caso, a limpeza é realizada primeiro e depois a análise - não ocorre economia.

Porém, como veremos mais adiante, a limpeza automática e a análise automática são realizadas em um processo e gerenciadas de maneira semelhante.

Limpeza completa (vácuo cheio)

Como vimos, a limpeza convencional libera mais espaço do que a limpeza intra-página, mas nem sempre resolve o problema completamente.

Se, por algum motivo, uma tabela ou índice tiver aumentado significativamente de tamanho, a limpeza regular liberará espaço nas páginas existentes: eles terão orifícios que serão usados ​​para inserir novas versões de linhas. Mas o número de páginas não será alterado e, portanto, do ponto de vista do sistema operacional, os arquivos ocuparão exatamente a mesma quantidade de espaço que ocupavam antes da limpeza. E isso é ruim porque:

• a varredura completa de uma tabela (ou índice) fica mais lenta;
• pode ser necessário um cache de buffer maior (porque as páginas são armazenadas e a densidade de informações úteis diminui);
• um nível "extra" pode aparecer na árvore de índices, o que tornará mais lento o acesso ao índice;
• arquivos ocupam espaço em disco extra e backups.

(A única exceção são as páginas completamente limpas no final do arquivo - essas páginas "cortam" o arquivo e retornam ao sistema operacional.)

Se o compartilhamento de informações úteis nos arquivos estiver abaixo de um limite razoável, o administrador poderá executar uma limpeza completa da tabela. Ao mesmo tempo, a tabela e todos os seus índices são completamente reconstruídos do zero e os dados são compactados da maneira mais compacta possível (é claro, levando em consideração o parâmetro fator de preenchimento). Ao reconstruir, o PostgreSQL reconstrói sequencialmente a tabela primeiro e depois cada um de seus índices. Novos arquivos são criados para cada objeto e, no final da reconstrução, os arquivos antigos são excluídos. Observe que, no processo de trabalho no disco, será necessário espaço adicional.

Para ilustrar, insira várias linhas na tabela novamente:

 => TRUNCATE vac; => INSERT INTO vac(s) SELECT 'A' FROM generate_series(1,500000); 

Como avaliar a densidade da informação? Para fazer isso, é conveniente usar a extensão especial:

 => CREATE EXTENSION pgstattuple; => SELECT * FROM pgstattuple('vac') \gx 

 -[ RECORD 1 ]------+--------- table_len | 68272128 tuple_count | 500000 tuple_len | 64500000 tuple_percent | 94.47 dead_tuple_count | 0 dead_tuple_len | 0 dead_tuple_percent | 0 free_space | 38776 free_percent | 0.06 

A função lê a tabela inteira e mostra estatísticas sobre quanto espaço quais dados estão ocupados nos arquivos. A principal informação em que estamos interessados ​​agora é o campo tuple_percent: a porcentagem ocupada por dados úteis. É inferior a 100 devido à sobrecarga inevitável de informações de serviço dentro da página, mas mesmo assim é bastante alta.

Para o índice, outras informações são exibidas, mas o campo avg_leaf_density tem o mesmo significado: a porcentagem de informações úteis (nas páginas de folha).

 => SELECT * FROM pgstatindex('vac_s') \gx 

 -[ RECORD 1 ]------+--------- version | 3 tree_level | 3 index_size | 72802304 root_block_no | 2722 internal_pages | 241 leaf_pages | 8645 empty_pages | 0 deleted_pages | 0 avg_leaf_density | 83.77 leaf_fragmentation | 64.25 

E aqui está o tamanho da tabela e do índice:

 => SELECT pg_size_pretty(pg_table_size('vac')) table_size, pg_size_pretty(pg_indexes_size('vac')) index_size; 

  table_size | index_size ------------+------------ 65 MB | 69 MB (1 row) 

Agora exclua 90% de todas as linhas. Selecionamos as linhas para exclusão aleatoriamente, para que em todas as páginas com alta probabilidade pelo menos uma linha permaneça:

 => DELETE FROM vac WHERE random() < 0.9; 

 DELETE 450189 

Qual o tamanho dos objetos após a limpeza normal?

 => VACUUM vac; => SELECT pg_size_pretty(pg_table_size('vac')) table_size, pg_size_pretty(pg_indexes_size('vac')) index_size; 

  table_size | index_size ------------+------------ 65 MB | 69 MB (1 row) 

Vimos que o tamanho não mudou: a limpeza regular não pode reduzir o tamanho dos arquivos de forma alguma. Embora a densidade da informação tenha obviamente diminuído cerca de 10 vezes:

 => SELECT vac.tuple_percent, vac_s.avg_leaf_density FROM pgstattuple('vac') vac, pgstatindex('vac_s') vac_s; 

  tuple_percent | avg_leaf_density ---------------+------------------ 9.41 | 9.73 (1 row) 

Agora verifique o que acontece após uma limpeza completa. Aqui estão os arquivos usados ​​pela tabela e índices agora:

 => SELECT pg_relation_filepath('vac'), pg_relation_filepath('vac_s'); 

  pg_relation_filepath | pg_relation_filepath ----------------------+---------------------- base/41493/57392 | base/41493/57393 (1 row) 

 => VACUUM FULL vac; => SELECT pg_relation_filepath('vac'), pg_relation_filepath('vac_s'); 

  pg_relation_filepath | pg_relation_filepath ----------------------+---------------------- base/41493/57404 | base/41493/57407 (1 row) 

Agora os arquivos são substituídos por novos. O tamanho da tabela e do índice diminuiu significativamente e a densidade de informações aumentou:

 => SELECT pg_size_pretty(pg_table_size('vac')) table_size, pg_size_pretty(pg_indexes_size('vac')) index_size; 

  table_size | index_size ------------+------------ 6648 kB | 6480 kB (1 row) 

 => SELECT vac.tuple_percent, vac_s.avg_leaf_density FROM pgstattuple('vac') vac, pgstatindex('vac_s') vac_s; 

  tuple_percent | avg_leaf_density ---------------+------------------ 94.39 | 91.08 (1 row) 

Observe que a densidade de informações no índice aumentou ainda em comparação com o original. Recriar um índice (árvore B) a partir dos dados disponíveis é mais rentável do que inserir dados em um índice existente, linha por linha.

As funções de extensão pgstattuple que usamos leram a tabela inteira. Se a tabela for grande, isso é inconveniente e, portanto, existe uma função pgstattuple_approx, que pula as páginas marcadas no mapa de visibilidade e mostra números aproximados.

Uma maneira ainda mais rápida, mas ainda menos precisa, é estimar a proporção do volume de dados para o tamanho do arquivo no diretório do sistema. Opções para essas consultas podem ser encontradas no wiki .

Uma limpeza completa não requer uso regular, pois bloqueia completamente todo o trabalho com a tabela (incluindo a consulta) por toda a duração do trabalho. É claro que em um sistema usado ativamente isso pode ser inaceitável. Bloqueios serão considerados separadamente, mas por enquanto nos restringiremos a mencionar a extensão pg_repack , que bloqueia a tabela apenas por um curto período de tempo no final do trabalho.

Equipes semelhantes

Existem vários comandos que também reconstroem completamente tabelas e índices, e isso é semelhante a uma limpeza completa. Todos eles bloqueiam completamente o trabalho com a tabela, todos excluem os arquivos de dados antigos e criam novos.

O comando CLUSTER é semelhante em tudo ao VACUUM FULL, mas adicionalmente organiza fisicamente a versão das strings de acordo com um dos índices disponíveis. Isso fornece ao agendador a capacidade de usar o acesso ao índice com mais eficiência em alguns casos. No entanto, deve-se entender que o armazenamento em cluster não é suportado: com alterações subseqüentes na tabela, a ordem física das versões de linha será violada.

O comando REINDEX reconstrói um único índice em uma tabela. De fato, VACUUM FULL e CLUSTER usam esse comando para recriar índices.

O comando TRUNCATE funciona logicamente da mesma forma que DELETE - exclui todas as linhas da tabela. Mas DELETE, como já discutido, marca apenas a versão das linhas como excluída, o que requer limpeza adicional. TRUNCATE apenas cria um novo arquivo limpo. Como regra, isso funciona mais rápido, mas lembre-se de que TRUNCATE bloqueará completamente o trabalho com a tabela durante todo o tempo até o final da transação.

Para ser continuado .