Re: Sample rate added to pg_stat_statements - Mailing list pgsql-hackers

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.