Subject: Re: benchmark results comparing versions 15.2 and 16

From: Andres Freund <[email-masked]>
Date: Mon, 15 May 2023 17:14:47 -0700

Hi,

On 2023-05-15 14:20:24 +0900, Michael Paquier wrote:
> On Thu, May 11, 2023 at 01:28:40PM +0900, Michael Paquier wrote:
> > On Tue, May 09, 2023 at 09:48:24AM -0700, Andres Freund wrote:
> >> On 2023-05-08 12:11:17 -0700, Andres Freund wrote:
> >>> I can reproduce a significant regression due to f193883fc of a workload just
> >>> running
> >>> SELECT CURRENT_TIMESTAMP;
> >>> 
> >>> A single session running it on my workstation via pgbench -Mprepared gets
> >>> before:
> >>> tps = 89359.128359 (without initial connection time)
> >>> after:
> >>> tps = 83843.585152 (without initial connection time)
> >>> 
> >>> Obviously this is an extreme workload, but that nevertheless seems too large
> >>> to just accept...
> 
> Extreme is adapted for a worst-case scenario.  Looking at my notes
> from a few months back, that's kind of what I did on my laptop, which
> was the only machine I had at hand back then:
> - Compilation of code with -O2.

I assume without assertions as well?

> I have re-run a bit more pgbench (1 client, prepared query with a
> single SELECT on a SQL keyword, etc.).  And, TBH, I am not seeing as
> much difference as you do (nothing with default pgbench setup, FWIW),
> still that's able to show a bit more difference than the other two
> cases.

> HEAD shows me an average output close to 43900 TPS (3 run of
> 60s each, for instance), while relying on SQLValueFunction shows an
> average of 45000TPS.  That counts for ~2.4% output regression here
> on bigbox based on these numbers.  Not a regression as high as
> mentioned above, still that's visible.

45k seems too low for a modern machine, given that I get > 80k in such a
workload, on a workstation with server CPUs (i.e. many cores, but not that
fast individually).  Hence wondering about assertions being enabled...

I get quite variable performance if I don't pin client / server to the same
core, but even the slow performance is faster than 45k.

Greetings,

Andres Freund

---

From: Michael Paquier <[email-masked]>
Date: Tue, 16 May 2023 09:42:31 +0900

> I assume without assertions as well?

Yup, no assertions.

> fast individually).  Hence wondering about assertions being enabled...

Nope, disabled.

> core, but even the slow performance is faster than 45k.

Okay.  You mean with something like taskset or similar, I guess?
--
Michael

---

From: Andres Freund <[email-masked]>
Date: Mon, 15 May 2023 17:54:53 -0700

Hi,

> Okay.  You mean with something like taskset or similar, I guess?

Yes. numactl --physcpubind ... in my case.  Linux has an optimization where it
does not need to send an IPI when the client and server are scheduled on the
same core. For single threaded ping-pong tasks like pgbench -c1, that can make
a huge difference, particularly on larger CPUs.  So you get a lot better
performance when forcing things to be colocated.

Greetings,

Andres Freund

---

From: Michael Paquier <[email-masked]>
Date: Tue, 16 May 2023 14:42:39 +0900

> performance when forcing things to be colocated.

Yes, that's not bringing the numbers higher with the simple cases I
reported previously, either.

Anyway, even if I cannot see such a high difference, I don't see how
to bring back the original numbers you are reporting without doing
more inlining and tying COERCE_SQL_SYNTAX more tightly within the
executor's portions for the FuncExprs, and there are the collation
assumptions as well.  Perhaps that's not the correct thing to do with
SQLValueFunction remaining around, but nothing can be done for v16, so
I am planning to just revert the change before beta1, and look at it
again later, from scratch.
--
Michael