Adding the enhancement for storing and retrieval of HammerDB results and configurations #352

emily-ygz · 2022-05-06T10:02:56Z

I understand this feature is already in the web service and is described in this blog post here https://www.hammerdb.com/blog/uncategorized/hammerdb-v4-3-new-features-pt2-enhanced-webservice-with-sqlite-repository/.

But it would also a useful feature for people who use CLI and GUI. To browse the generated results later on, there could be a better way to make the hammerdb log and used configurations more traceable And for GUI the results could be visualized for a better user experience

sm-shaw · 2022-05-06T12:02:00Z

This is a good feature proposal and one we should look at how best to implement.
Yes, the web service has a SQLite database with each workload referenced by a JOBID that can be retrieved later on with the jobs command. Therefore, it appears that the potential is to adapt this for use with the CLI and GUI also (so the same data generated could be viewed with any interface).
I could work on the GUI enhancement part, adding another tab such as we have for Oracle, PostgreSQL metrics for results viewing from SQLite with a spreadsheet type format would be a useful feature.

pooja-jain-17 · 2022-08-10T11:55:55Z

Yes, this is a great feature to have results and configuration be added to HammerDB logs. It will also be useful to include HammerDB version used to do the testing as part of the output in log files. Thanks.

sm-shaw · 2022-08-18T16:14:59Z

Issue #420 details how possibly this could be done by porting some of the WS features into the CLI/GUI.

sm-shaw · 2022-11-10T17:20:51Z

Pull request #453 and #458 add this functionality, initially, to the CLI. As detailed in #458 it is also possible to enable the job storage for the GUI however this has been held back to add the functionality mentioned above "And for GUI the results could be visualized for a better user experience" in the GUI.
Issue #430 planned to deprecate the web service once the jobs functionality was enabled in the CLI and GUI. Instead the updated plan is to modify the webservice to be query based enabling the viewing and sharing of jobs data generated by the GUI & CLI with functionality to start the service from these interfaces.

emily-ygz · 2022-11-11T03:03:02Z

Sounds great, thanks Steve!

Jiang-Hua · 2022-11-11T03:18:28Z

Now test results can be saved, HammerDB can provide a ranking list for users to upload test results to the website of HammerDB just like on the TPC website. Submitting a TPC result is very strict and costly. HammerDB can provide a lightweight ranking list.

sm-shaw · 2022-11-16T16:38:06Z

For the next step for analysing the data a modified web service approach is probably the best approach as we already have the working framework that needs updating for the functionality. We already have started with Wapp https://wapp.tcl.tk/home/doc/trunk/docs/usageexamples.md and have the following API implemented to query jobs that can now (v4.6) be generated by the CLI (and GUI) so the interface needs to implement these queries.

CLI Command	API	Description
jobs	get http://localhost:8080/jobs	list all jobs in the SQLite database.
jobs jobid	get http://localhost:8080/jobs?jobid=TEXT	list VU output for jobid.
jobs result	N/A	list all results found in the SQLite database.
jobs timestamp	N/A	list all timestamps found in the SQLite database.
jobs jobid bm	get http://localhost:8080/jobs?jobid=TEXT&bm	list the configured benchmark for the jobid.
jobs jobid db	get http://localhost:8080/jobs?jobid=TEXT&db	list the configured database for the jobid.
jobs jobid delete	get http://localhost:8080/jobs?jobid=TEXT&delete	delete the jobid from the SQLite database.
jobs jobid dict	get http://localhost:8080/jobs?jobid=TEXT&dict	list the configured dict for the jobid.
jobs jobid result	get http://localhost:8080/jobs?jobid=TEXT&result	list the result for the jobid.
jobs jobid status	get http://localhost:8080/jobs?jobid=TEXT&status	list the current status for the jobid.
jobs jobid tcount	get http://localhost:8080/jobs?jobid=TEXT&tcount	list the transaction count for the jobid if the transaction counter was run.
jobs jobid timestamp	get http://localhost:8080/jobs?jobid=TEXT&timestamp	list the timestamp for when the job started.
jobs jobid timing	get http://localhost:8080/jobs?jobid=TEXT&timing	list the xtprof timing for the job if time profiling was run.
jobs jobid vuid	get http://localhost:8080/jobs?jobid=TEXT&vu=INTEGER	list the output for the jobid for the specified virtual user.
jobs jobid timing vuid	get http://localhost:8080/jobs?jobid=TEXT&timing&vuid=INTEGER	list the time profile for the jobid for the specified virtual user.

