Oracle Database I/O Performance Tuning: Capturing Extra-Database I/O Performance MetricsAugust 25, 2010 Before tuning an Oracle Database 11g Release 2 (11gR2) for optimal I/O performance, it's important to understand how well the underlying I/O architecture is performing versus its expected I/O rates. This article explores how to implement a suitable extra-database I/O performance test and then capture the actual I/O performance results. Before tuning an Oracle 11g Release 2 (11gR2) database for optimal I/O performance, it's important to understand how well the underlying I/O architecture is performing versus its expected I/O rates. Using tools and concepts already discussed in prior articles, this article - the third in this series - explores how to implement a suitable extra-database I/O performance test and then capture the actual I/O performance results to show that I/O performance testing is still just as much art as science. The prior article in this series covered how to:
Before I even attempt to determine what is the I/O performance of any Oracle 11gR2 database, however, I'll first need to gather some pertinent statistics on what type of performance I can reasonably expect from the underlying I/O subsystem itself, so I'll next demonstrate exactly how to accomplish this with several detailed examples of:
Extra-Database I/O Testing: An Experimental I/O Subsystem ConfigurationI'll first need to configure a sufficiently complex I/O subsystem that will permit the ability to isolate and/or combine different I/O devices with sufficiently different I/O capacities and capabilities. If you've read any of my prior article series, you already know I've illustrated many times just how well VMware works to rapidly build and deploy virtualized testing environments. As with many of my prior experiments, I'll take advantage of my own home-grown server with a dual-core Intel CPU and 4 GB of addressable memory, and I'll use VMware Workstation 7.0.0 to create a virtual machine (VM) that uses just under 3GB of my server's available server memory to run the 64-bit version of the Oracle Enterprise Linux (OEL) operating system. I've deployed all available OEL 5 Update 4 RPMs that comply with Linux kernel 2.6.18-92.5. For my I/O subsystem, I've installed and configured three different physical storage devices:
The pertinent technical specifications for these storage devices are listed in Table 3.1 below.
To leverage these devices' different I/O capabilities, I'll use VMware's ability to access virtualized devices by creating at least one virtual file on each of the physical devices. I've listed the virtual files in Table 3.2.
Once I've started my test VM from within VM Workstation, these virtual files will become visible to its operating system as virtualized mount points. To illustrate this, I've captured the output from the fdisk -l command I issued as the root user on the VM in Listing 3.1. I/O Load Generation Using ORIONOracle Corporation provides a free I/O load generation tool called ORION (Oracle Input/Output Numbers) that's easily obtained from the Oracle Technology Network web site. One of ORION's major advantages is that it's extremely simple to install and use because, unlike vdbench and similar tools, it does not require the Java Runtime Environment (JRE) for execution. Once I'd downloaded the compressed executable, I simply unzipped it to my Oracle user account's home directory. The 64-bit version of the ORION executable has a small footprint of just under 50 MB; as I mentioned in the previous article in this series, running the executable without any commands simply echoes its various command set instructions. After a few abortive attempts at running a quick test of its capabilities, I decided to take a few extra moments to read through the additional documentation that's provided in the ORION User's Guide. (The link to this documentation seems to have been almost deliberately hidden at the bottom of the ORION utility download page.) Configuring ORION I/O Load Generation Tests. First and foremost, ORION needs to understand which I/O devices should be the target(s) of its I/O load generation efforts. I therefore created five ORION I/O test configuration files, one for each combination of virtualized mount points that I wished to test. To build an ORION test configuration file for the test named HDD_3Drives1LVM, for example, I constructed a test configuration file named HDD_3Drives1LVM.lun. I've laid out these test configurations in Listing 3.2. What's absolutely crucial to keep in mind here is that ORION is going to accept all of the devices named within the test configuration file - so if even the slightest amount of write operations are planned, it's best to make sure that the mount points are either completely empty or contain no files of significance! Executing a Simple ORION I/O Load Generation Test. Now that my test configuration files are ready, it's a simple matter to fire off an I/O test from a terminal window session. Here's an example of the feedback from my first test run: [oracle@11gR2Base ~]$ /home/oracle/orion_x86_64 -run simple -testname HDD_3Drives1LVM ORION: ORacle IO Numbers -- Version 11.1.0.7.0 HDD_3Drives1LVM_20100815_1627 Test will take approximately 9 minutes Larger caches may take longer . . . This first simple I/O generation test only generated a perfunctory, standard I/O workload against the specifically targeted LUNs. By default, ORION will run this "simple" test for just 60 seconds first using an 8 KB I/O size for small random I/O reads, and then with a size of 1 MB for large sequential reads - and no write I/Os will be performed at all. This "simple" test also simulates concatenated I/O by chaining all specified LUNs together and reading from each specified LUN from its first block to its last block. Executing an Advanced ORION I/O Load Generation Test. However, it's also possible to generate much more complex I/O testing against just about any combination of physical or virtualized I/O devices. It also offers a plethora of different combinations of I/O test generation options, including:
The ORION test scripts I've used to generate all five I/O test workloads are shown in Listing 3.3; please notice that these workloads take advantage of many of these parameter settings to generate I/O workloads of different types, durations, and percentages of read vs. write activity against dramatically different combinations of virtualized devices. Interpreting the Result of an ORION I/O Load Generation Test. After each I/O test successfully completed, ORION logged the results into a set of text and comma-separated values (CSV) files. Each file is named so it corresponds to the I/O generation test executed; ORION also adds a date and time stamp to each file so that prior results won't be overwritten by future executions of the same test. Table 3.4 describes these output files and their contents:
The comma-separated values (CSV) files are intended as input into common graphing tools, such as Microsoft Excel 7.0. Figures 3.1, 3.2, and 3.3 below illustrate how to take advantage of these data points. I've selected probably the most interesting and more complex ORION test series - XDD6Drives3Devices, which incorporated six LUNs from three different physical devices - to present the resulting test data.
The results from all of these tests - including a Microsoft Excel 7.0 spreadsheet that contains the raw data I used to create these graphs - is available for download here. Next StepsNow that I've got baseline I/O performance statistics for my I/O subsystem, the next step is to actually create Oracle database files within these mount points to determine the impact that different I/O configurations will have upon my database's performance. The next article in this series will therefore focus on how to use well-established database application workloads to evaluate the relative I/O performance. I will illustrate how to:
References and Additional ReadingBefore you proceed to experiment with any of these new features, I strongly suggest that you first look over the corresponding detailed Oracle documentation before trying them out for the first time. I've drawn upon the following Oracle Database 11g Release 2 documents for this article's technical details: E10713-03 Oracle Database 11gR2 Concepts E10500-02 Oracle Database 11gR2 Storage Administrator's Guide |
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
The Network for Technology Professionals
Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers
