How to run a fixed throughput workload with HammerDB

This post gives an introduction to understanding how to run a fixed throughput OLTP workload with HammerDB.  In this example, we will use the CLI to run TPROC-C on a MariaDB database to illustrate the concepts.

Setting the bar with the default workload

Firstly, it is important to understand that the majority of users wanting to run an OLTP workload will use the default approach. This way is typically the fastest way to determine the maximum throughput of a database with minimal configuration, and over time has been proven to show the same performance ratios between systems as a well configured fixed throughput setup.  Therefore, the first thing we will do is to run a default workload, using the sample scripts provided as shown.

set tmpdir $::env(TMP)
puts "SETTING CONFIGURATION"
dbset db maria
dbset bm TPC-C

diset connection maria_host localhost
diset connection maria_port 3306
diset connection maria_socket /tmp/mariadb.sock

diset tpcc maria_user root
diset tpcc maria_pass maria
diset tpcc maria_dbase tpcc
diset tpcc maria_driver timed
diset tpcc maria_rampup 2
diset tpcc maria_duration 5
diset tpcc maria_allwarehouse false
diset tpcc maria_timeprofile false

loadscript
puts "TEST STARTED"
vuset vu 80
vucreate
tcstart
tcstatus
set jobid [ vurun ]
vudestroy
tcstop
puts "TEST COMPLETE"
set of [ open $tmpdir/maria_tprocc w ]
puts $of $jobid
close $of

In this test, our MariaDB 10.10.1 database built with 1000 warehouses returned just over 700,000 NOPM illustrating the upper limit of the system and database combination we are testing.

Vuser 1:80 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 701702 NOPM from 1631827 MariaDB TPM

Fixing throughput with keying and thinking time

The key concept to understanding fixed throughput is that of keying and thinking time. This setting is exactly how it sounds and simulates the delay a real user would have in inputting data i.e. keying time and reading the results returned i.e. thinking time. So as a first step, we will go ahead and set keying and thinking time for one virtual user and run the test for 2 minutes of rampup and 2 minutes of testing time.

diset tpcc maria_keyandthink true

When we run the test, we can see that our virtual user ran transactions at 1 NOPM (new order per minute) with most of the time now spent in keying and thinking time.

TEST STARTED
Vuser 1 created MONITOR - WAIT IDLE
Vuser 2 created - WAIT IDLE
2 Virtual Users Created with Monitor VU
Transaction Counter Started
Transaction Counter thread running with threadid:tid0x7f141b7fe700
Vuser 1:RUNNING
Vuser 1:Ssl_cipher
0 MariaDB tpm
Vuser 1:Beginning rampup time of 2 minutes
Vuser 2:RUNNING
Vuser 2:Ssl_cipher
Vuser 2:Processing 10000000 transactions with output suppressed...
0 MariaDB tpm
0 MariaDB tpm
0 MariaDB tpm
0 MariaDB tpm
6 MariaDB tpm
Vuser 1:Rampup 1 minutes complete ...
6 MariaDB tpm
0 MariaDB tpm
0 MariaDB tpm
12 MariaDB tpm
6 MariaDB tpm
6 MariaDB tpm
Vuser 1:Rampup 2 minutes complete ...
Vuser 1:Rampup complete, Taking start Transaction Count.
Vuser 1:Timing test period of 2 in minutes
0 MariaDB tpm
6 MariaDB tpm
0 MariaDB tpm
0 MariaDB tpm
6 MariaDB tpm
12 MariaDB tpm
Vuser 1:1 ...,
6 MariaDB tpm
6 MariaDB tpm
0 MariaDB tpm
0 MariaDB tpm
0 MariaDB tpm
6 MariaDB tpm
Vuser 1:2 ...,
Vuser 1:Test complete, Taking end Transaction Count.
Vuser 1:1 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 1 NOPM from 3 MariaDB TPM
Vuser 1:FINISHED SUCCESS

Now, let’s see what happens if we increase the number of virtual users to 10 and 100.  Again, each virtual user we add is running at approximately 1 NOPM.

Vuser 1:10 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 14 NOPM from 29 MariaDB TPM

Vuser 1:100 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 117 NOPM from 284 MariaDB TPM

So what we have by adding keying and thinking time is a fixed throughput workload. Each virtual user we add is going to add approximately 1 NOPM, so we know exactly the configuration we need to test a particular level of throughput up to the limits of the system we found with the default workload.

Maximum Throughput

So far we have seen throughput of ‘approximately’ 1 NOPM per virtual user. However, the HammerDB TPROC-C workload is derived from the TPC-C specification using the same keying and thinking times with the specification determining maximum throughput as follows:

The maximum throughput is achieved with infinitely fast transactions resulting in a null response time and minimum required wait times. The intent of this clause is to prevent reporting a throughput that exceeds this maximum, which is computed to be 12.86 tpmC per warehouse.

In the TPC-C specification, each warehouse has 10 connections and therefore with HammerDB derived workload this enables us to use 1.28 NOPM per virtual user as a guide to our fixed throughput.

Asynchronous Scaling

Now, this guide of 1.28 NOPM per virtual user presents a challenge if we want to scale numerous virtual users to drive meaningful throughput.  In particular, if these virtual users are spending most of their time waiting for keying or thinking time. For this reason, HammerDB implements asynchronous scaling whereby each virtual user can make multiple connections to the database and run the transactions for these connections when they wake from their keying and thinking time.  We have covered in many blog posts before the concept of parallelism vs concurrency, e.g.

Why Tcl is 700% faster than Python for database benchmarking

Therefore, it should be clear that the virtual users are operating in parallel and the asynchronous connections concurrently.  This way, we can scale HammerDB fixed throughput workloads to thousands of connections by setting the async_scale and async_client options. Note that keyandthink must be enabled as it is the keying and thinking time that is managed asynchronously.

diset tpcc maria_async_scale true
diset tpcc maria_async_client 10

So let’s re-run with 1 virtual user but this time with 10 asynchronous clients. We can see that virtual user now makes 10 connections and we get the 12 NOPM we are expecting.

TEST STARTED
Vuser 1 created MONITOR - WAIT IDLE
Vuser 2 created - WAIT IDLE
2 Virtual Users Created with Monitor VU
Transaction Counter Started
Transaction Counter thread running with threadid:tid0x7f1ce2b97700
Vuser 1:RUNNING
Vuser 1:Ssl_cipher
0 MariaDB tpm
Vuser 1:Beginning rampup time of 2 minutes
Vuser 2:RUNNING
Vuser 2:Started asynchronous clients:vuser2:ac1 vuser2:ac2 vuser2:ac3 vuser2:ac4 vuser2:ac5 vuser2:ac6 vuser2:ac7 vuser2:ac8 vuser2:ac9 vuser2:ac10
Vuser 2:Processing 10000000 transactions with output suppressed...
Vuser 2:Processing 10000000 transactions with output suppressed...
Vuser 2:Processing 10000000 transactions with output suppressed...
Vuser 2:Processing 10000000 transactions with output suppressed...
Vuser 2:Processing 10000000 transactions with output suppressed...
Vuser 2:Processing 10000000 transactions with output suppressed...
Vuser 2:Processing 10000000 transactions with output suppressed...
Vuser 2:Processing 10000000 transactions with output suppressed...
Vuser 2:Processing 10000000 transactions with output suppressed...
Vuser 2:Processing 10000000 transactions with output suppressed...
...
Vuser 1:Test complete, Taking end Transaction Count.
Vuser 1:1 VU * 10 AC = 10 Active Sessions configured
Vuser 1:TEST RESULT : System achieved 12 NOPM from 31 MariaDB TPM
Vuser 1:FINISHED SUCCESS

Now if we ramp up the connections to 100 asynchronous clients, we get 128 NOPM.

Vuser 2:RUNNING
Vuser 2:Started asynchronous clients:vuser2:ac1 vuser2:ac2 vuser2:ac3 vuser2:ac4 vuser2:ac5 vuser2:ac6 vuser2:ac7 vuser2:ac8 vuser2:ac9 vuser2:ac10 vuser2:ac11 vuser2:ac12 vuser2:ac13 vuser2:ac14 vuser2:ac15 vuser2:ac16 vuser2:ac17 vuser2:ac18 vuser2:ac19 vuser2:ac20 vuser2:ac21 vuser2:ac22 vuser2:ac23 vuser2:ac24 vuser2:ac25 vuser2:ac26 vuser2:ac27 vuser2:ac28 vuser2:ac29 vuser2:ac30 vuser2:ac31 vuser2:ac32 vuser2:ac33 vuser2:ac34 vuser2:ac35 vuser2:ac36 vuser2:ac37 vuser2:ac38 vuser2:ac39 vuser2:ac40 vuser2:ac41 vuser2:ac42 vuser2:ac43 vuser2:ac44 vuser2:ac45 vuser2:ac46 vuser2:ac47 vuser2:ac48 vuser2:ac49 vuser2:ac50 vuser2:ac51 vuser2:ac52 vuser2:ac53 vuser2:ac54 vuser2:ac55 vuser2:ac56 vuser2:ac57 vuser2:ac58 vuser2:ac59 vuser2:ac60 vuser2:ac61 vuser2:ac62 vuser2:ac63 vuser2:ac64 vuser2:ac65 vuser2:ac66 vuser2:ac67 vuser2:ac68 vuser2:ac69 vuser2:ac70 vuser2:ac71 vuser2:ac72 vuser2:ac73 vuser2:ac74 vuser2:ac75 vuser2:ac76 vuser2:ac77 vuser2:ac78 vuser2:ac79 vuser2:ac80 vuser2:ac81 vuser2:ac82 vuser2:ac83 vuser2:ac84 vuser2:ac85 vuser2:ac86 vuser2:ac87 vuser2:ac88 vuser2:ac89 vuser2:ac90 vuser2:ac91 vuser2:ac92 vuser2:ac93 vuser2:ac94 vuser2:ac95 vuser2:ac96 vuser2:ac97 vuser2:ac98 vuser2:ac99 vuser2:ac100
...
Vuser 1:Test complete, Taking end Transaction Count.
Vuser 1:1 VU * 100 AC = 100 Active Sessions configured
Vuser 1:TEST RESULT : System achieved 128 NOPM from 286 MariaDB TPM
Vuser 1:FINISHED SUCCESS

and 10,000 connections gives us  12,612 NOPM, which is what we would expect from our fixed throughput configuration.

mysql> show processlist
...
10003 rows in set (0.01 sec)
...
Vuser 1:Test complete, Taking end Transaction Count.
Vuser 1:100 VU * 100 AC = 10000 Active Sessions configured
Vuser 1:TEST RESULT : System achieved 12612 NOPM from 29201 MariaDB TPM

Remember that you should also increase the rampup time to allow enough time for all of the asynchronous clients to connect and reached our fixed throughput rate.

Modifying the keying and thinking time

If you want to modify the keying and thinking time you can do that by modifying the script you are running. Firstly run the savescript command.

hammerdb>savescript fixed.tcl
Success ... wrote script to /home/HammerDB-4.8/TMP/fixed.tcl

and then edit the file. Towards the end of the file you can see the section where transaction selection takes place and the implementation of keying and thinking time.

        set choice [ RandomNumber 1 23 ]
                if {$choice <= 10} {
                    if { $async_verbose } { puts "$clientname:w_id:$w_id:neword" }
                    if { $KEYANDTHINK } { async_keytime 18  $clientname neword $async_verbose }
                    neword $maria_handler $w_id $w_id_input $prepare $RAISEERROR $clientname
                    if { $KEYANDTHINK } { async_thinktime 12 $clientname neword $async_verbose }
                } elseif {$choice <= 20} {
                    if { $async_verbose } { puts "$clientname:w_id:$w_id:payment" }
                    if { $KEYANDTHINK } { async_keytime 3 $clientname payment $async_verbose }
                    payment $maria_handler $w_id $w_id_input $prepare $RAISEERROR $clientname
                    if { $KEYANDTHINK } { async_thinktime 12 $clientname payment $async_verbose }
                } elseif {$choice <= 21} {
                    if { $async_verbose } { puts "$clientname:w_id:$w_id:delivery" }
                    if { $KEYANDTHINK } { async_keytime 2 $clientname delivery $async_verbose }
                    delivery $maria_handler $w_id $prepare $RAISEERROR $clientname
                    if { $KEYANDTHINK } { async_thinktime 10 $clientname delivery $async_verbose }
                } elseif {$choice <= 22} {
                    if { $async_verbose } { puts "$clientname:w_id:$w_id:slev" }
                    if { $KEYANDTHINK } { async_keytime 2 $clientname slev $async_verbose }
                    slev $maria_handler $w_id $stock_level_d_id $prepare $RAISEERROR $clientname
                    if { $KEYANDTHINK } { async_thinktime 5 $clientname slev $async_verbose }
                } elseif {$choice <= 23} {
                    if { $async_verbose } { puts "$clientname:w_id:$w_id:ostat" }
                    if { $KEYANDTHINK } { async_keytime 2 $clientname ostat $async_verbose }
                    ostat $maria_handler $w_id $prepare $RAISEERROR $clientname
                    if { $KEYANDTHINK } { async_thinktime 5 $clientname ostat $async_verbose }
                }

So, we will roughly halve the keying and thinking time (the values must be whole numbers) and save the file.

set choice [ RandomNumber 1 23 ]
                if {$choice <= 10} {
                    if { $async_verbose } { puts "$clientname:w_id:$w_id:neword" }
                    if { $KEYANDTHINK } { async_keytime 9  $clientname neword $async_verbose }
                    neword $maria_handler $w_id $w_id_input $prepare $RAISEERROR $clientname
                    if { $KEYANDTHINK } { async_thinktime 6 $clientname neword $async_verbose }
                } elseif {$choice <= 20} {
                    if { $async_verbose } { puts "$clientname:w_id:$w_id:payment" }
                    if { $KEYANDTHINK } { async_keytime 2 $clientname payment $async_verbose }
                    payment $maria_handler $w_id $w_id_input $prepare $RAISEERROR $clientname
                    if { $KEYANDTHINK } { async_thinktime 6 $clientname payment $async_verbose }
                } elseif {$choice <= 21} {
                    if { $async_verbose } { puts "$clientname:w_id:$w_id:delivery" }
                    if { $KEYANDTHINK } { async_keytime 1 $clientname delivery $async_verbose }
                    delivery $maria_handler $w_id $prepare $RAISEERROR $clientname
                    if { $KEYANDTHINK } { async_thinktime 5 $clientname delivery $async_verbose }
                } elseif {$choice <= 22} {
                    if { $async_verbose } { puts "$clientname:w_id:$w_id:slev" }
                    if { $KEYANDTHINK } { async_keytime 1 $clientname slev $async_verbose }
                    slev $maria_handler $w_id $stock_level_d_id $prepare $RAISEERROR $clientname
                    if { $KEYANDTHINK } { async_thinktime 3 $clientname slev $async_verbose }
                } elseif {$choice <= 23} {
                    if { $async_verbose } { puts "$clientname:w_id:$w_id:ostat" }
                    if { $KEYANDTHINK } { async_keytime 1 $clientname ostat $async_verbose }
                    ostat $maria_handler $w_id $prepare $RAISEERROR $clientname
                    if { $KEYANDTHINK } { async_thinktime 3 $clientname ostat $async_verbose }
                }

Now, we can use customscript to load the modified workload and custommonitor to configure the additional monitor virtual user shown in this excerpt.

#loadscript
customscript /home/HammerDB-4.8/TMP/fixed.tcl
custommonitor timed
puts "TEST STARTED"
vuset vu 100

Note, that with a customscript the values you have configured for the asynchronous clients will now be set in this script and not loaded dynamically.

set async_client 100;# Number of asynchronous clients per Vuser
set async_verbose false;# Report activity of asynchronous clients
set async_delay 1000;# Delay in ms between logins of asynchronous clients

So now we have halved the keying and thinking time we have doubled the throughput.

Vuser 1:Test complete, Taking end Transaction Count.
Vuser 1:100 VU * 100 AC = 10000 Active Sessions configured
Vuser 1:TEST RESULT : System achieved 24310 NOPM from 56630 MariaDB TPM
Vuser 1:FINISHED SUCCESS

You should not take the keying and thinking time to minimal values as remember a single virtual user is servicing the requests from all of their configured asynchronous connections.

Using middleware

We have illustrated the concept of a fixed throughput workload up to 10,000 connections and if you have followed the examples you will have already needed to increase system limits  such as open files and database limits such as max connections. Nevertheless the maximum setting for the number of connections for MariaDB is 100,000 still short of our 700,000 default workload. For this reason when configured such a fixed throughput workload you will need to configure middleware such as maxscale or proxysql in our MariaDB example. Typically you will have actual database connections numbered in the low hundreds with HammerDB connecting to the proxy instead.  Additionally you can configure HammerDB for remote primary and replica modes connecting multiple instances of HammerDB using asynchronous clients to scale up to hundreds of thousands of connections needed to drive your system to the maximum throughput achieved with the default workload.

 

HammerDB v4.8 New Features : Viewing your Benchmark results with the HammerDB Web Service

In HammerDB v4.7 introduced the concept of Jobs to the CLI, a central repository to store workload output, configuration and results. v4.8 extends this functionality to the GUI and adds a Web Service to view and visualize this Job related data.

Turning Jobs Off and On

Of course one of the most important aspects is to be able to disable the functionality. To turn the functionality on or off in the CLI use the jobs disable command.

hammerdb>jobs disable 1
Disabling jobs repository, restart HammerDB to take effect

and in the GUI use the Jobs Disabled/Enabled option.

Configuring the Jobs Database

The base configuration is set in the xml files in the config directory with the jobs SQLite database being set in the commandline section of generic.xml by specifying the filename. So for example to change this to hammer.db in in the DATA directory, set this as follows:

<commandline>
    <sqlite_db>/opt/HammerDB-4.8/DATA/hammer.db</sqlite_db> 
       <keepalive_margin>10</keepalive_margin>
       <jobsoutput>JSON</jobsoutput>
       <jobs_disable>0</jobs_disable>
</commandline>

Then HammerDB will initialize this database

$ ./hammerdbcli
HammerDB CLI v4.8
Copyright (C) 2003-2023 Steve Shaw
Type "help" for a list of commands
Initialized new Jobs on-disk database /opt/HammerDB-4.8/DATA/hammer.db

Note that this is the Jobs SQLite database. We also have the configuration SQLite databases that persists the configuration over restarts. This also includes the jobs SQLite database location, so after the configuration SQLite databases are created then changes to the XML files are not re-read until the files are deleted (or it detects a different version of HammerDB and will refresh them automatically).

Therefore the best approach is  to leave the configuration as the default of TMP and set an environment variable to the preferred location. On Linux it will look for environment variables TMP, TMPDIR, or TEMP and then finally look for /tmp. On Windows it will look for TEMP, TMP, TMPDIR or finally C:. for example, on Linux.

$ export TMP=`pwd`/TMP
$ ./hammerdbcli 
HammerDB CLI v4.8
Copyright (C) 2003-2023 Steve Shaw
Type "help" for a list of commands
Initialized new Jobs on-disk database /opt/HammerDB-4.8/TMP/hammer.DB

This database will be used by both the CLI and GUI and can be moved or removed as preferred. When jobs are enabled, if the database file does not exist in the specified location, it will be recreated.

Running a workload

Jobs only modify how output is stored and not how the workload itself is run. In this example, we will run a PostgreSQL TPROC-C autopilot workload to generate some jobs to analyse. Note that if we want to capture a transaction count during a run, then the transaction counter must be enabled and similarly if we want to capture timing data this option must be enabled.

We can see that a Job id is created each time a new workload is run.

Starting and Stopping the Web Service

To start and stop the Web Service under the GUI select the Jobs Options under the Tree menu.

The options enable you to start/stop and query the status of the Web Service.

When running, the status will show the Web Service environment.

Note that on Windows, security features may prevent you opening the port which is by default 8080 and therefore you may need to review the additional options for starting and stopping the Web Service.  Therefore we will give examples on Windows, however the commands are the same on Linux as well.

Under the CLI you have the command wsstart, wsstop and wsstatus for the same functionality as the GUI.  Also the command wsport can query and change the port the Web Service runs on.

HammerDB CLI v4.8
Copyright (C) 2003-2023 Steve Shaw
Type "help" for a list of commands
Initialized Jobs on-disk database C:/Users/hdb/AppData/Local/Temp/hammer.DB using existing tables (245,760 KB)
hammerdb>wsstart
HammerDB Web Service v4.8
Copyright (C) 2003-2023 Steve Shaw
Type "help" for a list of commands
Starting HammerDB Web Service on port 8080
hammerdb>wsstop
Stopping HammerDB Web Service on port 8080
hammerdb>wsstatus
Web Service not running: connect failed connection refused
hammerdb>wsport
Web Service Port set to 8080

You can also run the Web Service directly from a command prompt with the additional options of wait/nowait and gui, with wait, waiting without a CLI prompt, nowait returning a prompt that enables querying the interface directly (with the help command providing a list of commands) and gui that returns no output at all (as run by the GUI).

C:\Program Files\HammerDB-4.8>hammerdbws wait
HammerDB Web Service v4.8
Copyright (C) 2003-2023 Steve Shaw
Type "help" for a list of commands
Starting HammerDB Web Service on port 8080

Querying Jobs

With the Web Service running, we can start a browser from the GUI with the Browse option or run a browser directly to the port we have configured.  We can now see the list of Jobs we generated with autopilot, that they all ran successfully, and also pinpoint the highest performance.

We can now drill down on an individual job to view its configuration and output.

Text based data will be shown in JSON format, with the example showing the full workload configuration at the time it was run.

The result option will show a chart of the NOPM and TPM data with a link to the data it is generated from.

Charts are generated with Apache echarts and therefore the browser must be able to access the URL.

https://cdn.jsdelivr.net/npm/echarts@5.4.1/dist/echarts.min.js

If Apache echarts cannot be downloaded then the chart screens will be left blank.

The transaction counter link will be shown if the transaction counter was running during the workload (for both GUI and CLI). If the transaction counter was not running, then the link is omitted.

If the timing option was enabled, then  a chart for response times is also generated.

Charts are also generated for TPROC-H results and timings.

Failed and Unknown Jobs

If a Job errors or is left in an unknown state, then that will be reported in the Job Index. Note the top result is shown for both TPROC-C and TPROC-H.

Job CLI Interface

Jobs can also continue to be queried directly from the CLI with additional functionality at this interface such as querying the timings of individual Virtual Users.

hammerdb>job 6487506D5FDF03E263338333 result
[
  "6487506D5FDF03E263338333",
  "2023-06-12 18:05:49",
  "12 Active Virtual Users configured",
  "TEST RESULT : System achieved 46050 NOPM from 202715 Db2 TPM"
]


hammerdb>job 6487506D5FDF03E263338333 timing 2
{
  "NEWORD": {
    "elapsed_ms": "420335.0",
    "calls": "26129",
    "min_ms": "2.274",
    "avg_ms": "8.121",
    "max_ms": "194.175",
    "total_ms": "212183.912",
    "p99_ms": "24.189",
    "p95_ms": "14.837",
    "p50_ms": "7.21",
    "sd": "4729.629",
    "ratio_pct": "50.48"
  },
  "PAYMENT": {
    "elapsed_ms": "420335.0",
    "calls": "26219",
    "min_ms": "1.742",
    "avg_ms": "6.192",
    "max_ms": "154.997",
    "total_ms": "162349.531",
    "p99_ms": "18.641",
    "p95_ms": "11.624",
    "p50_ms": "5.47",
    "sd": "3479.359",
    "ratio_pct": "38.624"
  },
  "DELIVERY": {
    "elapsed_ms": "420335.0",
    "calls": "2691",
    "min_ms": "3.615",
    "avg_ms": "8.983",
    "max_ms": "66.023",
    "total_ms": "24172.593",
    "p99_ms": "27.364",
    "p95_ms": "15.336",
    "p50_ms": "8.032",
    "sd": "4385.244",
    "ratio_pct": "5.751"
  },
  "SLEV": {
    "elapsed_ms": "420335.0",
    "calls": "2714",
    "min_ms": "0.795",
    "avg_ms": "3.243",
    "max_ms": "78.237",
    "total_ms": "8800.962",
    "p99_ms": "11.414",
    "p95_ms": "7.068",
    "p50_ms": "2.586",
    "sd": "2776.821",
    "ratio_pct": "2.094"
  },
  "OSTAT": {
    "elapsed_ms": "420335.0",
    "calls": "2663",
    "min_ms": "0.705",
    "avg_ms": "3.072",
    "max_ms": "55.527",
    "total_ms": "8180.22",
    "p99_ms": "10.176",
    "p95_ms": "6.495",
    "p50_ms": "2.57",
    "sd": "2302.054",
    "ratio_pct": "1.946"
  }
}

Additional CLI functionality is also provided with the getchart command to return the HTML for a generated chart.

hammerdb>job 6487506D5FDF03E263338333 getchart result
<!DOCTYPE html>
<html>
  <head>
    <meta charset="UTF-8">
    <title>6487506D5FDF03E263338333 Result</title>
    <script type="text/javascript" src="https://cdn.jsdelivr.net/npm/echarts@5.4.1/dist/echarts.min.js"></script>
  </head>
  <body>
    <div id="id_a3a1672ce6814324bd80d8b84cff1557" class="chart-container" style="width:900px; height:500px;"></div>
    <script>
        var chart_a3a1672ce6814324bd80d8b84cff1557 = echarts.init(document.getElementById('id_a3a1672ce6814324bd80d8b84cff1557'), null, {renderer: 'canvas'});
        var option_a3a1672ce6814324bd80d8b84cff1557 = {
  "backgroundColor": "rgba(0,0,0,0)",
  "color": [
    "#5470c6",
    "#91cc75",
    "#fac858",
    "#ee6666",
    "#73c0de",
    "#3ba272",
    "#fc8452",
    "#9a60b4",
    "#ea7ccc"
  ],
  "animation": true,
  "animationDuration": 1000,
  "animationDurationUpdate": 500,
  "animationEasing": "cubicInOut",
  "animationEasingUpdate": "cubicInOut",
  "animationThreshold": 2000,
  "progressiveThreshold": 3000,
  "title": [{
      "show": true,
      "text": "Db2 TPROC-C Result 6487506D5FDF03E263338333 @ 2023-06-12 18:05:49",
      "target": "blank",
      "subtarget": "blank",
      "textAlign": null,
      "textVerticalAlign": "auto",
      "padding": 5,
      "itemGap": 10,
      "z": 2,
      "left": "auto",
      "top": "auto",
      "right": "auto",
      "bottom": "auto",
      "backgroundColor": "transparent",
      "borderColor": "transparent",
      "borderWidth": 1,
      "borderRadius": 0
    }],
  "legend": [{
      "type": "plain",
      "show": true,
      "z": 2,
      "left": "45%",
      "top": "auto",
      "right": "auto",
      "bottom": "5%",
      "width": "auto",
      "height": "auto",
      "orient": "horizontal",
      "align": "auto",
      "padding": 5,
      "itemGap": 10,
      "itemWidth": 25,
      "itemHeight": 14,
      "symbolRotate": "inherit",
      "selectedMode": true,
      "inactiveColor": "rgb(204, 204, 204)",
      "inactiveBorderColor": "rgb(204, 204, 204)",
      "inactiveBorderWidth": "auto",
      "backgroundColor": "transparent",
      "borderWidth": 0,
      "borderRadius": 0,
      "pageButtonItemGap": 5,
      "pageIconColor": "rgb(47, 69, 84)",
      "pageIconInactiveColor": "rgb(170, 170, 170)",
      "pageIconSize": 15
    }],
  "tooltip": [{
      "show": true,
      "trigger": "item",
      "showContent": true,
      "alwaysShowContent": false,
      "triggerOn": "mousemove|click",
      "transitionDuration": 0.4,
      "padding": 5,
      "order": "seriesAsc"
    }],
  "xAxis": [{
      "show": true,
      "type": "category",
      "data": ["Db2 12 Active Virtual Users configured"],
      "gridIndex": 0,
      "position": "bottom",
      "offset": 0,
      "nameLocation": "end",
      "nameGap": 15,
      "nameRotate": 0,
      "inverse": false,
      "boundaryGap": true,
      "scale": false,
      "splitNumber": 5,
      "minInterval": 0,
      "silent": false,
      "triggerEvent": false,
      "axisLabel": {
        "show": true,
        "interval": "auto",
        "inside": false,
        "rotate": 0,
        "margin": 8,
        "showMinLabel": null,
        "showMaxLabel": null,
        "hideOverlap": false,
        "fontStyle": "normal",
        "fontWeight": "normal",
        "fontFamily": "sans-serif",
        "fontSize": 12,
        "lineHeight": 12,
        "backgroundColor": "transparent",
        "borderWidth": 0,
        "borderType": "solid",
        "borderDashOffset": 0,
        "borderRadius": 0,
        "padding": 0,
        "shadowColor": "transparent",
        "shadowBlur": 0,
        "shadowOffsetX": 0,
        "shadowOffsetY": 0,
        "textBorderColor": null,
        "textBorderWidth": 0,
        "textBorderType": "solid",
        "textBorderDashOffset": 0,
        "textShadowColor": "transparent",
        "textShadowBlur": 0,
        "textShadowOffsetX": 0,
        "textShadowOffsetY": 0,
        "overflow": "truncate",
        "ellipsis": "..."
      },
      "zlevel": 0,
      "z": 0
    }],
  "yAxis": [{
      "show": true,
      "gridIndex": 0,
      "position": "left",
      "offset": 0,
      "realtimeSort": true,
      "sortSeriesIndex": 0,
      "type": "value",
      "name": "Transactions",
      "nameLocation": "end",
      "nameGap": 15,
      "nameRotate": 0,
      "inverse": false,
      "boundaryGap": false,
      "scale": false,
      "splitNumber": 5,
      "minInterval": 0,
      "silent": false,
      "triggerEvent": false,
      "axisLabel": {
        "show": true,
        "interval": "auto",
        "inside": false,
        "rotate": 0,
        "margin": 8,
        "formatter": "{value}",
        "showMinLabel": null,
        "showMaxLabel": null,
        "hideOverlap": false,
        "fontStyle": "normal",
        "fontWeight": "normal",
        "fontFamily": "sans-serif",
        "fontSize": 12,
        "lineHeight": 12,
        "backgroundColor": "transparent",
        "borderWidth": 0,
        "borderType": "solid",
        "borderDashOffset": 0,
        "borderRadius": 0,
        "padding": 0,
        "shadowColor": "transparent",
        "shadowBlur": 0,
        "shadowOffsetX": 0,
        "shadowOffsetY": 0,
        "textBorderColor": null,
        "textBorderWidth": 0,
        "textBorderType": "solid",
        "textBorderDashOffset": 0,
        "textShadowColor": "transparent",
        "textShadowBlur": 0,
        "textShadowOffsetX": 0,
        "textShadowOffsetY": 0,
        "overflow": "truncate",
        "ellipsis": "..."
      },
      "zlevel": 0,
      "z": 0
    }],
  "series": [
    {
      "type": "bar",
      "name": "NOPM",
      "colorBy": "series",
      "legendHoverLink": true,
      "coordinateSystem": "cartesian2d",
      "roundCap": false,
      "showBackground": false,
      "itemStyle": {
        "color": "#00CC00",
        "borderColor": "rgb(0, 0, 0)",
        "borderType": "solid",
        "borderDashOffset": 0,
        "borderCap": "butt",
        "borderJoin": "bevel",
        "borderMiterLimit": 10,
        "opacity": 0.90
      },
      "sampling": "max",
      "cursor": "pointer",
      "barMinWidth": null,
      "barCategoryGap": "20%",
      "large": false,
      "largeThreshold": 400,
      "data": [46050],
      "zlevel": 0,
      "z": 2,
      "silent": false
    },
    {
      "type": "bar",
      "name": "TPM",
      "colorBy": "series",
      "legendHoverLink": true,
      "coordinateSystem": "cartesian2d",
      "roundCap": false,
      "showBackground": false,
      "itemStyle": {
        "color": "#66ff66",
        "borderColor": "rgb(0, 0, 0)",
        "borderType": "solid",
        "borderDashOffset": 0,
        "borderCap": "butt",
        "borderJoin": "bevel",
        "borderMiterLimit": 10,
        "opacity": 0.90
      },
      "sampling": "max",
      "cursor": "pointer",
      "barMinWidth": null,
      "barCategoryGap": "20%",
      "large": false,
      "largeThreshold": 400,
      "data": [202715],
      "zlevel": 0,
      "z": 2,
      "silent": false
    }
  ]
}
chart_a3a1672ce6814324bd80d8b84cff1557.setOption(option_a3a1672ce6814324bd80d8b84cff1557);
    </script>
  </body>
</html>

Summary

In this post we have introduced new HammerDB v4.8 functionality for viewing workload related results and configuration from a central location.

How to Assess MySQL Performance

Predicting application performance is a difficult art, but an important one when choosing the target deployment environment. Among the different components of modern software solutions, the database is one of the most critical. Regardless of whether the computing platform to be evaluated is on-prem, containerized, virtualized, or in the cloud, it is crucial to consider several essential factors. In this blog, we aim to call out some key considerations when trying to assess MySQL performance for your application. We will do this by conducting experiments using HammerDB and sysbench — two popular workloads with different approach and focus — to evaluate MySQL performance. We will not concern ourselves with the raw throughput of workload. Instead, focus on understanding what the workloads exercise to help us determine how to best use them to aid our performance assessment.

Know your target platform

Everything starts with knowing the target. There are many times we get asked why some cloud instance performed poorly for their database application and almost always turned out to be some configuration error. Therefore, before we attempt to measure our database performance, we should know the system or cloud instance to be tested in detail. This allows us to know our operating environment and its capability. As database performance is heavily influenced by the performance of storage, network, memory, and processors, we must understand the upper limit of these key components. There are several ways to find out this information with the easiest way being by referring to the documentation. For example, if you are buying the latest Amazon memory-optimized EC2 instance (R7iz), the AWS page (https://aws.amazon.com/ec2/instance-types/) tells us the following:

  • Up to 3.9 GHz 4th Generation Intel Xeon Scalable processors (code-named Sapphire Rapids)
  • Up to 20% higher compute performance than z1d instances
  • Up to 50 Gbps of networking speed
  • Up to 40 Gbps of bandwidth to the Amazon Elastic Block Store (EBS)

We can also verify these capabilities by running some simple benchmarks on the different subsystems. For storage, FIO is generally used. For the network, we can use Iperf to assess the network bandwidth between the client and the database server to ensure it will be enough to meet our peak requirement.

Benchmarking the target

Two of the more popular database benchmarks for MySQL are HammerDB and sysbench. Since we are posting this blog on the HammerDB blog site, we will not describe the HammerDB workload in this blog. Instead, readers should refer to the HammerDB documentation.

sysbench is a widely used open-source benchmarking tool that is designed to evaluate the performance of CPU, memory, disk I/O, and database systems. In the context of MySQL performance evaluation, it simulates a typical online transaction processing (OLTP) workload on your MySQL database. The OLTP workload simulates a typical online transaction processing workload, where multiple clients execute transactions on a database concurrently. This all sounded very similar to HammerDB TPROC-C workload (we will look at HammerDB TPROC-H (OLAP) another time), so it is easy for any reader to think both workloads are about the same. Let’s look at how the workloads behave when running on an identical system

We ran both sysbench and HammerDB on a system with:

  • Processors: Two Intel Xeon 8360Y processor sockets (36 Core/72 Threads per socket). We used the first processor socket for the MySQL database and the second socket for the benchmark (sysbench or HammerDB).
  • Operating System: Ubuntu 22.04
  • Storage: The system has a SATA drive for the operating system and one NVMe (Intel SSD D7-P5510 (3.84 TB)) for storage of database tablespaces and logging.
  • Database: MySQL 8.0.31

We applied the following settings for both benchmarks to make it easier to stress the system. MySQL has thousands of other settings and is beyond the scope of this blog.

  • skip-log-bin
  • innodb_flush_log_at_trx_commit=0
  • innodb_flush_method=O_DIRECT_NO_FSYNC
  • innodb_doublewrite=0
  • innodb_buffer_pool_size=64000M
  • HammerDB Settings:
    • diset tpcc mysql_dbase tpcc
    • diset tpcc mysql_count_ware 1000
    • diset tpcc mysql_rampup 2
    • diset tpcc mysql_duration 5
    • vuset vu 256
  • Sysbench Settings:
    • Create sysbench: sysbench –mysql-user=user –mysql_password=password –mysql-host=dbserver –tables=214 –table-size=2000000 /usr/share/sysbench/oltp_read_write.lua prepare
    • –rand-type=uniform –report-interval=1 –time=900 –mysql-db=sbtest oltp_read_write run

Both workloads also support many, probably hundreds if not thousands, of tunable. We are using the most basic, default, configurations for a database of about 100GB. Please refer to this tuning guide to tune the system for HammerDB: Open Source Database Tuning Guide on 3rd Generation Intel® Xeon® Scalable Processors Based Platform. For the experiments in this blog, we did not tune the system. Instead, use the default settings. The goal of this blog is not to get the best performance but to show the differences between the benchmarks and how they can give us insight from different angles.

The table below shows the performance characteristics of both benchmarks captured using the System Activity Reporter (SAR) tool in Linux:

sysbench HammerDB sysbench/
HammerDB
CPU METRICS
cpu_utilization_% 66.83 97.81 0.68
cpu_utilization_%_in_kernel_mode 29.70 7.04 4.22
%usr 38.40 91.72 0.42
%sys 9.52 3.41 2.79
%iowait 36.51 0.04 835.79
%idle 12.01 2.89 4.16
DISK METRICS
sysstat_io_nvme0n1_avg_wait_ms 8.32 0.21 39.23
sysstat_io_nvme0n1_read_kB/s 2,535,38 14,289 177.44
sysstat_io_nvme0n1_write_kB/s 904,422 248,075 3.65
NETWORK METRICS
sysstat_net_ens260f1_rx_kB/s 38,241 22,700 1.68
sysstat_net_ens260f1_tx_kB/s 675,910 34,712 19.47
OTHER METRICS
average context switches/sec 1,610,78 320,699 5.02
avg interrupts/sec 888,675 256,589 3.46

From the above table, we noticed the following:

  • HammerDB saturates the CPU and spends a larger portion of time in user mode (91.72% for HammerDB vs. 38.40% for sysbench). sysbench spends a higher portion of time in system mode as compared to HammerDB (9.52% for sysbench vs. 3.41% for HammerDB). From this data, it can be argued that HammerDB is a better candidate to test MySQL database engine and CPU performance.
  • sysbench exerts greater pressure on the storage and network subsystem as compared to HammerDB. The %iowait for sysbench was very high even though we used a fast NVMe drive for the database and log files, ~37% iowait for sysbench vs. almost 0% for HammerDB. This suggested that sysbench is a better candidate for testing storage and network performance.

By default, HammerDB is designed to take advantage of database system caching mechanisms such as buffer caches, query caches, or statement caches. This means, HammerDB prioritizes caching the data as much as possible and pushing the CPU utilization. HammerDB does this using the concept of “home warehouses” where approximately 90% of the workload takes place. Each HammerDB virtual user is assigned a “home warehouse” at the start of the workload and which remains fixed. Hence if you configure a schema with 1000 warehouses and run a test with 10 virtual users by default most of the workload will be concentrated upon 10 warehouses. In the case of sysbench, if the database tables are too large to fit entirely in the main memory, it accesses data from the disk more frequently leading to more disk I/O and wait times. Also, sysbench makes a network roundtrip for every SQL Statement whereas HammerDB wraps a more complex workload in stored procedures resulting in only one network call per stored procedure.

We will have a follow-up blog looking into what the database is doing and how the CPUs are being used. Also, a separate blog looking into the detail of how each of these workloads used the storage. Be sure to check back. Or if you already conducted studies that answered these questions, please share. We can also collaborate.

Based on user requirements and priorities, HammerDB and sysbench are both great workloads to evaluate database performance. Also, instead of viewing them as mutually exclusive, they can also be used in tandem to get a more holistic view of the system being studied. In addition, both these workloads are highly configurable and can be configured as per the user’s requirements.

Size does matter!

When testing, we need to make sure that we tested with the configuration that matters most to our application. For example, if we want to evaluate moving our OLTP applications from MySQL 8.0.27 to MySQL 8.0.32, different test scenarios can give very different conclusions. The table below showed the result of running HammerDB on MySQL 8.0.27 and 8.0.32 on identical hardware, with identical settings, but at different load levels. We tested concurrency at 80 VU’s and concurrency at 256 VU’s.

HammerDB 4.5 TPROC-C running on

Intel Xeon 8360Y processor with 36-core (72-threads)

MySQL 8.0.27 MySQL 8.0.32
Concurrency at 80 VU’s 1.00 0.93
Concurrency at 256 VU’s 1.00 1.03

At 80 VU concurrency, we observed a 7% performance regression moving from MySQL 8.0.27 to 8.0.32. But at 256 VU concurrency, we would expect to get a 3% performance gain by moving from MySQL 8.0.27 to 8.0.32.

Summary

For any benchmark, it is critical that we use the configuration that is most representative of our deployment environment to get the most accurate assessment. Overall, HammerDB and sysbench are both valuable tools for evaluating MySQL performance.

  • HammerDB aimed to make it easier to stress test the database engine scaling to handle more connections, locking, and a high number of CPU cores.
  • sysbench has a higher demand for storage and medium stress on the CPU and network making it a good candidate for stress testing the platform.

Find the right PostgreSQL, MySQL and MariaDB parameters first time with hammerpost

Hammerpost is a HammerDB contribution project from @dineshkumar02 that wraps HammerDB with an automated framework to rapidly prototype multiple combinations of database parameters to find the optimal configuration for any specific environment.

You can find the hammerpost project and documentation here. This post gives a brief example of using hammerpost to encourage you to investigate this project further.  In this example, we already have a PostgreSQL database already running on a system and want to test different combinations of parameters.

Firstly, we will check that we can start our database.

hammerdb@REDPOLL:~$ /home/hammerdb/pgsql/bin/pg_ctl -D /home/hammerdb/pgsql/data start
waiting for server to start....2023-04-04 11:27:29.650 GMT [3555] LOG:  redirecting log output to logging collector process
2023-04-04 11:27:29.650 GMT [3555] HINT:  Future log output will appear in directory "log".
done
server started

Next, we will install the hammerpost-agent on the same system as the database,  this example the system is running Ubuntu 22.04.

hammerdb@REDPOLL:~$ git clone https://github.com/dineshkumar02/hammerpost-agent.git
Cloning into 'hammerpost-agent'...
remote: Enumerating objects: 38, done.
remote: Counting objects: 100% (38/38), done.
remote: Compressing objects: 100% (22/22), done.
remote: Total 38 (delta 15), reused 32 (delta 9), pack-reused 0
Receiving objects: 100% (38/38), 22.45 KiB | 1.25 MiB/s, done.
Resolving deltas: 100% (15/15), done.
hammerdb@REDPOLL:~$ cd hammerpost-agent
hammerdb@REDPOLL:~/hammerpost-agent$ make
GOOS=linux CGO_ENABLED=0 go build -o hammerpost-agent -v -ldflags="-X 'main.Version=0.1.0' -X 'main.GitCommit=45d7496' -X 'main.CommitDate=2023-04-02 19:14:52 +0530'"
hammerpost-agent

We can then start the agent on the same system as the database giving it the ability to start and stop our database with the modified parameters.

hammerdb@REDPOLL:~/hammerpost-agent$ sudo ./hammerpost-agent --stop-cmd "/home/hammerdb/pgsql/bin/pg_ctl -D /home/hammerdb/pgsql/data stop -m f" --start-cmd "/home/hammerdb/pgsql/bin/pg_ctl -D /home/hammerdb/pgsql/data start" --pgdsn "postgres://postgres:postgres@localhost:5432/postgres" --db-type postgres
[GIN-debug] [WARNING] Creating an Engine instance with the Logger and Recovery middleware already attached.

[GIN-debug] [WARNING] Running in "debug" mode. Switch to "release" mode in production.
 - using env:	export GIN_MODE=release
 - using code:	gin.SetMode(gin.ReleaseMode)

[GIN-debug] GET    /start                    --> main.main.func1 (3 handlers)
[GIN-debug] GET    /stop                     --> main.main.func2 (3 handlers)
[GIN-debug] GET    /info                     --> main.main.func3 (3 handlers)
[GIN-debug] GET    /metrics                  --> main.main.func4 (3 handlers)
[GIN-debug] POST   /set-param                --> main.main.func5 (3 handlers)
[GIN-debug] GET    /load                     --> main.main.func6 (3 handlers)
[GIN-debug] GET    /ping                     --> main.main.func7 (3 handlers)
[GIN-debug] [WARNING] You trusted all proxies, this is NOT safe. We recommend you to set a value.
Please check https://pkg.go.dev/github.com/gin-gonic/gin#readme-don-t-trust-all-proxies for details.
[GIN-debug] Listening and serving HTTP on :8989

Next we will grab the latest HammerDB PostgreSQL docker image with the drivers included.

hammerdb@REDPOLL:~$ sudo docker pull tpcorg/hammerdb:postgres
[sudo] password for hammerdb: 
postgres: Pulling from tpcorg/hammerdb
...
Digest: sha256:59d901bfd14452ca3f345524384dd9d4b61effcc555e5133a71534440141c378
Status: Downloaded newer image for tpcorg/hammerdb:postgres
docker.io/tpcorg/hammerdb:postgres

and start the image

hammerdb@REDPOLL:~$ sudo docker run --network=host -it --name hammerdb-postgres tpcorg/hammerdb:postgres bash
root@REDPOLL:/home/hammerdb/HammerDB-4.7#

We now install go inside the container and install hammerpost in the HammerDB directory. Note that it needs to be a more updated version than the default with the HammerDB Docker container.

root@REDPOLL:/usr/local# wget https://go.dev/dl/go1.19.4.linux-amd64.tar.gz
root@REDPOLL:/usr/local# tar -xvf go1.19.4.linux-amd64.tar.gz
root@REDPOLL:/usr/local# export PATH=/usr/local/go/bin:$PATH
root@REDPOLL:/usr/local# apt-get install make

root@REDPOLL:/home/hammerdb/HammerDB-4.7# git clone https://github.com/dineshkumar02/hammerpost.git
root@REDPOLL:/home/hammerdb/HammerDB-4.7/hammerpost# go version
go version go1.19.4 linux/amd64
root@REDPOLL:/home/hammerdb/HammerDB-4.7/hammerpost# go get
root@REDPOLL:/home/hammerdb/HammerDB-4.7/hammerpost# make

Back in the main HammerDB directory we can link the hammerpost executable, templates and parameter file to local links, meaning we can run hammerpost straight from the local hammerdb directory. We have used hammerp as the name of the link to the hammerpost execuatable as the directory is already called hammerpost.

root@REDPOLL:/home/hammerdb/HammerDB-4.7# ln -s ./hammerpost/hammerpost ./hammerp
root@REDPOLL:/home/hammerdb/HammerDB-4.7# ln -s ./hammerpost/hammer-templates ./hammer-templates
root@REDPOLL:/home/hammerdb/HammerDB-4.7# ln -s ./hammerpost/params.json ./params.json

With hammerpost installed the first step is to initialize the schema with an example as follows.

root@REDPOLL:/home/hammerdb/HammerDB-4.7# ./hammerp --init --name test-bench-1 --pgdsn "postgres://postgres:postgres@localhost:5432/postgres" --users 4 --warehouses 10 --hammerpost-agent localhost:8989

╔ hammerpost - v0.1.0 ═════════════════════════════════════════╗
║                                                              ║
║                                                              ║
║                 OS linux                                     ║
║           Platform ubuntu-22.04                              ║
║             Kernel 5.15.0-56-generic                         ║
║             Uptime 12131                                     ║
║    Total Processes 233                                       ║
║           Load Avg 0.05                                      ║
║                CPU Intel(R) Core(TM) i7-4770K CPU @ 3.50GHz  ║
║          CPU Count 4                                         ║
║          CPU Cores 1                                         ║
║            CPU Mhz 3491.918                                  ║
║   Total Memory(GB) 7                                         ║
║    Free Memory(GB) 1                                         ║
║    Used Memory(GB) 0                                         ║
║                                                              ║
║                                                              ║
╚══════════════════════════════════════════════════════════════╝

DB type  postgres
schema initialized

We can then update the params.json file with the combination of parameters we want to test. In this example it is 4 tests with different combinations of shared_buffers and wal_buffers.

root@REDPOLL:/home/hammerdb/HammerDB-4.7# vi params.json
{
        "shared_buffers": ["512MB", "1GB"],
        "wal_buffers": ["32MB", "64MB"]
}

We can then run the test. hammerpost will update the parameters, start and stop the database and run hammerdb.

root@REDPOLL:/home/hammerdb/HammerDB-4.7# ./hammerp --run --name test-bench --pgdsn "postgres://postgres:postgres@redpoll:5432/postgres" --users 4 --warehouses 10 --hammerpost-agent redpoll:8989 --param-file ./params.json  --logfile test-bench1.log

╔ hammerpost - v0.1.0 ═════════════════════════════════════════╗
║                                                              ║
║                                                              ║
║                 OS linux                                     ║
║           Platform ubuntu-22.04                              ║
║             Kernel 5.15.0-56-generic                         ║
║             Uptime 16947                                     ║
║    Total Processes 237                                       ║
║           Load Avg 0.42                                      ║
║                CPU Intel(R) Core(TM) i7-4770K CPU @ 3.50GHz  ║
║          CPU Count 4                                         ║
║          CPU Cores 1                                         ║
║            CPU Mhz 3491.918                                  ║
║   Total Memory(GB) 7                                         ║
║    Free Memory(GB) 0                                         ║
║    Used Memory(GB) 0                                         ║
║                                                              ║
║                                                              ║
╚══════════════════════════════════════════════════════════════╝

DB type  postgres
Benchmark id  5
Parameter test cases  4

┌ Parameters ──────────┐
│                      │
│ shared_buffers:512MB │
│ wal_buffers:32MB     │
│                      │
│                      │
└──────────────────────┘

┌ Results ──────────────┐
│                       │
│ 66906 NOPM 151341 TPM │
│                       │
└───────────────────────┘

┌ Parameters ────────┐
│                    │
│ shared_buffers:1GB │
│ wal_buffers:32MB   │
│                    │
│                    │
└────────────────────┘

┌ Results ──────────────┐
│                       │
│ 70710 NOPM 161991 TPM │
│                       │
└───────────────────────┘

┌ Parameters ──────────┐
│                      │
│ shared_buffers:512MB │
│ wal_buffers:16MB     │
│                      │
│                      │
└──────────────────────┘

┌ Results ──────────────┐
│                       │
│ 67146 NOPM 153539 TPM │
│                       │
└───────────────────────┘

┌ Parameters ────────┐
│                    │
│ shared_buffers:1GB │
│ wal_buffers:16MB   │
│                    │
│                    │
└────────────────────┘

┌ Results ──────────────┐
│                       │
│ 66126 NOPM 151047 TPM │
│                       │
└───────────────────────┘

hammerdb run completed
root@REDPOLL:/home/hammerdb/HammerDB-4.7#

hammerpost will also gather system level metrics that you can use to analyze your tests.

root@REDPOLL:/home/hammerdb/HammerDB-4.7# ./hammerp --test-details 9
+----------------------+----------------------+----------+----------------------+--------+-------+
|        START         |         END          | DURATION |      PARAMETERS      | OUTPUT | ERROR |
+----------------------+----------------------+----------+----------------------+--------+-------+
| 2023-04-04T15:11:38Z | 2023-04-04T15:14:34Z | 2m56s    | shared_buffers:512MB |        |       |
|                      |                      |          | wal_buffers:16MB     |        |       |
+----------------------+----------------------+----------+----------------------+--------+-------+
Test Details
root@REDPOLL:/home/hammerdb/HammerDB-4.7# ./hammerp --test-metrics 9
+-----------+--------------+-------------------------------+
| CPU USAGE | MEMORY USAGE |             TIME              |
+-----------+--------------+-------------------------------+
|     21.72 |        98.04 | 2023-04-04 15:13:27 +0000 UTC |
+-----------+--------------+-------------------------------+
|     95.36 |        98.35 | 2023-04-04 15:13:29 +0000 UTC |
+-----------+--------------+-------------------------------+
|     95.18 |        98.50 | 2023-04-04 15:13:31 +0000 UTC |
+-----------+--------------+-------------------------------+
|     98.75 |        98.57 | 2023-04-04 15:13:34 +0000 UTC |
+-----------+--------------+-------------------------------+
|    100.00 |        98.46 | 2023-04-04 15:13:36 +0000 UTC |
+-----------+--------------+-------------------------------+
|     97.96 |        98.50 | 2023-04-04 15:13:38 +0000 UTC |
+-----------+--------------+-------------------------------+
|     99.50 |        98.56 | 2023-04-04 15:13:40 +0000 UTC |
+-----------+--------------+-------------------------------+
|     98.99 |        98.35 | 2023-04-04 15:13:42 +0000 UTC |
+-----------+--------------+-------------------------------+
|    100.00 |        98.41 | 2023-04-04 15:13:45 +0000 UTC |
+-----------+--------------+-------------------------------+
|     94.72 |        98.49 | 2023-04-04 15:13:47 +0000 UTC |
+-----------+--------------+-------------------------------+
Test Metrics
root@REDPOLL:/home/hammerdb/HammerDB-4.7# ./hammerp --test-stats 9
+--------+--------+------------+----------+------------+----------+---------+
| AVGCPU | AVGMEM | AVGRPERSEC | AVGRMBPS | AVGWPERSEC | AVGWMBPS | AVGUTIL |
+--------+--------+------------+----------+------------+----------+---------+
|  94.69 |  98.40 |       0.00 |     0.00 |       0.00 |     0.00 |    0.00 |
+--------+--------+------------+----------+------------+----------+---------+

Hammerpost can be a great addition to your HammerDB benchmarking environment by enabling you to automate your tests to determine optimal parameter settings for your configuration.

HammerDB v4.7 New Features Pt 3: Lightweight Docker Images for rapid testing

HammerDB v4.5 introduced a Dockerfile and Docker Images to pull from Docker Hub for rapid deployment of HammerDB with builds already including third-party database libraries.

How to deploy HammerDB CLI fast with Docker

HammerDB v4.7 introduces more lightweight database specific Docker Images, so if you only want to run benchmarks against a specific database you can go from having no benchmarking environment to reviewing your results in as little as 3 commands.

In this example, we will use the PostgreSQL Docker image and have PostgreSQL running on our Linux system.  To install HammerDB for Docker we pull the PostgreSQL specific image.

1.

~/pgsql$ sudo docker pull tpcorg/hammerdb:postgres
[sudo] password for hammerdb: 
postgres: Pulling from tpcorg/hammerdb
06d39c85623a: Already exists 
a030842f98bc: Pull complete 
c5c513b4b24b: Pull complete 
6b7637531b01: Pull complete 
bdf997c08005: Pull complete 
f6cc176b4372: Pull complete 
bb48f6545dc6: Pull complete 
4f4fb700ef54: Pull complete 
Digest: sha256:59d901bfd14452ca3f345524384dd9d4b61effcc555e5133a71534440141c378
Status: Downloaded newer image for tpcorg/hammerdb:postgres
docker.io/tpcorg/hammerdb:postgres

and run it as follows:

2.

~/pgsql$ sudo docker run --network=host -it --name hammerdb-postgres tpcorg/hammerdb:postgres bash

As HammerDB has built in example scripts, if our database has default connection parameters, we can go straight ahead and run the PostgreSQL driver script. Alternatively, the following post explains how to update the scripts for your connection parameters. Note that if you are sure that you are using the default connection parameters, you can go ahead and run the script instead of the bash shell in step 2 as above.

HammerDB v4.7 New Features Pt 2: Example CLI Scripts

 

3.

/home/hammerdb/HammerDB-4.7# ./scripts/tcl/postgres/tprocc/pg_tprocc.sh

When the script has finished we can gather our results, having installed, started and run a benchmark in our HammerDB Docker image in 3 steps.

...
BUILD HAMMERDB SCHEMA
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
HammerDB CLI v4.7
...
HAMMERDB RESULT
HammerDB CLI v4.7
Copyright (C) 2003-2023 Steve Shaw
Type "help" for a list of commands
Initialized SQLite on-disk database /home/hammerdb/HammerDB-4.7/TMP/hammer.DB using existing tables (188,416 KB)
TRANSACTION RESPONSE TIMES
{
  "NEWORD": {
    "elapsed_ms": "436026.5",
    "calls": "33387",
    "min_ms": "0.505",
    "avg_ms": "7.431",
    "max_ms": "8131.221",
    "total_ms": "248091.653",
    "p99_ms": "31.727",
    "p95_ms": "2.933",
    "p50_ms": "1.676",
    "sd": "147681.729",
    "ratio_pct": "56.998"
  },
  "PAYMENT": {
    "elapsed_ms": "436026.5",
    "calls": "33439",
    "min_ms": "0.28",
    "avg_ms": "3.821",
    "max_ms": "9115.8",
    "total_ms": "127767.668",
    "p99_ms": "5.088",
    "p95_ms": "1.645",
    "p50_ms": "0.852",
    "sd": "115840.578",
    "ratio_pct": "29.354"
  },
  "DELIVERY": {
    "elapsed_ms": "436026.5",
    "calls": "3300",
    "min_ms": "1.129",
    "avg_ms": "8.367",
    "max_ms": "7738.33",
    "total_ms": "27612.602",
    "p99_ms": "39.382",
    "p95_ms": "6.893",
    "p50_ms": "3.635",
    "sd": "144034.336",
    "ratio_pct": "6.344"
  },
  "SLEV": {
    "elapsed_ms": "436026.5",
    "calls": "3296",
    "min_ms": "0.804",
    "avg_ms": "4.103",
    "max_ms": "1834.672",
    "total_ms": "13524.524",
    "p99_ms": "8.315",
    "p95_ms": "4.386",
    "p50_ms": "2.685",
    "sd": "39241.027",
    "ratio_pct": "3.107"
  },
  "OSTAT": {
    "elapsed_ms": "436026.5",
    "calls": "3378",
    "min_ms": "0.203",
    "avg_ms": "3.592",
    "max_ms": "2699.458",
    "total_ms": "12135.334",
    "p99_ms": "22.288",
    "p95_ms": "1.47",
    "p50_ms": "0.64",
    "sd": "65613.436",
    "ratio_pct": "2.788"
  }
}

TRANSACTION COUNT
{"PostgreSQL tpm": {
    "0": "2023-03-27 15:22:48",
    "65952": "2023-03-27 15:22:58",
    "72756": "2023-03-27 15:23:08",
    "50502": "2023-03-27 15:23:18",
    "41172": "2023-03-27 15:23:28",
    "55320": "2023-03-27 15:23:38",
    "72258": "2023-03-27 15:23:49",
    "68442": "2023-03-27 15:23:58",
    "61098": "2023-03-27 15:24:08",
    "52602": "2023-03-27 15:24:18",
    "57360": "2023-03-27 15:24:28",
    "56484": "2023-03-27 15:24:38",
    "74220": "2023-03-27 15:24:48",
    "55518": "2023-03-27 15:24:58",
    "69714": "2023-03-27 15:25:09",
    "63444": "2023-03-27 15:25:19",
    "62340": "2023-03-27 15:25:29",
    "57720": "2023-03-27 15:25:39",
    "40698": "2023-03-27 15:25:49",
    "40446": "2023-03-27 15:25:59",
    "39720": "2023-03-27 15:26:09",
    "31398": "2023-03-27 15:26:19",
    "38124": "2023-03-27 15:26:29",
    "36048": "2023-03-27 15:26:39",
    "29814": "2023-03-27 15:26:49",
    "34938": "2023-03-27 15:26:59",
    "13980": "2023-03-27 15:27:09",
    "23304": "2023-03-27 15:27:19",
    "11022": "2023-03-27 15:27:29",
    "47718": "2023-03-27 15:27:39",
    "38202": "2023-03-27 15:27:49",
    "36006": "2023-03-27 15:27:59",
    "11832": "2023-03-27 15:28:09",
    "29982": "2023-03-27 15:28:19",
    "37308": "2023-03-27 15:28:29",
    "36054": "2023-03-27 15:28:39",
    "21456": "2023-03-27 15:28:49",
    "15510": "2023-03-27 15:28:59",
    "36534": "2023-03-27 15:29:10",
    "34746": "2023-03-27 15:29:20",
    "28920": "2023-03-27 15:29:30",
    "8976": "2023-03-27 15:29:40",
    "29280": "2023-03-27 15:29:50",
    "38514": "2023-03-27 15:30:03",
    "24288": "2023-03-27 15:30:10",
  }}

HAMMERDB RESULT
[
  "6421B4C85F7E03E293532393",
  "2023-03-27 15:22:48",
  "4 Active Virtual Users configured",
  "TEST RESULT : System achieved 15824 NOPM from 36400 PostgreSQL TPM"
]

If preferred, to access utilities such as PostgreSQL metrics in this example, you can also run the HammerDB GUI and export the DISPLAY back to your host Desktop.

HammerDB v4.7 New Features Pt 2: Example CLI Scripts

With the HammerDB v4.5 Docker build, example CLI scripts were added to build and run the TPROC-C workload in the Tcl language. In HammerDB v4.6 these were enhanced to also add Python based scripts, and to include scripts for both TPROC-C and TPROC-H and a driver script for Linux environments. With HammerDB v4.7 these scripts have now moved into the main HammerDB directory to be included with all installations, rather than Docker only and a powershell driver script also added for Windows.  This post will give an overview of the example CLI scripts that you can run directly or use as a template to write your own.

When you list the HammerDB directory you can now see a directory called “scripts”. This directory contains CLI scripts to build, run, delete and query TPROC-C and TPROC-H workloads against all databases.

~/HammerDB-4.7$ ls -1
agent
bin
ChangeLog
CODE_OF_CONDUCT.md
config
hammerdb
hammerdbcli
hammerdbws
images
include
lib
LICENSE
modules
README.md
scripts
src

There is an option to run workloads in both Tcl and Python, note that these workloads underneath are identical, only the top-level interface is different and therefore there is no difference as to which language you use.

Linux example

For a first example we will look at the TPROC-C workload in Tcl running on MariaDB on Linux. In this directory you can see the generic build, run, delete and result query scripts as well as the Linux and Windows driver scripts.

~/HammerDB-4.7/scripts/tcl/maria/tprocc$ ls -1
maria_tprocc_buildschema.tcl
maria_tprocc_deleteschema.tcl
maria_tprocc.ps1
maria_tprocc_result.tcl
maria_tprocc_run.tcl
maria_tprocc.sh

in the driver script you can see that the TMP environment variable is set to the local directory and that the script is intended to be run from the main HammerDB directory and will call the build, run, delete and result scripts in turn.

export TMP=`pwd`/TMP
mkdir -p $TMP

echo "BUILD HAMMERDB SCHEMA"
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
./hammerdbcli auto ./scripts/tcl/maria/tprocc/maria_tprocc_buildschema.tcl
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
echo "RUN HAMMERDB TEST"
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
./hammerdbcli auto ./scripts/tcl/maria/tprocc/maria_tprocc_run.tcl
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
echo "DROP HAMMERDB SCHEMA"
./hammerdbcli auto ./scripts/tcl/maria/tprocc/maria_tprocc_deleteschema.tcl
echo "+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-"
echo "HAMMERDB RESULT"
./hammerdbcli auto ./scripts/tcl/maria/tprocc/maria_tprocc_result.tcl

First of all you should check the connection properties in your scripts and modify them to connect to your database.  Note that by default the build script will query the number of CPUs on the system that HammerDB is running and configure 5X this number for the number of warehouses to build.  If this value is greater or equal to 200 it will also partition the schema.  If HammerDB is running on a separate system from the database under test then you should modify this value accordingly.

puts "SETTING CONFIGURATION"
dbset db maria
dbset bm TPC-C

diset connection maria_host localhost
diset connection maria_port 3306
diset connection maria_socket /tmp/mariadb.sock

set vu [ numberOfCPUs ]
set warehouse [ expr {$vu * 5} ]
diset tpcc maria_count_ware $warehouse
diset tpcc maria_num_vu $vu
diset tpcc maria_user root
diset tpcc maria_pass maria
diset tpcc maria_dbase tpcc
diset tpcc maria_storage_engine innodb
if { $warehouse >= 200 } {
diset tpcc maria_partition true
} else {
diset tpcc maria_partition false
}
puts "SCHEMA BUILD STARTED"
buildschema
puts "SCHEMA BUILD COMPLETED"

For the run script, it will run a timed test with the use all warehouses setting enabled as well as the transaction counter and time profiling running. It will create the number of virtual users to the number of CPUs on the system where HammerDB is running.

set tmpdir $::env(TMP)
puts "SETTING CONFIGURATION"
dbset db maria
dbset bm TPC-C

diset connection maria_host localhost
diset connection maria_port 3306
diset connection maria_socket /tmp/mariadb.sock

diset tpcc maria_user root
diset tpcc maria_pass maria
diset tpcc maria_dbase tpcc
diset tpcc maria_driver timed
diset tpcc maria_rampup 2
diset tpcc maria_duration 5
diset tpcc maria_allwarehouse true
diset tpcc maria_timeprofile true

loadscript
puts "TEST STARTED"
vuset vu vcpu
vucreate
tcstart
tcstatus
set jobid [ vurun ]
vudestroy
tcstop
puts "TEST COMPLETE"
set of [ open $tmpdir/maria_tprocc w ]
puts $of $jobid
close $of

Once the workload has run, the delete script will delete the schema previously configured.

puts "SETTING CONFIGURATION"
dbset db maria
dbset bm TPC-C

diset connection maria_host localhost
diset connection maria_port 3306
diset connection maria_socket /tmp/mariadb.sock

diset tpcc maria_user root
diset tpcc maria_pass maria
diset tpcc maria_dbase tpcc
puts " DROP SCHEMA STARTED"
deleteschema
puts "DROP SCHEMA COMPLETED"

and finally the result script will report the results from the test.

set tmpdir $::env(TMP)
set ::outputfile $tmpdir/maria_tprocc
source ./scripts/tcl/generic/generic_tprocc_result.tcl

If we run the driver script, we see the following output (with build output truncated). Note that the NOPM/TPM result, transaction times, response times are reported at the end of the file.


$ ./scripts/tcl/maria/tprocc/maria_tprocc.sh
BUILD HAMMERDB SCHEMA
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
HammerDB CLI v4.7
Copyright (C) 2003-2023 Steve Shaw
Type "help" for a list of commands
Initialized SQLite on-disk database /home/steve/HammerDB-4.7/TMP/hammer.DB using existing tables (188,416 KB)
SETTING CONFIGURATION
Database set to MariaDB
Benchmark set to TPC-C for MariaDB
....
ALL VIRTUAL USERS COMPLETE
SCHEMA BUILD COMPLETED
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
RUN HAMMERDB TEST
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
HammerDB CLI v4.7
Copyright (C) 2003-2023 Steve Shaw
Type "help" for a list of commands
Initialized SQLite on-disk database /home/steve/HammerDB-4.7/TMP/hammer.DB using existing tables (180,224 KB)
SETTING CONFIGURATION
Database set to MariaDB
Benchmark set to TPC-C for MariaDB
Value localhost for connection:maria_host is the same as existing value localhost, no change made
Value 3306 for connection:maria_port is the same as existing value 3306, no change made
Value /tmp/mariadb.sock for connection:maria_socket is the same as existing value /tmp/mariadb.sock, no change made
Value root for tpcc:maria_user is the same as existing value root, no change made
Value maria for tpcc:maria_pass is the same as existing value maria, no change made
Value tpcc for tpcc:maria_dbase is the same as existing value tpcc, no change made
Value timed for tpcc:maria_driver is the same as existing value timed, no change made
Value 2 for tpcc:maria_rampup is the same as existing value 2, no change made
Value 5 for tpcc:maria_duration is the same as existing value 5, no change made
Changed tpcc:maria_allwarehouse from false to true for MariaDB
Changed tpcc:maria_timeprofile from false to true for MariaDB
Script loaded, Type "print script" to view
TEST STARTED
Vuser 1 created MONITOR - WAIT IDLE
Vuser 2 created - WAIT IDLE
Vuser 3 created - WAIT IDLE
Vuser 4 created - WAIT IDLE
Vuser 5 created - WAIT IDLE
5 Virtual Users Created with Monitor VU
Transaction Counter Started
Transaction Counter thread running with threadid:tid0x7f554ffff700
Vuser 1:RUNNING
Vuser 1:Initializing xtprof time profiler
Vuser 1:Ssl_cipher
Vuser 1:Beginning rampup time of 2 minutes
0 MariaDB tpm
Vuser 2:RUNNING
Vuser 2:Initializing xtprof time profiler
Vuser 2:Ssl_cipher
Vuser 2:VU 2 : Assigning WID=1 based on VU count 4, Warehouses = 20 (1 out of 5)
Vuser 2:VU 2 : Assigning WID=5 based on VU count 4, Warehouses = 20 (2 out of 5)
Vuser 2:VU 2 : Assigning WID=9 based on VU count 4, Warehouses = 20 (3 out of 5)
Vuser 2:VU 2 : Assigning WID=13 based on VU count 4, Warehouses = 20 (4 out of 5)
Vuser 2:VU 2 : Assigning WID=17 based on VU count 4, Warehouses = 20 (5 out of 5)
Vuser 2:Processing 10000000 transactions with output suppressed...
Vuser 3:RUNNING
Vuser 3:Initializing xtprof time profiler
Vuser 3:Ssl_cipher
Vuser 3:VU 3 : Assigning WID=2 based on VU count 4, Warehouses = 20 (1 out of 5)
Vuser 3:VU 3 : Assigning WID=6 based on VU count 4, Warehouses = 20 (2 out of 5)
Vuser 3:VU 3 : Assigning WID=10 based on VU count 4, Warehouses = 20 (3 out of 5)
Vuser 3:VU 3 : Assigning WID=14 based on VU count 4, Warehouses = 20 (4 out of 5)
Vuser 3:VU 3 : Assigning WID=18 based on VU count 4, Warehouses = 20 (5 out of 5)
Vuser 3:Processing 10000000 transactions with output suppressed...
Vuser 4:RUNNING
Vuser 4:Initializing xtprof time profiler
Vuser 4:Ssl_cipher
Vuser 4:VU 4 : Assigning WID=3 based on VU count 4, Warehouses = 20 (1 out of 5)
Vuser 4:VU 4 : Assigning WID=7 based on VU count 4, Warehouses = 20 (2 out of 5)
Vuser 4:VU 4 : Assigning WID=11 based on VU count 4, Warehouses = 20 (3 out of 5)
Vuser 4:VU 4 : Assigning WID=15 based on VU count 4, Warehouses = 20 (4 out of 5)
Vuser 4:VU 4 : Assigning WID=19 based on VU count 4, Warehouses = 20 (5 out of 5)
Vuser 4:Processing 10000000 transactions with output suppressed...
Vuser 5:RUNNING
Vuser 5:Initializing xtprof time profiler
Vuser 5:Ssl_cipher
Vuser 5:VU 5 : Assigning WID=4 based on VU count 4, Warehouses = 20 (1 out of 5)
Vuser 5:VU 5 : Assigning WID=8 based on VU count 4, Warehouses = 20 (2 out of 5)
Vuser 5:VU 5 : Assigning WID=12 based on VU count 4, Warehouses = 20 (3 out of 5)
Vuser 5:VU 5 : Assigning WID=16 based on VU count 4, Warehouses = 20 (4 out of 5)
Vuser 5:VU 5 : Assigning WID=20 based on VU count 4, Warehouses = 20 (5 out of 5)
Vuser 5:Processing 10000000 transactions with output suppressed...
77694 MariaDB tpm
90450 MariaDB tpm
81822 MariaDB tpm
79800 MariaDB tpm
82176 MariaDB tpm
Vuser 1:Rampup 1 minutes complete ...
81834 MariaDB tpm
81972 MariaDB tpm
81648 MariaDB tpm
81798 MariaDB tpm
81438 MariaDB tpm
80508 MariaDB tpm
Vuser 1:Rampup 2 minutes complete ...
Vuser 1:Rampup complete, Taking start Transaction Count.
Vuser 1:Timing test period of 5 in minutes
79194 MariaDB tpm
80826 MariaDB tpm
79920 MariaDB tpm
86010 MariaDB tpm
62040 MariaDB tpm
52104 MariaDB tpm
Vuser 1:1 ...,
56982 MariaDB tpm
62982 MariaDB tpm
62124 MariaDB tpm
66990 MariaDB tpm
65562 MariaDB tpm
58662 MariaDB tpm
Vuser 1:2 ...,
58428 MariaDB tpm
64380 MariaDB tpm
63972 MariaDB tpm
64146 MariaDB tpm
66588 MariaDB tpm
64188 MariaDB tpm
Vuser 1:3 ...,
65622 MariaDB tpm
66576 MariaDB tpm
64860 MariaDB tpm
68784 MariaDB tpm
70176 MariaDB tpm
69798 MariaDB tpm
Vuser 1:4 ...,
67494 MariaDB tpm
70884 MariaDB tpm
70260 MariaDB tpm
68388 MariaDB tpm
68922 MariaDB tpm
67584 MariaDB tpm
Vuser 1:5 ...,
Vuser 1:Test complete, Taking end Transaction Count.
Vuser 1:4 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 28639 NOPM from 66769 MariaDB TPM
Vuser 1:Gathering timing data from Active Virtual Users...
68418 MariaDB tpm
Vuser 2:FINISHED SUCCESS
Vuser 4:FINISHED SUCCESS
Vuser 5:FINISHED SUCCESS
Vuser 3:FINISHED SUCCESS
Vuser 1:Calculating timings...
Vuser 1:Writing timing data to /home/steve/HammerDB-4.7/TMP/hdbxtprofile.log
Vuser 1:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
vudestroy success
Transaction Counter thread running with threadid:tid0x7f554ffff700
Stopping Transaction Counter
TEST COMPLETE
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
DROP HAMMERDB SCHEMA
HammerDB CLI v4.7
Copyright (C) 2003-2023 Steve Shaw
Type "help" for a list of commands
Initialized SQLite on-disk database /home/steve/HammerDB-4.7/TMP/hammer.DB using existing tables (188,416 KB)
SETTING CONFIGURATION
Database set to MariaDB
Benchmark set to TPC-C for MariaDB
Value localhost for connection:maria_host is the same as existing value localhost, no change made
Value 3306 for connection:maria_port is the same as existing value 3306, no change made
Value /tmp/mariadb.sock for connection:maria_socket is the same as existing value /tmp/mariadb.sock, no change made
Value root for tpcc:maria_user is the same as existing value root, no change made
Value maria for tpcc:maria_pass is the same as existing value maria, no change made
Value tpcc for tpcc:maria_dbase is the same as existing value tpcc, no change made
DROP SCHEMA STARTED
Script cleared
Deleting schema with 1 Virtual User
Do you want to delete the TPCC TPROC-C schema
in host LOCALHOST:/TMP/MARIADB.SOCK under user ROOT?
Enter yes or no: replied yes
Vuser 1 created - WAIT IDLE
Vuser 1:RUNNING
Vuser 1:Ssl_cipher
DROP SCHEMA COMPLETED
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
HAMMERDB RESULT
HammerDB CLI v4.7
Copyright (C) 2003-2023 Steve Shaw
Type "help" for a list of commands
Initialized SQLite on-disk database /home/steve/HammerDB-4.7/TMP/hammer.DB using existing tables (188,416 KB)
TRANSACTION RESPONSE TIMES
{
"NEWORD": {
"elapsed_ms": "419814.0",
"calls": "54189",
"min_ms": "1.35",
"avg_ms": "4.197",
"max_ms": "191.359",
"total_ms": "227425.383",
"p99_ms": "14.25",
"p95_ms": "6.883",
"p50_ms": "3.788",
"sd": "3363.096",
"ratio_pct": "54.162"
},
"PAYMENT": {
"elapsed_ms": "419814.0",
"calls": "54597",
"min_ms": "0.655",
"avg_ms": "1.647",
"max_ms": "121.584",
"total_ms": "89933.834",
"p99_ms": "6.288",
"p95_ms": "2.984",
"p50_ms": "1.424",
"sd": "1521.734",
"ratio_pct": "21.418"
},
"DELIVERY": {
"elapsed_ms": "419814.0",
"calls": "5389",
"min_ms": "4.782",
"avg_ms": "13.291",
"max_ms": "225.309",
"total_ms": "71627.506",
"p99_ms": "91.928",
"p95_ms": "27.917",
"p50_ms": "9.796",
"sd": "14775.748",
"ratio_pct": "17.058"
},
"OSTAT": {
"elapsed_ms": "419814.0",
"calls": "5386",
"min_ms": "0.488",
"avg_ms": "2.335",
"max_ms": "139.762",
"total_ms": "12574.798",
"p99_ms": "40.093",
"p95_ms": "4.248",
"p50_ms": "1.153",
"sd": "7285.123",
"ratio_pct": "2.995"
},
"SLEV": {
"elapsed_ms": "419814.0",
"calls": "5367",
"min_ms": "0.904",
"avg_ms": "1.949",
"max_ms": "93.56",
"total_ms": "10459.164",
"p99_ms": "6.379",
"p95_ms": "3.1",
"p50_ms": "1.698",
"sd": "1775.819",
"ratio_pct": "2.491"
}
}

TRANSACTION COUNT
{"MariaDB tpm": {
"0": "2023-03-24 14:35:24",
"77694": "2023-03-24 14:35:34",
"90450": "2023-03-24 14:35:44",
"81822": "2023-03-24 14:35:54",
"79800": "2023-03-24 14:36:04",
"82176": "2023-03-24 14:36:14",
"81834": "2023-03-24 14:36:24",
"81972": "2023-03-24 14:36:34",
"81648": "2023-03-24 14:36:44",
"81798": "2023-03-24 14:36:54",
"81438": "2023-03-24 14:37:04",
"80508": "2023-03-24 14:37:14",
"79194": "2023-03-24 14:37:24",
"80826": "2023-03-24 14:37:34",
"79920": "2023-03-24 14:37:44",
"86010": "2023-03-24 14:37:54",
"62040": "2023-03-24 14:38:04",
"52104": "2023-03-24 14:38:14",
"56982": "2023-03-24 14:38:24",
"62982": "2023-03-24 14:38:34",
"62124": "2023-03-24 14:38:44",
"66990": "2023-03-24 14:38:54",
"65562": "2023-03-24 14:39:04",
"58662": "2023-03-24 14:39:14",
"58428": "2023-03-24 14:39:24",
"64380": "2023-03-24 14:39:34",
"63972": "2023-03-24 14:39:44",
"64146": "2023-03-24 14:39:54",
"66588": "2023-03-24 14:40:04",
"64188": "2023-03-24 14:40:14",
"65622": "2023-03-24 14:40:24",
"66576": "2023-03-24 14:40:34",
"64860": "2023-03-24 14:40:44",
"68784": "2023-03-24 14:40:54",
"70176": "2023-03-24 14:41:04",
"69798": "2023-03-24 14:41:14",
"67494": "2023-03-24 14:41:24",
"70884": "2023-03-24 14:41:34",
"70260": "2023-03-24 14:41:44",
"68388": "2023-03-24 14:41:54",
"68922": "2023-03-24 14:42:04",
"67584": "2023-03-24 14:42:14",
"68418": "2023-03-24 14:42:24"
}}

HAMMERDB RESULT
[
"641DB52C5F7A03E213335373",
"2023-03-24 14:35:24",
"4 Active Virtual Users configured",
"TEST RESULT : System achieved 28639 NOPM from 66769 MariaDB TPM"
]

The output is also stored in the TMP directory in a file identifed by the jobid.

~/HammerDB-4.7/TMP$ more maria_tprocc_641DB52C5F7A03E213335373.out
TRANSACTION RESPONSE TIMES
{
"NEWORD": {
"elapsed_ms": "419814.0",
"calls": "54189",
"min_ms": "1.35",
"avg_ms": "4.197",
"max_ms": "191.359",
"total_ms": "227425.383",
"p99_ms": "14.25",
"p95_ms": "6.883",
"p50_ms": "3.788",
"sd": "3363.096",
"ratio_pct": "54.162"
},
"PAYMENT": {
"elapsed_ms": "419814.0",
"calls": "54597",
"min_ms": "0.655",
"avg_ms": "1.647",
"max_ms": "121.584",
"total_ms": "89933.834",
"p99_ms": "6.288",
"p95_ms": "2.984",
"p50_ms": "1.424",
"sd": "1521.734",
"ratio_pct": "21.418"
},
"DELIVERY": {
"elapsed_ms": "419814.0",
"calls": "5389",
"min_ms": "4.782",
"avg_ms": "13.291",
"max_ms": "225.309",
"total_ms": "71627.506",
"p99_ms": "91.928",
"p95_ms": "27.917",
"p50_ms": "9.796",
"sd": "14775.748",
"ratio_pct": "17.058"
},
"OSTAT": {
"elapsed_ms": "419814.0",
"calls": "5386",
"min_ms": "0.488",
"avg_ms": "2.335",
"max_ms": "139.762",
"total_ms": "12574.798",
"p99_ms": "40.093",
"p95_ms": "4.248",
"p50_ms": "1.153",
"sd": "7285.123",
"ratio_pct": "2.995"
},
"SLEV": {
"elapsed_ms": "419814.0",
"calls": "5367",
"min_ms": "0.904",
"avg_ms": "1.949",
"max_ms": "93.56",
"total_ms": "10459.164",
"p99_ms": "6.379",
"p95_ms": "3.1",
"p50_ms": "1.698",
"sd": "1775.819",
"ratio_pct": "2.491"
}
}

TRANSACTION COUNT
{"MariaDB tpm": {
"0": "2023-03-24 14:35:24",
"77694": "2023-03-24 14:35:34",
"90450": "2023-03-24 14:35:44",
"81822": "2023-03-24 14:35:54",
"79800": "2023-03-24 14:36:04",
"82176": "2023-03-24 14:36:14",
"81834": "2023-03-24 14:36:24",
"81972": "2023-03-24 14:36:34",
"81648": "2023-03-24 14:36:44",
"81798": "2023-03-24 14:36:54",
"81438": "2023-03-24 14:37:04",
"80508": "2023-03-24 14:37:14",
"79194": "2023-03-24 14:37:24",
"80826": "2023-03-24 14:37:34",
"79920": "2023-03-24 14:37:44",
"86010": "2023-03-24 14:37:54",
"62040": "2023-03-24 14:38:04",
"52104": "2023-03-24 14:38:14",
"56982": "2023-03-24 14:38:24",
"62982": "2023-03-24 14:38:34",
"62124": "2023-03-24 14:38:44",
"66990": "2023-03-24 14:38:54",
"65562": "2023-03-24 14:39:04",
"58662": "2023-03-24 14:39:14",
"58428": "2023-03-24 14:39:24",
"64380": "2023-03-24 14:39:34",
"63972": "2023-03-24 14:39:44",
"64146": "2023-03-24 14:39:54",
"66588": "2023-03-24 14:40:04",
"64188": "2023-03-24 14:40:14",
"65622": "2023-03-24 14:40:24",
"66576": "2023-03-24 14:40:34",
"64860": "2023-03-24 14:40:44",
"68784": "2023-03-24 14:40:54",
"70176": "2023-03-24 14:41:04",
"69798": "2023-03-24 14:41:14",
"67494": "2023-03-24 14:41:24",
"70884": "2023-03-24 14:41:34",
"70260": "2023-03-24 14:41:44",
"68388": "2023-03-24 14:41:54",
"68922": "2023-03-24 14:42:04",
"67584": "2023-03-24 14:42:14",
"68418": "2023-03-24 14:42:24"
}}

HAMMERDB RESULT
[
"641DB52C5F7A03E213335373",
"2023-03-24 14:35:24",
"4 Active Virtual Users configured",
"TEST RESULT : System achieved 28639 NOPM from 66769 MariaDB TPM"
]

Note that the jobs are stored in the SQLite database in the configured TMP directory and therefore this can be queried with the jobs command to view all of the data stored for the job such as the dict and the response times for all running virtual users.

$ export TMP=`pwd`/TMP
$ ./hammerdbcli
HammerDB CLI v4.7
Copyright (C) 2003-2023 Steve Shaw
Type "help" for a list of commands
Initialized SQLite on-disk database /home/steve/HammerDB-4.7/TMP/hammer.DB using existing tables (188,416 KB)

hammerdb>jobs result
[
"641DB40C5F7A03E273636303",
"Jobid has no test result"
]

[
"641DB52C5F7A03E213335373",
"2023-03-24 14:35:24",
"4 Active Virtual Users configured",
"TEST RESULT : System achieved 28639 NOPM from 66769 MariaDB TPM"
]

Windows example

For a Windows example we will look at the Python scripts for TPROC-H running against SQL Server.

Similarly to Linux, set the connection settings in all scripts.  To edit the scripts within the Program Files directory run an editor such as Notepad as administrator.

#!/bin/tclsh
# maintainer: Pooja Jain

print("SETTING CONFIGURATION")
dbset('db','mssqls')
dbset('bm','TPC-H')

diset('connection','mssqls_tcp','false')
diset('connection','mssqls_port','1433')
diset('connection','mssqls_azure','false')
diset('connection','mssqls_encrypt_connection','true')
diset('connection','mssqls_trust_server_cert','true')
diset('connection','mssqls_authentication','windows')
diset('connection','mssqls_server','{(local)\SQLDEVELOP}')
diset('connection','mssqls_linux_server','{localhost}')
diset('connection','mssqls_linux_authent','sql')
diset('connection','mssqls_linux_odbc','{ODBC Driver 18 for SQL Server}')
diset('connection','mssqls_uid','sa')
diset('connection','mssqls_pass','admin')

vu = tclpy.eval('numberOfCPUs')
diset('tpch','mssqls_num_tpch_threads',vu)
diset('tpch','mssqls_scale_fact','1')
diset('tpch','mssqls_maxdop','2')
diset('tpch','mssqls_tpch_dbase','tpch')
diset('tpch','mssqls_colstore','false')

print("SCHEMA BUILD STARTED")
buildschema()
print("SCHEMA BUILD COMPLETED")
exit()

Note that by default running powershell scripts is disabled and therefore to run the HammerDB scripts on Windows this functionality should be enabled as follows.

To run the workload, call the powershell script from the HammerDB home directory.

C:\Program Files\HammerDB-4.7>powershell .\scripts\python\mssqls\tproch\mssqls_tproch_py.ps1

As on Linux, the workload will build, run, delete and report the results of the workload.

HAMMERDB RESULT
HammerDB CLI v4.7
Copyright (C) 2003-2023 Steve Shaw
Type "help()" for a list of commands
HAMMERDB RESULT
[
  "1",
  "Executing Query 14 (1 of 22)",
  "1",
  "query 14 completed in 0.136 seconds",
  "1",
  "Executing Query 2 (2 of 22)",
  "1",
  "query 2 completed in 0.229 seconds",
  "1",
  "Executing Query 9 (3 of 22)",
  "1",
  "query 9 completed in 1.206 seconds",
  "1",
  "Executing Query 20 (4 of 22)",
  "1",
  "query 20 completed in 0.195 seconds",
  "1",
  "Executing Query 6 (5 of 22)",
  "1",
  "query 6 completed in 0.283 seconds",
  "1",
  "Executing Query 17 (6 of 22)",
  "1",
  "query 17 completed in 0.191 seconds",
  "1",
  "Executing Query 18 (7 of 22)",
  "1",
  "query 18 completed in 0.601 seconds",
  "1",
  "Executing Query 8 (8 of 22)",
  "1",
  "query 8 completed in 0.693 seconds",
  "1",
  "Executing Query 21 (9 of 22)",
  "1",
  "query 21 completed in 1.045 seconds",
  "1",
  "Executing Query 13 (10 of 22)",
  "1",
  "query 13 completed in 2.973 seconds",
  "1",
  "Executing Query 3 (11 of 22)",
  "1",
  "query 3 completed in 0.279 seconds",
  "1",
  "Executing Query 22 (12 of 22)",
  "1",
  "query 22 completed in 0.276 seconds",
  "1",
  "Executing Query 16 (13 of 22)",
  "1",
  "query 16 completed in 0.383 seconds",
  "1",
  "Executing Query 4 (14 of 22)",
  "1",
  "query 4 completed in 0.33 seconds",
  "1",
  "Executing Query 11 (15 of 22)",
  "1",
  "query 11 completed in 0.094 seconds",
  "1",
  "Executing Query 15 (16 of 22)",
  "1",
  "query 15 completed in 0.18 seconds",
  "1",
  "Executing Query 1 (17 of 22)",
  "1",
  "query 1 completed in 0.997 seconds",
  "1",
  "Executing Query 10 (18 of 22)",
  "1",
  "query 10 completed in 0.356 seconds",
  "1",
  "Executing Query 19 (19 of 22)",
  "1",
  "query 19 completed in 0.516 seconds",
  "1",
  "Executing Query 5 (20 of 22)",
  "1",
  "query 5 completed in 0.408 seconds",
  "1",
  "Executing Query 7 (21 of 22)",
  "1",
  "query 7 completed in 0.263 seconds",
  "1",
  "Executing Query 12 (22 of 22)",
  "1",
  "query 12 completed in 0.403 seconds",
  "1",
  "Completed 1 query set(s) in 12 seconds",
  "1",
  "Geometric mean of query times returning rows (22) is \"0.38278\""
]

The temp directory used is the same default temp directory on Windows and therefore the output file can be found in this location.

Summary

The example scripts and driver scripts are intended as templates to show what you can do with your own HammerDB environment to build, run and delete workloads and query the results and configuration.

HammerDB v4.7 New Features Pt1: Transaction Counter and CPU Metrics

In HammerDB v4.7 the Transaction Counter and CPU Metrics have been updated on both Windows and Linux to use a package called tkpath enabling more advanced graphic features using the GPU where available.  This gives the transaction counter,

and CPU metrics,

a more updated look and feel, whilst maintaining the previous lightweight impact of the graphical code.

On Linux HammerDB GUI requires Cairo Graphics installed to support this functionality. On all Linux systems tested Cairo Graphics was already installed, however if not it can be installed as follows:

Ubuntu:

sudo apt-get install libcairo2-dev

Red Hat:

The transaction counter can be dragged out from the tab in the notebook as shown previously to display in a separate window, or left in the main window as shown below:

The embedded counter will now also resize when the main window is resized, so when doing demos and filling the screen with HammerDB, the transaction counter will now fill the available space.

The previous ribbon type effect has been deprecated for a more updated look. However, if there is a desire to maintain the previous graph, it can be enabled in the XML configuration file before HammerDB is started by setting the value tc_graph_ribbon to true from the default of false.

<transaction_counter> 
<settings> 
<tc_refresh_rate>10</tc_refresh_rate> 
<tc_log_to_temp>0</tc_log_to_temp> 
<tc_unique_log_name>0</tc_unique_log_name> 
<tc_log_timestamps>0</tc_log_timestamps> 
<tc_graph_ribbon>true</tc_graph_ribbon> 
</settings> 
</transaction_counter>

Note that the XML must be set before the first start, as after this point the parameters are stored in the SQLite files and therefore it will be necessary to remove/refresh the SQLite parameter files to pick up any subsequent changes. When this is done, the previous ribbon effect is shown, although updated to use tkpath features such as antialiasing.

Configuring unixODBC and SQL Server Drivers for HammerDB on Linux

 

In this post, we will do a walk through example of installing and configuring unixODBC and the SQL Server on Linux drivers as well as the HammerDB connection options to enable HammerDB on Linux to be able to connect to SQL Server. In the example, we have SQL Server running on Windows and are using a virtualized Linux instance to run HammerDB to connect to SQL Server.

unixODBC

Firstly get unixODBC from here http://www.unixodbc.org/ and configure making sure the that the –enable-fastvalidate option is given. In this example, we install in the /usr/local directory using the command make and make install after configure.

./configure --prefix=/usr/local/unixODBC --enable-gui=no --enable-drivers=no --enable-iconv --with-iconv-char-enc=UTF8 --with-iconv-ucode-enc=UTF16LE --enable-threads=yes --enable-fastvalidate

Microsoft ODBC Drivers

Then install the Microsoft ODBC Drivers using the instructions here.  Once installed, we will see the drivers successfully installed and find the dependent libraries we need.

root@REDPOLL:/opt/microsoft# ls
msodbcsql17 msodbcsql18
root@REDPOLL:/opt/microsoft# ls msodbcsql18/lib64/
libmsodbcsql-18.1.so.2.1

Add the drivers to /usr/local/odbcinst.ini either using the obcinst command or through manual editing of odbcinst.ini.

root@REDPOLL:/usr/local/unixODBC/etc# more odbcinst.ini 
[ODBC Driver 17 for SQL Server]
Description=Microsoft ODBC Driver 17 for SQL Server
Driver=/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.10.so.2.1
UsageCount=1
[ODBC Driver 18 for SQL Server]
Description=Microsoft ODBC Driver 18 for SQL Server
Driver=/opt/microsoft/msodbcsql18/lib64/libmsodbcsql-18.1.so.2.1
UsageCount=1

We can verify the configuration and drivers as follows:

root@REDPOLL:/usr/local/unixODBC# ./bin/odbcinst -j
unixODBC 2.3.11
DRIVERS............: /usr/local/unixODBC/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/unixODBC/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/unixODBC/etc/ODBCDataSources
USER DATA SOURCES..: /usr/local/unixODBC/etc/odbc.ini
SQLULEN Size.......: 8
SQLLEN Size........: 8
SQLSETPOSIROW Size.: 8

root@REDPOLL:/usr/local/unixODBC# ./bin/odbcinst -d -q
[ODBC Driver 17 for SQL Server]
[ODBC Driver 18 for SQL Server]

As our hammerdb user we can then set our .bash_profile and ensure that the ODBC drivers are found.

hammerdb@REDPOLL:~$ more .bash_profile 
export PATH="$PATH:/opt/mssql-tools/bin"
export PATH=:/usr/local/unixODBC/bin:$PATH
export LD_LIBRARY_PATH=/usr/local/unixODBC/lib:$LD_LIBRARY_PATH
export ODBCSYSINI=/usr/local/unixODBC/etc
export ODBCINI=/usr/local/unixODBC/etc/odbc.ini

hammerdb@REDPOLL:~$ odbcinst -d -q
[ODBC Driver 17 for SQL Server]
[ODBC Driver 18 for SQL Server]

Test Connectivity

In this example, as we are running HammerDB for Linux in a VM to connect back to the Windows host we need to either configure or temporarily disable Defender Firewall to allow HammerDB to connect to SQL Server.

And also enable remote connections to SQL Server from SSMS.

We also need to use SQL Server Authentication, and set a password for the sa user.

And configure HammerDB to connect from Linux to Windows, and therefore we set the TCP check option as well as using the SQL Server authentication with the user and password we set previously.

Build Schema

We can now run HammerDB to verify that we can connect to SQL Server from our Linux client.

Run Test

We the build is complete, we can begin running a test from our SQL Server on Linux client.

And confirm that our Linux host has connected and is running the workload on SQL Server.

Summary

In this post, we have shown how to configure the ODBC Drivers for SQL Server on Linux for HammerDB. When the test complete, do not forget to re-enable Windows Firewall if you disabled it.

How to deploy HammerDB CLI fast with Docker

 

The TPC publishes an official Docker image on Docker Hub to enable the rapid deployment and testing of databases with HammerDB.  This image includes example scripts to build schemas and test your databases with a single command.

In this post, we will show an example of using Docker to deploy the HammerDB command line and test a database with minimal effort. Our Linux test system is running a MariaDB 10.10 database with Docker installed, so we are going to use Docker to pull the HammerDB image and run the test scripts to measure the database performance.

Docker Hub

The image can be found on Docker Hub under the tpcorg account.

Docker Pull

On a system with docker installed, use docker pull tpcorg/hammerdb to pull the latest image.

hammerdb@REDPOLL:~$ sudo docker pull tpcorg/hammerdb
[sudo] password for hammerdb: 
Using default tag: latest
latest: Pulling from tpcorg/hammerdb
846c0b181fff: Pull complete 
10b4eb41ac75: Pull complete 
9021d634457d: Pull complete 
b365f60d978b: Pull complete 
215f30832a93: Pull complete 
689ecb6b15e8: Pull complete 
d6464f987fdc: Pull complete 
08589f44a894: Pull complete 
a3fcc3045ce7: Pull complete 
f8ceb6609f8d: Pull complete 
48e559ac800e: Pull complete 
4f4fb700ef54: Pull complete 
f9b42217af04: Pull complete 
Digest: sha256:69bf8b29a19855bb63c7e5266afeedbbb99540cef2a96c6b6a33e6747b3aa38b
Status: Downloaded newer image for tpcorg/hammerdb:latest
docker.io/tpcorg/hammerdb:latest

Use docker run to run the HammerDB container.

hammerdb@REDPOLL:~$ sudo docker run --network=host -it --name hammerdb tpcorg/hammerdb bash
root@REDPOLL:/home/hammerdb/HammerDB-4.6#

Note that all libraries are pre-installed, so we are ready to start running HammerDB.

root@REDPOLL:/home/hammerdb/HammerDB-4.6# ./hammerdbcli 
HammerDB CLI v4.6
Copyright (C) 2003-2022 Steve Shaw
Type "help" for a list of commands
Initialized new SQLite on-disk database /tmp/hammer.DB
hammerdb>librarycheck
Checking database library for Oracle
Success ... loaded library Oratcl for Oracle
Checking database library for MSSQLServer
Success ... loaded library tdbc::odbc for MSSQLServer
Checking database library for Db2
Error: failed to load db2tcl - couldn't load file "/home/hammerdb/HammerDB-4.6/lib/db2tcl2.0.1/libdb2tcl.so": libdb2.so.1: cannot open shared object file: No such file or directory
Ensure that Db2 client libraries are installed and the location in the LD_LIBRARY_PATH environment variable
Checking database library for MySQL
Success ... loaded library mysqltcl for MySQL
Checking database library for PostgreSQL
Success ... loaded library Pgtcl for PostgreSQL
Checking database library for MariaDB
Success ... loaded library mariatcl for MariaDB

Database Configuration

We have run Docker to enable to access the host network so we start the MariaDB on the host with the host bind_address (in this case the system name is redpoll) and the default port of 3306 and start the database.

[mysqld]
skip-log-bin
datadir=/home/hammerdb/mariadb-10.10.2-linux-systemd-x86_64/data
default_authentication_plugin=mysql_native_password
socket=/tmp/mariadb.sock
port=3306
log-error=/tmp/mariadb.log
bind_address=redpoll
...

hammerdb@REDPOLL:~/mariadb-10.10.2-linux-systemd-x86_64$ bin/mysqld --defaults-file=./my.cnf --user=hammerdb
2022-12-14 12:14:05 0 [Note] bin/mysqld (server 10.10.2-MariaDB) starting as process 4483 ...

We can check on the host the IP address of the Docker container,  in this case 172.17.0.1 and allow access to HammerDB from the container as the root user.

root@REDPOLL:/home/hammerdb/mariadb-10.10.2-linux-systemd-x86_64# ip addr show docker0
3: docker0: <NO-CARRIER,BROADCAST,MULTICAST,UP> mtu 1500 qdisc noqueue state DOWN group default 
    link/ether 02:42:a7:d9:56:a9 brd ff:ff:ff:ff:ff:ff
    inet 172.17.0.1/16 brd 172.17.255.255 scope global docker0
       valid_lft forever preferred_lft forever

MariaDB [(none)]> select password('maria');
+-------------------------------------------+
| password('maria')                         |
+-------------------------------------------+
| *8061C323A725701555411A7E18421F077A840CD7 |
+-------------------------------------------+
1 row in set (0.000 sec)

MariaDB [(none)]> create user 'root'@'172.17.0.1' identified by password '*8061C323A725701555411A7E18421F077A840CD7';
Query OK, 0 rows affected (0.002 sec)
MariaDB [(none)]>

Script Configuration

Under the HammerDB home directory is a scripts directory containing example scripts to test all supported databases except for Db2 in both tcl and python format for both TPROC-C and TPROC-H workloads. There is a driver script that can be used to build, test, delete and query the results of a workload with a single script. The scripts are available in both Python and Tcl formats.

root@REDPOLL:/home/hammerdb/HammerDB-4.6# ls -ltR scripts/
scripts/:
total 8
drwxr-xr-x 8 root root 4096 Dec 13 22:31 python
drwxr-xr-x 8 root root 4096 Dec 13 22:31 tcl
scripts/python:
total 24
drwxr-xr-x 2 root root 4096 Dec 13 22:31 generic
drwxr-xr-x 4 root root 4096 Dec 13 22:31 maria
drwxr-xr-x 4 root root 4096 Dec 13 22:31 mssqls
drwxr-xr-x 4 root root 4096 Dec 13 22:31 mysql
drwxr-xr-x 4 root root 4096 Dec 13 22:31 oracle
drwxr-xr-x 4 root root 4096 Dec 13 22:31 postgres

root@REDPOLL:/home/hammerdb/HammerDB-4.6/scripts/tcl/maria/tprocc# ls
maria_tprocc.sh 
maria_tprocc_deleteschema.tcl  
maria_tprocc_run.tcl
maria_tprocc_buildschema.tcl  
maria_tprocc_result.tcl

Update the scripts to be used with the connection settings for the database to be tested. In this case, the MariaDB database is running on port 3306 on host redpoll.  The socket will not be used as the hostname specified is not “localhost”. The connection parameters for all scripts is all we need to change.

!/bin/tclsh
# maintainer: Pooja Jain
puts "SETTING CONFIGURATION"
dbset db maria
dbset bm TPC-C
diset connection maria_host redpoll
diset connection maria_port 3306
diset connection maria_socket /tmp/mariadb.sock
...

By default the schema will build 5 x the number of warehouses of the CPUs on the system.

set vu [ numberOfCPUs ]
set warehouse [ expr {$vu * 5} ]
diset tpcc maria_count_ware $warehouse

and the test will run with the number of Virtual Users equivalent to the number of CPUs.

Run the test

We can now run the main driver script from the HammerDB home directory to build the schema, run the test, delete the schema and then print the results of the test as follows:

root@REDPOLL:/home/hammerdb/HammerDB-4.6# ./scripts/tcl/maria/tprocc/maria_tprocc.sh 
BUILD HAMMERDB SCHEMA 
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- 
HammerDB CLI v4.6 
Copyright (C) 2003-2022 Steve Shaw 
Type "help" for a list of commands 
Initialized SQLite on-disk database /home/hammerdb/HammerDB-4.6/TMP/hammer.DB using existing tables (229,376 KB) 
SETTING CONFIGURATION 
Database set to MariaDB 
Benchmark set to TPC-C for MariaDB 
Value redpoll for connection:maria_host is the same as existing value redpoll, no change made 
Value 3306 for connection:maria_port is the same as existing value 3306, no change made 
Value /tmp/mariadb.sock for connection:maria_socket is the same as existing value /tmp/mariadb.sock, no change made 
Value 20 for tpcc:maria_count_ware is the same as existing value 20, no change made 
Value 4 for tpcc:maria_num_vu is the same as existing value 4, no change made 
Value root for tpcc:maria_user is the same as existing value root, no change made 
Value maria for tpcc:maria_pass is the same as existing value maria, no change made 
Value tpcc for tpcc:maria_dbase is the same as existing value tpcc, no change made 
Value innodb for tpcc:maria_storage_engine is the same as existing value innodb, no change made 
Value false for tpcc:maria_partition is the same as existing value false, no change made 
SCHEMA BUILD STARTED 
Script cleared 
Building 20 Warehouses with 5 Virtual Users, 4 active + 1 Monitor VU(dict value maria_num_vu is set to 4) 
Ready to create a 20 Warehouse MariaDB TPROC-C schema 
in host REDPOLL:3306 under user ROOT in database TPCC with storage engine INNODB? 
Enter yes or no: replied yes 
Vuser 1 created - WAIT IDLE 
Vuser 2 created - WAIT IDLE 
Vuser 3 created - WAIT IDLE 
Vuser 4 created - WAIT IDLE 
Vuser 5 created - WAIT IDLE 
Vuser 1:RUNNING 
Vuser 1:Monitor Thread 
Vuser 1:CREATING TPCC SCHEMA 
Vuser 1:Ssl_cipher  
Vuser 1:CREATING DATABASE tpcc 
Vuser 1:CREATING TPCC TABLES 
Vuser 1:Loading Item 
Vuser 2:RUNNING 
Vuser 2:Worker Thread 
Vuser 2:Waiting for Monitor Thread... 
Vuser 2:Ssl_cipher  
Vuser 2:Loading 5 Warehouses start:1 end:5 
Vuser 2:Start:Wed Dec 14 13:47:59 +0000 2022 
Vuser 2:Loading Warehouse 
Vuser 2:Loading Stock Wid=1 
Vuser 3:RUNNING 
Vuser 3:Worker Thread 
Vuser 3:Waiting for Monitor Thread... 
Vuser 3:Ssl_cipher  
Vuser 3:Loading 5 Warehouses start:6 end:10 
Vuser 5:Loading Warehouse 
Vuser 5:Loading Stock Wid=16 
...
Vuser 2:FINISHED SUCCESS 
Vuser 3:...2000 
Vuser 3:...3000 
Vuser 3:Orders Done 
Vuser 3:End:Wed Dec 14 13:51:05 +0000 2022 
Vuser 3:FINISHED SUCCESS 
Vuser 1:Workers: 0 Active 4 Done 
Vuser 1:CREATING TPCC STORED PROCEDURES 
Vuser 1:GATHERING SCHEMA STATISTICS 
Vuser 1:TPCC SCHEMA COMPLETE 
Vuser 1:FINISHED SUCCESS 
ALL VIRTUAL USERS COMPLETE 
SCHEMA BUILD COMPLETED 
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- 
RUN HAMMERDB TEST 
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- 
HammerDB CLI v4.6 
Copyright (C) 2003-2022 Steve Shaw 
Type "help" for a list of commands 
Initialized SQLite on-disk database /home/hammerdb/HammerDB-4.6/TMP/hammer.DB using existing tables (372,736 KB) 
SETTING CONFIGURATION 
Database set to MariaDB 
Benchmark set to TPC-C for MariaDB 
Value redpoll for connection:maria_host is the same as existing value redpoll, no change made 
Value 3306 for connection:maria_port is the same as existing value 3306, no change made 
Value /tmp/mariadb.sock for connection:maria_socket is the same as existing value /tmp/mariadb.sock, no change made 
Value root for tpcc:maria_user is the same as existing value root, no change made 
Value maria for tpcc:maria_pass is the same as existing value maria, no change made 
Value tpcc for tpcc:maria_dbase is the same as existing value tpcc, no change made 
Value timed for tpcc:maria_driver is the same as existing value timed, no change made 
Value 2 for tpcc:maria_rampup is the same as existing value 2, no change made 
Value 5 for tpcc:maria_duration is the same as existing value 5, no change made 
Value true for tpcc:maria_allwarehouse is the same as existing value true, no change made 
Value true for tpcc:maria_timeprofile is the same as existing value true, no change made 
Script loaded, Type "print script" to view 
TEST STARTED 
Vuser 1 created MONITOR - WAIT IDLE 
Vuser 2 created - WAIT IDLE 
Vuser 3 created - WAIT IDLE 
Vuser 4 created - WAIT IDLE 
Vuser 5 created - WAIT IDLE 
5 Virtual Users Created with Monitor VU 
Transaction Counter Started 
Transaction Counter thread running with threadid:tid0x7fec51ffb700 
0 MariaDB tpm 
Vuser 1:RUNNING 
Vuser 1:Initializing xtprof time profiler 
Vuser 1:Ssl_cipher  
Vuser 1:Beginning rampup time of 2 minutes 
Vuser 2:RUNNING 
Vuser 2:Initializing xtprof time profiler 
Vuser 3:RUNNING 
Vuser 2:Ssl_cipher  
Vuser 3:Initializing xtprof time profiler 
Vuser 2:VU 2 : Assigning WID=1 based on VU count 4, Warehouses = 20 (1 out of 5) 
Vuser 3:Ssl_cipher  
Vuser 2:VU 2 : Assigning WID=5 based on VU count 4, Warehouses = 20 (2 out of 5) 
Vuser 3:VU 3 : Assigning WID=2 based on VU count 4, Warehouses = 20 (1 out of 5) 
Vuser 2:VU 2 : Assigning WID=9 based on VU count 4, Warehouses = 20 (3 out of 5) 
Vuser 3:VU 3 : Assigning WID=6 based on VU count 4, Warehouses = 20 (2 out of 5) 
Vuser 2:VU 2 : Assigning WID=13 based on VU count 4, Warehouses = 20 (4 out of 5) 
Vuser 3:VU 3 : Assigning WID=10 based on VU count 4, Warehouses = 20 (3 out of 5) 
Vuser 2:VU 2 : Assigning WID=17 based on VU count 4, Warehouses = 20 (5 out of 5) 
Vuser 3:VU 3 : Assigning WID=14 based on VU count 4, Warehouses = 20 (4 out of 5) 
Vuser 2:Processing 10000000 transactions with output suppressed... 
Vuser 3:VU 3 : Assigning WID=18 based on VU count 4, Warehouses = 20 (5 out of 5) 
Vuser 3:Processing 10000000 transactions with output suppressed... 
Vuser 4:RUNNING 
Vuser 4:Initializing xtprof time profiler 
Vuser 4:Ssl_cipher  
Vuser 4:VU 4 : Assigning WID=3 based on VU count 4, Warehouses = 20 (1 out of 5) 
Vuser 4:VU 4 : Assigning WID=7 based on VU count 4, Warehouses = 20 (2 out of 5) 
Vuser 4:VU 4 : Assigning WID=11 based on VU count 4, Warehouses = 20 (3 out of 5) 
Vuser 4:VU 4 : Assigning WID=15 based on VU count 4, Warehouses = 20 (4 out of 5) 
Vuser 4:VU 4 : Assigning WID=19 based on VU count 4, Warehouses = 20 (5 out of 5) 
Vuser 4:Processing 10000000 transactions with output suppressed... 
Vuser 5:RUNNING 
Vuser 5:Initializing xtprof time profiler 
Vuser 5:Ssl_cipher  
Vuser 5:VU 5 : Assigning WID=4 based on VU count 4, Warehouses = 20 (1 out of 5) 
Vuser 5:VU 5 : Assigning WID=8 based on VU count 4, Warehouses = 20 (2 out of 5) 
Vuser 5:VU 5 : Assigning WID=12 based on VU count 4, Warehouses = 20 (3 out of 5) 
Vuser 5:VU 5 : Assigning WID=16 based on VU count 4, Warehouses = 20 (4 out of 5) 
Vuser 5:VU 5 : Assigning WID=20 based on VU count 4, Warehouses = 20 (5 out of 5) 
Vuser 5:Processing 10000000 transactions with output suppressed... 
54690 MariaDB tpm 
86406 MariaDB tpm 
63144 MariaDB tpm 
55824 MariaDB tpm 
90012 MariaDB tpm 
Vuser 1:Rampup 1 minutes complete ... 
90228 MariaDB tpm 
93984 MariaDB tpm 
79008 MariaDB tpm 
67278 MariaDB tpm 
62952 MariaDB tpm 
93834 MariaDB tpm 
107478 MariaDB tpm 
Vuser 1:Rampup 2 minutes complete ... 
Vuser 1:Rampup complete, Taking start Transaction Count. 
Vuser 1:Timing test period of 5 in minutes 
104154 MariaDB tpm 
95004 MariaDB tpm 
82794 MariaDB tpm 
83328 MariaDB tpm 
74424 MariaDB tpm 
43962 MariaDB tpm 
Vuser 1:1 ..., 
105270 MariaDB tpm 
37362 MariaDB tpm 
12816 MariaDB tpm 
5304 MariaDB tpm 
10470 MariaDB tpm 
6084 MariaDB tpm 
Vuser 1:2 ..., 
5730 MariaDB tpm 
6048 MariaDB tpm 
6768 MariaDB tpm 
9438 MariaDB tpm 
11280 MariaDB tpm 
10428 MariaDB tpm 
Vuser 1:3 ..., 
17022 MariaDB tpm 
14310 MariaDB tpm 
19374 MariaDB tpm 
22326 MariaDB tpm 
16698 MariaDB tpm 
20100 MariaDB tpm 
21000 MariaDB tpm 
Vuser 1:4 ..., 
10902 MariaDB tpm 
14898 MariaDB tpm 
60228 MariaDB tpm 
37632 MariaDB tpm 
50778 MariaDB tpm 
34044 MariaDB tpm 
Vuser 1:5 ..., 
Vuser 1:Test complete, Taking end Transaction Count. 
Vuser 1:4 Active Virtual Users configured 
Vuser 1:TEST RESULT : System achieved 15199 NOPM from 35354 MariaDB TPM 
Vuser 1:Gathering timing data from Active Virtual Users... 
48096 MariaDB tpm 
Vuser 5:FINISHED SUCCESS 
Vuser 2:FINISHED SUCCESS 
Vuser 3:FINISHED SUCCESS 
Vuser 4:FINISHED SUCCESS 
Vuser 1:Calculating timings... 
8226 MariaDB tpm 
Vuser 1:Writing timing data to /home/hammerdb/HammerDB-4.6/TMP/hdbxtprofile.log 
0 MariaDB tpm 
0 MariaDB tpm 
0 MariaDB tpm 
0 MariaDB tpm 
0 MariaDB tpm 
0 MariaDB tpm 
0 MariaDB tpm 
0 MariaDB tpm 
0 MariaDB tpm 
Vuser 1:FINISHED SUCCESS 
ALL VIRTUAL USERS COMPLETE 
0 MariaDB tpm 
vudestroy success 
Transaction Counter thread running with threadid:tid0x7fec51ffb700 
Stopping Transaction Counter 
TEST COMPLETE 
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- 
DROP HAMMERDB SCHEMA 
HammerDB CLI v4.6 
Copyright (C) 2003-2022 Steve Shaw 
Type "help" for a list of commands 
Initialized SQLite on-disk database /home/hammerdb/HammerDB-4.6/TMP/hammer.DB using existing tables (397,312 KB) 
SETTING CONFIGURATION 
Database set to MariaDB 
Benchmark set to TPC-C for MariaDB 
Value redpoll for connection:maria_host is the same as existing value redpoll, no change made 
Value 3306 for connection:maria_port is the same as existing value 3306, no change made 
Value /tmp/mariadb.sock for connection:maria_socket is the same as existing value /tmp/mariadb.sock, no change made 
Value root for tpcc:maria_user is the same as existing value root, no change made 
Value maria for tpcc:maria_pass is the same as existing value maria, no change made 
Value tpcc for tpcc:maria_dbase is the same as existing value tpcc, no change made 
 DROP SCHEMA STARTED 
Script cleared 
Deleting schema with 1 Virtual User 
Do you want to delete the TPCC TPROC-C schema 
 in host REDPOLL:3306 under user ROOT? 
Enter yes or no: replied yes 
Vuser 1 created - WAIT IDLE 
Vuser 1:RUNNING 
Vuser 1:Ssl_cipher  
DROP SCHEMA COMPLETED 
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+- 
HAMMERDB RESULT 
HammerDB CLI v4.6 
Copyright (C) 2003-2022 Steve Shaw 
Type "help" for a list of commands 
Initialized SQLite on-disk database /home/hammerdb/HammerDB-4.6/TMP/hammer.DB using existing tables (397,312 KB) 
TRANSACTION RESPONSE TIMES 
{ 
  "DELIVERY": { 
    "elapsed_ms": "439561.0", 
    "calls": "3320", 
    "min_ms": "4.383", 
    "avg_ms": "59.972", 
    "max_ms": "77608.65", 
    "total_ms": "199106.945", 
    "p99_ms": "307.374", 
    "p95_ms": "10.461", 
    "p50_ms": "5.74", 
    "sd": "1684200.807", 
    "ratio_pct": "45.476" 
  }, 
  "NEWORD": { 
    "elapsed_ms": "439561.0", 
    "calls": "33945", 
    "min_ms": "1.227", 
    "avg_ms": "5.016", 
    "max_ms": "17419.641", 
    "total_ms": "170261.191", 
    "p99_ms": "8.509", 
    "p95_ms": "4.596", 
    "p50_ms": "2.503", 
    "sd": "105049.036", 
    "ratio_pct": "38.888" 
  }, 
  "PAYMENT": { 
    "elapsed_ms": "439561.0", 
    "calls": "33696", 
    "min_ms": "0.602", 
    "avg_ms": "1.512", 
    "max_ms": "2687.342", 
    "total_ms": "50964.941", 
    "p99_ms": "3.443", 
    "p95_ms": "1.773", 
    "p50_ms": "1.004", 
    "sd": "23559.832", 
    "ratio_pct": "11.64" 
  }, 
  "OSTAT": { 
    "elapsed_ms": "439561.0", 
    "calls": "3393", 
    "min_ms": "0.454", 
    "avg_ms": "3.023", 
    "max_ms": "1321.03", 
    "total_ms": "10255.874", 
    "p99_ms": "3.218", 
    "p95_ms": "1.519", 
    "p50_ms": "0.868", 
    "sd": "44465.804", 
    "ratio_pct": "2.342" 
  }, 
  "SLEV": { 
    "elapsed_ms": "439561.0", 
    "calls": "3310", 
    "min_ms": "0.82", 
    "avg_ms": "1.352", 
    "max_ms": "184.051", 
    "total_ms": "4476.381", 
    "p99_ms": "3.524", 
    "p95_ms": "1.995", 
    "p50_ms": "1.135", 
    "sd": "3229.144", 
    "ratio_pct": "1.022" 
  } 
} 
 
TRANSACTION COUNT 
{"MariaDB tpm": { 
    "0": "2022-12-14 14:00:23", 
    "54690": "2022-12-14 13:51:19", 
    "86406": "2022-12-14 13:51:29", 
    "63144": "2022-12-14 13:51:39", 
    "55824": "2022-12-14 13:51:49", 
    "90012": "2022-12-14 13:51:59", 
    "90228": "2022-12-14 13:52:10", 
    "93984": "2022-12-14 13:52:19", 
    "79008": "2022-12-14 13:52:29", 
    "67278": "2022-12-14 13:52:39", 
    "62952": "2022-12-14 13:52:49", 
    "93834": "2022-12-14 13:52:59", 
    "107478": "2022-12-14 13:53:09", 
    "104154": "2022-12-14 13:53:19", 
    "95004": "2022-12-14 13:53:29", 
    "82794": "2022-12-14 13:53:39", 
    "83328": "2022-12-14 13:53:49", 
    "74424": "2022-12-14 13:53:59", 
    "43962": "2022-12-14 13:54:09", 
    "105270": "2022-12-14 13:54:19", 
    "37362": "2022-12-14 13:54:29", 
    "12816": "2022-12-14 13:54:39", 
    "5304": "2022-12-14 13:54:49", 
    "10470": "2022-12-14 13:54:59", 
    "6084": "2022-12-14 13:55:09", 
    "5730": "2022-12-14 13:55:19", 
    "6048": "2022-12-14 13:55:29", 
    "6768": "2022-12-14 13:55:39", 
    "9438": "2022-12-14 13:55:49", 
    "11280": "2022-12-14 13:56:00", 
    "10428": "2022-12-14 13:56:10", 
    "17022": "2022-12-14 13:56:22", 
    "14310": "2022-12-14 13:56:30", 
    "19374": "2022-12-14 13:56:40", 
    "22326": "2022-12-14 13:56:50", 
    "16698": "2022-12-14 13:57:00", 
    "20100": "2022-12-14 13:57:10", 
    "21000": "2022-12-14 13:57:20", 
    "10902": "2022-12-14 13:57:30", 
    "14898": "2022-12-14 13:57:40", 
    "60228": "2022-12-14 13:57:50", 
    "37632": "2022-12-14 13:58:00", 
    "50778": "2022-12-14 13:58:10", 
    "34044": "2022-12-14 13:58:20", 
    "48096": "2022-12-14 13:58:32", 
    "8226": "2022-12-14 13:58:45" 
  }} 
 
HAMMERDB RESULT 
[ 
  "6399D4CD5EFC03E233239383", 
  "2022-12-14 13:51:09", 
  "4 Active Virtual Users configured", 
  "TEST RESULT : System achieved 15199 NOPM from 35354 MariaDB TPM" 
]

Note that we captured the job timings, transaction count and test result at the end of the test.

Results storage

HammerDB will export a TMP directory in the HammerDB home directory for the storage of configuration databases and a text output of results.

root@REDPOLL:/home/hammerdb/HammerDB-4.6/TMP# ls
database.db  hammer.DB-journal                          mariadb.db      postgresql.db
db2.db       hdbxtprofile.log                           mssqlserver.db
generic.db   maria_tprocc                               mysql.db
hammer.DB    maria_tprocc_6399D4CD5EFC03E233239383.out  oracle.db

However with the jobs command, you can also query the results directly from the repository.

hammerdb>job "6399D4CD5EFC03E233239383" result
[
  "6399D4CD5EFC03E233239383",
  "2022-12-14 13:51:09",
  "4 Active Virtual Users configured",
  "TEST RESULT : System achieved 15199 NOPM from 35354 MariaDB TPM"
]

Stopping and Starting Docker

Use the Docker, stop, start and attach commands to run the HammerDB container when required.

root@REDPOLL:~# docker stop hammerdb
hammerdb
root@REDPOLL:~# docker start hammerdb
hammerdb
root@REDPOLL:~# docker attach hammerdb
root@REDPOLL:/home/hammerdb/HammerDB-4.6#

Summary

In this post we have used the offical HammerDB docker container from tpcorg to rapidly deploy HammerDB and run the included example scripts to gain an insight into database performance on our host system. You can also run HammerDB interactively or with your own scripts to gain further insight.