Autovacuum Transaction Wraparound

classic Classic list List threaded Threaded
7 messages Options
Reply | Threaded
Open this post in threaded view
|

Autovacuum Transaction Wraparound

Perumal Raj
Hi Experts

I have noticed in my Database that, there is no regular Vacuum maintenance happening
So i started Weekly Job across cluster. But still i am seeing gradual growth on transacation ID. 

DB is still using default autovacuum_freeze_min_age & autovacuum_freeze_table_age.

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 
Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum Transaction Wraparound

Adrian Klaver-4
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum Transaction Wraparound

Perumal Raj
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.

Version : 9.2.24

Query : 

SELECT datname, age(datfrozenxid) FROM pg_database 
     datname      |    age    
------------------+-----------
 template1        | 133492380
 template0        | 180987489
 postgres         |  93330701
 nagio            | 109936658
 arch__old        | 109936658
 prod .           | 151621905
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 <[hidden email]> 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
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum Transaction Wraparound

Adrian Klaver-4
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
> <[hidden email] <mailto:[hidden email]>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum Transaction Wraparound

Perumal Raj
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 ) ?

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

Regards,



On Mon, Mar 11, 2019 at 12:45 PM Adrian Klaver <[hidden email]> wrote:
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
> <[hidden email] <mailto:[hidden email]>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]
Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum Transaction Wraparound

Adrian Klaver-4
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
[hidden email]

Reply | Threaded
Open this post in threaded view
|

Re: Autovacuum Transaction Wraparound

Adrian Klaver-4
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
> <[hidden email] <mailto:[hidden email]>> 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
>     [hidden email] <mailto:[hidden email]>
>


--
Adrian Klaver
[hidden email]