PostgreSQL在Linux下参数配置详解

1. 操作系统的参数

1.1 禁止SELinux

在打开SELinux时，有时会导致一些问题，所以为了方便一般会禁止SELinux。

先用命令sestatus检查SELinux是否已被打开，方法如下：

[root@pg01 ~]# sestatus
SELinux status:                 enabled
SELinuxfs mount:                /sys/fs/selinux
SELinux root directory:         /etc/selinux
Loaded policy name:             targeted
Current mode:                   enforcing
Mode from config file:          enforcing
Policy MLS status:              enabled
Policy deny_unknown status:     allowed
Max kernel policy version:      28

上面中的SELinux status: enabled说明selinux是打开的。

关闭方法是修改/etc/selinux/config：

SELINUX=disabled

修改上面的配置文件，需要重启才生效。我们可以先设置上，整个装完PostgreSQL之后，可以把机器统一重启一次。

也可以临时关闭，但临时关闭之后，重启之后就失败了，临时关闭的方法为执行下面的命令：

setenforce 0

1.2 关闭防火墙

一般我们会关闭防火墙，这样配置PostgreSQL起来方便一些，因为如果不关闭防火墙或在防火墙上做一些特别的配置，外面的机器有可能连接不上。

Linux下有两种防火墙iptables和firewalld

用下面的命令检查iptables是否打开了：

[root@pg01 ~]# systemctl status iptables
● iptables.service
   Loaded: not-found (Reason: No such file or directory)
   Active: inactive (dead)

如果不是禁止，则用下面的命令禁止：

systemctl disable iptables

看防火墙是否正在运行，如果是则关闭：

systemctl status firewalld

关闭和禁止firewalld的方法如下：

systemctl stop firewalld
systemctl disable firewalld

sysctl.conf中的配置

PostgreSQL在9.3版本之前与oralce数据库一样，都是使用“System V”类型的共享内存，但到9.3版本之后，只使用少量的“System V”类型的共享内存，大部分使用“mmap（）”方式的共享内存。

所以在PostgreSQL9.3之前的版本需要配置“System V”类型共享内存的操作系统参数，可以用下面的脚本shmsetup.sh脚本生成共享内存的配置：

#!/bin/bash
# simple shmsetup script
page_size=`getconf PAGE_SIZE`
phys_pages=`getconf _PHYS_PAGES`
shmall=`expr $phys_pages / 2`
shmmax=`expr $shmall \* $page_size`
echo kernel.shmmax = $shmmax
echo kernel.shmall = $shmall

这个脚本的使用方法如下：

shmsetup.sh >> /etc/sysctl.conf

最终我们的/etc/sysctl.conf的示例文件如下：

vm.swappiness=0
vm.overcommit_memory=2
vm.overcommit_ratio=85
vm.dirty_background_ratio=1
vm.dirty_ratio=2
kernel.sem=20 13000 20 650
kernel.sysrq = 1
kernel.core_uses_pid = 1
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.msgmni = 2048
net.ipv4.tcp_syncookies = 1
net.ipv4.ip_forward = 0
net.ipv4.conf.default.accept_source_route = 0
net.ipv4.tcp_tw_recycle = 1
net.ipv4.tcp_max_syn_backlog = 4096
net.ipv4.conf.all.arp_filter = 1
net.ipv4.ip_local_port_range = 1025 65535
net.core.netdev_max_backlog = 10000
net.core.rmem_max = 2097152
net.core.wmem_max = 2097152

参数解释如下：

• vm.swappiness=0 ：这是设置为尽量不用swap空间。数据库主机如果发生swap了，通常就可用了，所以此参数设置为0，即尽量不用swap空间。
• vm.overcommit_memory=2 ：禁止内存分配的超分配， 这样做可以让OOM发生的概率变低很多。
• vm.overcommit_ratio=90 ：这个值要根据你机器的实际内存情况设置，一般我们为了避免OOM，允许程序可以分配的内存不要超过总物理内存，当vm.overcommit_memory=2时，程序可以分配的内存= 总物理内存 × vm.overcommit_ratio/100 + swap_size，如32G的机器，有swap大小为4G，则可以把vm.overcommit_ratio设置为85，则：32×85/100 + 4 = 31.2G，这样算出来的内存没有超过总物理内容。
• vm.dirty_background_ratio=1：默认的这个值比较大，我们设置小一些，这样文件系统的脏数据超过1%，就会后台刷脏数据，这样可以避免数据库的性能大起大落。对于内存越大的机器此值应该设置的越小一些，这样每次让每次刷的脏数据不会太多。如我们需要每次脏数据超过512M，就要刷脏数据时，如果机器是48G，这时这个值设置为1，这样48G×1%=491M时就会脏数据，这样基本还可以。
• vm.dirty_ratio=2：与上面类似，这里是当脏数据超过2%，强制刷脏数据。

其中kernel.sem对应四个参数：

