衡量Oracle系统的存储性能
来源:互联网 发布:nba球员场均数据排名 编辑:程序博客网 时间:2024/06/03 21:16
Measuring Storage Performance For Oracle Systems
Storage vendors offer endless amounts of performance data on their products, but the information doesn't necessarily reflect how the storage will perform when it is used by an Oracle database. This article outlines some easy ways to test the performance of your storage systems, including some utilities provided by Oracle.
Remember, the Oracle utilities use a simulated load to mimic the type of operations performed by an Oracle database. There is no guarantee your applications will be able to achieve the level of performance they produce.
- DD
- ORION
- DBMS_RESOURCE_MANAGER.CALIBRATE_IO
- SLOB
DD
For a quick and dirty test of your disk performance, you can time writes performed by the dd
command.
$ time sh -c "dd if=/dev/zero of=dd-test-file bs=8k count=1000000 && sync"1000000+0 records in1000000+0 records outreal 0m18.42suser 0m0.70ssys 0m16.77s$ ls -l ddfile-rw-r--r-- 1 oracle oinstall 8192000000 Nov 2 16:11 dd-test-file$ rm dd-test-file$
This is no reflection of how disk performance will look when being accessed by Oracle systems.
ORION
ORION (ORacle IO Numbers) mimics the type of I/O performed by Oracle databases, which allows you to measure I/O performance for storage systems without actually installing Oracle. It used to be available to download for a number of platforms from OTN, but that download is no longer available. Instead, it is included in the "$ORACLE_HOME/bin" directory of Grid Infrastructure (GI) and database installations.
The usage information is displayed with the following command.
# export ORACLE_HOME=/u01/app/oracle/product/12.1.0.2/db_1# $ORACLE_HOME/bin/orion -help
To run the test you need to know the LUNs you will be using for Oracle. In this case I am running it on a little VM, so I'm just going to fake the LUNs using the ext3 filesystem.
# mkdir /luns# dd if=/dev/zero of=/luns/lun1 bs=1024k count=10# dd if=/dev/zero of=/luns/lun2 bs=1024k count=10# dd if=/dev/zero of=/luns/lun3 bs=1024k count=10# dd if=/dev/zero of=/luns/lun4 bs=1024k count=10
Next, create a file to hold the LUN configuration. In this case I will call my test "ob-test", so my LUN configuration file must be called "ob-test.lun". It is assumed it is present in the current directory. The file should contain a list of the luns used in the test.
/luns/lun1/luns/lun2/luns/lun3/luns/lun4
If you don't specify a test name, the utility assumes the test is called "orion" and looks for the presence of the "orion.lun" file.
Next we run a test. I'm going to do a full grid of tests, comparing the performance of a range of small I/O operations (8K) against a range of large I/O operations (1M). Doing the run using the "normal" option can take a long time, so you may want to try using the "basic" option first.
# $ORACLE_HOME/bin/orion -run normal -testname ob-testORION: ORacle IO Numbers -- Version 12.1.0.2.0ob-test_20140828_1804Calibration will take approximately 190 minutes.Using a large value for -cache_size may take longer.Maximum Large MBPS=1672.01 @ Small=0 and Large=2Maximum Small IOPS=16262 @ Small=13 and Large=1Small Read Latency: avg=796 us, min=111 us, max=4927 us, std dev=254 us @ Small=13 and Large=1Minimum Small Latency=148 usecs @ Small=1 and Large=0Small Read Latency: avg=148 us, min=0 us, max=82630 us, std dev=209 us @ Small=1 and Large=0Small Read / Write Latency Histogram @ Small=1 and Large=0 Latency: # of IOs (read) # of IOs (write) 0 - 1 us: 18 0 2 - 4 us: 0 0 4 - 8 us: 0 0 8 - 16 us: 3 0 16 - 32 us: 4 0 32 - 64 us: 5 0 64 - 128 us: 64513 0 128 - 256 us: 325674 0 256 - 512 us: 5759 0 512 - 1024 us: 139 0 1024 - 2048 us: 21 0 2048 - 4096 us: 3 0 4096 - 8192 us: 1 0 8192 - 16384 us: 0 0 16384 - 32768 us: 1 0 32768 - 65536 us: 0 0 65536 - 131072 us: 3 0 131072 - 262144 us: 0 0 262144 - 524288 us: 0 0 524288 - 1048576 us: 0 0 1048576 - 2097152 us: 0 0 2097152 - 4194304 us: 0 0 4194304 - 8388608 us: 0 0 8388608 - 16777216 us: 0 0 16777216 - 33554432 us: 0 0 33554432 - 67108864 us: 0 0 67108864 - 134217728 us: 0 0134217728 - 268435456 us: 0 0#
On completion of the test, the directory with the ORION executable will contain several new files containing information about the IOPS, MBPS and latency gathered for each test. Here are the files produced by this run.
- ob-test_20140828_1804_summary.txt
- ob-test_20140828_1804_iops.csv
- ob-test_20140828_1804_mbps.csv
- ob-test_20140828_1804_lat.csv
- ob-test_20140828_1804_trace.txt
- ob-test_20140828_1804_hist.txt
Obviously, the parameters you use for your runs will have to be tailored to reflect your setup.
Remember, no simulation is ever perfect, but the results give you an idea of what your storage system is capable of delivering.
DBMS_RESOURCE_MANAGER.CALIBRATE_IO
Introduced in Oracle Database 11g Release 1, the CALIBRATE_IO
procedure gives an idea of the capabilities of the storage system from within Oracle. There are a few restrictions associated with the procedure.
- The procedure must be called by a user with the SYSDBA priviledge.
TIMED_STATISTICS
must be set to TRUE, which is the default whenSTATISTICS_LEVEL
is set to TYPICAL.- Datafiles must be accessed using asynchronous I/O. This is the default when ASM is used.
You can check your current asynchronous I/O setting for your datafiles using the following query.
SELECT d.name, i.asynch_ioFROM v$datafile d, v$iostat_file iWHERE d.file# = i.file_noAND i.filetype_name = 'Data File';NAME ASYNCH_IO-------------------------------------------------- ---------/u01/app/oracle/oradata/DB11G/system01.dbf ASYNC_OFF/u01/app/oracle/oradata/DB11G/sysaux01.dbf ASYNC_OFF/u01/app/oracle/oradata/DB11G/undotbs01.dbf ASYNC_OFF/u01/app/oracle/oradata/DB11G/users01.dbf ASYNC_OFF/u01/app/oracle/oradata/DB11G/example01.dbf ASYNC_OFF5 rows selected.SQL>
To turn on asynchronous I/O, issue the following command and restart the database.
ALTER SYSTEM SET filesystemio_options=setall SCOPE=SPFILE;
Provided your storage supports asynchronous I/O, the ASYNC_IO flag should now have changed.
SELECT d.name, i.asynch_ioFROM v$datafile d, v$iostat_file iWHERE d.file# = i.file_noAND i.filetype_name = 'Data File';NAME ASYNCH_IO-------------------------------------------------- ---------/u01/app/oracle/oradata/DB11G/system01.dbf ASYNC_ON/u01/app/oracle/oradata/DB11G/sysaux01.dbf ASYNC_ON/u01/app/oracle/oradata/DB11G/undotbs01.dbf ASYNC_ON/u01/app/oracle/oradata/DB11G/users01.dbf ASYNC_ON/u01/app/oracle/oradata/DB11G/example01.dbf ASYNC_ON5 rows selected.SQL>
You can now call the procedure by running the following code.
CONN / AS SYSDBASET SERVEROUTPUT ONDECLARE l_latency PLS_INTEGER; l_iops PLS_INTEGER; l_mbps PLS_INTEGER;BEGIN DBMS_RESOURCE_MANAGER.calibrate_io (num_physical_disks => 1, max_latency => 20, max_iops => l_iops, max_mbps => l_mbps, actual_latency => l_latency); DBMS_OUTPUT.put_line('Max IOPS = ' || l_iops); DBMS_OUTPUT.put_line('Max MBPS = ' || l_mbps); DBMS_OUTPUT.put_line('Latency = ' || l_latency);END;/Max IOPS = 95Max MBPS = 449Latency = 20PL/SQL procedure successfully completed.SQL>
In addition to appearing on screen, the results of a calibration run can be displayed using the DBA_RSRC_IO_CALIBRATE
view.
SET LINESIZE 100COLUMN start_time FORMAT A20COLUMN end_time FORMAT A20SELECT TO_CHAR(start_time, 'DD-MON-YYY HH24:MI:SS') AS start_time, TO_CHAR(end_time, 'DD-MON-YYY HH24:MI:SS') AS end_time, max_iops, max_mbps, max_pmbps, latency, num_physical_disks AS disksFROM dba_rsrc_io_calibrate;START_TIME END_TIME MAX_IOPS MAX_MBPS MAX_PMBPS LATENCY DISKS-------------------- -------------------- ---------- ---------- ---------- ---------- ----------20-JUL-010 18:07:21 20-JUL-010 18:13:55 95 449 461 20 1SQL>
Calibration runs can be monitored using the V$IO_CALIBRATION_STATUS
view.
SLOB
In addition to the methods shown above, it is worth taking a look at the SLOB utility by Kevin Closson.
- 衡量Oracle系统的存储性能
- 如何衡量系统性能
- IO系统性能之一:衡量性能的几个指标
- IO系统性能之一:衡量性能的几个指标
- IO系统性能之一:衡量性能的几个指标
- IO系统性能之一:衡量性能的几个指标
- IO系统性能之一:衡量性能的几个指标
- IO系统性能之一:衡量性能的几个指标
- IO系统性能之一:衡量性能的几个指标
- IO系统性能之一:衡量性能的几个指标
- IO系统性能之一:衡量性能的几个指标
- IO系统性能之一:衡量性能的几个指标
- IO系统性能之一:衡量性能的几个指标
- IO系统性能之一:衡量性能的几个指标
- 衡量搜索引擎的性能
- IO系统性能 衡量 指标
- 衡量易操作数据存储(SOD)可扩展性能的十大准则(上)
- 衡量易操作数据存储(SOD)可扩展性能的十大准则(中)
- 复数运算
- d3d11 鼠标拾取
- SpringMvc实现验证码
- faster rcnn代码解读
- 思科模拟器:通过对路由器的RIP设置network命令将连在两台路由器上的电脑连通
- 衡量Oracle系统的存储性能
- android加密aes
- Hibernate 中HQL语句
- 一个故事讲清楚 NIO
- SQLServer多实例更改默认实例
- 第十章练习
- The 2nd MSR Video to Language Challenge
- JSP路径问题
- 服务器返回的HTTP状态码和与之关联的消息