Role will tune postgresql server according to information about hardware.
Followin OS parameters will be updated:
- vm.swappiness
- vm.overcommit_memory
Followin postgresql parameters will be updated:
- postgresql_work_mem
- max_connections
- maintenance_work_mem
- shared_buffers
- effective_cache_size
- random_page_cost
Postgresql server must be installed and running.
For ssd drives 1 should be replaced with higher number (e.g. 2,3 or even 10 depending on disk speed)
Default value is:
postgresql_drive_effective_spindle_count: 1
On small instances with 4 CPU or less it is recommended to have at least 10 connections
default is:
postgresql_min_connections: 10
should be 1.1 for ssd, 4 for hdd and 5 for very slow virtual or network drives
default is:
postgresql_random_page_cost: 4
Name of the postgresql service (on RHEL familiy is is postgresql but if you install from postgresql repo it is postgesql-number)
default is:
postgresql_service_name: postgresql
Setting overcommit to 2 prevents postgresql crashing from out of memory error
Allowed values are:
0 - heuristic overcommit
1 - always overcommit, never check
2 - always check, never overcommit
default value is:
vm_overcommit_memory: 2
Sets swappiness to minimal level but does not disable it (to disable set to 0)
default value is:
vm_swappiness: 1
Make sure to install required modules:
ansible-galaxy collection install community.general ansible.posix
In this example 2 parameters for SSD drive installation are used:
- hosts: servers
roles:
- { role: yilativs.postgresql_tuning, postgresql_random_page_cost: 1, postgresql_drive_effective_spindle_count: 2 }
GPLv2
Vitaliy Semochking yilativs@gmail.com