Skip site navigation (1) Skip section navigation (2)

Re: Configuration Advice

From: Scott Marlowe <smarlowe(at)g2switchworks(dot)com>
To: Steve <cheetah(at)tanabi(dot)org>
Cc: Benjamin Minshall <minshall(at)intellicon(dot)biz>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Configuration Advice
Date: 2007-01-17 23:13:51
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-performance
On Wed, 2007-01-17 at 15:58, Steve wrote:
> On Wed, 17 Jan 2007, Benjamin Minshall wrote:
> >
> >> Building these indexes takes forever!
> >
> >> Anyway -- ANYTHING we can do to make this go faster is appreciated :) 
> >> Here's some vital statistics:
> >
> >> - Machine is a 16 GB, 4 actual CPU dual-core opteron system using SCSI 
> >> discs.  The disc configuration seems to be a good one, it's the best of all 
> >> the ones we've tested so far.
> >
> > What are your shared_buffers, work_mem, and maintenance_work_mem settings?
> >
> > maintenance_work_mem is used for CREATE INDEX, and with 16GB of memory in the 
> > machine, maintenance_work_mem should be set to at least 1GB in my opinion.
> >
> shared_buffers = 8GB
> work_mem = 256MB
> maintenance_work_mem = 6GB
> So that should be covered, unless I'm using too much memory and swapping. 
> It does look like it's swapping a little, but not too badly as far as I 
> can tell.  I'm thinking of dialing back everything a bit, but I'm not 
> really sure what the heck to do :)  It's all guessing for me right now.

Generally speaking, once you've gotten to the point of swapping, even a
little, you've gone too far.  A better approach is to pick some
conservative number, like 10-25% of your ram for shared_buffers, and 1
gig or so for maintenance work_mem, and then increase them while
exercising the system, and measure the difference increasing them makes.

If going from 1G shared buffers to 2G shared buffers gets you a 10%
increase, then good.  If going from 2G to 4G gets you a 1.2% increase,
it's questionable.  You should reach a point where throwing more
shared_buffers stops helping before you start swapping.  But you might

Same goes for maintenance work mem.  Incremental changes, accompanied by
reproduceable benchmarks / behaviour measurements are the way to
determine the settings.

Note that you can also vary those during different times of the day. 
you can have maint_mem set to 1Gig during the day and crank it up to 8
gig or something while loading data.  Shared_buffers can't be changed
without restarting the db though.

In response to


pgsql-performance by date

Next:From: SteveDate: 2007-01-17 23:33:50
Subject: Re: Configuration Advice
Previous:From: Chris BrowneDate: 2007-01-17 23:08:44
Subject: Re: Configuration Advice

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group