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@xxxxxxxxxxxxxxx> 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
Raghavendra Rao J S V
Mobile- 8861161425
