Oracle 11g内置的IO测试包
这两天部门有个同事上新系统, 感觉Orion进行压力测试比较麻烦, 我印象中, Oracle 11g引入了一个进行IO压力测试的包, 具体的过程名包含Calibrat,就到自己测试环境的@?/rdbms/admin目录下查找了一把,得到了下列这些内容..
03 | catrm.sql:Rem vkolla 01/23/07 - use DBA_RSRC_IO_CALIBRATE |
04 | catrm.sql:Rem vkolla 11/13/06 - remove DBA_RSRC_IO_CALIBRATE |
05 | catrm.sql:Rem suelee 06/11/06 - Add IO calibration tables |
07 | catrm.sql: create or replace view DBA_RSRC_IO_CALIBRATE |
08 | catrm.sql: from sys.resource_io_calibrate$ |
09 | catrm.sql:comment on table DBA_RSRC_IO_CALIBRATE is |
10 | catrm.sql: 'Results of the most recent I/O calibration' |
11 | catrm.sql:comment on column DBA_RSRC_IO_CALIBRATE.START_TIME is |
12 | catrm.sql: 'start time of the most recent I/O calibration' |
13 | catrm.sql:comment on column DBA_RSRC_IO_CALIBRATE.END_TIME is |
14 | catrm.sql: 'end time of the most recent I/O calibration' |
15 | catrm.sql:comment on column DBA_RSRC_IO_CALIBRATE.MAX_IOPS is |
16 | catrm.sql:comment on column DBA_RSRC_IO_CALIBRATE.MAX_MBPS is |
17 | catrm.sql:comment on column DBA_RSRC_IO_CALIBRATE.MAX_PMBPS is |
18 | catrm.sql:comment on column DBA_RSRC_IO_CALIBRATE.LATENCY is |
19 | catrm.sql:comment on column DBA_RSRC_IO_CALIBRATE.NUM_PHYSICAL_DISKS is |
20 | catrm.sql: create or replace public synonym DBA_RSRC_IO_CALIBRATE |
21 | catrm.sql: for DBA_RSRC_IO_CALIBRATE |
22 | catrm.sql: grant select on DBA_RSRC_IO_CALIBRATE to SELECT_CATALOG_ROLE |
24 | cdfixed.sql:Rem vkolla 01/23/07 - calibration_results to status |
25 | cdfixed.sql:Rem vkolla 11/17/06 - v$io_calibration_results |
26 | cdfixed.sql: create or replace view v_$io_calibration_status |
27 | cdfixed.sql: as select * from v$io_calibration_status; |
28 | cdfixed.sql: create or replace public synonym v$io_calibration_status |
29 | cdfixed.sql: for v_$io_calibration_status; |
30 | cdfixed.sql: grant select on v_$io_calibration_status to SELECT_CATALOG_ROLE; |
31 | cdfixed.sql: create or replace view gv_$io_calibration_status |
32 | cdfixed.sql: as select * from gv$io_calibration_status; |
33 | cdfixed.sql: create or replace public synonym gv$io_calibration_status |
34 | cdfixed.sql: for gv_$io_calibration_status; |
35 | cdfixed.sql: grant select on gv_$io_calibration_status to SELECT_CATALOG_ROLE; |
37 | dbmsrmad.sql:Rem vkolla 08/07/06 - remove calibrate_mode from calibrate_io |
38 | dbmsrmad.sql:Rem suelee 06/11/06 - Add IO calibration routines |
40 | dbmsrmad.sql: PROCEDURE calibrate_io ( |
01 | sys@DBMAIN> desc dbms_resource_manager |
03 | Argument Name Type In / Out Default ? |
05 | NUM_PHYSICAL_DISKS BINARY_INTEGER IN DEFAULT |
06 | MAX_LATENCY BINARY_INTEGER IN DEFAULT |
07 | MAX_IOPS BINARY_INTEGER OUT |
08 | MAX_MBPS BINARY_INTEGER OUT |
09 | ACTUAL_LATENCY BINARY_INTEGER OUT |
12 | 第二个参数为可接受的最大延迟. 我最初对此的理解是, 这是压力测试程序能够接受的最大延迟, 超过此可能就不再进行测试. 不过,经过测试,我感觉这个并不是严格的限制. |
13 | 第三个参数为测试出来的系统能够支撑的最大IOPS |
14 | 第四个参数为测试出来的系统能够支撑的最大MBPS |
15 | 第五个参数我个人估计应该是系统在最大IOPS时对应的响应时间. |
下面为我在自己系统上运行的脚本以及其运行结果.
01 | sys@DBMAIN> set timing on serveroutput on |
03 | 2 v_max_iops BINARY_INTEGER; |
04 | 3 v_max_mbps BINARY_INTEGER; |
05 | 4 v_act_lat BINARY_INTEGER; |
07 | 6 dbms_resource_manager.CALIBRATE_IO(1,20,v_max_iops,v_max_mbps,v_act_lat); |
08 | 7 dbms_output.put_line( 'max iops : ' || v_max_iops ); |
09 | 8 dbms_output.put_line( 'max mbps : ' || v_max_mbps ); |
10 | 9 dbms_output.put_line( 'actual latency : ' || v_act_lat ); |
17 | PL/SQL procedure successfully completed. |
23 | 2 v_max_iops BINARY_INTEGER; |
24 | 3 v_max_mbps BINARY_INTEGER; |
25 | 4 v_act_lat BINARY_INTEGER; |
27 | 6 dbms_resource_manager.CALIBRATE_IO(4,20,v_max_iops,v_max_mbps,v_act_lat); |
28 | 7 dbms_output.put_line( 'max iops : ' || v_max_iops ); |
29 | 8 dbms_output.put_line( 'max mbps : ' || v_max_mbps ); |
30 | 9 dbms_output.put_line( 'actual latency : ' || v_act_lat ); |
37 | PL/SQL procedure successfully completed. |
42 | sys@DBMAIN>col start_time format a30 |
43 | sys@DBMAIN>col end_time format a30 |
45 | 1* select * from dba_rsrc_io_calibrate |
48 | START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY NUM_PHYSICAL_DISKS |
50 | 05-APR-10 09.59.51.135124 AM 05-APR-10 10.12.58.120160 AM 66 33 17 29 4 |
使用dbms_resource_manager.calibrate_io进行io测试需要系统支持异步IO, 也就是系统安装时必须安装有libaio(我所使用的RHEL 5.3使用的是libaio-0.3.106-3.2), 另外数据库的参数必须设置为支持异步IO.
02 | sys@DBMAIN>show parameter disk_asynch_io |
05 | disk_asynch_io boolean TRUE |
11 | sys@DBMAIN>show parameter filesystemio_options |
15 | filesystemio_options string none |
18 | sys@DBMAIN>col name format a50 |
20 | 1 select name ,asynch_io from v$datafile f,v$iostat_file i |
21 | 2 where f.file#=i.file_no |
22 | 3* and (filetype_name= 'Data File' or filetype_name= 'Temp File' ) |
27 | +DATA/dbmain/datafile/system.261.713375671 ASYNC_ON |
28 | +DATA/dbmain/datafile/sysaux.262.713375711 ASYNC_ON |
29 | +DATA/dbmain/datafile/sysaux.262.713375711 ASYNC_ON |
30 | +DATA/dbmain/datafile/undotbs.263.713375731 ASYNC_ON |
31 | +DATA/dbmain/datafile/test8k.265.713381589 ASYNC_ON |
32 | +DATA/dbmain/datafile/indx.269.713798841 ASYNC_ON |
33 | +DATA/dbmain/datafile/test.270.715172511 ASYNC_ON |
本文参考以下内容:
Oracle的官方文档 DBMS_RESOURCE_MANAGER
Arup Nanda的博客文章 Resource Manager I/O Calibration in 11g
No related posts.