On Wed, Oct 10, 2012 at 4:37 PM, Claudio Freire <klaussfreire@xxxxxxxxx> wrote: > On Wed, Oct 10, 2012 at 7:33 PM, Jeff Janes <jeff.janes@xxxxxxxxx> 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. -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
