Today we published a whitepaper entitled “SQL Anywhere I/O Requirements for Windows and Linux”, authored by SQL Anywhere engineering. The whitepaper outlines in detail the I/O requirements of SQL Anywhere servers on Windows and Linux platforms to ensure database recovery in the event of media and/or power failure. Here is its abstract:
Database servers need to be able to guarantee that data gets to stable storage to ensure that committed transactions persist and to properly implement recovery in case of power loss. Operating systems and storage devices cache and reorder write operations in order to improve performance. Running a database server on an improperly configured system can lead to data loss and file corruption. This document aims to provide the background necessary to understand the durable storage requirements and I/O semantics of SQL Anywhere.
And here is an excerpt from Section 3.2.1:
On Windows, a disk flush can be requested using the
FlushFileBuffers()call. SQL Anywhere uses this call in critical places to ask the OS to guarantee data reliability. Unfortunately, bugs in certain I/O drivers mean that flush commands are not always passed to the disk as a result of this call. SQL Anywhere also requests that I/Os use FUA by opening database and transaction log files by passing the FILE_FLAG_WRITE_THROUGH flag toCreateFile(). Unfortunately, the handling of FUA is not consistent across all disk manufacturers and versions of Windows. It has been observed that some ATA-based implementations discard the FUA bit entirely, compromising the reliability of SQL Anywhere.
This whitepaper is recommended reading for customers utilizing Windows on commodity hardware, or on any Linux distribution, particularly if using the EXT3 filesystem of which I have written previously.
Glenn Paulley is a Director of Engineering at Sybase iAnywhere.
