pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedRunning some query in low priority
8+ messages / 5 participants
[nested] [flat]
* Running some query in low priority
@ 2009-11-05 09:36 S Arvind <arvindwill@gmail.com>
2009-11-05 09:38 ` Re: Running some query in low priority Grzegorz Jaśkiewicz <gryzman@gmail.com>
2009-11-05 18:02 ` Re: Running some query in low priority Scott Marlowe <scott.marlowe@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: S Arvind @ 2009-11-05 09:36 UTC (permalink / raw)
To: pgsql-performance
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*
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Running some query in low priority
2009-11-05 09:36 Running some query in low priority S Arvind <arvindwill@gmail.com>
@ 2009-11-05 09:38 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2009-11-05 11:32 ` Re: Running some query in low priority Greg Stark <gsstark@mit.edu>
1 sibling, 1 reply; 8+ messages in thread
From: Grzegorz Jaśkiewicz @ 2009-11-05 09:38 UTC (permalink / raw)
To: S Arvind <arvindwill@gmail.com>; +Cc: pgsql-performance
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.
--
GJ
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Running some query in low priority
2009-11-05 09:36 Running some query in low priority S Arvind <arvindwill@gmail.com>
2009-11-05 09:38 ` Re: Running some query in low priority Grzegorz Jaśkiewicz <gryzman@gmail.com>
@ 2009-11-05 11:32 ` Greg Stark <gsstark@mit.edu>
2009-11-05 13:20 ` Re: Running some query in low priority S Arvind <arvindwill@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Greg Stark @ 2009-11-05 11:32 UTC (permalink / raw)
To: Grzegorz Jaśkiewicz <gryzman@gmail.com>; +Cc: S Arvind <arvindwill@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Running some query in low priority
2009-11-05 09:36 Running some query in low priority S Arvind <arvindwill@gmail.com>
2009-11-05 09:38 ` Re: Running some query in low priority Grzegorz Jaśkiewicz <gryzman@gmail.com>
2009-11-05 11:32 ` Re: Running some query in low priority Greg Stark <gsstark@mit.edu>
@ 2009-11-05 13:20 ` S Arvind <arvindwill@gmail.com>
2009-11-05 13:25 ` Re: Running some query in low priority Grzegorz Jaśkiewicz <gryzman@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: S Arvind @ 2009-11-05 13:20 UTC (permalink / raw)
To: pgsql-performance
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@mit.edu>
> 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
>
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Running some query in low priority
2009-11-05 09:36 Running some query in low priority S Arvind <arvindwill@gmail.com>
2009-11-05 09:38 ` Re: Running some query in low priority Grzegorz Jaśkiewicz <gryzman@gmail.com>
2009-11-05 11:32 ` Re: Running some query in low priority Greg Stark <gsstark@mit.edu>
2009-11-05 13:20 ` Re: Running some query in low priority S Arvind <arvindwill@gmail.com>
@ 2009-11-05 13:25 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2009-11-05 13:39 ` Re: Running some query in low priority Matthew Wakeling <matthew@flymine.org>
0 siblings, 1 reply; 8+ messages in thread
From: Grzegorz Jaśkiewicz @ 2009-11-05 13:25 UTC (permalink / raw)
To: S Arvind <arvindwill@gmail.com>; +Cc: pgsql-performance
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.
--
GJ
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Running some query in low priority
2009-11-05 09:36 Running some query in low priority S Arvind <arvindwill@gmail.com>
2009-11-05 09:38 ` Re: Running some query in low priority Grzegorz Jaśkiewicz <gryzman@gmail.com>
2009-11-05 11:32 ` Re: Running some query in low priority Greg Stark <gsstark@mit.edu>
2009-11-05 13:20 ` Re: Running some query in low priority S Arvind <arvindwill@gmail.com>
2009-11-05 13:25 ` Re: Running some query in low priority Grzegorz Jaśkiewicz <gryzman@gmail.com>
@ 2009-11-05 13:39 ` Matthew Wakeling <matthew@flymine.org>
2009-11-05 13:42 ` Re: Running some query in low priority Grzegorz Jaśkiewicz <gryzman@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Matthew Wakeling @ 2009-11-05 13:39 UTC (permalink / raw)
To: Grzegorz Jaśkiewicz <gryzman@gmail.com>; +Cc: S Arvind <arvindwill@gmail.com>; pgsql-performance
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Running some query in low priority
2009-11-05 09:36 Running some query in low priority S Arvind <arvindwill@gmail.com>
2009-11-05 09:38 ` Re: Running some query in low priority Grzegorz Jaśkiewicz <gryzman@gmail.com>
2009-11-05 11:32 ` Re: Running some query in low priority Greg Stark <gsstark@mit.edu>
2009-11-05 13:20 ` Re: Running some query in low priority S Arvind <arvindwill@gmail.com>
2009-11-05 13:25 ` Re: Running some query in low priority Grzegorz Jaśkiewicz <gryzman@gmail.com>
2009-11-05 13:39 ` Re: Running some query in low priority Matthew Wakeling <matthew@flymine.org>
@ 2009-11-05 13:42 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Grzegorz Jaśkiewicz @ 2009-11-05 13:42 UTC (permalink / raw)
To: Matthew Wakeling <matthew@flymine.org>; +Cc: S Arvind <arvindwill@gmail.com>; pgsql-performance
2009/11/5 Matthew Wakeling <matthew@flymine.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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: Running some query in low priority
2009-11-05 09:36 Running some query in low priority S Arvind <arvindwill@gmail.com>
@ 2009-11-05 18:02 ` Scott Marlowe <scott.marlowe@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Scott Marlowe @ 2009-11-05 18:02 UTC (permalink / raw)
To: S Arvind <arvindwill@gmail.com>; +Cc: pgsql-performance
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2009-11-05 18:02 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-11-05 09:36 Running some query in low priority S Arvind <arvindwill@gmail.com>
2009-11-05 09:38 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2009-11-05 11:32 ` Greg Stark <gsstark@mit.edu>
2009-11-05 13:20 ` S Arvind <arvindwill@gmail.com>
2009-11-05 13:25 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2009-11-05 13:39 ` Matthew Wakeling <matthew@flymine.org>
2009-11-05 13:42 ` Grzegorz Jaśkiewicz <gryzman@gmail.com>
2009-11-05 18:02 ` Scott Marlowe <scott.marlowe@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox