Re: linux distro for better pg performance

On Thu, 2004-04-15 at 06:39, Gavin M. Roy wrote:
> Your IDE drive is the biggest hardward bottleneck here. RPM's and bus
> transfers are slower than SCSI or SATA.

Individual disk throughput generally has very little bearing on database
performance compared to other factors. In fact, IDE bandwidth
performance is perfectly adequate for databases, and for database
purposes indistinguishable from SATA. I would say that average access
and read/write completion times, especially under load, are by far the
most limiting factors, and disk RPM is only one component of this. In
fact, disk RPM is a very expensive way to get marginally better
throughput in this regard, and I would suggest 10k rather than 15k
drives for the money.

There are really only two features that are worth buying in your disk
subsystem which many people ignore: TCQ and independently managed I/O
with a large battery-backed write-back cache. Currently, the only place
to really get this is with SCSI RAID. You can get 10k SATA drives, so
when you are buying SCSI you are really buying these features.

Do these features make a difference? Far more than you would imagine.
On one postgres server I just upgraded, we went from a 3Ware 8x7200-RPM
RAID-10 configuration to an LSI 320-2 SCSI 3x10k RAID-5, with 256M
cache, and got a 3-5x performance improvement in the disk subsystem
under full database load. SCSI RAID can service a lot of I/O requests
far more efficiently than current IDE/SATA RAID controllers, and it
shows in the stats. Under these types of loads, the actually bandwidth
utilized by the disks doesn't come anywhere close to even their rated
performance, never mind the theoretical performance of the bus. Service
times for IDE/SATA RAID increases dramatically under load, whereas SCSI
tends not to under the same load.

Considering that very good SCSI RAID controllers (e.g. the LSI 320-2
that I mention above) are only marginally more expensive than nominally
equivalent IDE/SATA controller solutions, using SCSI RAID with 10k
drives is pretty much the price-performance sweet spot if you use your
disk system hard (like we do). For databases with low disk I/O
intensity, stay with IDE/SATA and save a little money. For databases
that have high disk I/O intensity, use SCSI. The price premium for SCSI
is about 50%, but the performance difference is an integer factor under
load.

j. andrew rogers