Re: low perfomances migrating from 9.3 to 9.5

hi Melvin, Adrian,

>
> Where did you get the respective versions of Postgres?
>

both were compiled from source, from the official website tar files.
gcc is 4.1.2 20080704 (Red Hat 4.1.2-48)

>
> Where they installed the same way?
>

yes, exactly the same. My configure command line is:
./configure --prefix=/usr/local/pgsqlX.XX --with-perl --with-python
--with-tcl --with-openssl --with-pam --with-ldap --with-libxml
--with-libxslt --with-system-tzdata=/usr/share/zoneinfo/>

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

I was meaning the log feed is obviously "slow" because you can almost
"read" the log lines going through. You usually can't because it's too fast.

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

*>*
You're right. I did not run ANALYZE in the first time (assuming autovacuum
would do it when needed). But it should be noted that :
- even re-injecting the 9.3 dumps into the fresh 9.5 engine was much longer
than expected (it is agreed that I cannot run ANALYZE before re-injecting
the dumps ;)
- the pgbench run on both 9.3/9.5 systems were run without ANALYZE. And
yet, the 9.3 test provided better results than 9.5.

To be clear in my mind about it, I think I need to re-run these tests and
check whether it's machine/OS dependant or even I am doing my test the
wrong way.
I will be back to you with more objective values by next week.

thanks for helping,
Tom

2016-07-27 17:14 GMT+02:00 Melvin Davidson <melvin6925(at)gmail(dot)com>:

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