ca4ti

Postgres Optimization and Performance Tuning

based on the following article: An Introduction to PostgreSQL Performance Tuning and Optimization

huge pages: Enabling on Linux will give a performance boost to PostgreSQL (VMPeak / HugePageSize = 4310)
max_connections: optimal number is roughly 4 times the number of CPU cores. The recommended number is the GREATEST(4 x CPU cores, 100). Beyond this number, a connection pooler such as pgbouncer should be used. avoid setting max_connections too high as it will increase the size of various data structures in Postgres which can result in CPU cycles being wasted
shared_buffers: variable by workload - starting Point: LEAST(RAM/2, 10GB).
work_mem: ((Total RAM - shared_buffers)/(16 x CPU cores))
maintenance_work_mem: 1GB good start
effective_io_concurrency: SSD = 200, HDD = Number of Disks

It is possible to use curl to query the LXD unix.socket directly:

curl --unix-socket /var/lib/lxd/unix.socket http:/1.0/containers | jq .

Example output:

1. Backup VM

	Docs - LXD Helpful Docs:
	https://www.stgraber.org/2016/03/30/lxd-2-0-image-management-512/
	https://www.flockport.com/flockport-first-look-at-lxd/
	http://insights.ubuntu.com/2016/03/14/the-lxd-2-0-story-prologue/

	Docs - Installation:
	http://insights.ubuntu.com/2016/03/14/the-lxd-2-0-story-prologue/

	Docs - API:
	https://linuxcontainers.org/lxd/getting-started-cli/

	#Replace 'symplcms' with the database'name
	createdb --template=template0 --locale=en_US.UTF-8 --encoding=UTF8 -U postgres symplcms

	diskutil list
	diskutil unmountDisk /dev/disk2
	diskutil eraseDisk FAT32 SANDISK /dev/disk2