Search Postgresql Archives

Re: survey: pg_stat_statements total_time and entry deallocation

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



In addition to Kim's results and my initial data, a handful of people have forwarded results to me over the past few weeks.  Here's what came my way, aggregated and anonymized.

System #7 is the particularly interesting one. Note that if this user had *not* increased their max to 10k, the SQL statements responsible for about 20% of their databases time would have been rolled off and lost from pg_stat_statements.  On most systems the problem is actually a lot smaller than I expected, but there is still a case here where people might lose data from pg_stat_statements which they wouldn't want to lose.  Might be worth a discussion about switching to sorting based on total_time instead of calls?

-Jeremy


---------- pg_stat_statements.max = 5000
SYSTEM 1:

 bucket | entries |  max_calls  | total_time  | pct_time |     rows     | pct_rows
--------+---------+-------------+-------------+----------+--------------+----------
      1 |     243 |           4 |        2172 |     0.00 |        12395 |     0.00
      2 |     243 |           8 |        2466 |     0.00 |        36148 |     0.00
      3 |     243 |          15 |        6368 |     0.00 |        34711 |     0.00
      4 |     243 |          28 |       17801 |     0.00 |        59143 |     0.00
      5 |     243 |          52 |       23631 |     0.00 |       197337 |     0.00
      6 |     243 |         126 |      119005 |     0.00 |       146798 |     0.00
      7 |     243 |         275 |      184864 |     0.00 |       402160 |     0.00
      8 |     243 |         648 |      672907 |     0.00 |       948613 |     0.00
      9 |     243 |        1616 |     1175448 |     0.01 |      3011254 |     0.00
     10 |     242 |        3775 |      803799 |     0.00 |      6308289 |     0.00
     11 |     242 |        8572 |     3315890 |     0.02 |     13489707 |     0.00
     12 |     242 |       19436 |     7747413 |     0.04 |     22958217 |     0.01
     13 |     242 |       55933 |     8572104 |     0.05 |     45162385 |     0.01
     14 |     242 |      168402 |    22795122 |     0.13 |    135961496 |     0.04
     15 |     242 |      413909 |    62500891 |     0.35 |    352699202 |     0.11
     16 |     242 |      744639 |    94332579 |     0.53 |    386606008 |     0.12
     17 |     242 |     1374743 |   257049472 |     1.45 |   2085014682 |     0.64
     18 |     242 |     3891776 |   840326001 |     4.75 |   2183808874 |     0.67
     19 |     242 |    22378695 |  1544711644 |     8.73 |  10972242140 |     3.39
     20 |     242 | 15589184235 | 14858568512 |    83.93 | 307346618022 |    94.99
        |    4849 | 15589184235 | 17702928088 |   100.00 | 323555717581 |   100.00

SYSTEM 2:

 bucket | entries |  max_calls  | total_time | pct_time |     rows      | pct_rows
--------+---------+-------------+------------+----------+---------------+----------
      1 |     244 |          17 |      16191 |     0.00 |         48204 |     0.00
      2 |     244 |          64 |     271114 |     0.00 |        363019 |     0.00
      3 |     244 |         100 |     366725 |     0.00 |        671762 |     0.00
      4 |     244 |         140 |     180140 |     0.00 |       1164382 |     0.00
      5 |     244 |         186 |     608551 |     0.01 |       1768865 |     0.00
      6 |     244 |         243 |     541205 |     0.01 |       2349195 |     0.00
      7 |     244 |         309 |     456749 |     0.01 |       3152989 |     0.00
      8 |     244 |         393 |     658530 |     0.01 |       4168022 |     0.00
      9 |     244 |         510 |    1748446 |     0.02 |       5324083 |     0.00
     10 |     244 |         660 |     837506 |     0.01 |       6175995 |     0.00
     11 |     244 |         839 |    1238649 |     0.01 |       7998575 |     0.00
     12 |     244 |        1161 |    1155261 |     0.01 |       8722566 |     0.00
     13 |     244 |        1777 |    1590755 |     0.02 |      10255668 |     0.00
     14 |     244 |        3757 |    1892540 |     0.02 |      12267192 |     0.00
     15 |     244 |        7503 |    1970249 |     0.02 |      13806280 |     0.00
     16 |     244 |       14022 |    2246406 |     0.02 |       9873244 |     0.00
     17 |     244 |       27798 |    3710787 |     0.04 |      15258889 |     0.00
     18 |     244 |       60297 |    6049492 |     0.07 |      27484995 |     0.00
     19 |     243 |      263828 |    6925772 |     0.08 |      61324608 |     0.00
     20 |     243 | 11359462345 | 9082897956 |    99.64 | 1914448979042 |    99.99
        |    4878 | 11359462345 | 9115363026 |   100.00 | 1914641157575 |   100.00


