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

+ pgsql-general

Thanks and Regards,
Ashu Pachauri

---------- Forwarded message ---------
From: Ashu Pachauri <ashu210890(at)gmail(dot)com>
Date: Mon, Aug 13, 2018 at 7:53 PM
Subject: Re: is there any adverse effect on DB if I set autovacuum scale
factor to zero?
To: <raghavendrajsv(at)gmail(dot)com>

The way I see *autovacuum_vacuum_scale_factor* is not in terms of absolute
number but as the percentage of any table that can consist of updated /
deleted tuples to make it eligible for vacuuming. A factor of 0.1 ensures
that your tables would be eligible for vacuuming if more than 10% of the
tuples are deleted/updated.
1. If you think that 10% is too high for you in terms of storage cost, you
can decrease the number or set it to zero. But, I would advise to increase
the value of *autovacuum_vacuum_threshold* to something reasonable if you
do that, otherwise you pay the CPU cost frequent vacuuming across all
tables.
2. However, if your issue is not the fixed 10% overhead but the lack of
throughput i.e. you see the number of deleted/updated tuples keeps
increasing in an unbounded fashion, the right way to deal with it is a)
Having higher value of *autovacuum_max_workers* b) lower value for
*autovacuum_naptime*.

Apart from configuration tuning, one common reason for low vacuum
throughput is lock waits. You can turn on *log_lock_waits* config to find
out if that's what's happening. As a general rule of thumb, you should not
have long running transactions, especially the ones that require *share/share
row exclusive/ exclusive /access exclusive* locks. They not only hamper
vacuuming throughput but also the throughput of your db writes in general.

Thanks and Regards,
Ashu Pachauri

On Mon, Aug 13, 2018 at 7:11 PM Raghavendra Rao J S V <
raghavendrajsv(at)gmail(dot)com> 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*
>
>
> *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
>