From: Jim Nasby <nasbyj@amazon.com>
To: Andres Freund <andres@anarazel.de>
To: pgsql-hackers@postgresql.org
To: Thomas Munro <thomas.munro@gmail.com>
To: Melanie Plageman <melanieplageman@gmail.com>
Cc: Yura Sokolov <y.sokolov@postgrespro.ru>
Cc: Robert Haas <robertmhaas@gmail.com>
Subject: Re: refactoring relation extension and BufferAlloc(), faster COPY
Date: Tue, 21 Feb 2023 15:00:15 -0600
Message-ID: <a1efd2f9-290e-b860-b490-a8bf6530b288@amazon.com> (raw)
In-Reply-To: <20221029025420.eplyow6k7tgu6he3@awork3.anarazel.de>
References: <20221029025420.eplyow6k7tgu6he3@awork3.anarazel.de>
On 10/28/22 9:54 PM, Andres Freund wrote:
> b) I found that is quite beneficial to bulk-extend the relation with
> smgrextend() even without concurrency. The reason for that is the primarily
> the aforementioned dirty buffers that our current extension method causes.
>
> One bit that stumped me for quite a while is to know how much to extend the
> relation by. RelationGetBufferForTuple() drives the decision whether / how
> much to bulk extend purely on the contention on the extension lock, which
> obviously does not work for non-concurrent workloads.
>
> After quite a while I figured out that we actually have good information on
> how much to extend by, at least for COPY /
> heap_multi_insert(). heap_multi_insert() can compute how much space is
> needed to store all tuples, and pass that on to
> RelationGetBufferForTuple().
>
> For that to be accurate we need to recompute that number whenever we use an
> already partially filled page. That's not great, but doesn't appear to be a
> measurable overhead.
Some food for thought: I think it's also completely fine to extend any
relation over a certain size by multiple blocks, regardless of
concurrency. E.g. 10 extra blocks on an 80MB relation is 0.1%. I don't
have a good feel for what algorithm would make sense here; maybe
something along the lines of extend = max(relpages / 2048, 128); if
extend < 8 extend = 1; (presumably extending by just a couple extra
pages doesn't help much without concurrency).
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: nasbyj@amazon.com, andres@anarazel.de, thomas.munro@gmail.com, melanieplageman@gmail.com, y.sokolov@postgrespro.ru, robertmhaas@gmail.com
Subject: Re: refactoring relation extension and BufferAlloc(), faster COPY
In-Reply-To: <a1efd2f9-290e-b860-b490-a8bf6530b288@amazon.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox