Horton

Re: Performance degradation on concurrent COPY into a single relation in PG16.

Dean Rasheed <dean.a.rasheed@gmail.com>

View thread View on postgr.es
From: Dean Rasheed <dean.a.rasheed@gmail.com>
To: David Rowley <dgrowleyml@gmail.com>
Cc: Andres Freund <andres@anarazel.de>, Masahiko Sawada <sawada.mshk@gmail.com>, Jakub Wartak <jakub.wartak@enterprisedb.com>, Andrew Dunstan <andrew@dunslane.net>, Peter Eisentraut <peter@eisentraut.org>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-08-01T19:38:12Z
Lists: pgsql-hackers 
On Tue, 1 Aug 2023 at 15:01, David Rowley <dgrowleyml@gmail.com> wrote:
>
> Here's a patch with an else condition when the first digit check fails.
>
> master + fastpath4.patch:
> latency average = 1579.576 ms
> latency average = 1572.716 ms
> latency average = 1563.398 ms
>
> (appears slightly faster than fastpath3.patch)
>

Running the new test on slightly older Intel hardware (i9-9900K, gcc
11.3), I get the following:

REL_15_STABLE
latency average = 1687.695 ms
latency stddev = 3.068 ms

REL_16_STABLE
latency average = 1931.756 ms
latency stddev = 2.065 ms

REL_16_STABLE + pg_strtoint_fastpath1.patch
latency average = 1635.731 ms
latency stddev = 3.074 ms

REL_16_STABLE + pg_strtoint_fastpath2.patch
latency average = 1687.303 ms
latency stddev = 4.243 ms

REL_16_STABLE + pg_strtoint_fastpath3.patch
latency average = 1610.307 ms
latency stddev = 2.193 ms

REL_16_STABLE + pg_strtoint_fastpath4.patch
latency average = 1577.604 ms
latency stddev = 4.060 ms

HEAD
latency average = 1868.737 ms
latency stddev = 6.114 ms

HEAD + pg_strtoint_fastpath1.patch
latency average = 1683.215 ms
latency stddev = 2.322 ms

HEAD + pg_strtoint_fastpath2.patch
latency average = 1650.014 ms
latency stddev = 3.920 ms

HEAD + pg_strtoint_fastpath3.patch
latency average = 1670.337 ms
latency stddev = 5.074 ms

HEAD + pg_strtoint_fastpath4.patch
latency average = 1653.568 ms
latency stddev = 8.224 ms

I don't know why HEAD and v16 aren't consistent, but it's seems to be
quite reproducible, even though the numutils source is the same in
both branches, and using gdb to dump the disassembly for
pg_strtoint32_safe() shows that it's also the same.

Anyway, insofar as these results can be trusted, fastpath4.patch looks good.

Regards,
Dean

Commits

  1. Avoid edge case in pg_visibility test with small shared_buffers

  2. Fix bulk table extension when copying into multiple partitions

  3. hio: Take number of prior relation extensions into account

  4. Fix performance regression in pg_strtointNN_safe functions

  5. Fix performance problem with new COPY DEFAULT code

  6. hio: Use ExtendBufferedRelBy() to extend tables more efficiently

  7. Add VACUUM/ANALYZE BUFFER_USAGE_LIMIT option