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 <melvin6...@gmail.com>: > > > On Wed, Jul 27, 2016 at 11:01 AM, Adrian Klaver <adrian.kla...@aklaver.com > > wrote: > >> On 07/27/2016 07:52 AM, thomas veymont wrote: >> >>> >>> 2016-07-27 14:11 GMT+02:00 Michael Paquier <michael.paqu...@gmail.com >>> <mailto:michael.paqu...@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.kla...@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 >> > > > > > *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. >