---------- pg_stat_statements.max = 10000
SYSTEM 3:

 bucket | entries | max_calls | total_time | pct_time |    rows     | pct_rows
--------+---------+-----------+------------+----------+-------------+----------
      1 |     405 |         1 |       1903 |     0.00 |          24 |     0.00
      2 |     405 |         1 |      11492 |     0.00 |          20 |     0.00
      3 |     405 |         1 |       1755 |     0.00 |          61 |     0.00
      4 |     405 |         1 |       1628 |     0.00 |          71 |     0.00
      5 |     405 |         1 |       1758 |     0.00 |          16 |     0.00
      6 |     405 |         1 |       1867 |     0.00 |          18 |     0.00
      7 |     404 |         1 |       1596 |     0.00 |          23 |     0.00
      8 |     404 |         1 |       1679 |     0.00 |          13 |     0.00
      9 |     404 |         1 |       2984 |     0.00 |          23 |     0.00
     10 |     404 |         1 |       6016 |     0.00 |         248 |     0.00
     11 |     404 |         1 |       1714 |     0.00 |          49 |     0.00
     12 |     404 |         1 |       1732 |     0.00 |          17 |     0.00
     13 |     404 |         6 |      19308 |     0.01 |        1436 |     0.00
     14 |     404 |        23 |   27046782 |     9.51 |        4181 |     0.00
     15 |     404 |        90 |    1905408 |     0.67 |       18336 |     0.00
     16 |     404 |       644 |      93061 |     0.03 |      111651 |     0.00
     17 |     404 |      3083 |   12994763 |     4.57 |      516112 |     0.00
     18 |     404 |     24168 |     234976 |     0.08 |     5065065 |     0.05
     19 |     404 |    105507 |    9468647 |     3.33 |    24062919 |     0.22
     20 |     404 | 749065295 |  232561858 |    81.78 | 10913252211 |    99.73
        |    8086 | 749065295 |  284360925 |   100.00 | 10943032494 |   100.00

SYSTEM 4:

 bucket | entries | max_calls  | total_time | pct_time |    rows    | pct_rows
--------+---------+------------+------------+----------+------------+----------
      1 |     346 |          1 |      19667 |     0.00 |          2 |     0.00
      2 |     346 |          1 |      49980 |     0.01 |          4 |     0.00
      3 |     346 |          1 |      34046 |     0.00 |          8 |     0.00
      4 |     346 |          1 |     102305 |     0.01 |         43 |     0.00
      5 |     346 |          1 |      51369 |     0.01 |          5 |     0.00
      6 |     346 |          1 |      18724 |     0.00 |          5 |     0.00
      7 |     346 |          1 |      32055 |     0.00 |         43 |     0.00
      8 |     346 |          1 |      35607 |     0.01 |         45 |     0.00
      9 |     346 |          1 |      16091 |     0.00 |         31 |     0.00
     10 |     346 |          1 |      18696 |     0.00 |          6 |     0.00
     11 |     346 |          1 |      88879 |     0.01 |          5 |     0.00
     12 |     346 |          1 |      18936 |     0.00 |          9 |     0.00
     13 |     346 |          1 |       2720 |     0.00 |        408 |     0.00
     14 |     346 |          1 |      33465 |     0.00 |         13 |     0.00
     15 |     346 |          1 |      22200 |     0.00 |          2 |     0.00
     16 |     346 |          3 |    1516109 |     0.22 |        369 |     0.00
     17 |     346 |         48 |   47541581 |     6.96 |      36564 |     0.00
     18 |     346 |      13118 |   30152991 |     4.41 |     822077 |     0.01
     19 |     346 |     151800 |   13622497 |     1.99 |   22931644 |     0.26
     20 |     346 | 1830494680 |  589642951 |    86.33 | 8682168726 |    99.73
        |    6920 | 1830494680 |  683020868 |   100.00 | 8705960009 |   100.00

