PostgreSQL近年来越来越火，尤其是在国内，基于其生态的数据库产品种类繁多。如果有人问“信创数据库学什么比较好”，从以后的工作机会和学习资料的普及来说，我首先推荐的是PostgreSQL。然而，目前大多数PostgreSQL用户并没有认真配置数据库以达到最佳使用效果，充分发挥硬件的性能特点。其中，数据库IO的优化是重中之重，很大的IO延迟会导致所有SQL变慢。今天的文章将介绍提高PostgreSQLIO性能的八个技巧。首先，使用相应更快、更高吞吐量的存储硬件：提高IO性能的最重要因素之一是用于存储数据库文件的存储硬件。在关键系统中，固态硬盘(SSD)或硬件RAID阵列通常被认为具有更快的读写速度。高性能低延迟的集中式SAN存储是传统大型数据库的主要存储介质，但现在很多PG数据库都是单机部署，使用服务器本地存储来降低使用成本。充分优化本地存储的存储性能，提高存储介质的可靠性非常重要。使用本地SATASSD可以有效提升数据库的整体性能，在HDD上添加高性能缓冲区也具有成本效益。为企业应用设计出性能优异、价格适中的本地存储方案是保证PGIO性能的关键。二、调整shared_buffers：shared_buffers配置参数决定了PostgreSQL用来在内存中缓存数据页的内存量。调整此参数以匹配系统上可用的内存量以获得最佳性能。由于PG数据库采用了双缓冲机制，因此不同的业务负载，shared_buffers参数的设置会有所不同。PG管网推荐为shared_buffers配置25%的物理内存。在不了解业务场景和数据分布的情况下，这是一种适度的配置方案。例如，如果你的物理内存是256GB，而你的常用数据是100GB，那么将shared_buffers设置为128GB可能是一个更好的配置。设置shared_buffers的首要原则是不要让操作系统产生更多的页面变化。如果操作系统频繁换页，则需要评估操作系统内存占用是否过高，因为shared_buffers占用了过多的物理内存。造成的。三、优化WAL的配置：WAL是PostgreSQL中保证事务持久性和一致性的一个关键特性。配置wal_buffers参数以匹配您的工作负载并确保最佳WAL性能。调整wal_buffers的值时，重要的是要考虑生成WAL数据的速率。增加wal_buffers的值可以帮助降低磁盘写入频率并提高性能。但是在正常负载下，调整wal_buffers并没有看到数据库性能的提升，只有当WAL写入BUFFER的速度大于Walwriter写入磁盘的速度时，增加wal_buffers才会有特别明显的性能提升。作为一般规则，建议将wal_buffers的值设置为磁盘块大小的一个小倍数，即16MB。除了调整wal_buffers，调整max_wal_size等参数也可以有效降低WAL带来的性能下降。另外，对CHECKPOINT的优化也可以大大减少WAL的写入量，从而优化WAL的性能。四、IO分区：IO分区是一种将数据和索引分布在多个磁盘上的技术，可以通过减少磁盘I/O争用来帮助提高IO性能。考虑使用表和索引分区来利用这种性能提升。将WAL存储与单独的高性能存储结合起来，也是IO分区很常见的做法，对于高并发环境下的数据库IO性能提升非常有效。利用表空间将热表分散到不同的存储中，也是一种非常有效的IO分区方法。不过大家要注意，要使用IO分区，首先要保证存放PG数据库的磁盘或者磁盘组是分区的，并且有一定的隔离性。如果在一个磁盘组上划分多个逻辑卷，那么PG存储就是IO分区，用处不大。第五，预热缓存：pg_prewarm扩展可用于预热包含频繁访问数据的缓存，减少未来查询所需的磁盘I/O量。大家之前也做过很多测试，发现当PG数据库中的某条SQL执行计划不变时，执行速度差异很大。最后大家发现，如果SQL访问的数据大部分在sharedbuffer或者OS中的FILECACHE中，执行效率更高。因此，对PG数据库中的热点数据进行预热和缓冲是提高数据库性能的有效途径。pg_prewarm是一个非常常用的缓冲区预热插件。安装插件后，我们可以使用selectpg_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；对于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性能。请务必仔细考虑您的硬件设置并配置适当的参数以获得最佳结果。
