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

[Mark Kirkwood]

Luke Lonergan wrote:
>
> Now someone runs an ad-hoc query that is going to take a much longer time to
> run, say one day.  Every following ad-hoc query will wait the whole day for
> the queue to clear.
>
> The proven way to remove this issue is to time share the single queue slot
> for some number of runnable entries, say two.  During each time slot, say
> one minute, each runnable statement in the queue will rotate into execution.
> After it's time is done, a SIGSUSPEND is sent to it's PID and the next
> runnable statement rotates in and so on.
>
>
>   

Guys - v.interesting posts, I spent a while thinking about 'em!

However I see a serious issue with the time sharing idea. While it  
solves the queue congestion problem, I think it gets us back to resource 
overload again:

Suppose N queries, each of which requires M of some resource are in a 
queue. If we force the queue to be handled serially only M of the 
resource is in use at any time. If however, we time shared them all, 
then we can have up to NM of the resource in use - depending on whether 
the resources are required upfront by the queries, or consumed steadily 
throughout the execution cycle.

Now memory is typically required upfront -  so time sharing will force 
the machine to swap in all likelihood - possibly swapping *more* 
aggressively than if we let them all run together... getting us back to 
why we wanted queuing in the first place... In the case of IO, the time 
sharing may well flush the buffer cache for each query in turn and spoil 
any read-ahead that was working! However in the case of cpu the time 
sharing will work perfectly - but the standard cpu time slicing 
algorithms handle this fine anyway!

So, I don't see it as a win.

Cheers

Mark
Mark