export performance metrics to database

[bbcc1]

I'd like to request new feature to export performance metrics (TPM,NOPM and response time) to db so it is easier to generate the graph for performance comparison.

[sm-shaw]

Many thanks for the Issue, always great to get feedback. Firstly as HammerDB is open source the best way (or certainly quickest) to get a feature added is to develop it yourself and then submit a pull request to have the feature added. If you don't necessarily have the skills to do this then it can be considered, however there is a long feature request list so it may not get added immediately. Firstly please provide some more details about what you are thinking of capturing. As a guide one enhancement being considered is to store the results in the SQLite database already used for the web service interface. This would provide a standardised way to pull the results externally.

[bbcc1]

Storing the results in the sqlite db is good enough. Any ETA for this enhancement?

[sm-shaw]

As noted HammerDB is open source, the best way to have an ETA is to develop it yourself, if you cannot do this you can also consider employing a developer who can and submit a pull request. Otherwise consider other non-technical ways in which you can contribute such as writing documentation. (developers spending time writing documentation are not developing new features).
Otherwise if these options don’t work for you this feature is already in the web service version. It is not planned for inclusion in the next release v4.0 now planned for Q3-Q4/20. Beyond v4.0 many features such as this are candidates for consideration but not yet on the roadmap so there is no current ETA from the core development team for this functionality.

[sm-shaw]

Pull request #278 adds the CLI interface as well as an enhanced SQLite repository for job output, timing data and transaction count.

On starting the web service, there is now the same interactive prompt as in the CLI, with the exception of the switchmode, steprun and datagenrun commands and the addition of the jobs command.

HammerDB Web Service v4.2
Copyright (C) 2003-2021 Steve Shaw
Type "help" for a list of commands
The xml is well-formed, applying configuration
Initialized SQLite on-disk database using existing tables
Starting HammerDB Web Service on port 8080
Listening for HTTP requests on TCP port 8080
hammerws>help
HammerDB v4.2 WS Help Index

Type "help command" for more details on specific commands below

        buildschema
        clearscript
        customscript
        datagenrun
        dbset
        dgset
        diset
        jobs
        librarycheck
        loadscript
        print
        quit
        runtimer
        tcset
        tcstart
        tcstatus
        tcstop
        vucomplete
        vucreate
        vudestroy
        vurun
        vuset
        vustatus
        waittocomplete

hammerws>

Output is stored in a SQLite database and queried with the jobs command and returned in JSON list or object format.

hammerws>jobs
[
  "61670864893D03E233632383",
  "6167091B75C003E223439343",
  "616709D3E33C03E293539323",
  "61670A8A50D403E283235323"
]

The jobs command can be used for example to query the result

hammerws>jobs 61670A8A50D403E283235323 result

[
  "61670A8A50D403E283235323",
  "2021-10-13 17:34:18",
  "8 Active Virtual Users configured",
  "TEST RESULT : System achieved 144730 NOPM from 333047 SQL Server TPM"
]

and the configuration for the job

hammerws>jobs 61670A8A50D403E283235323 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 17 for SQL Server",
    "mssqls_linux_odbc": "ODBC Driver 17 for SQL Server",
    "mssqls_uid": "sa",
    "mssqls_pass": "admin"
  },
  "tpcc": {
    "mssqls_count_ware": "1",
    "mssqls_num_vu": "1",
    "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": "1",
    "mssqls_duration": "2",
    "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"
  }
}

The HTTP interface remains active as before with the CLI translating CLI commands via a REST interface to HTTP commands. Jobs can be run and queried through the CLI or directly through HTTP

a summary of job query commands are as follows:

get http://localhost:8080/jobs
get http://localhost:8080/jobs?jobid=TEXT
get http://localhost:8080/jobs?jobid=TEXT&bm
get http://localhost:8080/jobs?jobid=TEXT&db
get http://localhost:8080/jobs?jobid=TEXT&delete
get http://localhost:8080/jobs?jobid=TEXT&dict
get http://localhost:8080/jobs?jobid=TEXT&result
get http://localhost:8080/jobs?jobid=TEXT&status
get http://localhost:8080/jobs?jobid=TEXT&tcount
get http://localhost:8080/jobs?jobid=TEXT&timestamp
get http://localhost:8080/jobs?jobid=TEXT&timing
get http://localhost:8080/jobs?jobid=TEXT&timing&vuid=INTEGER
get http://localhost:8080/jobs?jobid=TEXT&vu=INTEGER

an example CLI script would be as follows with the addition of the jobs command.

