Kernel parameters and configuration script sharing that need to be modified to run PostgreSQL in CentOS

When PostgreSQL is deployed on CentOS, some operating system parameters need to be set, some of which are listed on the official documentation (portal). In addition to these, there are other Settings, such as the maximum number of processes allowed for a single user, the maximum number of handles for a single process, and so on, which generally need to be adjusted, otherwise the system will have problems under some conditions or performance degradation. The following is illustrated from the system resource constraint class and memory parameter optimization class.

system resource limitation class

1. A single user to allow the maximum number of processes: linux system default is 1024, if the maximum number of connections PG more than 1024, the actual number of connections will be less than 1024 (PG postmaster process, checkpointer process, bgwriter process, WAL routing process, etc. Will take up a few log process, so the number of connections to the customer segment is less than 1024), more than the connection requests will be submitted to the resource not enough error information. So, in order to avoid this kind of situation, need to adjust the PG linux user can use Number of processes, typically configured through limit.conf.

2. The maximum number of files a single process can open :linux is 1024 by default, and when SQL is complex, opens many tables, or accesses many partitions, the handle count is insufficient.

memory parameter optimization class

1. vm. dirty_background_ratio: this parameter control when and how the proportion of system memory write dirty, will start the daemons will buffer brush to disk. The default is 10%, and for large memory machine, such as more than 64 G, 10% of the memory of 6.4, a 6.4 G data to disk, can produce a large amount of disk IO moment, make the system response, affect other processes. So, generally more than 8 G memory machine, recommend set to 1%

Es54en.dirty_background_types: this parameter is similar to the one above, except that it sets the absolute value of memory corruption

The following is a script that configures these parameters and tests OK.

#limit process to 4096 instead 1024,for we may have 1024+ connections

echo "postgres        soft    nproc           4096" >> /etc/security/lmits.conf

 

#for big query,pg may open more than 1024 files per session

echo "postgres        hard    nofile          65535" >> /etc/security/limits.conf

echo "postgres        soft    nofile          65535" >> /etc/security/limits.conf

 

#default is 10% of memory,to smooth the io peek value,

#set this to tune background process flush buffer more frequently

echo "vm.dirty_background_ratio=0" >> /etc/sysctl.conf

echo "vm.dirty_background_bytes=1024000000" >> /etc/sysctl.conf

 

#make the sysctl.conf setting take effect

sysctl -p

 

#make limit to take effect

/etc/init.d/sshd restart