SYSTEM 5:


 bucket | entries | max_calls  | total_time  | pct_time |    rows     | pct_rows
--------+---------+------------+-------------+----------+-------------+----------
      1 |     475 |          9 |      893189 |     0.01 |      958040 |     0.00
      2 |     475 |         19 |     2462391 |     0.02 |     6248719 |     0.03
      3 |     475 |         34 |    20795485 |     0.15 |    61456170 |     0.27
      4 |     475 |         54 |   192705939 |     1.38 |    29269715 |     0.13
      5 |     475 |         76 |    20947539 |     0.15 |    62108105 |     0.27
      6 |     475 |        105 |    30439360 |     0.22 |    29579780 |     0.13
      7 |     475 |        149 |   112750582 |     0.81 |    12169241 |     0.05
      8 |     475 |        197 |    59168134 |     0.42 |    23153432 |     0.10
      9 |     475 |        233 |   270201714 |     1.93 |    47299193 |     0.21
     10 |     475 |        344 |    49366939 |     0.35 |    26034858 |     0.11
     11 |     474 |        480 |   147016160 |     1.05 |    25381678 |     0.11
     12 |     474 |        722 |   128961149 |     0.92 |    29928478 |     0.13
     13 |     474 |       1072 |   281782913 |     2.01 |    85346789 |     0.37
     14 |     474 |       1832 |    72278183 |     0.52 |    70689440 |     0.31
     15 |     474 |       2972 |  1476021946 |    10.55 |   196298836 |     0.86
     16 |     474 |       5707 |   967959256 |     6.92 |   183873573 |     0.80
     17 |     474 |      12876 |  1654604242 |    11.83 |   865935480 |     3.77
     18 |     474 |      30402 |  3708901952 |    26.51 |  1213651635 |     5.29
     19 |     474 |     250514 |  1028521943 |     7.35 |  7294711094 |    31.77
     20 |     474 | 1095421646 |  3765557605 |    26.91 | 12693321192 |    55.29
 [NULL] |    9490 | 1095421646 | 13991336620 |   100.00 | 22957415448 |   100.00

SYSTEM 6:

 bucket | entries | max_calls | total_time | pct_time |    rows    | pct_rows
--------+---------+-----------+------------+----------+------------+----------
      1 |     489 |         1 |      19089 |     0.01 |        227 |     0.00
      2 |     489 |         1 |      17191 |     0.01 |         63 |     0.00
      3 |     489 |         1 |      16191 |     0.01 |          3 |     0.00
      4 |     489 |         1 |      19004 |     0.01 |         46 |     0.00
      5 |     489 |         1 |      15455 |     0.01 |       2929 |     0.00
      6 |     488 |         1 |      15646 |     0.01 |          3 |     0.00
      7 |     488 |         1 |      14648 |     0.01 |         11 |     0.00
      8 |     488 |         1 |      42245 |     0.02 |          4 |     0.00
      9 |     488 |         1 |      25555 |     0.01 |         67 |     0.00
     10 |     488 |         1 |      22782 |     0.01 |       3137 |     0.00
     11 |     488 |         1 |       9012 |     0.00 |        110 |     0.00
     12 |     488 |         1 |      22773 |     0.01 |         76 |     0.00
     13 |     488 |         1 |      27693 |     0.01 |          3 |     0.00
     14 |     488 |         3 |    1526935 |     0.65 |       6347 |     0.00
     15 |     488 |        14 |    1563175 |     0.67 |      32264 |     0.00
     16 |     488 |        65 |    2159331 |     0.92 |     175479 |     0.01
     17 |     488 |       658 |   26080111 |    11.11 |     548347 |     0.02
     18 |     488 |     15043 |   71774949 |    30.57 |    2366723 |     0.09
     19 |     488 |    139639 |   35335687 |    15.05 |   44022110 |     1.72
     20 |     488 |  80924635 |   96044962 |    40.91 | 2512784413 |    98.16
        |    9765 |  80924635 |  234752432 |   100.00 | 2559942362 |   100.00