It should be possible to use something very similar to the checklist app https://www.sqlite.org/checklistapp/doc/trunk/README.md which is already used for SQLite so is robust, implementing something similar as shown here listing job numbers on the home page https://sqlite.org/src/ext/checklist also using SQLite for its repository.

For visualizing the data this package using apache echarts https://github.com/nico-robert/ticklecharts looks very useful. As per example below this renders an html file with a chart from your data. An example below is from a SQL Server test. This already uses huddle which HammerDB uses.

package require ticklecharts
set chart [ticklecharts::chart new]
$chart Xaxis -data [list {"2022-11-10 15:55:15" "2022-11-10 15:55:25" "2022-11-10 15:55:35" "2022-11-10 15:55:46" "2022-11-10 15:55:56" "2022-11-10 15:56:06" "2022-11-10 15:56:16" "2022-11-10-15:56:26" "2022-11-10 15:56:36" "2022-11-10 15:56:46" "2022-11-10 15:56:56" "2022-11-10 15:57:06" "2022-11-10 15:57:16" "2022-11-10 15:57:26" "2022-11-10 15:57:36"}]
$chart Yaxis
$chart AddLineSeries -data [list {0 146574 209502 229542 219078 201930 217962 215688 221238 209952 212550 188166 217692 203022 183282 195666 192834 155154 166032}]
$chart Render

Once we have a common way to view and browse the data locally an option could be added as you suggest to upload jobs to the HammerDB website for sharing and viewed with a similar interface.

The GUI will need enhancing with a way to enable/disable jobs and view the data (similar to how the CLI can view the data in v4.6) at this point the functionality to generate jobs from the GUI can be enabled as described in PR #458.

sm-shaw · 2023-03-14T17:53:34Z

Following shows an example of adding and echart to the CLI that stores and returns the html for a jobid. Example is for a job result with additional charts to be added for timing and tcount. These can then either be retrieved from the CLI manually or with the redesigned web service to query stored jobs via a browser.

