PostgreSQL settings max_connections too large to start (reprint)

This essay is reproduced from https://my.oschina.net/u/2381678/blog/552346.

The maximum number of links needs to be adjusted in the production environment PostgreSQL, but cannot be started after adjustment

The error is that the configuration of the semaphore set and semaphore in the kernel is too small. The semaphore is in the kernel, mainly to solve the inter-process synchronous, asynchronous lock problem, because each link of PostgreSQL is a process, so need more locks to use. To view the method:

These four numbers are: Semmsl,semmns,semopm,semmni

SEMMSL: The kernel parameter that controls the maximum number of signals per semaphore set.

Semmns: Core parameters, the maximum number of semaphores to be used within the control system.

The Semopm:semop () function (a kernel function, used to manipulate semaphores) is the maximum semaphore in a semaphore set that is invoked every time a lock can operate.

Semmni: The maximum number of semaphore sets in the kernel.

Semmns=semmsl*semmni

SEMOPM=SEMMSL, these two parameters are generally set to the same.

For PostgreSQL databases:

Semmni >= ceil ((max_connections + autovacuum_max_workers + 4)/16)

SEMMSL >= 17

Suppose a PG library is set up as follows:

max_connections=1000, autovacuum_max_workers = 3,

The settings for these parameters are:

Semmni = Ceil ((1000+3+4))/16) = 63, because other processes are used, generally set to 63+25=88

SEMMSL requirements greater than 17, keep the default 250

semopm=semsl=250

semmns=semmni*msmmsl=88*250=22000

In the/etc/sysctl.conf file, add:

KERNEL.SEM=250 22000 250 88

Run: sysctl-p

Make configuration effective

After the setting is complete, you can restart the operating system if you start the database or report the same error. (For what reason, I do not know, if there is any understanding of the great God, please enlighten)

PostgreSQL settings max_connections too large to start (reprint)