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
| jobs | list 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 vuid | Show 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.