Configuration for a new server. - Mailing list pgsql-performance

Scott,

I don’t know if you received my private email, but just in case you did not I am posting the infomration here.

I have a new set of servers coming in – Dual Xeon E5620’s, 96GB RAM, 18 spindles (1 RAID1 for OS – SATA, 12 disk RAID10 for data – SAS, RAID-1 for logs – SAS, 2 hot spares SAS).  They are replacing a single Dual Xeon E5406 with 16GB RAM and 2x RAID1 – one for OS/Data, one for Logs.

Current server is using 3840MB of shared buffers.

It will be running FreeBSD 8.1 x64, PG 9.0.2, running streaming replication to a like server.

I have read the performance tuning book written by Greg Smith, and am using it as a guide to configure it for performance.

The main questions which I have are the following:

Is the 25% RAM for shared memory still a good number to go with for this size server?

There are approximately 50 tables which get updated with almost 100% records updated every 5 minutes – what is a good number of autovacuum processes to have on these?  The current server I am replacing only has 3 of them but I think I may gain a benefit from having more.

Currently I have what I believe to be an aggressive bgwriter setting as follows:

bgwriter_delay = 200ms                  # 10-10000ms between rounds

bgwriter_lru_maxpages = 1000            # 0-1000 max buffers written/round    

bgwriter_lru_multiplier = 10            # 0-10.0 multipler on buffers scanned/round

Does this look right?

I have the following settings:

work_mem = 64MB                         # min 64kB

maintenance_work_mem = 128MB            # min 1MB

And, of course, some of the most critical ones – the WAL settings.  Right now, in order to give the best performance to the end users due to the size of the current box, I have a very unoptimal setting in my opinion

fsync = off                             # turns forced synchronization on or off

#synchronous_commit = on                # immediate fsync at commit

#wal_sync_method = fsync                # the default is the first option

                                        # supported by the operating system:

                                        #   open_datasync

                                        #   fdatasync

                                        #   fsync

                                        #   fsync_writethrough

                                        #   open_sync

full_page_writes = on                   # recover from partial page writes

wal_buffers = 16MB

#wal_buffers = 1024KB                   # min 32kB

                                        # (change requires restart)

# wal_writer_delay = 100ms              # 1-10000 milliseconds

#commit_delay = 0                       # range 0-100000, in microseconds

#commit_siblings = 5                    # range 1-1000

# - Checkpoints -

#checkpoint_segments = 128              # in logfile segments, min 1, 16MB each

checkpoint_segments = 1024

checkpoint_timeout = 60min              # range 30s-1h

#checkpoint_completion_target = 0.5     # checkpoint target duration, 0.0 - 1.0

checkpoint_completion_target = 0.1

checkpoint_warning = 45min              # 0 disables

These are values which I arrived to by playing with them to make sure that the end user performance did not suffer.  The checkpoints are taking about 8 minutes to complete, but between checkpoints the disk i/o on the data partition is very minimal – when I had lower segments running a 15 minute timeout with a .9 completion target, the platform was fairly slow vis-à-vis the end user.

The above configuration is using PG 8.4.

Thanks in advance for any insight.