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）可以把多列组合在一起再创建直方图，让一些关联列的执行计划更准确，可创建跨列统计信息,create statistics

2.4.1.3. 并行查询功能

pg9.6开始支持并行查询功能，但是比较弱，pg10之后功能大大增强，引入来并行索引：

postgres=# select name,setting from pg_settings where name like ‘%parallel%’;

name | setting

—————————————————+————-

enable_parallel_append | on

enable_parallel_hash | on

force_parallel_mode | off

max_parallel_maintenance_workers | 2

max_parallel_workers | 8

max_parallel_workers_per_gather | 2

min_parallel_index_scan_size | 64

min_parallel_table_scan_size | 1024

parallel_leader_participation | on

parallel_setup_cost | 1000

parallel_tuple_cost | 0.1

(11 rows)

2.4.1.4. Quorum Commit

pg10之前版本只能有的一台服务器充当同步节点，现在引入仲裁提交，支持同步多个复制standby，通过仲裁配置参数synchronous_standby_names进行设置，支持FIRST和ANY两种模式指定同步复制节点

postgres=# select * from pg_settings where name=’synchronous_standby_names’;

-[ RECORD 1 ]—-+————————————————————————————————————————

name | synchronous_standby_names

setting |

unit |

category | Replication / Master Server

short_desc | Number of synchronous standbys and list of names of potential synchronous ones.

extra_desc |

context | sighup

vartype | string

source | default

min_val |

max_val |

enumvals |

boot_val |

reset_val |

sourcefile |

sourceline |

pending_restart | f

2.4.1.5. 逻辑复制功能

pg10原生提供了逻辑复制的功能，实现了逻辑发布和订阅的功能

pg10不支持truncate的同步，导致10版本中作为逻辑同步的表不能做truncate。从pg11之后可以支持truncate功能

2.4.1.6. pg11版本新特性

1）声明式分区表功能大大增加，分区表可以加主键，外键，索引，支持hash分区

2）JIT（即时编译功能），提供一些批计算如sum的性能，通常提升在10%左右

3）hash join支持并行

4）支持存储过程（create procedure），并可以存储过程中嵌入事务

5）存储过程中可以加commit和rollback事务

6）b-tree索引支持并行create index

7）create index使用include可以非键列放到索引中，以便covering index而不必要回表

8）增加非空可瞬间完成，不需要rewrite表

9）vacuum增强：空闲空间可以更快的被重用，跳过一些没有必要的索引扫描

10）提升了多个并发事务commit的性能

11）逻辑复制支持truncate的同步

12）以前触发的toast的压缩都需要插入的数据大于1996个字节时才会触发，这个1996字节是固定的，不能改，现在给表加了存储参数toast_tuple_target，可以设置更新的值就可以触发toast的压缩机制

13）允许在initdb时改变WAL文件大小，以前是需要重新编译程序才能改变WAL文件的大小

14）现在在WAL日志中会把使用的部分填0，这样可以额提高压缩率

15）支持可通过grant权限下放系统权限

2.4.1.7. JIT

JIT(Just in time):即时编译，程序在运行过程中即时进行编译，可以把编译的中间代码缓存或者优化。

postgresql代码中都是通用的逻辑实现的，从而导致在执行过程中可能造成大量不必要的跳转和代码分支执行，继而造成大量不必要的指令执行，造成CPU的压力。而使用JIT技术可以将代码扁平化（inline）执行，直接调用对应的函数，而且如果知道具体输入，还可以直接删除掉很多间接代码的执行。

pg11版本实现了基于LLVM(Low Level Virtual Machine)的JIT

在pg11版本中JIT默认不开启的，PG12版本开启JIT是默认开启的：

pg11版本下的JIT参数

postgres=# select version();

version

-————————————————————————————————————————————————————

PostgreSQL 11.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-36), 64-bit

(1 row)

默认是不开启，需要在编译的时候使用—with-llvm参数开启

postgres=# select name,setting from pg_settings where name like ‘%jit%’;

name | setting

————————————-+————-

jit | off

jit_above_cost | 100000

jit_debugging_support | off

jit_dump_bitcode | off

jit_expressions | on

jit_inline_above_cost | 500000

jit_optimize_above_cost | 500000

jit_profiling_support | off

jit_provider | llvmjit

jit_tuple_deforming | on

