Em um servidor de banco de dados PostgreSQL 11, tenho uma tabela grande (932 milhões de linhas, 2150 GB, não particionada) na qual insiro cerca de 2 a 3 milhões de linhas todas as noites com um trabalho em lote. A tabela foi criada com autovacuum_enabled=FALSE. Após a inserção, faço um VACUUM (ANALYZE)na mesa, o que geralmente leva cerca de 30 a 40 segundos. No entanto, uma vez a cada poucas semanas esse vácuo leva muito tempo, como hoje, quando demorou 2:11 h:mm.
Agora estou pensando em fazer um vácuo antes do meu trabalho noturno para evitar esses longos tempos de execução durante a execução do trabalho. Eu dei uma olhada nas opções descritas na página de documentação de vácuo do PostgreSQL 11 . Eu sei que a VACUUM (FULL)é muito caro em termos de tempo de execução e, portanto, provavelmente está fora de questão. A VACUUM (FREEZE)será útil no meu caso? A outra opção, VACUUM (DISABLE_PAGE_SKIPPING)parece não ser relevante para as minhas necessidades.
Edit (2021-09-27): olhando para os últimos 270 dias (2021-01-01 a 2021-09-27), levou de 16 a 60 segundos na maioria dos dias para fazer o vácuo. No entanto, em 9 dias o vácuo ultrapassou os 1000 segundos, em 4 dos quais ultrapassou os 3500 segundos (ver gráfico abaixo). Interessante é o intervalo de tempo entre esses outliers, que fica entre 25 e 35 dias. Parece que algo se acumula ao longo desses 25-35 dias, exigindo um vácuo mais longo. Muito possivelmente, este é o wraparound do ID da transação mencionado nos comentários abaixo.
Editar (2022-02-08): Em setembro de 2021, modificamos nosso script alterando:
VACUUM (ANALYZE)
para:
ANALYZE (VERBOSE)
VACUUM (FREEZE, VERBOSE)
Infelizmente, as longas execuções mensais dessa etapa de trabalho ainda continuam. No entanto, com a divisão em duas etapas separadas de análise e vácuo, bem como com a saída detalhada, somos muito mais capazes de dizer o que está acontecendo.
Em um dia rápido, a saída das etapas de análise e vácuo se parece com isso:
INFO: analyzing "analytics.a_box_events"
INFO: "a_box_events": scanned 30000 of 152881380 pages, containing 264294 live rows and 0 dead rows; 30000 rows in sample, 1346854382 estimated total rows
ANALYZE
INFO: aggressively vacuuming "analytics.a_box_events"
INFO: "a_box_events": found 0 removable, 3407161 nonremovable row versions in 406302 out of 152881380 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 108425108
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 152475078 frozen pages.
0 pages are entirely empty.
CPU: user: 4.53 s, system: 3.76 s, elapsed: 25.77 s.
INFO: aggressively vacuuming "pg_toast.pg_toast_1361926"
INFO: "pg_toast_1361926": found 0 removable, 3700 nonremovable row versions in 680 out of 465143 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 108425110
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 464463 frozen pages.
0 pages are entirely empty.
CPU: user: 0.01 s, system: 0.00 s, elapsed: 0.05 s.
VACUUM
A análise não tem saída de temporização e o vácuo na mesa principal leva cerca de 20 a 40 segundos. Analisar e aspirar juntos geralmente terminam em menos de um minuto.
Uma vez a cada 35-45 dias, o vácuo leva muito mais tempo, pois vários minutos são gastos aspirando cada um dos 39 índices da tabela. Existem duas variações para isso:
- Nenhuma versão de linha de índice a ser removida
Saída de exemplo:
INFO: index "idx_a_box_events_node_id" now contains 1347181817 row versions in 4038010 pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 5.44 s, system: 13.45 s, elapsed: 230.59 s.
A saída final de todo o vácuo fica assim:
INFO: "a_box_events": found 0 removable, 2837554 nonremovable row versions in 340887 out of 153111143 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 108429069
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 152770256 frozen pages.
0 pages are entirely empty.
CPU: user: 669.94 s, system: 870.59 s, elapsed: 10501.17 s.
- Versões de linha de índice a serem removidas
Saída de exemplo:
INFO: scanned index "idx_a_box_events_node_id" to remove 2524 row versions
DETAIL: CPU: user: 49.34 s, system: 11.42 s, elapsed: 198.63 s
e:
INFO: index "idx_a_box_events_node_id" now contains 1228052362 row versions in 3478524 pages
DETAIL: 2524 index row versions were removed.
6 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
A saída final de todo o vácuo fica assim:
INFO: "a_box_events": found 56 removable, 3851482 nonremovable row versions in 461834 out of 139126225 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 97583006
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 138664391 frozen pages.
0 pages are entirely empty.
CPU: user: 2367.93 s, system: 761.34 s, elapsed: 8138.76 s.
Esses longos tempos de execução são atualmente mais um incômodo do que um impedimento. Uma atualização para a versão mais recente do servidor Postgres está planejada para o final de 2023. Nesse momento, tentaremos a opção INDEX_CLEANUP para VACUUM, executando-a uma vez a cada fim de semana, para evitar a limpeza dispendiosa dos índices no VACUUM diário. Enquanto isso, aparentemente, não temos escolha a não ser ser pacientes.
Edit (2022-08-24): Enquanto isso, particionamos nossa grande tabela em fatias mensais, dando-nos atualmente 56 partições, com cerca de 75-90 GB para a tabela e 45-55 GB para os índices por partição. Uma vez que uma noite, novos dados são adicionados à partição do mês atual, seguidos por um ANALYZE (VERBOSE)(não fazemos mais diariamente VACUUM). No primeiro domingo de um mês, também fazemos uma VACUUM (FULL, ANALYZE)partição do mês anterior.
Como pode ser esperado, o diário ANALYZE (VERBOSE)em uma única partição é bastante rápido, com uma média de 16 a 18 segundos por execução, aumentando de quase 0 segundos no primeiro dia de um mês até um máximo no final de um mês, após o qual os seguintes INSERTe ANALYZEsão aplicados a uma nova partição e são novamente mais rápidos:
Isso pode muito bem ser um wraparound de ID de transação, que deve varrer e congelar as linhas inseridas desde o último wraparound.
Você deve alterar sua execução noturna
VACUUM (FREEZE)para distribuir essa carga, principalmente se for uma tabela somente de inserção, em que o congelamento proativo não gasta recursos desnecessários. Consulte a documentação para uma discussão detalhada sobre a necessidade de congelar tuplas.A alternativa é atualizar para o PostgreSQL v13, que adicionou autovacuum com base no número de
INSERTs .Minha teoria sobre o que está acontecendo aqui é que para uma tabela somente INSERT, o vácuo só precisa visitar as partes da tabela sujas pelas inserções e pode pular a visita aos índices.
Mas se ele encontrar mesmo uma tupla morta (na v11), ele precisará varrer a totalidade de todos os índices, e isso pode levar muito tempo. Estou surpreso que leve mais de 2 horas, mas essa é uma tabela muito grande, então os índices também seriam muito grandes. INSERT e COPY bem-sucedidos não geram tuplas mortas. Mas mesmo um UPDATE ou DELETE faz, assim como qualquer INSERT ou COPY que foi revertido. Essa teoria parece compatível com o que você está vendo? Você já teve algum DELETE ou UPDATE de até mesmo uma tupla, ou já teve um INSERT que falhou?
Você pode executar um VACUUM às 21h e, se ele tiver que varrer todos os índices, o que você executar após o carregamento em massa não precisará fazê-lo novamente - a menos que o próprio carregamento em massa seja o que gerou o morto tuplas.
Mas e se você apenas executar ANALYZE, depois VACUUM, separadamente e nessa ordem, após o carregamento em massa? O problema é que um VACUUM longo está usando muito IO quando você começa a trabalhar e atrasa as coisas, ou o problema é que um VACUUM longo significa que o ANALYZE ainda não terminou e você recebe planos ruins no início do dia de trabalho?
Outro problema é que, se você não estiver fazendo nada para congelar partes da mesa, uma vez que um VACUUM anti-wraparound seja ativado, você poderá passar por um momento muito ruim. O primeiro VACUUM FREEZE que você fizer gerará uma enorme quantidade de IO e levará muito tempo, mas depois disso ele só precisará congelar pequenas partes da tabela de cada vez e, portanto, deve ser gerenciável. O bom do VACUUM FREEZE, ao contrário da varredura de índice de parte de um VACUUM, é que, se for interrompido, ele não perde todo o trabalho que fez até agora, pois as páginas registradas como congeladas no mapa de visibilidade podem ser ignoradas na próxima vez . Assim, você pode iniciar VACUUM FREEZE e depois cancelar se parecer estar causando problemas, ou se você sair de sua mesa para não estar por perto para monitorá-lo por um tempo. Então, uma vez que você está preso,