Oraclecapabilityi/o (stress test database server I/o performance)

Today is. I/o throughput is one of the factors that affect data redo. oracle media recovery depends on I/o, if I/o has a bottleneck, it will inevitably affect the restoration of the media in the slave database.

I/o stack contains hbas, storage related switches, and storage physical disks. Therefore, it is recommended that I/o be verified during application deployment in oracle to avoid problems. But there was a very tough problem before, that is, how to verify the I/o-side throughput, in other words, how to verify that the I/o throughput is more in line with the real production environment.

In Oracle Database 11g, the Real Application Testing feature (Capture/Replay) was introduced to inject real (captured) workload into the system. however, another new 11g feature is available to help assess the I/O capability of the database's storage system, and gauge maximum IOPS and Mbytes/s.

Capability I/o feature is based on a database internal function (dbms_resource_manager.calibrateio (). This function is integrated within the oracle database and can better meet the test I/o problems, the report information will be output at the end.

So what should I pay attention to when executing this package?

1. permission. You must have sysdba permission to execute this process. In addition, you must enable timed_statistics.

2. determine that all data files and temporary files in the database for asynchronous I/o have been started by the application. We can associate and confirm them through the v $ datafile and v $ iostat_file views.

Eg:

Col name format a50
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 ');

If asynchronous I/o is not started, set disk_asynch_io = true to enable this function, but it is enabled by default. If the maximum slots in linux is used up, the restore function is automatically disabled, that is why disk_asynch_io = true is not effective even though disk_asynch_io is set. You can view/proc/sys/fs/aio-max-nr for the largest slots. You can view/proc/sys/fs/aio-nr for confirmation.

3. Ensure that only the database to be tested is enabled on the server to avoid the impact of other application software.

4. For RAC, make sure that all instances are enabled, because all nodes will be fully proofread. You only need to execute this process on one instance.

5. Ensure that only one user performs an I/o check. You can view the current authentication status through v $ io_calibration_status.

In addition, I learned from the following processes:

The calibration will run in different phases. In the first phase, small block random I/O
Workload is saved med 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 internal SS all disks specified in NUM_DISKS
Variable. Furthermore, offline files are not considered for file I/O.

Once the execution is completed, the result will be stored in the dba_rsrc_io_calibrate table.

Okay.

After learning about this, let's start experimenting. Let the practice know.

Syntax:

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;
/
The parameters are described as follows:

Parameter Description
Num_physical_disks
Approximate number of physical disks in the database storage (number of physical disks input)

Max_latency
Maximum tolerable latency in milliseconds for database-block-sized IO requests (Maximum number of milliseconds for latency tolerance input)

Max_iops
Maximum number of I/O requests per second that can be sustained. the I/O requests are randomly-distributed, database-block-sized reads. (output)

Max_mbps
Maximum throughput of I/O that can be sustained, expressed in megabytes per second. the I/O requests are randomly-distributed, 1 megabyte reads. (The maximum throughput M in the duration is measured in units)

Actual_latency
Average latency of database-block-sized I/O requests at max_iops rate, expressed in milliseconds (Average latency)

The environment is oracle linux 6.4 + database 11.2.0.4 (RAC 2 node) + asm 11.2.0.4

By the way, if the database uses asm, all the physical disks in the datadg instead of the physical disks in fra must be used to verify the dead data file. Although I use the stored lun for lun ing

Num_physical_disks must be the actual number of physical disks. Because the test is a disk, this parameter is 1.

1. Verify that async I/o is enabled

[Oracle @ rac-one ~] $ Sqlplus/as sysdbaSQL * Plus: Release 11.2.0.4.0 Production on Mon Apr 21 22:35:23 2014 Copyright (c) 1982,201 3, Oracle. all rights reserved. connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0-64bit ProductionWith the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing optionsSQL> show parameter disk_asynch_io (View Whether the database has enabled asynchronous I/o) NAME TYPE VALUE ------------------------------------ -------------------------------------- disk_asynch_io boolean TRUESQL>! [Oracle @ rac-one ~] $ More/proc/sys/fs/aio-max-nr (maximum number of system slots) 1048576 [oracle @ rac-one ~] $ More/proc/sys/fs/aio-nr (currently used slots) 23680 [oracle @ rac-one ~] $ ExitexitSQL> col name for a50SQL> set linesize 200SQL> 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 certificate --------- + DATADG/rac/datafile/system.262.839537769 ASYNC_ON + DATADG/rac/datafile/system.262.839537769 ASYNC_ON + DATADG/rac/datafile/sysaux.263.839537911 ASYNC_ON + DATADG/rac/datafile/upload + DATADG/rac/datafile/undotbs2.266.839538155 ASYNC_ON + DATADG/rac/datafile/users.267.839538199 ASYNC_ON6 rows selected. SQL> start to verify I/o: SQL> set serveroutput onSQL> declare 2 lat integer; 3 iops integer; 4 mbps integer; 5 begin 6 -- dbms_resource_manager.calibrate_io (<num_disks>, <max_latency>, iops, mbps, lat); 7 dbms_resource_manager.calibrate_io (1, 10, iops, mbps, lat); 8 dbms_output.put_line ('max _ iops = '| iops ); 9 dbms_output.put_line ('latency = '| lat); 10 dbms_output.put_line ('max _ mbps =' | mbps); 11 end; 12/view status information: SQL> select * from v $ io_calibration_status; STATUS CALIBRATION_TIME ----------- progress IN PROGRESSSQL> select file_no, latency, small_read_reqs, latency, large_read_reqs from v $ iostat_file; FILE_NO limit SMALL_READ_REQS limit LARGE_READ_REQS ---------- hour --------------- hour ----------------- 0 0 0 24 0 0 107 6819 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 40 4808 884 FILE_NO 1_small_read_reqs 1_large_read_reqs ------------ ------------- begin ----------------- 1 0 4 0 0 0 2 28 2515 777 779 3 5 577 351 351 4 3 404 264

We can see that the test is in progress:

The output result is as follows:

SQL> declare 2 lat integer; 3 iops integer; 4 mbps integer; 5 begin 6 -- dbms_resource_manager.calibrate_io (<num_disks>, <max_latency>, iops, mbps, lat ); 7 bytes (, iops, mbps, lat); 8 dbms_output.put_line ('max _ iops = '| iops); 9 dbms_output.put_line ('latency =' | lat ); 10 dbms_output.put_line ('max _ mbps = '| mbps); 11 end; 12/max_iops = 45 latency = 42max_mbps = 12PL/SQL procedure Successfully completed. SQL> we can know that the number of requests for the two nodes is 45 and the maximum throughput is 12 M/s. The virtual machine is the scum of the card.