+ pgsql-general Thanks and Regards, Ashu Pachauri
---------- Forwarded message --------- From: Ashu Pachauri <ashu210...@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: <raghavendra...@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 < raghavendra...@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.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 >
