This article can also be found in the Premium Editorial Download "Storage magazine: .NET server storage: Friendly or not?."
Download it now to read this article plus other related content.
A company is only as strong as the data in its databases. And the care, feeding and protection of the company's databases are mission-critical IT services. Question: What's the best way to design network storage for databases? Don't answer too quickly. It's one of those sneaky SAT questions: The most obvious answer is usually wrong.
Tablespaces can be mapped directly to LUNs using either raw partition or file system storage.
Raw partitions vs. file system
As database and storage administrators well know, databases can provide their own filing system - called raw partitions - or use the filing capabilities of a file system. Historically, raw partitions have been perceived to have the best performance characteristics because they involve the least software overhead. Raw partitions function at the storing level and use block I/O commands and protocols such as SCSI.
But database performance is usually not the most important requirement - database availability and recovery is. There's little question that a database design using easy-to-locate storage and file system objects provides more options for protecting and recovering database data than black-box raw partitions do. As a storage or database administrator, you need to ask yourself the classic Clint Eastwood question: "Are you feeling lucky, punk?" If you would like to add an element of control to your luck quotient, then put your databases on a file system.
Using a file system, the database makes byte-range I/O requests of the file system, which in turn manages the block locations of those byte ranges within the files. When using a file system, configure it to match the I/O characteristics of the database. If the byte range I/O size from the database and the block size of the file system are the same, you'll have an efficient 1:1 relationship between byte range database file I/O requests and file system block I/O requests to storage. The block size configuration of the file system is typically done when the file system is first installed, so your planning process should include research to determine the database I/O size you'll use and which file system block sizes are supported by the file system. As a rule of thumb, if you aren't sure the sizes match, then use a slightly larger file system block size.
The file system can be on the same system as the database system, in which case there are no network communications to consider. The file system can also reside on a NAS system or file server, in which case, you want to know precisely how system-to-system communications work. Both NFS and Common Internet File System (CIFS) filing protocols can be used to run a database on a network-mounted file system. If you use NFS, make sure you use Transmission Control Protocol (TCP) as the transport protocol as opposed to User Datagram Protocol (UDP). Unlike TCP, UDP doesn't divide a message into packets (datagrams) that are reassembled at the end of the transmission. Specifically, UDP doesn't provide sequencing of the packets that the data arrives in. This means that the application program that uses UDP must be able to make sure that the entire message has arrived and is in the right order. On the other hand, TCP provides error checking, reordering and retransmission of data, which makes things far less confusing for error recovery.
Of course, when using NFS or CIFS to support databases, make sure that networking equipment is qualified and well-tested. You don't want database I/Os to be dropped by an overloaded TCP/IP switch or router. Although TCP will reorder data - you want to avoid it - if possible. Performance will probably be better if the database system and the system where the file system resides are connected through the same switch. Using subnets and virtual networks to segregate database traffic from other traffic is also a good idea.
Another approach that incorporates aspects of both raw partitions performance and file system management is Quick I/O from Veritas software. In a nutshell, Quick I/O establishes files within the Veritas file system that can be used by databases using raw partition block I/O. In other words, a Quick I/O file is a virtual raw partition in a file system that is used by the database.
This was first published in August 2002