ProductsDesktop Server For Scientific Computing For IBM POWER For IBM System z For SAP Business Applications Red Hat Network Satellite ManagementExtended Update Support High Availability High Performance Network Load Balancer Resilient Storage Scalable File System Smart Management Extended Lifecycle SupportWeb Server Developer Studio Portfolio Edition JBoss Operations Network FuseSource Integration Products Web Framework Kit Application Platform Data Grid Portal Platform SOA Platform Business Rules Management System (BRMS) Data Services Platform Messaging JBoss Community or JBoss enterprise
SolutionsApplication development Business process management Enterprise application integration Interoperability Operational efficiency Security VirtualizationMigrate to Red Hat Enterprise Linux Systems management Upgrading to Red Hat Enterprise Linux JBoss Enterprise Middleware IBM AIX to Red Hat Enterprise Linux HP-UX to Red Hat Enterprise Linux Solaris to Red Hat Enterprise Linux UNIX to Red Hat Enterprise Linux Start a conversation with Red Hat Migration services
TrainingPopular and new courses JBoss Middleware Administration curriculum Core System Administration curriculum JBoss Middleware Development curriculum Advanced System Administration curriculum Linux Development curriculum Cloud Computing and Virtualization curriculum
ConsultingStandard Operating Environment (SOE) Strategic Migration Planning Service-oriented architecture (SOA) Enterprise Data Solutions Business Process Management
Issue #13 November 2005
- Beyond armchair quarterback: Getting involved in Fedora
- Focus on Fedora Extras
- Five Fedora books reviewed and rated
- The Sisyphus security dilemma
- Integrating applications into the desktop, part 2
- Linux and the desktop? Take the survey
- Video: Banche Popolari Unite
- Tuning Oracle Database 10g for ext3 file systems
- Securing your system with Snort
From the Inside
In each Issue
- Editor's blog
- Red Hat speaks
- Ask Shadowman
- Tips & tricks
- Fedora status report
- Magazine archive
Tuning Oracle Database 10g for ext3 file systems
by Joseph Salisbury and D. John Shakshober
Red Hat® Enterprise Linux® 4 enables Oracle® Database 10g file system applications to perform within 3% of raw I/O due to enhancements to the ext3 file system and through the support of both Direct and Asynchronous I/O. Since Red Hat Enterprise Linux 2.1, the use of Direct I/O (DIO) has been an important ext file system feature for large Oracle database applications where the database sizes greatly exceed the amount of physical memory available on the servers.
The Linux 2.6 kernel implemented in Red Hat Enterprise Linux 4 continues to support Direct I/O to ext file systems and also includes scalability changes for Asynchronous I/O (AIO), which allows the simultaneous use of AIO and DIO to ext3 files for database use. This combination allows ext3 files system databases to scale up to CPU counts previously only considered for raw I/O while providing the manageability of a file system to ease system administration task like copying or moving files, backup to tape, and file system and volume snap shots.
Traditional Oracle users setup raw devices and/or volume managers with raw devices for peak Oracle performance. Red Hat Enterprise Linux 4 supports this by using the raw command to single disks, md stripes, or LVM2 volumes. For example:
raw /dev/raw/raw1 /dev/sda raw /dev/raw/raw2 /dev/md1 raw /dev/raw/raw3 /dev/vol1
Oracle Database 9i and Oracle Database 10g support a variety of file systems under Linux to offer improved manageability, exposing the database files to the operating system for easy manipulation of files (such as moving, copying, backing up and restoring files for archival purposes, or when altering or expanding underlying storage). In doing so, most file systems implement a page or file cache which caches file I/O in the systems memory. This works very well for frequently accessed data for problem sizes that fit within the memory size of the systems.
To cache or not to cachethat is the question
Oracle, by design, uses memory for its database cache. Large shared memory segments are allocated at database startup for Oracle's Shared Global Area (SGA). Oracle Database Administrators (DBAs) adjust and tune the SGA for optimal performance of their databases. For frequently accessed database files, Oracle is already caching file I/O and handling data coherency within the database architecture. If the file system is also caching I/O memory, performance can actually suffer by "double caching" file I/O. For large databases, which greatly exceed the total memory of a system or for data access patterns with very little data re-use, performance can suffer.
Red Hat Enterprise Linux 2.1 and higher support the use of Direct I/O to the ext3 file system by opening the file with the O_DIRECT flag, a POSIX-compliant specification. Oracle can disable this "double caching" by providing init.ora options for opening the file with direct access to storage using the O_DIRECT flag upon opening the fil
Figure 1 shows the comparison of raw versus ext3 file system with and without the use of Direct I/O for a 40 GB OLTP database and for a 100 GB decision support query with Oracle Database 10g 10.0.1.3.
In addition, Red Hat Enterprise Linux 2.1 and higher have supported for Asynchronous I/O (AIO) for systems with large I/O capabilities to optimize the concurrency of queuing multiple I/O requests to the storage device, allowing the application code to continue processing until the point where it simply must wait for the I/O requests to complete. Asynchronous I/O will guarantee integrity at I/O completion. This feature has been improving in Linux kernels between 2.4 and 2.6 kernels and has been supported for both raw and ext3 file systems.
Oracle Database 9i and Oracle Database 10g supports Asynchronous I/O to file systems using the following init.ora parameter:
With Red Hat Enterprise Linux 4, ext3 now supports both Direct I/O and Asynchronous I/O simultaneous, giving it the full capabilities of raw, while maintaining file system semantics.
Oracle Database 9i and Oracle Database 10g support both Direct and Asynchronous I/O using:
Figure 2 shows that an Oracle Database 10g R1 running OLTP transactions to ext3 file systems can gain 1.74x using Direct I/O and up to 2x gain combining with Asynchronous I/O over synchronous non-direct I/O. Note: Red Hat Enterprise Linux 4 vs Red Hat Enterprise Linux 3 gained performance from enhancements in ext3 block reservations.
Red Hat Enterprise Linux 2.1 and higher support ext file systems for Oracle users. This includes support for performance features like Direct I/O which benefits database sizes which exceed system memory. Asynchronous I/O was designed initially for raw I/O to allow for application concurrency on servers with larger I/O subsystems with raid-based UltraSCSI controllers or Fiber Channel SANs. Enhancements to AIO in Red Hat Enterprise Linux 4 allow AIO to be used with DIO to an ext3 file system. Offering these capabilities within file systems allows Oracle Database 9i and Oracle Database 10g users to maintain 97% of raw I/O performance when implementing their databases on a standard ext3 file system.