Die Performance einer Datenbank wird entscheidend geprägt von dem I/O Verhalten des Platten-Subsystems.
Mit Oracle 11gR2 wird standardmässig das Werkzeug „orion“ mit installiert.
Beschrieben ist „orion“ im Oracle Performance Tunig Guide Kapitel 8.
„orion“ erlaubt eine Vorhersage der I/O Performance ohne eine Datenbank zu installieren und eignet sich so, vorab Platten-Subsystems zu analysieren.
Die ermitteln Informationen werden u. a. in ein *.csv File abgelegt, dass in Excel eine graphische Darstellung erlaubt.
Beispiel:
[oracle]:> cat /tmp/o.lun /oracle/disk01/control01.ctl [oracle]:> $ORACLE_HOME/bin/orion -testname /tmp/o -run normal ORION: ORacle IO Numbers — Version 11.2.0.1.0/tmp/o_20110513_1052
Calibration will take approximately 19 minutes. Using a large value for -cache_size may take longer. [oracle]:> more /tmp/o_20110513_1052_mbps.csv /tmp/o_20110513_1052_lat.csv
::::::::::::::
/tmp/o_20110513_1052_mbps.csv
::::::::::::::
Large/Small, 0, 1, 2, 3, 4, 5
1, 1086.52, 101.61, 109.67, 163.55, 119.99, 113.40
2, 987.82, 114.27, 92.76, 155.87, 141.91, 114.85
::::::::::::::
/tmp/o_20110513_1052_lat.csv
::::::::::::::
Large/Small, 1, 2, 3, 4, 5
0, 1029.84, 1963.78, 2775.70, 3455.02, 4050.37
1, 511.23, 1207.35, 2093.45, 2952.65, 3667.30
2, 616.09, 1344.66, 1538.38, 2122.24, 3463.90 [oracle]:> more /tmp/o_20110513_1052_summary.txt
ORION VERSION 11.2.0.1.0 Command line: -testname /tmp/o -run normal These options enable these settings: Test: /tmp/o Small IO size: 8 KB Large IO size: 1024 KB IO types: small random IOs, large random IOs Sequential stream pattern: one LUN per stream Writes: 0% Cache size: not specified Duration for each data point: 60 seconds Small Columns:, 0, 1, 2, 3, 4, 5 Large Columns:, 0, 1, 2 Total Data Points: 18 Name: /oracle/disk01/control01.ctl Size: 18399232 1 files found. Maximum Large MBPS=1086.52 @ Small=0 and Large=1 Maximum Small IOPS=1954 @ Small=1 and Large=1 Minimum Small Latency=511.23 usecs @ Small=1 and Large=1 [oracle]:> more /tmp/o_20110513_1052_trace.txt TEST START Point 1 (small=0, large=0) of 403 Valid small 1 Valid large 1 Valid Point 2 (small=1, large=0) of 403 Valid small -2 Valid large 1 TEST START Point 1 (small=0, large=0) of 18 Valid small 1 Valid large 1 Valid ran (small): VLun = 0 Size = 18399232 ran (small): Index = 0 Avg Lat = 1029.84 us Count = 58193 ran (small): nio=58193 nior=58193 niow=0 req w%=0 act w%=0 ran (small): my 1 oth 0 iops 970 lat 1029.84 us bw = 7.58 MBps dur 59.96 s size 8 K READ Point 2 (small=1, large=0) of 18 Valid small 1 Valid large 1 Valid ran (small): VLun = 0 Size = 18399232 ran (small): Index = 0 Avg Lat = 1963.78 us Count = 30542 ran (small): Index = 1 Avg Lat = 1963.77 us Count = 30542 ran (small): nio=61084 nior=61084 niow=0 req w%=0 act w%=0 ran (small): my 2 oth 0 iops 1018 lat 1963.78 us bw = 7.95 MBps dur 59.99 s size 8 K READ Point 3 (small=2, large=0) of 18 Valid small 1 Valid large 1 Valid ran (small): VLun = 0 Size = 18399232 ran (small): Index = 0 Avg Lat = 2775.72 us Count = 21607 ran (small): Index = 1 Avg Lat = 2775.70 us Count = 21607 ran (small): Index = 2 Avg Lat = 2775.67 us Count = 21606 ran (small): nio=64820 nior=64820 niow=0 req w%=0 act w%=0 ran (small): my 3 oth 0 iops 1080 lat 2775.70 us bw = 8.44 MBps dur 59.98 s size 8 K READ Point 4 (small=3, large=0) of 18 Valid small 1 Valid large 1 Valid ran (small): VLun = 0 Size = 18399232 ran (small): Index = 0 Avg Lat = 3455.00 us Count = 17358 ran (small): Index = 1 Avg Lat = 3455.01 us Count = 17358 ran (small): Index = 2 Avg Lat = 3455.03 us Count = 17358 ran (small): Index = 3 Avg Lat = 3455.02 us Count = 17358 ran (small): nio=69432 nior=69432 niow=0 req w%=0 act w%=0 ran (small): my 4 oth 0 iops 1157 lat 3455.02 us bw = 9.04 MBps dur 59.98 s size 8 K READ Point 5 (small=4, large=0) of 18 Valid small 1 Valid large 1 Valid ran (small): VLun = 0 Size = 18399232 ran (small): Index = 0 Avg Lat = 4050.53 us Count = 14806 ran (small): Index = 1 Avg Lat = 4050.55 us Count = 14806 ran (small): Index = 2 Avg Lat = 4050.20 us Count = 14805 ran (small): Index = 3 Avg Lat = 4050.30 us Count = 14805 ran (small): Index = 4 Avg Lat = 4050.29 us Count = 14805 ran (small): nio=74027 nior=74027 niow=0 req w%=0 act w%=0 ran (small): my 5 oth 0 iops 1234 lat 4050.37 us bw = 9.64 MBps dur 59.97 s size 8 K READ Point 6 (small=5, large=0) of 18 Valid small 1 Valid large 1 Valid ran (large): VLun = 0 Size = 18399232 ran (large): Index = 0 Avg Lat = 919.87 us Count = 65158 ran (large): nio=65158 nior=65158 niow=0 req w%=0 act w%=0 ran (large): my 1 oth 0 iops 1086 lat 919.87 us bw = 1086.52 MBps dur 59.97 s size 1024 K READ Point 7 (small=0, large=1) of 18 Valid small 1 Valid large 1 Valid ran (small): VLun = 0 Size = 18399232 ran (small): Index = 0 Avg Lat = 511.23 us Count = 117252 ran (small): nio=117252 nior=117252 niow=0 req w%=0 act w%=0 ran (small): my 1 oth 1 iops 1954 lat 511.23 us bw = 15.27 MBps dur 60.00 s size 8 K READ ran (large): VLun = 0 Size = 18399232 ran (large): Index = 0 Avg Lat = 9840.80 us Count = 6094 ran (large): nio=6094 nior=6094 niow=0 req w%=0 act w%=0 ran (large): my 1 oth 1 iops 101 lat 9840.80 us bw = 101.61 MBps dur 59.98 s size 1024 K READ Point 8 (small=1, large=1) of 18 Valid small 1 Valid large 1 Valid ran (small): VLun = 0 Size = 18399232 ran (small): Index = 0 Avg Lat = 1207.34 us Count = 49671 ran (small): Index = 1 Avg Lat = 1207.35 us Count = 49670 ran (small): nio=99341 nior=99341 niow=0 req w%=0 act w%=0 ran (small): my 2 oth 1 iops 1655 lat 1207.35 us bw = 12.94 MBps dur 59.99 s size 8 K READ ran (large): VLun = 0 Size = 18399232 ran (large): Index = 0 Avg Lat = 9117.87 us Count = 6576 ran (large): nio=6576 nior=6576 niow=0 req w%=0 act w%=0 ran (large): my 1 oth 2 iops 109 lat 9117.87 us bw = 109.67 MBps dur 59.96 s size 1024 K READ Point 9 (small=2, large=1) of 18 Valid small 1 Valid large 1 Valid ran (small): VLun = 0 Size = 18399232 ran (small): Index = 0 Avg Lat = 2093.42 us Count = 28651 ran (small): Index = 1 Avg Lat = 2093.45 us Count = 28650 ran (small): Index = 2 Avg Lat = 2093.47 us Count = 28650 ran (small): nio=85951 nior=85951 niow=0 req w%=0 act w%=0 ran (small): my 3 oth 1 iops 1432 lat 2093.45 us bw = 11.19 MBps dur 59.99 s size 8 K READ ran (large): VLun = 0 Size = 18399232 ran (large): Index = 0 Avg Lat = 6113.83 us Count = 9807 ran (large): nio=9807 nior=9807 niow=0 req w%=0 act w%=0 ran (large): my 1 oth 3 iops 163 lat 6113.83 us bw = 163.55 MBps dur 59.96 s size 1024 K READ Point 10 (small=3, large=1) of 18 Valid small 1 Valid large 1 Valid ran (small): VLun = 0 Size = 18399232 ran (small): Index = 0 Avg Lat = 2952.92 us Count = 20268 ran (small): Index = 1 Avg Lat = 2952.93 us Count = 20268 ran (small): Index = 2 Avg Lat = 2952.58 us Count = 20268 ran (small): Index = 3 Avg Lat = 2952.17 us Count = 20267 ran (small): nio=81071 nior=81071 niow=0 req w%=0 act w%=0 ran (small): my 4 oth 1 iops 1354 lat 2952.65 us bw = 10.58 MBps dur 59.84 s size 8 K READ ran (large): VLun = 0 Size = 18399232 ran (large): Index = 0 Avg Lat = 8039.69 us Count = 7204 ran (large): nio=7204 nior=7204 niow=0 req w%=0 act w%=0 ran (large): my 1 oth 4 iops 119 lat 8039.69 us bw = 119.99 MBps dur 60.04 s size 1024 K READ Point 11 (small=4, large=1) of 18 Valid small 1 Valid large 1 Valid ran (small): VLun = 0 Size = 18399232 ran (small): Index = 0 Avg Lat = 3667.29 us Count = 16338 ran (small): Index = 1 Avg Lat = 3667.25 us Count = 16338 ran (small): Index = 2 Avg Lat = 3667.32 us Count = 16337 ran (small): Index = 3 Avg Lat = 3667.32 us Count = 16337 ran (small): Index = 4 Avg Lat = 3667.34 us Count = 16337 ran (small): nio=81687 nior=81687 niow=0 req w%=0 act w%=0 ran (small): my 5 oth 1 iops 1363 lat 3667.30 us bw = 10.65 MBps dur 59.92 s size 8 K READ ran (large): VLun = 0 Size = 18399232 ran (large): Index = 0 Avg Lat = 8702.64 us Count = 6807 ran (large): nio=6807 nior=6807 niow=0 req w%=0 act w%=0 ran (large): my 1 oth 5 iops 113 lat 8702.64 us bw = 113.40 MBps dur 60.03 s size 1024 K READ Point 12 (small=5, large=1) of 18 Valid small 1 Valid large 1 Valid ran (large): VLun = 0 Size = 18399232 ran (large): Index = 0 Avg Lat = 2024.09 us Count = 29632 ran (large): Index = 1 Avg Lat = 2024.18 us Count = 29631 ran (large): nio=59263 nior=59263 niow=0 req w%=0 act w%=0 ran (large): my 2 oth 0 iops 987 lat 2024.14 us bw = 987.82 MBps dur 59.99 s size 1024 K READ Point 13 (small=0, large=2) of 18 Valid small 1 Valid large 1 Valid ran (small): VLun = 0 Size = 18399232 ran (small): Index = 0 Avg Lat = 616.09 us Count = 97301 ran (small): nio=97301 nior=97301 niow=0 req w%=0 act w%=0 ran (small): my 1 oth 2 iops 1621 lat 616.09 us bw = 12.67 MBps dur 59.99 s size 8 K READ ran (large): VLun = 0 Size = 18399232 ran (large): Index = 0 Avg Lat = 17113.45 us Count = 3429 ran (large): Index = 1 Avg Lat = 17117.93 us Count = 3428 ran (large): nio=6857 nior=6857 niow=0 req w%=0 act w%=0 ran (large): my 2 oth 1 iops 114 lat 17115.69 us bw = 114.27 MBps dur 60.01 s size 1024 K READ Point 14 (small=1, large=2) of 18 Valid small 1 Valid large 1 Valid ran (small): VLun = 0 Size = 18399232 ran (small): Index = 0 Avg Lat = 1344.66 us Count = 44575 ran (small): Index = 1 Avg Lat = 1344.66 us Count = 44574 ran (small): nio=89149 nior=89149 niow=0 req w%=0 act w%=0 ran (small): my 2 oth 2 iops 1486 lat 1344.66 us bw = 11.62 MBps dur 59.96 s size 8 K READ ran (large): VLun = 0 Size = 18399232 ran (large): Index = 0 Avg Lat = 21513.83 us Count = 2784 ran (large): Index = 1 Avg Lat = 21513.99 us Count = 2784 ran (large): nio=5568 nior=5568 niow=0 req w%=0 act w%=0 ran (large): my 2 oth 2 iops 92 lat 21513.91 us bw = 92.76 MBps dur 60.02 s size 1024 K READ Point 15 (small=2, large=2) of 18 Valid small 1 Valid large 1 Valid ran (small): VLun = 0 Size = 18399232 ran (small): Index = 0 Avg Lat = 1538.37 us Count = 38983 ran (small): Index = 1 Avg Lat = 1538.39 us Count = 38983 ran (small): Index = 2 Avg Lat = 1538.39 us Count = 38983 ran (small): nio=116949 nior=116949 niow=0 req w%=0 act w%=0 ran (small): my 3 oth 2 iops 1949 lat 1538.38 us bw = 15.23 MBps dur 59.99 s size 8 K READ ran (large): VLun = 0 Size = 18399232 ran (large): Index = 0 Avg Lat = 12383.15 us Count = 4677 ran (large): Index = 1 Avg Lat = 12386.13 us Count = 4677 ran (large): nio=9354 nior=9354 niow=0 req w%=0 act w%=0 ran (large): my 2 oth 3 iops 155 lat 12384.64 us bw = 155.87 MBps dur 60.01 s size 1024 K READ Point 16 (small=3, large=2) of 18 Valid small 1 Valid large 1 Valid ran (small): VLun = 0 Size = 18399232 ran (small): Index = 0 Avg Lat = 2122.28 us Count = 28254 ran (small): Index = 1 Avg Lat = 2122.31 us Count = 28254 ran (small): Index = 2 Avg Lat = 2122.32 us Count = 28253 ran (small): Index = 3 Avg Lat = 2122.06 us Count = 28253 ran (small): nio=113014 nior=113014 niow=0 req w%=0 act w%=0 ran (small): my 4 oth 2 iops 1884 lat 2122.24 us bw = 14.72 MBps dur 59.97 s size 8 K READ ran (large): VLun = 0 Size = 18399232 ran (large): Index = 0 Avg Lat = 13678.64 us Count = 4258 ran (large): Index = 1 Avg Lat = 13678.83 us Count = 4258 ran (large): nio=8516 nior=8516 niow=0 req w%=0 act w%=0 ran (large): my 2 oth 4 iops 141 lat 13678.73 us bw = 141.91 MBps dur 60.01 s size 1024 K READ Point 17 (small=4, large=2) of 18 Valid small 1 Valid large 1 Valid ran (small): VLun = 0 Size = 18399232 ran (small): Index = 0 Avg Lat = 3464.10 us Count = 17309 ran (small): Index = 1 Avg Lat = 3464.15 us Count = 17308 ran (small): Index = 2 Avg Lat = 3463.77 us Count = 17308 ran (small): Index = 3 Avg Lat = 3463.76 us Count = 17308 ran (small): Index = 4 Avg Lat = 3463.75 us Count = 17308 ran (small): nio=86541 nior=86541 niow=0 req w%=0 act w%=0 ran (small): my 5 oth 2 iops 1443 lat 3463.90 us bw = 11.28 MBps dur 59.96 s size 8 K READ ran (large): VLun = 0 Size = 18399232 ran (large): Index = 0 Avg Lat = 17285.78 us Count = 3448 ran (large): Index = 1 Avg Lat = 17290.24 us Count = 3447 ran (large): nio=6895 nior=6895 niow=0 req w%=0 act w%=0 ran (large): my 2 oth 5 iops 114 lat 17288.01 us bw = 114.85 MBps dur 60.04 s size 1024 K READ Point 18 (small=5, large=2) of 18 Valid small 1 Valid large 1 Valid TEST END Usage: [oracle]:> orion –help ORION: ORacle IO Numbers — Version 11.2.0.1.0 ORION runs IO performance tests that model Oracle RDBMS IO workloads. It measures the performance of small (2-32K) IOs and large (128K+) IOs at various load levels. Each Orion data point is a test for a specific mix of small and large IO loads sustained for a duration. An Orion test consists of multiple data point tests. These data point tests can be represented as a two-dimensional matrix. Each column in the matrix represents data point tests with the same small IO load, but varying large IO loads. Each row represents data point tests with the same large IO load, but varying small IO loads. An Orion test can be for a single point, a single row, a single column, or the whole matrix. The ‚run‘ parameter is the only mandatory parameter. Defaults are indicated for all other parameters. For additional information on the user interface, see the Orion User Guide. <testname> is the prefix used for all input and output filenames. By default, it is ‚orion‘. It can be specified with the ‚testname‘ parameter. <testname>.lun should contain a carriage-return-separated list of LUNs. The output files for a test run are prefixed by <testname>_<date> where date is „yyyymmdd_hhmm“. The output files are: <testname>_<date>_summary.txt – Summary of the input parameters, along with the minimum small IO latency (in usecs), the maximum MBPS, and the maximum IOPS observed. <testname>_<date>_mbps.csv – Performance results of large IOs in MBPS. <testname>_<date>_iops.csv – Performance results of small IOs in IOPS. <testname>_<date>_lat.csv – Latency of small IOs (in usecs). <testname>_<date>_trace.txt – Extended, unprocessed output. WARNING: IF YOU ARE PERFORMING WRITE TESTS, BE PREPARED TO LOSE ANY DATA STORED ON THE LUNS. Mandatory parameters: run Type of workload to run (simple, normal, advanced, dss, oltp). simple – Tests random small (8K) IOs at various loads, then random large (1M) IOs at various loads. normal – Tests combinations of random small (8K) IOs and random large (1M) IOs. advanced – Tests the workload specified by the user using optional parameters. dss – Tests with random large (1M) IOs at increasing loads to determine the maximum throughput. oltp – Tests with random small (8K) IOs at increasing loads to determine the maximum IOPS. Optional parameters: testname Name of the test run. num_disks Number of disks (physical spindles). This number is used to gauge the range of loads that Orion should test at. Increasing this parameter results in Orion using heavier IO loads. Default is the number of LUNs in <testname>.lun. size_small Size of small IOs in KB. Default is 8. size_large Size of large IOs in KB. Default is 1024. type Type of large IOs (rand, seq). Default is rand. rand – Randomly distributed large IOs. seq – Sequential streams of large IOs. num_streamIO Number of concurrent IOs per stream. This parameter is only used if -type is seq. Default is 4. simulate Orion tests on a virtual LUN formed by combining the specified LUNs in one of these ways. This parameter is typically only used if -type is seq. Default is concat. concat – A serial concatenation of the LUNs. Each sequential stream issues IOs to only one LUN. raid0 – A RAID-0 mapping across the LUNs. Each sequential stream issues IOs across all LUNs, using RAID-0 striping. write Percentage of IOs that are writes (SEE WARNING ABOVE). Default is 0. cache_size Size in MBs of the array’s cache. Unless this option is set to 0, Orion issues a number of unmeasured, random IOs before each large sequential data point. These IOs fill up the storage array’s cache (if any) with random data so that IOs from one data point do not result in cache hits for the next data point. Read tests are preceded with junk reads and write tests are preceded with junk writes. If specified, this ‚cache warming‘ is performed until cache_size MBs of IO have been read or written. Default behavior is to issue 2 minutes of unmeasured random IOs before each data point. duration Duration of each data point in seconds. Default is 60. num_small Number of outstanding small IOs. This parameter controls the small IO load. Only used if -matrix is point, col, or max. No default. num_large This parameter controls the large IO load. For -type rand, number of outstanding large IOs. For -type seq, number of sequential IO streams. Only used if -matrix is point, row, or max. No default. matrix An Orion test consists of multiple data point tests. These data point tests can be represented as a two-dimensional matrix. Each column in the matrix represents data point tests with the same small IO load, but varying large IO loads. Each row represents data point tests with the same large IO load, but varying small IO loads. An Orion test can be for a single point, a single row, a single column, or the whole matrix, depending on the matrix option setting below. Default is basic. basic – Test small IOs only, then large IOs only. detailed – Test entire matrix. point – Test with num_small small IOs, num_large large IOs. col – Test a varying large IO load with num_small small IOs. row – Test a varying small IO load with num_large large IOs. max – Test varying loads up to the num_small and num_large limits. verbose Prints tracing information to standard output if set. Not set by default. Examples: For a preliminary set of data -run simple For a basic set of data -run normal To evaluate storage for an OLTP database -run oltp To evaluate storage for a data warehouse -run dss To generate combinations of 32KB and 1MB reads to random locations -run advanced -size_small 32 -size_large 1024 -type rand -matrix detailed To generate multiple sequential 1MB write streams, simulating RAID0 striping -run advanced -simulate RAID0 -write 100 -type seq -matrix col -num_small 0