Re: Cost limited statements RFC - Mailing list pgsql-hackers

On Thu, May 23, 2013 at 7:27 PM, Greg Smith <greg@2ndquadrant.com> wrote:
> 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.

Cool.  We have an outstanding customer request for this type of
functionality; although in that case, I think the desire is more along
the lines of being able to throttle writes rather than reads.

But I wonder if we wouldn't be better off coming up with a little more
user-friendly API.  Instead of exposing a cost delay, a cost limit,
and various charges, perhaps we should just provide limits measured in
KB/s, like dirty_rate_limit = <amount of data you can dirty per
second, in kB> and read_rate_limit = <amount of data you can read into
shared buffers per second, in kB>.  This is less powerful than what we
currently offer for autovacuum, which allows you to come up with a
"blended" measure of when vacuum has done too much work, but I don't
have a lot of confidence that it's better in practice.

> 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.

I haven't looked at this in detail, but I would hope it's not that
bad.  For one thing, many DDL commands don't do any significant I/O in
the first place and so can probably be disregarded.  Those that do are
mostly things that rewrite the table and things that build indexes.  I
doubt there are more than 3 or 4 code paths to patch.

> 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.

Well, we *could* have a system where, if someone blocks waiting for a
lock held by a rate-limited process, the rate limits are raised or
abolished.  But I'm pretty sure that's a bad idea.  I think that the
people who want rate limits want them because allowing too much write
(or maybe read?) activity hoses the performance of the entire system,
and that's not going to be any less true if there are multiple jobs
piling up.  Let's say someone has a giant COPY into a huge table, and
CLUSTER blocks behind it, waiting for AccessExclusiveLock.  Well...
making the COPY run faster so that we can hurry up and start
CLUSTER-ing seems pretty clearly wrong.  We want the COPY to run
slower, and we want the CLUSTER to run slower, too.  If we don't want
that, then, as you say, we shouldn't set the GUC in the first place.

Long story short, I'm inclined to define this as expected behavior.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company