Shulou(Shulou.com)06/01 Report--

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.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.