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. ---------------------------(end of broadcast)--------------------------- TIP 4: Don't 'kill -9' the postmaster