(10 rows)

2.4.1.8. procedure中使用commit，rollback

pg11之前版本，function和procedure是一回事，但是从pg11开始就变得不一样了

pg11开始procedure的调用一定要使用call()函数调用，不能再使用select()函数调用了

pg11开始存储过程中可以调用commit和rollback功能

2.4.1.9. 并行创建索引

pg11开始支持并行create index，默认开启，但是仅支持B-tree索引，其它类型索引现阶段不支持并行创建

postgres=# show max_parallel_maintenance_workers;

max_parallel_maintenance_workers

-————————————————-

2

(1 row)

2.4.1.10. 快速增加包含非空默认值的列

pg11之前增加的一个包含非空默认值的字段，将会导致表数据的重新写入，为每一行添加该字段，并且填充默认值。对于大表则非常耗时

pg11开始增加非空字段则可以快速完成，只要通过在表pg_attribute中添加两个字段attfdwoptions和attmissingval。如果我非空行则attmissingval值为true,则说明不需要重写表。一旦表被重写，如执行vacuum full table操作，相应的attfdwoptions和attmissingval属性中值就会被清除，因为系统部在需要这些值。

2.4.1.11. grant权限下放四个系统函数和新增默认角色

下放的四个系统函数

pg_ls_dir()

pg_read_file()

pg_read_binary_file()

pg_stat_file()

新增默认角色

pg_read_server_files:具有数据库服务端文件的读权限

pg_write_server_files:具有数据库服务端文件的写权限

pg_execute_server_program:具有执行数据库服务端的程序权限

2.4.1.12. pg12新特性

1）分区表DML性能增强，尤其是在分区数量比较多的情况下更为显著

2）pg12开始取消recovery.conf,把配置项移动到postgresql.conf中

3）减少了在创建GIST，GIN，SP-GIST索引的WAL日志量

4）pg12默认开启JIT

5）在btree索引中减少了不必要的多版本数据，提升了性能

6）vacuum增加了选项truncate，有可能不需要vacuum full也能释放部分空间到操作系统

max_wal_senders连接数从max_connections剥离

7）支持在线重建索引，reindex concurrently

8）提升了position函数的性能

9）serializable事务隔离级别，可以并行查询

2.4.1.13. btree索引优化

pg12中将数据的物理地址ctid和索引存储在一起，成为索引的一部分。主键index scan速度更快，同时节省了index存储空间

2.4.1.14. 新增视图和函数

pg_stat_progress_create_index:查看当前正在创建的索引进度，已经执行的数量块数量，已经执行的行数量，使用/等待锁的情况

pg_stat_progress_cluster:查看当前vacuum full/cluster进度，数据块读写数量，数据条目读写数量

pg_ls_archive_statusdir():列出归档状态文件内容

pg_promote():用于备库提升为主库

2.4.1.15. reindex concurrently

PG12之前版本重建索引通常是首先创建一个索引列相同的索引，之后删除旧的索引。pg12中reindex命令新增concurrently选项，从而支持在线重建索引。

reindex concurrently持有的是表级锁为show update exclusive锁，命令执行过程中不会阻塞表上的DML操作。相反如果没有concurrently则表级锁为share锁，会阻塞DML操作、

reindex concurrently原理步骤：

在线新建一个索引，相当于执行 CREATE INDEX CONCURRENTLY newindex ON table;

重命名新索引，相当于执行 ALTER INDEX newindex TO oldinex;

调整相关依赖关系

删除老索引，相当于执行 DROP INDEX CONCURRENTLY tmpindex

PS: 如果reindex concurrently命令执行失败，则状态标识为不可用”invalid”，之后只能通过reindex index命名重建，因此会阻塞表上的DML操作。

2.4.1.16. pg13新特性

1）分区表功能进一步加强

2）btree索引优化(引入deduplication技术)

3）增量排序(incremental sorting)

4）改变流复制的配置可以不用重启数据库

5）聚合时使用hash算法可以ID使用磁盘做溢出存储

6）pg_stat_statements插件增加了选项可以跟踪sql的planning time，而不仅仅是执行时间

7）支持并行vacuum

2.4.1.17. 分区表性能

1). 逻辑复制支持分区

2). 更多的一些情况下可以对分区进行裁剪和智能join

3). 分区表可以支持before trigger（不允许改变插入数据的目标分区）

