Re: Performance degradation on concurrent COPY into a single relation in PG16. - Mailing list pgsql-hackers

From Andres Freund
Subject Re: Performance degradation on concurrent COPY into a single relation in PG16.
Date
Msg-id 20230710154439.3nmjghva4o57fju3@awork3.anarazel.de
Whole thread Raw
In response to Re: Performance degradation on concurrent COPY into a single relation in PG16.  (Masahiko Sawada <sawada.mshk@gmail.com>)
List pgsql-hackers
Hi,

On 2023-07-03 11:59:38 +0900, Masahiko Sawada wrote:
> On Mon, Jul 3, 2023 at 11:55 AM Masahiko Sawada <sawada.mshk@gmail.com> wrote:
> >
> > After further investigation, the performance degradation comes from
> > calling posix_fallocate() (called via FileFallocate()) and pwritev()
> > (called via FileZero) alternatively depending on how many blocks we
> > extend by. And it happens only on the xfs filesystem.
>
> FYI, the attached simple C program proves the fact that calling
> alternatively posix_fallocate() and pwrite() causes slow performance
> on posix_fallocate():
>
> $ gcc -o test test.c
> $ time ./test test.1 1
> total   200000
> fallocate       200000
> filewrite       0
>
> real    0m1.305s
> user    0m0.050s
> sys     0m1.255s
>
> $ time ./test test.2 2
> total   200000
> fallocate       100000
> filewrite       100000
>
> real    1m29.222s
> user    0m0.139s
> sys     0m3.139s

On an xfs filesystem, with a very recent kernel:

time /tmp/msw_test /srv/dev/fio/msw 0
total    200000
fallocate    0
filewrite    200000

real    0m0.456s
user    0m0.017s
sys    0m0.439s


time /tmp/msw_test /srv/dev/fio/msw 1
total    200000
fallocate    200000
filewrite    0

real    0m0.141s
user    0m0.010s
sys    0m0.131s


time /tmp/msw_test /srv/dev/fio/msw 2
total    200000
fallocate    100000
filewrite    100000

real    0m0.297s
user    0m0.017s
sys    0m0.280s


So I don't think I can reproduce your problem on that system...

I also tried adding a fdatasync() into the loop, but that just made things
uniformly slow.


I guess I'll try to dig up whether this is a problem in older upstream
kernels, or whether it's been introduced in RHEL.

Greetings,

Andres Freund



pgsql-hackers by date:

Previous
From: Andres Freund
Date:
Subject: Re: Performance degradation on concurrent COPY into a single relation in PG16.
Next
From: Tom Lane
Date:
Subject: Re: gcc -Wclobbered in PostgresMain