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

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 

Sent from Yahoo Mail on Android

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

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

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 
Sent from Yahoo Mail on Android

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

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

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

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