Re: Performance degradation on concurrent COPY into a single relation in PG16. - Mailing 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