From: | Pavel Luzanov <p(dot)luzanov(at)postgrespro(dot)ru> |
---|---|
To: | Heikki Linnakangas <hlinnaka(at)iki(dot)fi>, Peter Geoghegan <pg(at)bowt(dot)ie> |
Cc: | Melanie Plageman <melanieplageman(at)gmail(dot)com>, "pgsql-generallists(dot)postgresql(dot)org" <pgsql-general(at)lists(dot)postgresql(dot)org> |
Subject: | Re: PG17 optimizations to vacuum |
Date: | 2024-09-03 07:34:58 |
Message-ID: | be5dbfb9-4a3c-4688-be00-415bb547fcd8@postgrespro.ru |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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(at)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(at)%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(at)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 | Date | Subject | |
---|---|---|---|
Next Message | Heikki Linnakangas | 2024-09-03 10:04:43 | Re: PG17 optimizations to vacuum |
Previous Message | Laurenz Albe | 2024-09-03 05:53:29 | Re: Partitioning and unique key |