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

[Mark Kirkwood]

Luke Lonergan wrote:
>
>
> Of course, but your points below miss the objective of the queue - we're
> not worried about CPU or memory sharing, it's disk sharing of a certain
> kind that causes non-linear effects.
>
> Sorting queries reading and writing at the same time are too difficult
> for the predictive read-ahead algorithms in the OS I/O scheduler to
> handle.  As a consequence, two sorting queries running at once will slow
> each other down by a non-linear factor caused by the change from
> sequential access to random access.  We regularly see a factor of 3-4
> slowdown (above and beyond the linear) in practice.
>
>
>   

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

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

Cheers

Mark