PostgreSQL优化简介

1. 优化简介

PostgreSQL数据库优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，增加系统的反应速度，主要优化的方向可考虑如下几点：

1）服务器硬件方面

2）文件系统方面，例如通过优化文件系统，提高磁盘IO的读写速度；通过优化操作系统调度策略，提高PostgreSQL的在高负荷情况下负载能力等。

3）数据库层面，例如数据库参数、表结构、索引、查询语句等优化方面。

接下来我们详细看一下，整体的优化思路：

2. 详细分析

2.1. 硬件配置建议

1）配置较大的内存。足够大的内存，是提高PostgreSQL数据库性能的方法之一。内存的速度比磁盘I/0快得多，可以通过增加系统的缓冲区容量，使数据在内存中停留的时间更长，以减少磁盘I/0。

2）配置高速磁盘系统，以减少读盘的等待时间，提高响应速度。

3）合理分布磁盘I/O，把磁盘I/O分散在多个设备上，以减少资源竞争，提高并行操作能力。

4）配置多处理器，PostgreSQL是多线程的数据库，多处理器可同时执行多个线程。

2.2. 硬件层推荐

1）CPU：CPU数量、核心等越多，以及频率越高，性能越好。

2）内存：内存频率越高，容量越大，性能越强大，一般推荐使用ECC（带有校验的内存），一般情况下为16G~128G。

3）磁盘：如果是机械硬盘，转速越高，速度越快。一般情况下推荐使用SAS机械盘、PCI-E SSD、FLASH等硬盘。

4）raid分配：建议使用raid5、raid10。

5）网卡：如果是多个网卡，建议将多个网卡进行bonding，最好是主备模式。

2.3. 文件系统层面优化

2.3.1. IO调度工具调优

cfq公平调度算法：为每个进程和线程单独创建一个队列来管理该进程的I/O请求，为这些进程和线程均匀分配I/O带宽，适合通用服务器，是linux系统中默认的调度算法。

noop电梯调度算法：它基于FIFO队列实现，所有I/O请求先进先出，适合SSD。

deadline保障算法：它为读和写分别创建FIFO队列，当内核收到请求时，先尝试合并，不能合并则尝试排序放入队列中，并且尽量保证在请求达到最终期限时进行调度，避免有一些请求长时间不能得到处理。适合虚拟机或I/O压力比较重的场景，例如数据库服务器。

1）查看调度算法：

cat sys/block/sda/queue/scheduler

2）修改调度算法：

echo noop >/sys/block/sda/queue/scheduler

grubby —update-kernel=ALL —args=”elevator=noop”

2.3.2. 预读参数调整

在内存中读取数据比从磁盘中读取要快很多，增加Linux内核预读，对于大量顺序读取的操作，可以减少I/O的等待时间。

如果应用场景中有大量的小文件，过多的预读会造成资源的让费。所以该值应该在实际环境中多次测试。

1）查看磁盘预读扇区：

blockdev —getra dev/sda

2）通过命令设置磁盘预读扇区（可以设置到16384或者更大）

blockdev —setra 16384 /dev/sda

echo 16384 /sys/block/sda/queue/read_ahead_kb

永久生效可以将此命令添加到/etc/rc.local

2.3.3. 透明大页

透明大页在运行时动态分配内存，而运行的内存分配会有延误，对于数据库管理员来讲这并不友好，所以建议关闭透明大页：

1）查看透明大页是否开启（never关闭）

cat /sys/kernel/mm/transparent_hugepage/enabled

2）关闭透明大页：

echo never > /sys/kernel/mm/transparent_hugepage/enabled

echo never > /sys/kernel/mm/transparent_hugepage/defrag

3）永久关闭：

vi /etc/rc.d/rc.local

if test -f /sys/kernel/mm/transparent_hugepage/enabled; then

echo never > /sys/kernel/mm/transparent_hugepage/enabled

fi

if test -f /sys/kernel/mm/transparent_hugepage/defrag; then

echo never > /sys/kernel/mm/transparent_hugepage/defrag

fi

2.3.4. NUMA

NUMA架构会优先在请求线程所在的CPU的local内存上分配空间，如果local内存不足，优先淘汰local内存中无用的页面，这样就会导致每个cpu内存分配不均匀，虽然可以通过配置NUMA的轮询机制缓解，但对于数据库管理员仍然不友好，建议关闭numa。

2.3.5. 内核参数方面

proc/sys/net/core/wmem_max

最大socket写buffer,可参考的优化值:873200

/proc/sys/net/core/rmem_max

最大socket读buffer,可参考的优化值:873200

/proc/sys/net/ipv4/tcp_wmem

TCP写buffer,可参考的优化值: 8192 436600 873200

/proc/sys/net/ipv4/tcp_rmem

TCP读buffer,可参考的优化值: 32768 436600 873200

/proc/sys/net/ipv4/tcp_mem

同样有3个值,意思是:

net.ipv4.tcp_mem[0]:低于此值,TCP没有内存压力.

