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