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

Na última vez em que conversamos sobre atualizações QUENTES e aspiração na página, hoje passaremos a um conhecido vulgar a vácuo . Realmente, tanto já foi escrito sobre isso que mal posso acrescentar algo novo, mas a beleza de uma imagem completa requer sacrifício. Portanto, tenha paciência.

Vácuo

O que o vácuo faz?

O vácuo na página funciona rápido, mas libera apenas parte do espaço. Ele funciona em uma página da tabela e não toca nos índices.

O vácuo básico, "normal", é feito usando o comando VACUUM, e o chamaremos apenas de "vácuo" (deixando "autovacuum" para uma discussão em separado).

Portanto, o vácuo processa a tabela inteira. Ele aspira não apenas as tuplas mortas, mas também faz referência a elas de todos os índices.

Aspirar é simultâneo com outras atividades no sistema. A tabela e os índices podem ser usados ​​regularmente para leituras e atualizações (no entanto, a execução simultânea de comandos como CREATE INDEX, ALTER TABLE e alguns outros é impossível).

Somente essas páginas da tabela são examinadas onde algumas atividades ocorreram. Para detectá-los, o mapa de visibilidade é usado (para lembrá-lo, o mapa rastreia as páginas que contêm tuplas bastante antigas, que são visíveis em todos os instantâneos de dados, com certeza). Somente as páginas são processadas que não são rastreadas pelo mapa de visibilidade e o próprio mapa é atualizado.

O mapa de espaço livre também é atualizado no processo para refletir o espaço livre extra nas páginas.

Como sempre, vamos criar 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'; 

Usamos o parâmetro autovacuum_enabled para desativar o processo de autovacuum. Discutiremos sobre isso na próxima vez, e agora é fundamental para nossos experimentos controlar manualmente a aspiração.

A tabela agora possui três tuplas, cada uma das quais é referenciada no í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 aspiração, as tuplas mortas são aspiradas, e apenas uma permanece ativa. E apenas uma referência permanece no índice:

 => 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 adquiriram o status "não utilizado" em vez de "morto", que seriam adquiridos com o vácuo na página.

Sobre o horizonte de transações mais uma vez

Como o PostgreSQL descobre quais tuplas podem ser consideradas mortas? Já abordamos o conceito de horizonte de transações ao discutir snapshots de dados , mas não custa reiterar um assunto tão importante.

Vamos começar o experimento anterior novamente.

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

Mas antes de atualizar a linha novamente, deixe mais uma transação iniciar (mas não terminar). Neste exemplo, ele usará o nível Read Committed, mas deverá obter um número de transação verdadeiro (não virtual). Por exemplo, a transação pode alterar e até bloquear determinadas linhas em qualquer tabela, não obrigatória 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 referências no índice agora. O que acontecerá após aspirar?

 => 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) 

Duas tuplas permanecem na tabela: VACUUM decidiu que a (0,2) tupla ainda não pode ser aspirada. O motivo está certamente no horizonte de transações do banco de dados, que neste exemplo é determinado pela transação não concluída:

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

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

Podemos pedir à VACUUM para relatar o que está acontecendo:

 => 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 

Note que:

• 2 nonremovable row versions - duas tuplas que não podem ser excluídas são encontradas na tabela.
• 1 dead row versions cannot be removed yet - uma delas está morta.
• oldest xmin mostra o horizonte atual.

Vamos reiterar a conclusão: se um banco de dados tiver transações de longa duração (não concluídas ou sendo executadas por muito tempo), isso pode causar inchaço na tabela, independentemente da frequência com que a aspiração ocorre. Portanto, as cargas de trabalho dos tipos OLTP e OLAP coexistem mal em um banco de dados PostgreSQL: os relatórios em execução por horas não permitem que as tabelas atualizadas sejam devidamente aspiradas. A criação de uma réplica separada para fins de relatório pode ser uma solução possível para isso.

Após a conclusão de uma transação aberta, o horizonte se move 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, apenas a versão mais recente, ao vivo, da linha é deixada na página:

 => 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) 

O índice também possui apenas uma linha:

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

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

O que acontece por dentro?

A aspiração deve processar a tabela e os índices ao mesmo tempo e fazer isso para não bloquear os outros processos. Como isso pode ser feito?

Tudo começa com a fase de heap de verificação (o mapa de visibilidade levado em consideração, como já mencionado). Nas páginas lidas, as tuplas mortas são detectadas e suas tid são gravadas em uma matriz especializada. A matriz é armazenada na memória local do processo de vácuo, onde bytes de memória maintenance_work_mem são alocados para ela. O valor padrão deste parâmetro é 64 MB. Observe que a quantidade total de memória é alocada de uma só vez, e não conforme a necessidade. No entanto, se a tabela não for grande, uma quantidade menor de memória será alocada.

