Обсуждение: PG16devel - vacuum_freeze_table_age seems not being taken into account
Hi List,
I am doing some tests to understand vacuum_freeze_table_age and vacuum_freeze_min_age parameters.
Here is my configuration:
postgres=# select name, setting from pg_settings where name = 'vacuum_freeze_min_age';
name | setting
-----------------------+---------
vacuum_freeze_min_age | 50
(1 ligne)
postgres=# select name, setting from pg_settings where name = 'vacuum_freeze_table_age';
name | setting
-------------------------+---------
vacuum_freeze_table_age | 150
(1 ligne)
name | setting
-----------------------+---------
vacuum_freeze_min_age | 50
(1 ligne)
postgres=# select name, setting from pg_settings where name = 'vacuum_freeze_table_age';
name | setting
-------------------------+---------
vacuum_freeze_table_age | 150
(1 ligne)
test table has an age of 51.
hydrodb=# SELECT c.oid::regclass as table_name,
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm') and c.relname='test';
table_name | age
------------+-----
test | 51
(1 ligne)
greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
FROM pg_class c
LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
WHERE c.relkind IN ('r', 'm') and c.relname='test';
table_name | age
------------+-----
test | 51
(1 ligne)
I expected it not to be processed by vacuum freeze.
However it has been entirely frozen.
Moreover, among the 51 rows, only 1 was eligible for freeze because its XID was older than vacuum_freeze_min_age.
hydrodb=# vacuum verbose test;
INFO: vacuuming "hydrodb.public.test"
INFO: finished vacuuming "hydrodb.public.test": index scans: 0
pages: 0 removed, 447 remain, 1 scanned (0.22% of total)
tuples: 0 removed, 100595 remain, 0 are dead but not yet removable
removable cutoff: 1569, which was 0 XIDs old when operation ended
new relfrozenxid: 1569, which is 51 XIDs ahead of previous value
frozen: 1 pages from table (0.22% of total) had 51 tuples frozen
parcours d'index non nécessaire : 0 blocs de la table (0.00% au total) ont 0 versions mortes de lignes supprimées
vitesse moyenne de lecture : 0.000 Mo/s, vitesse moyenne d'écriture : 58.302 Mo/s
utilisation du cache : 8 récupérés, 0 ratés, 1 modifiés
utilisation des WAL : 3 enregistrements, 1 images complètes de blocs, 6302 octets
utilisation du système : CPU : utilisateur : 0.00 s, système : 0.00 s, temps passé : 0.00 s
VACUUM
Thanks for any explanation
Simon
On Fri, 3 Mar 2023 at 23:43, Simon Elbaz <elbazsimon9@gmail.com> wrote:
> hydrodb=# SELECT c.oid::regclass as table_name,
> greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
> FROM pg_class c
> LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
> WHERE c.relkind IN ('r', 'm') and c.relname='test';
> table_name | age
> ------------+-----
> test | 51
> (1 ligne)
>
> I expected it not to be processed by vacuum freeze.
> However it has been entirely frozen.
You may have missed the wording in the docs about the FREEZE option.
"Specifying FREEZE is equivalent to performing VACUUM with the
vacuum_freeze_min_age and vacuum_freeze_table_age parameters set to
zero." [0]
David
[0] https://www.postgresql.org/docs/current/sql-vacuum.html
I ran vacuum without the freeze option as you can see below.
Simon
On Fri, Mar 3, 2023 at 12:01 PM David Rowley <dgrowleyml@gmail.com> wrote:
On Fri, 3 Mar 2023 at 23:43, Simon Elbaz <elbazsimon9@gmail.com> wrote:
> hydrodb=# SELECT c.oid::regclass as table_name,
> greatest(age(c.relfrozenxid),age(t.relfrozenxid)) as age
> FROM pg_class c
> LEFT JOIN pg_class t ON c.reltoastrelid = t.oid
> WHERE c.relkind IN ('r', 'm') and c.relname='test';
> table_name | age
> ------------+-----
> test | 51
> (1 ligne)
>
> I expected it not to be processed by vacuum freeze.
> However it has been entirely frozen.
You may have missed the wording in the docs about the FREEZE option.
"Specifying FREEZE is equivalent to performing VACUUM with the
vacuum_freeze_min_age and vacuum_freeze_table_age parameters set to
zero." [0]
David
[0] https://www.postgresql.org/docs/current/sql-vacuum.html
On Fri, Mar 3, 2023 at 2:43 AM Simon Elbaz <elbazsimon9@gmail.com> wrote: > I expected it not to be processed by vacuum freeze. > However it has been entirely frozen. > Moreover, among the 51 rows, only 1 was eligible for freeze because its XID was older than vacuum_freeze_min_age. The effect that you noticed is a consequence of page-level freezing, which is new to Postgres 16. VACUUM will now freeze all of the tuples on a page whenever it needs to freeze any tuples at all (barring any tuples that are fundamentally ineligible due to being after the removable/freezable cutoff). This is justified by the cost profile. Once we decide to freeze at least one tuple of a page, the added cost in WAL is low enough that it really doesn't make sense to not just freeze everything. The page that gets frozen by your test case is also set all-frozen in the visibility map. Without the optimization, we'd have frozen that one tuple and then set the page all-visible. The page would likely be frozen again by the next aggressive VACUUM, which is usually much more expensive. -- Peter Geoghegan