Re: Seq scans roadmap

Just to keep you guys informed, I've been busy testing and pondering
over different buffer ring strategies for vacuum, seqscans and copy.
Here's what I'm going to do:

Use a fixed size ring. Fixed as in doesn't change after the ring is
initialized, however different kinds of scans use differently sized rings.

I said earlier that it'd be invasive change to see if a buffer needs a
WAL flush and choose another victim if that's the case. I looked at it
again and found a pretty clean way of doing that, so I took that
approach for seq scans.

1. For VACUUM, use a ring of 32 buffers. 32 buffers is small enough to
give the L2 cache benefits and keep cache pollution low, but at the same
time it's large enough that it keeps the need to WAL flush reasonable
(1/32 of what we do now).

2. For sequential scans, also use a ring of 32 buffers, but whenever a
buffer in the ring would need a WAL flush to recycle, we throw it out of
the buffer ring instead. On read-only scans (and scans that only update
hint bit) this gives the L2 cache benefits and doesn't pollute the
buffer cache. On bulk updates, it's effectively the current behavior. On
scans that do some updates, it's something in between. In all cases it
should be no worse than what we have now. 32 buffers should be large
enough to leave a "cache trail" for Jeff's synchronized scans to work.

3. For COPY that doesn't write WAL, use the same strategy as for
sequential scans. This keeps the cache pollution low and gives the L2
cache benefits.

4. For COPY that writes WAL, use a large ring of 2048-4096 buffers. We
want to use a ring that can accommodate 1 WAL segment worth of data, to
avoid having to do any extra WAL flushes, and the WAL segment size is
2048 pages in the default configuration.

Some alternatives I considered but rejected:

* Instead of throwing away dirtied buffers in seq scans, accumulate them
in another fixed sized list. When the list gets full, do a WAL flush and
put them to the shared freelist or a backend-private freelist. That
would eliminate the cache pollution of bulk DELETEs and bulk UPDATEs,
and it could be used for vacuum as well. I think this would be the
optimal algorithm but I don't feel like inventing something that
complicated at this stage anymore. Maybe for 8.4.

* Using a different sized ring for 1st and 2nd vacuum phase. Decided
that it's not worth the trouble, the above is already an order of
magnitude better than the current behavior.

I'm going to rerun the performance tests I ran earlier with new patch,
tidy it up a bit, and submit it in the next few days. This turned out to
be even more laborious patch to review than I thought. While the patch
is short and in the end turned out to be very close to Simon's original
patch, there's many different usage scenarios that need to be catered
for and tested.

I still need to check the interaction with Jeff's patch. This is close
enough to Simon's original patch that I believe the results of the tests
Jeff ran earlier are still valid.

--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com