Re: Implementing cost limit/delays for insert/delete/update/select - Mailing list pgsql-hackers
From | Gregory Stark |
---|---|
Subject | Re: Implementing cost limit/delays for insert/delete/update/select |
Date | |
Msg-id | 87iqtoai51.fsf@oxford.xeocode.com Whole thread Raw |
In response to | Implementing cost limit/delays for insert/delete/update/select (Peter Schuller <peter.schuller@infidyne.com>) |
Responses |
Re: Implementing cost limit/delays for
insert/delete/update/select
Re: Implementing cost limit/delays for insert/delete/update/select Re: Implementing cost limit/delays for insert/delete/update/select |
List | pgsql-hackers |
"Peter Schuller" <peter.schuller@infidyne.com> writes: > 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 I think the experience with vacuum was that cost_delay was a mistake. The only parameter users really ought to be messing with is cost_limit. Every time a user has posted about vacuum taking interminably long it was because they set *_cost_delay to something unreasonable. I suppose this could be selection bias since we would never hear about users who didn't set it unreasonably high. But I think we should consider removing the {auto,}vacuum_cost_delay parameter or at least hiding and undocumenting it. It's a foot-gun and serves no useful purpose that merely lowering the {auto,}vacuum_cost_limit can't serve equally well. > 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 Why not just have the GUC and leave it at that? SET nice_cost_limit = ... DELETE FROM ... SET nice_cost_limit = ... UPDATE ... ... > In the future I foresee also specifying a nice multiplier of some > kind, thus supporting variable niceness on a per-statement basis. Yeah, actually I think both the vacuum and this parameter need some further thought. They don't represent any sort of real world parameter that the user has any hope of knowing how to set except by trial-and-error. I think we would be better off with something like a vacuum_io_bandwidth_cap or something like that. Then the user has a hope of understanding what kind of numbers make sense. > * Adding the GUC variables > * Modifying the parser slightly to support the NICELY "modifier" > (terminology?) As I mentioned I don't think this is necessary. > * 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(). ExecutePlan? That's not often enough. You can easily construct plans that do massive sequential scans on the inner side of a join or in a subquery -- all of which happens before a single record is returned from ExecutePlan for a. You would have to test for whether it's time to sleep much more often. Possibly before every ExecProcNode call would be enough. Even then you have to worry about the i/o and cpu resources used by by tuplesort. And there are degenerate cases where a single ExecProcNode could do a lot of i/o such as a large scan looking for a single matching record. -- Gregory Stark EnterpriseDB http://www.enterprisedb.com Get trained by Bruce Momjian - ask me about EnterpriseDB'sPostgreSQL training!
pgsql-hackers by date: