oraclecapabilityi/o(压力测试数据库服务器i/o性能） - 数据库编程

ion With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options SQL> show parameter disk_asynch_io（查看数据库是否启用了异步i/o） NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ disk_asynch_io boolean TRUE SQL> ! [oracle@rac-one ~]$ more /proc/sys/fs/aio-max-nr （系统slots最大数量） 1048576 [oracle@rac-one ~]$ more /proc/sys/fs/aio-nr （当前使用的slots） 23680 [oracle@rac-one ~]$ exit exit SQL> col name for a50 SQL> set linesize 200 SQL> 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'); -------------（查看启用了async的文件信息） NAME ASYNCH_IO -------------------------------------------------- --------- +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/undotbs1.264.839538031 ASYNC_ON +DATADG/rac/datafile/undotbs2.266.839538155 ASYNC_ON +DATADG/rac/datafile/users.267.839538199 ASYNC_ON 6 rows selected. SQL> 开始验证i/o: SQL> set serveroutput on SQL> declare 2 lat integer; 3 iops integer; 4 mbps integer; 5 begin 6 --dbms_resource_manager.calibrate_io(,,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 / 查看状态信息： SQL> select * from v$io_calibration_status; STATUS CALIBRATION_TIME ------------- --------------------------------------------------------------------------- IN PROGRESS SQL> select file_no,small_read_megabytes,small_read_reqs,large_read_megabytes,large_read_reqs from v$iostat_file; FILE_NO SMALL_READ_MEGABYTES SMALL_READ_REQS LARGE_READ_MEGABYTES LARGE_READ_REQS ---------- -------------------- --------------- -------------------- --------------- 0 0 24 0 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 0 0 0 0 0 0 0 0 0 1 40 4808 884 901 FILE_NO SMALL_READ_MEGABYTES SMALL_READ_REQS LARGE_READ_MEGABYTES LARGE_READ_REQS ---------- -------------------- --------------- -------------------- --------------- 1 0 4 0 0 2 28 2515 777 779 3 5 577 351 351 4 3 404 264 264

可知正在进行测试中：

执行结束将输出结果如下：

SQL> declare
  2    lat integer;
  3    iops integer;
  4    mbps integer;
  5  begin
  6  --dbms_resource_manager.calibrate_io(,,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  /
max_iops=45
latency=42
max_mbps=12

PL/SQL procedure successfully completed.

SQL> 

可以知道这两个节点请求小块读为45，最大吞吐量为12M/s。虚拟机就是卡的掉渣。

oraclecapabilityi/o(压力测试数据库服务器i/o性能）(二)