[Bizgres-general] [ENG] Re: Statement Queuing take II - Resource Scheduling

[Luke Lonergan]

Josh,

On 6/17/06 1:04 PM, "Josh Berkus" <josh at agliodbs.com> wrote:

>> I was not thinking of amending the client protocol - the idea being that
>> queuing would be "transparent" to clients (there was the discussion
>> point about whether there should be resource queuing specific timeouts).
> 
> It's not going to be very "transparent" if the client has to wait, frozen, for
> 10 minutes while the queue clears.

That¹s what they'd expect anyway if they're trying to run big queries on a
system with other big queries running.

We have a recent example where a customer is trying to run 3 concurrent
heavy duty (sort-heavy, full table scan, 10TB) queries on the system at one
time.  Of course, since they are all contenting for use of the same disks
for temp write/read, they end up converting normally sequential disk access
to random access and they end up taking 30 times as long to run instead of
the expected 3 times.

If they were run one after another with queueing, the three would run in
exactly three times the time as one query would run - the desired result.

Say you have a query that takes ten minutes:

Run them all at once - 300 minutes.  Run them one at a time, 30 minutes for
the 3rd, 20 minutes for the second, 10 minutes for the first into the queue.
Average response time 15 minutes.

Put the queue into Postgres as Mark is doing - before patch: 300 minutes.
After patch: 30 minutes.  10x speedup.

Yes the third person in the queue will wait 30 minutes "frozen", so we'll
need admin views of the queues to see what's going on.

Also - small queries need to slip into "small" queues.

- Luke