Обсуждение: Template0 datfrozenxid age is 160million and progressing

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

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

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

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

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

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

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

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

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