Re: autovacuum big table taking hours and sometimes seconds

[MichaelDBA <MichaelDBA@xxxxxxxxxxx>]

Hi all,

In the myriad of articles written about autovacuum tuning, I really like this article by Tomas Vondra of 2ndQuadrant:
https://blog.2ndquadrant.com/autovacuum-tuning-basics/

It is a concise article that touches on all the major aspects of autovacuuming tuning: thresholds, scale factors, throttling, etc.

Regards and happy vacuuming to yas!
Michael Vitale

Mariel Cherkassky

Wednesday, February 6, 2019 8:41 AM

which one you mean ? I changed the threshold and the scale for the specific table...

dangal

Wednesday, February 6, 2019 8:36 AM

Would it be nice to start changing those values ​​found in the default
postgres.conf so low?



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-performance-f2050081.html

David Rowley

Wednesday, February 6, 2019 8:05 AM

Going by the block hits/misses/dirtied and the mentioned vacuum times,
it looks like auto-vacuum is set to the standard settings and if so it
spent about 100% of its time sleeping on the job.

It might be a better idea to consider changing the vacuum settings
globally rather than just for one table.

Running a vacuum_cost_limit of 200 is likely something you'd not want
to ever do with modern hardware... well maybe unless you just bought
the latest Raspberry PI, or something. You should be tuning that
value to something that runs your vacuums to a speed you're happy with
but leaves enough IO and CPU for queries running on the database.

If you see that all auto-vacuum workers are busy more often than not,
then they're likely running too slowly and should be set to run more
quickly.