PostgreSQL IO优化技巧

​PostgreSQL近些年热度越来越高，特别在国内，基于其生态的数据库产品种类繁多。如果有人问“信创数据库学啥比较好”，从今后的工作机会以及学习资料的普及程度来说，我首先推荐的就是PostgreSQL。

不过目前大多数PostgreSQL用户都没有认真配置数据库，让其达到最佳的使用效果，并充分发挥出硬件的性能特征。其中数据库IO的优化是重中之重，IO延时较大会导致所有的SQL都会变慢。今天的这篇文章将介绍提高 PostgreSQL IO 性能的八个技巧。

首先，使用相应速度更快，吞吐能力更强的存储硬件：提高 IO 性能的最重要因素之一是用于存储数据库文件的存储硬件。在关键系统中，一般会考虑使用固态硬盘 (SSD) 或硬件 RAID 阵列以获得更快的读写速度。高性能低延时的集中式SAN存储是传统大型数据库的主要存储介质，不过现在很多PG数据库都在单机部署，使用服务器本地存储，从而降低使用成本。在本地存储中充分优化存储性能，提高存储介质可靠性是十分关键的。用本地SATA SSD盘可以有效提高数据库的整体性能，在HDD上增加高性能缓冲也是性价比很高的做法。为企业应用设计一个性能优秀，价格适中的本地存储方案，是确保PG IO性能的关键。

第二，调整 shared_buffers：shared_buffers 配置参数确定 PostgreSQL 用于在内存中缓存数据页的内存量。调整此参数以匹配系统上可用的内存量以获得最佳性能。由于PG数据库使用double buffer机制，因此不同的业务负载，shared_buffers参数的设置会有所不同。PG管网建议配置25%的物理内存给shared_buffers使用，这是一种当你不了解业务场景与数据分布时的中庸的配置方案。举个例子，如果你的物理内存是256GB，而你的常用数据是100GB，那么设置一个128GB的shared_buffers有可能是比较好的配置。设置shared_buffers的首要原则是，不能让操作系统产生较多的换页，如果OS经常性出现换页，那么你要评估一下是不是由于shared_buffers占用了过多的物理内存，导致OS内存使用率过高引起的。

第三，优化WAL的配置：WAL是 PostgreSQL 中的一项关键功能，可确保事务的持久性和一致性。配置 wal_buffers 参数以匹配您的工作负载并确保最佳 WAL 性能。调整 wal_buffers 的值时，重要的是要考虑生成 WAL 数据的速率，增加 wal_buffers 的值有助于降低磁盘写入频率并提高性能，不过在普通的负载下，调整wal_buffers并不能看到数据库性能的提升，只有当WAL写入BUFFER的速度大于Walwriter写盘的速度的时候，加大wal_buffers才会有特别明显的性能提升。作为一般规则，建议将 wal_buffers 的值设置为磁盘块大小的小倍数，16 MB。除了调整wal_buffers之外，调整max_wal_size等参数也能有效地减少WAL导致的性能下降，另外CHECKPOINT的优化也能大幅减少WAL的写出量，从而优化WAL的性能。

第四，IO分区：IO分区是一种将数据和索引分布在多个磁盘上的技术，它可以通过减少磁盘 I/O 争用来帮助提高 IO 性能。考虑使用表和索引分区来利用这种性能提升。将WAL存储与单独的高性能存储也是IO分区的一种十分常用的做法，对于高并发环境的数据库IO性能提升十分有效。利用tablespace将热表分散到不同的存储上去也是十分有效的IO分区的方法。不过大家要注意的是，要使用IO分区，首先要确保存放PG数据库的磁盘或者磁盘组本身是分区的，具有一定的隔离性，如果你在一个磁盘组上分出多个逻辑卷，然后将PG的存储做IO分区，那么用处就不大了。

第五，预热缓存：pg_prewarm 扩展可用于预热具有频繁访问数据的缓存，减少未来查询所需的磁盘 I/O 量。以前大家都做过很多测试，发现在PG数据库中某条SQL执行计划不变的情况下，执行速度差异很大，最终大家都发现了如果SQL访问的大多数数据都在shared buffers中或者在OS的FILE CACHE中，那么执行效率是较高的。因此在PG数据库中对热数据做预热缓冲是有效提升数据库性能的方法。Pg_prewarm是一个十分常用的缓冲预热插件。

安装完插件后，我们可以使用select pg_prewarm(‘tablename’)来预热某张表的数据。在某些大型统计报表开始之前先预热数据是提高性能的很好的方法，用于预热数据的PG插件也很多，大家可以根据需要选择使用。

第六，优化检查点：检查点是将共享缓冲区缓存中的脏页刷新到磁盘的过程。降低检查点的频率和大小有助于减少磁盘 I/O 并提高性能。优化检查点性能的一些技术包括增加 checkpoint_timeout 和 checkpoint_completion_target 配置参数，以及使用更快的存储硬件来存储数据和 WAL 文件。

第七，调整CBO策略参数：调整 effective_cache_size，random_page_cost等多个参数都是CBO优化器来评估各种操作的成本的重要参数，在一个有数万甚至数十万条SQL语句的数据库系统而言，CBO能够产生合理的执行计划对于数据库性能至关重要，PG数据库没有Oracle那么强大的SQL优化工具与优化手段来辅助，因此设置好这些与CBO产生合理执行计划紧密相关的参数十分重要。effective_cache_size 配置参数用于估计 PostgreSQL 可用的磁盘缓存量，从而确定扫描数据的成本。random_page_cost 配置参数确定随机磁盘 I/O 相对于顺序磁盘 I/O 的成本。设置此参数以准确反映系统上随机磁盘 I/O 的成本。据磁盘类型的不同，对 random_page_cost 的设置也会有所不同：对于 HDD，可以设置为 4.0 到 4.5；对于 SSD，可以设置为 1.0 到 1.5。如果使用中央化的 SAN 存储，可以根据其具体配置和性能进行调整，为了设置合理的值，需要对你的存储的随机读写性能进行测试。PG中还有几个类似的参数，可能会影响到CBO生成执行计划，如果你发现你的PG数据库中存在较多的错误的执行计划，那么可以尝试调整一下这些参数：seq_page_cost、cpu_tuple_costcpu_index_tuple_cost、cpu_operator_cost。

第八，操作系统参数优化：主要是在VM的后台写、前台写、脏块刷新策略、内存换页策略等方面进行优化，这方面我以前已经写过多篇文章介绍，在这里就不重复了，有兴趣的朋友可以去翻阅一下我以前的发文。

综上所述，这八个技巧可以大大提高 PostgreSQL 的 IO 性能。请务必仔细考虑您的硬件设置并配置适当的参数以获得最佳结果。​