SYSTEM 7:

 bucket | entries | max_calls | total_time  | pct_time |    rows     | pct_rows
--------+---------+-----------+-------------+----------+-------------+----------
      1 |     474 |         1 |      425784 |     0.00 |      361694 |     0.00
      2 |     474 |         2 |    22314020 |     0.22 |     2696158 |     0.00
      3 |     474 |         3 |    35609364 |     0.35 |   121351894 |     0.15
      4 |     474 |         5 |    69937281 |     0.69 |   103883776 |     0.13
      5 |     474 |         7 |    85452745 |     0.84 |   164374678 |     0.20
      6 |     473 |        11 |    19180309 |     0.19 |     7020715 |     0.01
      7 |     473 |        22 |    51312437 |     0.50 |    19251387 |     0.02
      8 |     473 |        55 |   169262450 |     1.66 |    22329861 |     0.03
      9 |     473 |        96 |  2198500745 |    21.58 |    64316407 |     0.08
     10 |     473 |       170 |  1745728752 |    17.13 |   361109507 |     0.45
     11 |     473 |       315 |   346059472 |     3.40 |   621710946 |     0.77
     12 |     473 |       480 |   318995391 |     3.13 |   692631372 |     0.86
     13 |     473 |      1332 |   228300426 |     2.24 |   505810992 |     0.63
     14 |     473 |      4572 |   417885907 |     4.10 |   446193312 |     0.55
     15 |     473 |      8989 |    69602904 |     0.68 |   332055799 |     0.41
     16 |     473 |     77792 |   877588966 |     8.61 |   241092971 |     0.30
     17 |     473 |    101016 |   416842394 |     4.09 |   930288091 |     1.16
     18 |     473 |    222680 |   704017256 |     6.91 |  9582455990 |    11.91
     19 |     473 |    320247 |    59125467 |     0.58 |  6191656576 |     7.69
     20 |     473 | 781250067 |  2352601485 |    23.09 | 60073580668 |    74.64
        |    9465 | 781250067 | 10188743553 |   100.00 | 80484172794 |   100.00

SYSTEM 8:

 bucket | entries | max_calls | total_time | pct_time |   rows    | pct_rows
--------+---------+-----------+------------+----------+-----------+----------
      1 |     488 |         1 |      23279 |     0.01 |       386 |     0.00
      2 |     488 |         1 |      23384 |     0.01 |       122 |     0.00
      3 |     488 |         1 |      35873 |     0.02 |        22 |     0.00
      4 |     488 |         1 |      23156 |     0.01 |        35 |     0.00
      5 |     487 |         1 |      13197 |     0.01 |       317 |     0.00
      6 |     487 |         1 |     766750 |     0.44 |        65 |     0.00
      7 |     487 |         1 |      31692 |     0.02 |       393 |     0.00
      8 |     487 |         1 |      18710 |     0.01 |     73103 |     0.02
      9 |     487 |         1 |      30208 |     0.02 |       152 |     0.00
     10 |     487 |         1 |      23425 |     0.01 |        14 |     0.00
     11 |     487 |         1 |      27419 |     0.02 |        20 |     0.00
     12 |     487 |         1 |      43374 |     0.02 |         3 |     0.00
     13 |     487 |         1 |      18055 |     0.01 |        58 |     0.00
     14 |     487 |         1 |      19688 |     0.01 |         6 |     0.00
     15 |     487 |         1 |      22245 |     0.01 |       439 |     0.00
     16 |     487 |        24 |    2039181 |     1.16 |    223493 |     0.05
     17 |     487 |       768 |   25701471 |    14.62 |    884186 |     0.20
     18 |     487 |     14121 |   53778863 |    30.60 |   2223665 |     0.50
     19 |     487 |    164629 |   34266645 |    19.49 |  37279499 |     8.41
     20 |     487 |  46517095 |   58866393 |    33.49 | 402612053 |    90.82
        |    9744 |  46517095 |  175773006 |   100.00 | 443298031 |   100.00


-- 
Jeremy Schneider
Database Engineer
Amazon Web Services

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux