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:
