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
1	Registration	CM-handles/second	65.145	66.050
2	DeRegistration	CM-handles/second	414.503	423.390
3a	Id Search no filter	milliseconds	251.698	255.105
3b	ID Search Module filter	milliseconds	229.158	232.945
3c	ID search Property Feature	milliseconds	605.919	613.579
3d	ID Search CPS Path	milliseconds	603.150	606.522
3e	ID search Trust level	milliseconds	2045.283	1943.426
4a	CM Handle search no filter	milliseconds	2253.802	2291.049
4b	CM Handle Search Module filter	milliseconds	3440.510	3504.141
4c	CM Handle search Property filter	milliseconds	3767.822	3875.768
4d	CM Handle search CPS Path filter	milliseconds	3802.582	3858.906
4e	CM Handle search Trust level filter	milliseconds	5291.188	5314.691
5a	P/T Read	milliseconds	18.177	18.344
5b	P/T Read Alternate ID	milliseconds	44.931	44.925
6a	CM-H P/T write	milliseconds	27.048	27.311
6b	CM-H P/T write alternate id	milliseconds	38.520	37.887
7	Legacy Batch	events/second	230.700	225.487

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
1	Registration	CM-handles/second	65.145	65.920
2	DeRegistration	CM-handles/second	414.503	456.307
3a	Id Search no filter	milliseconds	251.698	253.197
3b	ID Search Module filter	milliseconds	229.158	221.787
3c	ID search Property Feature	milliseconds	605.919	582.223
3d	ID Search CPS Path	milliseconds	603.150	583.439
3e	ID search Trust level	milliseconds	2045.283	2292.282
4a	CM Handle search no filter	milliseconds	2253.802	2215.542
4b	CM Handle Search Module filter	milliseconds	3440.510	3073.727
4c	CM Handle search Property filter	milliseconds	3767.822	3409.491
4d	CM Handle search CPS Path filter	milliseconds	3802.582	3394.069
4e	CM Handle search Trust level filter	milliseconds	5291.188	5166.248
5a	P/T Read	milliseconds	18.177	19.472
5b	P/T Read Alternate ID	milliseconds	44.931	46.016
6a	CM-H P/T write	milliseconds	27.048	27.103
6b	CM-H P/T write alternate id	milliseconds	38.520	41.335
7	Legacy Batch	events/second	230.700	257.933

autovacuum_work_mem = '8MB'

Setting maintenance_work_mem to a high value will also allow autovacuum workers to use that much memory each. A vacuum worker uses 6 bytes of memory for each dead tuple it wants to clean up, so a value of just 8MB will allow for about 1.4 million dead tuples.

#	Test	Units	Base	New Parameter Test
1	Registration	CM-handles/second	65.145	65.721
2	DeRegistration	CM-handles/second	414.503	409.907
3a	Id Search no filter	milliseconds	251.698	250.593
3b	ID Search Module filter	milliseconds	229.158	222.977
3c	ID search Property Feature	milliseconds	605.919	608.341
3d	ID Search CPS Path	milliseconds	603.150	605.082
3e	ID search Trust level	milliseconds	2045.283	2038.409
4a	CM Handle search no filter	milliseconds	2253.802	2236.383
4b	CM Handle Search Module filter	milliseconds	3440.510	3348.030
4c	CM Handle search Property filter	milliseconds	3767.822	3755.318
4d	CM Handle search CPS Path filter	milliseconds	3802.582	3753.047
4e	CM Handle search Trust level filter	milliseconds	5291.188	5227.262
5a	P/T Read	milliseconds	18.177	17.688
5b	P/T Read Alternate ID	milliseconds	44.931	44.034
6a	CM-H P/T write	milliseconds	27.048	26.505
6b	CM-H P/T write alternate id	milliseconds	38.520	39.084
7	Legacy Batch	events/second	230.700	237.738

shared_buffers = '256MB' & effective_cache_size = '768MB'

This parameter has the most variance of all. Some workloads work best with minimal values (such as 1GB or 2GB), even with huge database volumes. Other workloads require large values. The LEAST(RAM/2, 10GB) is a reasonable starting point. PGTune recommends 256MB shared_buffers and 768MB effective_cache_size for a 1Gb system.

#	Test	Units	Base	New Parameter Test
1	Registration	CM-handles/second	65.145	65.624
2	DeRegistration	CM-handles/second	414.503	420.871
3a	Id Search no filter	milliseconds	251.698	251.827
3b	ID Search Module filter	milliseconds	229.158	230.882
3c	ID search Property Feature	milliseconds	605.919	607.917
3d	ID Search CPS Path	milliseconds	603.150	605.288
3e	ID search Trust level	milliseconds	2045.283	1986.362
4a	CM Handle search no filter	milliseconds	2253.802	2274.026
4b	CM Handle Search Module filter	milliseconds	3440.510	3560.067
4c	CM Handle search Property filter	milliseconds	3767.822	3879.869
4d	CM Handle search CPS Path filter	milliseconds	3802.582	3882.111
4e	CM Handle search Trust level filter	milliseconds	5291.188	5371.930
5a	P/T Read	milliseconds	18.177	18.380
5b	P/T Read Alternate ID	milliseconds	44.931	44.203
6a	CM-H P/T write	milliseconds	27.048	26.770
6b	CM-H P/T write alternate id	milliseconds	38.520	39.225
7	Legacy Batch	events/second	230.700	231.402