On Aug 30, 2006, at 12:26 PM, Jim C. Nasby wrote:
You misunderstand how effective_cache_size is used. It's the *only*memory factor that plays a role in cost estimator functions. This meansit should include the memory set aside for caching in shared_buffers. Also, hibufspace is only talking about filesystem buffers in FreeBSD, which AFAIK has nothing to do with total memory available for caching, since VM pages are also used to cache data.
Curious... See Message-ID: <20031011092308.GA39942@xxxxxxxxxxxxxxx> from the October 2003 archives. (I'd provide a full link to it, but the http://archives.postgresql.org/pgsql-performance/ archives are botched -- only some posts are on the browsable archive but it is all in the raw mailbox download, so that's the only way to get the full message.) It reads in part:
From: Sean Chittenden <sean@xxxxxxxxxxxxxx> Date: Sat, 11 Oct 2003 02:23:08 -0700 [...] > echo "effective_cache_size = $((`sysctl -n vfs.hibufspace` / 8192))" > > I've used it for my dedicated servers. Is this calculation correct? Yes, or it's real close at least. vfs.hibufspace is the amount of kernel space that's used for caching IO operations (minus the necessary space taken for the kernel). If you're real paranoid, you could do some kernel profiling and figure out how much of the cache is actually disk IO and multiply the above by some percentage, say 80%? I haven't found it necessary to do so yet. Since hibufspace is all IO and caching any net activity is kinda pointless and I assume that 100% of it is used for a disk cache and don't use a multiplier. The 8192, however, is the size of a PG page, so, if you tweak PG's page size, you have to change this constant (*grumbles*). --END QUOTE--Given who Sean is, I tend to believe him. Whether this is still valid for FreeBSD 6.x, I'm unable to verify.
Basically, your best bet for setting effective_cache_size is to use the total memory in the machine, and substract some overhead for the OS andother processes. I'll typically subtract 1G.
I'll give this a whirl and see if it helps.Any opinions on using the FreeBSD sysctl kern.ipc.shm_use_phys to bypass the VM system for shared pages?
Attachment:
smime.p7s
Description: S/MIME cryptographic signature
