The following Blog discusses the process and results of testing I/O performance of an Oracle Database running on Oracle’s Cloud Infrastructure (OCI) compared to the same database running on Amazon Web Services (AWS). The Oracle Stress Testing Tool (SLOB) was used to test Data Load Performance, IOPS, physical random single-block reads and single block writes. The OCI OCPU Scaling feature was also tested and a cost analysis was performed to determine the configuration with the lowest TCO.

The results suggest that Oracle’s infrastructure is between 7 and 10 times faster in I/O than the AWS counterpart, using only a quarter of the time to load the same amount of data. Furthermore, OCI outperformed AWS significantly in wait times for critical I/O events, which indicates a superior I/O latency on OCI. Finally, the TCO of running the system on AWS is twice as high as the bill would be if the system was run on OCI. Oracle’s performance dominance can be partially explained by the unique and differentiated capabilities of Oracle’s next generation Baremetal infrastructure, which is very different to the architecture of AWS, but more on this later.

The following sections cover the details of the testing procedure and elaborate on the results.

  1. Test Objective:
  2. Infrastructure:
    1. OCI
    2. AWS
  3. I/O Stress Testing Tool (SLOB):
    1. Installation
    2. Configuration
  4. Testing:
    1. Testing Data Load Performance OCI vs AWS
      1. OCI Performance Charts
      2. AWS Performance Charts
    2. Testing Online CPU Scale Up/Down - OCI
    3. Testing physical random single-block reads (db file sequential read) - OCI vs AWS
      1. OCI Performance Charts
      2. AWS Performance Charts
    4. Testing random single block writes (DBWR flushing capacity) - OCI
    5. Read Performance Comparison - OCI
  5. Conclusion:

I. Test Objective:

To compare Read I/O performance of Oracle Database on OCI and AWS.

II. Infrastructure:

The following two infrastructures were considered:

OCI

BM.HighIO1.36 => OCPU: 36, 512GB RAM, 12.8TB(4*3.2TB) locally attached NVMe SSD, Usable Storage with Normal Redundancy (2-way Mirroring) is DATA 3.5 TB, RECO 740 GB
Compute Bare Metal Instance price is $0.102 OCPU/hr
BYOL High I/O Enterprise Edition price is $4.8146/hr

  1. Setup OCI Database with appropriate parameter changes
  2. Configure OEM in OCI Database Host
  3. Allow access of OEM port 1158 from public
  4. Enable Performance tab in OEM console
  5. Install and Configure SLOB on the database box

Monthly Running Cost
(8-2) OCPU * 24 hrs * 30 days * $0.136 = 588$
24 hrs * 30 days * $4.8146 = 3467$
Total Monthly Price = $4055 (Excluding Oracle License Price)

Oracle RDBMS perpetual License Cost (List price, Enterprise edition, no extra options)
8 OCPU * 0.5 * 47500$/License = $190000

AWS

db.m4.4xlarge => 16vCPU, 64GB RAM, PIOPS-Optimized: Yes, Network Performance: High, Database Storage 4TB SSD, Provisioned IOPS 40000
Single AZ, BYOL, US West Oregon db.m4.4xlarge price is $1.401 per hour
Storage price is $0.125 per GB-month
Provisioned IOPS price is $0.10 per IOPS-month

  1. Setup AWS RDS with appropriate parameter group, 4TB storage and 40K IOPS
  2. Setup EC2 instance for AWS RDS
  3. Make changes to Security Group to allow EC2 instance to connect to RDS Database
  4. Install Oracle XE on EC2 (utilities like SQLplus, TNSPING, awrrpt*.sql scripts are used by SLOB)
  5. Verify database connectivity from EC2 to RDS
  6. Install pre-requisites for SLOB; gcc, sysstat
  7. Install and Configure SLOB on EC2

Monthly Running Cost
40000 PIOPS * $0.10 = 4000$
4096 GB * $0.125 = 512$
24 hrs * 30 days * $1.401 = 1009$
Total Monthly Price = $5521 (Excluding Oracle License Price)

Oracle RDBMS perpetual License Cost (List price, Enterprise edition, no extra options)
16 VCPU * 0.5 * 47500$/License = $380000

Note:
1OCPU=2vCPUs
RDS has a limit of 16TB storage and 40000 IOPS for Oracle DB

III. I/O Stress Testing Tool (SLOB):

SLOB is not a database benchmark. SLOB is an Oracle I/O workload generation tool kit.
SLOB is a non-transactional database I/O workload generator

SLOB possesses the following characteristics:

    1. SLOB supports testing Oracle logical read (SGA buffer gets) scaling
    2. SLOB supports testing physical random single-block reads (DB file sequential read)
    3. SLOB supports testing random single block writes (DBWR flushing capacity)
    4. SLOB supports testing extreme REDO logging I/O
    5. SLOB consists of simple PL/SQL
    6. SLOB is entirely free of all application contention

Note:

SLOB does not work on multi tenant databases, so ensure you use Oracle 11.2.0.4 for testing.

Installation

SLOB 2.4 is only compatible with 11g Database and Client.
For OCI, install SLOB on the database system, for AWS RDS install it on the EC2 instance.

Configuration

Create a tablespace for loading data.
Load the data for stress testing using SLOB (creates 256 schemas each with 1G of data)

nohup ./setup.sh IOPS 256 &

View this sample slob.conf for Loading Data for more details:

SCALE=1G  
LOAD_PARALLEL_DEGREE=4  
DATABASE_STATISTICS_TYPE=awr  
ADMIN_SQLNET_SERVICE=“slob_phx1hx" #for OCI  
#ADMIN_SQLNET_SERVICE=“slob1" #for RDS  
SQLNET_SERVICE_BASE="slob_phx1hx”  
#SQLNET_SERVICE_BASE=“slob1"  
DBA_PRIV_USER=“system” #for OCI  
#DBA_PRIV_USER=“admin” #for RDS  
SYSDBA_PASSWD=“...”  

IV. Testing:

Notes:

  1. If you want to test your I/O subsystem with genuine Oracle SGA-buffered physical I/O it is best to not combine that with application contention.
  2. The basis of the default, contention-free behavior of SLOB is the sparse data block. With multiple or single schema, the threads_per_schema=1 or if it is > 1, and the data set is large, this ensures threads do not contend for the same block.
  3. When using a Single schema to create application contention, have multiple threads per schema. If there is “read by other session” this means CPU cycles spent did not result in a physical I/O, and there is application contention
  4. Multiple schema can be used to test multi tenant architecture Single schema run => sh . /runit.sh 1, with threads_per_schema=1 or 2 (ensure no contention)
  5. multiple schema run => sh ./runit.sh <no_of_schemas>
  6. Metrics of interest for Physical I/O Testing are SQL Executions, Physical IOPS (a.k.a., PIOPS)

Here is a sample slob.conf :

SCALE=1GB (active data set) 131072 blocks  
WORK_UNIT=64 (no of blocks read/updated by a single select/update)  
DATABASE_STATISTICS_TYPE=awr  
ADMIN_SQLNET_SERVICE=slob  
SQLNET_SERVICE_BASE=slob  
DBA_PRIV_USER=“system”  
SYSDBA_PASSWD=“”  
THREADS_PER_SCHEMA=1  
SCAN_PCT=0  
RUN_TIME=600  
WORK_LOOP=0  
REDO_STRESS=LITE  
UPDATE_PCT=0  
DO_HOTSPOT=FALSE  
HOT_SCHEMA_FREQUENCY=0 (disable hot scheme)  
THINK_TM_FREQUENCY=0 (disable think time)  

1. Testing Data Load Performance OCI vs AWS

Data Load Perfomance:

Type - db.m4.4xlarge
CPU - 16vcpu
SGA - 16G
PGA - 4G
Active Data - 256G
DB Connections - 4
Activity - Direct Path insert 256GB of data
Database Version - 11.2.0.4
db_block_size - 8k
Type - BM.HighIO.36
CPU - 8OCPU
SGA - 16G
PGA - 4G
Active Data - 256G
DB Connections - 4
Activity - Direct Path insert 256GB of data
Database Version - 11.2.0.4
db_block_size - 8k
Disk
Write Mb/sec 100-150 1000 (LGWR)
Write Req/sec (IOPS) 1500-2000 1500 (LGWR)
Write Latency msec 8-10 <1
Total Runtime mins 74 17

OCI Performance Charts

Latency, Throughput and IOPS

OCI Performance Charts:  I/O Throughput MB/s, Request/s
Active sessions
OCI Throughput Active sessions and Physical reads

AWS Performance Charts

IOPS (read)

AWS reads IOPS
CPU
AWS Cpu utilization
Latency (read)
AWS read Latency
Throughput (read)
AWS read Throughput
IOPS (write)
AWS write IOPS
Latency (write)
AWS write latency
Throughput (write)
AWS write Throughput

It was found that OCI is 7-10x faster in I/O compared to AWS, with 4x less time to load the same amount of data.

2. Testing Online CPU Scale Up/Down - OCI

During this test, we scaled the number of CPUs up and down in the middle of the data load. It was observed that Data Load was not interrupted and that the operating system reacted to scaling within a few minutes, reflecting the changes in it’s Oracle cpu_count.

On OCI, scaling up and down the CPU occasionally resulted in a short delay before changes were reflected on the OCI UI, but changes are reflected correctly when clogging into the m/c and checking /proc/cpuinfo .

Occasionally it also took some time to reflect the changes in Oracle’s cpu_count parameter
To reflect the change, do the following:

SQL> alter system set cpu_count=0;  
SQL> show parameter cpu_count  
NAME TYPE VALUE  
------------------------------------ ----------- ------------------------------  
cpu_count integer 64 (For cpu core 32 set in OCI UI)  

Or alternatively, run an instance restart for immediate effect.

3. Testing physical random single-block reads (db file sequential read) - OCI vs AWS

It is important at this stage to restart Oracle, so that the database cache is empty and every read is a Physical IO (otherwise db_cache_size should be very small, so active data set does not fit in).
In our case SGA is 16G and the active data set is 256GB.

nohup sh ./runit.sh 256 &

RDS vs OCI Read Perfomance

Type - db.m4.4xlarge
CPU - 16vcpu
SGA - 16G
PGA - 4G
Active Data - 256G
DB Connections - 256
R/W Ratio - 1000
Database Version - 11.2.0.4
db_block_size - 8k
Type - BM.HighIO.36
CPU - 8OCPU
SGA - 16G
PGA - 4G
Active Data - 256G
DB Connections - 256
R/W Ratio - 1000
Database Version - 11.2.0.4
db_block_size - 8k
CPU
User CPU % 0.1 47.5
System CPU % 6.4 52.2
IOWait CPU % 26.6 0
Idle CPU % 42 0.2
Load Average 222.5 235.5
Disk
Read Mb/sec 237.2 2503.8
Read Req/sec (IOPS) 30,362.8 320,399.5
Read Latency msec 25 9
Oracle Metrics
DB CPU 9.5 15.6
Buffer Hit % 8.12 7.63
Top Event db file parallel read
Total Waits - 294,667
Avg Wait Time (ms) - 149
% DB Time - 57.3
db file parallel read
Total Waits - 6,267,171
Avg Wait Time (ms) - 16
% DB Time - 66.6
Monthly Infrastructure Cost $5521/$380000 $4055/$190000

OCI Performance Charts

Latency, Throughput and IOPS

OCI Throughput and Latency
Load average, Active sessions
OCI Load perfomance and Throughput

AWS Performance Charts

IOPS (read)

AWS  Read IOPS
Latency (read)
AWS  Read Latency
Throughput (read)
AWS  Read Throughput

  1. The results of these tests suggest that OCI is up to 10x faster in I/O compared to Amazon. This can be observed when the charts for Read Mb/sec and Read IOPS are evaluated. The same holds true in the average wait times recorded for the Oracle Event “db parallel file read”.
  2. Furthermore, it seems that a lot of CPU time is wasted with I/O interrupts in AWS, whereas
    the CPU is completely utilized in OCI. This indicates a limitation of the I/O subsystem which may be worth examining in a future blog.

4. Testing random single block writes (DBWR flushing capacity) - OCI

Following testing is OCI only.

First, it is necessary to reduce the SGA size (and thus DB cache) to observe any impact on DataBase Writer (DBWR) performance.

nohup sh ./runit.sh 256 &  

DBWR Perfomance

Type - BM.HighIO.36
CPU - 2OCPU
SGA - 16G
PGA - 4G
Active Data - 256G
DB Connections - 256
R/W Ratio - 9010
Database Version - 11.2.0.4
db_block_size - 8k
Type - BM.HighIO.36
CPU - 16OCPU
SGA - 16G
PGA - 4G
Active Data - 256G
DB Connections - 256
R/W Ratio - 9010
Database Version - 11.2.0.4
db_block_size - 8k
CPU
User CPU % 60.2 38.2
System CPU % 36.8 49.5
IOWait CPU % 0 0
Idle CPU % 2.9 12.3
Load Average 89.4 165.6
Disk
Read Mb/sec 418.4 2,180.7
Write Mb/sec 41.6 235.8
Read Req/sec (IOPS) 53,353.5 278,741.4
Write Req/sec (IOPS) 5,322.3 30,179.9
Read Latency msec (db file sequential read) 2 1
Write Latency msec (direct path write) 3 1
Oracle Metrics
DB CPU 3.2 24.6
Executes (SQL)/sec 921.6 4,660.7
Buffer Hit % 8.76 8.44
Max Active Sessions 297 294
Top Event free buffer waits
Total Waits - 5,825,636
Avg Wait Time (ms) - 16 % DB Time - 59.5
db file sequential read
Total Waits - 37,261,822
Avg Wait Time (ms) - 1
% DB Time - 38.0
free buffer waits
Total Waits - 2,481,210
Avg Wait Time (ms) - 11
% DB Time - 34.2

  1. The CPU was a bottleneck in the column 1 configuration, hence why Read and Write IOPS were impacted.
  2. Increasing the number of CPUs in column 2 configuration shows that the CPU was completely utilized, thereby increasing Read and Write IOPS and the number of SQLs executed.
  3. “Free buffer waits” is still the 2nd top oracle event within the second column’s configuration, impacting DBWR performance since the SGA/db buffer is small and DBWR waits for free buffers.

5. Read Performance Comparison - OCI

OCI Read Perfomance

Type - BM.HighIO.36
CPU - 2OCPU
SGA - 16G
PGA - 4G
Active Data - 256G
DB Connections - 256
R/W Ratio - 1000
Database Version - 11.2.0.4
db_block_size - 8k
Type - BM.HighIO.36
CPU - 8OCPU
SGA - 16G
PGA - 4G
Active Data - 256G
DB Connections - 256
R/W Ratio - 1000
Database Version - 11.2.0.4
db_block_size - 8k
Type - BM.HighIO.36
CPU - 8OCPU
SGA - 16G
PGA - 4G
Active Data - 256G
DB Connections - 512 (256*2)
R/W Ratio - 1000
Database Version - 11.2.0.4
db_block_size - 8k
Type - BM.HighIO.36
CPU - 16OCPU
SGA - 16G
PGA - 4G
Active Data - 256G
DB Connections - 256
R/W Ratio - 1000
Database Version - 11.2.0.4
db_block_size - 8k
Type - BM.HighIO.36
CPU - 16OCPU
SGA - 8G
PGA - 4G
Active Data - 8G
DB Connections - 8
R/W Ratio - 1000
Database Version - 11.2.0.4
db_block_size - 8k
CPU
User CPU % 54.6 47.5 48.8 28.7 7.2
System CPU % 45.3 52.2 50.8 70.6 3.4
IOWait CPU % 0 0 0 0 0
Idle CPU % 0.1 0.2 0.4 0.8 89.4
Load Average 231.2 235.5 505.1 256.2 7
Disk
Read Mb/sec 770.8 2,503.8 2,437.1 2,480.2 1,192.4
Read Req/sec (IOPS) 98,577.4 320,399.5 311,788.3 327,235.6 152,482.7
Read Latency msec (db file sequential read) 20 6 13 3 0.2
Oracle Metrics
DB CPU 3.8 15.6 15.6 31.1 6.6
Executes (SQL)/sec 1,673.2 5,344.1 5,219.5 5,305.9 4,060.2
Buffer Hit % 7.77 7.63 7.51 7.44 8.3
Max Active Sessions 295 299 551 293 50
Top Event db file parallel read
Total Waits - 1,933,686
Avg Wait Time (ms) - 46
% DB Time - 56.8
db file parallel read
Total Waits - 6,267,171
Avg Wait Time (ms) - 16
% DB Time - 66.6
db file parallel read
Total Waits - 3,057,123
Avg Wait Time (ms) - 32
% DB Time - 64.5
db file parallel read
Total Waits - 3,108,863
Avg Wait Time (ms) - 7
% DB Time - 75.7
DB CPU
% DB Time - 82.7

  1. Here it can be seen that the CPU was a bottleneck in the column 1 configuration, hence Read IOPS was impacted.
  2. Increasing the CPU in column 2 configuration shows that CPU was completely utilized, thereby increasing Read IOPS and number of SQLs executed.
  3. Increasing the number of sessions/connections in the column 3 configuration, does not increase the Read IOPS or Read Mb/sec, which indicates that the maximum IOPS and throughput limit of the I/O subsystem were reached.
  4. Increasing the CPU in column 4 configuration does not increase the Read IOPS or Read Mb/sec, reaffirming the maximum IOPS and throughput limit of the I/O subsystem has been reached.
  5. Tests in columns 1-4 was heavy stress. Test in column 5 was done under lower load on the system to check nominal latency. 0.2ms is an impressive result.

V. Conclusion:

Oracle OCI configuration performed substantially better than AWS. Why? Main reason is difference in architecture: local NVMe SSD Oracle provides for DBaaS are superior to EBS storage Amazon provides for RDS.

So technology wise systems been compared are quite different. But the systems have similar price tag. AWS is a bit more expensive mostly due to high cost of provisioned IOPS storage.
Price been compared is before Oracle RDBMS licencing.