Re: AutoVacuum and growing transaction XID's

2020-05-12 Thread github kran
Thanks for yous suggestions Michael and David. On Fri, May 8, 2020 at 4:11 PM Michael Lewis wrote: > autovacuum_naptime being only 5 seconds seems too frequent. A lock_timeout > might be 1-5 seconds depending on your system. Usually, DDL can fail and > wait a little time rather than lock the ta

Re: AutoVacuum and growing transaction XID's

2020-05-08 Thread Michael Lewis
autovacuum_naptime being only 5 seconds seems too frequent. A lock_timeout might be 1-5 seconds depending on your system. Usually, DDL can fail and wait a little time rather than lock the table for minutes and have all reads back up behind the DDL. Given you have autovacuum_vacuum_cost_limit set t

Re: AutoVacuum and growing transaction XID's

2020-05-07 Thread github kran
On Thu, May 7, 2020 at 11:04 PM David Rowley wrote: > On Fri, 8 May 2020 at 13:51, github kran wrote: > > I can't either DROP or ALTER any other tables ( REMOVE Inheritance > for any of old tables where the WRITES are not getting written to). Any of > the ALTER TABLE OR DROP TABLE DDL's a

Re: AutoVacuum and growing transaction XID's

2020-05-07 Thread github kran
Thanks David for your replies. On Thu, May 7, 2020 at 11:01 PM David Rowley wrote: > On Fri, 8 May 2020 at 09:18, github kran wrote: > > 1) We haven't changed anything related to autovacuum except a work_mem > parameter which was increased to 4 GB which I believe is not related to > autovacuum

Re: AutoVacuum and growing transaction XID's

2020-05-07 Thread David Rowley
On Fri, 8 May 2020 at 13:51, github kran wrote: > I can't either DROP or ALTER any other tables ( REMOVE Inheritance for > any of old tables where the WRITES are not getting written to). Any of the > ALTER TABLE OR DROP TABLE DDL's arer not getting exeucted even I WAITED FOR > SEVERAL MI

Re: AutoVacuum and growing transaction XID's

2020-05-07 Thread David Rowley
On Fri, 8 May 2020 at 09:18, github kran wrote: > 1) We haven't changed anything related to autovacuum except a work_mem > parameter which was increased to 4 GB which I believe is not related to > autovacuum It might want to look into increasing vacuum_cost_limit to something well above 200 or

Re: AutoVacuum and growing transaction XID's

2020-05-07 Thread github kran
On Thu, May 7, 2020 at 4:18 PM github kran wrote: > > > On Thu, May 7, 2020 at 1:33 PM Michael Lewis wrote: > >> It is trying to do a vacuum freeze. Do you have autovacuum turned off? >> Any settings changed from default related to autovacuum? >> >> https://www.postgresql.org/docs/9.6/routine-va

Re: AutoVacuum and growing transaction XID's

2020-05-07 Thread github kran
On Thu, May 7, 2020 at 1:33 PM Michael Lewis wrote: > It is trying to do a vacuum freeze. Do you have autovacuum turned off? Any > settings changed from default related to autovacuum? > > https://www.postgresql.org/docs/9.6/routine-vacuuming.html > Read 24.1.5. Preventing Transaction ID Wraparoun

Re: AutoVacuum and growing transaction XID's

2020-05-07 Thread Michael Lewis
It is trying to do a vacuum freeze. Do you have autovacuum turned off? Any settings changed from default related to autovacuum? https://www.postgresql.org/docs/9.6/routine-vacuuming.html Read 24.1.5. Preventing Transaction ID Wraparound Failures These may also be of help- https://info.crunchydata

AutoVacuum and growing transaction XID's

2020-05-07 Thread github kran
Hello Team, We are using a PostgreSQL version -9.6.12 version and from last 4 weeks our Transaction ID's (XID's) have increased by 195 million to 341 million transactions. I see the below from pg_stat_activity from the postGreSQL DB. 1) Viewing the pg_stat-activity I noticed that the vacuum qu