And do you see changes if you increase min_wal_size? This will increase the number of WAL segments recycled instead of removed at each checkpoint. -- Michael
I have seen no improvment with the following parameters in 9.5: max_wal_size = 3GB min_wal_size = 512MB #checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0 #checkpoint_warning = 30s # 0 disables
while my 9.3 configuration is: checkpoint_segments = 128 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 5min # range 30s-1h checkpoint_completion_target = 0.9 # checkpoint target duration, 0.0 - 1.0 #checkpoint_warning = 30s # 0 disables
I have just run a quick pgbench test to get some objective numbers. Both tests were run on the same machine (ie. production machine), same disk, same logical volume :
On 9.5 :
$ pgbench -c 4 -j 2 -T 600 test starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 70 query mode: simple number of clients: 4 number of threads: 2 duration: 600 s number of transactions actually processed: 77318 latency average: 31.041 ms tps = 128.859708 (including connections establishing) tps = 128.860447 (excluding connections establishing)
On 9.3 :
$ pgbench -c 4 -j 2 -T 600 test starting vacuum...end. transaction type: TPC-B (sort of) scaling factor: 70 query mode: simple number of clients: 4 number of threads: 2 duration: 600 s number of transactions actually processed: 1834436 latency average: 1.308 ms tps = 3057.387254 (including connections establishing) tps = 3057.398493 (excluding connections establishing)
Note that the 9.3 is handling others production requests in the same time.
Is a checkpoint_segment/WAL problem still to be suspected ?