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

I/O throughput is a factor that affects data redo. oracle media recovery depends on I/O. If I/O has a bottleneck, it will inevitably affect the backup database.

I/o throughput is a factor that affects data redo. oracle media recovery depends on I/o. If I/o has a bottleneck, this will inevitably affect the standby Database

I/o throughput is a factor that affects data redo. Oracle media recovery depends on I/o. If I/o has a bottleneck, this will inevitably affect the recovery of the standby database media.

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 ( , , 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)

For more details, please continue to read the highlights on the next page: