Re: tuning questions

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.