[Bizgres-general] Off-list Re: [ENG] Re: Statement Queuing take II - Resource Scheduling (Running with cost and cursors)

[Luke Lonergan]

Mark, 

> Right, sorry- for the specific problem here, it might have 
> benefit - to time slice K of the sorters to make it easier on 
> the OS IO scheduler.  I guess the issue then is whether the 
> slicing management really allows the OS scheduler to  operate 
> unaffected by the previous slice's activity (or if not, how much)...

Yes.
 
> However I still think that even with no real memory pressure, 
> that while this could help the *sort* step, it may hurt  row 
> retrieval - as each query time slice may see a completely 
> cold cache (from previous mail - assuming the actual database 
> size > RAM, and each of the K queries scanning dataset is > 
> RAM/K, not unreasonable assumptions for DSS and queries whose 
> sort sets are large).

For (scans >> RAM), cache is irrelevant.

I believe that the amount of startup time for a suspended sort process
is very small, mostly the process swapping in a small number of pages,
on the order of a hundred milliseconds, perhaps up to a second at times.

This kind of time slicing would probably be used only for the heavy duty
queue, where queries interact negatively and you want to get some time
sharing to prevent starvation and more closely match fair share.

The lighter weight queues would run queries concurrently within some
limits defined by available memory resources, as they often interact
positively.

I think a time sharing mechanism is an inevitable outgrowth of this
resource management approach.

The reason it will work is that the DBMS is accurately aware of what the
operations being shared are, while the OS can not accurately predict it.
No amount of OS smarts will notice that there are really two statements
with a mix of CPU + large block read/write activity banging heads, but
the DBMS knows exactly when that happens.

We could possibly investigate time slicing at the sort level, but it
seems wrong to me.  It would seem that if we implement the time slicing
scheduler, we could use it for more circumstances than sort collisions.

- Luke