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