X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 85393D1D2F7 for ; Thu, 15 Apr 2004 15:28:25 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 52231-08 for ; Thu, 15 Apr 2004 15:28:27 -0300 (ADT) Received: from mx1.neopolitan.us (mx1.neopolitan.us [65.87.16.224]) by svr1.postgresql.org (Postfix) with ESMTP id 4D85AD1E97A for ; Thu, 15 Apr 2004 15:28:20 -0300 (ADT) Received: from [65.87.16.98] (HELO [10.0.0.210]) by mx1.neopolitan.us (CommuniGate Pro SMTP 4.1.8) with ESMTP-TLS id 3945316 for pgsql-performance@postgresql.org; Thu, 15 Apr 2004 11:28:26 -0700 Subject: Re: linux distro for better pg performance From: "J. Andrew Rogers" To: "pgsql-performance@postgresql.org" In-Reply-To: <407E909F.9000101@ehpg.net> References: <407E25A6.ADFE4815@t1.unisoftbg.com> <407E909F.9000101@ehpg.net> Content-Type: text/plain Organization: Message-Id: <1082053706.10823.38.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.2.2 (1.2.2-5) Date: 15 Apr 2004 11:28:26 -0700 Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/184 X-Sequence-Number: 6484 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