Re: low perfomances migrating from 9.3 to 9.5

[thomas veymont <thomas.veymont@xxxxxxxxx>]

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

On Wed, Jul 27, 2016 at 5:54 PM, thomas veymont
<thomas.veymont@xxxxxxxxx> wrote:
> We switched from PostgreSQL server 9.3 to 9.5. From the very beginning there
> was a noticeable drop in performances (for example : when injecting our SQL
> dumps into 9.5, COPY and CREATE INDEX were very slow).
>
> Our configuration file was the same for 9.3 and 9.5, except for the
> "chekpoint segment" that has been deprecated.

Which value are you using for max_wal_size and min_wal_size? And which
value of checkpoint_segments did you use previously? This could
influence the checkpoint frequency.
--
Michael

in 9.3 we had :
checkpoint_segments = 128              
#checkpoint_timeout = 5min              # range 30s-1h
checkpoint_completion_target = 0.9   # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s               # 0 disables

as far as I remember these settings were for faster bulk loading.

in 9.5 we set:
max_wal_size = 6GB                      # https://www.postgresql.org/docs/9.5/static/release-9-5.html, max_wal_size = (3 * checkpoint_segments) * 16MB
#min_wal_size = 80MB

though I tried max_wal_size = 3GB (default is 1GB) and did not notice any improvment.

cheers

tom