Share the kernel parameters and configuration scripts to be modified when running PostgreSQL in CentOS, centospostgresql

Share the kernel parameters and configuration scripts to be modified when running PostgreSQL in CentOS, centospostgresql

When deploying PostgreSQL on CentOS, you need to set some operating system parameters. The official documents list some parameters (portals ). In addition, there are some other settings, such as the maximum number of processes allowed by a single user and the maximum number of handles allowed by a single process, otherwise, the system may encounter problems or performance degradation under some conditions. The following describes the system resource restriction class and memory parameter optimization class.

System resource limit

1. maximum number of processes allowed by a single user: the default value in linux is 1024. If the maximum number of connections in PG exceeds 1024, the actual number of connections will be less than 1024 (PG's postmaster process, checkpointer process, bgwriter process, WAL sending process, log process, etc. will occupy several processes, so the number of connections to the customer segment will be less than 1024), excessive connection requests will report an error message of insufficient resources. therefore, to avoid this situation, you need to adjust the number of processes that can be used by linux users of PG, generally through limit. conf.

2. Maximum number of files that can be opened by a single process: the default value in linux is 1024. When SQL statements are complex, many tables are opened, or many partitions are accessed, the number of handles is insufficient.

Memory Parameter Optimization

1. vm. dirty_background_ratio: this parameter controls the number of dirty writes in the system memory. The background process is started to fl the buffer to the disk. the default value is 10%. For large memory machines, for example, if the memory size exceeds 64 GB and the memory size is 10%, writing 6.4G data into the disk at one time will generate a large amount of disk IO in an instant, which will cause the system to lose response, other processes are affected. therefore, we recommend that you set the memory size to 1% for machines with 8 GB or above.

2. vm. dirty_background_types: this parameter is similar to the preceding one, but this parameter can set the absolute value of dirty memory. If one of the two parameters is 0, the other will take effect.

The following is the script for configuring these parameters. Test OK on CentOS 6. x.

Copy codeThe Code is as follows:
# Limit process to 4096 instead 1024, for we may have 1024 + connections
Echo "s soft nproc 4096">/etc/security/lmits. conf
 
# For big query, pg may open more than 1024 files per session
Echo "s hard nofile 65535">/etc/security/limits. conf
Echo "s 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