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*

On Thu, Nov 5, 2009 at 9:36 AM, S Arvind <arvindwill(at)gmail(dot)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.

--
GJ

2009/11/5 Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>:
>
>
> On Thu, Nov 5, 2009 at 9:36 AM, S Arvind <arvindwill(at)gmail(dot)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

2009/11/5 Greg Stark <gsstark(at)mit(dot)edu>

> 2009/11/5 Grzegorz Jaśkiewicz <gryzman(at)gmail(dot)com>:
> >
> >
> > On Thu, Nov 5, 2009 at 9:36 AM, S Arvind <arvindwill(at)gmail(dot)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
>

On Thu, Nov 5, 2009 at 1:20 PM, S Arvind <arvindwill(at)gmail(dot)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.

--
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(at)flymine(dot)org>

> 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.
>

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(at)gmail(dot)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.