PostgreSQL kernel parameters and configuration script sharing that need to be modified in CentOS _postgresql

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

System Resource Constraint Class

1. Maximum number of processes allowed by a single user: The Linux system defaults to 1024, if the maximum number of PG connections exceeds 1024, the actual number of connections will be less than 1024 (PG postmaster process, Checkpointer process, bgwriter process, Wal send the process, the log process, etc. will take up several processes, so the number of connections to the customer segment will be less than 1024, more than the connection request will be reported insufficient resources error messages. So to avoid this, you need to adjust the number of processes that are available to the Linux user of PG, It is typically configured via limit.conf.

2. Maximum number of files a single process can open: Linux defaults to 1024, when SQL is complex, open many tables, or access many partitions, the number of handles is not enough error.

Memory parameter Optimization class

1.vm.dirty_background_ratio: This parameter controls how much of the system memory is dirty when it starts the background process to brush the buffer to disk. The default is 10%, for machines with large memory, such as 6.4 of memory over 64g,10%, Writing 6.4G data to disk at a time can result in a large amount of disk IO, causing the system to lose its response and affect other processes. So the general 8G above the memory machine, the recommended setting is 1%.

2.vm.dirty_background_types: This parameter is similar to the above, except that this parameter can set the absolute value of the memory being dirty. One of the two arguments is 0, and the other one works.

Here is a script that configures these parameters and tests OK on CentOS 6.x.

Copy Code code as follows:

#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 could 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