BufferAccessStrategy for bulk insert

I'm taking a look at doing the refactoring Tom Lane and Simon Riggs
discussed here:

http://archives.postgresql.org/pgsql-patches/2008-02/msg00155.php

In terms of the buffer manager, I think we can simply introduce a new
strategy type BAS_BULKWRITE and make it behave identically to
BAS_VACUUM. Anyone see a reason to do anything else?

The trickier part is to handle the communication between CopyFrom (or
the CTAS machinery), heap_insert, and RelationGetBufferForTuple.
There are basically three things we need to keep track of here:

(1) a BufferAccessStrategy (that is, the ring of buffers we're using
for this bulk insert)
(2) the last-pinned page (to implement Simon Riggs's proposed
optimization of keeping the most-recently-written page pinned)
(3) use_wal and use_fsm (to implement Tom Lane's suggestion of
reducing the number of options to heap_insert by rolling everything
into an options object)

Tom's email seemed to suggest that we might want to roll everything
into the BufferAccessStrategy itself, but that seems to require quite
a few things to know about the internals of BufferAccessStrategy that
currently don't, so I think that's a bad idea. I am kind of inclined
to define flags like this:

#define HEAP_INSERT_SKIP_WAL 0x0001
#define HEAP_INSERT_SKIP_FSM 0x0002
#define HEAP_INSERT_BULK 0x0004 /* do we even need this one? */

And then:

Oid heap_insert(Relation relation, HeapTuple tup, CommandId cid,
unsigned options, BulkInsertState *bistate);
BulkInsertState *GetBulkInsertState(void);
void FreeBulkInsertState(BulkInsertState *);

I'm always wary of reversing the sense of a boolean, but I think it
makes sense here; it doesn't really matter whether you call
heap_insert(relation, tup, cid, true, true) or heap_insert(relation,
tup, cid, false, false), but heap_insert(relation, tup, cid,
HEAP_INSERT_USE_WAL|HEAP_INSERT_USE_FSM, NULL) is a lot uglier than
heap_insert(relation, tup, cid, 0, NULL), and there aren't that many
places that need to be checked for correctness in making the change.

Admittedly, we could make the calling sequence for heap_insert shorter
by putting the options (and maybe even the CommandId) into
BulkInsertState and calling it HeapInsertOptions, but that forces
several callers of heap_insert who don't care at all about bulk
inserts to uselessly create and destroy a HeapInsertOptions object
just to pass a couple of boolean flags (and maybe the CommandId),
which seems like a loser.

Comments?

...Robert

"Robert Haas" <robertmhaas(at)gmail(dot)com> writes:
> I am kind of inclined to define flags like this:

> #define HEAP_INSERT_SKIP_WAL 0x0001
> #define HEAP_INSERT_SKIP_FSM 0x0002
> #define HEAP_INSERT_BULK 0x0004 /* do we even need this one? */

> And then:

> Oid heap_insert(Relation relation, HeapTuple tup, CommandId cid,
> unsigned options, BulkInsertState *bistate);
> BulkInsertState *GetBulkInsertState(void);
> void FreeBulkInsertState(BulkInsertState *);

Seems sane to me. I don't see the point of the HEAP_INSERT_BULK flag
bit --- providing or not providing bistate would cover that, and if
you have a bit as well then you have to define what the inconsistent
combinations mean. I concur with making all-zeroes be the typical
state of the flag bits, too.

FWIW, we generally declare bitmask flag variables as int, unless
there's some really good reason to do otherwise.

regards, tom lane

> Seems sane to me. I don't see the point of the HEAP_INSERT_BULK flag
> bit --- providing or not providing bistate would cover that, and if
> you have a bit as well then you have to define what the inconsistent
> combinations mean. I concur with making all-zeroes be the typical
> state of the flag bits, too.

Thanks for the design review. I had thought to make the inconsistent
combinations fail an assertion, but I'm just as happy to leave it out
altogether.

> FWIW, we generally declare bitmask flag variables as int, unless
> there's some really good reason to do otherwise.

OK, thanks for the tip.

...Robert

And here's the patch, which based on comments thus far does the following:

- Replaces the use_wal, use_fsm arguments in various places with a
single options argument.
- Creates a BAS_BULKWRITE buffer access strategy.
- Creates a BulkInsertState object so that COPY and CTAS can use
BAS_BULKWRITE and also keep the most recent page pinned.

Note that the original purpose of this exercise was to implement the
optimization that COPY and CTAS would keep the most recent page pinned
to avoid repeated pin/unpin cycles. This change shows a small but
measurable performance improvement on short rows. The remaining items
were added based on reviewer comments.

