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 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: 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 > 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 >
Re: Long running DDL statements blocking all queries
There was too much noise in the pg_stat_activity output, so I did not post it. I'll collect the output again and post. But, when I checked in pg_stat_activity, PID 18317 is the session that's running the ALTER statement and it was showing up as "active". So, it's not blocked by anything, but the fact that the ALTER statement is long running and it's blocking the operations that are not even on the same table for the entire duration it's running is troubling. Thanks, Ashu On Thu, May 31, 2018 at 6:28 PM Fabio Pardi wrote: > Ashu, > > please, provide full output of: > > > \x > select * from pg_stat_activity ; > > > > What you posted on github is only providing a list of blocked backends. > > If I read it correctly, then PID 18317 is the root cause of all the locks, > but it does not show up in the list, not being blocked by anything... > > > regards, > > fabio pardi > > > > On 31/05/18 14:26, Ashu Pachauri wrote: > > Thanks Fabio for the reply. > > The queries are blocked in the sense that I can see them in > pg_stat_activity. > > > > Please find the query and its output for correlating the blocked > activity with blocking query from pg_state_activity and pg_locks: > https://gist.github.com/ashu210890/c39cd7a38ce37f4baab2f58e1ade1403 > > > > This output was captured after stopping all writes to our postgres > database and the only thing talking to postgres was our webserver that only > does metadata reads. As you can see from the above gist, even the 'SET' > statements are blocked waiting for the ALTER statement to finish. > > > > > > Thanks, > > Ashu > > > > > > On Thu, May 31, 2018 at 4:38 PM Fabio Pardi <mailto:f.pa...@portavita.eu>> wrote: > > > > Hi Ashu, > > > > when you say 'almost every query in our application starts getting > blocked'... > > > > > > 'blocked' as in 'they are stuck and can be seen in > pg_stat_activity'? (in this case, please post the full content of > pg_stat_activity) > > > > or > > > > 'blocked' as in 'they are waiting in pgbouncer pool? > > > > > > regards, > > > > fabio pardi > > > > > > On 31/05/18 12:38, Ashu Pachauri wrote: > > > We have been using Postgres 9.5.12 behind PGBouncer and facing > some weird issues. Whenever we running long running DDL statements (e.g. > 'add index concurently' or 'Alter table alter column type'), after some > time, we start seeing that almost every query in our application starts > getting blocked. > > > I understand that the operations I mentioned cab be unsafe, but > the queries being blocked are on completely unrelated tables. I used the > instructions given on Postgres wiki ( > https://wiki.postgresql.org/wiki/Lock_Monitoring) to correlate the > blocking and blocked statements and there seems to be absolutely no > correlation. > > > > > > Thanks, > > > Ashu > > >
Re: Long running DDL statements blocking all queries
Thanks Fabio for the reply. The queries are blocked in the sense that I can see them in pg_stat_activity. Please find the query and its output for correlating the blocked activity with blocking query from pg_state_activity and pg_locks: https://gist.github.com/ashu210890/c39cd7a38ce37f4baab2f58e1ade1403 This output was captured after stopping all writes to our postgres database and the only thing talking to postgres was our webserver that only does metadata reads. As you can see from the above gist, even the 'SET' statements are blocked waiting for the ALTER statement to finish. Thanks, Ashu On Thu, May 31, 2018 at 4:38 PM Fabio Pardi wrote: > Hi Ashu, > > when you say 'almost every query in our application starts getting > blocked'... > > > 'blocked' as in 'they are stuck and can be seen in pg_stat_activity'? (in > this case, please post the full content of pg_stat_activity) > > or > > 'blocked' as in 'they are waiting in pgbouncer pool? > > > regards, > > fabio pardi > > > On 31/05/18 12:38, Ashu Pachauri wrote: > > We have been using Postgres 9.5.12 behind PGBouncer and facing some > weird issues. Whenever we running long running DDL statements (e.g. 'add > index concurently' or 'Alter table alter column type'), after some time, we > start seeing that almost every query in our application starts getting > blocked. > > I understand that the operations I mentioned cab be unsafe, but the > queries being blocked are on completely unrelated tables. I used the > instructions given on Postgres wiki ( > https://wiki.postgresql.org/wiki/Lock_Monitoring) to correlate the > blocking and blocked statements and there seems to be absolutely no > correlation. > > > > Thanks, > > Ashu > >
Long running DDL statements blocking all queries
We have been using Postgres 9.5.12 behind PGBouncer and facing some weird issues. Whenever we running long running DDL statements (e.g. 'add index concurently' or 'Alter table alter column type'), after some time, we start seeing that almost every query in our application starts getting blocked. I understand that the operations I mentioned cab be unsafe, but the queries being blocked are on completely unrelated tables. I used the instructions given on Postgres wiki (https://wiki.postgresql.org/wiki/Lock_Monitoring) to correlate the blocking and blocked statements and there seems to be absolutely no correlation. Thanks, Ashu
