From: Grzegorz Jaśkiewicz <gryzman@gmail.com>
To: Matthew Wakeling <matthew@flymine.org>
Cc: S Arvind <arvindwill@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Running some query in low priority
Date: Thu, 5 Nov 2009 13:42:56 +0000
Message-ID: <2f4958ff0911050542m479791c6lb0772f23a474cd37@mail.gmail.com> (raw)
In-Reply-To: <alpine.DEB.2.00.0911051335410.19472@aragorn.flymine.org>
References: <abf9211d0911050136g48739f0ft53b63f96cddf706@mail.gmail.com>
<2f4958ff0911050138n3f246b7ck6182cb702ac48e91@mail.gmail.com>
<407d949e0911050332n5ef355c1y156dcd26aa28d222@mail.gmail.com>
<abf9211d0911050520t72a5d6dfv877ced07077b11@mail.gmail.com>
<2f4958ff0911050525w678925uc8f63fe966951ef5@mail.gmail.com>
<alpine.DEB.2.00.0911051335410.19472@aragorn.flymine.org>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: gryzman@gmail.com, matthew@flymine.org, arvindwill@gmail.com
Subject: Re: Running some query in low priority
In-Reply-To: <2f4958ff0911050542m479791c6lb0772f23a474cd37@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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