The optimal performance of PostgreSQL depends on properly defined operating system parameters. Poorly configured OS kernel parameters can lead to a decrease in the performance of the database server. Therefore, it is essential that these parameters are tuned according to the database server and its workload. In this post, we will discuss some important Linux kernel parameters that can affect the performance of the database server and how to configure them.
SHMMAX / SHMALL
SHMMAX is a kernel parameter used to define the maximum size of a single shared memory segment that a Linux process can allocate. Before version 9.2, PostgreSQL used System V (SysV), which requires the configuration of SHMMAX. After 9.2, PostgreSQL switched to POSIX shared memory. So, less bytes of System V shared memory are now required.
Until version 9.3, SHMMAX was the most critical kernel parameter. The value of SHMMAX is set in bytes.
Similarly, SHMALL is another kernel parameter used to determine
the total number of pages of shared memory across the system. To view the current values of SHMMAX, SHMALL, or SHMMIN, use the command ipcs.
SHM* Details — Linux
$ ipcs -lm
------ Shared Memory Limits --------
max number of segments = 4096
max seg size (kbytes) = 1073741824
max total shared memory (kbytes) = 17179869184
min seg size (bytes) = 1SHM* Details — MacOS X
$ ipcs -M
IPC status from as of Thu Aug 16 22:20:35 PKT 2018
shminfo:
shmmax: 16777216 (max shared memory segment size)
shmmin: 1 (min shared memory segment size)
shmmni: 32 (max number of shared memory identifiers)
shmseg: 8 (max shared memory segments per process)
shmall: 1024 (max amount of shared memory in pages)
PostgreSQL uses System V IPC for allocating shared memory. This parameter is one of the most important kernel parameters. Whenever you receive the following error messages, it indicates that you have an older version of PostgreSQL and a very low value for SHMMAX. Users are expected to adjust and increase the value according to the shared memory they intend to use.
Possible Misconfiguration Errors
If SHMMAX is misconfigured, you may encounter errors when trying to initialize a PostgreSQL cluster with the command initdb.
initdb Failure
DETAIL: Failed system call was shmget(key=1, size=2072576, 03600).
HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.
You can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 2072576 bytes),
reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter,
in which case raising the request size or reconfiguring SHMMIN is called for.
The PostgreSQL documentation contains more information about shared memory configuration. child process exited with exit code 1
Similarly, you may encounter an error when starting the PostgreSQL server using the command pg_ctl.
pg_ctl Failure
DETAIL: Failed system call was shmget(key=5432001, size=14385152, 03600).
HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.
You can either reduce the request size or reconfigure the kernel with larger SHMMAX.; To reduce the request size (currently 14385152 bytes), reduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections.
If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter,
in which case raising the request size or reconfiguring SHMMIN is called for.
The PostgreSQL documentation contains more information about shared memory configuration.
Understanding the differences in definitions
The definitions of SHMMAX/SHMALL differ slightly in Linux and MacOS X:
- Linux: kernel.shmmax, kernel.shmall
- MacOS X: kern.sysv.shmmax, kern.sysv.shmall
The command sysctl can be used to temporarily change the value. To set permanent values, add an entry to /etc/sysctl.conf. Details are provided below.
Changing kernel parameters on MacOS X
# Get the value of SHMMAX
sudo sysctl kern.sysv.shmmax
kern.sysv.shmmax: 4096
# Get the value of SHMALL
sudo sysctl kern.sysv.shmall
kern.sysv.shmall: 4096
# Set the value of SHMMAX
sudo sysctl -w kern.sysv.shmmax=16777216
kern.sysv.shmmax: 4096 -> 16777216
# Set the value of SHMALL
sudo sysctl -w kern.sysv.shmall=16777216
kern.sysv.shmall: 4096 -> 16777216Changing kernel parameters on Linux
# Get the value of SHMMAX
sudo sysctl kernel.shmmax
kernel.shmmax: 4096
# Get the value of SHMALL
sudo sysctl kernel.shmall
kernel.shmall: 4096
# Set the value of SHMMAX
sudo sysctl -w kernel.shmmax=16777216
kernel.shmmax: 4096 -> 16777216
# Set the value of SHMALL
sudo sysctl -w kernel.shmall=16777216
kernel.shmall: 4096 -> 16777216Don't forget: to make the changes permanent, add these values to /etc/sysctl.conf
Huge Pages
Linux uses 4 KB memory pages by default, while BSD uses Super Pages, and Windows uses Large Pages. A page is a portion of RAM allocated to a process. A process may have multiple pages depending on memory requirements. The more memory a process needs, the more pages it is allocated. The OS maintains a page allocation table for processes. The smaller the page size, the larger the table, and the longer it takes to locate a page in this page table. Therefore, larger pages allow for a larger amount of memory with reduced overhead; fewer page views, fewer page faults, faster read/write operations through larger buffers. As a result, improved performance.
PostgreSQL supports large pages only on Linux. By default, Linux uses 4 KB memory pages, so in cases where there are too many memory operations, larger pages need to be set. A performance boost is observed when using large pages sized from 2 MB to 1 GB. The size of a large page can be set during boot. You can easily check the settings and usage of large pages on your Linux computer using the command cat /proc/meminfo | grep -i huge.
Obtaining information about large pages (Linux only)
Note: This is only for Linux, for other OS this operation is ignored$ cat /proc/meminfo | grep -i huge
AnonHugePages: 0 kB
ShmemHugePages: 0 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kBIn this example, although the large page size is set to 2048 (2 MB), the total number of large pages is 0. This means that large pages are disabled.
Script to determine the number of large pages
This simple script returns the required number of large pages. Run the script on your Linux server while PostgreSQL is running. Ensure that the environment variable $PGDATA is set to the PostgreSQL data directory.
Obtaining the number of required large pages
#!/bin/bash
pid=`head -1 $PGDATA/postmaster.pid`
echo "Pid: $pid"
peak=`grep ^VmPeak /proc/$pid/status | awk '{ print $2 }'`
echo "VmPeak: $peak kB"
hps=`grep ^Hugepagesize /proc/meminfo | awk '{ print $2 }'`
echo "Hugepagesize: $hps kB"
hp=$((peak/hps))
echo Set Huge Pages: $hpThe script output looks like this:
Script output
Pid: 12737
VmPeak: 180932 kB
Hugepagesize: 2048 kB
Set Huge Pages: 88The recommended value for large pages is 88, so you should set the value to 88.
Setting large pages
sysctl -w vm.nr_hugepages=88Check the large pages now, you will see that large pages are not in use (HugePages_Free = HugePages_Total).
Again, information about large pages (Linux only)
$ cat /proc/meminfo | grep -i huge
AnonHugePages: 0 kB
ShmemHugePages: 0 kB
HugePages_Total: 88
HugePages_Free: 88
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kBNow set the huge_pages parameter to 'on' in $PGDATA/postgresql.conf and restart the server.
And again, information about large pages (Linux only)
$ cat /proc/meminfo | grep -i huge
AnonHugePages: 0 kB
ShmemHugePages: 0 kB
HugePages_Total: 88
HugePages_Free: 81
HugePages_Rsvd: 64
HugePages_Surp: 0
Hugepagesize: 2048 kBNow you can see that very few large pages are being used. Let’s try adding some data to the database.
Some database operations for utilizing large pages
postgres=# CREATE TABLE foo(a INTEGER);
CREATE TABLE
postgres=# INSERT INTO foo VALUES(generate_Series(1,10000000));
INSERT 0 10000000Let's see if we are using more large pages now than before.
Once again, information about large pages (only on Linux)
$ cat /proc/meminfo | grep -i huge
AnonHugePages: 0 kB
ShmemHugePages: 0 kB
HugePages_Total: 88
HugePages_Free: 18
HugePages_Rsvd: 1
HugePages_Surp: 0
Hugepagesize: 2048 kBNow you can see that most large pages are in use.
Note: The approximate value for HugePages used here is very low, which is not a normal value for a production environment machine. Please assess the necessary number of pages for your system and set them accordingly based on load and resources.
vm.swappiness
vm.swappiness is another kernel parameter that can affect database performance. This parameter is used to manage swap behavior (swappiness) (swapping pages in and out of memory) in Linux. The value ranges from 0 to 100. It determines how much memory will be swapped out or in. Zero means swap is turned off, while 100 means aggressive swapping.
You can achieve good performance by setting lower values.
Setting the value to 0 in newer kernels may cause the OOM Killer (out-of-memory process in Linux) to terminate the process. Therefore, it is safe to set the value to 1 if you want to minimize swapping. The default value in Linux is 60. Higher values cause the MMU (memory management unit) to use more swap space than RAM, while lower values keep more data/code in memory.
Lower value is a good bet for improving performance in PostgreSQL.
vm.overcommit_memory / vm.overcommit_ratio
Applications allocate memory and release it when it is no longer needed. But sometimes an application allocates too much memory and doesn't release it. This can trigger the OOM killer. Here are the possible values of the parameter vm.overcommit_memory with a description for each:
- Heuristic overcommit (default); kernel-based heuristic
- Allow overcommit in all cases
- Don't overdo it, don't exceed the overcommit ratio.
Link:
vm.overcommit_ratio — percentage of memory available for overcommitting. A value of 50% in a 2 GB RAM system can allocate up to 3 GB of RAM.
A value of 2 for vm.overcommit_memory provides better performance for PostgreSQL. This value maximizes the use of memory by the server process without significant risk of being killed by the OOM killer. The application can restart, but only within the overcommit range, reducing the risk that the OOM killer will terminate the process. Therefore, a value of 2 offers better performance than the default value of 0. However, reliability can be improved by avoiding memory overload beyond the allowable range, thus eliminating the risk of the process being killed by the OOM killer.
In systems without swap, a vm.overcommit_memory value of 2 can cause issues.
vm.dirty_background_ratio / vm.dirty_background_bytes
vm.dirty_background_ratio — is the percentage of memory occupied by dirty pages that need to be written to disk. The flushing to disk is done in the background. This parameter ranges from 0 to 100; however, a value below 5 may be ineffective and some kernels do not support it. 10 is the default value in most Linux systems. You can improve performance for write-intensive operations with a smaller ratio, which would mean that Linux will flush dirty pages to disk in the background.
You need to set the value vm.dirty_background_bytes based on your disk speed.
There are no 'good' values for these two parameters, as both depend on the hardware. However, setting vm.dirty_background_ratio to 5 and vm.dirty_background_bytes to 25% of the disk speed improves performance by ~25% in most cases.
vm.dirty_ratio / dirty_bytes
This is the same as vm.dirty_background_ratio / dirty_background_bytes, except that flushing occurs during the active session, blocking the application. Therefore, vm.dirty_ratio should be higher than vm.dirty_background_ratio. This ensures that background processes run earlier to avoid potential blocking of the application. You can adjust the difference between these two ratios based on disk I/O load.
Summary
You can adjust other parameters to enhance performance, but the improvements will be minimal and you won't gain much benefit. We must remember that not all parameters apply to every type of application. Some applications perform better when certain parameters are adjusted, while others do not. You need to find the right balance between the configurations of these parameters for the expected workload and application type, while also considering the behavior of the operating system during the setup. Configuring kernel parameters is not as straightforward as adjusting database settings: it is more complicated to provide recommendations here.
Source: habr.com
