public inbox for [email protected]  

help / color / mirror / Atom feed

From: Ilia Evdokimov <[email protected]>
To: Alexander Korotkov <[email protected]>
To: Michael Paquier <[email protected]>
To: Greg Sabino Mullane <[email protected]>
To: Andrey Borodin <[email protected]>
To: Alena Rybakina <[email protected]>
To: Sami Imseih <[email protected]>
Cc: pgsql-hackers <[email protected]>
Subject: Re: Sample rate added to pg_stat_statements
Date: Wed, 19 Feb 2025 17:24:58 +0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <[email protected]>
	<[email protected]>
	<[email protected]>
	<CAA5RZ0vYu+dm3KKd5iaQ=LY0J2sjfxJG+-VCfkeVtWFuhSb-4A@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAA5RZ0s+khQ_u84qS_cAHzxVQQHK_UmWXxRBWTTex7zW1250tQ@mail.gmail.com>
	<[email protected]>
	<CAA5RZ0vxn_UiUkUK5SdngObLZzw40RhafB7SHydxzBw2_xjjiA@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>
	<CAA5RZ0tJjrX6YiQVDZCOA_BDMuhO-po_T8a3PmjxEEH_iL-qMg@mail.gmail.com>
	<[email protected]>
	<[email protected]>
	<[email protected]>


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.g...
>
> -- 
> 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.

view thread (58+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected], [email protected], [email protected], [email protected], [email protected]
  Subject: Re: Sample rate added to pg_stat_statements
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox