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

[Luke Lonergan]

Mark,

> Thinking about this a bit more - and generating a bit more 
> noise on this thread :-o , in this case the slicing might 
> actually *help* a bit, as it would have a similar effect on 
> IO scheduling for database files themselves (i.e fewer 
> concurrent readers).  Of course there are many subtle effects 
> that could nullify this (plan types that re-read their recent 
> input and are therefore helped by caching - which slicing
> *hurts*) and environmental factors (statements from other 
> queues hitting the same database files and spoiling the 
> possible gains).

This is actually where the OS prefetch is usually intelligent enough to
handle the workload.

Again from lots of example cases, simple scans with predicate kinds of
queries seem to benefit somewhat from concurrency.  They benefit greatly
when they are sharing access to cachable tables and so should be run
concurrently.

The problem with the negative interaction cases is that the I/O
scheduler is not able to gather I/Os into larger groups (I/O coalescing)
perhaps because of the insertion of mixed writes and reads (generally
associated with sorting, possibly also with hash joins).

In a recent example, some ad-hoc queries (joins against fact tables with
GROUP BY / ORDER BY clauses) were run together alongside ELT queries
(INSERT ... SELECT) while reporting queries were running.  The ELT
workload and the reporting queries run without negative interference
(ELT does not push each instance to it's write rate limit on MPP), but
the ad-hoc queries were slowing down by 2-3 times greater than they
would if there were simple CPU sharing.  As a consequence, other queries
also slowed down non-linearly, basically the whole system ran 3 times
less throughput as a consequence of the 3 ad-hoc queries running
simultaneously.

>From the user's perspective - the reporting queries would take 90
seconds instead of 30, the ad-hoc queries would take 60 minutes instead
of 20, etc.

If the ad-hoc queries were in a single threaded or time sliced queue,
the whole system would run 3 times faster and would be predictable.

Now - phase 2: if a hoggy query goes into the single threaded queue,
then the two other ad-hoc users would have to wait until he's done.
This is the scenario we've talked through.

- Luke