Обсуждение: 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.
От
M Tarkeshwar Rao
Дата:
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.
От
Martín Marqués
Дата:
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
Absolutely check the logs, or do a manual vacuum verbose with setting cost delay and cost limit (and maintenance work mem) the same as the values for auto vacuum runs. It should work out the same and you could time it for a period when the system is more lightly used it applicable.
If you have many very large indexes on the tables with a high number of tuples and bloat, that may be slowing the execution particularly if your allowed work memory for the operation doesn't allow a single pass of the index.
If you are on PG12+, you can reindex concurrently and then run vacuum and see how it goes.
Freezing will automatically happen according to settings, but if it is near the threshold then it could be that autovacuum is doing more work scanning old data. A manual vacuum freeze would mitigate that. That may not be significant though.
For your larger tables, or system in general, turning down your scale factor settings will qualify tables for autovacuum sooner. If it hurts, you aren't doing it often enough.
Also, reducing cost delays may be needed to pause for less time in the middle of autovacuum executions. The default changed from 20ms to 2ms with PG12 but if your I/O system can handle it, lower may be prudent to get the work done more quickly.
On Wed, Dec 16, 2020 at 6:55 AM M Tarkeshwar Rao <m.tarkeshwar.rao@ericsson.com> wrote:
...
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.
It might be helpful for us to see what data you are looking at to reach this conclusion.
Is autovacuum not working against large sized tables or Is there any parameters which need to set to make autovacuum functioning?
Autovacuum is not inherently broken for large tables. But vacuuming them takes longer than for small tables. If it is frequently interrupted by things like CREATE INDEX, ALTER TABLE, or database shutdown and restart, then it might never get through the entire table without interruption. If it is getting interrupted, you should see messages in the log file about it. You can also check pg_stat_user_tables to see when it was last successfully (to completion) auto vacuumed, and on new enough versions you can look in pg_stat_progress_vacuum to monitor the vacuuming while it occurs.
Cheers,
Jeff
RE: Autovacuum not functioning for large tables but it is working for few other small tables.
От
M Tarkeshwar Rao
Дата:
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