Message-ID: <4f98ec33-c627-4eb4-84f3-9994a0982277@tantorlabs.com>
Date: Thu, 30 Jan 2025 13:58:47 +0300
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: Sample rate added to pg_stat_statements
From: Ilia Evdokimov <ilya.evdokimov@tantorlabs.com>
To: Sami Imseih <samimseih@gmail.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>
References: <fe99e0ca-e564-480e-b865-5f0cee30bc60@tantorlabs.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>
 <CAA5RZ0vxn_UiUkUK5SdngObLZzw40RhafB7SHydxzBw2_xjjiA@mail.gmail.com>
 <cc1beb52-3e4e-4866-8c32-a5967b98c977@tantorlabs.com>
 <18631d46-1741-4edc-b116-8d9631cdf919@tantorlabs.com>
Content-Language: en-US
In-Reply-To: <18631d46-1741-4edc-b116-8d9631cdf919@tantorlabs.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/4f98ec33-c627-4eb4-84f3-9994a0982277%40tantorlabs.com>
Precedence: bulk


On 29.01.2025 21:52, Ilia Evdokimov wrote:
>
>>> ... I also attached the benchmark.sh
>>> script used to generate the output.
>
>
> In my opinion, if we can't observe bottleneck of spinlock on 32 CPUs, 
> we should determine the CPU count at which it becomes. This will help 
> us understand the scale of the problem. Does this make sense, or are 
> there really no real workloads where the same query runs on more than 
> 32 CPUs, and we've been trying to solve a non-existent problem?


I ran the same benchmark on 48 CPUs for -c 48 -j 20 for objectivity.

### 48 connections
pgbench -c48 -j20 -S -Mprepared -T120 --progress 10

sample_rate = 1
tps = 643251.640175 (without initial connection time)
waits
-----
     932  ClientRead
     911  CPU
      44  SpinDelay

sample_rate = .75
tps = 653946.777122 (without initial connection time)
waits
-----
     939  CPU
     875  ClientRead
       3  SpinDelay

sample_rate = .5
tps = 651654.348463 (without initial connection time)
waits
-----
     932  ClientRead
     841  CPU

sample_rate = .25
tps = 652668.807245 (without initial connection time)
waits
-----
     910  ClientRead
     860  CPU

sample_rate = 0
tps = 659111.347019 (without initial connection time)
waits
-----
     882  ClientRead
     849  CPU


There is a small amount ofSpinDelay, as the user mentioned. However, we 
can identify the threshold where the problem appears.

To summarize the results of all benchmarks, I compiled them into a table:

  CPUs | sample_rate | tps | CPU waits | ClientRead wait | SpinDelay wait
   192 |         1.0 | 484338|      9568 |             929 | 11107
   192 |        0.75 | 909547|      12079 |            2100 | 4781
   192 |         0.5 |1028594|     13253 |            3378 | 174
   192 |        0.25 |1019507|     13397 |            3423 | -
   192 |         0.0 |1015425|     13106 |            3502 | -

    48 |         1.0 | 643251|       911 |             932 | 44
    48 |        0.75 | 653946|       939 |             939 |  3
    48 |         0.5 | 651654|       841 |             932 | -
    48 |        0.25 | 652668|       860 |             910 | -
    48 |         0.0 | 659111|       849 |             882 | -

    32 |         1.0 | 620667|      1782 |             560 | -
    32 |        0.75 | 620667|      1736 |             554 | -
    32 |         0.5 | 624094|      1741 |             648 | -
    32 |        0.25 | 628638|      1702 |             576 | -
    32 |         0.0 | 630483|      1638 |             574 | -

--
Best regards,
Ilia Evdokimov,
Tantor Labs LLC.