X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 2303C9FA43C for ; Fri, 2 Jun 2006 15:40:22 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 05232-06 for ; Fri, 2 Jun 2006 15:40:13 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id 2C50D9FA0E5 for ; Fri, 2 Jun 2006 15:40:12 -0300 (ADT) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Fri, 2 Jun 2006 18:40:11 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 02 Jun 2006 13:40:11 -0500 Subject: Re: Initial database loading and IDE x SCSI From: Scott Marlowe To: carlosreimer@terra.com.br Cc: pgsql-performance@postgresql.org In-Reply-To: References: Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Message-Id: <1149273611.25526.169.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Fri, 02 Jun 2006 13:40:11 -0500 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200606/9 X-Sequence-Number: 19366 On Fri, 2006-06-02 at 13:25, carlosreimer@terra.com.br wrote: > Hi, >=20 > I would like to know if my supposition is right. >=20 > Considering an environment with only one hard disk attached to a server, = an > initial loading of the database probably is much faster using an IDE/ATA > interface with write-back on than using an SCSI interface. That=C2=B4s be= cause of > the SCSI command interface overhead. >=20 > Then main advantage of SCSI interfaces, the multiuser environment is lost= in > this scenery. >=20 > Am I right? Am I missing something here? >=20 > Even if I=C2=B4m right, is something that could be done too improove SCSI= loading > performance in this scenery? The answer is yes. And no. IDE drives notoriously lie about their cache, so that if you have the cache enabled, the IDE drive will nominally ack to an fsync before it's actually written the data. So, the IDE drive will write faster, but your data probably won't survive a system crash or power loss during a write. If you turn off the cache, then the IDE drive will be much slower. SCSI overhead isn't really a big issue during loads because you're usually writing data at a good clip, and the overhead of SCSI is pretty small by comparison to how much data you'll be slinging. However, SCSI drives don't lie about Fsync, so the maximum speed of your output will be limited by the speed at which your machine can fsync the pg_xlog output. =20 For a single disk system, just doing development or a reporting database, an IDE drive is often just fine. But under no circumstances should you put an accounting system on a single drive, especially IDE with cache turned on.