Friday, 1 August 2014

Simulate Database I/Os Before Installing A Database

What I'm introducing in this post is Orion tool to simulate database I/O before having Oracle installed on the system. In case you want to calibrate the I/O on system that already have Oracle database installed then the best way is to run DBMS_RESOURCE_MANAGER.calibrate_io by SYS user:

SQL> CONN / AS SYSDBA
SET SERVEROUTPUT ON
  DECLARE
l_latency  PLS_INTEGER;
l_iops     PLS_INTEGER;
l_mbps     PLS_INTEGER;
BEGIN
DBMS_RESOURCE_MANAGER.calibrate_io (num_physical_disks => 1, 
                                            max_latency        => 20,
                                            max_iops           => l_iops,
                                            max_mbps           => l_mbps,
                                            actual_latency     => l_latency);
DBMS_OUTPUT.put_line('Max IOPS = ' || l_iops);
DBMS_OUTPUT.put_line('Max MBPS = ' || l_mbps);
DBMS_OUTPUT.put_line('Latency  = ' || l_latency);
END;
/

Now let's continue with calibrating Oracle I/O's on a system that doesn't have Oracle installed yet.

What is Orion:

> Orion is a tool provided by Oracle to help you simulate database I/O load on disk devices to benchmark your hardware (disks) before having a database on your system.
> Orion is a self dependent tool, it doesn't need a database to be installed on the system before doing the test.
Note: Orion is not supported by Oracle.


How to use Orion:

Download Orion tool:
Go to http://download.oracle.com scroll down to Utilities section and download Orion from there
Note: For the time of writing this post 01-Aug-2014 I've checked Oracle web site but didn't find the link pointing to Orion utility, I guess it been accidentally removed, hopefully Oracle sort this out soon.

Unpack Orion:
# gunzip orion_linux_x86-64.gz
# chmod u+x orion_linux_x86-64


Run Orion:
1) create a file contains all disks you wanna to test in this format "use original device name /dev/xxx":
I recommend to test disk by disk by putting one disk only (one line) in fs.lun file to get a precise result.

# vi fs.lun

/dev/sda
/dev/sdc
/dev/sdb



2) Execute the following command:

# ./orion_linux_x86-64 -run simple -testname fs -num_disks 3

Parameters:
-run         Will determine the level of test:
                Simple     Small and Large Random I/O are tested individually.
                Normal     Same as the simple run level but does combinations of small and random I/Os together.
                Advanced Allows the user to use a wide variety of options to fine tune the workload.
-num_disks    Defines the number of spindles in the storage array that will be tested.
-size_small    Defines the size for small random I/O.
-size_large    Defines the size for large random or sequential I/O.
-type             Defines the type of large I/O (random or sequential).
-write            Defines the percentage of writes in the workload.
-matrix          Defines the mixture of workload to run.

Once the test is done, a summary file <testname>_summary.txt will be created contains a description of the options chosen and some high level statistics collected for the test like IOPS, MBPS and latency gathered.

Note: The result will represent disks as a whole, the max/min of all disks listed on fs.lun file.

Extra Examples:
    Simulate Read-Only test no writes, mix small & large random and sequential reads:
    # ./orion_linux_x86-64 -run advanced -testname fs -write 0 -simulate concat -matrix detailed -num_disks 3

    Simulate Write-Only test no Reads, mix small & large random and sequential writes:
    # ./orion_linux_x86-64 -run advanced -testname fs -write 100 -simulate concat -matrix detailed -num_disks 3

    In case you're getting ASYNC IO errors try this:
    # ./orion_linux_x86-64 -run advanced -testname fs -write 100 -simulate concat

For more details on Orion tool you can check the following links:
Oracle Documentation: http://download.oracle.com/otn/utilities_drivers/orion/Orion_Users_Guide.pdf?AuthParam=1406916708_e12a12c057ddf82cf5e3c56b651ee8df
For Deep explanation: http://www.slideshare.net/alexgorbachev/benchmarking-oracle-io-performance-with-orion-by-alex-gorbachev