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.
