CPS-2418 Review Postgres configuration
Purpose:
RegistrationReview current Postgres configuration and how it conforms to the current recommended/optimal settings.
Process:
Search multiple sources including wikis, videos and official documentation to source the common recommended settings and suggested tweaks to reach optimal performance.
Test changes using k6 tests locally.
Results:
wal_recycle = FALSE
Recycling WAL files. A WAL file is a Write Ahead Log file. The default for postgres is to recycle these files instead of creating new files. This is done by renaming them.
On Cloud and COW file systems it is often optimal to create new files instead of renaming.
# | Test | Units | Base | New Parameter Test | Difference |
|---|---|---|---|---|---|
1 | Registration | CM-handles/second | 65.145 | 65.734 | 1% |
2 | DeRegistration | CM-handles/second | 414.503 | 406.904 | -2% |
3a | Id Search no filter | milliseconds | 251.698 | 247.557 | 1.5% |
3b | ID Search Module filter | milliseconds | 229.158 | 221.479 | 3% |
3c | ID search Property Feature | milliseconds | 605.919 | 598.145 | 1% |
3d | ID Search CPS Path | milliseconds | 603.150 | 590.957 | 2% |
3e | ID search Trust level | milliseconds | 2045.283 | 2003.485 | 2% |
4a | CM Handle search no filter | milliseconds | 2253.802 | 2200.563 | 2% |
4b | CM Handle Search Module filter | milliseconds | 3440.510 | 3435.791 | Negligible |
4c | CM Handle search Property filter | milliseconds | 3767.822 | 3770.819 | Negligible |
4d | CM Handle search CPS Path filter | milliseconds | 3802.582 | 3792.978 | Negligible |
4e | CM Handle search Trust level filter | milliseconds | 5291.188 | 5335.575 | -1% |
5a | P/T Read | milliseconds | 18.177 | 18.089 | Negligible |
5b | P/T Read Alternate ID | milliseconds | 44.931 | 43.602 | 3% |
6a | CM-H P/T write | milliseconds | 27.048 | 26.271 | 3% |
6b | CM-H P/T write alternate id | milliseconds | 38.520 | 37.115 | 3.5% |
7 | Legacy Batch | events/second | 230.700 | 244.442 | 6% |
effective_cache_size = 512mb
This should be set to the smaller value of either 0.75* total RAM amount, or the sum of buff/cache, free RAM, and shared buffers in the output of free command, and is used to give PostgreSQL a hint about how much total cache space is available. This refers to caches in main memory, not CPU cache.
# | Test | Units | Base | New Parameter Test | Difference |
|---|---|---|---|---|---|
1 | Registration | CM-handles/second | 65.145 | 66.050 | 1% |
2 | DeRegistration | CM-handles/second | 414.503 | 423.390 | 2% |
3a | Id Search no filter | milliseconds | 251.698 | 255.105 | -1% |
3b | ID Search Module filter | milliseconds | 229.158 | 232.945 | -1.5% |
3c | ID search Property Feature | milliseconds | 605.919 | 613.579 | -1% |
3d | ID Search CPS Path | milliseconds | 603.150 | 606.522 | -0.5% |
3e | ID search Trust level | milliseconds | 2045.283 | 1943.426 | 5% |
4a | CM Handle search no filter | milliseconds | 2253.802 | 2291.049 | -1.5% |
4b | CM Handle Search Module filter | milliseconds | 3440.510 | 3504.141 | -2% |
4c | CM Handle search Property filter | milliseconds | 3767.822 | 3875.768 | -3% |
4d | CM Handle search CPS Path filter | milliseconds | 3802.582 | 3858.906 | -1.5% |
4e | CM Handle search Trust level filter | milliseconds | 5291.188 | 5314.691 | -0.5% |
5a | P/T Read | milliseconds | 18.177 | 18.344 | -1% |
5b | P/T Read Alternate ID | milliseconds | 44.931 | 44.925 | Negligible |
6a | CM-H P/T write | milliseconds | 27.048 | 27.311 | -1% |
6b | CM-H P/T write alternate id | milliseconds | 38.520 | 37.887 | 1.5% |
7 | Legacy Batch | events/second | 230.700 | 225.487 | -2% |
work_mem = '32MB'
The recommended starting point for work_mem is ((Total RAM - shared_buffers)/(16 x CPU cores)). The rationale behind this formula is that if you have numerous queries risking memory depletion, the system will already be constrained by CPU capacity. This formula provides for a relatively large limit for the general case.
# | Test | Units | Base | New Parameter Test | Difference |
|---|---|---|---|---|---|
1 | Registration | CM-handles/second | 65.145 | 65.920 | 1% |
2 | DeRegistration | CM-handles/second | 414.503 | 456.307 | 10% |
3a | Id Search no filter | milliseconds | 251.698 | 253.197 | -0.5% |
3b | ID Search Module filter | milliseconds | 229.158 | 221.787 | 3% |
3c | ID search Property Feature | milliseconds | 605.919 | 582.223 | 4% |
3d | ID Search CPS Path | milliseconds | 603.150 | 583.439 | 3% |
3e | ID search Trust level | milliseconds | 2045.283 | 2292.282 | -12% |
4a | CM Handle search no filter | milliseconds | 2253.802 | 2215.542 | 1.5% |
4b | CM Handle Search Module filter | milliseconds | 3440.510 | 3073.727 | 11% |
4c | CM Handle search Property filter | milliseconds | 3767.822 | 3409.491 | 9.5% |
4d | CM Handle search CPS Path filter | milliseconds | 3802.582 | 3394.069 | 11% |
4e | CM Handle search Trust level filter | milliseconds | 5291.188 | 5166.248 | 2% |
5a | P/T Read | milliseconds | 18.177 | 19.472 | -7% |
5b | P/T Read Alternate ID | milliseconds | 44.931 | 46.016 | -2% |