Oracle Database 12c
The Oracle Database application suite is still one of the most widely deployed commercial database apps today. It is used to support a very wide variety of usecases such as ERP systems, CRM systems, E-Commerce solutions etc.. Given the nature of the commercial licensing, performance and tuning are critical to ensure that a consumer gets the optimal performance per $ out of their investment.
When the Oracle database is the choice for your application, it is needed to assess Oracle random physical I/O capability on a given environment/platform in preparation for OLTP/ERP style workloads. There is no shortage of transactional benchmarks such as Dominic Giles’ Swingbench and cost-options such as Benchmark Factory. However, the problem with these kits is these kits are transactional so they are more suitable to prove Oracle scales those applications, rather than test database I/O characteristics. These transactional test kits involve physical I/O but the ratio of CPU utilisation to handle physical I/O is generally low and most CPU resources are being used to execute Oracle intrinsic transaction code paths.
Oracle SLOB – the Silly Little Oracle Benchmark is a complete toolkit for generating I/O through an Oracle database. SLOB is the tool to analyse the I/O capabilities of your Oracle database. SLOB testing has been used by many industrial vendors to show the SLOB results in their use cases, which are available here as a collection of articles.
Infrastructure Environment Tested
The Oracle DB application has been tested on the following:
|Host OS||CentOS Linux release 7.5.1804 (Core)|
|Application version||Oracle Database 12c Enterprise Edition Release 184.108.40.206.0 - 64bit Production|
|Environments tested||AWS R5 instances (Intel Xeon Platinum CPU @ 3.1 GHz ), Sunlight on premise (Intel Xeon Gold 6130 CPU @ 2.10 GHz)|
Configuration and Setup Description
The configuration consists of the following aspects:
- the VM instance configuration
- the host operating system (OS) configuration
- the Oracle database installation
- SLOB setup and configuration for testing
The VM instance hosting the Oracle database is configured as the following table.
|Instance Setup||Instance Type||Instance Storage||Number of Cores|
|Sunlight||Sunlight||500 GB||4, 8, 16|
|AWS General Purpose SSD||r5.xlarge, r5.2xlarge, r5.4xlarge||500 GiB General Purpose SSD (gp2) - 1500~3000 IOPS||4, 8, 16|
|AWS Provisioned IOPS SSD||r5.xlarge, r5.2xlarge, r5.4xlarge||500 GiB Provisioned IOPS SSD (io1) - up to 25000 IOPS||4, 8, 16|
The same host OS configuration is used across the different environments tests and the detail configuration is provided below. The SLOB configuration is provided in the different individual tests description.
Oracle Database Host OS Setup
Replace the kernel as stock kernel does not have multiqueue blkfront.
sudo rpm --import https://www.elrepo.org/RPM-GPG-KEY-elrepo.org sudo yum -y install https://www.elrepo.org/elrepo-release-7.0-3.el7.elrepo.noarch.rpm sudo yum --enablerepo=elrepo-kernel install kernel-ml sudo grub2-set-default 0 sudo reboot
The host operating system has been configured in the /etc/sysctl.conf file as the following setups.
fs.aio-max-nr = 1048576 fs.file-max = 6815744 kernel.shmall = 6180853 kernel.shmmax = 31645970432 kernel.shmmni = 4096 kernel.sem = 250 32000 100 128 net.ipv4.ip_local_port_range = 9000 65500 net.core.rmem_default = 262144 net.core.rmem_max = 4194304 net.core.wmem_default = 262144 net.core.wmem_max = 1048586 net.ipv6.conf.all.disable_ipv6 = 1 net.ipv6.conf.default.disable_ipv6 = 1 net.ipv6.conf.lo.disable_ipv6 = 1 vm.overcommit_ratio = 100
Oracle Database Installation
Install the prerequisite packages
sudo yum install -y binutils.x86_64 compat-libcap1.x86_64 gcc.x86_64 gcc-c++.x86_64 glibc.i686 glibc.x86_64 glibc-devel.i686 glibc-devel.x86_64 ksh compat-libstdc-33 libaio.i686 libaio.x86_64 libaio-devel.i686 libaio-devel.x86_64 libgcc.i686 libgcc.x86_64 libstdc.i686 libstdc.x86_64 libstdc-devel.i686 libstdc+-devel.x86_64 libXi.i686 libXi.x86_64 libXtst.i686 libXtst.x86_64 make.x86_64 sysstat.x86_64 zip unzip smartmontools bzip2 java-1.8.0 sudo yum groupinstall -y "X Window System"
Install the Oracle Database
Download the Oracle database package and install it using the installer wizard. More information about Oracle Database installation is available at Installation Guide provided by Oracle.
SLOB Tests and Results
SLOB setup and configuration
Creation of testing database