settings input for upgrade

Поиск
Список
Период
Сортировка
От Midge Brown
Тема settings input for upgrade
Дата
Msg-id F0ACDCA889994361A34F7459B11094F0@BERNICE
обсуждение исходный текст
Ответы Re: settings input for upgrade
Re: settings input for upgrade
Re: settings input for upgrade
Список pgsql-performance
Дерево обсуждения
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
 
 
 
 

В списке pgsql-performance по дате отправления: