Horton

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

Heikki Linnakangas <hlinnaka@iki.fi>

View thread View on postgr.es
From: Heikki Linnakangas <hlinnaka@iki.fi>
To: Masahiko Sawada <sawada.mshk@gmail.com>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-07-03T07:36:49Z
Lists: pgsql-hackers 
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. I'm 
not seeing that effect on my laptop:

/data$ time /tmp/test test.0 0
total	200000
fallocate	0
filewrite	200000

real	0m1.856s
user	0m0.140s
sys	0m1.688s
/data$ time /tmp/test test.1 1
total	200000
fallocate	200000
filewrite	0

real	0m1.335s
user	0m0.156s
sys	0m1.179s
/data$ time /tmp/test test.2 2
total	200000
fallocate	100000
filewrite	100000

real	0m2.159s
user	0m0.165s
sys	0m1.880s

/data$ uname -a
Linux heikkilaptop 6.0.0-6-amd64 #1 SMP PREEMPT_DYNAMIC Debian 6.0.12-1 
(2022-12-09) x86_64 GNU/Linux

/data is an nvme drive with ext4 filesystem.

-- 
Heikki Linnakangas
Neon (https://neon.tech)

Commits

  1. Avoid edge case in pg_visibility test with small shared_buffers

  2. Fix bulk table extension when copying into multiple partitions

  3. hio: Take number of prior relation extensions into account

  4. Fix performance regression in pg_strtointNN_safe functions

  5. Fix performance problem with new COPY DEFAULT code

  6. hio: Use ExtendBufferedRelBy() to extend tables more efficiently

  7. Add VACUUM/ANALYZE BUFFER_USAGE_LIMIT option