Thread: Template0 datfrozenxid age is 160million and progressing

Template0 datfrozenxid age is 160million and progressing

From
Rijo Roy
Date:
Hello Everyone, 

I am observing a steady increase in age(datfrozenxid) of template0 database in my Postgresql 10 running in a RHEL 6.9
Currently, it is at 166846989 and I am not able to vacuum the same as datallowconn is false. Thought of setting it to true and perform a vacuum freeze on the same. Before doing that, I wanted to check whether I should be worrying for something here or can I just go ahead with my plan. 

Thanks, 

Re: Template0 datfrozenxid age is 160million and progressing

From
Tom Lane
Date:
Rijo Roy <rjo_roy@yahoo.com> writes:
> I am observing a steady increase in age(datfrozenxid) of template0 database in my Postgresql 10 running in a RHEL
6.9Currently,it is at 166846989 and I am not able to vacuum the same as datallowconn is false. Thought of setting it to
trueand perform a vacuum freeze on the same. Before doing that, I wanted to check whether I should be worrying for
somethinghere or can I just go ahead with my plan.  

I would leave well enough alone.  Unless you've changed the default
values of autovacuum parameters, that behavior is perfectly normal
and not worrisome.  autovacuum should kick in and do something
about it at 200 million xacts (autovacuum_freeze_max_age).

            regards, tom lane


Re: Template0 datfrozenxid age is 160million and progressing

From
Rijo Roy
Date:
Hi Tom, 

Thanks for the advice. Autovacuum is disabled in the environment and I cannot take a call on enabling it back. I can only run manual vacuum on the database as the dev team fears autoanalyze changing the query performance. 
Do you still think we don't need to take any actions. 

Thanks, 
Rijo Roy 


On Wed, 1 Aug 2018 at 7:38 pm, Tom Lane
<tgl@sss.pgh.pa.us> wrote:
Rijo Roy <rjo_roy@yahoo.com> writes:

> I am observing a steady increase in age(datfrozenxid) of template0 database in my Postgresql 10 running in a RHEL 6.9Currently, it is at 166846989 and I am not able to vacuum the same as datallowconn is false. Thought of setting it to true and perform a vacuum freeze on the same. Before doing that, I wanted to check whether I should be worrying for something here or can I just go ahead with my plan. 


I would leave well enough alone.  Unless you've changed the default
values of autovacuum parameters, that behavior is perfectly normal
and not worrisome.  autovacuum should kick in and do something
about it at 200 million xacts (autovacuum_freeze_max_age).

            regards, tom lane

Re: Template0 datfrozenxid age is 160million and progressing

From
Tom Lane
Date:
Rijo Roy <rjo_roy@yahoo.com> writes:
> Thanks for the advice. Autovacuum is disabled in the environment and I cannot take a call on enabling it back. I can
onlyrun manual vacuum on the database as the dev team fears autoanalyze changing the query performance. Do you still
thinkwe don't need to take any actions.  

IMO, the action you need to take is enabling autovacuum.  We've
seen many many people go down the path you are taking, and it's
generally led to no good in the end.  Manual vacuuming tends
to miss stuff, and it cannot react adequately to activity spikes.

            regards, tom lane


Re: Template0 datfrozenxid age is 160million and progressing

From
Rijo Roy
Date:
I agree.. But unfortunately it is the business call and we cannot alter it.. I am planning to convince them by keeping autovacuum_analyze_threshold to a high value so that auto analyse will not kick in very often leaving the autovacuum to do its job.. 
Please advise. 

Thanks, 
Rijo Roy 


On Wed, 1 Aug 2018 at 7:54 pm, Tom Lane
<tgl@sss.pgh.pa.us> wrote:
Rijo Roy <rjo_roy@yahoo.com> writes:
> Thanks for the advice. Autovacuum is disabled in the environment and I cannot take a call on enabling it back. I can only run manual vacuum on the database as the dev team fears autoanalyze changing the query performance. Do you still think we don't need to take any actions. 

IMO, the action you need to take is enabling autovacuum.  We've
seen many many people go down the path you are taking, and it's
generally led to no good in the end.  Manual vacuuming tends
to miss stuff, and it cannot react adequately to activity spikes.


            regards, tom lane

Re: Template0 datfrozenxid age is 160million and progressing

