Re: postgresql.conf

On Wed, 2004-12-22 at 11:30, John Cunningham wrote:
> OK Guys - here's the config file as I've writtten it.
>
> I'll paste in the whole thing before, but this is the important stuff:
>
> max_connections = 256

Are you using a connection pooling scheme (jdbc based pooling, pgpool,
etc...)? If not, you probably should, and then drop the max connections
to something much smaller, like 30 or 40 or so. If you can.

> shared_buffers = 32768 # (256 MB)

That's really high, even for a machine with 6+ gigs of ram. Unless
you're working data set is that big, it's too big. IF, on average,
you're working with smaller amounts of data at a time, it might be
better to drop it down to 5000 to 10000. Few, if any benchmarks have
shown an improvement at settings over 10000. OTOH, you might be the one
person out of a thousand or so who needs larger shared_buffers. Note
that shared buffers aren't cache, and when the last backend referencing
a particular data set in memory stops referencing it, the data it
dropped and the buffer memory released back to the pool, so to speak.
The kernel is generally better at caching than postgresql anyway.

With 8.0's ARC cache algorithm in place, it might be time for someone to
start testing postgresql with a persistant buffer cache (i.e. make it
hold on to the old data sets intead of freeing up the space.)

> sort_mem = 1024 # min 64, size in KB

You can probably up this a bit, especially if you pool your
connections. Try 8192 for a starting point. Setting this too large can
be dangerous to the health of your OS, since you can starve the OS for
memory and make it start swapping processes out to come up with sort_mem

> fsync = No

Not really safe, and not the performance gain it once was, if I remember
a post from Tom recently correctly. On the other hand, IDE disks do
this by design, so if you were using those (with their cache enabled)
you'd be in the same boat.

> wal_sync_method = fsync # the default varies across platforms:
> effective_cache_size = 786432 # (6 GB)
> random_page_cost = 2 # units are one sequential page fetch cost

You can probably drop this down to 1.2 to 1.4 or so on a machine with a
fast disk subsystem and caching controller.

> I am seriously considering breaking the machine all the way down and
> changing to a stripped / mirrored config if that will be the fastest
> way to run it. Need advice on that. Here's the config file:

Do you have a battery backed caching raid controller? If not, that's
one of the first steps to better performance. After that, if you've got
lots of disks, a RAID 5 or RAID 1+0 should both be pretty fast. If
you've got <8 or so disks, the RAID 1+0 will normally be faster,
assuming your RAID controller handles that configuration well. Some
older / cheaper controllers can't parallelize their I/O and run the same
speed in 1+0 as they would in plain old 1.

> #max_fsm_relations = 1000 # min 10, fsm is free space map, ~40 bytes
> #max_fsm_pages = 10000 # min 1000, fsm is free space map, ~6 bytes

Are you sure you're vacuuming often enough and that these settings are
high enough? What does 'vacuum verbose' on your database say? You
might want to use pg_autovacuum to ensure sufficient vacuuming is taking
place.

> #
> # Locale settings
> #
> # (initialized by initdb -- may be changed)
> LC_MESSAGES = 'en_US.UTF-8'
> LC_MONETARY = 'en_US.UTF-8'
> LC_NUMERIC = 'en_US.utf-8'
> LC_TIME = 'en_US.UTF-8'

Are you doing a lot of text searching? If so, you might be better off
initing the database with locale=C instead.