Thread: Autovacuum Transaction Wraparound

On 3/11/19 11:51 AM, Perumal Raj wrote:
> Hi Experts
> 
> I have noticed in my Database that, there is no regular Vacuum 
> maintenance happening

What Postgres version?

> So i started Weekly Job across cluster. But still i am seeing gradual 
> growth on transacation ID.

What query are you using?

> 
> DB is still using default autovacuum_freeze_min_age & 
> autovacuum_freeze_table_age.

What are the actual settings for?:

https://www.postgresql.org/docs/10/runtime-config-autovacuum.html

> 
> Question : Since i am running regularly vacuum job ( weekly) and the 
> Transaction age is gradually growing , What is next once i hit 200M 
> limit ( default ).
> Should i increase my default value ? If so any calculation for increase 
> the value based on my DB transaction growth.
> 
> Thanks,
> Raj


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 3/11/19 12:16 PM, Perumal Raj wrote:
> Hi Adrian/Joshua
> 
> Sorry to mention in the previous thread,
> 
> Auto-vacuum is already enabled in the Cluster and its doing the job 
> perfectly. But only thing manual vacuum scheduled now (weekly Cluster 
> wide) after noticing 'Transaction Wraparound message during Autovacuum run.

What was the full message?

> 
> Version : 9.2.24

FYI 9.2 is 1 years+ past EOL.

> 
> Query :
> 
> SELECT datname, age(datfrozenxid) FROM pg_database
>       datname      |    age
> ------------------+-----------
>   template1        | 133492380
>   template0        | 180987489
>   postgres         |  93330701
>   nagio            | 109936658
>   arch__old        | 109936658
>   prod .           | 151621905

So at some point the server will force a VACUUM to freeze ids and 
prevent wraparound before the age gets to your autovacuum_freeze_max_age 
below. That might even have been the message you saw.

> 
> Settings :
> 
>                name               |  setting  | unit
> ---------------------------------+-----------+------
>   autovacuum                      | on        |
>   autovacuum_analyze_scale_factor | 0.05      |
>   autovacuum_analyze_threshold    | 50        |
>   autovacuum_freeze_max_age       | 200000000 |
>   autovacuum_max_workers          | 3         |
>   autovacuum_naptime              | 60        | s
>   autovacuum_vacuum_cost_delay    | 20        | ms
>   autovacuum_vacuum_cost_limit    | -1        |
>   autovacuum_vacuum_scale_factor  | 0.2       |
>   autovacuum_vacuum_threshold     | 50        |
> 
>   log_autovacuum_min_duration     |-1 .       |
> 
> Regards,
> 
> 
> On Mon, Mar 11, 2019 at 12:07 PM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 3/11/19 11:51 AM, Perumal Raj wrote:
>      > Hi Experts
>      >
>      > I have noticed in my Database that, there is no regular Vacuum
>      > maintenance happening
> 
>     What Postgres version?
> 
>      > So i started Weekly Job across cluster. But still i am seeing
>     gradual
>      > growth on transacation ID.
> 
>     What query are you using?
> 
>      >
>      > DB is still using default autovacuum_freeze_min_age &
>      > autovacuum_freeze_table_age.
> 
>     What are the actual settings for?:
> 
>     https://www.postgresql.org/docs/10/runtime-config-autovacuum.html
> 
>      >
>      > Question : Since i am running regularly vacuum job ( weekly) and the
>      > Transaction age is gradually growing , What is next once i hit 200M
>      > limit ( default ).
>      > Should i increase my default value ? If so any calculation for
>     increase
>      > the value based on my DB transaction growth.
>      >
>      > Thanks,
>      > Raj
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 3/11/19 1:24 PM, Perumal Raj wrote:
> Hi Adrian
> 
> What was the full message?
> 
>             autovacuum: VACUUM <table name >(to prevent wraparound)
> 
> Though i am running vacuum manually (nowadays) and autovacuum is running 
> perfectly once its threshold reaches.
> 
> What will happen if my DB reaches 200M transaction age again ? ( Here my 
> understanding is no dead tuples to cleanup --- I may be missing full 
> concept , Please correct me if i am wrong) .
> 
> What will be impact to DB ( Performance ) During Vacuum freeze ( My 
> Assumption is autovacuum will run "vacuum freeze" once DB age reached 
> 200M ) ?

I would read this:

https://www.postgresql.org/docs/9.2/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND

I believe it will answer most of your questions.

> 
> When should i consider to increase pg_settings value with respect to 
> Autovacuum ?
> 
> Regards,
> 
> 



-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 3/14/19 11:45 AM, Perumal Raj wrote:

Please reply to list also.
Ccing list.

> Thanks Adrian for the reply,
> 
> Yes , i went through the document.
> 
> My Only Worry is , Will i hit performance issue once i reach 200M 
> Age(default) even i have only static table.

If is truly static then there will be no or little xids generated  so 
the age will not be reached or reached slowly. Otherwise the normal 
autovacuuming will keep the xids under control. The caveat being an 
operation or operations that generate a lot of xids faster then the 
normal autovac settings can handle.

> 
> 
> 
> On Wed, Mar 13, 2019 at 12:23 PM Adrian Klaver 
> <adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>> wrote:
> 
>     On 3/11/19 1:24 PM, Perumal Raj wrote:
>      > Hi Adrian
>      >
>      > What was the full message?
>      >
>      >             autovacuum: VACUUM <table name >(to prevent wraparound)
>      >
>      > Though i am running vacuum manually (nowadays) and autovacuum is
>     running
>      > perfectly once its threshold reaches.
>      >
>      > What will happen if my DB reaches 200M transaction age again ? (
>     Here my
>      > understanding is no dead tuples to cleanup --- I may be missing full
>      > concept , Please correct me if i am wrong) .
>      >
>      > What will be impact to DB ( Performance ) During Vacuum freeze ( My
>      > Assumption is autovacuum will run "vacuum freeze" once DB age
>     reached
>      > 200M ) ?
> 
>     I would read this:
> 
>     https://www.postgresql.org/docs/9.2/routine-vacuuming.html#VACUUM-FOR-WRAPAROUND
> 
>     I believe it will answer most of your questions.
> 
>      >
>      > When should i consider to increase pg_settings value with respect to
>      > Autovacuum ?
>      >
>      > Regards,
>      >
>      >
> 
> 
> 
>     -- 
>     Adrian Klaver
>     adrian.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com