public inbox for [email protected]
help / color / mirror / Atom feedRe: PG17 optimizations to vacuum
5+ messages / 3 participants
[nested] [flat]
* Re: PG17 optimizations to vacuum
@ 2024-09-02 21:01 Peter Geoghegan <[email protected]>
2024-09-02 21:11 ` Re: PG17 optimizations to vacuum Heikki Linnakangas <[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:01 Re: PG17 optimizations to vacuum Peter Geoghegan <[email protected]>
@ 2024-09-02 21:11 ` Heikki Linnakangas <[email protected]>
2024-09-02 21:23 ` Re: PG17 optimizations to vacuum Heikki Linnakangas <[email protected]>
2024-09-03 07:34 ` Re: PG17 optimizations to vacuum Pavel Luzanov <[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:01 Re: PG17 optimizations to vacuum Peter Geoghegan <[email protected]>
2024-09-02 21:11 ` Re: PG17 optimizations to vacuum Heikki Linnakangas <[email protected]>
@ 2024-09-02 21:23 ` 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-02 21:01 Re: PG17 optimizations to vacuum Peter Geoghegan <[email protected]>
2024-09-02 21:11 ` Re: PG17 optimizations to vacuum Heikki Linnakangas <[email protected]>
@ 2024-09-03 07:34 ` Pavel Luzanov <[email protected]>
2024-09-03 10:04 ` Re: PG17 optimizations to vacuum 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-02 21:01 Re: PG17 optimizations to vacuum Peter Geoghegan <[email protected]>
2024-09-02 21:11 ` Re: PG17 optimizations to vacuum Heikki Linnakangas <[email protected]>
2024-09-03 07:34 ` Re: PG17 optimizations to vacuum Pavel Luzanov <[email protected]>
@ 2024-09-03 10:04 ` Heikki Linnakangas <[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