Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Apr 3, 2008

Measuring Disk I/O--Oracle's ORION Tool

By James Koopmann

Oracle’s ORION workload tool enables architects to effectively develop a workload that can mimic and stress a storage array in the same manner as planned applications with an Oracle backend database.

Introduction

In the last two articles “Measuring Disk I/O” and “Measuring Disk I/O—A Vendor View” we looked at what Oracle is doing from a workload perspective and what we might expect from the hardware respectively. These are two great tools to zero in on how disks are behaving for an Oracle workload and matching that to physical hardware.

To complete and increase your ability to model the physical aspects of an Oracle database, it is advantageous for the designer to test a disk configuration before they actually install an Oracle database on top of it. For this reason, I suggest you look at Oracle’s ORION tool to help benchmark your storage architecture. The proper benchmarking can be the difference between the same hardware having poor or excellent performance. Using Oracle’s ORION workload tool Database Architects can effectively develop a workload that can mimic and stress a storage array in the same manner as the planned application with an Oracle backend database. Because the ORION tool does not require a running Oracle database, multiple configurations can be tested such that an optimal storage configuration can be obtained while providing for reliability, stability, and scalability.

As part of the planning process, understanding the application mix that will be accessing the Oracle database is a good first place to start in understanding the workload mix placed on a database, and ultimately the performance requirements of the storage system. This is where you can run those couple of scripts provided in the “Measuring I/O” article and determine if your database is of an OLTP type or OLAP. Just thinking you have an OLTP is not enough; verify it! Get the IOPS and MBPS number throughout the entire day and understand the read and write percentages. Keep in mind that application mixes may change. A simple OLTP database could be cloned to a different system and begin to be used with an OLAP application. The data is the same but the I/O pattern is very different. Also, consider how your users are using data, a new group of power users may come on-board and turn an OLTP into an OLAP reporting engine without notice. Constant interrogation of a database system can easily tell how it is being used and ultimately configuring a storage array.

Let’s Use ORION

AFTER you understand your application mix, IOPS, MBPS, and percentage of reads to writes, you can then move on to putting a benchmark together with the ORION tool. Oracle’s Orion tool can be used to calibrate and design a storage array that can be relied upon to meet expected performance levels. The Orion tool measures Oracle performance without the need to install Oracle software or even create an Oracle database. Instead, Orion issues I/Os against raw disks using the same libraries an Oracle database would issue. Orion is able to simulate a variety of Oracle I/O workloads as described above, and through its command line options, to fine-tune the storage array thus creating the optimal storage array architecture for a particular environment and eliminating guesswork. After using Orion the architect will have a finer understanding for the performance capabilities of a storage array and will be confident in its deployment.

The Orion tool has a variety of options to fine-tune the sample workload desired to stress the storage system. Of interest are the following:

1.  Run Level

a.   Simple – Small and Large Random I/O are tested individually.

b.  Normal – Same as the simple run level but does combinations of small and random I/Os together.

c.   Advanced – Allows the user to use a wide variety of options to fine-tune the workload.

2.  num_disks – Defines the number of spindles in the storage array that will be tested.

3.  size_small – Defines the size for small random I/O.

4.  size_large – Defines the size for large random or sequential I/O.

5.  type – Defines the type of large I/O (random or sequential).

6.  write – Defines the percentage of writes in the workload.

7.  matrix – Defines the mixture of workload to run.

It is very easy to begin using the Orion tool.

1.  Download Orion.

2.  Install by unzipping the file.

3.  Create a file that contains a list of raw volume or files to test.

4.  Execute the Orion binary with workload options.

5.  View the tabular output.

It is suggested that the reader visit the Orion download page and read the Orion User Guide to obtain the latest instructions on how to run various workload scenarios.

For any workload that is tested against the storage system, Orion will load the system with various I/O streams of varying intensities to determine IOPS, latency, and MBPS. The exact load level is represented in the form of outstanding asynchronous I/Os that can continually be maintained. For instance if a mix workload of 2 outstanding large random I/Os in combination with 4 outstanding small random I/Os was maintained to produce a data transfer rate of 95MBPS, then there are actually two load levels. One load level of 2 for large random I/Os and one load level of 4 for small random I/Os. These two load levels together, for the storage configuration tested, produce the 95MBPS throughput. The key point to remember is that the load level represents the number of outstanding I/Os that are queued and not the number of I/Os that have been serviced.

A Simple Test

The following test is not an exhaustive evaluation of the Orion tool, but instead a simple test to show and prove the usefulness of the tool. In this test scenario, two different storage configurations are chosen. One configuration has one disk and the second configuration has three disks. Through this test, the reader should take away the ease at which a storage array can be stressed and then configured for an optimal Oracle storage system.

For this test, an Orion advanced run level was selected so that a proper workload could be used for the expected storage system. Notice that the example specifies the number of disks to test, will be a read-only test, provides a test name, and will mix small random with large random and sequential reads. Again, this is only to prove the usefulness of the tool and after looking at the many options that are available with the Orion tool the reader should construct a viable workload for the purposes their particular storage array will be used for.

./orion_linux_em64t -run advanced -testname orion1 -num_disks 1 -write 0 -simulate concat -matrix detailed
./orion_linux_em64t -run advanced -testname orion3 -num_disks 3 -write 0 -simulate concat -matrix detailed

Test Results

Summary Results

A summary file, <testname>_summary.txt, is created from the Orion workload tool and contains a description of the options chosen and some high level statistics collected for the test. For this test, it is immediately evident that the three disk configuration gives better numbers for IOPS and MBPS. Note that the numbers revolve around the maximum value seen for each I/O type. These numbers provide the best-case scenario for the storage configuration.

Single Disk

Maximum Large MBPS=64.43   @ Small=0 and Large=2
Maximum Small IOPS=586 @ Small=4 and Large=0
Minimum Small Latency=4.45 @ Small=2 and Large=0

Multile (3) Disks

Maximum Large MBPS=95.38   @ Small=0 and Large=6
Maximum Small IOPS=1021 @ Small=15 and Large=0
Minimum Small Latency=3.47 @ Small=2 and Large=0

Additional Result Files

ORION will also produce a file named ‘<testname>_iops.csv’ and ‘<testname>_mbps.csv’. Each of these files contains comma-separated values that represent the IOPS and MBPS for various I/O queue depths during the benchmark run. These files are important to look at to see where performance inflection points exist so that you can determine where diminishing returns on hardware investment occur.

The Orion tool provides two very important benefits. The first is the ease at which it allows architects to configure a system for use with an Oracle database. The guesswork is quickly removed for by performing a variety of benchmark test. Tests can be done for specific applications as well as different physical structures within Oracle. If a storage system is running an Oracle database, it is imperative that the storage system be stressed and benchmarked using the Orion tool. Only after running this tool can a true representation of how the storage system will perform under a true Oracle workload be presented to the architect and used for the proper configuration of a storage system.

» See All Articles by Columnist James Koopmann



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM


















Thanks for your registration, follow us on our social networks to keep up-to-date