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

Поиск
Список
Период
Сортировка
От Ashu Pachauri
Тема Fwd: is there any adverse effect on DB if I set autovacuum scale factor to zero?
Дата
Msg-id CA+C_EDhiY5Qq9XD_FD7_HdXk1oyyXmfiTZ=8Z2HKkLpXF0p75w@mail.gmail.com
обсуждение исходный текст
Ответ на is there any adverse effect on DB if I set autovacuum scale factor to zero?  (Raghavendra Rao J S V <raghavendrajsv@gmail.com>)
Ответы Re: Fwd: is there any adverse effect on DB if I set autovacuum scalefactor to zero?  (Tomas Vondra <tomas.vondra@2ndquadrant.com>)
Список pgsql-general
Дерево обсуждения
+ pgsql-general

Thanks and Regards,
Ashu Pachauri


---------- Forwarded message ---------
From: Ashu Pachauri <ashu210890@gmail.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@gmail.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@gmail.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.vondra@2ndquadrant.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
Вложения

В списке pgsql-general по дате отправления:

Предыдущее
От: "Phil Endecott"
Дата:
Сообщение: Re: Replication failure, slave requesting old segments
Следующее
От: Tomas Vondra
Дата:
Сообщение: Re: Fwd: is there any adverse effect on DB if I set autovacuum scalefactor to zero?