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.

• Download HammerDB installer (Click image below)

• Once HammerDB installer is downloaded, go ahead and install the same. Below are the links for installation steps.
  • For Windows
  • For Linux

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

• 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.

• Under Benchmark navigation now you can see SQL Server selected with TPC-C options.

• Expand TPC-C under SQL Server and then expand 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.

• In the Benchmark pane double-click on 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)

• Once completed you can see that the status is changed to Complete

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

• 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.

• 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.

Step 4 – Create Virtual User

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

• Expand Virtual User from Benchmark pane

• 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

• In the Benchmark navigation pane double-click on Create under Virtual User. This will create virtual users and keep them idle.

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).

• 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

• While workload is running you can monitor the real-time TPM (Transactions Per Minute) by clicking on Transaction Counter

• Once workload is completed you can see the status in top-right corner as well as under Virtual User 1-MONITOR

• 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.