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

On Thu, 27 Jul 2023 at 14:51, David Rowley <dgrowleyml(at)gmail(dot)com> wrote:
> Just to keep this moving and to make it easier for people to test the
> pg_strtoint patches, I've pushed the fix_COPY_DEFAULT.patch patch.
> The only thing I changed was to move the line that was allocating the
> array to a location more aligned with the order that the fields are
> defined in the struct.

I just did another round of benchmarking to see where we're at after
fox_COPY_DEFAULT.patch has been committed.

Below I've benchmarked REL_15_STABLE up to REL_16_STABLE with some
hand-selected key commits, many of which have been mentioned on this
thread already because they seem to affect the performance of COPY.

To summarise, REL_15_STABLE can run this benchmark in 526.014 ms on my
AMD 3990x machine. Today's REL_16_STABLE takes 530.344 ms. We're
talking about another patch to speed up the pg_strtoint functions
which gets this down to 483.790 ms. Do we need to do this for v16 or
can we just leave this as it is already? The slowdown does not seem
to be much above what we'd ordinarily classify as noise using this
test on my machine.

Benchmark setup:

COPY (SELECT generate_series(1, 2000000) a, (random() * 100000 -
50000)::int b, 3243423 c) TO '/tmp/lotsaints.copy';
DROP TABLE lotsaints; CREATE UNLOGGED TABLE lotsaints(a int, b int, c int);

Benchmark:
psql -qX -c 'truncate lotsaints' && pgbench -n -P1 -f <( echo "COPY
lotsaints FROM '/tmp/lotsaints.copy';") -t 15

2864eb977 REL_15_STABLE
latency average = 526.014 ms

8.84% postgres [.] pg_strtoint32

29452de73 Sat Dec 3 10:50:39 2022 -0500 The commit before "Improve
performance of pg_strtointNN functions"
latency average = 508.453 ms

10.21% postgres [.] pg_strtoint32

6b423ec67 Sun Dec 4 16:18:18 2022 +1300 Improve performance of
pg_strtointNN functions
latency average = 492.943 ms

7.73% postgres [.] pg_strtoint32

1939d2628 Fri Dec 9 10:08:44 2022 -0500 The commit before "Convert a
few datatype input functions to use "soft" error reporting."
latency average = 485.016 ms

8.43% postgres [.] pg_strtoint32

ccff2d20e Fri Dec 9 10:14:53 2022 -0500 Convert a few datatype input
functions to use "soft" error reporting.
latency average = 501.325 ms

6.90% postgres [.] pg_strtoint32_safe

60684dd83 Tue Dec 13 17:33:28 2022 -0800 The commit before
"Non-decimal integer literals"
latency average = 500.889 ms

8.27% postgres [.] pg_strtoint32_safe

6fcda9aba Wed Dec 14 05:40:38 2022 +0100 Non-decimal integer literals
latency average = 521.904 ms

9.02% postgres [.] pg_strtoint32_safe

1b6f632a3 Sat Feb 4 07:56:09 2023 +0100 The commit before "Allow
underscores in integer and numeric constants."
latency average = 523.195 ms

9.21% postgres [.] pg_strtoint32_safe

faff8f8e4 Sat Feb 4 09:48:51 2023 +0000 Allow underscores in integer
and numeric constants.
latency average = 493.064 ms

10.25% postgres [.] pg_strtoint32_safe

9f8377f7a Mon Mar 13 10:01:56 2023 -0400 Add a DEFAULT option to COPY FROM
latency average = 597.617 ms

12.91% postgres [.] CopyReadLine
10.62% postgres [.] CopyReadAttributesText
10.51% postgres [.] pg_strtoint32_safe
7.97% postgres [.] NextCopyFrom

REL_16_STABLE @ c1308ce2d Thu Jul 27 14:48:44 2023 +1200 Fix
performance problem with new COPY DEFAULT code
latency average = 530.344 ms

13.51% postgres [.] CopyReadLine
9.62% postgres [.] pg_strtoint32_safe
8.97% postgres [.] CopyReadAttributesText
8.43% postgres [.] NextCopyFrom

REL_16_STABLE + pg_strtoint_fastpath1.patch
latency average = 493.136 ms

13.79% postgres [.] CopyReadLine
11.82% postgres [.] CopyReadAttributesText
7.07% postgres [.] NextCopyFrom
6.81% postgres [.] pg_strtoint32_safe

REL_16_STABLE + pg_strtoint_fastpath2.patch
latency average = 483.790 ms

13.87% postgres [.] CopyReadLine
10.40% postgres [.] CopyReadAttributesText
8.22% postgres [.] NextCopyFrom
5.52% postgres [.] pg_strtoint32_safe

David