Thread: Template0 datfrozenxid age is 160million and progressing
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,
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
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
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
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
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
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