4). 可以显示地发布分区表，自动发布所有分区

5). 从分区表中添加/删除分区将自动从发布中添加/删除

2.4.1.18. btree索引优化

btree索引引入索引项去重技术，Deduplication。表中的列如果不是唯一的，可能会有很多相同的值，对应的B树索引也会有很多重复的索引记录。在pg13中索引借鉴了GIN索引的做法，将相同的key指向的对应行的ctid链接起来

2.4.1.19. vacuum并行

在之前的版本中，每个表的vacuum操作并不能并行，当表比较大的时候，vacuum时间会很长，pg13支持对索引的并行vacuum，但存在较多限制

1）目前仅限于索引，每个索引可以分配一个vacuum worker

2）不支持在加上full选项后使用

3）只有在至少有2个以上索引的表上使用parallel选项才有效

2.4.1.20. 数据库管理上优化

1）reindexdb命令增加–jobs选项，允许用户并行重建索引

2）引入了”可信插件”的概念，它允许超级用户指定一个普通用户可以安装的扩展

3）增强数据库状态的监控，跟踪WAL日志的使用统计，基于流式备份的进度和analyze指令的执行进度

4）支持pg_basebackup命令生成辅助清单文件，可以使用pg_verifybackup工具来验证备份的完整性

5）可以限制为流复制槽所保留的WAL空间

6）可以为standby设置临时流复制槽

7）pg_dump命令新增–include-foreign-data参数，可以实现在导出数据时导出外部数据封装器所引用的其他服务器上的数据

8）pg_rewind命令不仅可以在宕机后自动恢复，并且可以通过–write-recover-conf选项来配置pg备库，支持在目标实例上使用restore_command来获取所需的WAL日志

2.4.1.21. 最终结论

如上我们从pg9到pg13简单了解了一下pg在版本的迭代中，增加了一些非常实用的功能，而且在每次迭代中其稳定性以及性能上都做了一些提升，所以在版本的选择方面建议使用稳定较新的pg版本。

2.4.2. 数据库参数优化

2.4.2.1. 数据库参数调优

2.4.2.2. 其它参数优化

synchronous_commit

此参数的作用为在向客户端返回成功状态之前，强制提交等待WAL被写入磁盘。这是性能和可靠性之间的权衡。如果应用程序被设计为性能比可靠性更重要，那么关闭synchronous_commit。这意味着成功状态与保证写入磁盘之间会存在时间差。在服务器崩溃的情况下，即使客户端在提交时收到成功消息，数据也可能丢失。

fsync

强制把数据同步更新到磁盘,如果系统的IO压力很大，把改参数改为off

在fsync打开的情况下，优化后性能能够提升30%左右。因为有部分优化选项在默认的SQL测试语句中没有体现出它的优势，如果到实际测试中，提升应该不止30%。

测试的过程中，主要的瓶颈就在系统的IO，如果需要减少IO的负荷，最直接的方法就是把fsync关闭，但是这样就会在掉电的情况下，可能会丢失部分数据。

commit_delay

事务提交后，日志写到wal log上到wal_buffer写入到磁盘的时间间隔。需要配合commit_sibling。能够一次写入多个事务，减少IO，提高性能

commit_siblings

设置触发commit_delay的并发事务数，根据并发事务多少来配置。减少IO，提高性能

2.4.3. sql语句相关规范

1）每个表建议在30个字段以内

2）机密数据，加密后存储

3）整型数据，默认加上unsigned

4）选择尽可能小的数据类型，用于节省磁盘和内存空间

5）存储浮点数，可以放大倍数存储

6）每个表必须要有主键

7）每隔列使用not null，或增加默认值

8）避免使用not in、not exists、<>、like %%

9）多表连接，小表驱动大表

10）减少临时表应用，优化order by、group by、union、distinct、join等

11）减少语句查询范围，精确查询条件

12）多条件，符合联合索引最左原则

13）查询条件减少使用函数、拼接字符等条件、条件隐式转换

14）如果条件允许，union all代替union

15）减少having字句使用

16）如非必须不使用for update语句

17）update和delete开启安全更新参数

18）减少insert select语句应用

19）优化limit，最好业务逻辑中先获取主键ID，在基于ID进行查询

20）DDL执行前要审核

21）多表连接语句执行前查看执行计划