search

TPC-Council / HammerDB

HammerDB Database Load Testing and Benchmarking Tool
http://www.hammerdb.com
GNU General Public License v3.0
594 stars 121 forks source link

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

Closed emily-ygz closed 1 year ago

emily-ygz commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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 commented 2 years ago

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

sm-shaw commented 1 year ago

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 commented 1 year ago

Sounds great, thanks Steve!

Jiang-Hua commented 1 year ago

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 commented 1 year ago

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×tamp | 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

image

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 commented 1 year ago

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>
image
sm-shaw commented 1 year ago

PR https://github.com/TPC-Council/HammerDB/pull/530 adds getchart command with examples as follows:

tprochtiming tprochresult tprocctiming tprocctcount tproccresult

sm-shaw commented 1 year ago

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:

job1 job2 job3 job4 job5 job7 job9 job10 job11
sm-shaw commented 1 year ago

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

job5

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

job6 job7

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

job1

Each job has a menu of related data and configuration.

job9 job2 job3 job4 job8

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

job10

sm-shaw commented 1 year ago

@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 commented 1 year ago

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