HammerDB CLI for Oracle on Azure

Disclaimer: I’m not a big fan of benchmark data.  I find it doesn’t provide us as much value in the real world as we’d like to think it does.  As Cary Milsap says, “You can’t hardware your way out of a software problem” and I find that many folks think that if they just get the fastest hardware, their software problems will go away and this just isn’t true.  Sooner or later, it’s going to catch up with you-  and it rarely tells you what your real database workload needs to run most efficiently or what might be running in your database that could easily be optimized to save the business time and money.

The second issue is that when comparing different workloads or even worse, different platforms or applications, using the same configuration can be detrimental to the benchmarks collected, which is what we’ll discover in this post.

HammerDB and Oracle on Azure

I recently was asked to duplicate a customer’s environment in my own subscription and perform HammerDB tests to meet or beat their onprem performance.  This is the first time I’ve ever used this tool-  I’ve used SLOB, but I’m more often brought in on database optimization and knowing about infrastructure than just understanding what’s required to get the best numbers from a benchmark tool.  HammerDB is quite clear on the fact that although their benchmark values are based on TPC-C called TPROC-C, these values can’t be compared to any official TPC-C benchmarks.  The two values-  TPM, (Transactions Per Minute) and NOPM, (an internal metric for New Orders Per Minute) are just their primary values to compare one system to the next, again, not published benchmark values.  It’s also clearly stated that TPM can’t be compared from one database platform to the next, NOPM can, but only if you configure the workload test optimally for the platform.

As I wasn’t granted access to the customer’s Oracle AWR data, I had to make a lot of assumptions on the database side and this did make me quite nervous when the goal was to take what they have for their onprem environment and duplicate it.  What type of workload should I be designing the database for?  What parameters are set inside the database- was there any odd parameters that I should have known about?  What size and how many redo logs?  Are Huge Pages configured? The test examples from HammerDB’s workload showed both OLTP and OLAP, but rarely is a customer’s workload that straight forward and how was I to know what the customer possessed when they put their actual workload on the VM for real?

We all know that situation when requests for more information is repeatedly dismissed or dismissed as unnecessary, even when we set the expectation that assumptions will have to be made on our side.  I ended up without AWR data and having to document the risk of going with the limited information that was provided, which was interesting as I came up to speed on HammerDB and realized the first and last step in the test is to take an AWR snapshot when run in Oracle.

GUI or CLI

Although the customer was using the GUI for HammerDB, the ports required open for this wouldn’t be an option for me in my internal Azure subscription and I would only be able to use the CLI (unless I wanted to be on security’s bad list everyday.)  I discovered how poorly documented HammerDB CLI’s command line information is and found it one of the most sobering aspects of this exercise.  I discovered a number of bugs that both resulted in lost time and frustratingly no errors to lead me to any ideas on how to troubleshoot, (so the reason for documenting this in a blog post.)

For those that have never used HammerDB, historically it was used heavily for Oracle performance testing and I did locate some ex-coworkers names in the code base that tells me, yes, it was used for Oracle at one time.  The focus of the current documentation is more on the SQL Server and MySQL side, so if you’re working with it on Oracle and need to know more about a setting or worse, the CLI, be prepared to scour the web for this-  it’s not easy to locate.

The actual installation to the Azure VM is simple-

1. Download the software to the VM using a curl command or download locally and SCP over to your VM.
2. untar the file in a working directory, (I recommend the Oracle user home or similar.)
3. Install the lib font file needed for the Oracle client installation
4. Configure your .bash_profile with the environment variable if they aren’t already there and ensure all required are present.
5. Write a couple scripts to make it easy to automate the tests, including tracking of changes, (examples below)
6. Create the tpcctab bigfile tablespace if you want to use something other than USERS and the tmp log directories
7. Build the schema, clear the buffer pool, run the test, drop the tpcc user, rinse and repeat, (all included in the scripts)

Things to remember when using HammerDB on Azure with Oracle and the CLI:

