Content-Type: multipart/alternative;
 boundary="------------y6Dwn8fywxWGhkRdb02aYl2Z"
Message-ID: <3bda0d10-0d60-42b7-9600-abe23d54bb16@postgrespro.ru>
Date: Mon, 2 Sep 2024 00:44:40 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Content-Language: en-US, ru-RU
To: "pgsql-generallists.postgresql.org" <pgsql-general@lists.postgresql.org>
From: Pavel Luzanov <p.luzanov@postgrespro.ru>
Subject: PG17 optimizations to vacuum
Archived-At: <https://www.postgresql.org/message-id/3bda0d10-0d60-42b7-9600-abe23d54bb16%40postgrespro.ru>
Precedence: bulk

This is a multi-part message in MIME format.
--------------y6Dwn8fywxWGhkRdb02aYl2Z
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

Hello,

While playing with optimizations to vacuum in v17 I can't understand
how to measure this one:
"Allow vacuum to more efficiently remove and freeze tuples".

My test script and results:

CREATE TABLE t(id integer) WITH (autovacuum_enabled = off);
INSERT INTO t SELECT gen.id FROM generate_series(1,3_500_000) gen(id);
CREATE INDEX t_id ON t(id);
SET maintenance_work_mem = '1MB';
UPDATE t SET id = id + 1;
VACUUM FREEZE VERBOSE t;


v16.4
INFO:  aggressively vacuuming "postgres.public.t"
INFO:  finished vacuuming "postgres.public.t": index scans: 21
pages: 0 removed, 30974 remain, 30974 scanned (100.00% of total)
tuples: 3500000 removed, 3500000 remain, 0 are dead but not yet removable
removable cutoff: 1675, which was 0 XIDs old when operation ended
new relfrozenxid: 1675, which is 4 XIDs ahead of previous value
frozen: 15488 pages from table (50.00% of total) had 3500000 tuples frozen
index scan needed: 15487 pages from table (50.00% of total) had 3500000 dead item identifiers removed
index "t_id": pages: 19196 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 473.207 MB/s, avg write rate: 92.511 MB/s
buffer usage: 212718 hits, 267930 misses, 52380 dirtied
WAL usage: 96585 records, 42819 full page images, 198029405 bytes
system usage: CPU: user: 3.17 s, system: 0.48 s, elapsed: 4.42 s
VACUUM

master
INFO:  aggressively vacuuming "postgres.public.t"
INFO:  finished vacuuming "postgres.public.t": index scans: 1
pages: 0 removed, 30974 remain, 30974 scanned (100.00% of total)
tuples: 3500000 removed, 3500000 remain, 0 are dead but not yet removable
removable cutoff: 950, which was 0 XIDs old when operation ended
new relfrozenxid: 950, which is 4 XIDs ahead of previous value
frozen: 15488 pages from table (50.00% of total) had 3500000 tuples frozen
index scan needed: 15487 pages from table (50.00% of total) had 3500000 dead item identifiers removed
index "t_id": pages: 19196 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 101.121 MB/s, avg write rate: 120.530 MB/s
buffer usage: 48900 hits, 47749 reads, 56914 dirtied
WAL usage: 125391 records, 46626 full page images, 330547751 bytes
system usage: CPU: user: 2.90 s, system: 0.27 s, elapsed: 3.68 s
VACUUM


I see a perfectly working TID-store optimization.
With reduced maintenance_work_mem it used only one 'vacuuming indexes'
phase instead of 21 in v16.
But I also expected to see a reduction in the number of WAL records
and the total size of the WAL. Instead, WAL numbers have significantly
degraded.

What am I doing wrong?

-- 
Pavel Luzanov
Postgres Professional:https://postgrespro.com

--------------y6Dwn8fywxWGhkRdb02aYl2Z
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<!DOCTYPE html>
<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <pre>Hello,

While playing with optimizations to vacuum in v17 I can't understand
how to measure this one:
"Allow vacuum to more efficiently remove and freeze tuples".

My test script and results:

CREATE TABLE t(id integer) WITH (autovacuum_enabled = off);
INSERT INTO t SELECT gen.id FROM generate_series(1,3_500_000) gen(id);
CREATE INDEX t_id ON t(id);
SET maintenance_work_mem = '1MB';
UPDATE t SET id = id + 1;
VACUUM FREEZE VERBOSE t;


v16.4
INFO:  aggressively vacuuming "postgres.public.t"
INFO:  finished vacuuming "postgres.public.t": index scans: 21
pages: 0 removed, 30974 remain, 30974 scanned (100.00% of total)
tuples: 3500000 removed, 3500000 remain, 0 are dead but not yet removable
removable cutoff: 1675, which was 0 XIDs old when operation ended
new relfrozenxid: 1675, which is 4 XIDs ahead of previous value
frozen: 15488 pages from table (50.00% of total) had 3500000 tuples frozen
index scan needed: 15487 pages from table (50.00% of total) had 3500000 dead item identifiers removed
index "t_id": pages: 19196 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 473.207 MB/s, avg write rate: 92.511 MB/s
buffer usage: 212718 hits, 267930 misses, 52380 dirtied
WAL usage: 96585 records, 42819 full page images, 198029405 bytes
system usage: CPU: user: 3.17 s, system: 0.48 s, elapsed: 4.42 s
VACUUM

master
INFO:  aggressively vacuuming "postgres.public.t"
INFO:  finished vacuuming "postgres.public.t": index scans: 1
pages: 0 removed, 30974 remain, 30974 scanned (100.00% of total)
tuples: 3500000 removed, 3500000 remain, 0 are dead but not yet removable
removable cutoff: 950, which was 0 XIDs old when operation ended
new relfrozenxid: 950, which is 4 XIDs ahead of previous value
frozen: 15488 pages from table (50.00% of total) had 3500000 tuples frozen
index scan needed: 15487 pages from table (50.00% of total) had 3500000 dead item identifiers removed
index "t_id": pages: 19196 in total, 0 newly deleted, 0 currently deleted, 0 reusable
avg read rate: 101.121 MB/s, avg write rate: 120.530 MB/s
buffer usage: 48900 hits, 47749 reads, 56914 dirtied
WAL usage: 125391 records, 46626 full page images, 330547751 bytes
system usage: CPU: user: 2.90 s, system: 0.27 s, elapsed: 3.68 s
VACUUM


I see a perfectly working TID-store optimization.
With reduced maintenance_work_mem it used only one 'vacuuming indexes'
phase instead of 21 in v16.
But I also expected to see a reduction in the number of WAL records
and the total size of the WAL. Instead, WAL numbers have significantly
degraded.

What am I doing wrong?
</pre>
    <pre class="moz-signature" cols="72">-- 
Pavel Luzanov
Postgres Professional: <a class="moz-txt-link-freetext" href="https://postgrespro.com">https://postgrespro.com</a></pre>
  </body>
</html>

--------------y6Dwn8fywxWGhkRdb02aYl2Z--