Re: Performance comparison between Pgsql 10.5 and Pgsql 11.2

[Nicola Contu <nicola.contu@xxxxxxxxx>]

Because I have 10.6 in production :) and I am comparing with what I will be loosing.

And I read that in the release notes but as said in my first email, even with data_sync_retry=on (going back to previous behavior) doesn't make any difference.

So I am looking for something that will keep my performances but still allows me to upgrade to 11 in production.

Also, trying with 11.1, the problem seems still there.

Il giorno lun 4 mar 2019 alle ore 14:45 Imre Samu <pella.samu@xxxxxxxxx> ha scritto:

> is there any reason why I am getting worse results using pgsql11.2 in writing comparing it with pgsql 10.6?
>... And Yes both are compiled.

Why 10.6?

according to release notes
"14th February 2019: PostgreSQL 11.2, 10.7, 9.6.12, 9.5.16, and 9.4.21 Released!"  https://www.postgresql.org/about/news/1920/

imho: it would be better to compare PG11.2  with  PG10.7  (  similar bug Fixes and Improvements + same fsync()  behavior )

"This release changes the behavior in how PostgreSQL interfaces with fsync() and includes fixes for partitioning and over 70 other bugs that were reported over the past three months"

Imre

Nicola Contu <nicola.contu@xxxxxxxxx> ezt írta (időpont: 2019. márc. 4., H, 13:14):

I did a analyze in stages on both.

And Yes both are compiled.

This is the configure command (change 10.6 for PG10)

./configure --prefix=/usr/local/pgsql11.2

See attached perf report. The difference seems to be all in this line, but not sure :

+   26.80%     0.00%           222  postmaster       [kernel.kallsyms]                    [k] system_call_fastpath

I am using CentOS 7

With Centos I am using this profile for tuned-adm

[root@STAGING-CMD1 ~]#  tuned-adm active

Current active profile: latency-performance

Il giorno sab 2 mar 2019 alle ore 20:41 Thomas Munro <thomas.munro@xxxxxxxxx> ha scritto:

On Sat, Mar 2, 2019 at 5:02 AM Ray O'Donnell <ray@xxxxxxxxxxxx> wrote:
> On 01/03/2019 15:01, Nicola Contu wrote:
> > Hello,
> > is there any reason why I am getting worse results using pgsql11.2 in
> > writing comparing it with pgsql 10.6?
> >
> > I have two Instances, both just restored, so no bloats.
> > Running read queries I have pretty much same results, a little bit
> > better on pg11- Running writes the difference is in favour of 10.
>
> Did you run ANALYZE on the databases after restoring?

If you can rule out different query plans, and if you compiled them
both with the same compiler and optimisation levels and without
cassert enabled (it's a long shot but I mentioned that because you
showed a path in /usr/local so perhaps you're hand-compiling 11, but
10 came from a package?), then the next step might be to use a
profiler like "perf" (or something equivalent on your OS) to figure
out where 11 is spending more time in the write test?

--
Thomas Munro
https://enterprisedb.com