Message-ID: <7b9df88f-3015-464b-881e-4c6a7b3a5c03@tantorlabs.com>
Date: Wed, 19 Feb 2025 17:24:58 +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: Alexander Korotkov <aekorotkov@gmail.com>,
 Michael Paquier <michael@paquier.xyz>,
 Greg Sabino Mullane <htamfids@gmail.com>,
 Andrey Borodin <x4mmm@yandex-team.ru>,
 Alena Rybakina <a.rybakina@postgrespro.ru>, Sami Imseih <samimseih@gmail.com>
Cc: pgsql-hackers <pgsql-hackers@postgresql.org>
References: <fe99e0ca-e564-480e-b865-5f0cee30bc60@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>
 <4f98ec33-c627-4eb4-84f3-9994a0982277@tantorlabs.com>
 <CAA5RZ0tJjrX6YiQVDZCOA_BDMuhO-po_T8a3PmjxEEH_iL-qMg@mail.gmail.com>
 <623db69f-b40a-4d50-b1bd-bb073fcc4336@tantorlabs.com>
 <3546e2a7-5831-4c0a-996b-358d4ec483a6@tantorlabs.com>
 <6bf3059f-7ebe-4640-8870-1220f83b88ed@tantorlabs.com>
Content-Language: en-US
In-Reply-To: <6bf3059f-7ebe-4640-8870-1220f83b88ed@tantorlabs.com>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit
Archived-At: <https://www.postgresql.org/message-id/7b9df88f-3015-464b-881e-4c6a7b3a5c03%40tantorlabs.com>
Precedence: bulk


On 14.02.2025 16:17, Ilia Evdokimov wrote:
> Hi hackers,
>
> I've decided to explore a slightly different approach to reducing 
> spinlock contention—by introducing a simple execution time threshold. 
> If a query’s execution time exceeds this threshold, it is recorded in 
> pg_stat_statements; otherwise, it is ignored. As Alexander [0] pointed 
> out, this helps retain valuable queries for further analysis. A 
> similar mechanism is already present in auto_explain and 
> pg_store_plans. When pg_stat_statements.track_min_duration = -1, 
> disable tracking. If pg_stat_statements.track_min_duration = -1, all 
> statements are tracked.
>
> I benchmarked this approach using -M prepared -S on my machine with 48 
> CPUs. However, I couldn’t reproduce spinlock contention because the 
> machine isn’t large enough to create sufficient concurrency. 
> Nevertheless, I’m sharing my results for reference and checking 
> correct results of threshold.
>
> Here’s the benchmarking procedure I followed:
>     createdb pgbench
>     pgbench -i -s 3000 pgbench
>     psql -c 'SELECT pg_stat_statements_reset()'
>     pgbench -c 46 -j 46 -T 120 -M prepared -S --progress=10 pgbench
>
> select query, calls, min_exec_time, max_exec_time, mean_exec_time, 
> stddev_exec_time from pg_stat_statements where query = 'SELECT 
> abalance FROM pgbench_accounts WHERE aid = $1';
>
> track_min_duration |     calls | min_exec_time | max_exec_time |     
> mean_exec_time | stddev_exec_time
>                  0 | 111282955 |       0.00365 |      15.56946 | 
> 0.015042374707317802 | 0.06067634978916631
>                  5 |       458 |       5.00627 |      15.699129 |    
> 5.962879746724887 | 1.1432124887616204
>                 10 |        14 |      10.538461 |      16.113204 |   
> 12.415218999999999 | 1.5598854455354354
>                 20 |         - |              - |              - |     
>                - | -
>                 -1 |         - |              - |              - |     
>                - | -
>
> I’d greatly appreciate any feedback on this alternative approach, as 
> well as benchmarking on a pretty large machine to see its impact at 
> scale.
>
> [0]: 
> https://www.postgresql.org/message-id/CAPpHfdsTKAQqC3A48-MGQhrhfEamXZPb64w%3Dutk7thQcOMNr7Q%40mail.gmail.com
>
> -- 
> Best regards,
> Ilia Evdokimov,
> Tantor Labs LLC.


Hi hackers,

I rebased this patch to v18 to fix an inaccurate additional description 
of the GUC parameter. The -1 value should be described first, followed by 0.

Does anyone have other suggestions on how we could sample queries in 
pg_stat_statements to reduce spin-lock contention on entries?

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