Re: Autovacuum Transaction Wraparound - Mailing list pgsql-general
| From | Perumal Raj |
|---|---|
| Subject | Re: Autovacuum Transaction Wraparound |
| Date | |
| Msg-id | CALvqh4om9nPLn9E9U57gN+z5XTVoj0C_4fbjaQzMnWpqk5PgYA@mail.gmail.com Whole thread Raw |
| In response to | Re: Autovacuum Transaction Wraparound (Adrian Klaver <adrian.klaver@aklaver.com>) |
| Responses |
Re: Autovacuum Transaction Wraparound
(Adrian Klaver <adrian.klaver@aklaver.com>)
|
| List | pgsql-general |
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 <adrian.klaver@aklaver.com> 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
> <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
pgsql-general by date: