Seeking advice on system configuration (and I have read the techdocs.)
Probably worth reading the archives for the "performance" list.
We are converting a data collection system from Oracle to PostgreSQL 8.0. We are currently getting about 64 million rows per month; data is put into a new table each month. The number of simultaneous connections is very small: one that does all these inserts, and < 5 others that read.
We trying to identify a server for this. Options are a 4-way Opteron with 4 SCSI disks, or a 2-way Opteron with 6 SCSI disks. The 4-CPU box currently has 16 GB of memory and the 2-CPU 4 GB, but we can move that memory around as necessary.
(1) Would we be better off with more CPUs and fewer disks or fewer CPUs and more disks?
Usually, more disks. Obviously, you'll want to test your particular setup, but lots of RAM and lots of disk are generally more important than CPU.
(2) The techdocs suggest starting with 10% of available memory for shared buffers, which would be 1.6 GB on the 4-way. But I've seen posts here saying that anything more than 10,000 shared buffers (80 MB) provides little or no improvement. Where should we start?
Start at the "performance tuning" document below: http://www.varlena.com/varlena/GeneralBits/Tidbits/index.php
(3) If we go with more disks, should we attempt to split tables and indexes onto different drives (i.e., tablespaces), or just put all the disks in hardware RAID5 and use a single tablespace?
Check the performance list archive for lots of discussion about this. You might want to put the WAL on separate disk(s) which will reduce the number available for storage. It depends on what your peak write-rate is.
-- Richard Huxton Archonet Ltd
---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