One concern that I have about this approach is that the situation in
which people are probably most concerned about COPY performance is
restoring a dump. In that case, the COPY will be the only thing
running, and using a BufferAccessStrategy is an anti-optimization. I
don't think it's a very big effect (any testing anyone can do on real
hardware rather than what I have would be appreciated) but I'm sort of
unsold of optimizing for what I believe to be the less-common use
case. If the consensus is to reverse course on this point I'm happy
to rip those changes back out and resubmit; they are a relatively
small proportion of the patch.

...Robert

On Sun, Oct 26, 2008 at 8:37 PM, Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
>> Seems sane to me. I don't see the point of the HEAP_INSERT_BULK flag
>> bit --- providing or not providing bistate would cover that, and if
>> you have a bit as well then you have to define what the inconsistent
>> combinations mean. I concur with making all-zeroes be the typical
>> state of the flag bits, too.
>
> Thanks for the design review. I had thought to make the inconsistent
> combinations fail an assertion, but I'm just as happy to leave it out
> altogether.
>
>> FWIW, we generally declare bitmask flag variables as int, unless
>> there's some really good reason to do otherwise.
>
> OK, thanks for the tip.
>
> ...Robert
>

On Tue, 2008-10-28 at 23:45 -0400, Robert Haas wrote:

> One concern that I have about this approach is that the situation in
> which people are probably most concerned about COPY performance is
> restoring a dump. In that case, the COPY will be the only thing
> running, and using a BufferAccessStrategy is an anti-optimization. I
> don't think it's a very big effect (any testing anyone can do on real
> hardware rather than what I have would be appreciated) but I'm sort of
> unsold of optimizing for what I believe to be the less-common use
> case. If the consensus is to reverse course on this point I'm happy
> to rip those changes back out and resubmit; they are a relatively
> small proportion of the patch.

Having COPY use a BAS is mainly to ensure it doesn't swamp the cache.
Which is a gain in itself.

If you say its a loss you should publish timings to support that. Using
a BAS for VACUUM was a performance gain, not a loss.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

> If you say its a loss you should publish timings to support that. Using
> a BAS for VACUUM was a performance gain, not a loss.

Well, I can dig up and publish the timings from my laptop, but I'm not
sure where that will get us. Trust me, the numbers were higher with
BAS, otherwise I wouldn't be worrying about this. But I pretty much
doubt anyone cares how my laptop runs PostgreSQL anyway, which is why
I think someone should test this on good hardware and see what happens
there. The only change I made to disable the BAS was a one-line
change in GetBulkInsertState to replace BAS_BULKWRITE with BAS_NORMAL,
so it should be easy for someone to try it both ways.

Not at any point in the development of this patch was I able to match
the 15-17% copy speedup, 20% CTAS speedup that you cited with your
original email. I did get speedups, but they were considerably
smaller. So either my testing methodology is different, or my
hardware is different, or there is something wrong with my patch. I
don't think we're going to find out which it is until someone other
than me looks at this.

In any event, VACUUM is a read-write workload, and specifically, it
tends to write pages that have been written by other writers, and are
therefore potentially already in shared buffers. COPY and CTAS are
basically write-only workloads, though with COPY on an existing table
the FSM might guide you to free space on a page already in shared
buffers, or you might find an index page you need there. Still, if
you are doing a large bulk data load, those effects are probably
pretty small. So, the profile is somewhat.

I'm not really trying to argue that the BAS is a bad idea, but it is
certainly true that I do not have the data to prove that it is a good
idea.

...Robert

On Wed, 2008-10-29 at 21:58 -0400, Robert Haas wrote:
> > If you say its a loss you should publish timings to support that. Using
> > a BAS for VACUUM was a performance gain, not a loss.
>
> Well, I can dig up and publish the timings from my laptop, but I'm not
> sure where that will get us. Trust me, the numbers were higher with
> BAS, otherwise I wouldn't be worrying about this. But I pretty much
> doubt anyone cares how my laptop runs PostgreSQL anyway, which is why
> I think someone should test this on good hardware and see what happens
> there. The only change I made to disable the BAS was a one-line
> change in GetBulkInsertState to replace BAS_BULKWRITE with BAS_NORMAL,
> so it should be easy for someone to try it both ways.
>
> Not at any point in the development of this patch was I able to match
> the 15-17% copy speedup, 20% CTAS speedup that you cited with your
> original email. I did get speedups, but they were considerably
> smaller. So either my testing methodology is different, or my
> hardware is different, or there is something wrong with my patch. I
> don't think we're going to find out which it is until someone other
> than me looks at this.
>
> In any event, VACUUM is a read-write workload, and specifically, it
> tends to write pages that have been written by other writers, and are
> therefore potentially already in shared buffers. COPY and CTAS are
> basically write-only workloads, though with COPY on an existing table
> the FSM might guide you to free space on a page already in shared
> buffers, or you might find an index page you need there. Still, if
> you are doing a large bulk data load, those effects are probably
> pretty small. So, the profile is somewhat.
>
> I'm not really trying to argue that the BAS is a bad idea, but it is
> certainly true that I do not have the data to prove that it is a good
> idea.

