Subject: Re: PG17 optimizations to vacuum

From: Peter Geoghegan <[email-masked]>
Date: Mon, 2 Sep 2024 17:01:54 -0400

On Mon, Sep 2, 2024 at 4:58 PM Heikki Linnakangas <[email-masked]> wrote:
> Do you have any non-default settings? "select name,
> current_setting(name), source  from pg_settings where setting <>
> boot_val;" would show that.

What about page checksums?

One simple explanation is that we're writing extra FPIs to set hint
bits. But that explanation only works if you assume that page-level
checksums are in use (or that wal_log_hints is turned on).

-- 
Peter Geoghegan

---

From: Heikki Linnakangas <[email-masked]>
Date: Tue, 3 Sep 2024 00:11:48 +0300

> checksums are in use (or that wal_log_hints is turned on).

Hmm, yeah, that might be it. With chceksums enabled, I see ~120k WAL 
records, vs ~90k without checksums. But there's no difference between 
v16 and master.

Pavel, did you test v17 with checksums enabled and v16 with checksums 
disabled, by any chance?

-- 
Heikki Linnakangas
Neon (https://neon.tech)

---

From: Heikki Linnakangas <[email-masked]>
Date: Tue, 3 Sep 2024 00:23:43 +0300

Looking at the pg_waldump output from this test:

> ... > rmgr: XLOG        len (rec/tot):     49/  8209, tx:          0, lsn: 
0/FE052AA8, prev 0/FE0528A8, desc: FPI_FOR_HINT , blkref #0: rel 
1663/5/16396 blk 73 FPW
> rmgr: Heap2       len (rec/tot):    507/   507, tx:          0, lsn: 0/FE054AD8, prev 0/FE052AA8, desc: PRUNE snapshotConflictHorizon: 754, nredirected: 0, ndead: 226, nunused: 0, redirected: [], dead: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 199, 200, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226], unused: [], blkref #0: rel 1663/5/16396 blk 73
> rmgr: XLOG        len (rec/tot):     49/  8209, tx:          0, lsn: 0/FE054CD8, prev 0/FE054AD8, desc: FPI_FOR_HINT , blkref #0: rel 1663/5/16396 blk 74 FPW
> rmgr: Heap2       len (rec/tot):    507/   507, tx:          0, lsn: 0/FE056D08, prev 0/FE054CD8, desc: PRUNE snapshotConflictHorizon: 754, nredirected: 0, ndead: 226, nunused: 0, redirected: [], dead: [1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31, 32, 33, 34, 35, 36, 37, 38, 39, 40, 41, 42, 43, 44, 45, 46, 47, 48, 49, 50, 51, 52, 53, 54, 55, 56, 57, 58, 59, 60, 61, 62, 63, 64, 65, 66, 67, 68, 69, 70, 71, 72, 73, 74, 75, 76, 77, 78, 79, 80, 81, 82, 83, 84, 85, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 100, 101, 102, 103, 104, 105, 106, 107, 108, 109, 110, 111, 112, 113, 114, 115, 116, 117, 118, 119, 120, 121, 122, 123, 124, 125, 126, 127, 128, 129, 130, 131, 132, 133, 134, 135, 136, 137, 138, 139, 140, 141, 142, 143, 144, 145, 146, 147, 148, 149, 150, 151, 152, 153, 154, 155, 156, 157, 158, 159, 160, 161, 162, 163, 164, 165, 166, 167, 168, 169, 170, 171, 172, 173, 174, 175, 176, 177, 178, 179, 180, 181, 182, 183, 184, 185, 186, 187, 188, 189, 190, 191, 192, 193, 194, 195, 196, 197, 198, 199, 200, 201, 202, 203, 204, 205, 206, 207, 208, 209, 210, 211, 212, 213, 214, 215, 216, 217, 218, 219, 220, 221, 222, 223, 224, 225, 226], unused: [], blkref #0: rel 1663/5/16396 blk 74
> ...

The pattern of WAL records with checksums enabled is silly: For each 
page, we first write an FPI record, an immediately after that a PRUNE 
record that removes all the tuples on it, leaving the page empty.

