Re: Sample rate added to pg_stat_statements

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.

I have rebased the v16 patch to v17. I updated the extra description in
accordance with commit 977d865 [0] and removed the additional test with
pg_sleep, as it may produce different results on different platforms.

[0]:
https://git.postgresql.org/gitweb/?p=postgresql.git;a=commitdiff;h=977d865c36cc6ed4e813b8545b09d06f51534011

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