【PostgreSQL系列】PostgreSQL性能优化
💝💝💝欢迎来到我的博客，很高兴能够在这里和您见面！希望您在这里可以感受到一份轻松愉快的氛围，不仅可以获得有趣的内容和知识，也可以畅所欲言、分享您的想法和见解。
- 推荐:kwan 的首页,持续学习,不断总结,共同进步,活到老学到老
- 导航
- kwan 的解忧杂货铺:全面总结 java 核心技术,jvm,并发编程 redis,kafka,Spring,微服务等
- 常用开发工具系列:常用的开发工具,IDEA,Mac,Alfred,Git,typora 等
- 数据库系列:详细总结了常用数据库 mysql 技术点,以及工作中遇到的 mysql 问题等
- 新空间代码工作室:提供各种软件服务,承接各种毕业设计,毕业论文等
- 懒人运维系列:总结好用的命令,解放双手不香吗?能用一个命令完成绝不用两个操作
- 数据结构与算法系列:总结数据结构和算法,不同类型针对性训练,提升编程思维,剑指大厂
非常期待和您一起在这个小小的网络世界里共同探索、学习和成长。💝💝💝 ✨✨ 欢迎订阅本专栏 ✨✨
博客目录
- 一、WAL(预写式日志)相关参数优化
- 1. wal_buffers 参数
- 2. max_wal_size 参数
- 二、检查点相关参数优化
- 1. checkpoint_timeout 参数
- 2. checkpoint_completion_target 参数
- 三、I/O 性能相关参数优化
- 1. random_page_cost 参数
- 2. effective_io_concurrency 参数
- 四、内存相关参数优化
- 1. maintenance_work_mem 参数
- 2. work_mem 参数
- 五、参数调整后的配置重载
- 六、参数调优的综合策略
- 1. 循序渐进原则
- 2. 监控与评估
- 3. 工作负载特性考虑
- 七、常见问题与解决方案
- 1. 检查点引起的性能波动
- 2. 内存不足问题
- 3. WAL 相关瓶颈
PostgreSQL 作为一款功能强大的开源关系型数据库，其默认配置通常是为了适应广泛的通用场景而设计的。然而，在生产环境中，针对特定工作负载进行适当的参数调优可以显著提升数据库性能。
一、WAL(预写式日志)相关参数优化
1. wal_buffers 参数
wal_buffers参数控制 WAL(Write-Ahead Logging)缓冲区的大小，默认值为 4MB。WAL 缓冲区用于存储尚未写入磁盘的 WAL 数据，增大此值可以减少磁盘 I/O 操作。
ALTER SYSTEM SET wal_buffers = '16MB';
优化建议：
- 对于写入密集型工作负载，建议设置为 shared_buffers 的 1/32 到 1/64
- 最大不超过 WAL 段文件大小(通常为 16MB)
- 过大的 wal_buffers 会占用过多内存，但不会带来额外性能提升
2. max_wal_size 参数
max_wal_size参数控制 WAL 文件的最大总大小，默认值为 1GB。
ALTER SYSTEM SET max_wal_size = '4GB';
优化建议：
- 对于高写入负载的系统，增大此值可以减少检查点频率
- 通常设置为 checkpoint_timeout 期间预计产生的 WAL 量的 2-4 倍
- 需要确保有足够的磁盘空间存放 WAL 文件
二、检查点相关参数优化
1. checkpoint_timeout 参数
checkpoint_timeout参数设置自动检查点之间的最长时间间隔，默认值为 5 分钟。
ALTER SYSTEM SET checkpoint_timeout = '30min';
优化建议：
- 增大此值可以减少检查点频率，降低 I/O 负载
- 但过长的间隔会导致崩溃恢复时间变长
- 通常设置在 15-30 分钟之间较为合适
2. checkpoint_completion_target 参数
checkpoint_completion_target参数控制在检查点间隔内完成检查点的目标比例，默认值为 0.5。
ALTER SYSTEM SET checkpoint_completion_target = 0.9;
优化建议：
- 设置为 0.9 意味着检查点将在 90%的时间间隔内完成
- 较高的值可以使检查点 I/O 更均匀地分布
- 有助于避免 I/O 突发对系统性能的影响
三、I/O 性能相关参数优化
1. random_page_cost 参数
random_page_cost参数设置规划器对非顺序获取磁盘页面的成本估计，默认值为 4.0。
ALTER SYSTEM SET random_page_cost = 4.0;
优化建议：
- 对于 SSD 存储，可以设置为 1.0-2.0
- 对于高端存储阵列，可以设置为 2.0-3.0
- 较低的 random_page_cost 会使查询规划器更倾向于使用索引扫描
2. effective_io_concurrency 参数
effective_io_concurrency参数设置 PostgreSQL 预期的并发 I/O 操作数量，默认值为 1。
ALTER SYSTEM SET effective_io_concurrency = 3;
优化建议：
- 对于 SSD 或 RAID 阵列，可以设置为 2-4
- 此参数影响预读行为和维护工作
- 过高的值会导致不必要的 I/O 预取
四、内存相关参数优化
1. maintenance_work_mem 参数
maintenance_work_mem参数设置维护操作(如 VACUUM、CREATE INDEX 等)使用的最大内存量，默认值为 64MB。
ALTER SYSTEM SET maintenance_work_mem = '256MB';
优化建议：
- 对于大型数据库，可以设置为 256MB-1GB
- 较大的值可以加速维护操作
- 但需要确保系统有足够的可用内存
2. work_mem 参数
work_mem参数设置每个查询操作(如排序或哈希表)可以使用的内存量，默认值为 4MB。
ALTER SYSTEM SET work_mem = '16MB';
优化建议：
- 对于复杂查询较多的系统，可以适当增大
- 但过大的值可能导致内存使用失控
- 可以考虑在会话级别为特定查询设置更高的值
五、参数调整后的配置重载
修改这些参数后，通常不需要重启 PostgreSQL 服务，只需执行以下命令即可重新加载配置：
SELECT pg_reload_conf();
注意事项：
- 某些参数更改需要完全重启 PostgreSQL 服务才能生效
- 在生产环境中应用更改前，应在测试环境中验证
- 可以使用SHOW命令验证参数是否已正确应用
六、参数调优的综合策略
1. 循序渐进原则
参数调优应该采取渐进的方式，每次只调整一个或少数几个参数，观察系统行为变化后再决定下一步调整。
2. 监控与评估
使用 PostgreSQL 的内置统计视图(如 pg_stat_activity、pg_stat_bgwriter)和扩展(如 pg_stat_statements)来监控参数调整的效果。
3. 工作负载特性考虑
不同的应用场景需要不同的优化策略：
- OLTP 系统：关注短事务性能和高并发
- 数据仓库：关注大查询和复杂分析性能
- 混合负载：需要平衡各种需求
七、常见问题与解决方案
1. 检查点引起的性能波动
症状：系统周期性变慢，I/O 等待时间周期性增加。
解决方案：调整 checkpoint_timeout 和 checkpoint_completion_target，使检查点 I/O 更均匀分布。
2. 内存不足问题
症状：频繁的磁盘交换，响应时间不稳定。
解决方案：合理设置 work_mem 和 maintenance_work_mem，确保不超过系统可用内存。
3. WAL 相关瓶颈
症状：高写入负载下性能下降明显。
解决方案：优化 wal_buffers 和 max_wal_size，考虑使用更快的存储设备存放 WAL。
觉得有用的话点个赞 👍🏻 呗。
❤️❤️❤️本人水平有限，如有纰漏，欢迎各位大佬评论批评指正！😄😄😄
💘💘💘如果觉得这篇文对你有帮助的话，也请给个点赞、收藏下吧，非常感谢!👍 👍 👍
🔥🔥🔥Stay Hungry Stay Foolish 道阻且长,行则将至,让我们一起加油吧！🌙🌙🌙
