Re: shared_buffers/effective_cache_size on 96GB server

On Wed, Oct 10, 2012 at 4:37 PM, Claudio Freire <klaussfreire(at)gmail(dot)com> wrote:
> On Wed, Oct 10, 2012 at 7:33 PM, Jeff Janes <jeff(dot)janes(at)gmail(dot)com> wrote:
>>> Well, the real question is whether, while traversing the index, if some
>>> of the pages are going to be removed from the cache by other process
>>> cache usage. effective_cache_size is not figuring the cache will remain
>>> between queries.
>>
>> Does anyone see effective_cache_size make a difference anyway? If so,
>> in what circumstances?
>
> In my case, if I set it too high, I get impossibly suboptimal plans
> when an index scan over millions of rows hits the disk way too often
> way too randomly. The difference is minutes for a seqscan vs hours for
> the index scan. In fact, I prefer setting it too low than too high.

There's a corollary for very fast disk subsystems. If you've got say
40 15krpm disks in a RAID-10 you can get sequential read speeds into
the gigabytes per second, so that sequential page access costs MUCH
lower than random page access, to the point that if seq page access is
rated a 1, random page access should be much higher, sometimes on the
order of 100 or so. I.e. sequential accesses are almost always
preferred, especially if you're getting more than a tiny portion of
the table at one time.

As for the arguments for / against having a swap space, no one has
mentioned the one I've run into on many older kernels, and that is
BUGs. I have had to turn off swap on very large mem machines with
2.6.xx series kernels in the past. These machines all had properly
set vm.* settings for dirty buffers and percent etc. Didn't matter,
as after 2 to 4 weeks of hard working uptimes, I'd get an alert on the
db server for high load, log in, and see kswapd working its butt off
doing... NOTHING. Load would be in the 50 to 150 range. iostat showed
NOTHING in terms of si/so/bi/bo and so on. kswapd wasn't in a D
(iowait) state, but rather R, pegging a CPU core at 100% while
running, and apparently blocking a lot of other processes that wanted
to access memory, all of which were S(leeping) or R(unning). Two
seconds after a sudo swapoff -a completed and my machine went back to
a load of 2 to 5 as was normal for it. Honestly if you're running out
of memory on a machine with 256G and needing swap, you've got other
very real memory usage issues you've been ignoring to get to that
point.

Are all those bugs fixed in the 3.0.latest kernels? Not sure, but I
haven't had this issue on any big memory servers lately and they've
all had swap turned on.