Re: go for a script! / ex: PostgreSQL vs. MySQL - Mailing list pgsql-performance

From Sean Chittenden
Subject Re: go for a script! / ex: PostgreSQL vs. MySQL
Date
Msg-id 20031011092308.GA39942@perrin.nxad.com
Whole thread Raw
In response to Re: go for a script! / ex: PostgreSQL vs. MySQL  (Christopher Kings-Lynne <chriskl@familyhealth.com.au>)
Responses Re: go for a script! / ex: PostgreSQL vs. MySQL  (Vivek Khera <khera@kcilink.com>)
List pgsql-performance
> >NB> shared_buffers = 1/16th of total memory
> >NB> effective_cache_size = 80% of the supposed kernel cache.
>
> I think Sean(?) mentioned this one for FreeBSD (Bash code):

sh, not bash.  :)

> 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*).

-sc

--
Sean Chittenden

pgsql-performance by date:

Previous
From: Christopher Kings-Lynne
Date:
Subject: Re: go for a script! / ex: PostgreSQL vs. MySQL
Next
From: "Chris Faulkner"
Date:
Subject: sql performance and cache