Re: Autovacuum Transaction Wraparound - Mailing list pgsql-general

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