You should try profiling the patch. You can count the invocations of the
buffer access routines to check its all working in the right ratios.

Whatever timings you have are worth publishing.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

> You should try profiling the patch. You can count the invocations of the
> buffer access routines to check its all working in the right ratios.

*goes and learns how to do profile PostgreSQL*

OK, that was a good suggestion. It looks like part of my problem here
is that I didn't put the CREATE TABLE and the COPY into the same
transaction. As a result, a lot of time was spent on XLogInsert.
Modified the test case, new profiling results attached.

...Robert

> Whatever timings you have are worth publishing.

Here are the timings for copying the first ten million integers into a
one-column table created in the same transaction, with and without the
patch. As you can see, now that I've corrected my previous error of
not putting CREATE TABLE and COPY in the same transaction, the savings
are quite substantial, about 15%. Nice!

Trunk:
Time: 18931.516 ms
Time: 18251.732 ms
Time: 17284.274 ms
Time: 15900.131 ms
Time: 16439.617 ms

Patch:
Time: 14852.123 ms
Time: 15673.759 ms
Time: 15776.450 ms
Time: 14160.266 ms
Time: 13374.243 ms

...Robert

On Thu, 2008-10-30 at 23:05 -0400, Robert Haas wrote:
> > Whatever timings you have are worth publishing.
>
> Here are the timings for copying the first ten million integers into a
> one-column table created in the same transaction, with and without the
> patch. As you can see, now that I've corrected my previous error of
> not putting CREATE TABLE and COPY in the same transaction, the savings
> are quite substantial, about 15%. Nice!

I had faith. ;-)

Can you test whether using the buffer access strategy is a win or a
loss? Most of that gain is probably coming from the reduction in
pinning.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

On Thu, 2008-10-30 at 22:46 -0400, Robert Haas wrote:
> > You should try profiling the patch. You can count the invocations of the
> > buffer access routines to check its all working in the right ratios.
>
> *goes and learns how to do profile PostgreSQL*
>
> OK, that was a good suggestion. It looks like part of my problem here
> is that I didn't put the CREATE TABLE and the COPY into the same
> transaction. As a result, a lot of time was spent on XLogInsert.
> Modified the test case, new profiling results attached.

The CPU time in XLogInsert can be confusing. The WAL writes can make
COPY I/O bound and so any savings on CPU may have been masked in the
earlier tests.

Patched profile shows we can still save a further 20% by writing data
block-at-a-time. That's more complex because we'd need to buffer the
index inserts also, or it would optimise only for the no-index (initial
load) case. So I think this is definitely enough for this release.