From
David Rowley
Date:
On 2 August 2018 at 02:31, Rijo Roy <rjo_roy@yahoo.com> wrote:
> I agree.. But unfortunately it is the business call and we cannot alter it..
> I am planning to convince them by keeping autovacuum_analyze_threshold to a
> high value so that auto analyse will not kick in very often leaving the
> autovacuum to do its job..
> Please advise.

If autovacuum is disabled, it'll still kick in for any anti-wraparound
work that needs to be performed.

This is also mentioned in the docs:

"Note that even when this parameter is disabled, the system will
launch autovacuum processes if necessary to prevent transaction ID
wraparound. See Section 24.1.5 for more information."

https://www.postgresql.org/docs/10/static/runtime-config-autovacuum.html

-- 
 David Rowley                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services


Re: Template0 datfrozenxid age is 160million and progressing

From
Andres Freund
Date:
Hi,

On 2018-08-01 10:24:24 -0400, Tom Lane wrote:
> Rijo Roy <rjo_roy@yahoo.com> writes:
> > Thanks for the advice. Autovacuum is disabled in the environment and I cannot take a call on enabling it back. I
canonly run manual vacuum on the database as the dev team fears autoanalyze changing the query performance. Do you
stillthink we don't need to take any actions. 
 
> 
> IMO, the action you need to take is enabling autovacuum.  We've
> seen many many people go down the path you are taking, and it's
> generally led to no good in the end.  Manual vacuuming tends
> to miss stuff, and it cannot react adequately to activity spikes.

But it shouldn't matter here, autovacuum will start regardless, no?

Greetings,

Andres Freund


Re: Template0 datfrozenxid age is 160million and progressing

From
Tom Lane
Date:
Andres Freund <andres@anarazel.de> writes:
> On 2018-08-01 10:24:24 -0400, Tom Lane wrote:
>> IMO, the action you need to take is enabling autovacuum.  We've
>> seen many many people go down the path you are taking, and it's
>> generally led to no good in the end.  Manual vacuuming tends
>> to miss stuff, and it cannot react adequately to activity spikes.

> But it shouldn't matter here, autovacuum will start regardless, no?

Sure, once it decides that emergency anti-wraparound vacuuming is
necessary.  I really doubt the OP wants that to happen; it's the
exact opposite of non-intrusive.

            regards, tom lane


Re: Template0 datfrozenxid age is 160million and progressing

From
Andres Freund
Date:
On 2018-08-01 12:07:16 -0400, Tom Lane wrote:
> Andres Freund <andres@anarazel.de> writes:
> > On 2018-08-01 10:24:24 -0400, Tom Lane wrote:
> >> IMO, the action you need to take is enabling autovacuum.  We've
> >> seen many many people go down the path you are taking, and it's
> >> generally led to no good in the end.  Manual vacuuming tends
> >> to miss stuff, and it cannot react adequately to activity spikes.
> 
> > But it shouldn't matter here, autovacuum will start regardless, no?
> 
> Sure, once it decides that emergency anti-wraparound vacuuming is
> necessary.  I really doubt the OP wants that to happen; it's the
> exact opposite of non-intrusive.

