Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1slO4A-007Hkx-U2 for pgsql-general@arkaria.postgresql.org; Tue, 03 Sep 2024 07:35:07 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1slO49-008P20-T1 for pgsql-general@arkaria.postgresql.org; Tue, 03 Sep 2024 07:35:06 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1slO49-008P1s-Dr for pgsql-general@lists.postgresql.org; Tue, 03 Sep 2024 07:35:05 +0000 Received: from mail.postgrespro.ru ([93.174.131.139]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1slO45-000Pej-JL for pgsql-general@lists.postgresql.org; Tue, 03 Sep 2024 07:35:04 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/simple; d=postgrespro.ru; s=mx2023; t=1725348899; bh=2WWlQnOq5YN2rBuGf2/06gl1JTxbFn4ayFkF1Ymf1AQ=; h=Message-ID:Date:User-Agent:Subject:To:Cc:References:From: In-Reply-To:From; b=xv/mHt8vV6v4kqO5/kw5wyFkye3CPnpzz3gD/Al8KSckn7R1itagVJY97hVH1pNlP MEYjlA4/yxvL2uBAOtiY9JHCZPAwDhSgWV51dkBDUVlRFXGaKy6WVYBTWiAs1zLYSN FZU6xsxTK/BFZ7WHet2w5sNJF+hrBqNP9JPo9CMOBsBVBO1sMnSVSP91+s4BdZ4aXT x6ib2Eqfsh57eLz1fYV67zoII0qXaUkOP8b1S+Y23hMUxJBERhAn6XpoQ8uo1oLfzH aiO/jrP0XA98iM1yEw5yO4NF+ExywoJbq6CW/aTkj9j73wScXQ0QcoJtUL9LqQ23cQ aKWy6dzK5WI+A== Received: from [192.168.0.104] (unknown [5.35.115.211]) (using TLSv1.3 with cipher TLS_AES_128_GCM_SHA256 (128/128 bits) key-exchange X25519 server-signature RSA-PSS (2048 bits) server-digest SHA256) (Client did not present a certificate) (Authenticated sender: p.luzanov@postgrespro.ru) by mail.postgrespro.ru (Postfix/587) with ESMTPSA id 0532060646; Tue, 3 Sep 2024 10:34:59 +0300 (MSK) Content-Type: multipart/alternative; boundary="------------6SDRJfSC1HwqvpaModBF2Gcm" Message-ID: Date: Tue, 3 Sep 2024 10:34:58 +0300 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: PG17 optimizations to vacuum To: Heikki Linnakangas , Peter Geoghegan Cc: Melanie Plageman , "pgsql-generallists.postgresql.org" References: <3bda0d10-0d60-42b7-9600-abe23d54bb16@postgrespro.ru> <7bc8da54-fe4f-4ff2-aa4e-b54fc91df586@postgrespro.ru> <7760d3e3-15cc-4abf-ab33-027008c17d53@iki.fi> Content-Language: en-US, ru-RU From: Pavel Luzanov In-Reply-To: X-KSMG-AntiPhishing: NotDetected, bases: 2024/09/03 07:01:00 X-KSMG-AntiSpam-Interceptor-Info: not scanned X-KSMG-AntiSpam-Status: not scanned, disabled by settings X-KSMG-AntiVirus: Kaspersky Secure Mail Gateway, version 2.1.0.7854, bases: 2024/09/03 06:42:00 #26513250 X-KSMG-AntiVirus-Status: NotDetected, skipped X-KSMG-LinksScanning: not scanned, disabled by settings X-KSMG-Message-Action: skipped X-KSMG-Rule-ID: 1 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------6SDRJfSC1HwqvpaModBF2Gcm Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit 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 --------------6SDRJfSC1HwqvpaModBF2Gcm Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit 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, 
Thank you very much for your help and time spent!
Sorry for the noise before the release of PG17.

=====
I don't think it's necessary anymore. But just in case.

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
--------------6SDRJfSC1HwqvpaModBF2Gcm--