From: Scott Marlowe <smarlowe@g2switchworks.com>
To: Steve <cheetah@tanabi.org>
Cc: Benjamin Minshall <minshall@intellicon.biz>
Cc: pgsql-performance@postgresql.org
Subject: Re: Configuration Advice
Date: Wed, 17 Jan 2007 17:13:51 -0600
Message-ID: <1169075631.9586.61.camel@state.g2switchworks.com> (raw)
In-Reply-To: <Pine.GSO.4.64.0701171656450.4471@kingcheetah.tanabi.org>
References: <1168982580.8275.1169588211@webmail.messagingengine.com>
<26412.1168983547@sss.pgh.pa.us>
<1168986053.16393.1169598917@webmail.messagingengine.com>
<81961ff50701161444l3f932755m5a6e15ad2d948130@mail.gmail.com>
<1169002739.19758.1169636285@webmail.messagingengine.com>
<1169012767.6271.7.camel@localhost.localdomain>
<1169044649.19974.1169715743@webmail.messagingengine.com>
<1169050746.9586.44.camel@state.g2switchworks.com>
<45AE79D5.9080000@fuzzy.cz>
<Pine.GSO.4.64.0701171445570.4471@kingcheetah.tanabi.org>
<45AE962A.5070408@intellicon.biz>
<Pine.GSO.4.64.0701171656450.4471@kingcheetah.tanabi.org>
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
not.
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: smarlowe@g2switchworks.com, cheetah@tanabi.org, minshall@intellicon.biz
Subject: Re: Configuration Advice
In-Reply-To: <1169075631.9586.61.camel@state.g2switchworks.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox