Evaluation of storage device IO performance via database-oracle 11g IO calibration Function Introduction

evaluating storage devices from a databaseIOPerformance

---------oracle11g IO Calibration Function Introduction

Preface

I/OSubsystem isORACLEan important part of the database becauseI/Ooperation is through the whole process of database management, its operations include logs, tables, indexes, data dictionaries, and some sort,undooperations and so on, each database reads or writes data on disk, resulting in a diskIO, so to speak of a normal business database system,80%The performance consumption isIOrelative to the network,CPU, memory and other hardware, the development of disk read and write speed is relatively lagging, which also led to many business performance bottlenecks focused on the limited diskIOon,once it appearsIOthe performance problem caused by the bottleneck, expressed asCPUSometimes it takes a lot of time to waitIOoperation, we refer to this situation asIODensity (I/o-bound) system.

we're working on it . Zlhis Business System performance issues, most of them are also dealing with IO performance issues, mainly in three ways:

1 , His system is a high-density business system, at the peak of business will form a large number of centralized concurrent operations, resulting in a large number of I/O operation;

2 , non-standard SQL statement resulting in excessive disk access (e.g., full table scan, etc.);

3 , due to hardware-caused storage IO its own performance problems;

is a certain customer in the real environmentI/Oof performance bottlenecksAWRperformance reports,TOT5wait for the event, the main isI/Otype of wait, when dealing with a similar problem, we first assume that the storedI/Operformance is about meeting our business needs, ignoring the performance issues of the storage itself, and focusing on addressing1,2Application Design orSQLexcessive code caused by non-specificationI/Oread, but sometimes causeI/Operformance is the root cause of the problem is the storage, when encountering this problem, we used to copy, copy, read and write all of the subjective experience of the performance of the storage, or to find hardware to assist the analysis, the former is not easy for us from the data metrics on the storage performance to train, especially in some instantaneous concentration IOaccess to the bottleneck of the storage device to give full persuasion, the latter way if the hardware vendors themselves do not cooperate, we deal with the problem is very embarrassing, this time we urgently need a way to self-sufficient performance evaluation of the storage, the evaluation of performance indicators toI/OThe performance is quantified, which provides a reliable basis for the analysis and solution of the problem.

IORelated Concepts

before we evaluate storage performance, we need to know a couple of IO indicator concept, only the concept of these indicators have been understood, we can objectively evaluate the quality of a storage performance.

iops (i/ooperations Per Second) : i/o The number of transfers per second in each node in the stream (I/O) The number of operations that are used to evaluate the performance of storage random access measurements. iops usually for small i/o i/o OLTP system, high iops

io response time ( latency io

throughput ( throughput " : i/o windows mb/s i/o i/o

the above three indicators are basically able to measure the storage IO performance, where IOPS and throughput is the bigger the better, IO The response time is, of course, as short as possible.

IOCalibration

Overall storage performance is primarily a function of a number of key component layers, includingHBA,Storage Switches,Storage Arrayand thePhysical Disks. Together, these objects can form the whole systemIOability to haveIOoverall performance, throughOracleof theI/Ocalibration function allows you to evaluate the overall performance of the storage and determine and confirmI/Owhether the performance issue is caused by the database or the storage system. Unlike others with externalI/OEvaluation Tools,Oracleof theI/Othe principle of calibration is that the database randomly uses its data file to access the storage, which results in a more realistic reflection of the actual performance of the database access storage, it can help to calculate the current storage of the largestIOPSand throughput, the following conditions must be met to use this feature:

• Database version is 11g

• Operation user must have SYSDBA permission

• Database parameter timed_statistics must be true

• IO must be allowed asynchronously, but with the file system, you can set the Filesystemio_options parameter to SetAll

• Ensure that the data file allows asynchronous IO, which can be confirmed by the following SQL statement:

COL NAME FORMAT A50

SELECT name,asynch_iofrom v$datafile f,v$iostat_file I

WHERE f.file#=i.file_no

and filetype_name= ' Data File ';

i/o calibration is done by calling the oracle internal dbms_resoure_manager.cakibrate_io packet to get, which emits a series of i/o intensive read-only workloads to database files that determine the maximum storage iops ( per second IO number of requests ) and stores the throughput that can be performed per second mbps ( i/o)

i/o calibration is divided into two steps: the first step dbms_resource_manager.calibrate_io through continuous read operations. iops ( max_iops ) actual_latency max_latency io The number of milliseconds to request delay). The second step is through the dbms_resource_manager.calibrate_io 1m size continues to read all data files, this step is mainly to obtain the maximum throughput of this important indicator.

If the user is able to provide Num_physical_disks input parameters can make I/O calibration runs more accurately, this parameter specifies the approximate number of physical disks in the database where the system is stored, and if it is not clear 1 OK, think it's just a piece of disk.

The following is an execution Dbms_resource_manager. Calibrate_io Package case, the statement is very simple, as follows:

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 calibration operation is simple, but there are a few things to keep in mind when running:

• The Num_physical_disks input parameters in the process package are optional. The number of disks is not too accurate, so enter an approximate value to make the calibration faster and more accurate.

finally in I/O During the calibration process, you can V$io_calibration_status view to view the calibration status. after I/O Calibration is successful, you can see the calibration results in the dba_rsrc_io_calibrate table, in order to better understand I/o The calibration process, we take a normal desktop to carry out the next demonstration, see how the IO calibration operation.

through V$io_calibration_status You can view the execution status, and you can see that the process is executing, as follows

in the performance monitoring of the task manager of the operating system, you can see that each data file produces a large number of IO Read, Oracle is the evaluation of storage performance through this read operation.

finally in dba_rsrc_io_calibrate view, you can query to this time IO The values of the various performance indicators for the calibration are as follows, the storage performance of this test, the maximum number of data block requests that continue to be read per second ( Max_iops ) to - , maximum readable per second ( max_mbps ) to 43mbps, A single process can read maximum per second ( max_pmbps ) to 39mbps , read data block requests appear with - Secondary delay

here we pay attention to the same IO Calibration Execution 2 times, the results will vary and the results may not be executed several times 100% The same, because storage performance involves a lot of factors, such as the busy state of storage, temperature, IO requests, and so on, these will have a slight effect on the calibration, but the overall range will not be too large.

Performance Judgment

calibrated we get some metrics, so what is the storage performance that meets the business needs? Strictly speaking, of course iops io demand can be through peak business awr report to view by generating peak business report, view the report in other instance activity stats This part gets, to a user's awr performance report for example, focusing on these indicators [physical read total iorequests],[physical Read Total bytes],[physical write total io requests],[physicalwrite total IO requests] value per second because we io calibration is also counted in units per second.

we can calculate the physical read and write total requests per second for 70.74+80.62 ≈ 151 times, the size of the physical read and write per second is 1.32+1.09 ≈ 2.41mbs=19.28mbps, with this reference, we store the maximum calibration IOPS should not be less than 151 , the throughput per second cannot be less than 19.28mbps, if IO calibration close to or less than this value proves that there is a serious bottleneck in storage performance, for example, the machine we are testing cannot meet the user's IO performance requirements and need to improve performance to meet business needs.

Evaluation of storage device IO performance via database-oracle 11g IO calibration Function Introduction