DBMS? Resource? Manager.calibrate? Io of Oracle I / O performance test (under continuous update)

Time:2020-2-9

brief introduction

DBMS? Resource? Manager.calibrate? IO PL / SQL procedure is provided in the Oracle 11g database to test the I / O performance of the current database instance.
The parameters of DBMS? Resource? Manager.calibrate? IO are as follows:

DBMS_RESOURCE_MANAGER.CALIBRATE_IO (
   num_physical_disks      IN  PLS_INTEGER DEFAULT 1,
   max_latency             IN  PLS_INTEGER DEFAULT 20,
   max_iops                OUT PLS_INTEGER,
   max_mbps                OUT PLS_INTEGER,
   actual_latency          OUT PLS_INTEGER); 

You can use the following methods to call the test:

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 (1, 100, 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 num physical disks parameter is set according to the number of disks in the current system. In the virtual environment, different values may be selected for test and comparison to obtain the best test effect.

Test case

The following is the test of Oracle RDBMS 11.2.0.4 in virtual machine. Select num physical disks as 1, 2, 3 and 4 respectively for comparison.
The test results are as follows:

num_physical_disks=1
max_iops = 118
latency  = 10
max_mbps = 12
num_physical_disks=2
max_iops = 126
latency  = 9
max_mbps = 16
num_physical_disks=3
max_iops = 110
latency  = 11
max_mbps = 16
num_physical_disks=4
max_iops = 102
latency  = 12
max_mbps = 14

As you can see, in my current environment, when num? Physical? Disks = 2, the test can get the best I / O performance.

Compare with Oracle Orion

Compared with the Oracle Orion tool, I / O performance test using DBMS ﹣ resource ﹣ manager.calibrate ﹣ IO method is tested in the real Oracle instance environment, which can better reflect the real situation compared with the Oracle Orion program simulating the I / O operation of Oracle.

However, DBMS resource manager.calibrate IO needs to have an Oracle instance first, and the test results and control parameters are too simple to be adjusted according to the real system. Oracle Orion has many control parameters, which can be used to evaluate the performance of I / O subsystem before implementing database.

Some studies

async IO?

Comparing the performance of Oracle Orion and DBMS ﹣ resource ﹣ manager.calibrate ﹣ IO, it is found that the performance of DBMS ﹣ resource ﹣ manager.calibrate ﹣ IO is much lower than that of Oracle Orion.

After reading the document, I found that the async io of DBF was not turned on.
Open async IO with alter system set filesystemio ﹣ options =’asynch ‘scope = SPFILE, and then restart the database instance.

Rerun the test with the following results:

max_iops = 120
latency  = 98
max_mbps = 18

(this result is not very different from that without async IO). We need to continue to study it.

Recommended Today

[Redis5 source code learning] analysis of the randomkey part of redis command

baiyan Command syntax Command meaning: randomly return a key from the currently selected databaseCommand format: RANDOMKEY Command actual combat: 127.0.0.1:6379> keys * 1) “kkk” 2) “key1” 127.0.0.1:6379> randomkey “key1” 127.0.0.1:6379> randomkey “kkk” Return value: random key; nil if database is empty Source code analysis Main process The processing function corresponding to the keys command is […]