hammerdb>jobs 640A2EA33C7603E273635333 getchart result
<!DOCTYPE html>
<html>
  <head>
    <meta charset="UTF-8">
    <title>640A2EA33C7603E273635333 Result</title>
    <script type="text/javascript" src="https://cdn.jsdelivr.net/npm/echarts@5.4.1/dist/echarts.min.js"></script>
  </head>
  <body>
    <div id="id_96803d58de4d4a719cf81099f1d10b42" class="chart-container" style="width:900px; height:500px;"></div>
    <script>
        var chart_96803d58de4d4a719cf81099f1d10b42 = echarts.init(document.getElementById('id_96803d58de4d4a719cf81099f1d10b42'), null, {renderer: 'canvas'});
        var option_96803d58de4d4a719cf81099f1d10b42 = {
  "backgroundColor": "rgba(0,0,0,0)",
  "color": [
    "#5470c6",
    "#91cc75",
    "#fac858",
    "#ee6666",
    "#73c0de",
    "#3ba272",
    "#fc8452",
    "#9a60b4",
    "#ea7ccc"
  ],
  "animation": true,
  "animationDuration": 1000,
  "animationDurationUpdate": 500,
  "animationEasing": "cubicInOut",
  "animationEasingUpdate": "cubicInOut",
  "animationThreshold": 2000,
  "progressiveThreshold": 3000,
  "title": [{
      "show": true,
      "text": "SQL Server Result 640A2EA33C7603E273635333 @ 2023-03-09 19:08:19",
      "target": "blank",
      "subtarget": "blank",
      "textAlign": null,
      "textVerticalAlign": "auto",
      "padding": 5,
      "itemGap": 10,
      "z": 2,
      "left": "auto",
      "top": "auto",
      "right": "auto",
      "bottom": "auto",
      "backgroundColor": "transparent",
      "borderColor": "transparent",
      "borderWidth": 1,
      "borderRadius": 0
    }],
  "legend": [{
      "type": "plain",
      "show": true,
      "z": 2,
      "left": "45%",
      "top": "auto",
      "right": "auto",
      "bottom": "5%",
      "width": "auto",
      "height": "auto",
      "orient": "horizontal",
      "align": "auto",
      "padding": 5,
      "itemGap": 10,
      "itemWidth": 25,
      "itemHeight": 14,
      "symbolRotate": "inherit",
      "selectedMode": true,
      "inactiveColor": "rgb(204, 204, 204)",
      "inactiveBorderColor": "rgb(204, 204, 204)",
      "inactiveBorderWidth": "auto",
      "backgroundColor": "transparent",
      "borderWidth": 0,
      "borderRadius": 0,
      "pageButtonItemGap": 5,
      "pageIconColor": "rgb(47, 69, 84)",
      "pageIconInactiveColor": "rgb(170, 170, 170)",
      "pageIconSize": 15
    }],
  "tooltip": [{
      "show": true,
      "trigger": "item",
      "showContent": true,
      "alwaysShowContent": false,
      "triggerOn": "mousemove|click",
      "transitionDuration": 0.4,
      "padding": 5,
      "order": "seriesAsc"
    }],
  "xAxis": [{
      "show": true,
      "type": "category",
      "data": ["SQL Server 8 Active Virtual Users configured"],
      "gridIndex": 0,
      "position": "bottom",
      "offset": 0,
      "nameLocation": "end",
      "nameGap": 15,
      "nameRotate": 0,
      "inverse": false,
      "boundaryGap": true,
      "scale": false,
      "splitNumber": 5,
      "minInterval": 0,
      "silent": false,
      "triggerEvent": false,
      "axisLabel": {
        "show": true,
        "interval": "auto",
        "inside": false,
        "rotate": 0,
        "margin": 8,
        "showMinLabel": null,
        "showMaxLabel": null,
        "hideOverlap": false,
        "fontStyle": "normal",
        "fontWeight": "normal",
        "fontFamily": "sans-serif",
        "fontSize": 12,
        "lineHeight": 12,
        "backgroundColor": "transparent",
        "borderWidth": 0,
        "borderType": "solid",
        "borderDashOffset": 0,
        "borderRadius": 0,
        "padding": 0,
        "shadowColor": "transparent",
        "shadowBlur": 0,
        "shadowOffsetX": 0,
        "shadowOffsetY": 0,
        "textBorderColor": null,
        "textBorderWidth": 0,
        "textBorderType": "solid",
        "textBorderDashOffset": 0,
        "textShadowColor": "transparent",
        "textShadowBlur": 0,
        "textShadowOffsetX": 0,
        "textShadowOffsetY": 0,
        "overflow": "truncate",
        "ellipsis": "..."
      },
      "zlevel": 0,
      "z": 0
    }],
  "yAxis": [{
      "show": true,
      "gridIndex": 0,
      "position": "left",
      "offset": 0,
      "realtimeSort": true,
      "sortSeriesIndex": 0,
      "type": "value",
      "name": "Transactions",
      "nameLocation": "end",
      "nameGap": 15,
      "nameRotate": 0,
      "inverse": false,
      "boundaryGap": false,
      "scale": false,
      "splitNumber": 5,
      "minInterval": 0,
      "silent": false,
      "triggerEvent": false,
      "axisLabel": {
        "show": true,
        "interval": "auto",
        "inside": false,
        "rotate": 0,
        "margin": 8,
        "showMinLabel": null,
        "showMaxLabel": null,
        "hideOverlap": false,
        "fontStyle": "normal",
        "fontWeight": "normal",
        "fontFamily": "sans-serif",
        "fontSize": 12,
        "lineHeight": 12,
        "backgroundColor": "transparent",
        "borderWidth": 0,
        "borderType": "solid",
        "borderDashOffset": 0,
        "borderRadius": 0,
        "padding": 0,
        "shadowColor": "transparent",
        "shadowBlur": 0,
        "shadowOffsetX": 0,
        "shadowOffsetY": 0,
        "textBorderColor": null,
        "textBorderWidth": 0,
        "textBorderType": "solid",
        "textBorderDashOffset": 0,
        "textShadowColor": "transparent",
        "textShadowBlur": 0,
        "textShadowOffsetX": 0,
        "textShadowOffsetY": 0,
        "overflow": "truncate",
        "ellipsis": "..."
      },
      "zlevel": 0,
      "z": 0
    }],
  "series": [
    {
      "type": "bar",
      "name": "NOPM",
      "colorBy": "series",
      "legendHoverLink": true,
      "coordinateSystem": "cartesian2d",
      "roundCap": false,
      "showBackground": false,
      "sampling": "max",
      "cursor": "pointer",
      "barMinWidth": null,
      "barCategoryGap": "20%",
      "large": false,
      "largeThreshold": 400,
      "data": [77283],
      "zlevel": 0,
      "z": 2,
      "silent": false
    },
    {
      "type": "bar",
      "name": "TPM",
      "colorBy": "series",
      "legendHoverLink": true,
      "coordinateSystem": "cartesian2d",
      "roundCap": false,
      "showBackground": false,
      "sampling": "max",
      "cursor": "pointer",
      "barMinWidth": null,
      "barCategoryGap": "20%",
      "large": false,
      "largeThreshold": 400,
      "data": [179586],
      "zlevel": 0,
      "z": 2,
      "silent": false
    }
  ]
}
        chart_96803d58de4d4a719cf81099f1d10b42.setOption(option_96803d58de4d4a719cf81099f1d10b42);
    </script>
  </body>
