Horton

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

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andres Freund <andres@anarazel.de>
Cc: Heikki Linnakangas <hlinnaka@iki.fi>, Alvaro Herrera <alvherre@alvh.no-ip.org>, Masahiko Sawada <sawada.mshk@gmail.com>, 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-09-06T22:01:53Z
Lists: pgsql-hackers 
Andres Freund <andres@anarazel.de> writes:
> On 2023-08-16 13:15:46 +0200, Alvaro Herrera wrote:
>> Since the wins from this patch were replicated and it has been pushed, I
>> understand that this open item can be marked as closed, so I've done
>> that.

> Thanks!

It turns out that this patch is what's making buildfarm member
chipmunk fail in contrib/pg_visibility [1].  That's easily reproduced
by running the test with shared_buffers = 10MB.  I didn't dig further
than the "git bisect" result:

$ git bisect bad
82a4edabd272f70d044faec8cf7fd1eab92d9991 is the first bad commit
commit 82a4edabd272f70d044faec8cf7fd1eab92d9991
Author: Andres Freund <andres@anarazel.de>
Date:   Mon Aug 14 09:54:03 2023 -0700

    hio: Take number of prior relation extensions into account

but I imagine the problem is that the patch's more aggressive
relation-extension heuristic is causing the table to have more
pages than the test case expects.  Is it really a good idea
for such a heuristic to depend on shared_buffers?  If you don't
want to change the heuristic then we'll have to find a way to
tweak this test to avoid it.

			regards, tom lane

[1] https://buildfarm.postgresql.org/cgi-bin/show_log.pl?nm=chipmunk&dt=2023-09-06%2014%3A14%3A51

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