Re: good pc but bad performance,why? - Mailing list pgsql-performance

On Wed, 7 Apr 2004, Andrew McMillan wrote:

> On Wed, 2004-04-07 at 20:56, huang yaqin wrote:
> > Hello, Richard Huxton,
> >
> >          You said  turning fsync off may cause losing data, that's terrible.
> >     I use SCSI disk, and file system is ext3. I tune postgresql.conf and can't get any improvement. So what can I
do?
> >     Does SCSI disk and IDE disk have difference?
>
> Yes, turning off fsync means that the database is not guaranteeing
> consistency of writes to disk any longer.  On the other hand your IDE
> system probably never was, because IDE drives just typically turn on
> write caching in hardware without telling anyone.
>
> SCSI typically doesn't turn on write caching in the physical drive by
> default, as Tom Lane pointed out earlier.  Good SCSI has a battery
> backed up cache, and then it is OK to turn on write caching, because the
> controller has enough battery to complete all writes in the event of a
> power failure.

Actually, almost all SCSI drives turn on write caching by default, they
just don't lie about fsync, so you still have a one update per revolution
limit, but other things can be happening while that write is being
commited due to the multi-threaded nature of both the SCSI interface and
the kernel drivers associated with them

It would appear the linux kernel hackers are trying to implement the
multi-threaded features of the latest ATA spec, so that, at some future
date, you could have IDE drives that cache AND tell the truth of their
sync AND can do more than one thing at a time.

> One thing I recommend is to use ext2 (or almost anything but ext3).
> There is no real need (or benefit) from having the database on a
> journalled filesystem - the journalling is only trying to give similar
> sorts of guarantees to what the fsync in PostgreSQL is doing.

Is this true?  I was under the impression that without at least meta-data
journaling postgresql could still be corrupted by power failure.

> The suggestion someone else made regarding use of software raid is
> probably also a good one if you are trying to use the on-board RAID at
> the moment.

Some onboard RAID controllers are fairly good (dell's 2600 series have an
adaptec on board that can have battery backed cache that is ok, the lsi
megaraid based one is faster under linux though.)  But some of them are
pretty poor performers.

> Finally, I would say that because you are seeing poor performance on one
> box and great performance on another, you should look at the hardware,
> or at the hardware drivers, for the problem - not so much at PostgreSQL.

More than likely, the biggest issue is that the SCSI drives are performing
proper fsync, while the IDE drives are lying.  Definitely a time to look
at a good caching RAID controller.