Re: [PERFORM] performance problems.

[Vivek Khera]

On Aug 30, 2006, at 12:26 PM, Jim C. Nasby wrote:

You misunderstand how effective_cache_size is used. It's the *only*

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.

memory factor that plays a role in cost estimator functions. This means

Curious... See Message-ID: <[EMAIL PROTECTED]> 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 <[EMAIL PROTECTED]>
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?

smime.p7s
Description: S/MIME cryptographic signature