Re: Database writing architecture

In article <316D6BD4.41C67EA6_at_exu.ericsson.se>

           exuward_at_exu.ericsson.se "Gerald Ward" writes:
> I'm very curious about comparing the writing architecture
> of Oracle versus Sybase. This question stems from prior
> discussions on the difference between databases installed
> on raw partions and databases installed on filesystems
> (UNIX).
>
> Sybase may have problems with database consistency
> if filesystem databases are used because the OS buffers
> the writes. Apparantly, Oracle doesn't have this problem
> or it isn't as big of an issue. Is this indeed true?
I don't really see how this could be true. The Unix operating system buffers writes to its file systems. Hence, when the DBMS writes a log entry (record/page/...) if the log is held on a filesystem, it cannot be sure that the entry has made it to disk. Should there then be a system failure, the log on disk may not be intact. Unix only writes its file system buffers to disk when the "sync" processing occurs (which is usually a tunable option, but is often every 30 seconds or when the buffers get full).

Oracle and Sybase SQL Server have rather different logging approaches - SQL Server uses a log which contains an entry for each modification made to the data in the database. My understanding is that Oracle uses "Rollback Segments" which hold "before images" of the data and the database changes the actual data on the disk, retrieving the before images from the rollback segments should a rollback be needed. Both approaches have strengths and weaknesses.

However, both of these approaches require reliable disk writes - hence, DBMSs usually use raw partitions (which are unbuffered) rather than file systems (which are buffered). Today, there are versions of Unix that allow more sophisticated approaches (such as IBM's Journaled File System - JFS) which can prevent the traditional data loss problems, but in general, the Unix file system is not really suitable for a database log area.

Eoin.

-- 
Eoin Woods, Watford, Hertfordshire, WD1 1PD, UK
Email:      Eoin_at_copse.demon.co.uk