No trabalho, temos uma tabela de banco de dados que usamos para trabalhos enfileirados, então ela vê muito rendimento. Um problema que encontramos é que após um fim de semana sem nenhuma alteração de código, os índices nesta tabela são preenchidos com tuplas mortas. Quando executamos VACUUM VERBOSE ANALYZE, isso aparece como "600461 versões de linha morta não podem ser removidas ainda, xmin mais antigo: 902335252" (consulte [1]).
Quando procuramos o que estava impedindo o vácuo de limpá-los, ele apontou para uma consulta em execução LISTEN, usando o recurso pubsub do Postgres. Isso estava em execução há vários dias, o que eu acho que é uma maneira esperada de usar o LISTEN.
Portanto, uma maneira de resolver isso é garantir que nossos servidores de aplicativos sejam reiniciados regularmente, para que não possam ficar ouvindo por tanto tempo, ou reinicie qualquer LISTENs em execução por mais de um período de tempo. Dito isso, eu queria saber se havia uma maneira fácil de lidar com isso com o Postgres. Existe alguma maneira de configurar a transação para não bloquear a limpeza das tuplas mortas? Poderia algo estar errado no código do nosso aplicativo que faz com que LISTE tenha esse comportamento?
"Não tenha transações de longa duração" faz sentido para mim como um princípio geral, mas é bastante estranho para o LISTENqual se destina a ser usado como uma maneira de obter atualizações de streaming, então eu esperava que o Postgres pudesse ter uma boa solução.
=> SELECT *
-> FROM pg_stat_activity
-> WHERE backend_xmin = '902335252';
-[ RECORD 1 ]----+------------------------------
datid | 16404
datname | company_web_backend
pid | 8936
leader_pid |
usesysid | 16388
usename | company_web_backend
application_name |
client_addr | 10.0.1.80
client_hostname |
client_port | 56654
backend_start | 2021-07-24 01:21:28.270245+00
xact_start | 2021-07-24 01:21:28.279008+00
query_start | 2021-07-24 01:21:28.279008+00
state_change | 2021-07-24 01:21:28.281313+00
wait_event_type | Client
wait_event | ClientWrite
state | idle
backend_xid |
backend_xmin | 902335252
query | LISTEN queued_jobs
backend_type | client backend
[1]
server=> VACUUM VERBOSE ANALYZE queued_jobs;
INFO: vacuuming "public.queued_jobs"
INFO: launched 2 parallel vacuum workers for index cleanup (planned: 2)
INFO: "queued_jobs": found 0 removable, 5324589 nonremovable row versions in 553064 out of 8685508 pages
DETAIL: 600461 dead row versions cannot be removed yet, oldest xmin: 902335252
There were 9061227 unused item identifiers.
Skipped 3 pages due to buffer pins, 5619824 frozen pages.
0 pages are entirely empty.
CPU: user: 1.42 s, system: 1.25 s, elapsed: 3.10 s.
INFO: vacuuming "pg_toast.pg_toast_37823"
INFO: "pg_toast_37823": found 0 removable, 414848 nonremovable row versions in 79545 out of 16530493 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 902335252
There were 0 unused item identifiers.
Skipped 0 pages due to buffer pins, 14394456 frozen pages.
0 pages are entirely empty.
CPU: user: 0.46 s, system: 0.12 s, elapsed: 0.58 s.
INFO: analyzing "public.queued_jobs"
INFO: "queued_jobs": scanned 30000 of 8685508 pages, containing 311159 live rows and 2103 dead rows; 30000 rows in sample, 90085799 estimated total rows
VACUUM