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. |