Em seguida, chegamos ao final da tabela ou a memória alocada para a matriz acabou. Nos dois casos, a fase dos índices de aspiração é iniciada. Para esse fim, cada índice criado na tabela é totalmente varrido em busca das linhas que referenciam as tuplas lembradas. As linhas encontradas são aspiradas das páginas de índice.

Aqui, confrontamos o seguinte: os índices ainda não têm referências a tuplas mortas, enquanto a tabela ainda as possui. E isso é contrário a nada: ao executar uma consulta, não atingimos tuplas mortas (com acesso ao índice) ou as rejeitamos na verificação de visibilidade (ao verificar a tabela).

Depois disso, a fase de aspiração começa. A tabela é digitalizada novamente para ler as páginas apropriadas, aspirá-las das tuplas lembradas e soltar os ponteiros. Podemos fazer isso, pois não há mais referências dos índices.

Se a tabela não foi totalmente lida durante o primeiro ciclo, a matriz é limpa e tudo é repetido de onde chegamos.

Em resumo:

• A tabela é sempre digitalizada duas vezes.
• Se a aspiração excluir tantas tuplas que nem todas cabem na memória do tamanho maintenance_work_mem , todos os índices serão verificados quantas vezes forem necessárias.

Para tabelas grandes, isso pode exigir muito tempo e adicionar uma carga de trabalho significativa do sistema. Obviamente, as consultas não serão bloqueadas, mas a entrada / saída extra é definitivamente indesejável.

Para acelerar o processo, faz sentido chamar VACUUM com mais frequência (para que não sejam eliminadas muitas tuplas de cada vez) ou alocar mais memória.

Para observar entre parênteses, a partir da versão 11, o PostgreSQL pode ignorar as verificações de índice, a menos que seja 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 podemos descobrir que o VACUUM não pode fazer seu trabalho em um ciclo?

Já vimos a primeira maneira: chamar o comando VACUUM com a opção VERBOSE. Nesse caso, informações sobre as fases do processo serão enviadas para o console.

Segundo, começando com a versão 9.6, a visualização pg_stat_progress_vacuum está disponível, o que também fornece todas as informações necessárias.

(A terceira maneira também está disponível: enviar as informações para o log de mensagens, mas isso funciona apenas para o vácuo automático, que será discutido na próxima vez.)

Vamos inserir algumas linhas na tabela, para que o processo de vácuo dure bastante, e vamos atualizar todas elas, para que o VACUUM consiga o que fazer.

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

Vamos reduzir o tamanho da memória alocada para a matriz de identificadores:

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

Vamos iniciar o VACUUM e, enquanto estiver funcionando, vamos acessar a 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 podemos ver, em particular:

• O nome da fase atual - discutimos três fases principais, mas há mais delas em geral.
• O número total de páginas da tabela ( heap_blks_total ).
• O número de páginas digitalizadas ( heap_blks_scanned ).
• O número de páginas já heap_blks_vacuumed ( heap_blks_vacuumed ).
• O número de ciclos de vácuo do índice ( index_vacuum_count ).

O progresso geral é determinado pela proporção de heap_blks_vacuumed e heap_blks_total , mas devemos levar em consideração que esse valor muda em grandes incrementos, em vez de suavemente, devido à varredura dos índices. A atenção principal, no entanto, deve ser dada ao número de ciclos de vácuo: o número maior que 1 significa que a memória alocada não foi suficiente para concluir a aspiração em um ciclo.

A saída do comando VACUUM VERBOSE, já concluída nessa época, mostrará a imagem 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 

Podemos ver aqui que foram realizados três ciclos sobre os índices e, em cada ciclo, 174480 ponteiros para tuplas mortas foram aspirados. Por que exatamente esse número? Uma vez ocupa 6 bytes e 1024 * 1024/6 = 174762, que é o número que vemos em pg_stat_progress_vacuum.max_dead_tuples . Na realidade, um pouco menos pode ser usado: isso garante que, quando uma próxima página for lida, todos os ponteiros para tuplas mortas caberão na memória, com certeza.

Análise

A análise ou, em outras palavras, a coleta de estatísticas para o planejador de consultas, não está formalmente relacionada à aspiração. No entanto, podemos realizar a análise não apenas usando o comando ANALYZE, mas combinar a aspiração e a análise no VACUUM ANALYZE. Aqui o vácuo é feito primeiro e depois a análise, de modo que isso não gera ganhos.

Mas, como veremos mais adiante, o autovacuum e a análise automática são feitos em um processo e são controlados de maneira semelhante.

VÁCUO CHEIO

Como observado acima, o vácuo libera mais espaço do que o in-page, mas ainda assim não resolve completamente o problema.

Se, por algum motivo, o tamanho de uma tabela ou de um índice aumentar muito, o VACUUM liberará espaço dentro das páginas existentes: ocorrerão "furos", que serão usados ​​para a inserção de novas tuplas. Mas o número de páginas não muda e, portanto, do ponto de vista do sistema operacional, os arquivos ocupam exatamente o mesmo espaço que antes do vácuo. E isso não é bom porque:

