Re: is there any adverse effect on DB if I set autovacuum scale factor to zero?

On 08/13/2018 03:41 PM, Raghavendra Rao J S V wrote:
>
> Hi Tomas,
>
> Thank you very much for your response.
>
> As we  know table becomes a candidate for autovacuum  process based on
> below formula.
>
>
> *Autovacuum VACUUM thresold for a table = autovacuum_vacuum_scale_factor
> * number of tuples + autovacuum_vacuum_threshold*
>
>
>
> *Current settings in my database are as follows.*
>
>
> /autovacuum_vacuum_scale_factor = 0.1/
>
> /autovacuum_vacuum_threshold = 40/
>
>
>
> Due to above formula the dead tuples are accumulating based on the
> number of live tuples as show below picture.
>
>
>
> select relname,n_live_tup,n_dead_tup,(n_live_tup*.1+40)
> expected_to_autovacuum,* from pg_stat_user_tables
> where  n_dead_tup>800
> order by n_live_tup desc
> limit 100;
>
>
>
>
> In order to avoid the dead tuples accumulation I wold like to change the
> auto vacuum  settings in *"postgresql.conf"* as below.
>
> /autovacuum_vacuum_scale_factor = 0.01/
> //autovacuum_vacuum_threshold = 100/
> /

OK, so the tables apparently have enough dead tuples to trigger vacuum.
That mean the autovacuum throughput is insufficient to do all the
cleanup. If you lower the scale factor, the amount of cleanup will
*increase* (more tables being eligible for cleanup) making it less
likely autovacuum can keep up.

You need to increase the throughtput, by increasing vacuum_cost_limit or
something like that.

> *Kindly guide me your views. Does it cause any adverse effect on DB.*
> *

Well, it forces the database to do more stuff / more often, so it may
have adverse impact, of course. It's hard to say if it's going to be a
win overall, because we don't know how serious is the bloat.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services