On Thu, 4 Dec 2003, Jack Coates wrote:

> On Thu, 2003-12-04 at 12:27, Richard Huxton wrote:
> > On Thursday 04 December 2003 19:50, Jack Coates wrote:
> > >
> > > I'm trying to set Postgres's shared memory usage in a fashion that
> > > allows it to return requested results quickly. Unfortunately, none of
> > > these changes allow PG to use more than a little under 300M RAM.
> > > vacuumdb --analyze is now taking an inordinate amount of time as well
> > > (40 minutes and counting), so that change needs to be rolled back.
> > 
> > You don't want PG to use all your RAM, it's designed to let the underlying OS 
> > do a lot of caching for it. Probably worth having a look at vmstat/iostat and 
> > see if it's saturating on I/O.
> latest changes:
> shared_buffers = 35642
> max_fsm_relations = 1000
> max_fsm_pages = 10000
> wal_buffers = 64
> sort_mem = 32768
> vacuum_mem = 32768
> effective_cache_size = 10000
> /proc/sys/kernel/shmmax = 500000000
> IO is active, but hardly saturated. CPU load is hefty though, load
> average is at 4 now.

Postgresql is busily managing a far too large shared buffer.  Let the 
kernel do that.  Postgresql's shared buffers should be bug enough to hold 
as much of the current working set as it can, up to about 25% or so of the 
servers memory, or 512Meg, whichever comes first.  Unless a single query 
will actually use all of the buffer at once, you're not likely to see an 

Also, your effective cache size is really small.  On a typical Postgresql 
server with 2 gigs of ram, you'll have about 1 to 1.5 gigs as kernel cache 
and buffer, and if it's dedicated to postgresql, then the effective cache 
setting for 1 gig would be 131072 (assuming 8k pages).

If you're updating a lot of tuples without vacuums, you'll likely want to 
up your fsm settings.

Note you can change things like sort_mem, effective_cache_size and 
random_page_cost on the fly (but not buffers, they're allocated at 
startup, nor fsm, they are as well.)

so, if you're gonna have one huge honkin query that needs to sort a 
hundred megs at a time, but you'd rather not up your sort memory that high 
(sort mem is PER SORT, not per backend or per database, so it can get out 
of hand quickly) then you can just 

set sort_mem=128000;

before throwing out the big queries that need all the sort.

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend

Reply via email to