PostgreSQL checkpoint 相关参数优化设置与解释

本文涉及的产品
云数据库 RDS SQL Server,基础系列 2核4GB
云数据库 RDS MySQL,集群系列 2核4GB
推荐场景:
搭建个人博客
RDS PostgreSQL Serverless,0.5-4RCU 50GB 3个月
推荐场景:
对影评进行热评分析
简介:

标签

PostgreSQL , checkpoint , IO HANG


背景

数据库的检查点相关参数如何配置,能让数据库运行更加顺滑?

内存、磁盘能力如何搭配,能发挥更好的性能?

OS的参数如何设置,能尽可能的降低大内存机器带来的IO风暴问题?

数据库检查点设置

假设服务器内存是512GB。磁盘顺序、随机写吞吐是2 GB/s。

1、经验值

shared_buffers = 128GB                  # 1/4 内存  
  
checkpoint_timeout = 30min              # range 30s-1d  
  
max_wal_size = 256GB          # 2*shared_buffers  
min_wal_size = 64GB           # shared_buffers * 1/2  
  
checkpoint_completion_target = 0.9     # checkpoint target duration, 0.0 - 1.0  

这里还没有考虑磁盘的读写能力。

因此可能经验值配置的参数会导致一些性能问题。

我们根据上面的设置,来计算一下数据库发生检查点时到底会产生多少IO。

1、以极端为例进行讲解。假设shared buffer所有页都是脏页。

那么有128 GB脏页。

另外两个参数控制平滑刷脏页的调度:

2、30分钟*0.9 的时间窗口内,要刷完所有脏页。也就是说超过时间后,CKPT进程会全速刷脏页,checkpoint进程不进行任何sleep。

3、在新产生 256GB * 0.9 的WAL一个周期内,要刷完所有脏页。也就是检查点开始后,产生的WAL如果超过了,CKPT进程会全速刷脏页,checkpoint进程不进行任何sleep。

开销计算

1、刷脏页开销,属于离散IO。

平均值: 128GB / (30600.9) = 80.9 MB/s

(实际是sleep调度的,峰值可能还会更高一些) 。 物理写。

2、业务产生的写WAL开销,属于顺序IO。

最大平均值: 256GB / (30*60) = 145.6 MB/s

(实际是sleep调度的,峰值可能还会更高一些) 。 物理写。

3、业务上写操作除了产生WAL,还有bgwriter(异步WRITER) 。 异步写。

最大平均值: 256GB / (30*60) = 145.6 MB/s (因为一个块可能被多次修改,但是writer此时可能更少) 。

4、操作系统merge IO(写bgwriter的DIRTY PAGE),离散大块IO。 物理写。

最大平均值: 256GB / (30*60) = 145.6 MB/s

因此最糟糕的情况下,以上述配置为例,检查点期间,数据库写操作吞吐均值可能是 80.9 MB/s + 145.6 MB/s + 145.6 MB/s = 372.1 MB/s。

这个值,结合磁盘能力,可以判断检查点是否会有磁盘瓶颈。

建议设置

由于业务上还需要读写磁盘,同时PG目前的版本还需要垃圾回收,FREEZE等会产生IO的操作。因此不能让计算得到的峰值与磁盘实际IO能力相当,应该有所保留。

1、磁盘IOPS指标,写吞吐。(假设给25%用作 刷脏离散写、WAL顺序写)

2、如果按经验参数,评估出来磁盘能力不足(检查点实际需要的IO能力,与磁盘厂商给出的IO能力的25%不匹配),怎么办?

首先调大checkpoint_timeout参数,如果到最大值(1 DAY),依旧无法满足,则需要降低shared_buffers,以及相应的max_wal_size。

3、建议设置 log_checkpoints=on , 可以评估checkpoint的统计信息,用于帮助修正以上参数。

操作系统刷脏页设置

大内存机器,LINUX可能会遇到IO HANG的问题,原因也是刷脏页的配置不正确。

LINUX也有刷脏页的内核配置,默认是一个百分比,10%的脏页,后台进程开始刷脏页。如果产生脏页过快,到达20%时,用户进程也会帮助刷脏页。

因此如果内存越大,这个阈值就越大,而如果磁盘能力没有跟上,可能一次性会刷几十GB的脏页,导致磁盘的IO能力打爆,影响正常业务。

相关参数

1、os 内核参数

vm.dirty_background_bytes = 409600000  
vm.dirty_background_ratio = 0  
vm.dirty_bytes = 0  
vm.dirty_expire_centisecs = 3000  
vm.dirty_ratio = 95  
vm.dirty_writeback_centisecs = 100  

2、数据库参数

shared_buffers = 8GB                    # min 128kB  
                                        # (change requires restart)  
  
# - Checkpoints -  
  
