1. 在应用负载不变的情况下可以通过如下方法进行WAL优化
1) 延长checkpoint时间间隔
	FPI产生于checkpoint之后第一次变脏的page，在下次checkpoint到来之前，已经输出过FPI的page是不在需要再次输出FPI。因此checkpoint时间间隔越长，FPI产生的频度会越低。增大checkpoint_timeout和max_wal_size可以延长checkpoint时间间隔。
	2) 增加hot_update比例
	普通的update经常需要更新2个数据块，并且可能还需要更新索引page，这些又都有可能产生FPI。而hot_update只修改1个数据块，需要写的WAL量也大大减少，通过调整fillfactor可以达到目标
	3) 压缩
PG9.5新增了一个wal_compression 参数，设置为on可以对FPI进行压缩，消减WAL的大小，另外还可以在外部通过ssl/ssh的压缩功能减少主备间的通信流量，已经自定义归档脚本对归档的wal进行压缩
  • 1.
  • 2.
  • 3.
  • 4.
  • 5.
  • 6.
  1. 验证
1) 初始化pgbench
	-bash-4.2$ pgbench -i
	Password: 
	dropping old tables...
	NOTICE:  table "pgbench_accounts" does not exist, skipping
	NOTICE:  table "pgbench_branches" does not exist, skipping
	NOTICE:  table "pgbench_history" does not exist, skipping
	NOTICE:  table "pgbench_tellers" does not exist, skipping
	creating tables...
	generating data (client-side)...
	100000 of 100000 tuples (100%) done (elapsed 0.13 s, remaining 0.00 s)
	vacuuming...
	creating primary keys...
	done in 0.42 s (drop tables 0.00 s, create tables 0.02 s, client-side generate 0.21 s, vacuum 0.09 s, primary keys 0.09 s).
	2) 验证--延长checkpoint
		a. 第一次
		#配置参数
		max_connections = 3000 
		shared_buffers = 2048MB	
		max_wal_size = 1GB
		min_wal_size = 100MB
		wal_keep_size=5000
		checkpoint_timeout = 5min
		
		#生成数据
		-bash-4.2$ psql -c "checkpoint;select pg_current_wal_lsn()"; pgbench -n -c 100 -j 100 -t 100; psql -c "select pg_current_wal_lsn()"
		Password for user postgres: 
		CHECKPOINT
		 pg_current_wal_lsn 
		--------------------
		 0/A0001C0
		(1 row)
		
		Password: 
		pgbench (15.8)
		transaction type: <builtin: TPC-B (sort of)>
		scaling factor: 1
		query mode: simple
		number of clients: 100
		number of threads: 100
		maximum number of tries: 1
		number of transactions per client: 100
		number of transactions actually processed: 10000/10000
		number of failed transactions: 0 (0.000%)
		latency average = 347.779 ms
		initial connection time = 273.044 ms
		tps = 287.538702 (without initial connection time)
		Password for user postgres: 
		 pg_current_wal_lsn 
		--------------------
		 0/B3EC678
		(1 row)
		
		#解析WAL日志
		-bash-4.2$ pg_waldump -t 1 --stats=record -s  0/A0001C0 -e  0/B3EC678
		WAL statistics between 0/A0001C0 and 0/B3EC678:
		Type                                                 N      (%)                      Record size      (%)                  FPI size      (%)        Combined size      (%)
		----                                           -      ---          -----------      ---             --------      ---        -------------      ---
		Transaction/COMMIT                         10003 ( 12.62)               340502 (  6.44)                    0 (  0.00)               340502 (  1.65)
		Standby/RUNNING_XACTS                          3 (  0.00)                  956 (  0.02)                    0 (  0.00)                  956 (  0.00)
		Standby/INVALIDATIONS                          3 (  0.00)                  366 (  0.01)                    0 (  0.00)                  366 (  0.00)
		Heap2/PRUNE                                 3901 (  4.92)               271240 (  5.13)                    0 (  0.00)               271240 (  1.31)
		Heap2/VACUUM                                   2 (  0.00)                  118 (  0.00)                    0 (  0.00)                  118 (  0.00)
		Heap2/VISIBLE                                  3 (  0.00)                  192 (  0.00)                98304 (  0.64)                98496 (  0.48)
		Heap/INSERT                                 9905 ( 12.50)               786061 ( 14.86)                    0 (  0.00)               786061 (  3.80)
		Heap/UPDATE                                 3140 (  3.96)               530419 ( 10.03)                 7548 (  0.05)               537967 (  2.60)
		Heap/HOT_UPDATE                            26752 ( 33.76)              1932113 ( 36.53)                25988 (  0.17)              1958101 (  9.48)
		Heap/LOCK                                  22033 ( 27.81)              1191797 ( 22.53)             13202280 ( 85.88)             14394077 ( 69.66)
		Heap/INPLACE                                   8 (  0.01)                 1631 (  0.03)                24716 (  0.16)                26347 (  0.13)
		Heap/INSERT+INIT                             101 (  0.13)                 7979 (  0.15)                    0 (  0.00)                 7979 (  0.04)
		Heap/UPDATE+INIT                             115 (  0.15)                18374 (  0.35)                    0 (  0.00)                18374 (  0.09)
		Btree/INSERT_LEAF                           3261 (  4.12)               207923 (  3.93)              2014420