• SEMMSL(信号集的最大信号量数）：需要大于17，我们取20即可，如果这台机器上还安装有Oracle，需要把此参数设置为250
• SEMMNS（整个系统范围内的最大信息量数）：SEMMSL × SEMMNI = 650×20 = 13000
• SEMOPM（semop函数在一次调用中所能操作一个信号量集中最大的信号量数）: 与SEMMSL相同，为20
• SEMMNI（信号量集的最大数目）：ceil(max_conntions + autovacuum_max_workers + 4)/16，如果对于10000个连接，而autovacuum_max_workers=3，所以SEMMNI最少要大于626，我们取650

limits.conf

/etc/security/limits.conf:

* soft nofile 65536
* hard nofile 65536
* soft nproc 131072
* hard nproc 131072

以及/etc/security/ limits.d/，通常此文件的内容如下：

* soft nproc 4096
root soft nproc unlimited

需要把第一行“* soft nproc 4096”改成：

* soft nproc 131072
root soft nproc unlimited

然后su - postgres用ulimit -a命令检查 ：

[postgres@pg01 ~]$ ulimit -a
core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 513410
max locked memory       (kbytes, -l) 64
max memory size         (kbytes, -m) unlimited
open files                      (-n) 65536
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 8192
cpu time               (seconds, -t) unlimited
max user processes              (-u) 4096
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

主要看“open files”与“max user processes”
有时上面的参数不生效，需要重启sshd服务或重启机器。

XFS的设置

格式化XFS建议用下面的参数：

mkfs.xfs -f -i size=512 -l size=128m,lazy-count=1 -d agcount=16 -L pgdata01 /dev/sdb1

把上面的参数解释一下：

• -i size=512 ： 默认的值是256KB，这里的设置为512是为了selinux的，这个设置针对inode size，selinux使用xfs的Extend Attribute，首先要写到inode中，如果容量不够就会写到block中，会损失性能，默认是256KB的时候就不够，所以设置为512。通常我们不会打开selinux，不过为了保险期间，还是把此值设置为512吧。因为大家对linux系统的安全性还是挺信任的，所以通常把selinux给关掉的。

• -l size=128m ：注意是小写的m，不是大写的。这个参数值指定日志管理区块(log section)的大小，这个值默认是10m。我们把这个参数成128m，可以显著的提高xfs文件系统删除文件的速度，当然还有其它方面也会提升性能能，如拷贝文件的速度。 指定较大的值会需要较多的内存，如果内存很少的机器可以使用默认值。

• -l lazy-count=1 ： This changes the method of logging various persistent counters in the superblock. Under metadata intensive workloads, these counters are updated and logged frequently enough that the superblock updates become a serialisation point in the filesystem. The value can be either 0 or 1.
  With lazy-count=1, the superblock is not modified or logged on every change of the persistent counters. Instead, enough information is kept in other parts of the filesystem to be able to maintain the persistent counter values without needed to keep them in the superblock. This gives significant improvements in performance on some configurations. The default value is 0 (off) so you must specify lazy-count=1 if you want to make use of this feature.

• -d agcount=16 ：默认值是根据容量自动设置的。可以设置成1/2/4/16等等。这个参数指定xfs的储存区群组(英文称为allocation group)中存储区组的个数。在XFS中把存储空间分成多个存储区组(allocation group)，每个储存区群组中都包含了整个文件系统的superblock、剩余空间的管理机制、inode的分配与追踪，所以可以在多个储存区群组里面并行分配block和inode，所以对并行的写I/O有较大帮助，所以我们把此值设置高，这样对并发的写有优势。并发写的场景：如单机多实例，或者Greenplum，都是典型的应用场景。当然这个参数调大大了，可能会多占些CPU，值越小，占用率越低。每个存储区载最小64MB，最大1TB，如果是很大的空间，如超过16TB，这个值需要调整的更大。

如果是做了Raid的硬盘，也可以加条带大小的参数：

• su=8k: 指定条带单元(stripe unit）的大小，通常是与数据库的数据块大小。
• sw=value：指定raid的条带宽度，根据实际情况设置，如果是Raid5设置为 Raid5中实际硬盘数-1，如果是Raid10，则为实际硬盘数/2，如8块硬盘Raid10，则此值设置为：4。

注意，条带还有两个参数sunit和swidth，实际上这两个参数与su和sw类似，只是单位不同，sunit是扇区大小，sunit=16等同于su=8k，当su=8k时，swidth=64等同于sw=4，4块盘的条带大小为 4×8k=32k，即64个扇区。

通常的mount参数如下：

defaults,noatime,nodiratime,noikeep,nobarrier,logbufs=8,logbsize=32k,allocsize=4M,attr2,inode64,swalloc

需要把这些mount参数配置到/etc/fstab中，如下所示：

echo LABEL=/pgdata01 /data xfs defaults,noatime,nodiratime,nobarrier,logbufs=8,logbsize=32k,allocsize=4M,attr2,largeio,inode64,swalloc 0 0 >>/etc/fstab

如果是SSD硬盘，增加了参数“discard,ikeep”：

defaults,noatime,nodiratime,discard,ikeep,nobarrier,allocsize=4M,attr2,largeio,inode64,swalloc

把xfs的一些mount参数解释一下：

• noatime、nodiratime：不实时更新文件的访问时间戳，这样会减少一些写。在Linux下只要设置 noatime 就可以了，不必再设置 nodiratime
• nobarrier： 通常我们的数据库机器都是带有不丢失缓存的Raid卡，这是就可以把nobarrier打开。文件系统barrier是作为一个增强的安全特性，是一种保证文件系统一致性的机制， BarrierI/O的目的是使其之前的I/O在其之前写入存储介质，之后的I/O需要等到其写入完成后才能得到执行。在文件系统中需要保证数据块被写入之后元数据才会被写入，所以XFS和ext4都使用IO层的barrier的机制，保证文件系统数据的一致性。注意：当我们使用了软raid、LVM或multipath，则barrier功能是不生效的，即相当于是nobarrier的情况。
• discard：即Linux的trim指令，在SSD上启用，方便SSD回收无用的空间。
• ikeep：与其他文件系统不一样，XFS不是在mkfs时分配inode节点，而是动态分配的，当设置为noikeep时，会自动回收inode节点的空间，让磁盘上的碎片少一些，但会让性能变低一点，对SSD来说，完全不必要，所以要设置ikeep。
• inode64：现在都是64位操作系统了，当然要设置inode64了，对于Linux内核v3.7及之后，inode64就是默认值了，即不用设置，也是这个了。
• logbufs=8 ：设置内存日志缓冲区的数量，有效数字范围为2-8。默认值就是8，所以这个参数实际上可以不用设置
• logbsize=32k：指定每个内存日志缓冲区的大小。默认值就是32k，通常也是够了，对于一些内存大的机器，可以设置为256k。
• allocsize=4M： 可以设置值的范围为4K~1G，文件在的扩展时，每次扩展的空间至少要扩展多大，这时设置的较大，这样在批量插入时会快一些。所以如果想让批量快一些，可以把此值调的大一些，如32M甚至128M。
• attr2：attr2为“Ondisk format extension for extended attributes” 的简称，让文件系统中的文件的属性可以支持扩展属性。SELinux要求使用这个。
• swalloc ：文件扩展大小时，扩展的大小会与条带的大小对齐(data allocations will be rounded up to stripe width boundaries when the current end of file is being extended and the file size is larger than the stripe width size.)

块设备的IO调度策略

数据库通常建议使用deadline，要让deadline生效的最简单方法是对PostgreSQL数据库使用的硬盘做下面的设置：

echo deadline > /sys/block/sda/queue/scheduler
echo deadline > /sys/block/sdb/queue/scheduler
echo deadline > /sys/block/sdc/queue/scheduler

但上面的设置，机器重启之后就失效，所以需要把上面的内容加到/etc/rc.local中，以便每次重启能生效。

当然在CentOS6.X中和CentOS7.X中我们可以在全局范围内把磁盘的默认调度策略就改成deadline，这种方法是我们强烈推荐的，方法如下：

如果是CentOS6.X在/boot/grub/grub.conf中加入elevator=deadline，如下所示：

kernel /vmlinuz-2.6.18-274.3.1.el5 ro root=LABEL=/ elevator=deadline crashkernel=128M@16M quiet panic=30 transparent_hugepage=never
initrd /initrd-2.6.18-274.3.1.el5.img

如果是CentOS7.X，执行下面命令：

grubby --update-kernel=ALL --args="elevator=deadline"

而在centos7.2以上的版本，不需要设置上面的内容，默认已是“deadline”策略了。

禁止透明大页

Linux下的透明大页不够成熟，通常需要禁止，否则会导致数据库的性能变低或反应变慢。

查看是否开启了透明大页：

cat /sys/kernel/mm/*transparent_hugepage/enabled

CentOS7.X下禁止透明大页：

grubby --update-kernel=ALL --args="transparent_hugepage=never"

安装postgresql9.6

yum install postgresql96-contrib.x86_64 postgresql96-libs.x86_64 postgresql96-plperl.x86_64 postgresql96-plpython.x86_64 postgresql96-libs.x86_64 postgresql96-server.x86_64 postgresql96.x86_64 postgresql96-devel.x86_64

数据库的配置

典型的数据库配置：

listen_addresses = '*'
max_connections = 10000
tcp_keepalives_idle = 5
tcp_keepalives_interval = 5
tcp_keepalives_count =3
shared_buffers = 32GB
maintenance_work_mem = 256M
bgwriter_delay = 50ms
wal_level = logical
max_wal_size = 4GB
min_wal_size = 160MB
max_wal_senders = 10
wal_keep_segments = 80
hot_standby = on
hot_standby_feedback = on
logging_collector = on
log_min_duration_statement = 10000
track_io_timing = on
track_functions = all
autovacuum_vacuum_cost_delay = 1ms
statement_timeout = 0
lock_timeout = 60000

有时虽然设置autovacuum_vacuum_cost_delay为1ms，但有些操作系统会延迟10ms，这时需要加大“autovacuum_vacuum_cost_limit”的值，默认为200

默认情况下9.6下logging_collector已经设置为on，不需要设置了。

也可以安装一些常用的插件如pg_stat_statement、pg_buffercache：

CREATE EXTENSION pg_buffercache;
CREATE EXTENSION pg_stat_statements;