Tuning Linux kernel for PostgreSQL

Let us look at how to tune the Linux kernel before deploying PostgreSQL on production servers.

Check this documentation If you want detailed information concerning the linux kernel. Also there is an Ansible role developed by me to fine-tune the linux kernel before deploying PostgreSQL.

shmmax parameter

Shmmax, a kernel parameter refers to the maximum size of a shared memory segment allocated to a process. So assuming we did not adjust the kernel.shmmax parameter for the PostgreSQL service, executing the ipcs command will show the default value of kernel.shmmax assigned to PostgreSQL.

------ Shared Memory Segments --------
key shmid owner perms bytes nattch status
0x01a42e4c 0 postgres 600 56 6
0x00000000 7 mikey 600 524288 2 dest
0x00000000 21 mikey 600 524288 2 dest
0x00000000 22 mikey 600 49152 2 dest
0x00000000 23 mikey 600 49152 2 dest
0x00000000 26 mikey 600 524288 2 dest

It is advisable to increase the value of shmmax to allocate a sizeable memory segment to PostgreSQL.

Changing the value of kernel.shmmax

We can change the value of the kernel.shmmax parameter via sysctl command or add an entry to the path /etc/sysctl.conf. The sysctl command allows us to change the value of shmmax temporarily. You can add an entry to the sysctl.conf file makes the change permanent.

Using the systctl command to set a new value for kernel.shmmax

sudo sysctl -w kernel.shmmax=16777216

Adding an entry to sysctl.conf file

Use the command sudo vim /etc/sysctl.conf to open the file sysctl.conf and add the following value for kernel.shmmax parameter:

kernel.shmmax=16777216

shmall parameter

This kernel parameter refers to the total size of the shared memory pages. Make sure the value you set for the kernel.shmall parameter is large enough for the PostgreSQL server (and other processes or applications).

You can set the value of kernel.shmall parameter to 2097152 via the sysctl command as shown below or add it to the sysctl.conf file inside the /etc directory to make it permanent.

sudo sysctl -w kernel.shmall=2097152

Swappiness parameter

The vm.swappiness parameter is used to control memory paging. Memory paging (also referred to as swapping) is a memory management plan that allows the operating system to retrieve processes from secondary storage into the main memory in the form of pages.

The value for this parameter ranges from 0 to 100. By default, the swappiness value is set to 60. We should expect the operating system to swap more frequently. Frequent swapping could affect database performance.

Setting the vm.swappiness parameter value to 1 prevents the operating systems from swapping frequently. It will only swap when it’s necessary to avoid a PostgreSQL crash.

Overcommit memory parameter

The vm.overcommit_memory parameter allows the kernel to allocate more memory than the available RAM on the system.

Over-committing memory is possible because the kernel assumes that not every process running on a Linux server would completely consumes the memory assigned to it.

If we have an Ec2 instance available with a RAM or main memory of 5GB, we can allocate 2GB each to A, B, and C Linux processes running on the Ec2 instance server. This simply means we have allocated more memory resources to active processes than the physical memory available.

Assuming process A does not consume all the memory assigned to it since it is not active anymore. So the kernel in this case moves process A to disk and then assigns unused memory of process A to another active process.

However, it is not advisable to enable this kernel parameter if you have a Linux server hosting other processes apart from the postmaster process belonging to PostgreSQL.

Why? Because the kernel will terminate the postmaster process if it finds out that there is not enough memory for PostgreSQL to consume.

Out of Memory: Killed process 12345 (postgres)

You can disable the vm.overcommit_memory parameter by assigning the value 2 to it if you are not sure of the workload of the PostgreSQL server.

Overcommit memory ratio parameter

This parameter prevents the kernel from over-committing memory to active processes on a Linux server beyond the percentage range. By default, the value for the vm.overcommit_ratio is 50. So in this case, the kernel can’t overcommit memory beyond the 50% range to active processes.

A Linux server with a RAM of 2GB expects the kernel to over-commit 3GB (and not more than 3GB of RAM to active/running processes).

Huge pages

Huge Pages is present in Linux kernel 2.6 and above. This parameter allows the kernel to manage memory pages greater than the default value of 4KB. In Linux, every active process is allocated a chunk or block of RAM known technically known as a memory page. So if there is a process like the postmaster process which requires more memory, then more pages or chunks of RAM will be allocated. Every process in Linux has its independent page table.

So assuming we decided not to increase the page size above the default value which is 4K for processes noted for excess memory operations, then the kernel needs to allocate many pages in smaller sizes.

The smaller the page size, the more time it takes for the kernel to look up to a page in the page table. Therefore huge page size reduces the overhead in accessing a page in the page table.

So in a situation where you have active processes on a Linux server known for performing excess memory operations, then you may need to set the Huge page size to 1GB.

Dirty background ratio parameter

vm.dirty_background_ratio sets the minimum memory percentage of the system memory for dirty pages, before the system starts to flush dirty pages to the disk. Flushing is done in the background. The value for this parameter ranges from 0 to 100.

When the percentage memory for dirty pages is reached, all dirty pages in the memory are flushed in the background to the disk without blocking the application making writes also to the disk.

You can set the value for this parameter to 10 or 15 depending on the total size of RAM. If you set it too high, it might introduce bottlenecks into your system. If you set it too low, flushing may not be done often enough, and occasional intensive flushing operations may slow down your system.

Dirty ratio parameter

vm.dirty_ratio is somehow related to the previous parameter vm.dirty_background_ratio. It defines the memory percentage for dirty pages. But unlike the dirty background ratio parameter, it blocks all processes from making writes till it has flushed all dirty pages to the disk. In other words, when dirty pages reach the vm.dirty_background_ratio they start being flushed; if they don’t flush fast enough and they reach vm.dirty_background_ratio they will start flushing aggressively, blocking other processes.

It is advisable to set this parameter to a higher value than the vm.dirty_background_ratio parameter.

Conclusions

Although there are other kernel parameters like file-max, rmem_default, and rmem_max, the above listed kernel parameters are the most important ones to optimise the Linux kernel before deploying PostgreSQL. But if you tune parameters that are not mentioned here, please let us know in the comments.

If you want to make sure that your system and PostgreSQL configurations are optimal, consider our PostgreSQL Health Checks.

Michael Aboagye