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
