[Bizgres-general] Statement Queuing take II - Resource Scheduling

[Jim C. Nasby]

On Mon, Jun 12, 2006 at 02:59:03PM +1200, Mark Kirkwood wrote:
> Transaction level is attractive, because it is considerably simpler to
> implement and avoids any deadlock issues[1]. However it is highly
> desirable to be able to make additional decisions based on the nature of
> the statements contained in the transaction, which is not possible using
> this level of granularity alone. Also there is the possibility of of
> idle backends occupying a slot in the queue until they timeout, which is
> undesirable.
 
How horrible would it be to have a mode where an entire transaction was
entered into the backend before any actual execution took place? This
would allow for estimating the total work required be the transaction. I
think it could also have other benefits down the road, such as knowing
what tables would not be involved in a transaction, which means that
vaccum could ignore this long-running transaction when dealing with
them.

> A challenge for this sort of resource scheduling is in obtaining the
> information from the host OS platform in order to determine the limits - 
> a plugin or port may be needed for each one.
 
There's other things that would definately benefit from having this
information. I know there's been numerous times when it would be nice to
know if something had come from the OS cache or not, as one example.

> It is envisaged that there will be new parameters for each additional 
> limiter (e.g. max_work_memory=K for a memory limiter), these will also 
> queue similarly to the active statement case.
 
One thing that would be extremely handy to have very early on (which
wouldn't require any OS-specific plugins) is the ability to limit based
on work_mem consumption. The idea is to configure a role that's using
queueing so that it has a much larger work_mem setting available for
it's queries; but you would then need to limit when queries could run
based on both estimated and actual work_mem consumption.

Ideally, this would eventually extend down into operations that use
work_mem, so that if a per-statement or per-queue work_mem limit was
going to be exceeded, the backend would switch over to using disk.
 
> 1/ This resource management functionality is targeted at DSS/Data
> Warehouse workloads/systems - is it suitable for batch or financial 
 
In my experience, batch systems typically have their own set of controls
in place to limit concurrency, so I think the immediate benefit won't be
as large. The real question is: would this queueing system allow those
batch systems to be simplified.

Once we have the ability to hold statements (or transactions) based on
things like CPU utilization or I/O bandwidth, I think it becomes even
more valuable to batch processing.

> year-end type workloads too? (It is envisaged that there *will* be a 
> performance hit if enabled on a typical OLTP systems needing a high 
> level concurrent activity).

Will this hit exist even for roles that have queueing disabled?

> 2/ Is there a need for separate timeout parameters for these resource
> locks (or is two very similar parameters even more likely to confuse
> than a possible behavior change to the existing ones?).
 
One issue I'm worried about is a large statement in the middle of a
transaction getting queued and not running for a very long time (hours).
A timeout would remove that risk. Another possibility is a 'time-in',
where if a statement has been in the queue for too long, start running
everything in front of it in the queue, as well as itself (I don't think
we'd want to run something from the queue out of order).

> 3/ The resource locks will be released at statement finish - is this
> possible to detect reliably?
 
Not sure what you're asking here... surely you can tell when a statement
finishes?

> 4/ Do we want this ROLE related control, or should there be a global
> parameter that controls all connections except the superuser?
 
I can envisioun cases where that would be handy, but I think it best to
wait until people actually request it.

One question is what about roles assigned to other roles? Do you always
use the limits of the lowest role in the tree, or are limits cumulative?
In the future, I can see cumulative limits being very useful. I don't
think this should be implemented now, but it's probably worth
considering in the design and syntax.

> 5/ There is to be one lock per resource limit *and* ROLE, is this
> necessary? - could we work out the ROLE whenever we examine the queue?
> (would that be too big a performance hit)?
 
Is it bad to have one per limit and role? With only one kind of limit, I
don't see this as being a big deal.

> 6/ Is the decision to make the resource locks distinct from the standard 
> locks a good one? For instance it may make the deadlock detection code 
> more complex, and it probably requires an additional LOCKTABLE hash. On 
> the other had it could well increase robustness by not touching the lock 
> code.
 
Probably something best asked when this is brought up on -hackers.
-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby at pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461