Re: How to reduce impact of a query.

Scott Marlowe wrote:
> On Mon, Nov 17, 2008 at 11:10 PM, Craig Ringer
> <craig(at)postnewspapers(dot)com(dot)au> wrote:
>
>> 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(...).

Unfortunately, this isn't as useful as I'd initially hoped:

http://kerneltrap.org/node/7563 :

Quoting Torvalds:

"So O_DIRECT not only is a total disaster from a design standpoint (just
look at all the crap it results in), it also indirectly has hurt better
interfaces. For example, POSIX_FADV_NOREUSE (which _could_ be a useful
and clean interface to make sure we don't pollute memory unnecessarily
with cached pages after they are all done) ends up being a no-op ;/"

Darn.

> It could be that in the OP's case the data set for the big query is so
> big it blows out share_buffers completely / most of the way, and then
> I/O for the other data has to hit the drives instead of memory and
> that's why they're so slow.

shared_buffers alone shouldn't be *too* bad (right?), but if it's
pushing data out of the OS's cache as well (or the OS does a bad job of
caching disk reads) then that'd really hurt, yeah.

--
Craig Ringer