Testing Overview
This is a through analysis of the Oracle Database Appliance (ODA) System IO performance. The data collected during testing was carefully reviewed.
Test Scenario
There is a total of sixteen (16) HDD Data disks in the Production Primary X5-2 ODA device. During testing, the HDD Disk IOPS Stats were captured for each disk. The test aimed at collecting performance statistics for Individual Disk Wise IO during each of the planned Test Run(s).
Test Objective(s)
The main objective for the test was to collect performance statistics for Individual Disk Wise IO per HDD Disk and then compare the performance of the X5.2 MI-CXT database against the Oracle Database Appliance Standard Performance Ratings for the main data processing metrics:
- IO Per Second (HDD/Flash)
- MB Per Second (HDD/Flash)
- Log Generation Rate (MB/Sec)
For the purposes of these performance tests, the Oracle Database Appliance X5-2 test results were measured against the Oracle Database Appliance Sizing Templates (OLTP)[1] where:
| Configuration Parameter | Where Value Equals |
| CPU Count | 16 (vCPU) |
| SGA (Gb) | 27 (Gb) |
| PGA (Gb) | 10 (Gb) |
| Flash (Gb) | 12 (Gb) |
| Log Buffer Size (Mb) | 16 (Mb) |
| Processes Redo Log | 2 (Gb) |
| Processes | 3000 (Max) |
Test Standards
Pass/Fail Standards
| Standard Performance Ratings: Main Data Processing Metrics | Optimal Threshold Value for HDD/Flash |
| A. IO Per Second (HDD/Flash) | 750/75000 |
| B. MB Per Second (HDD/Flash) | 1500/1500 |
| C. Log Generation Rate (MB/Sec) | 27.3 |
| PASS/FAIL Criteria |
| PASS= Where System IO < Optimal Threshold |
| FAIL = Where System IO > Optimal Threshold |
Test Summary of Findings
System IO Overall Performance
Disk IO per second during each Test Run fluctuated between minimum 150 to maximum 400 ops.
Test Results Outcome = Pass
For details per Test Run, please see attached.
Test Conclusion(s)
The X5.2 IOSTAT results from OS watcher reported Disk IO per second fluctuations between minimum 150 to maximum 400 ops for each Test Run.
With the Read & Write (RW) per second performance tested using 8k blocks at different intervals, maximum 2500 MB per second during read operation(s) and 653 MB per sec during write operation(s) was observed.
Therefore, based on these outcomes, X5-2 ODA HDD Disks clearly demonstrated to have enough System IO capacity for the task as per the ODA performance standards mentioned in the table shown below[1].
In fact, the X5-2 IO Per Second and MB Per Second can be scaled up to max 1500 IO per second/3000 MB per second with ONE X5-2 storage shelf and can be doubled with TWO X5-2 storage shelfs.
AWR Findings for DBA Consideration
Manual Data Read & Write (RW) Tests
- 1 GB of Data Write per seconds with 8k block is 350Mb/s to 650 Mb/s (approx).
[root@nasodacxt0201d1 datastore]# time dd if=/dev/zero of=testfile bs=8k count=128000
128000+0 records in
128000+0 records out
1048576000 bytes (1.0 GB) copied, 1.60589 s, 653 MB/s
real 0m1.700s
user 0m0.030s
sys 0m1.599s
[root@nasodacxt0201d1 datastore]#
- 1 GB of Data Read per seconds with 8k block is 1 to 2 Gb/s (approx)
[root@nasodacxt0201d1 datastore]# time dd if=testfile of=/dev/null bs=8k
128000+0 records in
128000+0 records out
1048576000 bytes (1.0 GB) copied, 0.405152 s, 2.6 GB/s
real 0m0.407s
user 0m0.016s
sys 0m0.382s
[root@nasodacxt0201d1 datastore]#
| db file sequential read | 379,827 | 8438.3 | 22.22 | 52.4 | User I/O |
| db file parallel read | 86,190 | 6872 | 79.73 | 42.7 | User I/O |
I/O Wait Definitions
The “I/O Wait” is an administrative wait class such as log files and it originates from Oracle Operations. User I/O is an application wait class such as a database file (dbf) and it originates from User (Application) SQL Queries.
- User IO is an application related wait and NOT system IO.
AWR report shows User IO as Top Wait Classes by Total Wait Time consuming 95.9% of DB Time during 30-Jan-18 01:00:19 to 30-Jan-18 02:00:28 and 51.3% of DB time during 30-Jan-18 02:00:28 and 30-Jan-18 03:00:38
- With User IO events listed as the top two (2) out of the Top 10 Foreground Events by Total Wait Time, it appears that read operations on the datafiles lying on HDD maybe behind the high database IO we have observed. It is our suggestion for our CHC CXT Logical DBA to inspect the Application SQL scripts as ordered by User I/O Wait Time to determine if further SQL Tuning may be needed.
| db file sequential read | 379,827 | 8438.3 | 22.22 | 52.4 | User I/O |
| db file parallel read | 86,190 | 6872 | 79.73 | 42.7 | User I/O |
When a DBA sees “User I/O” as a major wait event, SQL tuning is always the best answer. Industry standards best practices for SQL tuning usually involve adding missing indexes, hints, SQL profiles, etc.
————————————————————————————————————
Best Practices For Disk IO Improvement(s) for Database on ODA
Below are the some Oracle DBA industry best practice recommendations for Improved disk I/O.
I: Enable Smart Flash Cache below
ALTER SYSTEM SET DB_FLASH_CACHE_FILE = ” SCOPE=SPFILE;
ALTER SYSTEM SET DB_FLASH_CACHE_SIZE = 30G SCOPE=SPFILE;
II: Database Auditing Configuration
If auditing needs to be enabled, move aud$, fga$ out of SYSAUX tablespace to a custom tablespace.
III: ODA recommendations
DB parameter ‘db_lost_write_protect’ to NONE
[1] http://www.oracle.com/technetwork/database/database-appliance/documentation/oda-eval-comparing-performance-1895230.pdf.
Orama.blog 