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
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.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
tune-adm profile postgresql and then double-check that it took effect with
Once activated, you should be all set. Happy computing, or something. ;)