PostgreSQL‎ > ‎

Server Settings

Kernel Parameters

The default maximum segment size is 32 MB, and the default maximum total size is 2097152 pages. A page is almost always 4096 bytes except in unusual kernel configurations with "huge pages" (use getconf PAGE_SIZE to verify).

The shared memory size settings can be changed via the sysctl interface. For example, to allow 16 GB:

$ sysctl -w kernel.shmmax=17179869184
$ sysctl -w kernel.shmall=4194304


In addition these settings can be preserved between reboots in the file /etc/sysctl.conf. Doing that is highly recommended. (DTS: Better would be /etc/sysctl.d/00-postgresql.conf)
The remaining defaults are quite generously sized, and usually do not require changes.

Scheduler

kernel.sched_migration_cost=5000000
kernel.sched_autogroup_enabled=0

Huge Pages

Transparent Huge Pages

Recommendation is to disable transparent huge pages on Linux. PostgreSQL does not benefit from them and there are some known issues when they are enabled. Similar to Oracle.



NUMA

Disable zone reclaim mode. In /etc/sysctl.conf:

vm.zone_reclaim_mode = 0 

I/O

noop scheduler works well for RAID or SSD.

Deadline I/O scheduler is widely used for all database services, as it enforces latency limits over all I/O requests. I/O is done in batches, and we can tune the batch size and expire timings using the following parameters:
  • fifo_batch
  • read_expire
  • write_expire
  • writes_starved

Reference

Comments