12. CLI Jobs Interface

From v4.6 the HammerDB CLI includes a jobs interface that stores the configuration, output, transaction count and timing data for a HammerDB workload that can be queried at a later point in time. This data is stored in a SQLite database called hammer.DB. From v4.8 the GUI environment also uses this Jobs database. The location of this is set in the generic.xml configuration file in the commandline section as sqlite_db, by default this area will be in the Temp directory. A flexible way to set the location is to set the TMP environment variable to a preferred location. For example on Linux.

export TMP=`pwd`/TMP

and on Windows.

C:\Program Files\HammerDB-4.9>set TEMP=C:\temp
C:\Program Files\HammerDB-4.9>hammerdbcli.bat
HammerDB CLI v4.10
Copyright (C) 2003-2024 Steve Shaw
Type "help" for a list of commands
Initialized new Jobs on-disk database C:/temp/hammer.DB
hammerdb>

The Jobs database can be relocated to any alternative preferred location as desired.

  <commandline>
<sqlite_db>TMP</sqlite_db> 
        ...
        <jobsoutput>JSON</jobsoutput>
        <jobs_disable>0</jobs_disable>
   </commandline>

Additional configuration options are jobsoutput that can be set to "text" or "JSON" and jobs_disable that if set to 1 will disable the storage of jobs data.

Also under the timeprofile section there is the option to individually disable the storage of the timeprofiler when timing data is activated by setting xt_job_storage to 0.

<timeprofile>
...
           <xt_job_storage>1</xt_job_storage>
    </timeprofile>

If jobs are enabled and the database is successfully initialized then a message such as follows will be shown on CLI startup.

Initialized SQLite on-disk database C:/Users/Hdb/AppData/Local/Temp/hammer.DB using existing tables (36,864 KB)

If manually deleted HammerDB will recreate a new jobs database.

If you do not want a jobs repository you can disable it as follows and then restart your database.

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

Any further jobs commands will receive the following message until re-enabled.

hammerdb>jobs
Error: Jobs Disabled: enable with command "jobs disable 0" and restart HammerDB

hammerdb>

When jobs are running all output will be stored in the local SQLite database and therefore if for example running "test" workloads with large volumes of output or establishing a configuration you may not wish to store jobs output at this time.

The jobs command has the following functionality:

Table 9.3. Jobs command

jobslist all jobs
jobs [jobid|joblist||result|timestamp]Query all jobs, list the VU output for the jobid, list the results for all jobs, list the timestamp for all jobs.
jobs format [ text|JSON]Format job output as text or JSON.
jobs disable [ 0 | 1 ]Disable or re-enable storage of job output after restart.
jobs profileid [ id ]Returns or sets the performance profile id
jobs profile [ id ]Returns the results for the performance profile id
jobs jobid [bm|db|delete|dict|metrics|result|status|system|tcount|timestamp|timing|vuid]Query an individual job, showing the full output, benchmark, database, delete the individual job, show the result, status, transaction count, timestamp, timing and limited output for a single vuid.
jobs jobid timing - Usage: jobs jobid timing vuidShow the timing data for an individual vuid.
jobs jobid getchart [result | timing | tcount | metrics | profile ]Generate html chart for TPROC-C/TPROC-H results, timing, transaction counts and performance profile

As an example of jobs functionality if we run the following script.

dbset db mssqls
diset tpcc mssqls_driver timed
diset tpcc mssqls_rampup 2
diset tpcc mssqls_duration 5
diset tpcc mssqls_timeprofile true
loadscript
vuset vu vcpu
vucreate
tcstart
set jobid [ vurun ]
tcstop
vudestroy
puts "jobid is $jobid"

It produces output as follows, the vurun command returns the jobid of the job it ran.

hammerdb>source clijob.tcl
Database set to MSSQLServer
Value timed for tpcc:mssqls_driver is the same as existing value timed, no change made
Value 2 for tpcc:mssqls_rampup is the same as existing value 2, no change made
Value 5 for tpcc:mssqls_duration is the same as existing value 5, no change made
Value true for tpcc:mssqls_timeprofile is the same as existing value true, no change made
Script loaded, Type "print script" to view
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
Vuser 6 created - WAIT IDLE
Vuser 7 created - WAIT IDLE
Vuser 8 created - WAIT IDLE
Vuser 9 created - WAIT IDLE
9 Virtual Users Created with Monitor VU
Transaction Counter Started
Vuser 1:RUNNING
Vuser 1:Initializing xtprof time profiler
0 MSSQLServer tpm
Vuser 1:Beginning rampup time of 2 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 6:RUNNING
Vuser 6:Initializing xtprof time profiler
Vuser 6:Processing 10000000 transactions with output suppressed...
Vuser 7:RUNNING
Vuser 7:Initializing xtprof time profiler
Vuser 7:Processing 10000000 transactions with output suppressed...
Vuser 8:RUNNING
Vuser 8:Initializing xtprof time profiler
Vuser 8:Processing 10000000 transactions with output suppressed...
Vuser 9:RUNNING
Vuser 9:Initializing xtprof time profiler
Vuser 9:Processing 10000000 transactions with output suppressed...
321276 MSSQLServer tpm
310152 MSSQLServer tpm
353814 MSSQLServer tpm
419544 MSSQLServer tpm
407388 MSSQLServer tpm
Vuser 1:Rampup 1 minutes complete ...
416802 MSSQLServer tpm
311670 MSSQLServer tpm
397320 MSSQLServer tpm
410778 MSSQLServer tpm
393156 MSSQLServer tpm
411288 MSSQLServer tpm
Vuser 1:Rampup 2 minutes complete ...
Vuser 1:Rampup complete, Taking start Transaction Count.
Vuser 1:Timing test period of 5 in minutes
361482 MSSQLServer tpm
388710 MSSQLServer tpm
381024 MSSQLServer tpm
309954 MSSQLServer tpm
385224 MSSQLServer tpm
394812 MSSQLServer tpm
Vuser 1:1 ...,
382122 MSSQLServer tpm
354288 MSSQLServer tpm
422178 MSSQLServer tpm
393714 MSSQLServer tpm
327702 MSSQLServer tpm
281616 MSSQLServer tpm
Vuser 1:2 ...,
405342 MSSQLServer tpm
403242 MSSQLServer tpm
385608 MSSQLServer tpm
399612 MSSQLServer tpm
422100 MSSQLServer tpm
385494 MSSQLServer tpm
Vuser 1:3 ...,
393402 MSSQLServer tpm
320376 MSSQLServer tpm
412254 MSSQLServer tpm
411258 MSSQLServer tpm
394920 MSSQLServer tpm
400608 MSSQLServer tpm
Vuser 1:4 ...,
372738 MSSQLServer tpm
370362 MSSQLServer tpm
387276 MSSQLServer tpm
379926 MSSQLServer tpm
350820 MSSQLServer tpm
370398 MSSQLServer tpm
Vuser 1:5 ...,
Vuser 1:Test complete, Taking end Transaction Count.
Vuser 1:8 Active Virtual Users configured
Vuser 1:TEST RESULT : System achieved 161688 NOPM from 376090 SQL Server TPM
Vuser 1:Gathering timing data from Active Virtual Users...
Vuser 8:FINISHED SUCCESS
Vuser 3:FINISHED SUCCESS
Vuser 9:FINISHED SUCCESS
Vuser 6:FINISHED SUCCESS
293592 MSSQLServer tpm
Vuser 4:FINISHED SUCCESS
Vuser 5:FINISHED SUCCESS
Vuser 2:FINISHED SUCCESS
Vuser 7:FINISHED SUCCESS
Vuser 1:Calculating timings...
Vuser 1:Writing timing data to C:/Users/Steve/AppData/Local/Temp/hdbxtprofile.log
Vuser 1:FINISHED SUCCESS
ALL VIRTUAL USERS COMPLETE
156 MSSQLServer tpm
Transaction Counter thread running with threadid:tid0000000000001308
Stopping Transaction Counter
vudestroy success
jobid is jobid=637CF9B73C8D03E243636303

hammerdb>

Querying the job status will return all of the status messages from the Virtual Users showing that the job completed successfully.

hammerdb>job 637CF9B73C8D03E243636303 status
[
  "0",
  "Vuser 1:RUNNING",
  "0",
  "Vuser 2:RUNNING",
  "0",
  "Vuser 3:RUNNING",
  "0",
  "Vuser 4:RUNNING",
  "0",
  "Vuser 5:RUNNING",
  "0",
  "Vuser 6:RUNNING",
  "0",
  "Vuser 7:RUNNING",
  "0",
  "Vuser 8:RUNNING",
  "0",
  "Vuser 9:RUNNING",
  "0",
  "Vuser 8:FINISHED SUCCESS",
  "0",
  "Vuser 3:FINISHED SUCCESS",
  "0",
  "Vuser 9:FINISHED SUCCESS",
  "0",
  "Vuser 6:FINISHED SUCCESS",
  "0",
  "Vuser 4:FINISHED SUCCESS",
  "0",
  "Vuser 5:FINISHED SUCCESS",
  "0",
  "Vuser 2:FINISHED SUCCESS",
  "0",
  "Vuser 7:FINISHED SUCCESS",
  "0",
  "Vuser 1:FINISHED SUCCESS",
  "0",
  "ALL VIRTUAL USERS COMPLETE"
]

We can also view the result of the job.

hammerdb>job 637CF9B73C8D03E243636303 result
[
  "637CF9B73C8D03E243636303",
  "2022-11-22 16:32:55",
  "8 Active Virtual Users configured",
  "TEST RESULT : System achieved 161688 NOPM from 376090 SQL Server TPM"
]

Querying the job with no further arguments returns the output from all Virtual Users. If a vuid is specified it returns the output from a single VU.

hammerdb>job 637CF9B73C8D03E243636303
[
  "0",
  "Vuser 1:RUNNING",
  "1",
  "Initializing xtprof time profiler",
  "1",
  "Beginning rampup time of 2 minutes",
  "0",
  "Vuser 2:RUNNING",
  "2",
  "Initializing xtprof time profiler",
  "2",
  "Processing 10000000 transactions with output suppressed...",
  "0",
  "Vuser 3:RUNNING",
  "3",
  "Initializing xtprof time profiler",
  "3",
  "Processing 10000000 transactions with output suppressed...",
  "0",
  "Vuser 4:RUNNING",
  "4",
  "Initializing xtprof time profiler",
  "4",
  "Processing 10000000 transactions with output suppressed...",
  "0",
  "Vuser 5:RUNNING",
  "5",
  "Initializing xtprof time profiler",
  "5",
  "Processing 10000000 transactions with output suppressed...",
  "0",
  "Vuser 6:RUNNING",
  "6",
  "Initializing xtprof time profiler",
  "6",
  "Processing 10000000 transactions with output suppressed...",
  "0",
  "Vuser 7:RUNNING",
  "7",
  "Initializing xtprof time profiler",
  "7",
  "Processing 10000000 transactions with output suppressed...",
  "0",
  "Vuser 8:RUNNING",
  "8",
  "Initializing xtprof time profiler",
  "8",
  "Processing 10000000 transactions with output suppressed...",
  "0",
  "Vuser 9:RUNNING",
  "9",
  "Initializing xtprof time profiler",
  "9",
  "Processing 10000000 transactions with output suppressed...",
  "1",
  "Rampup 1 minutes complete ...",
  "1",
  "Rampup 2 minutes complete ...",
  "1",
  "Rampup complete, Taking start Transaction Count.",
  "1",
  "Timing test period of 5 in minutes",
  "1",
  "1  ...,",
  "1",
  "2  ...,",
  "1",
  "3  ...,",
  "1",
  "4  ...,",
  "1",
  "5  ...,",
  "1",
  "Test complete, Taking end Transaction Count.",
  "1",
  "8 Active Virtual Users configured",
  "1",
  "TEST RESULT : System achieved 161688 NOPM from 376090 SQL Server TPM",
  "1",
  "Gathering timing data from Active Virtual Users...",
  "0",
  "Vuser 8:FINISHED SUCCESS",
  "0",
  "Vuser 3:FINISHED SUCCESS",
  "0",
  "Vuser 9:FINISHED SUCCESS",
  "0",
  "Vuser 6:FINISHED SUCCESS",
  "0",
  "Vuser 4:FINISHED SUCCESS",
  "0",
  "Vuser 5:FINISHED SUCCESS",
  "0",
  "Vuser 2:FINISHED SUCCESS",
  "0",
  "Vuser 7:FINISHED SUCCESS",
  "1",
  "Calculating timings...",
  "1",
  "Writing timing data to C:\/Users\/Steve\/AppData\/Local\/Temp\/hdbxtprofile.log",
  "0",
  "Vuser 1:FINISHED SUCCESS",
  "0",
  "ALL VIRTUAL USERS COMPLETE"
]

We also store the configuration so can query the dict of the job that was run.

hammerdb>job 637CF9B73C8D03E243636303 dict
{
  "connection": {
    "mssqls_server": "(local)\\SQLDEVELOP",
    "mssqls_linux_server": "localhost",
    "mssqls_tcp": "false",
    "mssqls_port": "1433",
    "mssqls_azure": "false",
    "mssqls_authentication": "windows",
    "mssqls_linux_authent": "sql",
    "mssqls_odbc_driver": "ODBC Driver 18 for SQL Server",
    "mssqls_linux_odbc": "ODBC Driver 18 for SQL Server",
    "mssqls_uid": "sa",
    "mssqls_pass": "admin",
    "mssqls_encrypt_connection": "true",
    "mssqls_trust_server_cert": "true"
  },
  "tpcc": {
    "mssqls_count_ware": "10",
    "mssqls_num_vu": "8",
    "mssqls_dbase": "tpcc",
    "mssqls_imdb": "false",
    "mssqls_bucket": "1",
    "mssqls_durability": "SCHEMA_AND_DATA",
    "mssqls_total_iterations": "10000000",
    "mssqls_raiseerror": "false",
    "mssqls_keyandthink": "false",
    "mssqls_checkpoint": "false",
    "mssqls_driver": "timed",
    "mssqls_rampup": "2",
    "mssqls_duration": "5",
    "mssqls_allwarehouse": "false",
    "mssqls_timeprofile": "true",
    "mssqls_async_scale": "false",
    "mssqls_async_client": "10",
    "mssqls_async_verbose": "false",
    "mssqls_async_delay": "1000",
    "mssqls_connect_pool": "false"
  }
}

If we ran the transaction counter we can retrieve the transaction count for the duration of the job.

hammerdb>job 637CF9B73C8D03E243636303 tcount
{"MSSQLServer tpm": {
    "0": "2022-11-22 16:32:56",
    "321276": "2022-11-22 16:33:06",
    "310152": "2022-11-22 16:33:16",
    "353814": "2022-11-22 16:33:26",
    "419544": "2022-11-22 16:33:36",
    "407388": "2022-11-22 16:33:46",
    "416802": "2022-11-22 16:33:56",
    "311670": "2022-11-22 16:34:06",
    "397320": "2022-11-22 16:34:16",
    "410778": "2022-11-22 16:34:26",
    "393156": "2022-11-22 16:34:36",
    "411288": "2022-11-22 16:34:46",
    "361482": "2022-11-22 16:34:56",
    "388710": "2022-11-22 16:35:07",
    "381024": "2022-11-22 16:35:17",
    "309954": "2022-11-22 16:35:27",
    "385224": "2022-11-22 16:35:37",
    "394812": "2022-11-22 16:35:47",
    "382122": "2022-11-22 16:35:57",
    "354288": "2022-11-22 16:36:07",
    "422178": "2022-11-22 16:36:17",
    "393714": "2022-11-22 16:36:27",
    "327702": "2022-11-22 16:36:37",
    "281616": "2022-11-22 16:36:47",
    "405342": "2022-11-22 16:36:57",
    "403242": "2022-11-22 16:37:07",
    "385608": "2022-11-22 16:37:18",
    "399612": "2022-11-22 16:37:28",
    "422100": "2022-11-22 16:37:38",
    "385494": "2022-11-22 16:37:48",
    "393402": "2022-11-22 16:37:58",
    "320376": "2022-11-22 16:38:08",
    "412254": "2022-11-22 16:38:18",
    "411258": "2022-11-22 16:38:28",
    "394920": "2022-11-22 16:38:38",
    "400608": "2022-11-22 16:38:48",
    "372738": "2022-11-22 16:38:58",
    "370362": "2022-11-22 16:39:09",
    "387276": "2022-11-22 16:39:19",
    "379926": "2022-11-22 16:39:29",
    "350820": "2022-11-22 16:39:39",
    "370398": "2022-11-22 16:39:49",
    "293592": "2022-11-22 16:39:59",
    "156": "2022-11-22 16:40:09"
  }}

and similarly with the timing data.

hammerdb>job 637CF9B73C8D03E243636303 timing
{
  "NEWORD": {
    "elapsed_ms": "419545.5",
    "calls": "135219",
    "min_ms": "0.442",
    "avg_ms": "1.365",
    "max_ms": "5233.271",
    "total_ms": "184527.133",
    "p99_ms": "3.706",
    "p95_ms": "2.07",
    "p50_ms": "1.081",
    "sd": "155972.059",
    "ratio_pct": "44.232"
  },
  "PAYMENT": {
    "elapsed_ms": "419545.5",
    "calls": "135485",
    "min_ms": "0.371",
    "avg_ms": "1.009",
    "max_ms": "465.959",
    "total_ms": "136748.509",
    "p99_ms": "3.098",
    "p95_ms": "1.677",
    "p50_ms": "0.741",
    "sd": "36829.979",
    "ratio_pct": "32.779"
  },
  "DELIVERY": {
    "elapsed_ms": "419545.5",
    "calls": "13481",
    "min_ms": "0.922",
    "avg_ms": "2.882",
    "max_ms": "4545.935",
    "total_ms": "38847.119",
    "p99_ms": "5.296",
    "p95_ms": "3.074",
    "p50_ms": "1.879",
    "sd": "523063.177",
    "ratio_pct": "9.312"
  },
  "SLEV": {
    "elapsed_ms": "419545.5",
    "calls": "13435",
    "min_ms": "0.645",
    "avg_ms": "2.015",
    "max_ms": "1279.086",
    "total_ms": "27074.317",
    "p99_ms": "2.706",
    "p95_ms": "1.898",
    "p50_ms": "1.304",
    "sd": "220436.794",
    "ratio_pct": "6.49"
  },
  "OSTAT": {
    "elapsed_ms": "419545.5",
    "calls": "13548",
    "min_ms": "0.3",
    "avg_ms": "1.266",
    "max_ms": "2386.227",
    "total_ms": "17153.631",
    "p99_ms": "2.365",
    "p95_ms": "1.544",
    "p50_ms": "0.771",
    "sd": "235611.656",
    "ratio_pct": "4.112"
  }
}

By default the jobs output is returned in JSON list and dict format. If preferred this output can be set to text.

hammerdb>job format text
Setting jobs output format to text

hammerdb>jobs
6373B8BC5B2903E243637333
637CD47BAB9C03E203730313
637CD5C0CC7A03E203330323
637CD5FEF1A303E293839323
637CD63E175A03E203430383
637CD8FAB8D703E233032343
637CD9FD537D03E283432383
637CDA447DC703E293232393
637CDA82A24E03E233437353
637CDAC0C74B03E263631323
637CF5B8DA7503E293739383
637CF7CA167B03E213436333
637CF9B73C8D03E243636303

hammerdb>job 637CF9B73C8D03E243636303 bm
TPC-C

hammerdb>job 637CF9B73C8D03E243636303 timestamp
637CF9B73C8D03E243636303 {2022-11-22 16:32:55}

If you run the metstart command before a CLI job then HammerDB will also record the CPU metrics for the Job.

Connecting to Agent to Display CPU Metrics
Metric receive port open @ 11732 on yellowhammer
Connecting to HammerDB Agent @ localhost:10000
Testing Agent Connectivity...OK
Metrics Connected
Started CPU Metrics for Intel(R) Core(TM) i5-1035G7 CPU @ 1.20GHz:(8 CPUs)
233352 MSSQLServer tpm
CPU all usr%-23.85 sys%-3.03 irq%-0.00 idle%-71.33
369714 MSSQLServer tpm
CPU all usr%-24.85 sys%-2.36 irq%-0.00 idle%-72.65
383484 MSSQLServer tpm
CPU all usr%-24.67 sys%-2.31 irq%-0.00 idle%-72.81
337770 MSSQLServer tpm
CPU all usr%-0.00 sys%-1.97 irq%-0.00 idle%-81.31
211182 MSSQLServer tpm
CPU all usr%-24.03 sys%-2.21 irq%-0.00 idle%-73.24
Vuser 1:Rampup 1 minutes complete ...
Vuser 1:Rampup complete, Taking start Transaction Count.
Vuser 1:Timing test period of 2 in minutes
293148 MSSQLServer tpm
CPU all usr%-22.90 sys%-2.65 irq%-0.00 idle%-72.83
259614 MSSQLServer tpm
CPU all usr%-45.36 sys%-8.97 irq%-0.58 idle%-41.59
241854 MSSQLServer tpm
CPU all usr%-18.19 sys%-2.73 irq%-0.29 idle%-77.61
280578 MSSQLServer tpm
CPU all usr%-23.01 sys%-3.04 irq%-0.00 idle%-73.33
221520 MSSQLServer tpm
CPU all usr%-20.61 sys%-2.75 irq%-0.31 idle%-75.22
221610 MSSQLServer tpm
CPU all usr%-24.43 sys%-3.30 irq%-0.00 idle%-71.92
Vuser 1:1 ...,
189894 MSSQLServer tpm
CPU all usr%-4.46 sys%-1.28 irq%-0.00 idle%-86.20
193962 MSSQLServer tpm
CPU all usr%-20.12 sys%-3.19 irq%-0.44 idle%-76.33
200604 MSSQLServer tpm
CPU all usr%-18.80 sys%-2.49 irq%-0.00 idle%-76.88
240342 MSSQLServer tpm
CPU all usr%-16.95 sys%-1.83 irq%-0.11 idle%-80.77
235584 MSSQLServer tpm
CPU all usr%-18.14 sys%-2.56 irq%-0.00 idle%-78.80
240912 MSSQLServer tpm
CPU all usr%-19.13 sys%-2.49 irq%-0.00 idle%-77.52
223194 MSSQLServer tpm

The jobs functionality extends HammerDB functionality as an essential repository for your test configuration and results.