|
TLDR: can you run this query on your prod DB and post the results? ===== The default value of pg_stat_statements.max is 5000. Nonetheless, I was recently trying to track down a SQL that did some temp I/O on a system and discovered that I could only find it in pg_stat_statements if I used \watch 1 and left a terminal open... that is, pg_stat_statements was deleting SQL statements from memory every second. The code to manage deallocation of entries is well over 10 years old. When it needs space for a new SQL and all <max> entries are already allocated, it simply removes the bottom 5% by execution count. There's a comment that's been in there since the beginning, asking a question: should deallocation reflect execution time and/or buffer usage instead of execution count? I'm most interested in total_time (I think if buffer access matters then total_time will reflect that). This made me curious about the distribution of how total execution time is represented in pg_stat_statements on a busy system. I got ahold of some friends running busy, large postgres databases and had them run this simple query to show how total_time is distributed across pg_stat_statements. Here's the query and results: select
bucket
,count(*) entries
,max(calls) max_calls
,round(sum(total_time)) total_time
,round((100*sum(total_time)/avg(total_total_time))::numeric,2) pct_time
,round(sum(rows)) "rows"
,round((100*sum(rows)/avg(total_rows))::numeric,2) pct_rows
from
(
select
ntile(20) over (order by calls) bucket
,calls
,total_time
,sum(total_time) over () total_total_time
,rows
,sum(rows) over () total_rows
from pg_stat_statements
) stmts
group by rollup(bucket) order by bucket;
----- Verification:
select current_setting('pg_stat_statements.max');
current_setting
-----------------
5000
----- System 1:
bucket | entries | max_calls | total_time | pct_time | rows | pct_rows
--------+---------+-------------+------------+----------+-------------+----------
1 | 247 | 2 | 169 | 0.00 | 375 | 0.00
2 | 247 | 2 | 90 | 0.00 | 49 | 0.00
3 | 247 | 2 | 305 | 0.00 | 99 | 0.00
4 | 247 | 3 | 353 | 0.00 | 550 | 0.00
5 | 247 | 8 | 148 | 0.00 | 1609 | 0.00
6 | 247 | 44 | 1082 | 0.00 | 8912 | 0.00
7 | 247 | 73 | 2515 | 0.00 | 8734 | 0.00
8 | 247 | 117 | 5922 | 0.00 | 15690 | 0.00
9 | 247 | 292 | 43513 | 0.00 | 92579 | 0.00
10 | 247 | 851 | 20992 | 0.00 | 183895 | 0.00
11 | 247 | 4260 | 109540 | 0.00 | 811519 | 0.00
12 | 247 | 4682 | 278213 | 0.00 | 1113520 | 0.00
13 | 247 | 5009 | 295912 | 0.00 | 1197532 | 0.00
14 | 247 | 5379 | 312627 | 0.00 | 1280930 | 0.00
15 | 247 | 12298 | 541302 | 0.01 | 1954252 | 0.01
16 | 247 | 30573 | 1252066 | 0.01 | 6388232 | 0.02
17 | 247 | 62390 | 2139082 | 0.03 | 10699710 | 0.04
18 | 247 | 362514 | 7901250 | 0.09 | 181476105 | 0.64
19 | 246 | 3311178 | 4823142913 | 56.53 | 416459495 | 1.47
20 | 246 | 11373074151 | 3696341402 | 43.32 | 27693002500 | 97.80
[NULL] | 4938 | 11373074151 | 8532389397 | 100.00 | 28314696287 | 100.00
----- System 2:
bucket | entries | max_calls | total_time | pct_time | rows | pct_rows
--------+---------+------------+------------+----------+-------------+----------
1 | 244 | 2 | 242 | 0.00 | 570 | 0.00
2 | 244 | 2 | 13 | 0.00 | 36 | 0.00
3 | 244 | 2 | 14 | 0.00 | 34 | 0.00
4 | 244 | 2 | 16 | 0.00 | 34 | 0.00
5 | 244 | 2 | 14 | 0.00 | 38 | 0.00
6 | 244 | 2 | 14 | 0.00 | 68 | 0.00
7 | 244 | 2 | 14 | 0.00 | 30 | 0.00
8 | 244 | 4 | 19 | 0.00 | 532 | 0.00
9 | 244 | 34 | 184 | 0.00 | 2547 | 0.00
10 | 244 | 202 | 10577 | 0.00 | 29682 | 0.00
11 | 244 | 644 | 23279 | 0.00 | 125213 | 0.00
12 | 244 | 1544 | 46987 | 0.00 | 348863 | 0.00
13 | 244 | 1943 | 90455 | 0.00 | 424748 | 0.00
14 | 244 | 2172 | 105795 | 0.00 | 502619 | 0.00
15 | 244 | 2349 | 115168 | 0.00 | 548606 | 0.00
16 | 244 | 2532 | 124633 | 0.00 | 593084 | 0.00
17 | 244 | 2834 | 134928 | 0.00 | 646622 | 0.00
18 | 244 | 38747 | 184982 | 0.00 | 1275064 | 0.01
19 | 244 | 2334761 | 3496474265 | 58.13 | 551602241 | 2.63
20 | 243 | 8743645771 | 2517902540 | 41.86 | 20380562694 | 97.34
[NULL] | 4879 | 8743645771 | 6015214139 | 100.00 | 20936663325 | 100.00
Would anyone else be willing to run this query against their prod box and post the results? Especially if it is using all 5000 entries in pg_stat_statements? I for one would like to know just how confident I should be in whether pg_stat_statements is representative of all the activity on a large system! -Jeremy -- Jeremy Schneider Database Engineer Amazon Web Services |
