[Babase] postgresql config specs

Karl O. Pinc babase@www.eco.princeton.edu
Wed, 13 Oct 2004 03:47:58 -0500


It occurs to me that you're not running the database on a dedicated
server.   (I used dig.)

In that case this is what I need to be confortable:

shared_buffers=51200 ( 400MB of shared memory )

That's twice the database size, because the rule of
thumb is double for indexes, and you need to allow
for growth too.  However, the whole point here is to
avoid hitting the disk so this should mever be made so
large as to initate any swapping.

wal_buffers=8192 (16MB assuming units of 2K
Linux kernel page sizes -- I forget)

Which leads to their sum:

effective_cache_size=53248

That's 416MB.  Note that other databases have to run in the pg
RAM leftover as slop.  (Of course I don't imagine it's actually
allocated by linux and used by pg unless needed.)
And all other apps on the box must run in the RAM not allocated
to pg.

checkpoint_segments=24 (in 16MB blocks)

to accomidate the larger wal_buffer

sort_mem=10240
(its in 1K units)

as that's enough to fit the keys of our largest table
which ensures we can join tables without hitting the disk.

wal_buffers and checkpoint_segments should be reduced to their
defaults when we're done with the conversion as smaller
sizes mean less data potential data loss in the event of a
crash.  So effective_cache_sized should be reduced 1 for 1
with the reduction in wal_buffers.

If this is not pushing the hardware then being a little less
stingy in shared_buffers, and sort_mem wouldn't be amiss.
Especially shared_buffers if there's plan for other databases
to be used concurrently.

Without any sort of diagnostic output that's the best I
can do.

Karl <kop@meme.com>
Free Software:  "You don't pay back, you pay forward."
                  -- Robert A. Heinlein