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:[EMAIL PROTECTED] > Sent: Monday, December 06, 2004 10:39 AM > To: David Esposito > Cc: [EMAIL PROTECTED] > 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 <[EMAIL PROTECTED]> > 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. > ---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
