Re: Planning a new server - help needed - Mailing list pgsql-performance

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

I guess you mean postgresql 8.3.1? :-)

I use FreeBSD 7 release on a 8-way HP DL360 G5 with a ciss controller.
Works out of the box and I haven't had any issue with 7.0 at all.

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

SAS would probably be the way to go. I haven't tried the
rocketraid-controller. I use the built-in p400i-controller on my
servers using the ciss-driver. I've heard many positive remarks about
areca.

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

I'd stick with ufs2 atm. There are some issues with zfs which probably
have been ironed out by now but ufs2 has been deployed for a longer
time. Performance-wise they are about the same.

>  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

If you have enough disks raid-6 should perform almost as good as raid
1+0. I've setup 11 disks in raid-6 plus one hotspare so I can get more
space out of it. "Enough disks" are approx. eight and up.

--
regards
Claus

When lenity and cruelty play for a kingdom,
the gentlest gamester is the soonest winner.

Shakespeare