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
