Re: Database writing architecture

In article <316D6BD4.41C67EA6_at_exu.ericsson.se>, Gerald Ward <exuward_at_exu.ericsson.se> says:
>
>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?
>
>Does anybody know or have theories on how these DBMSs accomplish
>their writes and/or what would explain the above mentioned
>behavior? I am not familiar on the internals of either
>system.
>
>By using filesystem databases you loose some performance,
>but gain a great deal in the area of administration etc.
>
>Oracle has two writer processes: log-writer and db-writer.
>Sybase may have something similar but it is done under
>the covers with threads. Could this be a possible explanation?
>
>Also, what are the various issues you have encountered
>through the use of write-back caching controllers and power
>failures, etc.
>
>
>Thanks,
>
>Jerry.

Jerry,
These are good questions. Here is a halting understanding of some of this stuff. A good reference here is _Advanced Programming in the Unix Environment_ by W. Richard Stevens.

Any program written to run in the (modern?) Unix environment has three kinds of i/o functions availble to it: (1) unbuffered, (2) buffered, and (3) asynchronous. (Some older Unices may not support asynch i/o). So these are the kinds of functions that both Sybase and Oracle developers can use to write information to their databases. Unbuffered and buffered functions invoke a system call either directly or indirectly. My understanding is that asynchronous calls avoid the kernel and write directly to the controller. I believe asynchronous calls are used when writing to Sybase databases on raw partitions. I don't know what exact calls (buffered or unbuffered) are used to write to Sybase databases on a Unix file system. There are some Unix commands that may tell you this (try truss or trace).

Sybase engine(s) (the dataserver process(es)) are dispatched on physical processors; they do all the work for tasks that are dispatched on them. This includes reads and writes to the virtual devices. Actually, in some cases, performance on Unix filesystems will be better than on raw partitions.

Writes from cache to a physical device are done at various times: one is when a transaction is commited: The engine will write the modified pages to the log device; this is what makes databases and transactions recoverable. Another write is done at CHECKPOINT; the engine will write modified pages to the data device. There are other utility writes to manage cache (reclaiming of free pages, etc), but I don't know too much about these.

When a Unix box has crashed with a SQL Server on it, typically there are problems with the databases. Relevent factors here include the amount and timing of physical writes. For example, if a checkpoint of a large database happened right before the power failure and the log information corresponding to that activity is not written to the log device, database recovery will probably be problematic.

I don't know how Oracle implements its physical i/o.

Hope this helps

Van Scott Received on Fri Apr 12 1996 - 00:00:00 CEST