PostgreSQL IO优化技巧

数据库 PostgreSQL
如果有人问“信创数据库学啥比较好”,从今后的工作机会以及学习资料的普及程度来说,我首先推荐的就是PostgreSQL。

​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 性能。请务必仔细考虑您的硬件设置并配置适当的参数以获得最佳结果。​

责任编辑:武晓燕 来源: 白鳝的洞穴
相关推荐

2024-04-12 08:28:38

优化查询语句PostgreSQL索引

2013-12-26 13:19:26

PostgreSQL优化

2024-04-08 09:00:30

PostgreSQ数据库EXPLAIN 命令

2018-05-25 15:04:57

数据库PostgreSQL查询优化器

2018-05-23 13:47:28

数据库PostgreSQL查询优化

2020-12-17 07:52:38

JavaScript

2011-03-25 13:43:54

Cacti优化

2011-07-01 16:05:22

SEO

2011-05-25 20:53:26

SEO

2009-06-16 16:39:49

Hibernate性能

2011-03-10 10:09:33

LAMP优化

2011-03-09 10:55:33

LAMP优化技巧

2020-10-06 18:57:14

PostgreSQL数据库数据导入

2017-12-12 14:26:16

数据库PostgreSQL逻辑优化

2023-03-10 08:37:33

预热优化PostgreSQL

2011-06-14 14:17:23

性能优化系统层次

2011-06-14 11:14:10

性能优化代码

2010-08-10 10:17:44

Flex内存

2013-04-07 09:53:24

Windows系统优化

2011-07-11 15:26:49

性能优化算法
点赞
收藏

51CTO技术栈公众号