I/O CALIBRATION IN 11G

A very good evening from chandigarh (INDIA) ... Here in koenig we come with another powerful post for 11G Oracle database's one of the most helpful goodies known as I/O calibration. In this post i have focused to make understand a DBA the concept of this calibration and how he can exploit such a wonderfull tool, I first got to know about this tool last year when i first read a blog of Vinay Pandey (The Oracle Security Vault Administrator) .. So here is my explanation for this feature...

At any given time a DBA is looking to establish a solid I/O subsystem for an applocation. If any component in the I/O stack is limited in throughput, it will become the weakest link in the I/O flow. The I/O stack offcourse includes the physical disks, the storage array, the storage switches, the HBA's.

I/O Calibration is a modified version of ORION tool and is based on the asynchronous library. This feature is based on the PL/SQL function called DBMS_RESOURCE_MANAGER.CALIBRATE_IO(). It is used to evaluate the performance of the storage subsystem and determine whether I/O performance problems comes from the database host or the storage subsystem. When it is invoked it will generate I/O intensive read-only random I/O and large-block(1MB) sequential I/O workloads. Unlike other tools, this tool uses oracle code stack the software libraries to drive the I/O; not some generic I/O generator. This capability is important - you are testing the I/O for an Oracle database right ?

Finally when the job is finished you can view the results in DBA_RSRC_IO_CALIBRATE view or the I/O calibration page: maximum I/O per second, maximum megabytes per second, and average actual latency metrics.

This calibrate I/O procedure will have 2 input variables and 3 output variables.

the input variables are NUM_DISKS and MAX_LATENCY.

NUM_DISKS = To get the most accurate results, its best to provide the actual number of physical disks that are used for this database. The Storage Administrator can provide this value. Keep in mind that when ASM is used to manage the database files, say in the DATA diskgroup, then only physical disks that make up the DATA diskgroup should be used for the NUM_DISKS variable; i.e.; do not include the disks from the FRA diskgroup.

LATENCY – This should be set to the defined response time given by your service level agreement for your application. for example your 95th percentile response time SLA is 10secs. I hope you are aware of significance level thresholds.

Now the 3 output variables are MAX_IOPS,MAX_MBPS, ACTUAL_LATENCY

MAX_IOPS - the maximum number of I/O Operations per second
MAX_MBPS - the maximum Megabytes per second
ACTUAL_LATENCY - the actual latency observed during the test

To run I/O calibration and assess the I/O capability of the storage subsystem used by Oracle Database, use the

DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure:

SET SERVEROUTPUT ON
DECLARE
  lat  INTEGER;
  iops INTEGER;
  mbps INTEGER;
BEGIN
-- DBMS_RESOURCE_MANAGER.CALIBRATE_IO (<DISKS>, <MAX_LATENCY>, iops, mbps, lat);
   DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);

  DBMS_OUTPUT.PUT_LINE ('max_iops = ' || iops);
  DBMS_OUTPUT.PUT_LINE ('latency  = ' || lat);
  dbms_output.put_line('max_mbps = ' || mbps);
end;
/

When running the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure, consider the following:


# Do not run the procedure multiple times across separate databases that use the same storage subsystem

# Quiesce the database to minimize I/O on the instance

# For Oracle Real Application Clusters (RAC) configurations, ensure that all instances are opened to calibrate the storage subsystem across nodes

# The execution time of the procedure is dependent on the number of disks in the storage subsystem and increases with the number of nodes in the database

# In some cases, asynchronous I/O is permitted for datafiles, but the I/O subsystem for submitting asynchronous I/O may be maximized, and I/O calibration cannot continue. In such cases, refer to the port-specific documentation for information about checking the maximum limit for asynchronous I/O on the system. The calibration will run in different phases. In the first phase, small block random I/O workload is performed on each node and then concurrently on all nodes. The second phase will generate large block sequential I/O on each node. Note, that the Calibrate I/O is expecting that a datafile is spread across all disks specified in NUM_DISKS variable. Furthermore, offline files are not considered for file I/O.

I also felt the need to explain the following metrics which were just thrown above :=

IOPS (I/O per second):= This metric represents the number of small random I/O that can be serviced in a second. The IOPS rate mainly depends on how fast a disk media can spin.

MBPS (megabytes per second) := The rate at which data can be transferred between the computing server node and the storage array depends on the capacity of I/O channel that is used to transfer data. More data can be transferred through a wider pipe.

I/O Latency: Traditionally it means the time it takes to access a particular sector on the disk. But from database point of view it means all the time it takes for a submitted I/O request to be serviced by the storage. A high latency usually indicates an overloaded system.




Have a nice day !!!

Related links:

HOME

http://koenigocm.blogspot.in/