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 improvement. 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
