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.von...@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
