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

From: Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)com>
To: Heikki Linnakangas <hlinnaka(at)iki(dot)fi>
Cc: PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>
Subject: Re: Performance degradation on concurrent COPY into a single relation in PG16.
Date: 2023-07-03 07:54:16
Message-ID: CAD21AoAQXYU34pttz9kKWP8z-QTfvK1_wo3+7cn2KO0f4oGnVw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Mon, Jul 3, 2023 at 4:36 PM Heikki Linnakangas <hlinnaka(at)iki(dot)fi> wrote:
>
> On 03/07/2023 05:59, Masahiko Sawada wrote:
> > On Mon, Jul 3, 2023 at 11:55 AM Masahiko Sawada <sawada(dot)mshk(at)gmail(dot)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

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Peter Eisentraut 2023-07-03 08:17:40 Re: Add information about command path and version of flex in meson output
Previous Message Peter Eisentraut 2023-07-03 07:47:05 Re: pg_upgrade --copy-file-range