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:
- Execute CALIBRATE_IO on the existing storage: Run the procedure to obtain baseline values for maximum IOPS, MB/s, and latency
- Perform the test migration: Migrate the database to the new storage system
- Execute CALIBRATE_IO on the new storage: Run the procedure again to get the performance values for the new storage system
- 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.