Re: Synchronized Scan benchmark results

On Tue, 2007-04-03 at 10:37 -0700, Jeff Davis wrote:
> > > The primary aspect of my patch, the Synchronized Scanning, performed
> > > great though. Even the CFQ scheduler, that does not appear to properly
> > > read ahead, performed substantially better than plain 8.2.3. And even
> > > better, Simon's patch didn't seem to hurt Synchronized Scans at all.
> > >
> > > Out of the 36 runs I did, a couple appear anomalous. I will retest those
> > > soon.
> >
> > Which ones were they?
>
> This one stood out to me:
>
> * Machine 1, Linux AS, Sync Scan patch + Recycle Buffers patch, single
> scan: 204s
>
> Compared to these tests:
>
> * Machine 1, Linux AS, Sync Scan patch + Recycle Buffers patch, scan.rb:
> all 5 scans are below 170s.
>
> * Machine 1, Linux AS, Sync Scan patch only, scan.rb: 165s.
>
> That makes no sense to me, so it's probably a fluke (by which I mean
> some other activity on the system, perhaps swapping some large
> applications). The second two tests are consistent with all the other
> numbers I got, but the first one took 40 seconds longer than I would
> expect. I'll do a simple re-test tonight.

What did you set scan_recycle_buffers to? The default was zero.

I think v2 of the patch interpreted that setting as meaning attempt to
reuse the same buffer again immediately, which probably wouldn't be
optimal. Which is why I issued v3... I think you'll need to set
scan_recycle_buffers = 0 (==off in v3) and scan_recycle_buffers = 32 to
get sensible comparison figures.

So please can you use v3 for any further testing. Thanks.

> > I would like to see some tests with different queries that have varying
> > I/O and CPU requirements to see if they stay together too. That won't
> > block the patch, but it will help everybody understand what the range of
> > real world applicability there is in this. I'd guess this can benefit us
> > sufficiently frequently in most cases that its worth it.
>
> I'll do some more varied tests. The best idea I've come up with so far
> is to do something that requires random seeking going concurrently with
> the scans.

No, what I mean is different kinds of scans:
- a simple scan like count(*)
- a more complex one that does buckets of cycles per tuple
- a hash join

In particular, select count(*) isn't very representative. Using all Hash
Joins would be a much better test - since IMHO that case is the common
use case for this feature.

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