Re: low perfomances migrating from 9.3 to 9.5

On Wed, Jul 27, 2016 at 11:01 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

*I have to ask, was a vacuumdb -Z OR psql -U postgres -c ANALYZE ; *

*done after the migration?*

*Without accurate stats, performance goes down the drain.*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.