Robert Haas <robertmhaas(at)gmail(dot)com> wrote:
> Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
>> The second tier is implemented to run after a plan is chosen, and
>> may postpone execution of a query (or reduce the resources it is
>> allowed) if starting it at that time might overload available
>> resources.
>
> It seems like it might be helpful, before tackling what you're
> talking about here, to have some better tools for controlling
> resource utilization. Right now, the tools we have a pretty
> crude. You can't even nice/ionice a certain backend without
> risking priority inversion, and there's no sensible way to limit
> the amount of amount of working memory per-query, only per
> query-node.
>
> http://archives.postgresql.org/pgsql-hackers/2009-10/msg00125.php
I see your point, but it seems largely orthogonal:
(1) These issues wouldn't preclude a very simple but still useful
ACP which just limits the active connection count. This is really
what I most want, and would solve a problem frequently reported on
the lists.
(2) If the ACP had a hook to allow plugging new policies, it would
support development and testing of the types of measurement and
control you describe, not hinder it.
(3) You could get some useful benefit from an ACP which just
postponed queries when a memory-heavy plan was ready and a lot of
memory was already reserved by executing queries anticipated to be
memory-heavy. That is, you wouldn't need to solve the harder
problem of *limiting* memory usage to get benefit from being able to
roughly *estimate* memory usage.
Frankly, solving the problems you reference might be more work than
implementing true serializable transactions. (At least *I'm*
clueless about how to solve the memory allocation problems, and feel
relatively confident about how to deal with serializable
transactions.) I'm interested in ACPs because even the simplest
implementation could reduce the number of serialization errors in
some environments, improving performance in serializable isolation
level. If doing that is a first step in helping to solve the
problems you describe, I'll be happy to have helped. I don't think
our shop can afford to tackle everything you reference there,
however.
-Kevin