dbset db mssqls
dbset bm TPC-C
diset connection mssqls_server {(local)\\SQLDEVELOP}
diset tpcc mssqls_driver timed
diset tpcc mssqls_rampup 1
diset tpcc mssqls_duration 2
diset tpcc mssqls_timeprofile true
tcset refreshrate 10
loadscript
foreach z {1 2} {
puts "$z VU TEST"
vuset vu $z
vucreate
tcstart
set jobid [ vurun ]
runtimer 200
tcstop
jobs $jobid result
jobs $jobid timing
vudestroy
}

Returning output as follows:

hammerws>source sqlrun.tcl
{"success": {"message": "Database set to MSSQLServer"}}
{"success": {"message": "Benchmark set to TPC-C for MSSQLServer"}}
{"success": {"message": "Changed connection:mssqls_server from (local) to (local)\\SQLDEVELOP for MSSQLServer"}}
{"success": {"message": "Set driver script to timed, clearing Script, reload script to activate new setting"}}
{"success": {"message": "Changed tpcc:mssqls_rampup from 2 to 1 for MSSQLServer"}}
{"success": {"message": "Changed tpcc:mssqls_duration from 5 to 2 for MSSQLServer"}}
{"success": {"message": "Changed tpcc:mssqls_timeprofile from false to true for MSSQLServer"}}
{"success": {"message": "Transaction Counter refresh rate set to 10"}}
{"success": {"message": "script loaded"}}
1 VU TEST
{"success": {"message": "Virtual users set to 1"}}
{"success": {"message": "2 Virtual Users Created with Monitor VU"}}
{"success": {"message": "Transaction Counter Thread Started"}}
{"success": {"message": "Running Virtual Users: JOBID=6167140AF91503E273533373"}}
{"success": {"message": "Timer: 1 minutes elapsed"}}
{"success": {"message": "Timer: 2 minutes elapsed"}}
{"success": {"message": "Timer: 3 minutes elapsed"}}
{"success": {"message": "runtimer returned after 181 seconds"}}
{"success": {"message": "Transaction Counter thread running with threadid:tid0000000000003770"}}{"success": {"message": "Stopping Transaction Counter"}}
[
  "6167140AF91503E273533373",
  "2021-10-13 18:14:50",
  "1 Active Virtual Users configured",
  "TEST RESULT : System achieved 35002 NOPM from 80406 SQL Server TPM"
]
{
  "NEWORD": {
    "elapsed_ms": "179763.0",
    "calls": "103739",
    "min_ms": "0.427",
    "avg_ms": "0.698",
    "max_ms": "60.841",
    "total_ms": "72459.506",
    "p99_ms": "0.972",
    "p95_ms": "0.872",
    "p50_ms": "0.698",
    "sd": "2353.53",
    "ratio_pct": "40.308"
  },
  "PAYMENT": {
    "elapsed_ms": "179763.0",
    "calls": "103180",
    "min_ms": "0.33",
    "avg_ms": "0.593",
    "max_ms": "136.184",
    "total_ms": "61177.791",
    "p99_ms": "1.199",
    "p95_ms": "0.969",
    "p50_ms": "0.564",
    "sd": "4626.163",
    "ratio_pct": "34.032"
  },
  "DELIVERY": {
    "elapsed_ms": "179763.0",
    "calls": "10486",
    "min_ms": "1.296",
    "avg_ms": "2.035",
    "max_ms": "8.069",
    "total_ms": "21336.176",
    "p99_ms": "3.154",
    "p95_ms": "2.774",
    "p50_ms": "1.958",
    "sd": "4366.575",
    "ratio_pct": "11.869"
  },
  "SLEV": {
    "elapsed_ms": "179763.0",
    "calls": "10371",
    "min_ms": "0.631",
    "avg_ms": "1.183",
    "max_ms": "214.9",
    "total_ms": "12264.473",
    "p99_ms": "1.278",
    "p95_ms": "1.021",
    "p50_ms": "0.765",
    "sd": "74262.016",
    "ratio_pct": "6.823"
  },
  "OSTAT": {
    "elapsed_ms": "179763.0",
    "calls": "10304",
    "min_ms": "0.202",
    "avg_ms": "0.77",
    "max_ms": "178.14",
    "total_ms": "7939.151",
    "p99_ms": "1.408",
    "p95_ms": "1.134",
    "p50_ms": "0.607",
    "sd": "39337.086",
    "ratio_pct": "4.416"
  }
}
{"success": {"message": "vudestroy success"}}

With this job able to be queried at a later date through either the command line or over HTTP such as retrieving the transaction counter data.

The SQLite repository functionality will only be enabled within the WS interface as opposed to the GUI or standalone CLI that will continue to output to the standard output and text files. It is envisaged that storing output in a repository with retrieval over HTTP enables the potential to build an alternative web based UI over the existing core HammerDB functionality in future as originally raised in #108.