Thread: Statement Queuing
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
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
Jim C. Nasby wrote: > 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. > > Right - in principle it is not that difficult to add (once I have the machinery for the cost limiter going properly that is). I thinking we could either: 1. Add hooks to count work_mem allocations where they happen, or 2. Scan the plan tree and deduce how many work_mem allocations there will be. 1. might be tricky, because I'm taking the resource lock before the executor is actually run (beginning of PortalRun), so 2. might be the most workable approach. However as I understand it, this sounds very like Simon's shared work_mem proposal, and the major issue there (as I understood it) was that for many/most(?) OSes free(3) doesn't synchronously release memory back to OSes free list - it may only be immediately reusable for the process that actually freed it (in some cases it may only *ever* be reusable for the process that freed it - until that process terminates of course). Now it may be for DSS workloads that the freed memory gets back to the free list "quickly enough", or that this type of work_mem limiting - even though not entirely accurate in its memory arithmetic, is "good enough" to prevent OOM situations - clearly some time will need to be spent checking this for the various platforms. These factors may make it better to aim for the simple count + cost limiters first, and *then* look at the memory one. Cheers Mark
Mark Kirkwood <markir@paradise.net.nz> writes:
> Right - in principle it is not that difficult to add (once I have the
> machinery for the cost limiter going properly that is). I thinking we
> could either:
> 1. Add hooks to count work_mem allocations where they happen, or
> 2. Scan the plan tree and deduce how many work_mem allocations there
> will be.
The problem with this is that many of the cost models depend on
work_mem, so you can't simply arbitrarily alter the setting
after-the-fact. At least not if you don't want to kill performance.
regards, tom lane
Tom Lane wrote: > Mark Kirkwood <markir@paradise.net.nz> writes: >> Right - in principle it is not that difficult to add (once I have the >> machinery for the cost limiter going properly that is). I thinking we >> could either: > >> 1. Add hooks to count work_mem allocations where they happen, or >> 2. Scan the plan tree and deduce how many work_mem allocations there >> will be. > > The problem with this is that many of the cost models depend on > work_mem, so you can't simply arbitrarily alter the setting > after-the-fact. At least not if you don't want to kill performance. > Right - the intention would be to merely count the number of work_mem allocations against some type of total, rather than modifying work_mem itself. Cheers Mark