Kevin Closson's Blog: Platforms, Databases and Storage

Before I offer the Step-By-Step guide, I feel compelled to answer the question that some exceedingly small percentage of readers must surely have in mind–why test with SLOB? If you are new to SLOB (obtainable here) and wonder why anyone would test platform suitability for Oracle with SLOB, please consider the following picture and read this blog post.

SLOB Is How You Test Platforms for Oracle Database.

Simply put, SLOB is the right tool for testing platform suitability for Oracle Database. That means, for example, testing Oracle Database I/O from an Amazon RDS for Oracle instance.

Introduction

This is a simple 9-step, step-by-step guide for installing, loading and testing a basic SLOB deployment on a local server with connectivity to an instance of Amazon RDS for Oracle in Amazon Web Services. To show how simple SLOB deployment and usage is, even in a DBaaS scenario, I chose to base this guide on a t2.micro instance. The t2.micro instance type is eligible for free-tier usage.

Step One

The first step in the process is to create your t2.micro Amazon Web Services EC2 instance. Figure 1 and Figure 2 show the settings I used for this example.

Figure 1: Create a Simple t2.micro EC2 Instance

Figure 2: Configure a Simple EC2 Instance

Step Two

Obtain the SLOB distribution file from the SLOB Resources Page. Please note the usage of the md5sum(1) (see Figure 3) command to verify the contents are correct before performing the tar archive extraction. You’ll notice the SLOB Resources Page cites the check sums as a way to ensure there is no corruption during downloading.

After the tar archive is extracted, simply change directories into the wait_kit directory and execute make(1) as seen in Figure 3.

Figure 3: Install SLOB. Create Trigger Mechanism.

Step Three

In order to connect to your Amazon RDS for Oracle instance, you’ll have to configure SQL*Net on your host. In this example I have installed Oracle Database 11g Express Edition and am using the client tools from that ORACLE_HOME.

Figure 4 shows how to construct a SQL*Net service that will offer connectivity to your Amazon RDS for Oracle instance. The HOST assignment is the Amazon RDS for Oracle endpoint which can be seen in the instance details portion of the RDS page in the AWS Console. After configuring SQL*Net it is good to test the connectivity with the tnsping command–also seen in Figure 4.

Figure 4: Configure Local Host SQL*Net tnsnames.ora File.

Step Four

As per the SLOB documentation you must create a tablespace in which to store SLOB objects. Figure 5 shows how the necessary Oracle Managed Files parameter is already set in the Amazon RDS for Oracle instance. This is because Amazon RDS for Oracle is implemented with Oracle Managed Files.

Since the Oracle Managed Files parameter is set the only thing you need to do is execute the ~SLOB/misc/ts.sql SQL script. This script will create a bigfile tablespace and set it up with autoextend in preparation for data loading.

Figure 5: Create a Tablespace to House SLOB Table and Index Segments.

Step Five

This step is not absolutely necessary but is helpful. Figure 6 shows the size of the database buffers in the SGA (in bytes). The idea behind generating SLOB IOPS is to have an active data set larger than the SGA. This is all covered in detail in the SLOB documentation.

Once you know the size of your SGA database buffer pool you will be able to calculate the minimum number of SLOB schemas that need to be loaded. Please note, SLOB supports two schema models as described in the SLOB documentation. The model I’ve chosen for this example is the Multiple Schema Model. By default, the slob.conf file has a very small SCALE setting (80MB). As such, each SLOB schema is loaded with a table that is 80MB in size. There is a small about of index overhead as well.

Since the default slob.conf file is configured to load only 80MB per schema, you need to calculate how many schemas are needed to saturate the SGA buffer pool. The necessary math for a default slob.conf (SCALE=80MB) with an SGA buffer pool of roughly 160MB is shown in Figure 6. The math shows that the SGA will be saturated with only 2 SLOB schemas loaded. Any number of SLOB schemas beyond this will cause significant physical random I/O. For this example I loaded 8 schemas as shown later in this blog post.

Figure 6: Simple Math to Determine Minimum SLOB SCALE for IOPS Testing.

Step Six

Figure 7 shows in yellow highlight all of the necessary edits to the default slob.conf file. As the picture shows, I chose to generate AWR reports by setting the DATABASE_STATISTICS_TYPE parameter as per the SLOB documentation.

In order to direct the SLOB test program to connect to your Amazon RDS for Oracle instance you’ll need to set the other four parameters highlighted in yellow in Figure 7. This is also covered in the SLOB documentation. As seen in Figure 4, I configured a SQL*Net service called “slob”. To that end, Figure 7 shows the necessary parameter assignments.

The bottom two parameters are Amazon RDS for Oracle connectivity settings. The DBA_PRIV_USER parameter in slob.conf maps to the master user of your Amazon RDS for Oracle instance. The SYSDBA_PASSWD parameter needs to be set to your Amazon RDS for Oracle master user password.

Figure 7: Edit the slob.conf File.

Step Seven

At this point in the procedure it is time to load the SLOB tables. Figure 8 shows example output of the SLOB setup.sh data loader creating and loading 8 SLOB schemas.

Figure 8: Execute the setup.sh Script to Load the SLOB Objects.

After the setup.sh script exits, it is good practice to view the output file as per setup.sh command output. Figure 9 shows that SLOB expected to load 10,240 blocks of table data in each SLOB schema and that this was, indeed, the amount loaded (highlighted in yellow).

Figure 9: Examine Data Loading Log File for Possible Errors.

Step Eight

Once the data is loaded, it is time to run a SLOB physical I/O test. Figure 10 and Figure 11 show the command output from the runit.sh command.

Figure 10: Execute the runit.sh Script to Commence a Test.

Figure 11: Final runit.sh Output. A Successful Test.

Step Nine

During the SLOB physical I/O testing I monitored the throughput for the instance in the RDS page of the AWS console. Figure 12 shows that SLOB was driving the instance to perform a bit over 3,000 physical reads per second. However, it is best to view Oracle performance statistics via either AWR or STATSPACK. As an aside, the default database statistics type in SLOB is STATSPACK.

Figure 12: Screen Shot of AWS Console. RDS Performance Metrics.

Finally, Figure 13 shows the Load Profile section of the AWR report created during the SLOB test executed in Figure 10. The internal Oracle statistics agree with the RDS monitoring (Figure 12) because Amazon RDS for Oracle is implemented with the Oracle Database initialization parameter filesystemio_options set to “setall”. As such, Oracle Database uses direct and asynchronous I/O. Direct I/O on a file system ensures the precise amount of data Oracle Database is requesting is returned in each I/O system call.

Figure 13: Examine the AWR file Created During the Test.

Summary

Testing I/O on RDS for Oracle cannot be any simpler, nor more accurate than with SLOB. I hope this post convinced you of the former and testing will reveal the latter.