Re: autovacuum big table taking hours and sometimes seconds

Hey,
As I said, I set the next settings for the toasted table :

alter table orig_table set (toast.autovacuum_vacuum_scale_factor = 0);

alter table orig_table set (toast.autovacuum_vacuum_threshold =10000);

Can you explain a little bit more why you decided that the autovacuum spent
it time on sleeping ?

I see the autovacuum statistics from the logs, how can I check that the
workers are busy very often ?

My vacuum limit is 200 right now, basically If vacuum runs on my toasted
table and reached 200 but it didnt finish to clean all the dead tuples,
after the nap, should it continue cleaning it or wait until the
vacuum_threshold hit again ?

‫בתאריך יום ד׳, 6 בפבר׳ 2019 ב-15:05 מאת ‪David Rowley‬‏ <‪
david(dot)rowley(at)2ndquadrant(dot)com‬‏>:‬

> On Thu, 7 Feb 2019 at 00:17, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>
> wrote:
> >
> > On Wed, 2019-02-06 at 12:29 +0200, Mariel Cherkassky wrote:
> > > Now the question is how to handle or tune it ? Is there any change
> that I need to increase the cost_limit / cost_delay ?
> >
> > Maybe configuring autovacuum to run faster will help:
> >
> > alter table orig_table set (toast.autovacuum_vacuum_cost_limit = 2000);
> >
> > Or, more extreme:
> >
> > alter table orig_table set (toast.autovacuum_vacuum_cost_delay = 0);
>
> 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.
>
> --
> David Rowley http://www.2ndQuadrant.com/
> PostgreSQL Development, 24x7 Support, Training & Services
>