Hi all,
As we know, the VACUUM VERBOSE output has a lot of dependencies from production end and is indefinite as of now. We don’t have any clue till now on why exactly the auto-vacuum is not working for the table. So we need to have a work around to move ahead for the time being.
Can you please suggest any workaround so that we can resolve the issue or any other way by which we can avoid this situation?
Regards
Tarkeshwar
-----Original Message-----
From: Tomas Vondra <tomas.vondra@xxxxxxxxxxxxxxxx>
Sent: Thursday, December 17, 2020 7:16 AM
To: M Tarkeshwar Rao <m.tarkeshwar.rao@xxxxxxxxxxxx>; pgsql-performance@xxxxxxxxxxxxxx
Cc: Neeraj Gupta G <neeraj.g.gupta@xxxxxxxxxxxx>; Atul Parashar <atul.parashar@xxxxxxxxxxxx>; Shishir Singh <shishir.singh@xxxxxxxxxxxxxxx>; Ankit Sharma <ankit.sharma10@xxxxxxxxxxxxxxx>
Subject: Re: Autovacuum not functioning for large tables but it is working for few other small tables.
On 12/16/20 12:55 PM, M Tarkeshwar Rao wrote:
> Hi all,
>
> We have facing some discrepancy in Postgresql database related to the
> autovacuum functionality.
>
> By default autovacuum was enable on Postgres which is used to remove
> the dead tuples from the database.
>
> We have observed autovaccum cleaning dead rows from *table_A* but same
> was not functioning correctly for *table_B* which have a large
> size(100+GB) in comparision to table_A.
>
> All the threshold level requirements for autovacuum was meet and there
> are about Million’s of dead tuples but autovacuum was unable to clear
> them, which cause performance issue on production server.
>
> Is autovacuum not working against large sized tables or Is there any
> parameters which need to set to make autovacuum functioning?
>
No, autovacuum should work for tables with any size. The most likely explanation is that the rows in the large table were deleted more recently and there is a long-running transaction blocking the cleanup.
Or maybe not, hard to say with the info you provided.
A couple suggestions:
1) enable logging for autovacuum by setting
log_autovacuum_min_duration = 10ms (or similar low value)
2) check that the autovacuum is actually executed on the large table (there's last_autovacuum in pg_stat_all_tables)
3) try running VACUUM VERBOSE on the large table, it may tell you that the rows can't be cleaned up yet.
regards
--
Tomas Vondra
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company
