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

[Gavin Sherry]

On Sun, 6 Aug 2006, Luke Lonergan wrote:

> Mark,
>
> > Right, but if our time slicing conflicts or confuses the
> > OS's, or is not really lessening the resource load, then we
> > are losing out.
>
> 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.

A novel way to solve this problem might be to introduce the concept of a
pool of temporary storage devices. Say we had 4 partitions in our pool and
4 concurrent queries doing external sort then we could do each
concurrently without conflicting IO (IO bus aside). This will not scale
but it does have the benefit of zero overhead.

Thanks,

Gavin