Rate this Entry

Benchmarking (Part 2): Oracle DB Performance with Hammerora

Posted March 6th, 2008 at 20:51 by chrisplough
Updated April 22nd, 2008 at 07:44 by chrisplough (Updating benchmark results for new hardware)

Hello!

I wanted to give a quick tutorial on how to benchmark DB performance with Hammerora. This is a great benchmark for determining the relative performance of Oracle databases -- allowing you to compare various platforms and also DB configuration changes. Since this is a highly multi-threaded test, it simulates the activity coming from the OTM DB connection pool very well. Best of all, it fits into the MavenWire Benchmark Toolkit, because it works well. As an added bonus for the community - it's free!

First, some links you'll need:

Hammerora
http://hammerora.sourceforge.net/

Oracle Instant Client (10.2.0.3)
http://www.oracle.com/technology/tec...antclient.html

Oracle DB (10.2.0.3 Enterprise Edition -- for OTM v5.5)
http://technet.oracle.com/

To get started, download and install Hammerora. Follow their installation instructions and you'll be fine (http://hammerora.sourceforge.net/installation.htm). Ensure that you're getting the right binaries (32-bit or 64-bit) for your version of Linux. Note: I wasn't able to get the console installation working, so you may need to install via the GUI (XWindows).

Below, I've provided a pre-modified config.xml file for Hammerora - as this sets up many of the default benchmark behaviors that we use, including the number of warehouses (50), number of virtual users (50), wait times (0), number of iterations (2000), etc. All of these are important for creating reproducible results and being able to compare against our reference performance numbers. Copy this file into your Hammerora installation directory, overwriting the existing file.
(unzip after downloading)

Download: config.xml.zip

In addition, we've also provided a modified benchmark script (mavenwire_tpcc.trc), based on the TPC-C script provided with Hammerora. This updated script includes logging of the individual user's runtime, for performance comparison. Copy this file into your Hammerora installation directory.
(unzip after downloading)

Download: mavenwire_tpcc.trc.zip

Finally, create a tnsnames.ora file under $HAMMERORA_HOME/instantclient_10_2/ for your DB server. In order to match up with the included configuration files, use a connect string of "HAMMERORA". Alternately, you can edit the DB connect string parameters (there are several of them), within the Hammerora config.xml and mavenwire_tpcc.trc scripts.

In order to run the test, you'll need a database. Since our interest is in testing Oracle DB performance in relation to OTM, I recommend creating an Oracle 10.2.0.3 database with the same parameters as your PROD OTM database. For tablespaces, however, you'll only need the following:

Tablespace: tpcctab
Size: 5GB

Code:

create tablespace tpcctab
logging
datafile '/u01/oradata/hammerora/TPCCTAB01.dbf'
size 5g
autoextend on
next 256m maxsize 10240m
extent management local;

Temporary Tablespace: TEMP
Size: 2GB

Code:

create temporary tablespace temp
tempfile '/u01/oradata/hammerora/TEMP01.dbf'
size 2g
autoextend on
next 256m maxsize 4096m
extent management local;

Then, just create a user, via the following commands:

Code:

drop user tpcc cascade;
create user tpcc
identified by tpcc
default tablespace tpcctab
temporary tablespace temp;
grant dba to tpcc;

Now, ensure you've configured your environment variables (as noted on the Hammerora install page) startup Hammerora ("./hammerora.tcl &"). Assuming everything starts up without error, then you'll need to load the TPC-C schema into your database (this may take several hours). In order to do this, select the "Create TPC Schema" button. Walk away and have a nice cup of coffee (and maybe catch an episode of MythBusters) until it is complete. Once it is, check for any errors within the console.

At this point, shutdown Hammerora and take a backup of your database (cold backup, preferably). You'll need this, since you should delete and restore your database between each benchmark run. This is necessary to ensure that you get consistent, repeatable results.

Now, startup Hammerora again and prepare to run the benchmark. To run it, do the following:

Select "File > Open" and then open the mavenwire_tpcc.trc test file.
Click the "Create Virtual Users" button to instantiate your virtual users.
Begin recording system statistics (sar) and Oracle Sysstats.
Click the "Run Hammerora Loadtest" button to start the actual test.
Wait a while -- depending on the speed of your DB server, this could take several minutes to several hours.
Once all of the users are complete (check the "Complete" and "Status" columns), click the "Destroy Virtual Users" button to flush out the remaining benchmark timings.
Stop recording system statistics (sar) and Oracle Sysstats.
The output from the run will be logged to /tmp/hammerora.log.

Example Results (from /tmp/hammerora.log):
Note: You should clear out this file before each benchmark run.

Code:

# grep "User execution time" /tmp/hammerora.log
...
tid0x4aa10940:User execution time 1204836463 to 1204836684 = 221 seconds
tid0x5ea30940:User execution time 1204836463 to 1204836684 = 221 seconds
tid0x4e616940:User execution time 1204836463 to 1204836685 = 222 seconds
tid0x41e02940:User execution time 1204836463 to 1204836686 = 223 seconds
tid0x41401940:User execution time 1204836463 to 1204836686 = 223 seconds
tid0x42803940:User execution time 1204836463 to 1204836688 = 225 seconds
tid0x43204940:User execution time 1204836464 to 1204836689 = 225 seconds
tid0x43c05940:User execution time 1204836464 to 1204836689 = 225 seconds
tid0x45a08940:User execution time 1204836466 to 1204836715 = 249 seconds
tid0x44606940:User execution time 1204836466 to 1204836715 = 249 seconds
tid0x4780b940:User execution time 1204836467 to 1204836733 = 266 seconds
tid0x45007940:User execution time 1204836466 to 1204836733 = 267 seconds
...

To ensure I have solid, repeatable results - I average out all 50 user runtimes to achieve an overall average runtime for the benchmark run. I then run the full benchmark a total of 3 times (deleting and restoring the DB each time) and calculate the average of all 3 benchmark results to give a final performance number.

For reference, when running this test on one of our medium-sized Hosting DB servers, the following results were returned:

Run 1 (already averaged): 284.55
Run 2 (already averaged): 280.14
Run 3 (already averaged): 291.06
Final Averaged Result: 285.25
(lower is better)

(Update 20080422: Just got some new servers [i.e. expensive toys] into our Hosting environment. Here are the new benchmark results -- FAST!)

Run 1 (already averaged): 217.23
Run 2 (already averaged): 225.84
Run 3 (already averaged): 219.37
Final Averaged Result: 220.81
(lower is better)

If you'd like to compare different platforms (say Solaris or HPUX vs. Linux) or databases, just install Hammerora on a central testing box (preferably Linux), configure your tnsnames.ora file and then run the tests and compare the results.

I hope this helps.

--Chris

Other related Posts: