PG参数优化

checkpoint优化

触发条件

当满足下面任意条件时，执行一次checkpoint（9.4版本之后）

• max_wal_size             #当写入了max_wal_size大小的wal日志时，触发一次checkpoint。建议值：max_wal_size=shared_buffers * 0.75
• checkpoint_timeout       #当checkpoint_timeout时间后，触发一次checkpoint建议值：10min
• 手动执行 checkpoint 命令

控制checkpoint写脏页速度

为了避免检查点产生太多的I/O，导致系统性能出现大的抖动，可以让PostgreSQL在平时也尽快平均地把脏页刷新到磁盘中，而不必等到发生检查点时才发现需要写太多的脏页。这个机制是由参数checkpoint_completion_target来控制的，此参数的默认值为“0.5”，即让PostgreSQL在两个检查点间隔时间的0.5倍时间内完成所有脏页的刷新。看起来把该值设置得越接近1.0，性能的抖动越平稳，但实际上不要设置为“1.0”，设置为“0.9”就足够了，因为设置为“1.0”极有可能导致不能按时完成检查点

• checkpoint_completion_target   #建议值：0.9

VACUUM中的优化

打开自动vacuum

强烈建议打开autovacuum，自动进行垃圾回收和统计信息收集。

• autovacuum  #是否开启自动vacuum强烈建议值：on
• autovacuum_max_workers  #autovacuum进程的最大数量

控制vacuum执行力度/频度

进行垃圾回收和统计信息收集时，可能会造成数据库IO压力，引起数据库"抖动",可以通过参数进行控制.让vacuum/autovacuum工作到一定的量（vacuum_cost_limit或者autovacuum_vacuum_cost_limit）时，休眠（vacuum_cost_delay或者autovacuum_vacuum_cost_delay）一定的ms时间。

• vacuum_cost_limit =200 or 10000  #vacuum执行代价值累计。机械硬盘使用默认值200即可，SSD调整到10000
• vacuum_cost_delay =0  #默认关闭 vacuum休眠时间
• autovacuum_vacuum_cost_limit = -1  #默认是-1，表示使用vacuum_cost_limit的值。
• autovacuum_vacuum_cost_delay = 2ms  #12版本及以后版本默认是2ms，12版本前是20ms。建议调整到2ms

vacuum_cost_limit=访问在共享内存中的数据块数×vacuum_cost_page_hit+访问在磁盘上的数据块数×vacuum_cost_page_miss+修改干净的在磁盘中的数据块数×vacuum_cost_page_dirty

• vacuum_cost_page_hit：VACUUM访问的数据块在共享内存中的代价值，默认为“1”
• vacuum_cost_page_miss：VACUUM访问的数据块不在共享内存中的代价值，默认为“10”。
• vacuum_cost_page_dirty：VACUUM改变一个非脏数据块为脏数据块的代价值，默认为“20”。

VACUUM制定力度优化思路

如果数据库压力较大：调小vacuum_cost_limit，调大autovacuum_vacuum_cost_delay
如果vacuum工作不力：调大vacuum_cost_limit，调小autovacuum_vacuum_cost_delay，调大autovacuum_max_workers

触发VACUUM的条件

触发一：一张表变更的行数超过如下公式的阈值时，AutoVacuum才会对这张表做VACUUM
autovacuum_vacuum_scale_factor×表上记录数+autovacuum_vacuum_threshold

• autovacuum_vacuum_threshold  #默认50，threshold：当表上发生变化的行数至少达到此参数值时，才可
  能让AutoVacuum对其进行VACUUM
• autovacuum_vacuum_scale_factor  #默认0.2，表示当变化的行数据为表上记录数20%的时候就可能触发vacuum

触发二：当表中行上的事务ID太早，超过autovacuum_freeze_max_age参数指定的年龄时，会强制调起autovacuum进程对这张表做急切模式下VACUUM操作。急切模式下会进行全表扫描，引发大量的IO。（涉及到事务回卷，不详细描述）

可以对表单独设置这两个参数
alter table big_table set (autovacuum_vacuum_threshold=90,toast.autovacuum_vacuum_threshold=90);

触发ANALYZE的条件

一张表变更的行数超过如下公式的阈值时，AutoVacuum才会对这张表做ANALYZE
autovacuum_analyze_scale_factor×表上记录数+autovacuum_analyze_threshold

• autovacuum_analyze_threshold  #默认50，表上修改，删除的记录数
• autovacuum_analyze_scale_factor  #默认0.2，表示当变化的行数据为表上记录数20%的时候就可能触发ANALYZE