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

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*

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

Regards,
Raghavendra Rao

On 13 August 2018 at 18:05, Tomas Vondra <tomas(dot)vondra(at)2ndquadrant(dot)com>
wrote:

>
>
> On 08/13/2018 11:07 AM, Raghavendra Rao J S V wrote:
>
>> Hi All,
>>
>> We are using postgres *9.2* version on *Centos *operating system. We
>> have around *1300+* tables.We have following auto vacuum settings are
>> enables. Still few of the tables(84 tables) which are always busy are not
>> vacuumed.Dead tuples in those tables are more than 5000. Due to that
>> tables are bloating and observed few areas has performance degradation.
>>
>>
> You don't say how large the tables are, so it's impossible to say whether
> 5000 dead tuples is excessive or not. IMHO it's a negligible amount and
> should not lead to excessive bloat or issues.
>
> A certain amount of wasted is expected - it's a trade-off between
> immediate and delayed cleanup. If you delay the cleanup a bit, it's going
> to be more efficient overall.
>
> It's also unclear why the tables are not vacuumed - it may easily be due
> to all the autovacuum workers being constantly busy, unable to cleanup all
> tables in a timely manner. In that case lowering the threshold is not going
> to help, on the contrary.
>
> regards
>
> --
> Tomas Vondra http://www.2ndQuadrant.com
> PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
>

--
Regards,
Raghavendra Rao J S V
Mobile- 8861161425