Postgresql sysctl.conf

From NailingJello.com
Jump to: navigation, search

Recommended reading:

Postgresql tunning guide

The following setup the shared memory space for the database. Make sure to include a little bit extra for other processes that may use this space. I usually allocate from 5 to 10% more than what is allocated to the database. The settings were used on a server with 32G of RAM.

kernel.shmmax = 18496081920
kernel.shmall = 4515645

Under heavy IO workload, once the kernel has dirty_background_bytes worth of data to flush to disk, it will begin that operation. Once the dirty_bytes level is reached kernel will stop everything until the data has been flushed to disk.

These two values are critical and need to be tuned to your environment (specifically your disk speed). You do not want them to be changed when you adjust memory settings. For that reason I recommend setting these values as opposed to vm.dirty_ratio.

vm.dirty_bytes = 134217728
vm.dirty_background_bytes = 67108864

The net effect of having these values set too high is that when your system gets really busy the database will simply appear to become unavailable until most of the data in the buffer is written to disk. That may be several gigabytes's worth of pages which can take up to an hour. During this time your database will appear to be completely unresponsive. Set it to a value that you know will flush to disk within an acceptable amount of time for your environment.