From: Tomas Vondra <tomas.vondra@enterprisedb.com>
To: M Tarkeshwar Rao <m.tarkeshwar.rao@ericsson.com>
To: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Cc: Neeraj Gupta G <neeraj.g.gupta@ericsson.com>
Cc: Atul Parashar <atul.parashar@ericsson.com>
Cc: Shishir Singh <shishir.singh@globallogic.com>
Cc: Ankit Sharma <ankit.sharma10@globallogic.com>
Subject: Re: Autovacuum not functioning for large tables but it is working for few other small tables.
Date: Thu, 17 Dec 2020 02:46:16 +0100
Message-ID: <cdfe8ced-45b9-2607-e1e5-98ee45afd694@enterprisedb.com> (raw)
In-Reply-To: <AM6PR0702MB3783AE08F338E43BE1D82914AEC50@AM6PR0702MB3783.eurprd07.prod.outlook.com>
References: <AM6PR0702MB3783F4CFF71E3FD27E9C9AC0AEC50@AM6PR0702MB3783.eurprd07.prod.outlook.com>
<AM6PR0702MB3783AE08F338E43BE1D82914AEC50@AM6PR0702MB3783.eurprd07.prod.outlook.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tomas.vondra@enterprisedb.com, m.tarkeshwar.rao@ericsson.com, neeraj.g.gupta@ericsson.com, atul.parashar@ericsson.com, shishir.singh@globallogic.com, ankit.sharma10@globallogic.com
Subject: Re: Autovacuum not functioning for large tables but it is working for few other small tables.
In-Reply-To: <cdfe8ced-45b9-2607-e1e5-98ee45afd694@enterprisedb.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox