Re: BUG #15609: synchronous_commit=off insert performance regression with secondary indexes

Hi Andrew,

1) changing wal_levels did not make any difference w/
synchronous_commit=off.
This pattern also applies to fsync=off

2) We suspect the same with regards to the flushing bottleneck being
removed.
But that seems to imply that there was a change deeper in the code that
causes this regression on PG10+.
IMHO I cannot be the only one that noticed this, since PG10 has been out
for a while and secondary indexes are quite common in practice.
I was wondering if anyone can point me in the right direction on how to
further investigate this?

Cheers,

Paolo

Test results below.

---------------------------
WAL_LEVEL = {DEFAULTS}
--(MINIMAL ON 9.5,9.6 , REPLICA ON 10,11)
SYNCHRONOUS_COMMIT=OFF VERSION TPS DIFF FROM PG95

PG9.5 104503 0.0%
PG9.6 98842 -5.4%
PG10 103924 -0.6%
PG11 117635 12.6%

PG9.5 67285 0.0%
PG9.6 70153 4.3%
PG10 53657 -20.3%
PG11 49952 -25.8%

PG9.5 67695 0.0%
PG9.6 68592 1.3%
PG10 51039 -24.6%
PG11 48630 -28.2%

PG9.5 66102 0.0%
PG9.6 67883 2.7%
PG10 48964 -25.9%
PG11 46215 -30.1%

WAL_LEVEL = MINIMAL
SYNCHRONOUS_COMMIT=OFF VERSION TPS DIFF FROM PG95

PG9.5 103785 0.0%
PG9.6 98303 -5.3%
PG10 103369 -0.4%
PG11 116446 12.2%

PG9.5 67877 0.0%
PG9.6 70841 4.4%
PG10 52885 -22.1%
PG11 50111 -26.2%

PG9.5 67754 0.0%
PG9.6 69373 2.4%
PG10 52646 -22.3%
PG11 48824 -27.9%

PG9.5 66197 0.0%
PG9.6 69217 4.6%
PG10 50518 -23.7%
PG11 47389 -28.4%

WAL_LEVEL = HOT_STANDBY; (9.5, 9.6)
WAL_LEVEL = REPLICA; (10, 11)
SYNCHRONOUS_COMMIT=OFF VERSION TPS DIFF FROM PG95

PG9.5 104718 0.0%
PG9.6 97279 -7.1%
PG10 104249 -0.4%
PG11 116119 10.9%

PG9.5 68819 0.0%
PG9.6 71223 3.5%
PG10 52592 -23.6%
PG11 50047 -27.3%

PG9.5 67057 0.0%
PG9.6 69256 3.3%
PG10 51317 -23.5%
PG11 48401 -27.8%

PG9.5 66727 0.0%
PG9.6 67591 1.3%
PG10 49819 -25.3%
PG11 47453 -28.9%

WAL_LEVEL = LOGICAL
SYNCHRONOUS_COMMIT=OFF VERSION TPS DIFF FROM PG95

PG9.5 104208 0.0%
PG9.6 97920 -6.0%
PG10 104084 -0.1%
PG11 115364 10.7%

PG9.5 66910 0.0%
PG9.6 70968 6.1%
PG10 52719 -21.2%
PG11 48882 -26.9%

PG9.5 67704 0.0%
PG9.6 69768 3.0%
PG10 50080 -26.0%
PG11 49294 -27.2%

PG9.5 67490 0.0%
PG9.6 68872 2.0%
PG10 45837 -32.1%
PG11 46505 -31.1%
---------------------------

On Tue, 29 Jan 2019 at 20:30, Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)uk>
wrote:

> >>>>> "PG" == PG Bug reporting form <noreply(at)postgresql(dot)org> writes:
>
> PG> Questions:
>
> PG> Is there an extra setting for Postgres 10+ required to "recover"
> PG> the performance loss from PG9.5?
>
> The default wal_level changed between pg 9.6 and pg10, does reverting
> that change make any difference? (this is just a guess)
>
> PG> Why is PG10+ slower by default when synchronous_commit is off?
>
> synchronous_commit is probably only relevant to the extent that turning
> it off causes the test not to be bottlenecked on WAL flush calls.
>
> --
> Andrew (irc:RhodiumToad)
>

--------------------------------------------------------------------------------------------------------------------------------------------
PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL

VERIZON CONNECT CONFIDENTIALITY NOTICE
This message is intended for the addressee only and may contain confidential and/or privileged material. Any review, re-transmission, dissemination, reliance upon or other use of this information by persons or entities other than the addressee is prohibited. If you receive this in error, please contact the sender and delete this message. Thank you.