Database Server Configuration
This text describes a checklist you need to follow when configuring a new database server.
Check if the server is available by SSH and that necessary permissions are granted. Depending on the situation you might need to sudo as postgres or root to control PostgreSQL and other services like replication tools, connection poolers, sysfsutils, to use the sysctl utility, to configure of all these things and to use configuration management systems.
user@db1: ~ $ sudo -l
If you are migrating a database to the server or setting up a hot standby ensure that all the required mount points for tablespaces are created. Use \db+
in psql to check tablespaces and their locations.
Ensure that the required version of PotsgreSQL is installed. If it is not then install the package and initialize the cluster. Also check and install all the needed satellite tools like replication systems and connection poolers.
Now update sysctl.conf.
These settings can be set like it is shown below to not reboot the server.
db1: ~ # sysctl -w some.parameter=some_value
Set the SHMMAX and SHMALL kernel settings accordingly to the shared memory amount assumed to be used.
Let us assume that we want to set shared buffers to 25% of RAM. Note that shared buffers should be set slightly less than SHMMAX/SHMALL. So let us set SHMMAX/SHMALL to 35% of RAM.
Calculate them and apply with the sysctl utility once. If you use FreeBSD use sysctl -n hw.availpages
instead of getconf _PHYS_PAGES
.
db1: ~ # _SHMALL=$(expr $(getconf _PHYS_PAGES) \* 35 / 100)
db1: ~ # _SHMMAX=$(expr $(getconf PAGE_SIZE) \* $_SHMALL)
For FreeBSD use kern.ipc.*
instead of kernel.*
.
kernel.shmall = 179085
kernel.shmmax = 733532160
On FreeBSD add kern.ipc.semmap = 256
to these settings too.
kern.ipc.semmap=256
Avoid swap if you need. Note that it is not recommended to do for low RAM servers mostly if they are not dedicated for PostgreSQL as swappines may free some memory by moving some initialization data to swap or it might warn for about lack of memory.
vm.swappiness = 0
For FreeBSD like this.
vm.swap_enabled=0
If swap is not disabled on FreeBSD the following makes shared pages unswappable that is highly recommended for databases.
kern.ipc.shm_use_phys=1
Maximum number of file-handles for Linux. It must be high for active servers.
fs.file-max = 65535
And for FreeBSD.
kern.maxfiles=65535
kern.maxfilesperproc=65535
pdflush tuning to prevent lag spikes for old Linux kernels.
vm.dirty_ratio = 10
vm.dirty_background_ratio = 1
vm.dirty_expire_centisecs = 499
pdflush tuning to prevent lag spikes for new Linux kernels. The recommended estimation is 64MB and 50% of the controller cache size accordingly if the cache size is known. Otherwise 8MB and 64MB. Look through dmesg for scsi (hardware RAID) or md (software RAID) to determine what controller is installed.
vm.dirty_background_bytes = 67108864
vm.dirty_bytes = 536870912
On Linux with many processes (eg. client connections) increase this setting to prevent the scheduler breakdown.
kernel.sched_migration_cost = 5000000
It must be turned off on server Linux systems to provide more CPU to PostgreSQL.
kernel.sched_autogroup_enabled = 0
Setup hugepages for Linux. Do not forget to replace 110 with your postgres group in vm.hugetlb_shm_group.
vm.hugetlb_shm_group = 110
vm.hugepages_treat_as_movable = 0
vm.nr_overcommit_hugepages = 512
The Huge Page Size is 2048kB. So for example for 16GB shared buffers the number of them is 8192.
vm.nr_hugepages = 8192
On old Linux kernels, that does not support vm.nr_overcommit_hugepages append additional 512 to the vm.nr_hugepages number.
To make PostgreSQL use hugepages download and make the library as described on http://oss.linbit.com/hugetlb/ and add it to the environment for the postgres user (the environment file is in /etc/postgresql/ or /etc/sysconfig/pgsql/ or .bash_profile in postgres home depending on Linux distributive).
LD_PRELOAD='/usr/local/lib/hugetlb.so'
export LD_PRELOAD
On modern systems you can find libhugetlbfs0 package for this purpose. Install it instead of building hugetlb and add libhugetlbfs0.so to the environment instead of hugetlb.so along with setting huge pages to be used with shared memory.
HUGETLB_SHM=yes
LD_PRELOAD='/usr/lib/libhugetlbfs.so'
export HUGETLB_SHM
export LD_PRELOAD
You also need to remember about setting enough memory locking limits. It must not be less than shared memory amount plus required memory for connections. Let us set it to 64GB in /etc/security/limits.conf.
postgres soft memlock 68719476736
postgres hard memlock 68719476736
To check if it is used by postgres execute the following command.
db1: ~ # pmap -x PID | grep hugetlb.so
Where PID is a process ID of any running postgres process.
And this one to check if it used at all.
db1: ~ # cat /proc/meminfo | grep -i huge
On FreeBSD you will also need to update /boot/loader.conf with SEMMNS and SEMMNI settings, see PostgreSQL documentation for more information about them.
kern.ipc.semmns=32000
kern.ipc.semmni=128
It requires you to reboot.
Transparent huge pages defragmentation could lead to unpredictable database stalls on some Linux kernels. The recommended settings for this are below. Add them to rc.local.
db1: ~ # echo always > /sys/kernel/mm/transparent_hugepage/enabled
db1: ~ # echo madvise > /sys/kernel/mm/transparent_hugepage/defrag
For NUMA hardware users on Linux turn off the NUMA local pages reclaim as it leads to wrong caching strategy for databases.
vm.zone_reclaim_mode = 0
Again on NUMA systems it is recommended to set memory interleaving mode for better performance. The following should show the only node if this mode is on.
db1: ~ # numactl --hardware
Usually it can be set in BIOS however if it is not set this way you can start the database manually with this option.
db1: ~ # numactl --interleave=all /etc/init.d/postgresql start
Now adjust your /etc/fstab.
Set noatime,nobarrier
to gain better performance for data partitions. Due to the known XFS allocation issue in some recent Linux kernels that leads to significant database bloats it is recommended to set allocsize=1m
if you use XFS of course.
/data xfs noatime,nobarrier,allocsize=1m
Remount affected mount points or reboot.
On Linux add the appropriate blockdev settings. Usually good settings for modern systems looks like it is shown below. Add them to rc.local.
db1: ~ # echo noop > /sys/block/sda/queue/scheduler
db1: ~ # echo 16384 > /sys/block/sda/queue/read_ahead_kb
Do not forget about open files limit. For modern servers a good value is 65535.
db1: ~ # ulimit -n 65535
Install all the required locales.
Adjust postgresql.conf, pg_hba.conf and connection pooler configuration (and probably its users configuration). Restart PostgreSQL and the connection pooler.