How to perform Oracle capability I/O (Stress test database server I/O performance)

　　

I. Oracle related statistical functions for IO information

The Oracle I/O stack contains HBAs, which stores the relevant switches and stores the physical disks. Then Oracle recommends validating I/O to avoid problems when deploying applications. But before there was a very prickly problem, that was how to verify I/O side throughput, in other words, how to verify that I/O throughput is more consistent with the real production environment.

    In Oracle Database 11g, the real Application Testing feature (Capture/replay) is introduced to inject Real (captured) WOR Kload into the system. However, another new 11g feature is available to help assess the I/O capability of the database ' s storage System, and Gaug E maximum IOPS and mbytes/s.

Oracle Instructions for IO evaluation

Capability I/O feature is based on a function within a database (Dbms_resource_manager.calibrateio ()), which is internally integrated by Oracle DB and is more capable of satisfying test I/o problems. and will output the relevant report information at the end.

　　

What do you need to pay attention to executing this package?

1, permissions, must have SYSDBA to perform the process of the permissions , in addition to open timed_statistics.

2, determine the asynchronous I/O in the database all the data files and temporary files have been applied to launch, we can through the V$datafile and V$iostat_file View Association, confirmation.

Col name format A50select name,asynch_io from V$datafile f,v$iostat_file iwhere f.file#=i.file_noand (filetype_name= ' Data file ' or filetype_name= ' Temp file ');

Verify that the required files have been started

If the asynchronous I/O is not started, setting Disk_asynch_io=true starts the function, but it is turned on by default, and if the maximum slots is used in Linux, the Karma function is automatically turned off, which is why the disk_asynch_io= is set. True but still not achieving the effect. The largest slots can be viewed/PROC/SYS/FS/AIO-MAX-NR currently used can be viewed/proc/sys/fs/aio-nr to confirm separately.

3, ensure that the server only needs to test the database open, to avoid the impact of other application software.

4. For RAC, it is necessary to ensure that all instances are turned on, as all nodes will be fully proofread, and the process can only be performed in one instance.

5. Ensure that only one user performs a proofread I/O operation. You can view the current authentication status through V$io_calibration_status.

Also look at the data to see how several processes are available:

    The calibration would 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 would generate large block sequential I/O on each node. Note, that the calibrate I/O is expecting a datafile are spread across all disks specified in Num_disks variable. Furthermore, offline files is not considered for file I/O.

Oracle Official English note

Once executed, the results will be present in the Dba_rsrc_io_calibrate table. With this in view, let's start with the experimental operation. Let practice be the truth.

• The syntax is as follows:

SET serveroutput Ondeclarelat integer;iops integer;mbps INTEGER; begin--Dbms_resource_manager. Calibrate_io (<DISKS>, <max_latency>, ioPS, Mbps, LAT);D Bms_resource_manager. Calibrate_io (2, ioPS, Mbps, LAT);D bms_output. Put_Line (' max_iops = ' | | iops);D bms_output. Put_Line (' latency = ' | | lat);D bms_output. Put_Line (' max_mbps = ' | | mbps); end;/

Syntax: Querying I0 information for SQL

• The parameters are described as follows:

Parameter descriptionnum_physical_disksapproximate number of physical disks in the database storage (Physical disk count input) max_ Latencymaximum tolerable latency in milliseconds for database-block-sized IO requests (maximum available tolerance delay of milliseconds input) max_ Iopsmaximum number of I/O requests per second that can is sustained. The I/O requests is randomly-distributed, database-block-sized reads. (Number of requests for maximum I/O per second output) Max_mbpsmaximum Throughput of I/O that can is sustained, expressed in megabytes per second. The I/O requests is randomly-distributed, 1 megabyte reads. (Maximum throughput m for the duration) actual_latencyaverage latency of database-block-sized I/O requests at max_iops rate, expressed in milliseconds (average latency)

SQL parameter Description

Second, the test

• Test Scenario Description:

Environment for Oracle Linux 6.4 +database 11.2.0.4 (RAC 2 node) +asm 11.2.0.4

Yes, and if the database is using ASM, then if the validated dead data file needs to use all the physical disks in DATADG, not the physical disks in the FRA. Although I mapped using a stored LUN, the num_physical_disks must be the number of real physical disks, because the test is a disk, then this parameter is 1.

2.1. Verify that async I/O is started

[[email protected] ~]$ sqlplus/as sysdba sql*plus:release 11.2.0.4.0 Production on Mon Apr 22:35:23 Copyri  Ght (c) 1982, Oracle. All rights reserved. Connected to:oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit productionwith The partitioning, Real appli Cation Clusters, Automatic Storage Management, Olap,data Mining and Real application testing Options sql> show Paramete R Disk_asynch_io (see if the database has asynchronous I/O enabled) NAME TYPE VALUE---------------------------------- -------------------------------------------disk_asynch_io boolean truesql>! [[email protected] ~]$ MORE/PROC/SYS/FS/AIO-MAX-NR (maximum number of systems slots) 1048576[[email protected] ~]$ more/proc/ SYS/FS/AIO-NR (currently used slots) 23680[[email protected] ~]$ exitexit sql> col name for a50sql> set Linesize 200SQL&G T Select Name,asynch_io from V$datafile f,v$iostat_file I where f.file#=i.file_no and (filetype_name= ' Data file ' or FIletype_name= ' Temp File '); -------------(view file information with async enabled) NAME asynch_io------------------------------- ----------------------------+datadg/rac/datafile/system.262.839537769 async_on+datadg/rac/datafile/system.262.8 39537769 async_on+datadg/rac/datafile/sysaux.263.839537911 async_on+datadg/rac/datafile/undotbs1.264.839           538031 async_on+datadg/rac/datafile/undotbs2.266.839538155 async_on+datadg/rac/datafile/users.267.839538199 async_on 6 rows selected.

To see if asynchronous I/O is started

2.2. Validation begins

Sql> set Serveroutput onsql> declare      lat integer;      ioPS integer;      Mbps integer;    Begin    --dbms_resource_manager.calibrate_io (<num_disks>,<max_latency>,iops,mbps,lat);    Dbms_resource_manager.calibrate_io (1,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;   /

To start verifying I/O:

2.3. Verification Results

Sql> declare      lat integer;      ioPS integer;      Mbps integer;    Begin    --dbms_resource_manager.calibrate_io (<num_disks>,<max_latency>,iops,mbps,lat);    Dbms_resource_manager.calibrate_io (1,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;    /max_iops=45latency=42max_mbps=12 PL/SQL procedure successfully completed.

Results:

How to perform Oracle capability I/O (Stress test database server I/O performance)