Monitoring and Testing IO

1
Monitoring and Testing I/O

• Using Perfmon, Oracle tools and IOMeter for
  monitoring and load testing the I/O Subsystem

2
Objectives

• At the end of this module the student will
  understand the following tasks and concepts.
• Monitoring the I/O Subsystem using Perfmon
• Monitoring the I/O Subsystem using Oracle Tools
• Load Testing the I/O Subsystem using TTCP

3
Oracle and I/O

• I/O performance is crucial to Oracle Performance
• Data buffers are sensitive to read I/O latency
• Log buffers are sensitive to write I/O latency

4
Oracle I/O ProblemsPerformance Degradation

• Index lookup
• 10 I/Os
• 10 x 6 ms 60 ms (not bad)
• 10 x 100 ms 1 sec (noticeable)
• Table Scan
• 30,000 I/Os
• 30,000 x 6 ms 90 sec (not bad for a Table Scan)
• 30,000 x 100 ms 3,000 sec 50 minutes
• You are getting phone calls and emails

5
Monitoring I/O PerformancePerfmon

• General Tips
• Set the sample rate up
• 1 sec is too fast
• Not accurate on all counters
• Not enough time to see all the counters
• Way to fast for saving the data
• Heizenburg Uncertainty Principle
• Use both graph and report as appropriate
• Save data if possible

6
Monitoring I/O PerformancePerfmon

• Logical Disk Counters
• Lists the Physical Disks by Drive Letter
• Good for monitoring file I/O
• Physical Disk Counters
• Lists the Physical Disks by Physical Disk
• Good for monitoring the LUNs or Volumes

7
Monitoring I/O PerformancePerfmon

• Latencies
• IOPS (I/Os Per Second)
• Throughput
• I/O sizes

8
Monitoring I/O PerformancePerfmon

• Latencies
• Avg. Disk sec/Read
• Provides Read Latency Statistics
• Avg. Disk sec/Write
• Provides Write Latency Statistics

9
Monitoring I/O PerformanceLatency
10
Monitoring I/O PerformancePerfmon

• IOPS
• Disk Reads/sec
• Disk Transfers/sec
• Useful for total volume IOPS (remember RAID
  overhead)
• Disk Writes/sec

11
Monitoring I/O PerformanceIOPS
12
Monitoring I/O PerformancePerfmon

• Throughput
• Disk Bytes/sec
• Total Throughput
• Disk Read Bytes/sec
• Disk Write Bytes/sec

13
Monitoring I/O PerformanceThroughput
14
Monitoring I/O PerformancePerfmon

• I/O sizes
• Avg. Disk Bytes/Read
• Avg. Disk Bytes/Write

15
Monitoring I/O PerformanceI/O Sizes
16
Monitoring I/O PerformancePerfmon

• Miscellaneous
• Current Disk Queue Length

17
Monitoring I/O PerformanceOracle Tools

• OEM Performance Page
• Instance Disk I/O
• Instance Throughput

18
Monitoring I/O PerformanceOracle Tools

• Oracle Counters for Windows Performance Monitor
• Add Oracle Data Files counter
• physreads/sec
• Physwrites/se
• Choose data files to monitor
• Shows I/O for database files

19
Monitoring I/O PerformanceOracle Tools

• STATSPACK Reports
• Tablespace IO Stats
• Data File IO Stats

20
Load Testing I/O

• Used to determine I/O problems in a controlled
  and scientific manner
• Used to identify the upper limits of the storage
  subsystem
• Used to prove or disprove hypothesis or theories

21
Load Testing I/OIOMeter

• IOMeter is a public domain utility
• Available at http//www.sourceforge.net
• Generates I/O based on your specifications
• Provides accurate results
• Is repeatable

22
Load Testing I/OIOMeter

• Made up of two major components
• GUI
• Used for configuration and results
• Dynamo
• Used to generate the load

23
Load Testing I/OIOMeter

• Configuring IOMeter
• Disk Targets
• Network Targets
• Access Specifications
• Results Display
• Test Setup

24
Load Testing I/ODisk Targets

• Maximum Disk Size
• For random I/Os this must be sufficiently large
• of Outstanding I/Os
• Defines concurrency of I/Os
• If set to one there will only be one I/O issued
  at a time and IOPS 1 / latency

25
Load Testing I/ONetwork Targets

• Used for network I/O testing

26
Load Testing I/OAccess Specifications

• Specifies the type of I/O
• Block Size
• Read/Write ratio
• Sequential/Random Ratio
• Create new spec for Oracle

27
Load Testing I/OTest Setup

• Used to set up automated tests
• Run time
• Ramp up
• Number of workers
• Etc.

28
Load Testing I/OResults Display

• View the performance during the test
• IOPS
• Throughput
• Latency
• CPU Utilization

29
Review

• What type of I/O problem causes the biggest
  problem with Oracle Data Files ?
• Name some Perfmon counters to monitor for I/O?
• What counter does the Oracle Counters for Windows
  Performance Monitor add to Perfmon that measures
  Oracle I/O?
• What are the two components of IOMeter?

30
Summary

• Monitoring the I/O Subsystem using Perfmon
• Monitoring the I/O Subsystem using Oracle Tools
• Load Testing the I/O Subsystem using TTCP