Using the buffer access strategy is going to be a big win for people
running large data loads in production and it will also help with people
running parallel load tasks (e.g. Dimitri's pg_loader). That effect is
more subtle and harder to measure, but it's an important consideration.

Thanks very much for finishing the patch in time for commitfest.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

> Can you test whether using the buffer access strategy is a win or a
> loss? Most of that gain is probably coming from the reduction in
> pinning.

Patch resnapped to HEAD, with straightforward adjustments to
compensate for Heikki's changes to the ReadBuffer interface. See
attached.

New testing results, now with and without BAS:

--TRUNK--
Time: 17945.523 ms
Time: 18682.172 ms
Time: 17047.841 ms
Time: 16344.442 ms
Time: 18727.417 ms

--PATCHED--
Time: 13323.772 ms
Time: 13869.724 ms
Time: 14043.666 ms
Time: 13934.132 ms
Time: 13193.702 ms

--PATCHED with BAS disabled--
Time: 14460.432 ms
Time: 14745.206 ms
Time: 14345.973 ms
Time: 14601.448 ms
Time: 16535.167 ms

I'm not sure why the BAS seemed to be slowing things down before.
Maybe it's different if we're copying into a pre-existing table, so
that WAL is enabled? Or it could have just been a fluke - the numbers
were close. I'll try to run some additional tests if time permits.

...Robert

On Sat, 2008-11-01 at 13:23 -0400, Robert Haas wrote:
> > Can you test whether using the buffer access strategy is a win or a
> > loss? Most of that gain is probably coming from the reduction in
> > pinning.
>
> --PATCHED--
> Time: 13869.724 ms (median)

> --PATCHED with BAS disabled--
> Time: 14460.432 ms (median with outlier removed)

That seems a conclusive argument in favour. Small additional performance
gain. plus generally beneficial behaviour for concurrent loads.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support

"Robert Haas" <robertmhaas(at)gmail(dot)com> writes:
> Patch resnapped to HEAD, with straightforward adjustments to
> compensate for Heikki's changes to the ReadBuffer interface. See
> attached.

I looked this over a bit. A couple of suggestions:

1. You could probably simplify life a bit by treating the
BulkInsertState as having an *extra* pin on the buffer, ie, do
IncrBufferRefCount when saving a buffer reference in BulkInsertState and
ReleaseBuffer when removing one. Changing a buffer's local pin count
from 1 to 2 or back again is quite cheap, so you wouldn't need to
special-case things to avoid the existing pin and release operations.
For instance this diff hunk goes away:

***************
*** 1963,1969 ****

END_CRIT_SECTION();

! UnlockReleaseBuffer(buffer);

/*
* If tuple is cachable, mark it for invalidation from the caches in case
--- 1987,1996 ----

END_CRIT_SECTION();

! /* Release the lock, but keep the buffer pinned if doing bulk insert. */
! LockBuffer(buffer, BUFFER_LOCK_UNLOCK);
! if (!bistate)
! ReleaseBuffer(buffer);

/*
* If tuple is cachable, mark it for invalidation from the caches in case

2. The logic changes in RelationGetBufferForTuple seem bizarre and
overcomplicated. ISTM that the buffer saved by the bistate ought to
be about equivalent to relation->rd_targblock, ie, it's your first
trial location and also a place to save the located buffer on the way
out. I'd suggest tossing that part of the patch and starting over.

regards, tom lane

> 2. The logic changes in RelationGetBufferForTuple seem bizarre and
> overcomplicated. ISTM that the buffer saved by the bistate ought to
> be about equivalent to relation->rd_targblock, ie, it's your first
> trial location and also a place to save the located buffer on the way
> out. I'd suggest tossing that part of the patch and starting over.

Hmm, would that be safe in the presence of concurrent or recursive
bulk inserts into the same relation?

...Robert

"Robert Haas" <robertmhaas(at)gmail(dot)com> writes:
>> 2. The logic changes in RelationGetBufferForTuple seem bizarre and
>> overcomplicated. ISTM that the buffer saved by the bistate ought to
>> be about equivalent to relation->rd_targblock, ie, it's your first
>> trial location and also a place to save the located buffer on the way
>> out. I'd suggest tossing that part of the patch and starting over.

> Hmm, would that be safe in the presence of concurrent or recursive
> bulk inserts into the same relation?

As safe as it is now --- you're relying on the bistate to carry the
query-local state. Probably the best design is to just ignore
rd_targblock when a bistate is provided, and use the bistate's buffer
instead.

regards, tom lane

OK, here's an updated version...

1. Use IncrBufferRefCount() so that we can do unconditional
ReleaseBuffers elsewhere. I'm not sure this is really any simpler,
and although IncrBufferRefCount() is pretty cheap, it's certainly not
as cheap as a NULL pointer test.

2. Consolidate a bunch of logic into a new function
RelationReadBuffer. This simpifies the logic in
RelationGetBufferForTuple() considerably.

3. Make RelationGetBufferForTuple ignore relation->rd_block in favor
of bistate->last_pin whenever possible. Changing this to also not
bother setting relation->rd_block didn't seem worthwhile, so I didn't.

...Robert

On Tue, Nov 4, 2008 at 4:18 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Robert Haas" <robertmhaas(at)gmail(dot)com> writes:
>>> 2. The logic changes in RelationGetBufferForTuple seem bizarre and
>>> overcomplicated. ISTM that the buffer saved by the bistate ought to
>>> be about equivalent to relation->rd_targblock, ie, it's your first
>>> trial location and also a place to save the located buffer on the way
>>> out. I'd suggest tossing that part of the patch and starting over.
>
>> Hmm, would that be safe in the presence of concurrent or recursive
>> bulk inserts into the same relation?
>
> As safe as it is now --- you're relying on the bistate to carry the
> query-local state. Probably the best design is to just ignore
> rd_targblock when a bistate is provided, and use the bistate's buffer
> instead.
>
> regards, tom lane
>

"Robert Haas" <robertmhaas(at)gmail(dot)com> writes:
> OK, here's an updated version...

Applied with some small stylistic revisions.

regards, tom lane