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

On Tue, Jul 11, 2023 at 12:34 AM Andres Freund <andres@anarazel.de> wrote:
>
> Hi,
>
> On 2023-07-03 11:55:13 +0900, Masahiko Sawada wrote:
> > While testing PG16, I observed that in PG16 there is a big performance
> > degradation in concurrent COPY into a single relation with 2 - 16
> > clients in my environment. I've attached a test script that measures
> > the execution time of COPYing 5GB data in total to the single relation
> > while changing the number of concurrent insertions, in PG16 and PG15.
> > Here are the results on my environment (EC2 instance, RHEL 8.6, 128
> > vCPUs, 512GB RAM):
>
> Gah, RHEL with its frankenkernels, the bane of my existance.
>
> FWIW, I had extensively tested this with XFS, just with a newer kernel. Have
> you tested this on RHEL9 as well by any chance?

I've tested this on RHEL9 (m5.24xlarge; 96vCPUs, 384GB RAM), and it
seems to be reproducible on RHEL9 too.

$ uname -rms
Linux 5.14.0-284.11.1.el9_2.x86_64 x86_64
$ cat /etc/redhat-release
Red Hat Enterprise Linux release 9.2 (Plow)

PG15: nclients = 1, execution time = 22.193
PG15: nclients = 2, execution time = 12.430
PG15: nclients = 4, execution time = 8.677
PG15: nclients = 8, execution time = 6.144
PG15: nclients = 16, execution time = 5.938
PG15: nclients = 32, execution time = 5.775
PG15: nclients = 64, execution time = 5.928
PG15: nclients = 128, execution time = 6.346
PG15: nclients = 256, execution time = 6.641

PG16: nclients = 1, execution time = 24.601
PG16: nclients = 2, execution time = 27.845
PG16: nclients = 4, execution time = 40.575
PG16: nclients = 8, execution time = 24.379
PG16: nclients = 16, execution time = 15.835
PG16: nclients = 32, execution time = 8.370
PG16: nclients = 64, execution time = 4.042
PG16: nclients = 128, execution time = 2.956
PG16: nclients = 256, execution time = 2.591

Tests with test.c program:

$ rm -f test.data; time ./test test.data 0
total   200000
fallocate       0
filewrite       200000

real    0m0.709s
user    0m0.057s
sys     0m0.649s

$ rm -f test.data; time ./test test.data 1
total   200000
fallocate       200000
filewrite       0

real    0m0.853s
user    0m0.058s
sys     0m0.791s

$ rm -f test.data; time ./test test.data 2
total   200000
fallocate       100000
filewrite       100000

real    2m10.002s
user    0m0.366s
sys     0m6.649s

Regards,

-- 
Masahiko Sawada
Amazon Web Services: https://aws.amazon.com