Re: Avoid Wraparound Failures

From: Loles <lolesft(at)gmail(dot)com>
To: Michael Banck <mbanck(at)gmx(dot)net>
Cc: Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at>, Ron <ronljohnsonjr(at)gmail(dot)com>, pgsql-admin(at)lists(dot)postgresql(dot)org
Subject: Re: Avoid Wraparound Failures
Date: 2022-03-26 18:28:33
Message-ID: CA+FWGK63FtDD0-kFkfq0kLzNE2V01xi3zLv4+MJDcCOYTrdjiQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

Thank you very much friends!!

I have read all of you carefully, and I have read the simulation of the
failure and subsequent recovery. Very interesting.

With all the information, it seems clear that autovacuum must be well
adjusted for this process to be able to maintenance the tables in good
condition and include the execution of vacuum as maintenance routines.

I have done a test and... to my amazement, vacuum does not reduce the age
of the relfrozenxid.

The test is very simple.

Create new database and connect to it:

postgres=# create database test_xid;
CREATE DATABASE
postgres=#
postgres=# \c test_xid
You are now connected to database "test_xid" as user "todopostgresql".

Check the current transaction:

test_xid=# select txid_current();
txid_current
--------------
983
(1 row)

Create a table and insert 10 million tuples:

test_xid=# create table t1 (id serial);
CREATE TABLE
test_xid=# insert into t1 select generate_series(1,10000000);
INSERT 0 10000000

The frozen transaction on table t1 is 984 and has a distance of 3 from
txid, which is currently 987. So far so good.

test_xid=# select relfrozenxid, age(relfrozenxid) from pg_class where
relname = 't1';
relfrozenxid | age
--------------+-----
984 | 3

test_xid=# select txid_current();
txid_current
--------------
987

"Dirty" table t1:

test_xid=# update t1 set id = id + 1;
UPDATE 10000000
test_xid=#
test_xid=# select relfrozenxid, age(relfrozenxid) from pg_class where
relname = 't1';
relfrozenxid | age
--------------+-----
984 | 5
(1 row)

test_xid=# select txid_current();
txid_current
--------------
989
(1 row)

If I do a vacuum now, the 10 million "dirty" tuples will be marked as
reusable.

test_xid=# vacuum t1;
VACUUM
test_xid=# select relfrozenxid, age(relfrozenxid) from pg_class where
relname = 't1';
relfrozenxid | age
--------------+-----
984 | 7
(1 row)

test_xid=# select txid_current();
txid_current
--------------
991
(1 row)

If I now insert another 10 million tuples they should occupy the space of
already past ¿frozen by vacuum? transactions:

test_xid=# insert into t1 select generate_series(1,10000000);
INSERT 0 10000000
test_xid=# select relfrozenxid, age(relfrozenxid) from pg_class where
relname = 't1';
relfrozenxid | age
--------------+-----
984 | 9
(1 row)

test_xid=# select txid_current();
txid_current
--------------
993
(1 row)

But no. I don't see the improvement. Try again with vacuum analyze:

test_xid=# update t1 set id = id + 1;
UPDATE 20000000
test_xid=#
test_xid=# select relfrozenxid, age(relfrozenxid) from pg_class where
relname = 't1';
relfrozenxid | age
--------------+-----
984 | 12
(1 row)

test_xid=# select txid_current();
txid_current
--------------
996
(1 row)

test_xid=# vacuum ANALYZE t1;
VACUUM
test_xid=# select relfrozenxid, age(relfrozenxid) from pg_class where
relname = 't1';
relfrozenxid | age
--------------+-----
984 | 14
(1 row)

test_xid=# insert into t1 select generate_series(1,10000000);
INSERT 0 10000000
test_xid=# select relfrozenxid, age(relfrozenxid) from pg_class where
relname = 't1';
relfrozenxid | age
--------------+-----
984 | 15
(1 row)

test_xid=# select txid_current();
txid_current
--------------
999
(1 row)

Either. Do a vacuum of the entire database:

test_xid=# vacuum ;
VACUUM
test_xid=# select relfrozenxid, age(relfrozenxid) from pg_class where
relname = 't1';
relfrozenxid | age
--------------+-----
984 | 17

Neither. Try vacuum freeze:

test_xid=# vacuum FREEZE t1;
VACUUM
test_xid=# select relfrozenxid, age(relfrozenxid) from pg_class where
relname = 't1';
relfrozenxid | age
--------------+-----
1001 | 0
(1 row)

test_xid=# select txid_current();
txid_current
--------------
1001
(1 row)

Yes now. Sure, because age is the difference between txid and relfrozenxid
and "vacuum freexe" freezes all transactions before txid.

But we have agreed that vacuum freeze has disadvantages and that vacuum
should be enough.

Why does vacuum seem to do nothing? Is my test wrong?

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

El sáb, 26 mar 2022 a las 9:32, Michael Banck (<mbanck(at)gmx(dot)net>) escribió:

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

In response to

Responses

Browse pgsql-admin by date

  From Date Subject
Next Message Peter Geoghegan 2022-03-26 18:53:48 Re: Avoid Wraparound Failures
Previous Message Michael Banck 2022-03-26 08:32:41 Re: Avoid Wraparound Failures