public inbox for [email protected]help / color / mirror / Atom feed
Re: PG17 optimizations to vacuum 5+ messages / 3 participants [nested] [flat]
* Re: PG17 optimizations to vacuum @ 2024-09-02 21:01 Peter Geoghegan <[email protected]> 0 siblings, 1 reply; 5+ messages in thread From: Peter Geoghegan @ 2024-09-02 21:01 UTC (permalink / raw) To: Heikki Linnakangas <[email protected]>; +Cc: Pavel Luzanov <[email protected]>; Melanie Plageman <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On Mon, Sep 2, 2024 at 4:58 PM Heikki Linnakangas <[email protected]> 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG17 optimizations to vacuum @ 2024-09-02 21:11 Heikki Linnakangas <[email protected]> parent: Peter Geoghegan <[email protected]> 0 siblings, 2 replies; 5+ messages in thread From: Heikki Linnakangas @ 2024-09-02 21:11 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; Pavel Luzanov <[email protected]>; +Cc: Melanie Plageman <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On 03/09/2024 00:01, Peter Geoghegan wrote: > On Mon, Sep 2, 2024 at 4:58 PM Heikki Linnakangas <[email protected]> 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). 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) ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG17 optimizations to vacuum @ 2024-09-02 21:23 Heikki Linnakangas <[email protected]> parent: Heikki Linnakangas <[email protected]> 1 sibling, 0 replies; 5+ messages in thread From: Heikki Linnakangas @ 2024-09-02 21:23 UTC (permalink / raw) To: Peter Geoghegan <[email protected]>; Pavel Luzanov <[email protected]>; +Cc: Melanie Plageman <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On 03/09/2024 00:11, Heikki Linnakangas wrote: > On 03/09/2024 00:01, Peter Geoghegan wrote: >> On Mon, Sep 2, 2024 at 4:58 PM Heikki Linnakangas <[email protected]> >> 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). > > 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. 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) ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG17 optimizations to vacuum @ 2024-09-03 07:34 Pavel Luzanov <[email protected]> parent: Heikki Linnakangas <[email protected]> 1 sibling, 1 reply; 5+ messages in thread From: Pavel Luzanov @ 2024-09-03 07:34 UTC (permalink / raw) To: Heikki Linnakangas <[email protected]>; Peter Geoghegan <[email protected]>; +Cc: Melanie Plageman <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On 03.09.2024 00:11, Heikki Linnakangas wrote: > Pavel, did you test v17 with checksums enabled and v16 with checksums > disabled, by any chance? 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 ^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG17 optimizations to vacuum @ 2024-09-03 10:04 Heikki Linnakangas <[email protected]> parent: Pavel Luzanov <[email protected]> 0 siblings, 0 replies; 5+ messages in thread From: Heikki Linnakangas @ 2024-09-03 10:04 UTC (permalink / raw) To: Pavel Luzanov <[email protected]>; Peter Geoghegan <[email protected]>; +Cc: Melanie Plageman <[email protected]>; pgsql-generallists.postgresql.org <[email protected]> On 03/09/2024 10:34, Pavel Luzanov wrote: > On 03.09.2024 00:11, Heikki Linnakangas wrote: >> Pavel, did you test v17 with checksums enabled and v16 with checksums >> disabled, by any chance? > > 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 That's more like it :-) > This a great optimization! > > 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) ^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2024-09-03 10:04 UTC | newest] Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2024-09-02 21:01 Re: PG17 optimizations to vacuum Peter Geoghegan <[email protected]> 2024-09-02 21:11 ` Heikki Linnakangas <[email protected]> 2024-09-02 21:23 ` Heikki Linnakangas <[email protected]> 2024-09-03 07:34 ` Pavel Luzanov <[email protected]> 2024-09-03 10:04 ` Heikki Linnakangas <[email protected]>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox