Back To Basics

Database Performance Benchmark using HammerDB

I have been using HammerDB for database performance bench-marking . This is very useful tool if you’re getting into any proof of concepts (POC) or testing of new database infrastructure stack before production roll-out.

In this blog post we will discuss around using HammerDB to generate OLTP (TPC-C workload) on Microsoft SQL database. In my example I had Microsoft SQL and HammerDB server created on AWS EC2 instances. But from HammerDB perspective it doesn’t matter as far as it can communicate to database instance.

We will follow 5 simple steps to run synthetic workload on MS SQL database. So let’s get started.

Step 1 – Installing the HammerDB Application

Before you get started make sure that –

  • HammerDB and MS SQL server are on same network/VLAN
  • MS SQL server is installed and running
  • Login to MS SQL console and create new SQL database. Make sure that new database is created on the target storage disks. If you create database in C drive, then you might not get expected performance due to disk bottleneck.
Microsoft SQL database console
  • Download HammerDB installer (Click image below)
HammerDB download link
  • Once HammerDB installer is downloaded, go ahead and install the same. Below are the links for installation steps.

At this point you should have

  • MS SQL server ready with test database created on desired target storage disks
  • HammerDB installed and running

Step 2 – Configuration of Schema Build

We will need to created OLTP workload schema as per TPC-C specifications. Follow below steps for building OLTP schema on target database

  • Open the HammerDB console
HammerDB – Console
  • Under Benchmark navigation page, double-click on SQL Server. In Benchmark Options pop-up window select SQL Server and TPC-C options. Then click Ok. Click Ok once again to confirm the selection.
HammerDB – Selecting SQL database and TPC-C workload
  • Under Benchmark navigation now you can see SQL Server selected with TPC-C options.
HammerDB – SQL server and TPC-C benchmark
  • Expand TPC-C under SQL Server and then expand Schema Build
HammerDB – TPC-C Schema Build
  • Double-click on Options under Schema Build. Pop-up window of TPC-C Build Options will open. In this window enter below details, and then click Ok
    • SQL Server – IP address or hostname of SQL server. Keep (local) if HammerDB is installed on the SQL server.
    • Authentication – Use Windows Authentication if you want logged-in user credentials to be used for SQL server, else select SQL authentication and enter credentials.
    • SQL Server Database – Name of the database which we had created in Step 1
    • Number of Warehouses – Enter the value to number of warehouses you have chosen for your testing. Preferably select number of warehouses equal to number of cores on the server
    • Virtual Users to Build Schema – Set this value equal to number of warehouses.
HammerDB – TPC-C Build Options
  • In the Benchmark pane double-click on Build
HammerDB – TPC-C Schema Build
  • Click Yes to confirm creating schema in the target SQL database.
  • HammerDB will now start creating virtual user threads and create schema in the target database. This process takes some time. You can monitor the status in top-right corner of HammerDB console. (TPC-C creation in top-right)
HammerDB – TPC-C Schema Creation in process
  • Once completed you can see that the status is changed to Complete
HammerDB – TPC-C Schema Creation Completed

Step 3 – Configure Driver Script

Follow below steps to configure HammerDB driver script

  • Expand the Driver Script from Benchmark navigation pane and double-click on Options
HammerDB – Driver Script
  • In the TPC-C Driver Options pop-up window enter below details, and then click Ok
    • SQL Server – Keep it same as Step 2
    • Authentication – Keep it same as Step 2
    • Total Transactions Per User – Keep default value. This value will set the number of transactions each virtual user will process before logging off
    • TPC-C Driver Script – Keep this option as Timed Driver Script. This will run the workload for finite time as specified in Minutes for Test Duration
    • Minutes of Rampup Time – The rampup time defines the time in minutes for the monitoring virtual user to wait for the virtual users running the workload to connect to the database. 2 minutes in my case. You can increase this number if you’ve higher number of virtual users.
    • Minutes for Test Duration – The Minutes for Test Duration is shown as duration in the Driver Script. This does not include rampup time.
    • Use All Warehouses – Keep this option checked.
HammerDB – Driver Script Options
  • In the Benchmark navigation double-click on Load (under Driver Script). You need to Load the Driver Script every time you make changes to Driver Script Options.
HammerDB – Driver Script Load

Step 4 – Create Virtual User

Once HammerDB driver script is loaded, follow below steps to create virtual user.

  • Expand Virtual User from Benchmark pane
HammerDB – Virtual User
  • Double-click on Options. In the pop-up window enter below details, and then click Ok.
    • Virtual Users – Keep number of users same as Step 2
    • Keep Other inputs as default
HammerDB – Virtual User Options
  • In the Benchmark navigation pane double-click on Create under Virtual User. This will create virtual users and keep them idle.
HammerDB – Virtual User Create

At this point we are ready to start OLTP workload on target database.

Step 5 – Run HammerDB OLTP (TPC-C) Workload

Follow below steps to run the OLTP workload and monitor the TPMs.

  • In Benchmark pane double-click on Run (Under Virtual User).
HammerDB – Run OLTP (TPC-C) workload
  • Now Virtual Users will start logging into the target database and begin running their workload. You can monitor the status under Virtual User 1-MONITOR
HammerDB – Starting OLTP (TPC-C) Workload – In Progress
  • While workload is running you can monitor the real-time TPM (Transactions Per Minute) by clicking on Transaction Counter
HammerDB – Transaction Counter
  • Once workload is completed you can see the status in top-right corner as well as under Virtual User 1-MONITOR
HammerDB – Starting OLTP (TPC-C) Workload – Completed
  • Once the workload is completed note down TEST RESULT under Virtual User 1-MONITOR

You can run multiple tests and take average across them for realistic performance numbers.

Apart from MS SQL (used in this blog post) HammerDB supports running OLTP (TPC-C) and OLAP (TPC-H) on Oracle, IBM DB2, MySQL, PostgreSQL, MariaDB and Redis.

I hope this helps everyone.

1 reply »

Leave a Reply

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

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

Facebook photo

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

Connecting to %s