Re: survey: pg_stat_statements total_time and entry deallocation

[legrand legrand]

Hello,

What about adding a log message for each entry_dealloc() execution? 
it could be usefull to start thinking increasing pg_stat_statements.max.

is there any rule regarding the acceptable max value ? I'm playing in test
with a 20 000 value without any problem, could it extendend to 100 000 ?

In a system with pressure on numbers of pgss lines, and regular
entry_dealloc() executions, low frequency  entries are evicted firsts, and
this should still be the same for new small queries with usage based on
total_time. 

Maybe there is a third way, that would be to evict queries based on the
"oldest modification time" ... 
This would garantee that latest queries would be kept long enough to be
collected by aggregation tools.

Regards
PAscal



--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html

Re: survey: pg_stat_statements total_time and entry deallocation

[Kim Rose Carlsen]

Here is some data from our production. I hope it can be of any use to you.

- System 1:
hiper=> 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;
 bucket | entries | max_calls | total_time | pct_time |    rows    | pct_rows 
+-+---++--++--
  1 | 245 |    71 |    4745479 | 0.38 | 640677 | 0.04
  2 | 245 |    96 |   22151762 | 1.76 | 236827 | 0.01
  3 | 245 |   122 |    9028387 | 0.72 | 297861 | 0.02
  4 | 244 |   167 |    4711705 | 0.38 | 328928 | 0.02
  5 | 244 |   228 |    9490670 | 0.76 | 337712 | 0.02
  6 | 244 |   305 |    7296024 | 0.58 | 273740 | 0.02
  7 | 244 |   394 |   35828651 | 2.85 |    1140064 | 0.07
  8 | 244 |   540 |   34180388 | 2.72 |    1313171 | 0.08
  9 | 244 |   711 |   29748121 | 2.37 | 865894 | 0.05
 10 | 244 |   989 |   12864432 | 1.02 |    1665529 | 0.10
 11 | 244 |  1507 |    4009346 | 0.32 |    1295032 | 0.08
 12 | 244 |  2511 |   13444734 | 1.07 |    4711699 | 0.30
 13 | 244 |  4567 |  401096681 |    31.94 |    3386595 | 0.21
 14 | 244 |  8086 |    4750899 | 0.38 |    8236002 | 0.52
 15 | 244 | 13356 |   19875345 | 1.58 |    6040996 | 0.38
 16 | 244 | 22454 |   23361859 | 1.86 |   16906926 | 1.06
 17 | 244 | 59660 |   68633113 | 5.46 |   40170089 | 2.52
 18 | 244 |    141667 |   59768727 | 4.76 |   76054887 | 4.77
 19 | 244 |    431946 |  330488976 |    26.31 |  213238961 |    13.38
 20 | 244 | 170978486 |  160486607 |    12.78 | 1216933189 |    76.34
    |    4883 | 170978486 | 1255961906 |   100.00 | 1594074779 |   100.00
(21 rows)

hiper=> select current_setting('pg_stat_statements.max');
 current_setting 
-
 5000
(1 row)