Re: postgresql.conf - Mailing list pgsql-general

The server is a DELL Poweredge 2650 with it's built in RAID - 4 disks
currently in a RAID 5 config.  I will check on the battery backup.

I'm putting this server together and rebuilding our overall db
structure all at the same time, so I have a good amount of flexiblity.
 I realized I was not taking as much advantage of the machine as
possible before - hence the call out to the list.

I'll look in to the persistent connections - last time we built this
it was several versions ago and the overall attitude was that it
didn't work terrible well.  Is this a PG setting or something in PHP?

The shared buffers was a big concern - I've read that there's a limit
that helps, but as the machine will only do DB transactions, I don't
know what else to do with the RAM.  It's intended for PG's use.

Obviously I'd rather have fsync on - I was really looking for some
opinions on this.  Better safe than sorry - but I am trying to sqeeze
every bit of juice possible out of this machine.

The machine has 4 drives - should I do a RAID 1+0 or a 5?

Most of the searching is char fields then linking ids from one table to another.

BTW - this is one of the best discussions I've been on - glad everyone
can be so helpful.

Thanks!
-John



On Wed, 22 Dec 2004 12:08:10 -0600, Scott Marlowe
<smarlowe@g2switchworks.com> wrote:
> 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.
>