Date Published: November 14, 2017

Benchmarking Oracle’s DBaaS against RDS – A Performance Comparison.

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:

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/sec100-1501000 (LGWR)
Write Req/sec (IOPS)1500-20001500 (LGWR)
Write Latency msec8-10<1
Total Runtime mins7417

OCI Performance Charts

Latency, Throughput and IOPS

Active sessions

AWS Performance Charts

IOPS (read)

CPU

Latency (read)

Throughput (read)

IOPS (write)

Latency (write)

AWS write latency

Throughput (write)

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.147.5
System CPU %6.452.2
IOWait CPU %26.60
Idle CPU %420.2
Load Average222.5235.5
Disk
Read Mb/sec237.22503.8
Read Req/sec (IOPS)30,362.8320,399.5
Read Latency msec259
Oracle Metrics
DB CPU9.515.6
Buffer Hit %8.127.63
Top Eventdb 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

Load average, Active sessions

AWS Performance Charts

IOPS (read)

Latency (read)

Throughput (read)

  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.238.2
System CPU %36.849.5
IOWait CPU %00
Idle CPU %2.912.3
Load Average89.4165.6
Disk
Read Mb/sec418.42,180.7
Write Mb/sec41.6235.8
Read Req/sec (IOPS)53,353.5278,741.4
Write Req/sec (IOPS)5,322.330,179.9
Read Latency msec (db file sequential read)21
Write Latency msec (direct path write)31
Oracle Metrics
DB CPU3.224.6
Executes (SQL)/sec921.64,660.7
Buffer Hit %8.768.44
Max Active Sessions297294
Top Eventfree 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.647.548.828.77.2
System CPU %45.352.250.870.63.4
IOWait CPU %00000
Idle CPU %0.10.20.40.889.4
Load Average231.2235.5505.1256.27
Disk
Read Mb/sec770.82,503.82,437.12,480.21,192.4
Read Req/sec (IOPS)98,577.4320,399.5311,788.3327,235.6152,482.7
Read Latency msec (db file sequential read)2061330.2
Oracle Metrics
DB CPU3.815.615.631.16.6
Executes (SQL)/sec1,673.25,344.15,219.55,305.94,060.2
Buffer Hit %7.777.637.517.448.3
Max Active Sessions29529955129350
Top Eventdb 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.

Comments

Write a Reply or Comment

Your email address will not be published. Required fields are marked *