Benchmark Oracle database to the end with HammerDB

We have new faster machine and need a performance test, we can write our scripts/querys that do the measure job,
and measure performance with a 3rd party monitor tool (nagios,zabbix,etc..), but here comes HammerDB for save our
time and do the benchmark. Want to measure only storage performance with out oracle? better use IOmeter.

HammerDB last time know as HammerORA now support lot more databases (SQL Server, TimesTen, PostgreSQL, Greenplum, Postgres Plus Advanced Server, MySQL, Redis and Trafodion SQL on Hadoop), You can specify several different factors to customize your testing, yet the tool has an easy to use interface. It’s also very well documented, with some good instruction on how to set up your own load tests, lets take a closer look.

First of all, is free (like free beer), is an open source database load testing and benchmarking tool that allow you to execute TPC-C y TPC-H, database/schema generator, set test size, set a warm time and set virtual users for each test.

Download: http://hammerora.sourceforge.net/download.html
Quick guide:  http://hammerora.sourceforge.net/hammerdb_quickstart_oracle.pdf
Documentation: http://www.hammerdb.com/document.html
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/

This post is a fast step by step guide to get ready with this software, for our tests we install on windows and run test against DB on linux OS:

1) Install: http://hammerora.sourceforge.net/installation.htm

we installed hammerdb in a windows dedicated host, with rest stuff.

2) create a tnsnames.ora file under $HAMMERORA_HOME/instantclient_10_2/ for your DB server

3) create tablespace and hammerdb user

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

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

4) load the TPC-C schema into your database –> “Create TPC Schema” button (this may take lot time maybe hours)

5) Backup of your database (cold backup, preferably).

For our test we stop database(test enviroment,production clone) and take snapshot for restore(is virtual)

6) In hammerdb machine config:
config.xml
mavenwire_tpcc.trc

we used a pre pre-modified config.xml file and a modified benchmark script (mavenwire_tpcc.trc)
including number of virtual users , wait times, number of iterations , etc. All of these are important
for creating reproducible results and being able to compare against our reference performance numbers.
Copy this files into your Hammerora installation directory, overwriting the existing file

config.xml.zip
mavenwire_tpcc.trc.zip

7) Start test:
1-Select “File > Open” and then open the mavenwire_tpcc.trc test file.
2-Click the “Create Virtual Users” button to instantiate your virtual users.
3-Begin recording system statistics (sar) and Oracle Sysstats.
4-Click the “Run Hammerora Loadtest” button to start the actual test.
5-Wait a while — depending on the speed of your DB server, this could take several minutes to several hours.
6-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.
7-Stop recording system statistics (sar) and Oracle Sysstats.
8-The output from the run will be logged to /tmp/hammerora.log (Note: You should clear out this file before each benchmark run.):
8) Results:

grep “User execution time” /tmp/hammerora.log

tid0x4aa10940:User execution time 1204836463 to 1204836684 = 235 seconds
tid0x5ea30940:User execution time 1204836463 to 1204836684 = 235 seconds
tid0x4e616940:User execution time 1204836463 to 1204836685 = 235 seconds

Best is average out all user runtimes to achieve an overall average runtime. Good
is to run again the benchmark(deleting and restoring the DB each time) more times and take a total average.

Advertisements

Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s