Skip site navigation (1) Skip section navigation (2)

Implementing cost limit/delays for insert/delete/update/select

From: Peter Schuller <peter(dot)schuller(at)infidyne(dot)com>
To: pgsql-hackers(at)postgresql(dot)org
Subject: Implementing cost limit/delays for insert/delete/update/select
Date: 2008-08-25 20:39:07
Message-ID: 20080825203906.GA571@hyperion.scode.org (view raw or flat)
Thread:
Lists: pgsql-hackers
Hello,

I'd like to have a stab at implementing cost delays, for regular
INSERT/DELETE/UPDATE/SELECT. The motivation is roughly the same as for
VACUUM and the autovacuum limits; one may have application specific
bulk operations that need executing without adverseley affecting
latency/throughput of other operations.

I tentatively call this executing statements "nicely". A better naming
scheme might be a good idea...

The idea would be to introduce two GUC variables:

  - nice_cost_limit
  - nice_cost_delay

Which would be equivalent to their vacuum_* counterparts.

Upon executing an INSERT, UPDATE, DELETE or SELECT, one would
optionally specify a "NICELY" modifier to enable nice cost limits for
that statement. For example:

   DELETE NICELY FROM large_table WHERE id < 50000000

In the future I foresee also specifying a nice multiplier of some
kind, thus supporting variable niceness on a per-statement basis.

To avoid complexity, the initial version would not contain anything
similar to the autovacuum balancing of costs between separate vacuum
processes. The cost accounting would be entirely local to each
backend.

Firstly, any opinions on whether this is a good idea, or on whether
the above suggestions sound sensible?

Implementation:

Although the current cost delay support, in terms of variable naming,
suggest it is specific to vacuuming, I haven't found anything that is
really specific to this. As far as I can tell, an implementaiton would
entail:

* Adding the GUC variables
* Modifying the parser slightly to support the NICELY "modifier"
  (terminology?)
* Modify ExecPlan in backend/executor/execMain.c to contain accounting
  initialization and cleanup like backend/commands/vacuum.c's vacuum().
* Create an equivalent of the vacuum_delay_point() and call it in each
  loop iteration in ExecPlan().

And then costmetically, probably rename various things so that
non-vacuum specific cost accounting is no longer named as if it were.

Does this sound vaguely sensible? Is there an obvious show-stopper I
am missing?

-- 
/ Peter Schuller

PGP userID: 0xE9758B7D or 'Peter Schuller <peter(dot)schuller(at)infidyne(dot)com>'
Key retrieval: Send an E-Mail to getpgpkey(at)scode(dot)org
E-Mail: peter(dot)schuller(at)infidyne(dot)com Web: http://www.scode.org

Responses

pgsql-hackers by date

Next:From: Grant FinnemoreDate: 2008-08-25 20:57:19
Subject: Proposal to sync SET ROLE and pg_stat_activity
Previous:From: Tom LaneDate: 2008-08-25 20:01:19
Subject: Re: IN, BETWEEN, spec compliance, and odd operator names

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group