Postgresql之数据库优化参数设置

比较常见的几个数据库参数配置：
1.share_buffer
大的shared_buffers需要大的checkpoint_segments,同时需要申请更多的System V共享内存资源. 并且增加共享内存管理的开销.
这个值不需要设的太大, 因为PostgreSQL还依赖操作系统的文件系统cache来提高读性能, 另外, 写操作频繁的数据库这个设太大反而会增加checkpoint压力.
在9.4版本中会增加mmap以及huge page table的支持以减少内存管理的开销.
shared_buffers = 512MB

2.maintenance_work_mem
这个值越大, VACUUM, CREATE INDEX的操作越快, 当然大到一定程度瓶颈就不在内存了, 可能是CPU例如创建索引.
这个值是一个操作的内存使用上限, 而不是一次性分配出去的. 并且需要注意如果开启了autovacuum, 最大可能有
autovacuum_max_workers*maintenance_work_mem的内存被系统消耗掉.
maintenance_work_mem = 512MB

3.VACUUM
手动执行vacuum操作时, 默认是没有停顿执行到底的, 为了防止VACUUM操作消耗太多数据库服务器硬件资源, 这个值是指vacuum在消耗多少资源后停顿多少时
间,以便其他的操作可以使用更多的硬件资源.
vacuum_cost_delay = 10ms
vacuum_cost_limit = 10000
默认autovacuum就是打开的, log_autovacuum_min_duration = 0记录所有的autovacuum操作.
autovacuum = on
log_autovacuum_min_duration = 0

4.bgwriter_delay

默认bgwriter进程执行一次后会停顿200ms再被唤醒执行下一次操作, 当数据库的写操作很频繁的时候, 200ms可能太长, 导致其他进程需要花费过多的时间来进行
bgwriter的操作. 短暂的停顿更利于将shared buffer中的脏块flush到磁盘, 降低backend 主动flush 以申请共享内存的情形. 后面使用explain时会讲到.
bgwriter_delay = 10ms

5.wal_level 
如果需要做数据库WAL日志备份的话至少需要设置成archive级别, 如果需要做hot_standby那么需要设置成hot_standby, 由于这个值修改需要重启数据库, 所以先
设置成archive比较好.
wal_level= archive

6.wal_buffers
wal buffers默认是-1 根据shared_buffers的设置自动调整shared_buffers*3% .最大限制是XLOG的segment_size.
wal_buffers = 16384kB

7.checkpoint设置
多少个xlog file产生后开始checkpoint操作,这个值越大, 允许shared_buffer中的被频繁访问的脏数据存储得更久. 一定程度上可以提高数据库性能. 
但是太大的话会导致在数据库发生checkpoint的时候需要处理更多的脏数据带来长时间的IO开销(还要考虑bgwriter的存在).
太小的话会导致产生更多的WAL文件(因为full page writes=on, CHECKPOINT后的第一次块的改变要写全块, checkpoint越频繁, 越多的数据更新要写全块导致产
生更多WAL）.
checkpoint_segments = 32   （9.6版本中没找到这个参数）
这个和checkpoint_segments的效果是一样的, 只是触发的条件是时间条件.
checkpoint_timeout = 5min