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 20230710162415.sjrwneocahwd5prk@awork3.anarazel.de
Whole thread Raw
In response to Re: Performance degradation on concurrent COPY into a single relation in PG16.  (Jakub Wartak <jakub.wartak@enterprisedb.com>)
Responses Re: Performance degradation on concurrent COPY into a single relation in PG16.
Re: Performance degradation on concurrent COPY into a single relation in PG16.
List pgsql-hackers
Hi,

On 2023-07-03 11:53:56 +0200, Jakub Wartak wrote:
> Out of curiosity I've tried and it is reproducible as you have stated : XFS
> @ 4.18.0-425.10.1.el8_7.x86_64:
>...
> According to iostat and blktrace -d /dev/sda -o - | blkparse -i - output ,
> the XFS issues sync writes while ext4 does not, xfs looks like constant
> loop of sync writes (D) by kworker/2:1H-kblockd:

That clearly won't go well.  It's not reproducible on newer systems,
unfortunately :(. Or well, fortunately maybe.


I wonder if a trick to avoid this could be to memorialize the fact that we
bulk extended before and extend by that much going forward? That'd avoid the
swapping back and forth.


One thing that confuses me is that Sawada-san observed a regression at a
single client - yet from what I can tell, there's actually not a single
fallocate() being generated in that case, because the table is so narrow that
we never end up extending by a sufficient number of blocks in
RelationAddBlocks() to reach that path. Yet there's a regression at 1 client.

I don't yet have a RHEL8 system at hand, could either of you send the result
of a
  strace -c -p $pid_of_backend_doing_copy

Greetings,

Andres Freund



pgsql-hackers by date:

Previous
From: Matthias van de Meent
Date:
Subject: Re: PATCH: Using BRIN indexes for sorted output
Next
From: Gurjeet Singh
Date:
Subject: Re: BUG #18016: REINDEX TABLE failure