</html>

hammerdb>jobs 640A2EA33C7603E273635 getchart result
Chart for jobid 640A2EA33C7603E273635 not available, jobid does not exist

hammerdb>

sm-shaw · 2023-04-04T09:58:17Z

PR #530 adds getchart command with examples as follows:

sm-shaw · 2023-05-09T15:06:44Z

Branch #352a is tracking these changes.

Web service has been rewritten to be able to browse jobs from both GUI and CLI.
GUI updated to enable jobs (but will add feature to disable as per CLI if desired).
As above, charts automatically generated when data such as transaction count has been captured.
Plan to add Jobs menu option to GUI to enable/disable and start service and browser.

Examples shown below:

sm-shaw · 2023-06-06T17:08:17Z

Jobs/Web Service start/stop/query functionality has been added to both the GUI and CLI

Jobs can be disabled in both GUI and CLI to prevent any job related storage in SQLite (i.e. work as before)

The browse jobs option will start the default system browser directed to the web service

Each job has a menu of related data and configuration.

Text data is shown in JSON format - for which there are a number of browser add ons/extensions

sm-shaw · 2023-06-19T09:45:22Z

@nico-robert yes I will and many thank for https://github.com/nico-robert/ticklecharts it is an awesome package.
There was one very small addition I made to save the output of the render command into a variable.
Then we can then save the html into a SQLite database alongside the data referenced by jobid for later retrieval e.g.

set html [ $bar RenderX -title "$jobid Result" ]
hdbjobs eval {INSERT INTO JOBCHART(jobid,chart,html) VALUES($jobid,'result',$html)}
return $html

all of the use of ticklecharts is in here https://github.com/TPC-Council/HammerDB/blob/master/modules/jobs-1.0.tm and all open source so can be shared if it is any use to other looking for examples of where ticklecharts has been used.
Thanks again!

nico-robert · 2023-06-19T20:21:43Z

I thought that by deleting my last message, the reference to last comment would be removed from it (echarts/issue/11135)... Thank you for posting your message , good luck with your project.
Nicolas

sm-shaw added the enhancement New feature or request label Aug 3, 2022

sm-shaw mentioned this issue Aug 18, 2022

Deprecate Web Service functionality, include features in GUI & CLI #420

Closed

sm-shaw mentioned this issue Aug 24, 2022

Enhance CLI to accept Python run scripts with .py extensions #386

Closed

sm-shaw mentioned this issue Jun 9, 2023

Enhancement for storing and retrieval of HammerDB results and configurations #558

Merged

sm-shaw linked a pull request Jun 9, 2023 that will close this issue

Enhancement for storing and retrieval of HammerDB results and configurations #558

Merged

abondvt89 closed this as completed in #558 Jun 13, 2023

sm-shaw mentioned this issue Sep 2, 2023

HammerDB CLI issue with Changing MsSQLs ODBC Driver #118

Closed

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Adding the enhancement for storing and retrieval of HammerDB results and configurations #352

Adding the enhancement for storing and retrieval of HammerDB results and configurations #352

emily-ygz commented May 6, 2022

sm-shaw commented May 6, 2022

pooja-jain-17 commented Aug 10, 2022

sm-shaw commented Aug 18, 2022

sm-shaw commented Nov 10, 2022

emily-ygz commented Nov 11, 2022

Jiang-Hua commented Nov 11, 2022

sm-shaw commented Nov 16, 2022

sm-shaw commented Mar 14, 2023

sm-shaw commented Apr 4, 2023

sm-shaw commented May 9, 2023

sm-shaw commented Jun 6, 2023

sm-shaw commented Jun 19, 2023

nico-robert commented Jun 19, 2023

Adding the enhancement for storing and retrieval of HammerDB results and configurations #352

Adding the enhancement for storing and retrieval of HammerDB results and configurations #352

Comments

emily-ygz commented May 6, 2022

sm-shaw commented May 6, 2022

pooja-jain-17 commented Aug 10, 2022

sm-shaw commented Aug 18, 2022

sm-shaw commented Nov 10, 2022

emily-ygz commented Nov 11, 2022

Jiang-Hua commented Nov 11, 2022

sm-shaw commented Nov 16, 2022

sm-shaw commented Mar 14, 2023

sm-shaw commented Apr 4, 2023

sm-shaw commented May 9, 2023

sm-shaw commented Jun 6, 2023

sm-shaw commented Jun 19, 2023

nico-robert commented Jun 19, 2023