Re: Synchronized Scan benchmark results - Mailing list pgsql-hackers

On Tue, 2007-04-03 at 10:01 +0100, Simon Riggs wrote:
> On Mon, 2007-04-02 at 16:14 -0700, Jeff Davis wrote:
> 
> > The results are very positive and quite conclusive.
> 
> Can we show some summary results?

I should be able to make some graphs today.

> I'm happy that the scans stay together all the way around, even handling
> the max-> 0 blockid transition well. So definite winner for me.

Yes, I was happy with the results.

> > However, the "sync_seqscan_offset" aspect of my patch, which attempts to
> > use pages that were cached before the scan began, did not show a lot of
> > promise. That aspect of my patch may end up being cut.
> 
> Yes, please remove :-)

Ok.

> > 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.

> > Note: I posted the versions of the patches that I used for the tests on
> > the page above. The version of Simon's patch that I used did not apply
> > cleanly to 8.2.3, but the only problem appeared to be in copy.c, so I
> > went ahead with the tests. If this somehow compromised the patch, then
> > let me know.
> 
> [It was never designed to apply cleanly to 8.2.3, as we might guess]
> That was v2, the current v3 should be OK because I removed the
> experimental COPY interaction. That will not have affected the tests.

Good to know.

> 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. 

Pgbench would probably be a good idea too, since it's more standard.

Regards,Jeff Davis