SEO

Patch to implement buffer cache recycling for scans, as being discussed
on pgsql-hackers.

Applies cleanly to cvstip, passes make installcheck when used by default
for all SeqScans. Tested with scan_recycle_buffers = 1,4,8,16

Should be regarded as WIP. Presumably there are some failure conditions
that require the buffer to be reset; these have not yet been considered.

No docs.

SET scan_recyle_buffers = N
default = 0

8 <= N <= 64 would yield benefits according to earlier results

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Simon Riggs wrote:
> Patch to implement buffer cache recycling for scans, as being discussed
> on pgsql-hackers.

A few questions come to mind:

How does it behave with Jeff's synchronized seq scans patch?

I wonder if calling RelationGetNumberOfBlocks on every seq scan becomes
a performance issue for tiny tables with for example just 1 page. It
performs an lseek, which isn't free.

What happens if multiple backends choose overlapping sets of buffers to
recycle?

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

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Fri, 2007-03-09 at 20:08 +0000, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > Patch to implement buffer cache recycling for scans, as being discussed
> > on pgsql-hackers.
>
> A few questions come to mind:

Good questions. I don't expect this will go through easily, so we need
to examine these thoughts thoroughly.

> How does it behave with Jeff's synchronized seq scans patch?

I've offered Jeff lots of support throughout that patch's development
and its a feature I'd like to see. The current synch scan patch relies
upon the cache spoiling effect to gain its benefit. I think that can be
tightened up, so that we can make both work.

Currently synch scans help DSS apps but not OLTP. This patch reduces the
negative effects of VACUUM on OLTP workloads, as well as helping DSS.

> I wonder if calling RelationGetNumberOfBlocks on every seq scan becomes
> a performance issue for tiny tables with for example just 1 page. It
> performs an lseek, which isn't free.

Jeff's patch does this also, for similar reasons.

> What happens if multiple backends choose overlapping sets of buffers to
> recycle?

They won't. If a buffer is pinned, it will fall out of the the list of
buffers being recycled and not be reused. So they will each tend towards
a unique list of buffers.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

Heikki Linnakangas <heikki@enterprisedb.com> writes:
> I wonder if calling RelationGetNumberOfBlocks on every seq scan becomes
> a performance issue for tiny tables with for example just 1 page. It
> performs an lseek, which isn't free.

We do that anyway; but certainly Simon's patch ought not be injecting
an additional one.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On Fri, 2007-03-09 at 16:45 -0500, Tom Lane wrote:
> Heikki Linnakangas <heikki@enterprisedb.com> writes:
> > I wonder if calling RelationGetNumberOfBlocks on every seq scan becomes
> > a performance issue for tiny tables with for example just 1 page. It
> > performs an lseek, which isn't free.
>
> We do that anyway; but certainly Simon's patch ought not be injecting
> an additional one.

It should be possible to pass that down from the planner to the
executor, in certain cases. Or at least pass down the possibility that
such a check might be worthwhile.

Another approach might be to make the call after the first ~10 I/Os on a
SeqScan, after which an lseek will be just noise. That way an
all-in-cache scan would never need it at all. Thats easy to arrange
because the hint is invoked from the exec nodes themselves.

We probably need to get some measurements for the main benefit of the
patch before we look further into those thoughts.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

"Simon Riggs" <simon@2ndquadrant.com> writes:
> On Fri, 2007-03-09 at 16:45 -0500, Tom Lane wrote:
>> We do that anyway; but certainly Simon's patch ought not be injecting
>> an additional one.

> It should be possible to pass that down from the planner to the
> executor, in certain cases.

Huh? See HeapScanDesc->rs_nblocks.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Fri, 2007-03-09 at 20:37 +0000, Simon Riggs wrote:
> > I wonder if calling RelationGetNumberOfBlocks on every seq scan becomes
> > a performance issue for tiny tables with for example just 1 page. It
> > performs an lseek, which isn't free.
>
> Jeff's patch does this also, for similar reasons.
>

As Tom pointed out, the value is already in memory by the time it gets
to my code. My code just reads that value from memory.

Regards,
Jeff Davis


---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

On Fri, 2007-03-09 at 20:08 +0000, Heikki Linnakangas wrote:
> Simon Riggs wrote:
> > Patch to implement buffer cache recycling for scans, as being discussed
> > on pgsql-hackers.
>
> A few questions come to mind:
>
> How does it behave with Jeff's synchronized seq scans patch?
>

I will test it and post my results. I would expect that the CPU usage
will increase, but it might not make a big difference in the overall
cache hit rate if you count OS buffer cache hits.

Regards,
Jeff Davis


---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org

On Fri, 2007-03-09 at 18:05 -0500, Tom Lane wrote:
> "Simon Riggs" <simon@2ndquadrant.com> writes:
> > On Fri, 2007-03-09 at 16:45 -0500, Tom Lane wrote:
> >> We do that anyway; but certainly Simon's patch ought not be injecting
> >> an additional one.
>
> > It should be possible to pass that down from the planner to the
> > executor, in certain cases.
>
> Huh? See HeapScanDesc->rs_nblocks.

Many thanks.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

On Sat, 2007-03-10 at 07:59 +0000, Simon Riggs wrote:
> On Fri, 2007-03-09 at 18:05 -0500, Tom Lane wrote:
> > "Simon Riggs" <simon@2ndquadrant.com> writes:
> > > On Fri, 2007-03-09 at 16:45 -0500, Tom Lane wrote:
> > >> We do that anyway; but certainly Simon's patch ought not be injecting
> > >> an additional one.
> >
> > > It should be possible to pass that down from the planner to the
> > > executor, in certain cases.
> >
> > Huh? See HeapScanDesc->rs_nblocks.
>
> Many thanks.

New patch enclosed, implementation as you've requested.

Not ready to apply yet, but good for testing.

COPY command now also uses this hint, to allow test results and
discussion. Others could also, perhaps needing different values.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend