Re: Statement Queuing - Mailing list pgsql-hackers

Something that would be extremely useful to add to the first pass of
this would be to have a work_mem limiter. This would allow users to set
work_mem much more aggressively without worrying about pushing the
machine to swapping. That capability alone would make this valuable to a
very large number of our users.

On Mon, Jul 10, 2006 at 11:02:58AM +1200, Mark Kirkwood wrote:
> A while ago in connection with the 8.2 planning [1] there was some
> discussion of resource management and statement queuing [2].
> 
> I am currently looking at implementing a resource management/queuing
> module for Greenplum - initially targeting Bizgres, but I'm thinking it
> could be beneficial for non-Bizgres (i.e Postgresql) users too.
> 
> There has been some discussion on the Bizgres list already [3] together
> with some WIP code [4] and a review [5].
> 
> the code is a little rough - representing my getting to grips with the
> standard lock mechanism in order to produce enough of a prototype to
> study the issues. In that light I would very much appreciate comments
> concerning the design itself and also feedback for the questions posted
> in the review [4] - either here, the Bizgres-general list or both.
> 
> Here is a lightning overview of this whole resource queuing/scheduling
> business to hopefully put it in context (very short version of [3]):
> 
> For certain workloads (particularly DSS and reporting), the usual
> controls (max_connections or a connection pool, work_mem etc) are not
> really enough to stop the situation where several simultaneously
> executing expensive queries temporarily cripple a system. This is
> particularly the case where user specified queries are permitted. What
> is needed is some way to throttle or queue these queries in some finer
> manner - such as (simple case) restricting the number of simultaneously
> executing queries, or restricting the total cost of all simultaneously
> executing queries (others are obviously possible, these are just the
> simplest).
> 
> To make this work a new object - a resource queue is proposed, which
> holds limits and current counters for resources, plus a new sort of
> lock, something like a standard one, but instead of deterministic
> conflict rules based on lockmethod, a check on the counter/total for the
> relevant resource is performed instead.
> 
> best wishes
> 
> Mark
> 
> [1] http://archives.postgresql.org/pgsql-hackers/2006-03/msg01122.php
> [2] http://archives.postgresql.org/pgsql-hackers/2006-03/msg00821.php
> [3] http://pgfoundry.org/pipermail/bizgres-general/2006-June/000492.html
> [4]
> http://homepages.paradise.net.nz/markir/download/bizgres/bizgres-resschedular-06-29.patch
> [5]
> http://lists.pgfoundry.org/pipermail/bizgres-general/2006-July/000521.html
> 
> 
> 
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 9: In versions below 8.0, the planner will ignore your desire to
>       choose an index scan if your joining column's datatypes do not
>       match
> 

-- 
Jim C. Nasby, Sr. Engineering Consultant      jnasby@pervasive.com
Pervasive Software      http://pervasive.com    work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf       cell: 512-569-9461