11. CLI Example Scripts driven by Bash and Powershell

11. CLI Example Scripts driven by Bash and Powershell
Prev	Chapter 9. Command Line Interface (CLI)	Next

From HammerDB v4.7 the default HammerDB installation includes a scripts directory that contains example scripts for both TPROC-C and TPROC-H workloads for all databases in both Tcl and Python format to provide a template for updating and modifying scripts for an individual environment.

At a minimum you should update the connection parameters in the scripts you wish to run for your environment. Additionally the default scripts will size the workload and run the number of Virtual Users according to the number of CPUs on the system where HammerDB is running and therefore if running the client on a separate system to the database then the number of virtual users and schema sizes should be modified accordingly.

All target directories include driver scripts in powershell for Windows and bash for Linux to run an automated sequence of scripts to build, test, delete and query results for a workload. These driver scripts should be run with the full path to the script from the HammerDB home directory for example on Windows:

powershell scripts\python\mssqls\tproch\mssqls_tproch_py.ps1

and on Linux

./scripts/tcl/maria/tprocc/maria_tprocc.sh

Example output for MariaDB is shown as follows:

./scripts/tcl/maria/tprocc/maria_tprocc.sh
...
Vuser 4:Loading Orders for D=3 W=15
Vuser 5:...3000
Vuser 5:Orders Done
Vuser 5:Loading Orders for D=2 W=20
Vuser 3:...3000
Vuser 3:Orders Done
Vuser 3:Loading Orders for D=3 W=10
Vuser 2:...2000
Vuser 4:...1000
Vuser 2:...3000
Vuser 5:...1000
Vuser 2:Orders Done
Vuser 2:Loading Orders for D=4 W=5
Vuser 3:...1000
Vuser 4:...2000
Vuser 5:...2000
Vuser 2:...1000
Vuser 3:...2000
Vuser 4:...3000
Vuser 5:...3000
Vuser 4:Orders Done
Vuser 4:Loading Orders for D=4 W=15
Vuser 5:Orders Done
Vuser 5:Loading Orders for D=3 W=20
Vuser 3:...3000
Vuser 3:Orders Done
Vuser 2:...2000
Vuser 3:Loading Orders for D=4 W=10
Vuser 4:...1000
Vuser 2:...3000
Vuser 2:Orders Done
Vuser 2:Loading Orders for D=5 W=5
Vuser 5:...1000
Vuser 3:...1000
Vuser 2:...1000
Vuser 4:...2000
Vuser 5:...2000
Vuser 3:...2000
Vuser 2:...2000
Vuser 4:...3000
Vuser 4:Orders Done
Vuser 4:Loading Orders for D=5 W=15
Vuser 3:...3000
Vuser 5:...3000
Vuser 3:Orders Done
Vuser 5:Orders Done
Vuser 3:Loading Orders for D=5 W=10
Vuser 5:Loading Orders for D=4 W=20
Vuser 2:...3000
Vuser 2:Orders Done
Vuser 2:Loading Orders for D=6 W=5
Vuser 4:...1000
Vuser 5:...1000
Vuser 3:...1000
Vuser 2:...1000
Vuser 4:...2000
Vuser 3:...2000
Vuser 5:...2000
Vuser 4:...3000
Vuser 4:Orders Done
Vuser 4:Loading Orders for D=6 W=15
Vuser 2:...2000
Vuser 3:...3000
Vuser 5:...3000
Vuser 3:Orders Done
Vuser 5:Orders Done
Vuser 3:Loading Orders for D=6 W=10
Vuser 5:Loading Orders for D=5 W=20
Vuser 2:...3000
Vuser 2:Orders Done
Vuser 2:Loading Orders for D=7 W=5
Vuser 4:...1000
Vuser 5:...1000
Vuser 3:...1000
Vuser 2:...1000
Vuser 4:...2000
Vuser 5:...2000
Vuser 3:...2000
Vuser 4:...3000
Vuser 4:Orders Done
Vuser 4:Loading Orders for D=7 W=15
Vuser 2:...2000
Vuser 5:...3000
Vuser 5:Orders Done
Vuser 5:Loading Orders for D=6 W=20
Vuser 3:...3000
Vuser 3:Orders Done
Vuser 3:Loading Orders for D=7 W=10
Vuser 2:...3000
Vuser 4:...1000
Vuser 5:...1000
Vuser 2:Orders Done
Vuser 3:...1000
Vuser 2:Loading Orders for D=8 W=5
Vuser 4:...2000
Vuser 5:...2000
Vuser 4:...3000
Vuser 4:Orders Done
Vuser 4:Loading Orders for D=8 W=15
Vuser 2:...1000
Vuser 5:...3000
Vuser 3:...2000
Vuser 5:Orders Done
Vuser 5:Loading Orders for D=7 W=20
Vuser 3:...3000
Vuser 4:...1000
Vuser 3:Orders Done
Vuser 3:Loading Orders for D=8 W=10
Vuser 2:...2000
Vuser 5:...1000
Vuser 2:...3000
Vuser 2:Orders Done
Vuser 2:Loading Orders for D=9 W=5
Vuser 4:...2000
Vuser 3:...1000
Vuser 5:...2000
Vuser 4:...3000
Vuser 4:Orders Done
Vuser 4:Loading Orders for D=9 W=15
Vuser 5:...3000
Vuser 5:Orders Done
Vuser 5:Loading Orders for D=8 W=20
Vuser 3:...2000
Vuser 2:...1000
Vuser 4:...1000
Vuser 3:...3000
Vuser 5:...1000
Vuser 2:...2000
Vuser 3:Orders Done
Vuser 4:...2000
Vuser 3:Loading Orders for D=9 W=10
Vuser 5:...2000
Vuser 2:...3000
Vuser 2:Orders Done
Vuser 2:Loading Orders for D=10 W=5
Vuser 4:...3000
Vuser 4:Orders Done
Vuser 4:Loading Orders for D=10 W=15
Vuser 5:...3000
Vuser 3:...1000
Vuser 5:Orders Done
Vuser 5:Loading Orders for D=9 W=20
Vuser 2:...1000
Vuser 4:...1000
Vuser 3:...2000
Vuser 5:...1000
Vuser 2:...2000
Vuser 3:...3000
Vuser 4:...2000
Vuser 3:Orders Done
Vuser 3:Loading Orders for D=10 W=10
Vuser 5:...2000
Vuser 2:...3000
Vuser 2:Orders Done
Vuser 2:End:Fri Feb 10 15:36:44 GMT 2023
Vuser 2:FINISHED SUCCESS
Vuser 4:...3000
Vuser 4:Orders Done
Vuser 4:End:Fri Feb 10 15:36:44 GMT 2023
Vuser 4:FINISHED SUCCESS
Vuser 3:...1000
Vuser 5:...3000
Vuser 5:Orders Done
Vuser 5:Loading Orders for D=10 W=20
Vuser 3:...2000
Vuser 5:...1000
Vuser 3:...3000
Vuser 3:Orders Done
Vuser 3:End:Fri Feb 10 15:36:45 GMT 2023
Vuser 3:FINISHED SUCCESS
Vuser 5:...2000
Vuser 5:...3000
Vuser 5:Orders Done
Vuser 5:End:Fri Feb 10 15:36:46 GMT 2023
Vuser 5: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 /opt/HammerDB-master/Build/BawtBuild/Linux/x64/Release/Distribution/HammerDB-4.6/TMP/hammer.DB using existing tables (802,816 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
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:tid0x7f64a37fe700
Vuser 1:RUNNING
Vuser 1:Initializing xtprof time profiler
0 MariaDB tpm
Vuser 1:Ssl_cipher
Vuser 1:Beginning rampup time of 2 minutes
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 3:RUNNING
Vuser 3:Initializing xtprof time profiler
Vuser 2:VU 2 : Assigning WID=9 based on VU count 4, Warehouses = 20 (3 out of 5)
Vuser 3:Ssl_cipher
Vuser 2:VU 2 : Assigning WID=13 based on VU count 4, Warehouses = 20 (4 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=17 based on VU count 4, Warehouses = 20 (5 out of 5)
Vuser 3:VU 3 : Assigning WID=6 based on VU count 4, Warehouses = 20 (2 out of 5)
Vuser 2:Processing 10000000 transactions with output suppressed...
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 4:RUNNING
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: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...
69528 MariaDB tpm
85680 MariaDB tpm
77940 MariaDB tpm
79722 MariaDB tpm
81180 MariaDB tpm
80652 MariaDB tpm
Vuser 1:Rampup 1 minutes complete ...
78762 MariaDB tpm
80508 MariaDB tpm
79014 MariaDB tpm
80208 MariaDB tpm
79278 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
78324 MariaDB tpm
79398 MariaDB tpm
78462 MariaDB tpm
82122 MariaDB tpm
71898 MariaDB tpm
53268 MariaDB tpm
Vuser 1:1 ...,
59340 MariaDB tpm
60990 MariaDB tpm
59760 MariaDB tpm
63852 MariaDB tpm
65172 MariaDB tpm
62694 MariaDB tpm
65286 MariaDB tpm
Vuser 1:2 ...,
63138 MariaDB tpm
64878 MariaDB tpm
63252 MariaDB tpm
61836 MariaDB tpm
62964 MariaDB tpm
61302 MariaDB tpm
Vuser 1:3 ...,
62778 MariaDB tpm
63174 MariaDB tpm
64452 MariaDB tpm
67230 MariaDB tpm
64524 MariaDB tpm
67542 MariaDB tpm
Vuser 1:4 ...,
67050 MariaDB tpm
69540 MariaDB tpm
69612 MariaDB tpm
67926 MariaDB tpm
67182 MariaDB tpm
68634 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 28531 NOPM from 66257 MariaDB TPM
Vuser 1:Gathering timing data from Active Virtual Users...
Vuser 3:FINISHED SUCCESS
Vuser 2:FINISHED SUCCESS
Vuser 4:FINISHED SUCCESS
Vuser 5:FINISHED SUCCESS
Vuser 1:Calculating timings...
Vuser 1:Writing timing data to /opt/HammerDB-master/Build/BawtBuild/Linux/x64/Release/Distribution/HammerDB-4.6/TMP/hdbxtprofile.log
Vuser 1:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
vudestroy success
Transaction Counter thread running with threadid:tid0x7f64a37fe700
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 /opt/HammerDB-master/Build/BawtBuild/Linux/x64/Release/Distribution/HammerDB-4.6/TMP/hammer.DB using existing tables (827,392 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.6
Copyright (C) 2003-2022 Steve Shaw
Type "help" for a list of commands
Initialized SQLite on-disk database /opt/HammerDB-master/Build/BawtBuild/Linux/x64/Release/Distribution/HammerDB-4.6/TMP/hammer.DB using existing tables (827,392 KB)
TRANSACTION RESPONSE TIMES
{
  "NEWORD": {
    "elapsed_ms": "420772.0",
    "calls": "53589",
    "min_ms": "1.251",
    "avg_ms": "4.257",
    "max_ms": "126.83",
    "total_ms": "228145.129",
    "p99_ms": "14.536",
    "p95_ms": "7.009",
    "p50_ms": "3.849",
    "sd": "3230.18",
    "ratio_pct": "54.171"
  },
  "PAYMENT": {
    "elapsed_ms": "420772.0",
    "calls": "53271",
    "min_ms": "0.64",
    "avg_ms": "1.674",
    "max_ms": "151.637",
    "total_ms": "89174.518",
    "p99_ms": "6.25",
    "p95_ms": "3.016",
    "p50_ms": "1.454",
    "sd": "1637.493",
    "ratio_pct": "21.174"
  },
  "DELIVERY": {
    "elapsed_ms": "420772.0",
    "calls": "5315",
    "min_ms": "5.085",
    "avg_ms": "13.539",
    "max_ms": "206.927",
    "total_ms": "71960.928",
    "p99_ms": "96.888",
    "p95_ms": "31.328",
    "p50_ms": "9.982",
    "sd": "14895.051",
    "ratio_pct": "17.086"
  },
  "OSTAT": {
    "elapsed_ms": "420772.0",
    "calls": "5349",
    "min_ms": "0.484",
    "avg_ms": "2.556",
    "max_ms": "119.354",
    "total_ms": "13670.706",
    "p99_ms": "51.026",
    "p95_ms": "4.436",
    "p50_ms": "1.17",
    "sd": "7759.746",
    "ratio_pct": "3.246"
  },
  "SLEV": {
    "elapsed_ms": "420772.0",
    "calls": "5356",
    "min_ms": "0.82",
    "avg_ms": "1.96",
    "max_ms": "55.227",
    "total_ms": "10496.673",
    "p99_ms": "6.68",
    "p95_ms": "2.998",
    "p50_ms": "1.746",
    "sd": "1219.649",
    "ratio_pct": "2.492"
  }
}

TRANSACTION COUNT
{"MariaDB tpm": {
    "0": "2023-02-10 15:36:52",
    "69528": "2023-02-10 15:37:02",
    "85680": "2023-02-10 15:37:12",
    "77940": "2023-02-10 15:37:22",
    "79722": "2023-02-10 15:37:32",
    "81180": "2023-02-10 15:37:42",
    "80652": "2023-02-10 15:37:52",
    "78762": "2023-02-10 15:38:02",
    "80508": "2023-02-10 15:38:12",
    "79014": "2023-02-10 15:38:22",
    "80208": "2023-02-10 15:38:32",
    "79278": "2023-02-10 15:38:42",
    "78324": "2023-02-10 15:38:52",
    "79398": "2023-02-10 15:39:02",
    "78462": "2023-02-10 15:39:12",
    "82122": "2023-02-10 15:39:22",
    "71898": "2023-02-10 15:39:32",
    "53268": "2023-02-10 15:39:42",
    "59340": "2023-02-10 15:39:52",
    "60990": "2023-02-10 15:40:02",
    "59760": "2023-02-10 15:40:12",
    "63852": "2023-02-10 15:40:22",
    "65172": "2023-02-10 15:40:32",
    "62694": "2023-02-10 15:40:42",
    "65286": "2023-02-10 15:40:52",
    "63138": "2023-02-10 15:41:02",
    "64878": "2023-02-10 15:41:12",
    "63252": "2023-02-10 15:41:22",
    "61836": "2023-02-10 15:41:32",
    "62964": "2023-02-10 15:41:42",
    "61302": "2023-02-10 15:41:52",
    "62778": "2023-02-10 15:42:02",
    "63174": "2023-02-10 15:42:12",
    "64452": "2023-02-10 15:42:22",
    "67230": "2023-02-10 15:42:32",
    "64524": "2023-02-10 15:42:42",
    "67542": "2023-02-10 15:42:52",
    "67050": "2023-02-10 15:43:02",
    "69540": "2023-02-10 15:43:12",
    "69612": "2023-02-10 15:43:22",
    "67926": "2023-02-10 15:43:32",
    "67182": "2023-02-10 15:43:42",
    "68634": "2023-02-10 15:43:52"
  }}

HAMMERDB RESULT
[
  "63E664945F4503E233630353",
  "2023-02-10 15:36:52",
  "4 Active Virtual Users configured",
  "TEST RESULT : System achieved 28531 NOPM from 66257 MariaDB TPM"
]

Note that the performance results are gathered and reported at the end of the workload using the HammerDB Jobs interface and this Jobs interface is detailed in the following section.

Prev	Up	Next
10. CLI Scripting in Python	Home	12. CLI Jobs Interface