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

[Jim C. Nasby]

On Thu, Jun 15, 2006 at 10:55:03AM -0700, Josh Berkus wrote:
 
> 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.
     
You just provided the best argument for not making this database-wide:
it would mean that you could never mix OLTP and DSS workloads in the
same database, and it would impose a tremendous hit on OLTP connections..

I don't think query queueing makes any sense in an OLTP environment. If
you want to limit how much load an OLTP app places on the database, you
should do it by limiting connections.

That said, the concept of total_query_cost is interesting... that might
make a lot more sense than simply limiting the number of queries.

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

Only if you're giving users direct access to the database. In a pure DSS
system I can see how this could be a problem, but a lot of times it's
very handy to allow processes to change work_mem for a given query.
-- 
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