Обсуждение: Avoid Wraparound Failures

Hi!

Suppose the databases on my instance are near to have a wraparound failure.

(I think so, from what I see, but in the PostgreSQL log I haven't seen any warning about It yet).

What do I have to do?

vacuum freeze;

better than,

vacuum analyze;

Or both?

If the autovacuum_freeze configuration parameters have defaults values, should I modify any first?

On Fri, 2022-03-25 at 23:16 +0100, Loles wrote:
> Suppose the databases on my instance are near to have a wraparound failure.
> 
> (I think so, from what I see, but in the PostgreSQL log I haven't seen any warning about It yet).
> 
> What do I have to do?
> 
> vacuum freeze;
> 
> better than,
> 
> vacuum analyze;
> 
> Or both?
> 
> If the autovacuum_freeze configuration parameters have defaults values, should I modify any first?
> 
> More I read of this topic, more confused I am.
> 
> Please, I need simple and wise advice :)

DON'T PANIC

If what you see is the age of your oldest unfrozen rows approaching 200 million,
everything is just normal.  That's when anti-wraparound autovacuum *begins*.

Normally, you have nothing to do, except perhaps make sure than autovacuum is
fast enough (autovacuum_vacuum_cost_delay = 2).

If you want to prevent that autovacuum run from happening while your system is
busy (which normally also is no problem), you can trigger a manual VACUUM at
a time of lower database activity.  But make it a plain VACUUM, not a
VACUUM (FREEZE) or (god forbid) VACUUM (FULL), and only VACUUM those tables
that are large and approaching the threshold.  And don't VACUUM them all at the
same time.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

On 3/25/22 23:34, Laurenz Albe wrote:
> On Fri, 2022-03-25 at 23:16 +0100, Loles wrote:
>> Suppose the databases on my instance are near to have a wraparound failure.
>>
>> (I think so, from what I see, but in the PostgreSQL log I haven't seen any warning about It yet).
>>
>> What do I have to do?
>>
>> vacuum freeze;
>>
>> better than,
>>
>> vacuum analyze;
>>
>> Or both?
>>
>> If the autovacuum_freeze configuration parameters have defaults values, should I modify any first?
>>
>> More I read of this topic, more confused I am.
>>
>> Please, I need simple and wise advice :)
> DON'T PANIC
>
> If what you see is the age of your oldest unfrozen rows approaching 200 million,
> everything is just normal.  That's when anti-wraparound autovacuum *begins*.
>
> Normally, you have nothing to do, except perhaps make sure than autovacuum is
> fast enough (autovacuum_vacuum_cost_delay = 2).
>
> If you want to prevent that autovacuum run from happening while your system is
> busy (which normally also is no problem), you can trigger a manual VACUUM at
> a time of lower database activity.  But make it a plain VACUUM, not a
> VACUUM (FREEZE)

Why not VACUUM FREEZE?

> or (god forbid) VACUUM (FULL), and only VACUUM those tables
> that are large and approaching the threshold.  And don't VACUUM them all at the
> same time.
>
> Yours,
> Laurenz Albe

-- 
Angular momentum makes the world go 'round.

On Fri, 2022-03-25 at 23:45 -0500, Ron wrote:
> > If you want to prevent that autovacuum run from happening while your system is
> > busy (which normally also is no problem), you can trigger a manual VACUUM at
> > a time of lower database activity.  But make it a plain VACUUM, not a
> > VACUUM (FREEZE)
> 
> Why not VACUUM FREEZE?

Because then you freeze *all* visible rows, not only those that are older than
"vacuum_freeze_min_age".  That will cause more pages to get dirtied, so there will
be more writing I/O.  And unless all these rows won't get modified in the forseeable
future, that is a waste.  "vacuum_freeze_min_age" is there for a reason: the
expectation is that rows that have not been modified for 50 million transactions
have better odds at not getting modified soon.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

On Sat, 2022-03-26 at 07:47 +0100, Loles wrote:
> So, if I have understood it correctly:
> 
> 1) set autovacuum_vacuum_cost_delay from 20 to 2

It is not strictly required, but that will keep autovacuum from taking
forever to process your big tables.

It has no influence on manual VACUUM though.

> 2) execute, only for the largest tables, vacuum command:
> 
>    vacuum tab1;
>    vacuum tab2;
> 
>    and so on

If you don't want to wait for autovacuum to do it automatically, yes.

> 3) Sleep peacefully :)

Definitely.  This is normal behavior.

> Another doubt: query filtered relkind = 'r' but there are also sequences, views, catalog tables... that are also
closeto 200 million. 
 

Sequences and views are don't need VACUUM.
Materialized views and catalog tables do.

> Vacuum the catalog tables too or is it not necessary?

Same as all other tables: do it manually or let autovacuum do it automatically.

> I understand that the views, sequences and everything that depends on the table will improve by vacuuming that
table.

There is no influence on sequences.

VACUUM can improve the performance of some queries, but mostly it is about keeping your tables
healthy and in good shape.  Views may benefit just like all other queries do.

Yours,
Laurenz Albe
-- 
Cybertec | https://www.cybertec-postgresql.com

Hi,

On Sat, Mar 26, 2022 at 07:47:17AM +0100, Loles wrote:
> PostgreSQL version 9.5.5 (I know. I do not like it. In Spanish there
> is a saying that says "the things of the Palace go slowly")

Note that 9.5 does not have the freeze map yet (that came in 9.6), so
anything freeze-related is way worse there.

So maybe just get some sign-off from the higher-ups that they are aware
of the risks of running an unpatched, end-of-lifed database to cover
your own bases.


Michael

On Sat, Mar 26, 2022 at 11:29 AM Loles <lolesft@gmail.com> wrote:
> Why does vacuum seem to do nothing? Is my test wrong?

No, your test seems fine. I work on this area of the code, and I have
to concede that it's more confusing than it really needs to be.

> I need to understand.. and be able to continue with my life XD

Technically relfrozenxid can be advanced by any VACUUM operation. In
practice there are a couple of low-level issues that make it rather
unlikely that it will happen, outside of an aggressive VACUUM -- so a
person could be forgiven for thinking that it's only possible during
aggressive VACUUMs. Aggressive VACUUMs are (by definition) guaranteed
to be able to advance relfrozenxid such that the final
age(relfrozenxid) is set to a value approximately equal to your
vacuum_freeze_min_age setting. They happen because no non-aggressive
VACUUM ever advanced relfrozenxid (even though, as I said, that's very
much the common case!).

An aggressive VACUUM can be either an anti-wraparound autovacuum, or a
VACUUM (manual or autovacuum) that is aggressive by virtue of the
table's age(relfrozenxid) exceeding vacuum_freeze_table_age when the
VACUUM begins. In practice most installations usually have
relfrozenxid advanced by aggressive anti-wraparound VACUUMs.

Fortunately it's usually fine to ignore all of this -- you can just
rely on autovacuum. To answer your original question: the simplest
thing you could do to dramatically reduce the risk of wraparound
failure (which is probably very low for your application already) is
to upgrade.

In particular, the most recent stable version (Postgres 14) has a new
wraparound failsafe mechanism that makes autovacuum do everything it
can to avoid wraparound failure, should you get near the point of
running out of XID space. It also recovers from the situation without
operator intervention should the worst happen (unless maybe you have
something that totally holds back cleanup by VACUUM, like a leaked
replication slot). Finally, there is the freeze map work added to 9.6,
which also saves a lot of work during aggressive VACUUMs.

-- 
Peter Geoghegan