Re: Sync Scan update

On Tue, 2006-12-19 at 17:43 +0000, Simon Riggs wrote:
> On Tue, 2006-12-19 at 09:07 -0800, Jeff Davis wrote:
> > I have updated my Synchronized Scan patch and have had more time for
> > testing.
> >
> > Go to http://j-davis.com/postgresql/syncscan-results10.html
> > where you can download the patch, and see the benchmarks that I've run.
> >
> > The results are very promising. I did not see any significant slowdown
> > for non-concurrent scans or for scans that fit into memory, although I
> > do need more testing in this area.
>
> Yes, very promising.
>
> Like to see some tests with 2 parallel threads, since that is the most
> common case. I'd also like to see some tests with varying queries,
> rather than all use select count(*). My worry is that these tests all
> progress along their scans at exactly the same rate, so are likely to
> stay in touch. What happens when we have significantly more CPU work to
> do on one scan - does it fall behind??

Right, that's important. Hopefully the test you describe below sheds
some light on that.

> I'd like to see all testing use log_executor_stats=on for those
> sessions. I would like to know whether the blocks are being hit while
> still in shared_buffers or whether we rely on the use of the full
> filesystem buffer cache to provide performance.

Ok, will do.

> It would be very cool to run a background performance test also, say a
> pgbench run with a -S 100. That would show us what its like to try to
> run multiple queries when most of the cache is full with something else.

Do you mean '-S -s 100' or '-s 100'? Reading the pgbench docs it doesn't
look like '-S' takes an argument.

> It would be better to have a GUC to control the scanning
> e.g.
> synch_scan_threshold = 256MB
>
> rather than link it to effective_cache_size always, since that is
> related to index scan tuning.

I will make it completely unrelated to effective_cache_size. I'll do the
same with "sync_scan_start_offset" (by the way, does someone have a
better name for that?).

Regards,
Jeff Davis