PG系统优化

转自：Marc Beyerle “PostgreSQL: Experiences and tuning recommendations on Linux on IBM Z”

吞吐量优化

Marc Beyerle 写了一篇调优文章介绍通过步骤整体优化提升 PG 45%

1：将PostgreSQL共享缓冲区的大小增加到物理内存总量的1/4，并将有效缓存的大小提高到3/4
shared_buffer 25% 物理内存
effective_cache_size 75% 物理内存 (仅影响优化器评估执行计划)

2：对于只读工作负载，考虑关闭PostgreSQL中的autovacuum守护进程
对于写繁重的生产工作负载，保持autovacuum守护进程处于打开状态

3：启用预读可使吞吐量增加近6%，PostgreSQL在很大程度上依赖于Linux页面缓存的有效性。其他数据库自己决定哪些页面应该提前读取，并且不依赖于操作系统的提前读取功能

4：启用hugepage会使工作负载的吞吐量增加约7%,打开大页用于共享缓冲区消耗，关闭THP
vi /etc/sysctl.conf
vm.nr_hugepages=17408

5: 降低内核调度器的迁移成本使工作负载的吞吐量增加约9%
/etc/sysctl.conf
kernel.sched_migration_cost_ns=50000

响应时间优化

优化前

优化后

1.内核参数调整

默认值：
vm.dirty_background_ratio=10 (10% of 64GB = 6.4 GB)
vm.dirty_ratio=20 (20% of 64GB = 12.8 GB)
调整值：
vm.dirty_background_bytes=67108864 (64 MB)
vm.dirty_bytes=536870912 (512 MB)

系统尽早刷脏，能消除IO刷脏毛刺

2.调整PG参数

checkpoint_completion_target=0.9 #(0.5)  两个checkpoint之间多长时间内完成
max_wal_size=16G  #(1 G)
wal_buffers=16 MB #(-1)
synchronous_commit=off #(on)

一般性建议

1.建议至少有少量可用的交换空间，以便在内存消耗出现峰值时做好准备

2.使用Direct I/O，绕过页面缓存（对性能影响低于1%）

3.使用合适的linux内核版本

4.数据库数据文件与日志文件分开，避免IO竞争（顺序IO，随机IO分离）