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

#	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

#	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

#	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%
6a	CM-H P/T write	milliseconds	27.048	27.103	Negligible
6b	CM-H P/T write alternate id	milliseconds	38.520	41.335	-7%
7	Legacy Batch	events/second	230.700	257.933	12%

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	Difference

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

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	Difference

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