Horton

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

Andres Freund <andres@anarazel.de>

View thread View on postgr.es
From: Andres Freund <andres@anarazel.de>
To: Jakub Wartak <jakub.wartak@enterprisedb.com>
Cc: Masahiko Sawada <sawada.mshk@gmail.com>, Heikki Linnakangas <hlinnaka@iki.fi>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-07-10T16:24:15Z
Lists: 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

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