Re: low perfomances migrating from 9.3 to 9.5

On 07/27/2016 07:52 AM, thomas veymont wrote:
>
> 2016-07-27 14:11 GMT+02:00 Michael Paquier <michael(dot)paquier(at)gmail(dot)com
> <mailto: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 ?

Where did you get the respective versions of Postgres?

Where they installed the same way?

You mentioned the log feed showing obvious performance issues, can we
see the relevant portions?

>
> cheers
> Tom
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com