• A verificação completa da tabela (ou índice) fica mais lenta.
• Um cache de buffer maior pode ser necessário (pois são as páginas armazenadas lá e a densidade de informações úteis diminui).
• Na árvore de índices, pode ocorrer um nível extra, que diminui o acesso ao índice.
• Os arquivos ocupam espaço extra no disco e nas cópias de backup.

(A única exceção são as páginas totalmente aspiradas, localizadas no final do arquivo. Essas páginas são cortadas do arquivo e retornadas ao sistema operacional.)

Se o compartilhamento de informações úteis nos arquivos cair abaixo de algum limite razoável, o administrador poderá executar VACUUM CHEIO da tabela. Nesse caso, a tabela e todos os seus índices são reconstruídos do zero e os dados são compactados de maneira compacta (é claro, o parâmetro fillfactor levado em consideração). Durante a reconstrução, o PostgreSQL primeiro reconstrói a tabela e, em seguida, cada um de seus índices, um por um. Para cada objeto, novos arquivos são criados e arquivos antigos são removidos no final da reconstrução. Devemos levar em consideração que será necessário espaço em disco extra no processo.

Para ilustrar isso, vamos novamente inserir um certo número de linhas na tabela:

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

Como podemos estimar a densidade da informação? Para fazer isso, é conveniente usar uma extensão especializada:

 => 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: quais dados ocupam quanto espaço nos arquivos. A principal informação de nosso interesse agora é o campo tuple_percent : a porcentagem de dados úteis. É inferior a 100 por causa da inevitável sobrecarga de informações dentro de uma página, mas ainda é bastante alta.

Para o índice, são avg_leaf_density informações diferentes, 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 estes são os tamanhos da tabela e índices:

 => 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 vamos excluir 90% de todas as linhas. Fazemos uma escolha aleatória de linhas a serem excluídas, para que pelo menos uma linha permaneça altamente em cada página:

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

 DELETE 450189 

Qual o tamanho dos objetos após o VACUUM?

 => 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) 

Podemos ver que o tamanho não mudou: VACUUM de maneira alguma pode reduzir o tamanho dos arquivos. E isso ocorre embora a densidade de informações tenha diminuído aproximadamente 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 vamos verificar o que obtemos após o VACUUM FULL. Agora a tabela e os índices usam os seguintes arquivos:

 => 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) 

Os arquivos são substituídos por novos agora. Os tamanhos da tabela e índices diminuíram significativamente, enquanto a densidade de informações aumentou de acordo:

 => 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 é ainda maior que a original. É mais vantajoso recriar um índice (árvore B) a partir dos dados disponíveis do que inserir os dados em um índice existente linha por linha.

As funções da extensão pgstattuple que usamos lemos a tabela inteira. Mas isso é inconveniente se a tabela for grande, portanto a extensão tem a função pgstattuple_approx , que pula as páginas marcadas no mapa de visibilidade e mostra valores aproximados.

Mais uma maneira, mas ainda menos precisa, é usar o catálogo do sistema para estimar aproximadamente a proporção entre o tamanho dos dados e o tamanho do arquivo. Você pode encontrar exemplos de tais consultas no wiki .

O VACUUM FULL não se destina ao uso regular, pois bloqueia qualquer trabalho com a tabela (consultas incluídas) por toda a duração do processo. É claro que, para um sistema muito usado, isso pode parecer inaceitável. Os bloqueios serão discutidos separadamente, e agora mencionaremos apenas a extensão pg_repack , que bloqueia a tabela por apenas um curto período de tempo no final do trabalho.

Comandos semelhantes

Existem alguns comandos que também reconstroem completamente tabelas e índices e, portanto, se assemelham ao VACUUM FULL. Todos eles bloqueiam completamente qualquer trabalho com a tabela, todos removem arquivos de dados antigos e criam novos.

O comando CLUSTER é semelhante ao VACUUM FULL, mas também ordena fisicamente as tuplas de acordo com um dos índices disponíveis. Isso permite que o planejador use o acesso ao índice com mais eficiência em alguns casos. Mas devemos ter em mente que o agrupamento não é mantido: a ordem física das tuplas será interrompida com as alterações subsequentes da tabela.

O comando REINDEX reconstrói um índice separado na tabela. VACUUM FULL e CLUSTER realmente usam esse comando para recriar índices.

A lógica do comando TRUNCATE é semelhante à de DELETE - exclui todas as linhas da tabela. Mas DELETE, como já foi mencionado, marca apenas as tuplas como excluídas, e isso requer mais limpeza. E TRUNCATE apenas cria um arquivo novo e limpo. Como regra, isso funciona mais rápido, mas devemos lembrar que o TRUNCATE bloqueará qualquer trabalho com a tabela até o final da transação.

Continue lendo .