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-25T19:42:26Z
Lists: pgsql-hackers

Attachments

Andres Freund <andres@anarazel.de> writes:
> On 2023-09-06 18:01:53 -0400, Tom Lane wrote:
>> 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:

> At first I was a bit confounded by not being able to reproduce this. My test
> environment had max_connections=110 for some other reason - and the problem
> doesn't reproduce with that setting...

I just did a git bisect run to discover when the failure documented
in bug #18130 [1] started.  And the answer is commit 82a4edabd.
Now, it's pretty obvious that that commit didn't in itself cause
problems like this:

postgres=# \copy test from 'bug18130.csv' csv
ERROR:  could not read block 5 in file "base/5/17005": read only 0 of 8192 bytes
CONTEXT:  COPY test, line 472: "0,185647715,222655,489637,2,2023-07-31,9100.0000000,302110385,2023-07-30 14:16:36.750981+00,14026347..."

IMO there must be some very nasty bug lurking in the new
multiple-block extension logic, that happens to be exposed by this
test case with 82a4edabd's adjustments to the when-to-extend choices
but wasn't before that.

To save other people the trouble of extracting the in-line data
in the bug submission, I've attached the test files I was using.
The DDL is simplified slightly from what was submitted.  I'm not
entirely sure why a no-op trigger is needed to provoke the bug...

			regards, tom lane

[1] https://www.postgresql.org/message-id/18130-7a86a7356a75209d%40postgresql.org

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