Re: performance problems.

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
> means
> it 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(dot)GA39942(at)perrin(dot)nxad(dot)com>
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(at)chittenden(dot)org>
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
> and
> other 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?