Measuring Storage Performance For Oracle Systems
Storage vendors offer endless amounts of performance data on their products, but the information doesn't necessarily reflect how the storage will perform when it is used by an Oracle database. This article outlines some easy ways to test the performance of your storage systems, including some utilities provided by Oracle.
Remember, the Oracle utilities use a simulated load to mimic the type of operations performed by an Oracle database. There is no guarantee your applications will be able to achieve the level of performance they produce.
For a quick and dirty test of your disk performance, you can time writes performed by the
$ time sh -c "dd if=/dev/zero of=dd-test-file bs=8k count=1000000 && sync" 1000000+0 records in 1000000+0 records out real 0m18.42s user 0m0.70s sys 0m16.77s $ ls -l ddfile -rw-r--r-- 1 oracle oinstall 8192000000 Nov 2 16:11 dd-test-file $ rm dd-test-file $
This is no reflection of how disk performance will look when being accessed by Oracle systems.
ORION (ORacle IO Numbers) mimics the type of I/O performed by Oracle databases, which allows you to measure I/O performance for storage systems without actually installing Oracle. It is available for a number of platforms from this location.
Once you have downloaded the utility, unzip it and make it executable.
# cd /host/software/oracle/orion # gunzip orion_linux_x86-64.gz # chmod u+x orion_linux_x86-64
The usage information is displayed with the following command.
# ./orion_linux_x86-64 -help
To run the test you need to know the LUNs you will be using for Oracle. In this case I am running it on a little VM, so I'm just going to fake the LUNs using the ext3 filesystem.
# mkdir /luns # dd if=/dev/zero of=/luns/lun1 bs=1024k count=10 # dd if=/dev/zero of=/luns/lun2 bs=1024k count=10 # dd if=/dev/zero of=/luns/lun3 bs=1024k count=10 # dd if=/dev/zero of=/luns/lun4 bs=1024k count=10
Next, create a file to hold the LUN configuration. In this case I will call my test "ob-test", so my LUN configuration file must be called "ob-test.lun". Make sure it is in the same directory as the ORION executable. The file should contain a list of the luns used in the test.
/luns/lun1 /luns/lun2 /luns/lun3 /luns/lun4
If you don't specify a test name, the utility assumes the test is called "orion" and looks for the presence of the "orion.lun" file.
Next we run a test. I'm going to do a full grid of tests, comparing the performance of a range of small I/O operations (8K) against a range of large I/O operations (1M). Doing the run using the "normal" option can take a long time, so you may want to try using the "basic" option first.
# ./orion_linux_x86-64 -run normal -testname ob-test ORION: ORacle IO Numbers -- Version 188.8.131.52.0 ob-test_20100720_1229 Test will take approximately 190 minutes Larger caches may take longer #
On completion of the test, the directory with the ORION executable will contain several new files containing information about the IOPS, MBPS and latency gathered for each test. Here are the files produced by this run.
Obviously, the parameters you use for your runs will have to be tailored to reflect your setup.
Remember, no simulation is ever perfect, but the results give you an idea of what your storage system is capable of delivering.
Introduced in Oracle Database 11g Release 1, the
CALIBRATE_IO procedure gives an idea of the capabilities of the storage system from within Oracle. There are a few restrictions associated with the procedure.
- The procedure must be called by a user with the SYSDBA priviledge.
TIMED_STATISTICSmust be set to TRUE, which is the default when
STATISTICS_LEVELis set to TYPICAL.
- Datafiles must be accessed using asynchronous I/O. This is the default when ASM is used.
You can check your current asynchronous I/O setting for your datafiles using the following query.
SELECT d.name, i.asynch_io FROM v$datafile d, v$iostat_file i WHERE d.file# = i.file_no AND i.filetype_name = 'Data File'; NAME ASYNCH_IO -------------------------------------------------- --------- /u01/app/oracle/oradata/DB11G/system01.dbf ASYNC_OFF /u01/app/oracle/oradata/DB11G/sysaux01.dbf ASYNC_OFF /u01/app/oracle/oradata/DB11G/undotbs01.dbf ASYNC_OFF /u01/app/oracle/oradata/DB11G/users01.dbf ASYNC_OFF /u01/app/oracle/oradata/DB11G/example01.dbf ASYNC_OFF 5 rows selected. SQL>
To turn on asynchronous I/O, issue the following command and restart the database.
ALTER SYSTEM SET filesystemio_options=setall SCOPE=SPFILE;
Provided your storage supports asynchronous I/O, the ASYNC_IO flag should now have changed.
SELECT d.name, i.asynch_io FROM v$datafile d, v$iostat_file i WHERE d.file# = i.file_no AND i.filetype_name = 'Data File'; NAME ASYNCH_IO -------------------------------------------------- --------- /u01/app/oracle/oradata/DB11G/system01.dbf ASYNC_ON /u01/app/oracle/oradata/DB11G/sysaux01.dbf ASYNC_ON /u01/app/oracle/oradata/DB11G/undotbs01.dbf ASYNC_ON /u01/app/oracle/oradata/DB11G/users01.dbf ASYNC_ON /u01/app/oracle/oradata/DB11G/example01.dbf ASYNC_ON 5 rows selected. SQL>
You can now call the procedure by running the following code.
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; / Max IOPS = 95 Max MBPS = 449 Latency = 20 PL/SQL procedure successfully completed. SQL>
In addition to appearing on screen, the results of a calibration run can be displayed using the
SET LINESIZE 100 COLUMN start_time FORMAT A20 COLUMN end_time FORMAT A20 SELECT TO_CHAR(start_time, 'DD-MON-YYY HH24:MI:SS') AS start_time, TO_CHAR(end_time, 'DD-MON-YYY HH24:MI:SS') AS end_time, max_iops, max_mbps, max_pmbps, latency, num_physical_disks AS disks FROM dba_rsrc_io_calibrate; START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY DISKS -------------------- -------------------- ---------- ---------- ---------- ---------- ---------- 20-JUL-010 18:07:21 20-JUL-010 18:13:55 95 449 461 20 1 SQL>
Calibration runs can be monitored using the
For more information see.
Hope this helps. Regards Tim...