I'm in the process of upgrading from postgres 7.4.8 to 9.0.4 and wanted to run my decisions past some folks who can give me some input on whether my decisions make sense or not.
It's basically a LAPP configuration and on a busy day we probably get in the neighborhood of a million hits.
Server Info:
- 4 dual-core AMD Opteron 2212 processors, 2010.485 MHz
- 64GB RAM
- 16 67GB RAID 1 drives and 1 464GB RAID 10 drive (all ext3)
- Linux 2.6.18-164.el5 #1 SMP Thu Sep 3 03:28:30 EDT 2009 x86_64 x86_64 x86_64 GNU/Linux
There are 3 separate databases:
DB1 is 10GB and consists of multiple tables that I've spread out so that the 3 most used have their data and indexes on 6 separate RAID1 drives, the 3 next busiest have data & index on 3 drives, and the remaining tables and indexes are on the RAID10 drive. The WAL for all is on a separate RAID1 drive.
The others are very write-heavy, started as one table within the original DB, and were split out on an odd/even id # in an effort to get better performance:
DB2 is 25GB with data, index, and WAL all on separate RAID1 drives.
DB3 is 15GB with data, index, and WAL on separate RAID1 drives.
Here are the changes I made to postgres.conf. The only differences between the conf file for DB1 and those for DB2 & 3 are the port and effective_cache_size (which I made slightly smaller -- 8 GB instead of 10 -- for the 2 write-heavy DBs). The 600 max connections are often idle and don't get explicitly closed in the application. I'm looking at connection pooling as well.
autovacuum = on
autovacuum_analyze_threshold = 250
autovacuum_freeze_max_age = 200000000
autovacuum_max_workers = 3
autovacuum_naptime = 10min
autovacuum_vacuum_cost_delay = 20ms
autovacuum_vacuum_cost_limit = -1
autovacuum_vacuum_threshold = 250
checkpoint_completion_target = 0.7
checkpoint_segments = 64
checkpoint_timeout = 5min
checkpoint_warning = 30s
deadlock_timeout = 3s
effective_cache_size = 10GB
log_autovacuum_min_duration = 1s
maintenance_work_mem = 256MB
max_connections = 600
max_locks_per_transaction = 64
max_stack_depth = 8MB
shared_buffers = 4GB
vacuum_cost_delay = 10ms
wal_buffers = 32MB
wal_level = minimal
work_mem = 128MB
ANY comments or suggestions would be greatly appreciated.
Thank you,
Midge
