Measuring Storage Performance for Oracle DBs

 

Intro

Storage vendors often provide extensive product performance data, but this information may not accurately predict how the storage will perform with an Oracle database. This article offers practical methods to evaluate storage system performance, including Oracle-provided utilities.

There are two main methods to analyze the performance of storage.

• Oracle Orion
• Oracle procedure: DBMS_RESOURCE_MANAGER.calibrate_io.

I will show you how to measure IO using both methods in this article. 

The easiest way to use procedure DBMS_RESOURCE_MANAGER.calibrate_io. 

In Oracle, the DBMS_RESOURCE_MANAGER package provides procedures to manage database resources. To generate a read-only workload to measure the maximum number of IOPS (Input/Output Operations Per Second) and MB/s (Megabytes per second), you can use the CALIBRATE_IO procedure. This procedure helps in determining the I/O capabilities of the storage subsystem.

What is Oracle Calibrate? 

Oracle's CALIBRATE_IO is a procedure within the DBMS_RESOURCE_MANAGER package that measures the I/O performance of the storage subsystem associated with an Oracle database. It helps database administrators determine the maximum Input/Output Operations Per Second (IOPS) and the maximum throughput in megabytes per second (MB/s) that the storage system can sustain under a read-only workload.

If you want more information about the DBMS_RESOURCE_MANAGER.calibrate_io. you can refer to the mentioned link.

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_RESOURCE_MANAGER.html

Benefits:

• Performance Tuning: Helps in identifying storage bottlenecks and tuning the database configuration for optimal performance.

• Capacity Planning: Provides insights into the storage system's capabilities, aiding in capacity planning and future upgrades.

• Benchmarking: Allows for benchmarking the storage performance, which can be useful for comparing different storage systems or configurations.

To ensure an accurate comparison of storage performance values, follow these steps:

1. Execute CALIBRATE_IO on the existing storage: Run the procedure to obtain baseline values for maximum IOPS, MB/s, and latency.
2. Perform the test migration: Migrate the database to the new storage system
3. Execute CALIBRATE_IO on the new storage: Run the procedure again to get the performance values for the new storage system.
4. Compare the values: Analyze the results to determine the performance differences between the old and new storage systems.

Parameters required by CALIBRATE_IO procedure :

• num_physical_disks: Specifies the number of physical disks in the storage system.
• max_latency: Defines the maximum acceptable latency (in milliseconds) for the I/O operations.
• Output Variables: Variables to store the results of the procedure, including max_iops, max_mbps, and actual_latency.

What do we need to consider before executing in production?

• The calibration procedure is generating a high load, make sure to run this in off-peak time. 

DECLARE
  l_max_iops       BINARY_INTEGER;
  l_max_mbps       BINARY_INTEGER;
  l_actual_latency BINARY_INTEGER;
BEGIN
  -- Generate a read-only workload and determine maximum IOPS and MB/s
  DBMS_RESOURCE_MANAGER.CALIBRATE_IO (
    num_physical_disks => 4,  -- Number of physical disks in the storage
    max_latency        => 20, -- Maximum tolerable latency in milliseconds
    max_iops           => l_max_iops,  -- Output: Maximum IOPS
    max_mbps           => l_max_mbps,  -- Output: Maximum MB/s
    actual_latency     => l_actual_latency -- Output: Actual latency in milliseconds
  );

  -- Display the results
  DBMS_OUTPUT.PUT_LINE('Max IOPS: ' || l_max_iops);
  DBMS_OUTPUT.PUT_LINE('Max MB/s: ' || l_max_mbps);
  DBMS_OUTPUT.PUT_LINE('Actual Latency: ' || l_actual_latency || ' ms');
END;
/

Expected output

SQL> SET SERVEROUTPUT ON
DECLARE
  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;
/
SQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16



max_iops = 11518
latency  = 9.413
max_mbps = 64
Max IOPS = 11518
Max MBPS = 64
Latency  = 9

PL/SQL procedure successfully completed.

It can be challenging to gain a clear understanding of storage performance. For more precise values, it is often better to analyze database performance using another tool. However, Oracle Orion is an excellent tool for obtaining storage performance metrics for comparisons.

What is Oracle Orion?

