Re: How to reduce impact of a query.

On Mon, Nov 17, 2008 at 8:42 AM, Howard Cole <howardnews(at)selestial(dot)com> wrote:
> Scott Marlowe wrote:
>>
>> Your entire disk io subsystem is a pair of hard drives. I'm assuming
>> software RAID.
>
> Correct.
>>>
>>> The time that this query takes is not the issue, rather it is the impact
>>> that it has on the server - effectively killing it for the 40 seconds due
>>> to
>>> the heavy disk access.
>>>
>>
>> You either need to invest more into your drive subsystem so it can
>> handle parallel load better, or you need to create a slave db with
>> slony or londiste so that the ugly queries hit the slave.
>>
>> ____
>
> I take your point Scott. But short of the hardware upgrade, is there a way
> to effectively throttle this query? If I reduce maintenance_work_mem then
> the database dump/restore is slower but there is less overall impact on the
> server. Is there some equivalent parameter on the server to throttle general
> queries? It would be unfortunate if all queries slowed down a bit, but a
> better outcome than having the entire server hang for 40 seconds.

The problem is most likely you're I/O bound. If one query is hitting
a table it can pull in data (sequentially) at 40 to 80 megabytes per
second. Since most of your queries are small, they don't run into
each other a lot, so to speak. As soon as your big reporting query
hits it's likely hitting the drives much longer and getting in the way
of all the other queries.

You could add more ram, that should help since the kernel could then
fit much more of your data into ram and not be as dependent on your
drive subsystem. Memory is cheap, even FBDIMMS are pretty inexpensive
nowadays for 4 to 8 gigs of ram in a server.

While you can nice the backend process with some kind of script it
doesn't generally affect io priority. Some oses do allow process
priority to dictate io priority, but I'm pretty sure linux doesn't.
It still might help a little, but right now you need to either add
enough ram for the kernel to cache the majority of your data set or
get more drives.