public inbox for [email protected]
help / color / mirror / Atom feedFrom: Pavel Luzanov <[email protected]>
To: pgsql-generallists.postgresql.org <[email protected]>
Subject: PG17 optimizations to vacuum
Date: Mon, 2 Sep 2024 00:44:40 +0300
Message-ID: <[email protected]> (raw)
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
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected]
Subject: Re: PG17 optimizations to vacuum
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox