X5.2 Oracle Database Appliance System IO Assessment

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:

  1. IO Per Second (HDD/Flash)
  2. MB Per Second (HDD/Flash)
  3. 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. 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. 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.

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s