Hello,
I need to install a new server for postgresql 8.3. It will run two
databases, web server and some background programs. We already have a
server but it is becoming slow and we would like to have something that
is faster. It is a cost sensitive application, and I would like to get
your opinion in some questions.
The database itself is an OLTP system. There are many smaller tables,
and some bigger ones (biggest table with 1.2 million records, table size
966MB, indexes size 790MB). In the bigger tables there are only a few
records updated frequently, most of the other records are not changed.
The smaller tables are updated continuously.
Question 1. We are going to use PostgreSQL 3.1 with FreeBSD. The pg docs
say that it is better to use FreeBSD because it can alter the I/O
priority of processes dynamically. The latest legacy release is 6.3
which is probably more stable. However, folks say that 7.0 has superior
performance on the same hardware. Can I use 7.0 on a production server?
Question 2. SCSI or SATA? I plan to buy a RocketRAID 3520 controller
with 8 SATA 2 disks. The operating system would be on another disk pair,
connected to the motherboard's controller. I wonder if I can get more
performance with SCSI, for the same amount of money? (I can spend about
$1500 on the controller and the disks, that would cover 10 SATA 2 disks
and the controller.)
Question 3. FreeBSD 7.0 can use the ZFS file system. I suspect that UFS
2 + soft updates will be better, but I'm not sure. Which is better?
Question 4. How to make the partitions? This is the hardest question.
Here is my plan:
- the OS resides on 2 disks, RAID 1
- the databases should go on 8 disks, RAID 0 + 1
However, the transaction log file should be on a separate disk and maybe
I could gain more performance by putting indexes on a separate drive,
but I do not want to reduce the number of disks in the RAID 0+1 array.
Should I put indexes and transaction log on the RAID 1 array? Or should
I invest a bit more money, add an SATA RAID controller with 16 channels
and add more disks? Would it pay the bill? Another alternative is to put
the biggest tables on a separate array so that it will be faster when we
join these tables with other tables.
I know that it is hard to answer without knowing the structure of the
databases. :-( I can make tests with different configurations later, but
I would like to know your opinion first - what should I try?
Thanks,
Laszlo
