I'm working on a new project here that I wanted to announce, just to
keep from duplicating effort in this area. I've started to add a cost
limit delay for regular statements. The idea is that you set a new
statement_cost_delay setting before running something, and it will
restrict total resources the same way autovacuum does. I'll be happy
with it when it's good enough to throttle I/O on SELECT and CREATE INDEX
CONCURRENTLY.
Modifying the buffer manager to account for statement-based cost
accumulation isn't difficult. The tricky part here is finding the right
spot to put the delay at. In the vacuum case, it's easy to insert a
call to check for a delay after every block of I/O. It should be
possible to find a single or small number of spots to put a delay check
in the executor. But I expect that every utility command may need to be
modified individually to find a useful delay point. This is starting to
remind me of the SEPostgres refactoring, because all of the per-command
uniqueness ends up requiring a lot of work to modify in a unified way.
The main unintended consequences issue I've found so far is when a cost
delayed statement holds a heavy lock. Autovacuum has some protection
against letting processes with an exclusive lock on a table go to sleep. It won't be easy to do that with arbitrary
statements. There's a
certain amount of allowing the user to shoot themselves in the foot here
that will be time consuming (if not impossible) to eliminate. The
person who runs an exclusive CLUSTER that's limited by
statement_cost_delay may suffer from holding the lock too long. But
that might be their intention with setting the value. Hard to idiot
proof this without eliminating useful options too.
--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com
