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: Masahiko Sawada <sawada.mshk@gmail.com>
Cc: David Rowley <dgrowleyml@gmail.com>, Dean Rasheed <dean.a.rasheed@gmail.com>, Jakub Wartak <jakub.wartak@enterprisedb.com>, Andrew Dunstan <andrew@dunslane.net>, Peter Eisentraut <peter@eisentraut.org>, PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Date: 2023-08-07T18:10:26Z
Lists: pgsql-hackers

Attachments

Hi,

On 2023-08-07 23:05:39 +0900, Masahiko Sawada wrote:
> On Mon, Aug 7, 2023 at 3:16 PM David Rowley <dgrowleyml@gmail.com> wrote:
> >
> > On Wed, 2 Aug 2023 at 13:35, David Rowley <dgrowleyml@gmail.com> wrote:
> > > So, it looks like this item can be closed off.  I'll hold off from
> > > doing that for a few days just in case anyone else wants to give
> > > feedback or test themselves.
> >
> > Alright, closed.
>
> IIUC the problem with multiple concurrent COPY is not resolved yet.

Yea - it was just hard to analyze until the other regressions were fixed.


> The result of nclients = 1 became better thanks to recent fixes, but
> there still seems to be the performance regression at nclient = 2~16
> (on RHEL 8 and 9). Andres reported[1] that after changing
> MAX_BUFFERED_TUPLES to 5000 the numbers became a lot better but it
> would not be the solution, as he mentioned.

I think there could be a quite simple fix: Track by how much we've extended
the relation previously in the same bistate. If we already extended by many
blocks, it's very likey that we'll do so further.

A simple prototype patch attached. The results for me are promising. I copied
a smaller file [1], to have more accurate throughput results at shorter runs
(15s).

HEAD before:
clients	     tps
1	      41
2	      76
4	     136
8	     248
16	     360
32	     375
64	     317


HEAD after:
clients	     tps
1	      43
2	      80
4	     155
8	     280
16	     369
32	     405
64	     344

Any chance you could your benchmark? I don't see as much of a regression vs 16
as you...

Greetings,

Andres Freund

[1] COPY (SELECT generate_series(1, 100000)) TO '/tmp/data.copy';

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