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

From Masahiko Sawada
Subject Re: Performance degradation on concurrent COPY into a single relation in PG16.
Date
Msg-id CAD21AoAQXYU34pttz9kKWP8z-QTfvK1_wo3+7cn2KO0f4oGnVw@mail.gmail.com
Whole thread Raw
In response to Re: Performance degradation on concurrent COPY into a single relation in PG16.  (Heikki Linnakangas <hlinnaka@iki.fi>)
Responses Re: Performance degradation on concurrent COPY into a single relation in PG16.
List pgsql-hackers
On Mon, Jul 3, 2023 at 4:36 PM Heikki Linnakangas <hlinnaka@iki.fi> wrote:
>
> On 03/07/2023 05:59, 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
>
> This must be highly dependent on the underlying OS and filesystem.

Right. The above were the result where I created the file on the xfs
filesystem. The kernel version and the xfs filesystem version are:

% uname -rms
Linux 4.18.0-372.9.1.el8.x86_64 x86_64

% sudo xfs_db -r /dev/nvme4n1p2
xfs_db> version
versionnum [0xb4b5+0x18a] =
V5,NLINK,DIRV2,ATTR,ALIGN,LOGV2,EXTFLG,MOREBITS,ATTR2,LAZYSBCOUNT,PROJID32BIT,CRC,FTYPE,FINOBT,SPARSE_INODES,REFLINK

As far as I tested, it happens only on the xfs filesystem (at least
the above version) and doesn't happen on ext4 and ext3 filesystems.

Regards,

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



pgsql-hackers by date:

Previous
From: Peter Eisentraut
Date:
Subject: Re: pg_upgrade --copy-file-range
Next
From: Peter Eisentraut
Date:
Subject: Re: Add information about command path and version of flex in meson output