Re: low perfomances migrating from 9.3 to 9.5

2016-07-27 14:11 GMT+02:00 Michael Paquier <michael(dot)paquier(at)gmail(dot)com>:

>
>
> 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