Re: How to reduce impact of a query.

Howard Cole wrote:

> Thanks for the input Scott. You are correct - I am IO bound, but only
> for the query described. 99% of the time, my IO runs at 3% or less, even
> during peak times, only this one query, which happens approximately 10
> times a day grinds the system to a halt.

If your I/O is normally that idle, surely one big query shouldn't stop
everything? Slow it down, maybe, but stop it?

Perhaps your RAID drivers, hardware (if any) or OS are deeply queuing
requests and/or doing lots of readahead, probably to make sequential I/O
benchmarks and random I/O throughput benchmarks that don't consider
request latency look better.

Consider turning down I/O queue depths if possible, and/or tuning
readhead to something suitable for your I/O loads. The latter will
require some work to find the right balance between random request
latency and sequential I/O throughput.

I had HUGE problems with a 3Ware 8500-8 RAID controller queuing requests
very deeply inside the Linux driver its self, rather than the OS's I/O
scheduler, causing high priority small I/O to be stuck behind long
series of low priority bulk reads and writes since the driver wasn't
aware of the OS's I/O priority mechanisms. I ended up modifying the
driver a little to reduce the queue depth since there wasn't a runtime
param for it, and the result was VASTLY improved I/O latency with only a
very small cost to throughput.

It'd be nice if the controller had the brains to be told "fetch this
block, and read this one too if it happens to be on the way" ... but if
there's anything like that out there, with hardware or driver level I/O
priority awareness, I haven't run into it yet. Alas.

By the way, it'd be REALLY nice if the postmaster had the facility to
set CPU and I/O nice levels for the backends individually (on Linux and
other supporting platforms). I'm currently using a user C function
linked into the backend to set the nice level, but it'd be a great thing
to have built-in. Would patches for this be considered, with the
functions being no-ops (with warnings?) on non-supporting platforms?

I also think it's a wee bit of a pity that there's no way to tell Pg
that a job isn't important, so data shouldn't be permitted to push much
else out of shared_buffers or the OS's cache. The latter can be ensured
to an extent, at least on Linux, with posix_fadvise(...,
POSIX_FADV_NOREUSE) or with madvise(...). The former is presumably
possible with proper work_mem (etc) settings, but I find it's the OS's
habit of filling the cache with gigabytes of data I won't need again
that's the real problem. I don't know how this'd work when interacting
with other backends doing other work with the same tables, though.

--
Craig Ringer