That's solely what would trigger it were autovacuum enabled, too? I've
complained about "emergency anti-wraparound" beeing anything but
emergency (they're largely unavoidable unless you manually script it),
but they're what happen once autovacuum_freeze_max_age is reached, and
that's the only trigger for vacuuming old relations independent of other
activity?

Greetings,

Andres Freund


Re: Template0 datfrozenxid age is 160million and progressing

From
Alvaro Herrera
Date:
On 2018-Aug-01, Andres Freund wrote:

> On 2018-08-01 12:07:16 -0400, Tom Lane wrote:
> > Andres Freund <andres@anarazel.de> writes:
> > > On 2018-08-01 10:24:24 -0400, Tom Lane wrote:
> > >> IMO, the action you need to take is enabling autovacuum.  We've
> > >> seen many many people go down the path you are taking, and it's
> > >> generally led to no good in the end.  Manual vacuuming tends
> > >> to miss stuff, and it cannot react adequately to activity spikes.
> > 
> > > But it shouldn't matter here, autovacuum will start regardless, no?
> > 
> > Sure, once it decides that emergency anti-wraparound vacuuming is
> > necessary.  I really doubt the OP wants that to happen; it's the
> > exact opposite of non-intrusive.
> 
> That's solely what would trigger it were autovacuum enabled, too? I've
> complained about "emergency anti-wraparound" beeing anything but
> emergency (they're largely unavoidable unless you manually script it),
> but they're what happen once autovacuum_freeze_max_age is reached, and
> that's the only trigger for vacuuming old relations independent of other
> activity?

With a small database like template0, it doesn't matter.  The vacuuming
is going to be over before OP realizes it has happened anyway.
Certainly having it happen on a normal-sized table can become
problematic, but presumably OP has taken steps to avoid it when
disabling autovacuum (which is why only template0 is getting into
trouble.)

I think emergency vacuum should behave differently (not scan indexes,
just apply HOT page prune and clear old XIDs/multixacts), which would
make it much faster, but that's a separate line of thought (and of
development).

-- 
Álvaro Herrera                https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services


Re: Template0 datfrozenxid age is 160million and progressing

From
Andres Freund
Date:
On 2018-08-01 12:20:15 -0400, Alvaro Herrera wrote:
> On 2018-Aug-01, Andres Freund wrote:
> 
> > On 2018-08-01 12:07:16 -0400, Tom Lane wrote:
> > > Andres Freund <andres@anarazel.de> writes:
> > > > On 2018-08-01 10:24:24 -0400, Tom Lane wrote:
> > > >> IMO, the action you need to take is enabling autovacuum.  We've
> > > >> seen many many people go down the path you are taking, and it's
> > > >> generally led to no good in the end.  Manual vacuuming tends
> > > >> to miss stuff, and it cannot react adequately to activity spikes.
> > > 
> > > > But it shouldn't matter here, autovacuum will start regardless, no?
> > > 
> > > Sure, once it decides that emergency anti-wraparound vacuuming is
> > > necessary.  I really doubt the OP wants that to happen; it's the
> > > exact opposite of non-intrusive.
> > 
> > That's solely what would trigger it were autovacuum enabled, too? I've
> > complained about "emergency anti-wraparound" beeing anything but
> > emergency (they're largely unavoidable unless you manually script it),
> > but they're what happen once autovacuum_freeze_max_age is reached, and
> > that's the only trigger for vacuuming old relations independent of other
> > activity?
> 
> With a small database like template0, it doesn't matter.  The vacuuming
> is going to be over before OP realizes it has happened anyway.
> Certainly having it happen on a normal-sized table can become
> problematic, but presumably OP has taken steps to avoid it when
> disabling autovacuum (which is why only template0 is getting into
> trouble.)

Right.


> I think emergency vacuum should behave differently (not scan indexes,
> just apply HOT page prune and clear old XIDs/multixacts), which would
> make it much faster, but that's a separate line of thought (and of
> development).

What I'd love is for freeze_max_age triggered vacuums *not* to be
emergency vacuums. They should just be normal ones. There should be a
separate GUC that triggers the emergency bit. It's really annoying to
get a hard to kill ant-wraparound autovacuum on an insert only table,
where it's the only thing that'll trigger the autovacuum.

Greetings,

Andres Freund


Re: Template0 datfrozenxid age is 160million and progressing

From
Vik Fearing
Date:
On 01/08/18 18:36, Andres Freund wrote:
> It's really annoying to
> get a hard to kill ant-wraparound autovacuum on an insert only table,
> where it's the only thing that'll trigger the autovacuum.

Somebody should do something about that.
-- 
Vik Fearing                                          +33 6 46 75 15 36
http://2ndQuadrant.fr     PostgreSQL : Expertise, Formation et Support


Re: Template0 datfrozenxid age is 160million and progressing

From
Stephen Eilert
Date:
160 million is a very low number. I manage production databases which reach this value in a day easily. As other said, 200 million is the default threshold for the anti-wraparound vacuums. I wouldn't worry, specially for template0.

That said, there is nothing preventing you from temporarily changing DATALLOWCONN, running vaccuum (which should be very quick) and then changing it back. But you should not have to.  I do that in our production database, but only because it suffers from a bad schema design and we ended up with thousands of tables, which is too much for the autovacuum workers to cope alone, so they need a manual "boost". I still don't disable autovacuum.

I don't really understand the bit about autovacuum changing query performance. In which scenario would it be preferable to have outdated analyzer statistics? This would be like running a system with garbage collection disabled because GC can increase the amount of free memory. That's the whole point.

— Stephen