From version v4.6 HammerDB allows you to run the Command Line Interface in both interactive and scripted sessions using a Python interpreter as an alternative to the default Tcl environment on both Linux and Windows.
Python Version Dependency
HammerDB will rely on the system installed Python interpreter and at a specific version as described in the following table. On Windows you need to install Python from www.python.org rather than the Windows app store to ensure that the required libraries are installed.
Linux | 3.8 |
Red Hat Enterprise Linux | 3.6 |
Windows | 3.10 |
If you need a different version of Python you can build HammerDB from source as detailed in the documentation to use any version of Python 3 that you wish.
Starting the CLI in Python Mode
To start HammerDB in Python mode run the hammerdbcli tool with “py” or “python” as the first argument. HammerDB will show the prompt “hammerdb>>>” to indicate that it is running a Python interpreter.
$ ./hammerdbcli py
HammerDB CLI v4.6
Copyright (C) 2003-2022 Steve Shaw
Type "help()" for a list of commands
hammerdb>>>
On Windows firstly open a CMD shell, navigate to HammerDB directory and run the same command as on Linux.
C:\Program Files\HammerDB-4.6>hammerdbcli py
HammerDB CLI v4.6
Copyright (C) 2003-2022 Steve Shaw
Type "help()" for a list of commands
hammerdb>>>
In this mode HammerDB will now accept commands and scripts in Python format enabling integration with an existing Python environment. A Python script can be run from an external command or script using the auto argument and a .py extension to the script.
hammerdbcli py auto autorunbuild.py
or the script run from the interactive prompt using the source command.
hammerdb>>>source('cliexample.py')
The HammerDB commands are accessed as Python functions with all arguments given as strings or integers where appropriate.
print("SETTING CONFIGURATION")
dbset('db','mssqls')
diset('tpcc','mssqls_driver','timed')
diset('tpcc','mssqls_rampup',0)
diset('tpcc','mssqls_duration',1)
vuset('logtotemp',1)
loadscript()
print("SEQUENCE STARTED")
for z in [1,2,4]:
print(z," VU TEST")
vuset('vu',z)
vucreate()
vurun()
vudestroy()
print("TEST SEQUENCE COMPLETE")
Running this script returns output as follows using the Python loop to drive HammerDB.
hammerdb>>>source('cliexample.py') hammerdb>>>SETTING CONFIGURATION Database set to MSSQLServer Value timed for tpcc:mssqls_driver is the same as existing value timed, no change made Changed tpcc:mssqls_rampup from 2 to 0 for MSSQLServer Changed tpcc:mssqls_duration from 5 to 1 for MSSQLServer Script loaded, Type "print script" to view SEQUENCE STARTED 1 VU TEST Vuser 1 created MONITOR - WAIT IDLE Vuser 2 created - WAIT IDLE Logging activated to C:/Users/Hdb/AppData/Local/Temp/hammerdb.log 2 Virtual Users Created with Monitor VU Vuser 1:RUNNING Vuser 1:Initializing xtprof time profiler Vuser 1:Beginning rampup time of 0 minutes Vuser 1:Rampup complete, Taking start Transaction Count. Vuser 1:Timing test period of 1 in minutes Vuser 2:RUNNING Vuser 2:Initializing xtprof time profiler Vuser 2:Processing 10000000 transactions with output suppressed... Vuser 1:1 ..., Vuser 1:Test complete, Taking end Transaction Count. Vuser 1:1 Active Virtual Users configured Vuser 1:TEST RESULT : System achieved 39945 NOPM from 92893 SQL Server TPM Vuser 1:Gathering timing data from Active Virtual Users... Vuser 2:FINISHED SUCCESS Vuser 1:Calculating timings... Vuser 1:Writing timing data to C:/Users/Hdb/AppData/Local/Temp/hdbxtprofile.log Vuser 1:FINISHED SUCCESS ALL VIRTUAL USERS COMPLETE vudestroy success 2 VU TEST Vuser 1 created MONITOR - WAIT IDLE Vuser 2 created - WAIT IDLE Vuser 3 created - WAIT IDLE Logging activated to C:/Users/Hdb/AppData/Local/Temp/hammerdb.log 3 Virtual Users Created with Monitor VU Vuser 1:RUNNING Vuser 1:Initializing xtprof time profiler Vuser 1:Beginning rampup time of 0 minutes Vuser 1:Rampup complete, Taking start Transaction Count. Vuser 1:Timing test period of 1 in minutes Vuser 2:RUNNING Vuser 2:Initializing xtprof time profiler Vuser 2:Processing 10000000 transactions with output suppressed... Vuser 3:RUNNING Vuser 3:Initializing xtprof time profiler Vuser 3:Processing 10000000 transactions with output suppressed... Vuser 1:1 ..., Vuser 1:Test complete, Taking end Transaction Count. Vuser 1:2 Active Virtual Users configured Vuser 1:TEST RESULT : System achieved 81178 NOPM from 189455 SQL Server TPM Vuser 1:Gathering timing data from Active Virtual Users... Vuser 3:FINISHED SUCCESS Vuser 2:FINISHED SUCCESS Vuser 1:Calculating timings... Vuser 1:Writing timing data to C:/Users/Hdb/AppData/Local/Temp/hdbxtprofile.log Vuser 1:FINISHED SUCCESS ALL VIRTUAL USERS COMPLETE vudestroy success 4 VU TEST 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 Logging activated to C:/Users/Hdb/AppData/Local/Temp/hammerdb.log 5 Virtual Users Created with Monitor VU Vuser 1:RUNNING Vuser 1:Initializing xtprof time profiler Vuser 1:Beginning rampup time of 0 minutes Vuser 1:Rampup complete, Taking start Transaction Count. Vuser 1:Timing test period of 1 in minutes Vuser 2:RUNNING Vuser 2:Initializing xtprof time profiler Vuser 2:Processing 10000000 transactions with output suppressed... Vuser 3:RUNNING Vuser 3:Initializing xtprof time profiler Vuser 3:Processing 10000000 transactions with output suppressed... Vuser 4:RUNNING Vuser 4:Initializing xtprof time profiler Vuser 4:Processing 10000000 transactions with output suppressed... Vuser 5:RUNNING Vuser 5:Initializing xtprof time profiler Vuser 5:Processing 10000000 transactions with output suppressed... Vuser 1:1 ..., Vuser 1:Test complete, Taking end Transaction Count. Vuser 1:4 Active Virtual Users configured Vuser 1:TEST RESULT : System achieved 118762 NOPM from 276763 SQL Server TPM Vuser 1:Gathering timing data from Active Virtual Users... Vuser 5:FINISHED SUCCESS Vuser 4:FINISHED SUCCESS Vuser 3:FINISHED SUCCESS Vuser 2:FINISHED SUCCESS Vuser 1:Calculating timings... Vuser 1:Writing timing data to C:/Users/Hdb/AppData/Local/Temp/hdbxtprofile.log Vuser 1:FINISHED SUCCESS ALL VIRTUAL USERS COMPLETE vudestroy success TEST SEQUENCE COMPLETE hammerdb>>>
Python Performance and the GIL
You should already be aware that due to the Python GIL impacting multithreading performance it is not possible to drive a high-performance database workload directly from Python.
Why Tcl is 700% faster than Python for database benchmarking
Therefore, although on both Linux and Windows, you will see Python as the top process in performance monitoring tools when running the workload.
The multithreading capabilities of Tcl are embedded into Python ensuring that the same high levels of performance can be achieved as when running directly in Tcl.
How the Python CLI Interface works
The HammerDB CLI interface uses a package called tclpy to embed HammerDB into the Python interpreter. Therefore, advanced users can also use the command tclpy.eval to call HammerDB commands directly and tclpy.eval(‘vurun’) is functionally equivalent to vurun() but will return output values and therefore in the example below this is how vurun can be called in v4.6 to capture the jobid.
print("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()
print("TEST STARTED")
vuset('vu','vcpu')
vucreate()
tcstart()
tcstatus()
jobid = tclpy.eval('vurun')
vudestroy()
tcstop()
print("TEST COMPLETE")
fd = open("maria_tprocc.out", "w")
fd.write(jobid)
fd.close()
exit()
Similarly, as we are running an embedded interpreter any redirection of stdout to capture should be passed to the embedded Tcl level and not at the Python level. See the Python Docker script examples for how this is done.
Calling Python from Tcl
Although the examples in this post show HammerDB being called from a Python interpreter it is also possible to load the same package into HammerDB being run with a Tcl interpreter and call Python functions with the “py eval” command, for example:
HammerDB CLI v4.6
Copyright (C) 2003-2022 Steve Shaw
Type "help" for a list of commands
Initialized SQLite on-disk database /tmp/hammer.DB using existing tables (36,864 KB)
hammerdb>package require tclpy
0.4
hammerdb>py eval {divide = lambda x: 1.0/int(x)}
hammerdb>set d [py call divide 16]
0.0625
Summary
From HammerDB v4.6, Python users can run the HammerDB CLI in interactive or scripted mode using a Python interpreter, yet still take advantage of the advanced high performance mulithreading of Tcl. This way HammerDB can extended to take advantage of the features of both languages.