Re: Autovacuum Transaction Wraparound

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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com