On Thu, May 23, 2013 at 7:27 PM, Greg Smith <g...@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 -- Sent via pgsql-hackers mailing list (firstname.lastname@example.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers