Re: Database writing architecture

I was just gone for a little bit and almost missed this thread.

In article <1996Apr15.082156_at_bose.com>, seigh_at_bose.com (Joe Seigh) wrote:
>
>In article <4kpjqg$td_at_inet-nntp-gw-1.us.oracle.com>, pzola_at_us.oracle.com
 (Paul Zola ) writes:
>....
>|>
>|> I have no direct experience with Sybase, so I can't say for sure
>|> whether or not Sybase runs the risk of database corruption when using
>|> filesystem files. If true, there's no inherent limitation in UNIX that
>|> makes this so.
>
>fsync() has been around for ages, so there must be some other reason Sybase
 makes
>disclaimers about using filesystem files. With regards to raw files,
 unbuffered
>doesn't necessarily mean that the i/o isn't reordered by the device driver.
 If
>so then fsync() is required also. Or do the official raw device semantics
>preclude this?

^ I believe they do.

 Paul Chen's note about performance with sync writes and Sybase's warnings about file system databases lead me to assume (there's that word again) that Sybase does not provide sync writes on file systems. This makes sense to me, I would rather have the performance for test databases.

A filesystem database would improve performance by ordering writes and by delaying writes (really just spreading them out over time in the best case). The asyncronous property of filesystem writes provides improved user response time VS ACTUALLY DELAYING THE USER UNTIL THE WRITE COMPLETES. There is significant overhead associated with the filesystem (additional copy of data, additional context switch, check for data in buffer, other misc.)

Modern DBMS systems provide filesystem like functionality internally which is optimized to the engine. The throughput and data consistency cannot be matched by a general purpose filesystem. Even when the DBMS integrates with the filesystem (as was suggested with Oracle) for reliable data, there is a performance penalty by duplicating effort. In evaluations please be sure to consider overall system performance and extrapolate to a heavily loaded system.

There are documented cases where tempdb was placed on a filesystem and significant improvements in overall system performace are noted. The primary attribution I am aware of is related to the frequent change in tempdb data including creating, using and deleting tables. The filesystem may never perform physical I/O for a short-term temporary table; the data could all reside in buffers. Received on Mon Apr 22 1996 - 00:00:00 CEST