#checkpoint_timeout = 5min              # range 30s-1d  
#max_wal_size = 1GB  
#min_wal_size = 80MB  
#checkpoint_completion_target = 0.5     # checkpoint target duration, 0.0 - 1.0  
#checkpoint_flush_after = 256kB         # measured in pages, 0 disables  
#checkpoint_warning = 30s               # 0 disables  

小结

为了尽量的降低数据库检查点带来的IO突增,影响业务。应该根据磁盘IO能力来修正检查点设置的经验值,避免IO HANG的问题。

同时OS层面的道理也一样,要设置好对应的OS刷脏页的调度参数。

参考

《DBA不可不知的操作系统内核参数》

《PostgreSQL 检查点性能影响及源码分析 - 7》

《PostgreSQL 检查点性能影响及源码分析 - 6》

《PostgreSQL 检查点性能影响及源码分析 - 5》

《PostgreSQL 检查点性能影响及源码分析 - 4》

《PostgreSQL 检查点性能影响及源码分析 - 3》

《PostgreSQL 检查点性能影响及源码分析 - 2》

《PostgreSQL 检查点性能影响及源码分析 - 1》

《PostgreSQL 9.6 检查点柔性优化(SYNC_FILE_RANGE) - 在单机多实例下的IO Hang问题浅析与优化》

相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
目录
相关文章
|
7月前
|
监控 关系型数据库 数据库
PostgreSQL的索引优化策略?
【8月更文挑战第26天】PostgreSQL的索引优化策略?
168 1
|
3月前
|
Oracle 安全 关系型数据库
【赵渝强老师】PostgreSQL的参数文件
PostgreSQL数据库的四个主要参数文件包括:`postgresql.conf`(主要配置文件)、`pg_hba.conf`(访问控制文件)、`pg_ident.conf`(用户映射文件)和`postgresql.auto.conf`(自动保存修改后的参数)。视频讲解和详细说明帮助理解各文件的作用。
161 19
|
6月前
|
缓存 关系型数据库 数据库
如何优化 PostgreSQL 数据库性能?
如何优化 PostgreSQL 数据库性能?
290 2
|
7月前
|
监控 关系型数据库 数据库
如何优化PostgreSQL的性能?
【8月更文挑战第4天】如何优化PostgreSQL的性能?
336 7
|
7月前
|
关系型数据库 Shell 数据库
PostgreSQL怎么设置?
【8月更文挑战第6天】PostgreSQL怎么设置?
64 3
|
8月前
|
SQL 监控 关系型数据库
实时计算 Flink版操作报错合集之在设置监控PostgreSQL数据库时,将wal_level设置为logical,出现一些表更新和删除操作报错,怎么办
在使用实时计算Flink版过程中,可能会遇到各种错误,了解这些错误的原因及解决方法对于高效排错至关重要。针对具体问题,查看Flink的日志是关键,它们通常会提供更详细的错误信息和堆栈跟踪,有助于定位问题。此外,Flink社区文档和官方论坛也是寻求帮助的好去处。以下是一些常见的操作报错及其可能的原因与解决策略。
|
8月前
|
SQL 分布式计算 关系型数据库
实时计算 Flink版产品使用问题之在使用FlinkCDC与PostgreSQL进行集成时,该如何配置参数
实时计算Flink版作为一种强大的流处理和批处理统一的计算框架,广泛应用于各种需要实时数据处理和分析的场景。实时计算Flink版通常结合SQL接口、DataStream API、以及与上下游数据源和存储系统的丰富连接器,提供了一套全面的解决方案,以应对各种实时计算需求。其低延迟、高吞吐、容错性强的特点,使其成为众多企业和组织实时数据处理首选的技术平台。以下是实时计算Flink版的一些典型使用合集。
实时计算 Flink版产品使用问题之在使用FlinkCDC与PostgreSQL进行集成时,该如何配置参数
|
7月前
|
开发框架 关系型数据库 数据库
在 PostgreSQL 中,解决图片二进制数据,由于bytea_output参数问题导致显示不正常的问题。
在 PostgreSQL 中,解决图片二进制数据,由于bytea_output参数问题导致显示不正常的问题。
|
10月前
|
存储 JSON 关系型数据库
PostgreSQL Json应用场景介绍和Shared Detoast优化
PostgreSQL Json应用场景介绍和Shared Detoast优化
|
10月前
|
弹性计算 关系型数据库 数据库
开源PostgreSQL在倚天ECS上的最佳优化实践
本文基于倚天ECS硬件平台,以自顶向下的方式从上层应用、到基础软件,再到底层芯片硬件,通过应用与芯片的硬件特性的亲和性分析,实现PostgreSQL与倚天芯片软硬协同的深度优化,充分使能倚天硬件性能,帮助开源PostgreSQL应用实现性能提升。

相关产品

  • 云原生数据库 PolarDB
  • 云数据库 RDS PostgreSQL 版