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

[Josh Berkus]

Jim, Mark,

> How horrible would it be to have a mode where an entire transaction was
> entered into the backend before any actual execution took place? 

Beyond horrible.  Impossible.  Think of a stored procedure, where each 
executing query supplies data for the next.   How are we going to cost 
that transaction without executing it, exactly?

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

Funny, I'm looking (medium-term) to DTrace for being able to determine 
real-time host info.  At least on Solaris.

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

Yeah, Simon looked at this earlier.  I think he was unable to come up with 
any "worm_mem pool" idea that wouldn't cause horrible lock contention.

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

Sure.  However, I argee with Mark that the first step is the ability to 
limit the number of queries by Role.

> 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?

Lowest role in the tree.   Otherwise, it's a foot-gun.

So, I want to suggest an alternative approach to something:

Mark suggested a two-variable approach:

concurrent_queries == max number of simultaneously executing queries, per 
ROLE.
trivial_query_threshold == estimated query cost below which queries don't 
count towards the above

From a user perspective, it would be better to simplify this as:

total_query_cost == total cost of all queries to run simultaneously; beyond 
this cost, queries will queue up.

Regardless, I see some potential pitfalls with the whole resource 
allocation thing:

Locking: PostgreSQL already has issues with locking limiting our 
multi-processor scalability.   These resource limits would cause 
additional lock contention, since before executing each query you'd need 
to take one away from a list, and add it back after completion.
    This isn't as much of a concern with the low-volume, high-query-cost 
environment of DSS.  However, the contention would mean that this approach 
would be useful *only* for DSS, and would need to be disabled for OLTP and 
Web systems.  Further, that might relegate the allocator to /contrib, 
never to make it into the main code.
    
Permissions:  For RA to work, we need to be able to make some of the RA 
variables superuser-only-set.  This is an issue because PostgreSQL does 
not have a way, currently, to create superuser-only runtime-change 
variables, nor a way for a superuser to say "regular users can't change 
this userset variable".   Mind you, the latter would be good for 
PostgreSQL system management in general; it's *always* been a problem that 
Joe User can decide to up his work_mem to 524,000 if he wants to.

Deadlocking:  this has already been discussed.  This is a reason to do 
per-role thresholds; in an extreme case, one could give SPs their own 
Roles to prevent deadlocks.

Client Protocol Issues:  we would need to add a message to the client 
protocol which indicates "query accepted and queued".  Drivers would need 
to support this.   Then comes the question of, do we return control to the 
client?    If so, do they get to send another query?   What happens if 
results for the 1st query come back while the 2nd query is executing?  If 
we don't return control, don't we risk running out of connections?
  This is all the multiple-query-client protocol which has been previously 
discussed, and is a significant project in its own right.

-- 
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco