On Fri, 2021-02-19 at 10:51 +0000, M Tarkeshwar Rao wrote: > Please find the Vacuum(verbose) output. Can you please suggest what is the reason? > How can we avoid these scenarios? > > The customer tried to run the VACUUM(verbose) last night, but it was running > continuously for 5 hours without any visible progress. So they had to abort it > as it was going to exhaust their maintenance window. > > db_Server14=# VACUUM (VERBOSE) audittraillogentry; > INFO: vacuuming "mmsuper.audittraillogentry" > INFO: scanned index "audittraillogentry_pkey" to remove 11184539 row versions > DETAIL: CPU 25.24s/49.11u sec elapsed 81.33 sec > INFO: scanned index "audit_intime_index" to remove 11184539 row versions > DETAIL: CPU 23.27s/59.28u sec elapsed 88.63 sec > INFO: scanned index "audit_outtime_index" to remove 11184539 row versions > DETAIL: CPU 27.02s/55.10u sec elapsed 92.04 sec > INFO: scanned index "audit_sourceid_index" to remove 11184539 row versions > DETAIL: CPU 110.81s/72.29u sec elapsed 260.71 sec > [and so on, the same 6 indexes are repeatedly scanned] PostgreSQL performs VACUUM in batches of "maintenance_work_mem" size of tuple identifiers. If that parameter is small, the indexes have to be scanned often. Try increasing "maintenance_work_mem" to 1GB (if you have enough RAM), that will make it faster. Yours, Laurenz Albe -- Cybertec | https://www.cybertec-postgresql.com
