PostgreSQL-- semaphore and shmall and shmmax related settings

The relationship between shared_buffers and shmmax,shmall,max_connections 1.1.The relationship between shared_buffers and max_connections

Shared_buffers is the query cache. A relatively large setting can improve the efficiency of PostgreSQL. It is generally recommended to set it to 3 connections 4 of the system memory, but it must not be less than 128KB, and not less than 16KB times max _ connections.

1.2.The relationship between shared_buffers and shmmax,shmall

Shared_buffers this parameter cannot exceed the value specified by the operating system parameter SHMMAX

The shmall and shmmax parameters of the kernel are set in the / etc/sysctl.conf file, which can take effect through sysctl-p

SHMMAX= is configured with the maximum memory segment size-> this setting is better than SGA_MAX_SIZE.

The size of the minimum memory segment for SHMMIN=

SHMMNI= the total number of memory segment of the entire system

SHMSEG= the maximum number of memory segment that can be used per process

Shmall is the size of all allowed shared memory, and shmmax is the size allowed for a single segment. These two can be set to 90% of memory. For example, 1610241024102490% = 15461882265 the size of the recording shmall is 15461882265ax 4k (available by getconf PAGESIZE) = 3774873.

Modify / etc/sysctl.conf

Kernel.shmmax=15461882265

Kernel.shmall=3774873

Kernel.msgmax=65535

Kernel.msgmnb=65535

Execute sudo sysctl-p

You can use ipcs-l to see the results. You can see the actual use of ipcs-u

Note: shmmax & shmall changes the shared memory from system V to Posix and mmap starting with PostgreSQL9.3, eliminating the need to set the shmmax parameters of the linux system. 2. The relationship between max_connections and semaphores.

If the max_connections setting is too large and the semaphore is consumed, it may cause PostgreSQL to fail to start.

2.1. Semaphore setting

Semaphore control is set in the / etc/sysctl.conf file and takes effect through sysctl-p, which is used to control kernel semaphores, which are the methods used by System VIPC for interprocess communication.

Kernel.sem = 250 512000 20482.2, query current semaphore usage

View current semaphore usage

2.3. View the current settings:

Cat / proc/sys/kernel/sem

These four numbers are: SEMMSL,SEMMNS,SEMOPM,SEMMNI

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

SEMMNS: kernel parameter that controls the maximum number of semaphores that can be used within the system.

The SEMOPM:semop () function (kernel function, which is used to manipulate semaphores) calls the largest semaphore in one semaphore set of locking operations at a time.

SEMMNI: the maximum number of semaphore sets in the kernel.

SEMMNS=SEMMSL*SEMMNI

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

2.4. Semaphore setting calculation

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 ((1000334)) / 16) = 63, because considering the use of other processes, it is generally set to 633250088

The SEMMSL requirement is greater than 17, and the default is 250.

SEMOPM=SEMSL=250

SEMMNS=SEMMNIMSMMSL=88250=22000

Add to the / etc/sysctl.conf file:

Kernel.sem=250 22000 25088 run: sysctl-p takes effect

Make the configuration effective

After setting up, if you start the database and still report the same error, you can restart the operating system and try again.