Thread: Running some query in low priority
Hi Developers and Tuners,
Is there any way to run some query in low priority and some query in higher priority in pg. The main reason for this is i need my main application(high priority) to be undisturbed by the sub application(low priority) which is running on same DB. Is there anyother good way to operate this?
Arvind S
"Many of lifes failure are people who did not realize how close they were to success when they gave up."
-Thomas Edison
Is there any way to run some query in low priority and some query in higher priority in pg. The main reason for this is i need my main application(high priority) to be undisturbed by the sub application(low priority) which is running on same DB. Is there anyother good way to operate this?
Arvind S
"Many of lifes failure are people who did not realize how close they were to success when they gave up."
-Thomas Edison
On Thu, Nov 5, 2009 at 9:36 AM, S Arvind <arvindwill@gmail.com> wrote:
other than manually re-nicing back end, no.
Hi Developers and Tuners,
Is there any way to run some query in low priority and some query in higher priority in pg. The main reason for this is i need my main application(high priority) to be undisturbed by the sub application(low priority) which is running on same DB. Is there anyother good way to operate this?
other than manually re-nicing back end, no.
--
GJ
2009/11/5 Grzegorz Jaśkiewicz <gryzman@gmail.com>: > > > On Thu, Nov 5, 2009 at 9:36 AM, S Arvind <arvindwill@gmail.com> wrote: >> >> Hi Developers and Tuners, >> Is there any way to run some query in low priority and some query >> in higher priority in pg. The main reason for this is i need my main >> application(high priority) to be undisturbed by the sub application(low >> priority) which is running on same DB. Is there anyother good way to operate >> this? > > other than manually re-nicing back end, no. And unfortunately this doesn't really work very well. renicing only affects cpu priority and usually it's i/o priority you want to adjust. Even if you can adjust i/o priority per process on your operating system the database often does i/o work for one process in another process or has times when a process is waiting on another process to finish i/o. So lowering the i/o priority of the low priority process might not have the desired effect of speeding up other processes. Usually this isn't a problem unless you have a large batch load or something like that happening which consumes all available i/o. In that case you can sometimes reduce the i/o demand by just throttling the rate at which you send data to or read data from the server. -- greg
Thank Greg and Grzegorz,
As told i have large batch load to the postgres which i need to be run in low priority. Is it really throttling the data will help to lower the postgres workload for some queries?
-Arvind S
As told i have large batch load to the postgres which i need to be run in low priority. Is it really throttling the data will help to lower the postgres workload for some queries?
-Arvind S
2009/11/5 Greg Stark <gsstark@mit.edu>
2009/11/5 Grzegorz Jaśkiewicz <gryzman@gmail.com>:And unfortunately this doesn't really work very well. renicing only>
>
> On Thu, Nov 5, 2009 at 9:36 AM, S Arvind <arvindwill@gmail.com> wrote:
>>
>> Hi Developers and Tuners,
>> Is there any way to run some query in low priority and some query
>> in higher priority in pg. The main reason for this is i need my main
>> application(high priority) to be undisturbed by the sub application(low
>> priority) which is running on same DB. Is there anyother good way to operate
>> this?
>
> other than manually re-nicing back end, no.
affects cpu priority and usually it's i/o priority you want to adjust.
Even if you can adjust i/o priority per process on your operating
system the database often does i/o work for one process in another
process or has times when a process is waiting on another process to
finish i/o. So lowering the i/o priority of the low priority process
might not have the desired effect of speeding up other processes.
Usually this isn't a problem unless you have a large batch load or
something like that happening which consumes all available i/o. In
that case you can sometimes reduce the i/o demand by just throttling
the rate at which you send data to or read data from the server.
--
greg
On Thu, Nov 5, 2009 at 1:20 PM, S Arvind <arvindwill@gmail.com> wrote:
Thank Greg and Grzegorz,
As told i have large batch load to the postgres which i need to be run in low priority. Is it really throttling the data will help to lower the postgres workload for some queries?
depends on what you are actually trying to achieve.
If it is an insert of some sort, than divide it up. If it is a query that runs over data, use limits, and do it in small batches. Overall, divide in conquer approach works in these scenarios.
If it is an insert of some sort, than divide it up. If it is a query that runs over data, use limits, and do it in small batches. Overall, divide in conquer approach works in these scenarios.
--
GJ
On Thu, 5 Nov 2009, Grzegorz Jaśkiewicz wrote:
> If it is an insert of some sort, than divide it up. If it is a query that runs over data,
> use limits, and do it in small batches. Overall, divide in conquer approach works in
> these scenarios.
Unfortunately, dividing the work up can cause a much greater load, which
would make things worse. If you are inserting in smaller chunks and
committing more frequently that can reduce performance. If you split up
queries with limit and offset, that will just multiply the number of times
the query has to be run. Each time, the query will be evaluated, the first
<offset> rows thrown away, and the next <limit> rows returned, which will
waste a huge amount of time.
If you are inserting data, then use a COPY from stdin, and then you can
throttle the data stream. When you are querying, declare a cursor, and
fetch from it at a throttled rate.
Matthew
--
Bashir: The point is, if you lie all the time, nobody will believe you, even
when you're telling the truth. (RE: The boy who cried wolf)
Garak: Are you sure that's the point, Doctor?
Bashir: What else could it be? -- Star Trek DS9
Garak: That you should never tell the same lie twice. -- Improbable Cause
2009/11/5 Matthew Wakeling <matthew@flymine.org>
as with everything, you have to find the right balance. I think he is looking for low impact, not speed. So he has to trade one for another. Find a small enough batch size, but not too small, cos like you said - things will have too much impact otherwise.
On Thu, 5 Nov 2009, Grzegorz Jaśkiewicz wrote:Unfortunately, dividing the work up can cause a much greater load, which would make things worse. If you are inserting in smaller chunks and committing more frequently that can reduce performance. If you split up queries with limit and offset, that will just multiply the number of times the query has to be run. Each time, the query will be evaluated, the first <offset> rows thrown away, and the next <limit> rows returned, which will waste a huge amount of time.If it is an insert of some sort, than divide it up. If it is a query that runs over data,
use limits, and do it in small batches. Overall, divide in conquer approach works in
these scenarios.
If you are inserting data, then use a COPY from stdin, and then you can throttle the data stream. When you are querying, declare a cursor, and fetch from it at a throttled rate.
as with everything, you have to find the right balance. I think he is looking for low impact, not speed. So he has to trade one for another. Find a small enough batch size, but not too small, cos like you said - things will have too much impact otherwise.
--
GJ
On Thu, Nov 5, 2009 at 2:36 AM, S Arvind <arvindwill@gmail.com> wrote: > Hi Developers and Tuners, > Is there any way to run some query in low priority and some query > in higher priority in pg. The main reason for this is i need my main > application(high priority) to be undisturbed by the sub application(low > priority) which is running on same DB. Is there anyother good way to operate > this? Are you IO or CPU bound? If CPU bound get more CPUs. If IO bound see about getting more IO, specifically a fast RAID controller with Battery Backed Cache, and a fair number of fast hard drives in a RAID-10. Trying to throttle one thing to get the others to run faster can only buy you so much time. As load increases you'll need more CPU or IO. If the thing you're doing is CPU intensive, and it needs lots of CPUs, then look at some form of replication to other boxes to throw more CPUs at the problem.