1.  Ensure you are using the Oracle OS user to setup, own and run the software on the VM.
2.  All scripts and commands are in TCL, which is pretty easy to work with.
3.  Install the lib files as the root owner.
4.  If you’re using ultra disk and doing work from the CLI- do ALL THE WORK from the CLI-  don’t go back and forth between the Portal and the CLI.  There’s definitely some buggy issues attaching the storage from the CLI if you’ve created the Ultra disk from the Azure Portal.
5. Create redo logs big enough that you aren’t switching during the test run and place redo on as fast of disk, (i.e. ultra premium disk) as required to support the test-  this isn’t your production workload, but a benchmark test that could be hindered by logfile latency issues.
6. Configure a TNS connection and verify that you can connect to the database before attempting any tests.
7. The TPCC user will own the objects and run the tests.
8. Use read-only host caching on the premium disk you use for the data or ultra disk.
9.  If you’re seeing waits on the OS disk, consider upsizing it to a P15 from a P4, but you should absolutely be using a premium disk for the OS Disk, same for your swapfile.  If you have to resize the OS Disk, there’s a good chance you’ll have to rebuild the whole machine, so plan ahead and get the right VM series that supports Premium disk for the OS Disk and get it right the first time, (some of my favorite are the Eds v4 VM series).

Recommendations for Oracle with HammerDB, (not just with Azure)

1.  Verify that the vCPU isn’t in power saver mode using Julian Dyke’s recommended script.

SET SERVEROUTPUT ON
SET TIMING ON

DECLARE
n NUMBER := 0;
BEGIN
FOR f IN 1..10000000
LOOP
n := MOD (n,999999) + SQRT (f);
END LOOP;
DBMS_OUTPUT.PUT_LINE ('Res = '||TO_CHAR (n,'999999.99'));
END;
/

1.  As one user can pretty much consume a single CPU, keep to 1:1 for CPU, increased 35% if the CPU has hyperthreading turned on.
2. As stated earlier, size the redo logs to run without switching for your test.  The redo logs CAN’T be too big for this test and at no time should ‘just match’ your real workload, as this isn’t a real workload, it’s pushing the hardware.  If you’re doing a lot of log switching and just seeing waits on the LGWR, what are you really testing?  It’s a good idea to monitor the log switches in both the AWR and at the database level parameters and ensure all delays are eliminated as much as possible:

alter system set log_checkpoints_to_alert = true
alter system set log_checkpoint_timeout=0;
alter system set log_checkpoint_interval=0;
alter system set fast_start_mttr_target=0;

You may run into a “feature” in Oracle 12c that refuses to let you drop the TPCC or TPCH schema, (admin) user.  This can impact the ability for the HammerDB code to build the schema or run the test successfully, even after a vucomplete/vudestroy has executed.  To work around this, I simple created a login trigger to address it:

CREATE OR REPLACE TRIGGER TRG_ORASCRPT
AFTER LOGON
ON DATABASE
BEGIN
alter session set "_ORACLE_SCRIPT"=true;
EXCEPTION
WHEN OTHERS THen
NULL;
END;
/

My Setup

I am one of the few that are dedicated to the Azure cloud shell, so I use this as my “jumpbox” and just set up my ssh from the terminal to the VM.  Once logged in, I am able to simply follow the instructions for setting up HammerDB on the Linn ux VM.  Always remember to get the prerequisite lib file installed, too.

Update the .bash_profile or your Oracle profile with the LD_LIBRARY_PATH info, not just the standard ORACLE_HOME and PATH.  Without this, HammerDB will fail.

The HammerDB CLI has some commands that will serve you well:

Command                                                    Usage

print dict	Displays the values for the current HammerDB run settings
diset <user> <parameter>	Configures the settings for a user in HammerDB
dbset	Used to setup the connection information for the database
dgset	Is for the data generated options.
waittocomplete	Without this, you could have some threads finish much sooner than others and HammerDB, the shell terminal, etc. could think you’re finished before you really are.
buildschema	loads data vs. querying data, this has to be the first step in your workload tests.
loadscript	loads the script that you want to run instead of passing information at the command line.
vurun	runs the script loaded, along with any one-0ff settings that have been passed.  Always a good idea to run “print dict” beforehand to verify the settings are present to meet the requirements.
vucomplete	Signals to HammerDB that the test run is complete.
vudestroy	Takes down the test and the users that were running if they’ve all be granted a status of complete by “vucomplete”

 

You can get around this by passing in some of the parameters from the command line before you execute your script, like the following example:

diset tpcc count_ware 10
diset tpcc tpcc_def_tab tpcctab
diset tpcc allwarehouse true
source /home/oracle/HammerDB-4.0/scripts/ivtst.tcl

 

A script, (bldschema.tcl) to run against an Oracle database with the following configuration would look like this to build a warehouse schema:

#!/bin/tclsh
puts "SETTING CONFIGURATION"
global complete
proc wait_to_complete {} {
global complete
set complete [vucomplete]
if {!$complete} {after 5000 wait_to_complete} else { exit }
}
dbset db ora
diset connection system_password "ChangeMe1"
diset connection instance oradb01
diset tpcc count_ware 50
diset tpcc num_vu 8
diset tpcc tpcc_def_tab tpcctab
print dict
buildschema
wait_to_complete

This script would ensure:

1. Does not exit until all users have completed their warehouse builds.
2. Connects to the database oradb01 as the System user using the password specified.
3. Will build 10 warehouses.
4. Using 8 users, (personally, I’d use a 1:1 per CPU count.  If you have hyperthreading on, you can expect a 35% gain per vCPU, so for an 8 vCPU VM, 10 would be the max users I would use, with a minimum of 50 warehouses to keep a 1:1 on the warehouse query, too.)
5. Creates a user named tpcc using the password tpcc and grants the necessary permissions to perform the test.
6. Will load all data into the tpcctab tablespace.
7. Builds the 10 warehouse schemas in the database.

Once you build your data in your warehouse(s), you want to then run tests against it to see the performance on queries, not just data loads.  This can be done with another script, (runoltp.tcl):

!/usr/bin/tclsh
puts "SETTING CONFIGURATION"
dbset db ora
diset connection system_password "Dr4g0nfly1"
diset connection instance oradb01
diset tpcc ora_driver timed
diset tpcc rampup 0
diset tpcc duration 1
vuset logtotemp 1
loadscript
puts "SEQUENCE STARTED"
foreach z { 12 } {
puts "$z VU TEST"
vuset vu $z
vucreate
vurun
runtimer 300
vudestroy
after 5000
}
puts "TEST SEQUENCE COMPLETE"

 

Both these scripts can be executed with the following “wrapper script” that will ensure you don’t time out in the Azure Cloud Shell, (think of it as a nohup for Cloud Shell using the wait for complete option, which works well here.)

 

Results- OK, Now What?

Once the tests are completed, you will see the following types of results from both the output and the log file that was created in the directory you set for the configuration, (hammerdb.log).  Can this data provide much in the way of understanding the performance of the infrastructure in terms of Transactions Per Minute, (TPM) and New Orders Per Minute, (NOPM)?  I really felt like I was just tuning the redo logs for the workload.  It just seemed quite limited, in my view.  When reviewing the information on comparing results, I found only a little value from what was documented.  For every database platform, there so much variation in architecture recommendations the tool just doesn’t cover and I’m not sure it really should be required to.

For this test, an Oracle 19c database, huge pages set to 31599, 8 redo logs at 8G each, interleaved between two ultra disks and the data, with it’s low impact read-only host caching on Premium P40 disk, I was able to achieve the goal:

 System achieved 225322 NOPM from 353261 Oracle TPM

The most important part of this test is to understand what to do with it to optimize it.  The infrastructure people will most likely want to throw a larger VM at it or faster disk, (dialing up the ultra disk was the common thing in this test…) if they want it to have higher numbers, but with the HammerDB test for Oracle, there is a beginning and ending snapshot that’s taken in AWR.  PLEASE USE THIS.  HammerDB is really heavy on the redo and for this “fake” workload, it resulted in me interleaving the redo logs on two ultra disks to match the onprem environment HammerDB tests.  Even with these numbers, to increase the performance, I would be looking at how to squeeze more out of the LGWR and log switches, not the disk.  If you switch at all, upsize the redo logs so they don’t switch and consider not using mirrored redo logs during the testing period.

Do these number really mean anything?

So I can get incredible performance for redo log on high redo log switch and commit workloads in Azure if anyone is interested, but I was confounded knowing this isn’t how most workloads are and their real workload may be very, very different once they migrate over.  Maybe I just work on too many OLAP workloads coming from Exadata, but this hasn’t been my experience and I would love to get some feedback from others where they found HammerDB helpful outside of kicking the tires on your hardware.

Till next time.