MIME-Version: 1.0
References: <fe99e0ca-e564-480e-b865-5f0cee30bc60@tantorlabs.com>
 <f202ed0f-28c3-45aa-884c-db0a78322342@tantorlabs.com> <fb3df2e0-2cd4-49ea-bfda-148bc43d613d@tantorlabs.com>
 <52e5e3aa-066a-4daf-8d65-77c34a2f0d98@tantorlabs.com> <6482A576-4465-4E46-A42C-F1767E8DDE30@yandex-team.ru>
 <CAA5RZ0s=JH4owVr=Tfr4DYpFnBrYJjKoq2d7NqdkBQKY5whiRQ@mail.gmail.com>
 <20994d10-ab44-4c6b-91bc-c4675cd2e58e@tantorlabs.com> <9D8A5BC8-A3A5-4DA9-9382-F30D343247D1@yandex-team.ru>
 <CAA5RZ0sBsajHdE_HPoJ-C5rp8QmfArd6a6yJo6bDMVpyW25E+Q@mail.gmail.com>
 <6cf6270b-e8fa-4d99-b74a-17e781cdf309@tantorlabs.com> <b40b76c3-fc54-4f11-a50f-e63cd6434560@postgrespro.ru>
 <3a27c830-b81f-4560-9774-a87d9b922fbd@tantorlabs.com> <CAA5RZ0vYu+dm3KKd5iaQ=LY0J2sjfxJG+-VCfkeVtWFuhSb-4A@mail.gmail.com>
 <cebfee9c-a894-46f1-b6bc-5bf4a0753b41@tantorlabs.com> <7AD765EF-14DE-4B3E-8282-E82A9B44DBAA@yandex-team.ru>
 <e738eac5-ad4d-454c-a079-38766e32ce52@tantorlabs.com> <CAA5RZ0s+khQ_u84qS_cAHzxVQQHK_UmWXxRBWTTex7zW1250tQ@mail.gmail.com>
 <0c7770bd-9750-4200-a472-bdb262583c2f@tantorlabs.com>
In-Reply-To: <0c7770bd-9750-4200-a472-bdb262583c2f@tantorlabs.com>
From: Sami Imseih <samimseih@gmail.com>
Date: Tue, 28 Jan 2025 11:21:42 -0600
Message-ID: <CAA5RZ0vxn_UiUkUK5SdngObLZzw40RhafB7SHydxzBw2_xjjiA@mail.gmail.com>
Subject: Re: Sample rate added to pg_stat_statements
To: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
Cc: Andrey Borodin <x4mmm@yandex-team.ru>, Alena Rybakina <a.rybakina@postgrespro.ru>, 
	Alexander Korotkov <aekorotkov@gmail.com>, Michael Paquier <michael@paquier.xyz>, 
	Greg Sabino Mullane <htamfids@gmail.com>, pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: multipart/mixed; boundary="000000000000e66e19062cc76d83"
Archived-At: <https://www.postgresql.org/message-id/CAA5RZ0vxn_UiUkUK5SdngObLZzw40RhafB7SHydxzBw2_xjjiA%40mail.gmail.com>
Precedence: bulk

--000000000000e66e19062cc76d83
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

> All the changes mentioned above are included in the v13 patch. Since the
> patch status is 'Ready for Committer,' I believe it is now better for
> upstream inclusion, with improved details in tests and documentation. Do
> you have any further suggestions?

I am not quite clear on the sample_1.out. I do like the idea of separating
the sample tests, but I was thinking of something a bit more simple.
What do you think of my attached, sampling.sql, test? It tests sample
rate in both
simple and extended query protocols and for both top level and
nested levels?

> If anyone has the capability to run this benchmark on machines with more
> CPUs or with different queries, it would be nice. I=E2=80=99d appreciate =
any
> suggestions or feedback.

I wanted to share some additional benchmarks I ran as well
on a r8g.48xlarge ( 192 vCPUs, 1,536 GiB of memory) configured
with 16GB of shared_buffers. I also attached the benchmark.sh
script used to generate the output.
The benchmark is running the select-only pgbench workload,
so we have a single heavily contentious entry, which is the
worst case.

The test shows that the spinlock (SpinDelay waits)
becomes an issue at high connection counts and will
become worse on larger machines. A sample_rate going from
1 to .75 shows a 60% improvement; but this is on a single
contentious entry. Most workloads will likely not see this type
of improvement. I also could not really observe
this type of difference on smaller machines ( i.e. 32 vCPUs),
as expected.

## init
pgbench -i -s500

### 192 connections
pgbench -c192 -j20 -S -Mprepared -T120 --progress 10

sample_rate =3D 1
tps =3D 484338.769799 (without initial connection time)
waits
-----
  11107  SpinDelay
   9568  CPU
    929  ClientRead
     13  DataFileRead
      3  BufferMapping

sample_rate =3D .75
tps =3D 909547.562124 (without initial connection time)
waits
-----
  12079  CPU
   4781  SpinDelay
   2100  ClientRead

sample_rate =3D .5
tps =3D 1028594.555273 (without initial connection time)
waits
-----
  13253  CPU
   3378  ClientRead
    174  SpinDelay

sample_rate =3D .25
tps =3D 1019507.126313 (without initial connection time)
waits
-----
  13397  CPU
   3423  ClientRead

sample_rate =3D 0
tps =3D 1015425.288538 (without initial connection time)
waits
-----
  13106  CPU
   3502  ClientRead

### 32 connections
pgbench -c32 -j20 -S -Mprepared -T120 --progress 10

sample_rate =3D 1
tps =3D 620667.049565 (without initial connection time)
waits
-----
   1782  CPU
    560  ClientRead

sample_rate =3D .75
tps =3D 620663.131347 (without initial connection time)
waits
-----
   1736  CPU
    554  ClientRead

sample_rate =3D .5
tps =3D 624094.688239 (without initial connection time)
waits
-----
   1741  CPU
    648  ClientRead

sample_rate =3D .25
tps =3D 628638.538204 (without initial connection time)
waits
-----
   1702  CPU
    576  ClientRead

sample_rate =3D 0
tps =3D 630483.464912 (without initial connection time)
waits
-----
   1638  CPU
    574  ClientRead

Regards,

Sami

--000000000000e66e19062cc76d83
Content-Type: application/octet-stream; name="sampling.sql"
Content-Disposition: attachment; filename="sampling.sql"
Content-Transfer-Encoding: base64
Content-ID: <f_m6gq8ryv0>
X-Attachment-Id: f_m6gq8ryv0

LS0KLS0gc2FtcGxlIHN0YXRlbWVudHMKLS0KCi0tIHRvcC1sZXZlbCB0cmFja2luZyAtIHNpbXBs
ZSBxdWVyeSBwcm90b2NvbApTSE9XIHBnX3N0YXRfc3RhdGVtZW50cy50cmFjazsKU0VUIHBnX3N0
YXRfc3RhdGVtZW50cy5zYW1wbGVfcmF0ZSA9IDAuMDsKU0VMRUNUIHBnX3N0YXRfc3RhdGVtZW50
c19yZXNldCgpIElTIE5PVCBOVUxMIEFTIHQ7ClNFTEVDVCAxIEFTICJpbnQiOwpTRUxFQ1QgcXVl
cnksIGNhbGxzIEZST00gcGdfc3RhdF9zdGF0ZW1lbnRzIE9SREVSIEJZIHF1ZXJ5IENPTExBVEUg
IkMiOwpTRVQgcGdfc3RhdF9zdGF0ZW1lbnRzLnNhbXBsZV9yYXRlID0gMS4wOwpTRUxFQ1QgMSBB
UyAiaW50IjsKU0VMRUNUIHF1ZXJ5LCBjYWxscyBGUk9NIHBnX3N0YXRfc3RhdGVtZW50cyBPUkRF
UiBCWSBxdWVyeSBDT0xMQVRFICJDIjsKCi0tIHRvcC1sZXZlbCB0cmFja2luZyAtIGV4dGVuZGVk
IHF1ZXJ5IHByb3RvY29sClNFVCBwZ19zdGF0X3N0YXRlbWVudHMuc2FtcGxlX3JhdGUgPSAwLjA7
ClNFTEVDVCBwZ19zdGF0X3N0YXRlbWVudHNfcmVzZXQoKSBJUyBOT1QgTlVMTCBBUyB0OwpTRUxF
Q1QgMSBccGFyc2Ugc3RtdApcYmluZF9uYW1lZCBzdG10IFxnClNFTEVDVCBxdWVyeSwgY2FsbHMg
RlJPTSBwZ19zdGF0X3N0YXRlbWVudHMgT1JERVIgQlkgcXVlcnkgQ09MTEFURSAiQyI7ClNFVCBw
Z19zdGF0X3N0YXRlbWVudHMuc2FtcGxlX3JhdGUgPSAxLjA7ClxiaW5kX25hbWVkIHN0bXQgXGcK
U0VMRUNUIHF1ZXJ5LCBjYWxscyBGUk9NIHBnX3N0YXRfc3RhdGVtZW50cyBPUkRFUiBCWSBxdWVy
eSBDT0xMQVRFICJDIjsKU0VMRUNUIHBnX3N0YXRfc3RhdGVtZW50c19yZXNldCgpIElTIE5PVCBO
VUxMIEFTIHQ7CkRFQUxMT0NBVEUgc3RtdDsKCi0tIG5lc3RlZCB0cmFja2luZyAtIHNpbXBsZSBx
dWVyeSBwcm90b2NvbApTRVQgcGdfc3RhdF9zdGF0ZW1lbnRzLnRyYWNrID0gImFsbCI7ClNFTEVD
VCBwZ19zdGF0X3N0YXRlbWVudHNfcmVzZXQoKSBJUyBOT1QgTlVMTCBBUyB0OwpTRVQgcGdfc3Rh
dF9zdGF0ZW1lbnRzLnNhbXBsZV9yYXRlID0gMTsKRVhQTEFJTiAoQ09TVFMgT0ZGKSBTRUxFQ1Qg
MTsKRVhQTEFJTiAoQ09TVFMgT0ZGKSBTRUxFQ1QgMTsKU0VUIHBnX3N0YXRfc3RhdGVtZW50cy5z
YW1wbGVfcmF0ZSA9IDA7CkVYUExBSU4gKENPU1RTIE9GRikgU0VMRUNUIDE7CkVYUExBSU4gKENP
U1RTIE9GRikgU0VMRUNUIDE7ClNFTEVDVCB0b3BsZXZlbCwgY2FsbHMsIHF1ZXJ5IEZST00gcGdf
c3RhdF9zdGF0ZW1lbnRzCiAgT1JERVIgQlkgcXVlcnkgQ09MTEFURSAiQyI7CgotLSBuZXN0ZWQg
dHJhY2tpbmcgLSBleHRlbmRlZCBxdWVyeSBwcm90b2NvbApTRVQgcGdfc3RhdF9zdGF0ZW1lbnRz
LnRyYWNrID0gImFsbCI7ClNFTEVDVCBwZ19zdGF0X3N0YXRlbWVudHNfcmVzZXQoKSBJUyBOT1Qg
TlVMTCBBUyB0OwpTRVQgcGdfc3RhdF9zdGF0ZW1lbnRzLnNhbXBsZV9yYXRlID0gMTsKRVhQTEFJ
TiAoQ09TVFMgT0ZGKSBTRUxFQ1QgMTsgXHBhcnNlIHN0bXQKXGJpbmRfbmFtZWQgc3RtdCBcZwpc
YmluZF9uYW1lZCBzdG10IFxnClNFVCBwZ19zdGF0X3N0YXRlbWVudHMuc2FtcGxlX3JhdGUgPSAw
OwpcYmluZF9uYW1lZCBzdG10IFxnClxiaW5kX25hbWVkIHN0bXQgXGcKU0VMRUNUIHRvcGxldmVs
LCBjYWxscywgcXVlcnkgRlJPTSBwZ19zdGF0X3N0YXRlbWVudHMKICBPUkRFUiBCWSBxdWVyeSBD
T0xMQVRFICJDIjs=
--000000000000e66e19062cc76d83
Content-Type: application/x-sh; name="benchmark.sh"
Content-Disposition: attachment; filename="benchmark.sh"
Content-Transfer-Encoding: base64
Content-ID: <f_m6gqfaap1>
X-Attachment-Id: f_m6gqfaap1

