Re: 8.4 open item: copy performance regression?

On Sat, 2009-06-20 at 02:53 -0400, Greg Smith wrote:
> On Sat, 20 Jun 2009, Simon Riggs wrote:
>
> > At the time, I also proposed a "filled buffer list" change to bufmgr to
> > allow bgwriter to preferentially target COPY's filled blocks, which
> > would also help with this effect.
>
> One of the things I keep meaning to investigate is whether there's any
> benefit to pushing buffers the background writer cleans onto the free
> list, to speed up their subsequent allocation to needy backends. Both
> this and the sequential scan ring buffer implementation might both benefit
> from an approach where buffers are similarly pushed onto the free list
> when they're no longer needed by the process that pulled them in.
> Buffers could move from allocated->used->filled buffer list->free list in
> the COPY buffer case, and allocated->used->free list when executing a
> sequential scan.

The reason for not doing that would be that we don't know that the
blocks are free to use; we know very little about them. The longer we
leave them the more likely they are to be reused, so putting buffers
onto the freelist when they aren't actually free would likely make the
cache less effective, ISTM.

With filled buffers from COPY we have a very good clue that the buffers
will no longer be needed and can treat them differently from others.
Also, if we can get the bgwriter to do some of the work then we would
have the COPY process, bgwriter and WALwriter all working together on
the data loading. (We need to make WALwriter a little smarter also, so
that it can respond to changing WAL write workloads).

> That would seem to me to be a more robust general approach for solving
> this class of problem than the whole ring buffer idea, which is a great
> start but bound to run into situations where the size of the buffer just
> isn't right anymore a few hardware generations down the road.

The ring buffer optimises L2 cache, not the buffer cache in general. If
we put buffers back on the freelist that is the same as having one
global ring buffer, which would then spoil the benefit for L2 on
multi-CPU systems.

We don't see any L2 benefit with COPY yet, but it is potentially there
if we can overcome the stronger effect of the WAL costs. When the ring
buffer expands to minimise WAL overheads we also minimise benefit for
L2.

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