Re: Autovacuum Transaction Wraparound

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 <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
>