Re: Autovacuum not functioning for large tables but it is working for few other small tables.

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