Re: [PERFORM] "nice"/low priority Query
Jim C. Nasby wrote: Actually, from what I've read 4.2BSD actually took priority into account when scheduling I/O. FWIW, you can set I/O priority in recent versions of the Linux kernel using ionice, which is part of RML's schedutils package (which was recently merged into util-linux). -Neil ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [PERFORM] "nice"/low priority Query
Tobias Brox wrote: [Tobias Brox - Tue at 06:04:34PM +0200] (...) and at one point in the code I'm even asking the database for "explain plan", grepping out the estimated cost number, and referring the user to take contact with the IT-dept if he really needs the report. But I digress. I just came to think about some more "dirty" tricks I can do. I have turned on stats collection in the configuration; now, if I do: select count(*) from pg_stat_activity where not current_query like '%'; or, eventually: select count(*) from pg_stat_activity where not current_query like '%' and query_start+'1 second' it will give a hint about how busy the database server is, thus I can eventually let the application sleep and retry if there are any other heavy queries in progress. Or - create a table with an estimated_cost column, when you start a new "heavy" query, insert that query's cost, then sleep SUM(estimated_cost)/100 secs or something. When the query ends, delete the cost-row. Hmm - actually rather than dividing by 100, perhaps make it a tunable value. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [PERFORM] "nice"/low priority Query
[Tobias Brox - Tue at 06:04:34PM +0200] > (...) and at one > point in the code I'm even asking the database for "explain plan", grepping > out the estimated cost number, and referring the user to take contact with > the IT-dept if he really needs the report. But I digress. I just came to think about some more "dirty" tricks I can do. I have turned on stats collection in the configuration; now, if I do: select count(*) from pg_stat_activity where not current_query like '%'; or, eventually: select count(*) from pg_stat_activity where not current_query like '%' and query_start+'1 second'http://archives.postgresql.org
Re: [PERFORM] "nice"/low priority Query
On Tue, Aug 02, 2005 at 12:19:30PM -0400, Tom Lane wrote: > Tobias Brox <[EMAIL PROTECTED]> writes: > > Is there any ways to give postgresql a hint that a > > particular SQL call should be run at lower priority? Since every db > > connection has a pid, I can manually run "renice" to scheduele it by the OS > > - but of course I can't do it manually all the time. > > And it won't help you anyway, because renice only affects CPU priority > not I/O scheduling ... which, by your description, is the real problem. Actually, from what I've read 4.2BSD actually took priority into account when scheduling I/O. I don't know if this behavior is still present in FreeBSD or the like, though. So depending on the OS, priority could play a role in determining I/O scheduling. -- Jim C. Nasby, Database Consultant [EMAIL PROTECTED] Give your computer some brain candy! www.distributed.net Team #1828 Windows: "Where do you want to go today?" Linux: "Where do you want to go tomorrow?" FreeBSD: "Are you guys coming, or what?" ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [PERFORM] "nice"/low priority Query
Tom Lane wrote: Tobias Brox <[EMAIL PROTECTED]> writes: Is there any ways to give postgresql a hint that a particular SQL call should be run at lower priority? Since every db connection has a pid, I can manually run "renice" to scheduele it by the OS - but of course I can't do it manually all the time. And it won't help you anyway, because renice only affects CPU priority not I/O scheduling ... which, by your description, is the real problem. I think the only thing that's likely to help much is trying to arrange that the "simple" queries only need to touch pages that are already in memory. Some playing around with shared_buffer sizing might help. Also, if you're not on PG 8.0.*, an update might help. Would it be useful to be able to re-use the vacuum_cost_xxx settings in 8.0 for this sort of thing? I'm thinking a long-running report query isn't that different from a vacuum. -- Richard Huxton Archonet Ltd ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [PERFORM] "nice"/low priority Query
Tobias Brox <[EMAIL PROTECTED]> writes: > Is there any ways to give postgresql a hint that a > particular SQL call should be run at lower priority? Since every db > connection has a pid, I can manually run "renice" to scheduele it by the OS > - but of course I can't do it manually all the time. And it won't help you anyway, because renice only affects CPU priority not I/O scheduling ... which, by your description, is the real problem. I think the only thing that's likely to help much is trying to arrange that the "simple" queries only need to touch pages that are already in memory. Some playing around with shared_buffer sizing might help. Also, if you're not on PG 8.0.*, an update might help. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
