Subject: Re: RES: Initial database loading and IDE x SCSI
From: Mark Lewis <mark.lewis@mir3.com>
To: carlosreimer@terra.com.br
Cc: Tom Lane <tgl@sss.pgh.pa.us>, pgsql-performance@postgresql.org
In-Reply-To: <PEEPKDFEHHEMKBBFPOOKCEHFCPAA.carlosreimer@terra.com.br>
References: <PEEPKDFEHHEMKBBFPOOKCEHFCPAA.carlosreimer@terra.com.br>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: quoted-printable
Organization: MIR3, Inc.
Date: Fri, 02 Jun 2006 13:13:29 -0700
Message-Id: <1149279209.20798.108.camel@archimedes>
Mime-Version: 1.0

On Fri, 2006-06-02 at 16:54 -0300, carlosreimer@terra.com.br wrote:
> > <carlosreimer@terra.com.br> writes:
> > > I would like to know if my supposition is right.
> >
> > > 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 because of
> > > the SCSI command interface overhead.
> >
> > I *seriously* doubt that.
> >
> > If you see a difference in practice it's likely got more to do with the
> > SCSI drive not lying about write-complete ...
> >
>=20
> Many thanks for the answers! There are some more thinks I could not
> understand about this issue?
>=20
> I was considering it but if you have a lot of writes operations, will not
> the disk cache full quickly?
>=20
> If it=C2=B4s full will not the system wait until something could be write=
 to the
> disk surface?
>=20
> If you have almost all the time the cache full will it not useless?
>=20
> Should not, in this scenary, with almost all the time the cache full, IDE
> and SCSI write operations have almost the same performance?
>=20

This is the ideal case.  However, you only get to that case if you use
large transactions or run with fsync=3Doff or run with a write cache (like
IDE drives, or nice RAID controllers which have a battery-backed cache).

Remember that one of the important qualities of a transaction is that
it's durable, so once you commit it the data is definitely stored on the
disk and one nanosecond later you could power the machine off and it
would still be there.

To achieve that durability guarantee, the system needs to make sure that
if you commit a transaction, the data is actually written to the
physical platters on the hard drive.

This means that if you take the naive approach to importing data (one
row at a time, each in its own transaction), then instead of blasting
data onto the hard drive at maximum speed, the application will wait for
the platter to rotate to the right position, write one row's worth of
data, then wait for the platter to rotate to the right position again
and insert another row, etc.  This approach is very slow.

The naive approach works on IDE drives because they don't (usually)
honor the request to write the data immediately, so it can fill its
write cache up with several megabytes of data and write it out to the
disk at its leisure.

> Thanks in advance,
>=20
> Reimer
>=20

-- Mark