IyMjIHdhaXRzLnNxbApcbyAvdG1wL3dhaXRzCnNlbGVjdCAnT1VUJywgQ09BTEVTQ0Uod2FpdF9l
dmVudCwgJ0NQVScpIHdhaXQgZnJvbSAgcGdfc3RhdF9hY3Rpdml0eSB3aGVyZSBzdGF0ZSA9ICdh
Y3RpdmUnOwpcd2F0Y2ggMQoKCgojIyMgcGdiZW5jaApDT05ORUNUSU9OUz0xOTIKcGdiZW5jaCAt
aSAtczUwMAoKcm0gLXJmdiAvdG1wL3Jlc3VsdHMKZm9yIGkgaW4gMSAuNzUgLjUgLjI1IDAKZG8K
ICBwc3FsIC1jICJhbHRlciBzeXN0ZW0gc2V0IHBnX3N0YXRfc3RhdGVtZW50cy5zYW1wbGVfcmF0
ZSA9ICR7aX07IiAyPi9kZXYvbnVsbCA+L2Rldi9udWxsCiAgcHNxbCAtYyAic2VsZWN0IHBnX3Jl
bG9hZF9jb25mKCk7IiAyPi9kZXYvbnVsbCA+L2Rldi9udWxsCiAgcHNxbCAtYyAic2hvdyBwZ19z
dGF0X3N0YXRlbWVudHMuc2FtcGxlX3JhdGU7IiAyPi9kZXYvbnVsbCA+L2Rldi9udWxsCiAgZWNo
byAtZSAiXG5zYW1wbGVfcmF0ZSA9ICRpIiA+Pi90bXAvcmVzdWx0cwogIG5vaHVwIHBnYmVuY2gg
LWMke0NPTk5FQ1RJT05TfSAtajIwIC1TIC1NcHJlcGFyZWQgLVQxMjAgLS1wcm9ncmVzcyAxMCB8
IGdyZXAgInRwcyA9ICIgMj4+L3RtcC9yZXN1bHRzID4+L3RtcC9yZXN1bHRzICYKICB0aW1lb3V0
IDEyNSBwc3FsIC1mIC90bXAvd2FpdHMuc3FsCiAgZWNobyAid2FpdHMiID4+L3RtcC9yZXN1bHRz
CiAgZWNobyAiLS0tLS0iID4+L3RtcC9yZXN1bHRzCiAgY2F0IC90bXAvd2FpdHMgfCBncmVwIE9V
VCB8IGF3ayAne3ByaW50ICQyfScgRlM9InwiIHwgc29ydCB8IHVuaXEgLWMgfCBzb3J0IC1uIC1y
IC1rMSA+Pi90bXAvcmVzdWx0cwogIHJtIC1yZnYgL3RtcC93YWl0cwogIHJtIC1yZnYgL3RtcC9v
dXQKZG9uZQ==
--000000000000e66e19062cc76d83--