We’ve had a small flurry of customers asking about tuning their OS for the best PostgreSQL performance. While the answer to this question is always ’that depends on your hardware and workload’ and involves a lot of iteration between changing a setting and benchmarking, I thought I’d take a moment to point out that once you do manage to dial-in the settings, you should be writing a profile and deploying to your systems for tuned to make use of. Please, for the love of $diety, stop editing sysctl.conf and friends!
If you’re running RedHat (or a RedHat-derived) OS, tuned is probably already installed and may even be already running. If not, it’s a simple yum install tuned to rectify. If you’re on Debian (or a Debian-derived) OS, simply apt install tuned. Now that you have it installed, you can ask it to recommend a profile for you by issuing tuned-adm recommend and you can see which profile, if any, is in use by issuing tuned-adm active. You can also list all the available profiles with tuned-adm list.
That’s great and all, but there’s no PostgreSQL profile that ships with tuned. So, let’s build one! First, decide which of the profiles in tuned-adm list is the most appropriate starting point for you. If you’re running on bare metal, this is probably throughput-performance. If your system is virtualized, you probably want virtual-guest. We’re going to use this profile as the ‘base’ of our profile. Profiles are stored in /etc/tuned, so let’s start setting ours up:
$ mkdir /etc/tuned/postgresql
$ cat << EOF > /etc/tuned/postgresql/tuned.conf
[main]
include= throughput-performance
EOF
As you can see, we’re going to start with the throughput-performance profile as a base, and then make tweaks from there. The first tweak? Disable Transparent Huge Pages (THP):
$ cat << EOF >> /etc/tuned/postgresql/tuned.conf
[vm]
transparent_hugepages=never
EOF
If you’re not aware, THP on a dedicated PostgreSQL server really don’t play nicely. It’s best to avoid them completely.
Now, we want to set the ‘standard database VM config’ so we:
$ cat << EOF >> /etc/tuned/postgresql/tuned.conf
[sysctl]
vm.overcommit_memory = 2
vm.swappiness = 1
EOF
As pointed out in the comments, vm.overcommit_memory and vm.dirty_ratio (which I set below) interplay. vm.dirty_ratio is the value that represents the percentage of MemTotal that can consume dirty pages before all processes must write dirty buffers back to disk and when this value is reached all I/O is blocked for any new writes until dirty pages have been flushed. By default this set to 50% which means that at most only half of your MemTotal can ever only be dirty at once. Please see the kernel documentation for details on all these settings.
I, personally, also then add (after benchmarking, of course):
$ cat << EOF >> /etc/tuned/postgresql/tuned.conf
kernel.sched_autogroup_enabled = 0
kernel.sched_migration_cost_ns = 50000000
vm.dirty_background_bytes = 134217728
vm.dirty_expire_centisecs = 499
vm.dirty_ratio = 90
vm.overcommit_ratio = 90
vm.zone_reclaim_mode = 0
EOF
This gives you a complete PostgreSQL tuned profile that looks like:
[main]
include= throughput-performance
[sysctl]
kernel.sched_autogroup_enabled = 0
kernel.sched_migration_cost_ns = 50000000
vm.dirty_background_bytes = 134217728
vm.dirty_expire_centisecs = 499
vm.dirty_ratio = 90
vm.overcommit_memory = 2
vm.overcommit_ratio = 90
vm.swappiness = 1
vm.zone_reclaim_mode = 0
[vm]
transparent_hugepages=never
(well, in a slightly different order, but you get the drift).
Now that we have our profile, run a quick tuned-adm list to ensure that it is listed as available. Assuming it is, you can enable it with tuned-adm profile postgresql and then double-check that it took effect with tuned-adm active.
Once activated, you should be all set. Happy computing, or something. ;)