Re: low perfomances migrating from 9.3 to 9.5

[thomas veymont <thomas.veymont@xxxxxxxxx>]

2016-07-27 14:11 GMT+02:00 Michael Paquier <michael.paquier@xxxxxxxxx>:

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 ?

cheers
Tom