{"id":35920,"date":"2019-10-31T22:07:35","date_gmt":"2019-10-31T19:07:35","guid":{"rendered":"https:\/\/prohoster.info\/blog\/nastrojka-parametrov-yadra-linux-dlya-optimizatsii-postgresql\/"},"modified":"2019-10-31T22:07:35","modified_gmt":"2019-10-31T19:07:35","slug":"nastrojka-parametrov-yadra-linux-dlya-optimizatsii-postgresql","status":"publish","type":"post","link":"https:\/\/prohoster.info\/en\/blog\/administrirovanie\/nastrojka-parametrov-yadra-linux-dlya-optimizatsii-postgresql","title":{"rendered":"Configuring Linux Kernel Parameters for PostgreSQL Optimization","gt_translate_keys":[{"key":"rendered","format":"text"}]},"content":{"rendered":"

\"Configuring 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.<\/p>\n

SHMMAX \/ SHMALL<\/h2>\n

\nSHMMAX<\/b> 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.<\/p>\n

Until version 9.3, SHMMAX was the most critical kernel parameter. The value of SHMMAX is set in bytes.
\n<\/a><\/noindex>
\nSimilarly, SHMALL<\/b> is another kernel parameter used to determine
\nthe total number of pages of shared memory across the system. To view the current values of SHMMAX, SHMALL, or SHMMIN, use the command ipcs<\/i>.<\/p>\n

SHM* Details \u2014 Linux<\/b><\/p>\n

$ ipcs -lm\n\n------ Shared Memory Limits --------\nmax number of segments = 4096\nmax seg size (kbytes) = 1073741824\nmax total shared memory (kbytes) = 17179869184\nmin seg size (bytes) = 1<\/code><\/pre>\n
\nSHM* Details \u2014 MacOS X<\/b><\/p>\n
$ ipcs -M\nIPC status from  as of Thu Aug 16 22:20:35 PKT 2018\nshminfo:\n\tshmmax: 16777216\t(max shared memory segment size)\n\tshmmin:       1\t(min shared memory segment size)\n\tshmmni:      32\t(max number of shared memory identifiers)\n\tshmseg:       8\t(max shared memory segments per process)\n\tshmall:    1024\t(max amount of shared memory in pages)\n<\/code><\/pre>\n
\nPostgreSQL uses System V IPC<\/b> 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.<\/p>\n
Possible Misconfiguration Errors<\/h3>\n
\nIf SHMMAX is misconfigured, you may encounter errors when trying to initialize a PostgreSQL cluster with the command initdb<\/i>.<\/p>\n
initdb Failure<\/b>
\nDETAIL: Failed system call was shmget(key=1, size=2072576, 03600). <\/p>\n
HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.\u00a0 
\nYou can either reduce the request size or reconfigure the kernel with larger SHMMAX. To reduce the request size (currently 2072576 bytes), 
\nreduce PostgreSQL's shared memory usage, perhaps by reducing shared_buffers or max_connections. <\/p>\n
If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter,
\nin which case raising the request size or reconfiguring SHMMIN is called for. <\/p>\n
The PostgreSQL documentation contains more information about shared memory configuration. child process exited with exit code 1<\/code><\/p>\n
Similarly, you may encounter an error when starting the PostgreSQL server using the command pg_ctl<\/i>.<\/p>\n
pg_ctl Failure<\/b>
\nDETAIL: Failed system call was shmget(key=5432001, size=14385152, 03600).<\/p>\n
HINT: This error usually means that PostgreSQL's request for a shared memory segment exceeded your kernel's SHMMAX parameter.<\/p>\n
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.<\/p>\n
If the request size is already small, it's possible that it is less than your kernel's SHMMIN parameter, 
\nin which case raising the request size or reconfiguring SHMMIN is called for.<\/p>\n
The PostgreSQL documentation contains more information about shared memory configuration.<\/code><\/p>\n
Understanding the differences in definitions<\/h3>\n
\nThe definitions of SHMMAX\/SHMALL differ slightly in Linux and MacOS X:<\/p>\n