Oracle Orion is a tool designed to measure the performance of an I/O subsystem by simulating Oracle database I/O workloads. It helps database administrators evaluate the capabilities of their storage systems before deploying Oracle databases or making significant changes to the storage configuration.

Key Features of Oracle Orion:

• Simulation of Database I/O: Orion simulates different types of database I/O workloads, including OLTP (Online Transaction Processing) and DSS (Decision Support Systems), to provide insights into how the storage subsystem will perform under various conditions.

• Benchmarking: Orion can be used to benchmark the performance of storage systems, allowing administrators to compare different configurations or storage solutions.

• IOPS and Throughput Measurement: Orion measures key performance metrics such as IOPS (Input/Output Operations Per Second) and MB/s (Megabytes per second), providing a detailed understanding of the storage performance.

• Latency Measurement: Orion also measures the latency of I/O operations, helping identify potential bottlenecks in the storage subsystem.

For this testing we need to create a file with all the disk names, But an easy method could you create a file under the same storage and use that file for testing.

-- Create a file using dd commands . Here we are creating 10G file under /oradata mount point.

time dd if=/dev/urandom of=/u01/test bs=1M count=100000

local.testdb.oracle>time dd if=/dev/urandom of=/u01/test bs=1M count=100000
0+100000 records in
0+100000 records out

real    1m46.83s
user    0m0.13s
sys     1m46.03s
local.testdb>

Create a file called old-TEST-EBS, add the previously created name to it, and then execute the Orion test.

touch old-TEST-EBS
echo "/u01/test" >> old-TEST-EBS
$ORACLE_HOME/bin/orion -run normal -testname old-TEST-EBS

After the test completes, Orion generates output files such as old-TEST-EBS_summary.txt, which contains the summarized performance metrics.

Example

local.testdb.oracle>$ORACLE_HOME/bin/orion -run normal -testname old-TEST-EBS
ORION: ORacle IO Numbers -- Version RDBMS_19.3.0.0.0DBRU_SOLARIS.SPARC64_190417.190419
old-TEST-EBS_20240423_1700
Calibration will take approximately 19 minutes.
Using a large value for -cache_size may take longer.

Setting ftype=0
Maximum Large MBPS=413.65 @ Small=3 and Large=2

Maximum Small IOPS=13536 @ Small=5 and Large=0
Small Read Latency: avg=368.399 us, min=12.000 us, max=17290.000 us, std dev=228.665 us @ Small=5 and Large=0

Minimum Small Latency=338.806 usecs @ Small=4 and Large=0
Small Read Latency: avg=338.806 us, min=11.000 us, max=19356.000 us, std dev=229.915 us @ Small=4 and Large=0
Small Read / Write Latency Histogram @ Small=4 and Large=0
        Latency:                         # of IOs (read)          # of IOs (write)
        0 - 8          us:               0 (  0.00)               0 (  0.00)
        8 - 16         us:            5815 (  0.82)               0 (  0.00)
       16 - 32         us:           88749 ( 13.39)               0 (  0.00)
       32 - 64         us:            5391 ( 14.15)               0 (  0.00)
       64 - 128        us:            1672 ( 14.39)               0 (  0.00)
      128 - 256        us:           57836 ( 22.57)               0 (  0.00)
      256 - 512        us:          433729 ( 83.98)               0 (  0.00)
      512 - 1024       us:          111811 ( 99.80)               0 (  0.00)
     1024 - 2048       us:             862 ( 99.93)               0 (  0.00)
     2048 - 4096       us:             404 ( 99.98)               0 (  0.00)
     4096 - 8192       us:              58 ( 99.99)               0 (  0.00)
     8192 - 16384      us:              59 (100.00)               0 (  0.00)
    16384 - 32768      us:               5 (100.00)               0 (  0.00)
    32768 - 268435456  us:               0 (100.00)               0 (  0.00)
local.testdb.oracle>

You can use below mentioned summarized table to analyze the performance.

Conclusion

For Quick Assessments: Use Oracle Calibrate_IO for quick and easy assessments of storage performance directly within the Oracle database environment.

For Detailed Analysis: Use Oracle Orion for more detailed and flexible performance analysis, especially when comparing different storage configurations or conducting thorough benchmarks.

By leveraging both tools appropriately, database administrators can gain a comprehensive understanding of their storage performance, ensuring optimal configuration and performance of their Oracle databases.