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