This is the same with v16 and v17, but we certainly left money on the 
table by not folding that FPI into the VACUUM/PRUNE record.

-- 
Heikki Linnakangas
Neon (https://neon.tech)

---

From: Pavel Luzanov <[email-masked]>
Date: Tue, 3 Sep 2024 10:34:58 +0300

> Pavel, did you test v17 with checksums enabled and v16 with checksums 

Exactly, You are right!

My v16 cluster comes from the default Ubuntu distribution.
I forgot that checksums disabled by default.
But when I initialize the master cluster, I automatically set -k option.

More accurate results for the test:

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

checksums disabled
v16.4	WAL usage: 77437 records, 20832 full page images, 110654467 bytes
master	WAL usage: 61949 records, 20581 full page images, 92549229 bytes

checksums enabled
v16.4	WAL usage: 92848 records, 20985 full page images, 194863720 bytes
master	WAL usage: 76520 records, 20358 full page images, 181867154 bytes

This a great optimization!

Peter, Melanie, Heikki,Thankyouvery much foryour helpandtimespent!Sorryforthe noisebeforethe releaseof PG17.

=====
I don't thinkit'snecessaryanymore.Butjust incase.

Non-default settings for v16

postgres@postgres(16.4)=# \dconfig
              List of non-default configuration parameters
          Parameter          |                  Value
----------------------------+-----------------------------------------
  application_name           | psql
  client_encoding            | UTF8
  cluster_name               | 16/main
  config_file                | /etc/postgresql/16/main/postgresql.conf
  data_directory             | /var/lib/postgresql/16/main
  DateStyle                  | ISO, DMY
  default_text_search_config | pg_catalog.english
  external_pid_file          | /var/run/postgresql/16-main.pid
  hba_file                   | /etc/postgresql/16/main/pg_hba.conf
  ident_file                 | /etc/postgresql/16/main/pg_ident.conf
  lc_messages                | en_US.UTF-8
  lc_monetary                | ru_RU.UTF-8
  lc_numeric                 | ru_RU.UTF-8
  lc_time                    | ru_RU.UTF-8
  log_line_prefix            | %m [%p] %q%u@%d
  log_timezone               | Europe/Moscow
  port                       | 5433
  ssl                        | on
  ssl_cert_file              | /etc/ssl/certs/ssl-cert-snakeoil.pem
  ssl_key_file               | /etc/ssl/private/ssl-cert-snakeoil.key
  TimeZone                   | Europe/Moscow
(21 rows)

Building options and non-default settings for master:

./configure --silent --prefix=/home/pal/master --with-pgport=5401 --with-lz4 --with-icu --with-zstd --enable-nls --with-libxml --with-llvm
make world --silent -j
make --silent install-world

initdb -k -U postgres

postgres@postgres(18.0)=# \dconfig
             List of non-default configuration parameters
          Parameter          |                 Value
----------------------------+---------------------------------------
  application_name           | psql
  client_encoding            | UTF8
  config_file                | /home/pal/master/data/postgresql.conf
  data_directory             | /home/pal/master/data
  DateStyle                  | ISO, DMY
  default_text_search_config | pg_catalog.english
  hba_file                   | /home/pal/master/data/pg_hba.conf
  ident_file                 | /home/pal/master/data/pg_ident.conf
  lc_messages                | en_US.UTF-8
  lc_monetary                | ru_RU.UTF-8
  lc_numeric                 | ru_RU.UTF-8
  lc_time                    | ru_RU.UTF-8
  log_timezone               | Europe/Moscow
  TimeZone                   | Europe/Moscow
(14 rows)

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

---

From: Heikki Linnakangas <[email-masked]>
Date: Tue, 3 Sep 2024 13:04:43 +0300

> master	WAL usage: 76520 records, 20358 full page images, 181867154 bytes

That's more like it :-)

> Peter, Melanie, Heikki, 
> Thank you very much for your help and time spent!
> Sorry for the noise before the release of PG17.

Thanks for the testing!

-- 
Heikki Linnakangas
Neon (https://neon.tech)