Thread: Autovacuum not functioning for large tables but it is working for few other small tables.
Autovacuum not functioning for large tables but it is working for few other small tables.
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?
Any suggestions?
Regards
Tarkeshwar
Re: Autovacuum not functioning for large tables but it is working for few other small tables.
Hi, > 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 havea 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 autovacuumwas 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? Do you have autovacuum logging enabled in this server? If so, would be good if you could share them here. Having the output from logs of autovacuum for these tables would give some insights on where the problem might reside. -- Martín Marqués It’s not that I have something to hide, it’s that I have nothing I want you to see
Re: Autovacuum not functioning for large tables but it is working for few other small tables.
Re: Autovacuum not functioning for large tables but it is working for few other small tables.
...
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?
RE: Autovacuum not functioning for large tables but it is working for few other small tables.
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’thave any clue till now on why exactly the auto-vacuum is not working for the table. So we need to have a work aroundto 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@enterprisedb.com> Sent: Thursday, December 17, 2020 7:16 AM To: M Tarkeshwar Rao <m.tarkeshwar.rao@ericsson.com>; pgsql-performance@postgresql.org Cc: Neeraj Gupta G <neeraj.g.gupta@ericsson.com>; Atul Parashar <atul.parashar@ericsson.com>; Shishir Singh <shishir.singh@globallogic.com>;Ankit Sharma <ankit.sharma10@globallogic.com> 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 weredeleted 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