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.
