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.paquier@gmail.com
<mailto:michael.paquier@gmail.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.klaver@aklaver.com


--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general