net.ipv4.tcp_mem[1]:在此值下,进入内存压力阶段.

net.ipv4.tcp_mem[2]:高于此值,TCP拒绝分配socket.

上述内存单位是页,而不是字节.可参考的优化值是:786432 1048576 1572864

/proc/sys/net/core/netdev_max_backlog

进入包的最大设备队列.默认是300,对重负载服务器而言,该值太低,可调整到1000

/proc/sys/net/core/somaxconn

listen()的默认参数,挂起请求的最大数量.默认是128.对繁忙的服务器,增加该值有助于网络性能.可调整到256.

/proc/sys/net/ipv4/tcp_max_syn_backlog

进入SYN包的最大请求队列.默认1024.对重负载服务器,可调整到2048

/proc/sys/net/ipv4/tcp_retries2

TCP失败重传次数,默认值15,意味着重传15次才彻底放弃.可减少到5,尽早释放内核资源.

/proc/sys/net/ipv4/tcp_keepalive_time

/proc/sys/net/ipv4/tcp_keepalive_intvl

/proc/sys/net/ipv4/tcp_keepalive_probes

这3个参数与TCP KeepAlive有关.默认值是:

tcp_keepalive_time = 7200 seconds (2 hours)

tcp_keepalive_probes = 9

tcp_keepalive_intvl = 75 seconds

意思是如果某个TCP连接在idle 2个小时后,内核才发起probe.如果probe 9次(每次75秒)不成功,内核才彻底放弃,认为该连接已失效.对服务器而言,显然上述值太大. 可调整到:

/proc/sys/net/ipv4/tcp_keepalive_time 1800

/proc/sys/net/ipv4/tcp_keepalive_intvl 30

/proc/sys/net/ipv4/tcp_keepalive_probes 3

/proc/sys/net/ipv4/ip_local_port_range

指定端口范围的一个配置,默认是32768 61000,已够大.

net.ipv4.tcp_syncookies = 1

表示开启SYN Cookies。当出现SYN等待队列溢出时，启用cookies来处理，可防范少量SYN攻击，默认为0，表示关闭；

net.ipv4.tcp_tw_reuse = 1

表示开启重用。允许将TIME-WAIT sockets重新用于新的TCP连接，默认为0，表示关闭；

net.ipv4.tcp_tw_recycle = 1

表示开启TCP连接中TIME-WAIT sockets的快速回收，默认为0，表示关闭。

net.ipv4.ip_local_port_range = 1024 65000

表示用于向外连接的端口范围。缺省情况下很小：32768到61000，改为1024到65000。

net.ipv4.tcp_max_syn_backlog = 8192

表示SYN队列的长度，默认为1024，加大队列长度为8192，可以容纳更多等待连接的网络连接数。

读缓存与交换空间：

将该参数设置为0，代表在内存紧张时优先减少RAM里文件系统缓存大小。

vm.swappiness=0

禁用过度分配内存机制（进程申请多少分配多少，不考虑实际内存）

vm.overcommit_memory=2

在以下示例中，当脏页超过vm.dirty_background_ratio=5时，将启动I/O，即开始刷新/写入磁盘.当脏页总数超过vm.dirty_ratio=10时，所有写入将被阻止，直到某些脏页被写入磁盘为止。

vm.dirty_ratio=10

vm.dirty_background_ratio=5

kernel.shmmax = 509898752

kernel.shmall = 124487

计算共享内存段的最大大小，以及所有进程可以使用的共享内存总页数,可以使用如下脚本进行计算

vi 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

内核参数有很多，由于时间原因，本次就分享如上这些，感兴趣的小伙伴可以自行上网查询更多内容。

2.3.6. 其它

除了如上系统优化的内容外，其实还有很多的系统优化项目，例如格式化磁盘时的系统文件格式，selinux以及防火墙的关闭，透明大页的设置，以及文件系统的常用命令等等，这些都可以提高数据库的性能，已经稳定性，感兴趣的同学可以研究一下。

2.4. 数据库优化层面

2.4.1. 数据库版本的选择

说到数据库版本的选择，在这里不得不提到，数据库各个版本之间所增加的一些特性，从而对比到底使用哪个版本的数据库，首先我问先从PG9版本开始聊起：

2.4.1.1. 不同大版本之间的改进

pg9使用继承式分区，pg10实现了声明式分区，pg11完善了功能，pg12提升了性能。

2.4.1.2. pg10版本新特性

1）增加声明式分区

2）pg_stat_activity视图新增信息列，以前版本只是包含用户后台服务进行进程信息

3）增加了安全级别更高的密码验证的方式，SCRAM-SHA-256

4）hash索引可以走流复制，从此可以大胆使用hash索引

5）增加并行功能，引入并行索引，完全支持并行b-tree扫描和bitmap

6）引入Quorum Commit，支持同步复制多个standby

7）提供了逻辑复制功能：发布订阅功能，create publication命令

8）可以把多列组合在一起再创建直方图，让一些关联列的执行计划更准确，可创建跨列统计信息,creat