Re: Statement Queuing

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(at)pervasive(dot)com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461