Thread: "set nice" running a transaction in a lower priority?
I was wondering if there is a way to set transaction priority so that it takes its good sweet time and handles other queries quickly? I have an "import" process that takes a while, and I don't mind having the user who is doing the import wait; but it would be nice to have the box more responsive while the import is occuring. Best, Clark
On a related topic: Is it possible to run a background query (I use JDBC)? For example, I would like to run a query which I know takes a long time to process, without holding the connection open and/or synchronously waiting for the query response. This is related to the "import" because I typically do my import to a temp table, then INSERT...SELECT into the real table which is very slow because of all the indexes which have to be handled. Thanks, Doug
On Wednesday 12 Jun 2002 6:33 pm, Clark C . Evans wrote: > I was wondering if there is a way to set transaction > priority so that it takes its good sweet time and > handles other queries quickly? Not really - and it might be counter-productive to slow down the import. If another transaction can't complete until the import is finished then everything could end up delayed. I guess you could nice the pid of the relevant backend process, but I'd be inclined to batch the import into sections of 5000 records or so and sleep for a second between batches. - Richard Huxton
Richard Huxton <dev@archonet.com> writes: > On Wednesday 12 Jun 2002 6:33 pm, Clark C . Evans wrote: >> I was wondering if there is a way to set transaction >> priority so that it takes its good sweet time and >> handles other queries quickly? > Not really - and it might be counter-productive to slow down the import. If > another transaction can't complete until the import is finished then > everything could end up delayed. > I guess you could nice the pid of the relevant backend process, That would be a bad idea for the same reason --- you'd get into priority-inversion scenarios where foreground processes are waiting for the low-priority process, which can't run because other foreground processes are hogging all the CPU. > but I'd be > inclined to batch the import into sections of 5000 records or so and sleep > for a second between batches. Some kind of throttle on the client side seems like a better idea to me too. regards, tom lane
On Thu, Jun 13, 2002 at 09:09:46AM -0400, Tom Lane wrote: | Some kind of throttle on the client side seems like a better idea Thanks. This is exactly what is needed. ;) Clark