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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>> 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@xxxxxxxxxxx <mailto:adrian.klaver@xxxxxxxxxxx>
--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx
