Thanks for the replies guys ... The copy of Bruce's book I was reading is
at:
http://www.postgresql.org/docs/aw_pgsql_book/hw_performance/node8.html and I
was mistaken, it recommends 25% of physical memory be allocated to the
shared cache .. Is there a better resource (even a commercial publication)
that I should've been looking through? Bruce's book is a little too
high-level and obviously leaves out some of the detail about the fact that
there is a practical maximum ...
I will crank my shared_buffers down ... But how do I know what my sort_mem
setting should be? Are there statistics tables that indicate cache
hits/misses like in Oracle?
Lastly, about the effective_cache_size ... If I cut down my shared buffers
to 10,000 like Tom recommended, and I assume that the OS itself and some
overhead for the sort_mem and vacuum mem takes up about 512MB total, should
I set the effective_cache_size to assume that the remaining 1.5 GB of
physical memory is being allocated for the file cache by the kernel?
Thanks,
Dave
> -----Original Message-----
> From: Martijn van Oosterhout [mailto:kleptog@svana.org]
> Sent: Monday, December 06, 2004 10:39 AM
> To: David Esposito
> Cc: pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Performance tuning on RedHat Enterprise Linux 3
>
> On Mon, Dec 06, 2004 at 09:08:02AM -0500, David Esposito wrote:
> > shared_buffers = 131072 (roughly 1GB)
> > max_fsm_relations = 10000
> > max_fsm_pages = 10000000
> > sort_mem = 4096
> > vacuum_mem = 262144
> > Roughly 25 - 30 connections open (mostly idle) at any given time
> > (connection pools)
>
> I'd suggest reducing shared_buffers to maybe a few thousand, there's
> really no point reserving so much memory that way, it just a waste.
>
> Secondly, up your sort_mem a bit to reflact how big your sorts are
> likely to be.
>
> How's your effective_cache_size?
> --
> Martijn van Oosterhout <kleptog@svana.org>
> http://svana.org/kleptog/
> > Patent. n. Genius is 5% inspiration and 95% perspiration. A
> patent is a
> > tool for doing 5% of the work and then sitting around
> waiting for someone
> > else to do the other 95% so you can sue them.
>
