{"id":1348,"date":"2023-03-25T17:50:06","date_gmt":"2023-03-25T17:50:06","guid":{"rendered":"https:\/\/www.hammerdb.com\/blog\/?p=1348"},"modified":"2023-04-04T11:06:34","modified_gmt":"2023-04-04T11:06:34","slug":"hammerdb-v4-7-new-features-pt-2-example-cli-scripts","status":"publish","type":"post","link":"https:\/\/www.hammerdb.com\/blog\/uncategorized\/hammerdb-v4-7-new-features-pt-2-example-cli-scripts\/","title":{"rendered":"HammerDB v4.7 New Features Pt 2: Example CLI Scripts"},"content":{"rendered":"

With the HammerDB v4.5 Docker build, example CLI scripts were added to build and run the TPROC-C workload in the Tcl language. In HammerDB v4.6 these were enhanced to also add Python based scripts, and to include scripts for both TPROC-C and TPROC-H and a driver script for Linux environments. With HammerDB v4.7 these scripts have now moved into the main HammerDB directory to be included with all installations, rather than Docker only and a powershell driver script also added for Windows.\u00a0 This post will give an overview of the example CLI scripts that you can run directly or use as a template to write your own.<\/p>\n

When you list the HammerDB directory you can now see a directory called “scripts”. This directory contains CLI scripts to build, run, delete and query TPROC-C and TPROC-H workloads against all databases.<\/p>\n

\n\t\t\t
\n
\n
\n
<\/div>\n
<\/div>\n
<\/div>\n <\/div>\n
\n \n Copy Code<\/span>\n Copied<\/span>\n Use a different Browser<\/span><\/a>\n <\/div>\n <\/div>\n 
<\/p>\n
~\/HammerDB-4.7$ ls -1\r\nagent\r\nbin\r\nChangeLog\r\nCODE_OF_CONDUCT.md\r\nconfig\r\nhammerdb\r\nhammerdbcli\r\nhammerdbws\r\nimages\r\ninclude\r\nlib\r\nLICENSE\r\nmodules\r\nREADME.md\r\nscripts\r\nsrc<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
There is an option to run workloads in both Tcl and Python, note that these workloads underneath are identical, only the top-level interface is different and therefore there is no difference as to which language you use.<\/p>\n
Linux example<\/h2>\n
For a first example we will look at the TPROC-C workload in Tcl running on MariaDB on Linux. In this directory you can see the generic build, run, delete and result query scripts as well as the Linux and Windows driver scripts.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
~\/HammerDB-4.7\/scripts\/tcl\/maria\/tprocc$ ls -1\r\nmaria_tprocc_buildschema.tcl\r\nmaria_tprocc_deleteschema.tcl\r\nmaria_tprocc.ps1\r\nmaria_tprocc_result.tcl\r\nmaria_tprocc_run.tcl\r\nmaria_tprocc.sh<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
in the driver script you can see that the TMP environment variable is set to the local directory and that the script is intended to be run from the main HammerDB directory and will call the build, run, delete and result scripts in turn.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
export TMP=`pwd`\/TMP\r\nmkdir -p $TMP\r\n\r\necho \"BUILD HAMMERDB SCHEMA\"\r\necho \"+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-\"\r\n.\/hammerdbcli auto .\/scripts\/tcl\/maria\/tprocc\/maria_tprocc_buildschema.tcl\r\necho \"+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-\"\r\necho \"RUN HAMMERDB TEST\"\r\necho \"+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-\"\r\n.\/hammerdbcli auto .\/scripts\/tcl\/maria\/tprocc\/maria_tprocc_run.tcl\r\necho \"+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-\"\r\necho \"DROP HAMMERDB SCHEMA\"\r\n.\/hammerdbcli auto .\/scripts\/tcl\/maria\/tprocc\/maria_tprocc_deleteschema.tcl\r\necho \"+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-\"\r\necho \"HAMMERDB RESULT\"\r\n.\/hammerdbcli auto .\/scripts\/tcl\/maria\/tprocc\/maria_tprocc_result.tcl<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
First of all you should check the connection properties in your scripts and modify them to connect to your database.\u00a0 Note that by default the build script will query the number of CPUs on the system that HammerDB is running and configure 5X this number for the number of warehouses to build.\u00a0 If this value is greater or equal to 200 it will also partition the schema.\u00a0 If HammerDB is running on a separate system from the database under test then you should modify this value accordingly.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
puts \"SETTING CONFIGURATION\"\r\ndbset db maria\r\ndbset bm TPC-C\r\n\r\ndiset connection maria_host localhost\r\ndiset connection maria_port 3306\r\ndiset connection maria_socket \/tmp\/mariadb.sock\r\n\r\nset vu [ numberOfCPUs ]\r\nset warehouse [ expr {$vu * 5} ]\r\ndiset tpcc maria_count_ware $warehouse\r\ndiset tpcc maria_num_vu $vu\r\ndiset tpcc maria_user root\r\ndiset tpcc maria_pass maria\r\ndiset tpcc maria_dbase tpcc\r\ndiset tpcc maria_storage_engine innodb\r\nif { $warehouse >= 200 } {\r\ndiset tpcc maria_partition true\r\n} else {\r\ndiset tpcc maria_partition false\r\n}\r\nputs \"SCHEMA BUILD STARTED\"\r\nbuildschema\r\nputs \"SCHEMA BUILD COMPLETED\"<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
For the run script, it will run a timed test with the use all warehouses setting enabled as well as the transaction counter and time profiling running. It will create the number of virtual users to the number of CPUs on the system where HammerDB is running.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
set tmpdir $::env(TMP)\r\nputs \"SETTING CONFIGURATION\"\r\ndbset db maria\r\ndbset bm TPC-C\r\n\r\ndiset connection maria_host localhost\r\ndiset connection maria_port 3306\r\ndiset connection maria_socket \/tmp\/mariadb.sock\r\n\r\ndiset tpcc maria_user root\r\ndiset tpcc maria_pass maria\r\ndiset tpcc maria_dbase tpcc\r\ndiset tpcc maria_driver timed\r\ndiset tpcc maria_rampup 2\r\ndiset tpcc maria_duration 5\r\ndiset tpcc maria_allwarehouse true\r\ndiset tpcc maria_timeprofile true\r\n\r\nloadscript\r\nputs \"TEST STARTED\"\r\nvuset vu vcpu\r\nvucreate\r\ntcstart\r\ntcstatus\r\nset jobid [ vurun ]\r\nvudestroy\r\ntcstop\r\nputs \"TEST COMPLETE\"\r\nset of [ open $tmpdir\/maria_tprocc w ]\r\nputs $of $jobid\r\nclose $of<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
Once the workload has run, the delete script will delete the schema previously configured.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
puts \"SETTING CONFIGURATION\"\r\ndbset db maria\r\ndbset bm TPC-C\r\n\r\ndiset connection maria_host localhost\r\ndiset connection maria_port 3306\r\ndiset connection maria_socket \/tmp\/mariadb.sock\r\n\r\ndiset tpcc maria_user root\r\ndiset tpcc maria_pass maria\r\ndiset tpcc maria_dbase tpcc\r\nputs \" DROP SCHEMA STARTED\"\r\ndeleteschema\r\nputs \"DROP SCHEMA COMPLETED\"<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
and finally the result script will report the results from the test.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
set tmpdir $::env(TMP)\r\nset ::outputfile $tmpdir\/maria_tprocc\r\nsource .\/scripts\/tcl\/generic\/generic_tprocc_result.tcl<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
If we run the driver script, we see the following output (with build output truncated). Note that the NOPM\/TPM result, transaction times, response times are reported at the end of the file.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
\n
$ .\/scripts\/tcl\/maria\/tprocc\/maria_tprocc.sh\r\nBUILD HAMMERDB SCHEMA\r\n+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-\r\nHammerDB CLI v4.7\r\nCopyright (C) 2003-2023 Steve Shaw\r\nType \"help\" for a list of commands\r\nInitialized SQLite on-disk database \/home\/steve\/HammerDB-4.7\/TMP\/hammer.DB using existing tables (188,416 KB)\r\nSETTING CONFIGURATION\r\nDatabase set to MariaDB\r\nBenchmark set to TPC-C for MariaDB\r\n....\r\nALL VIRTUAL USERS COMPLETE\r\nSCHEMA BUILD COMPLETED\r\n+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-\r\nRUN HAMMERDB TEST\r\n+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-\r\nHammerDB CLI v4.7\r\nCopyright (C) 2003-2023 Steve Shaw\r\nType \"help\" for a list of commands\r\nInitialized SQLite on-disk database \/home\/steve\/HammerDB-4.7\/TMP\/hammer.DB using existing tables (180,224 KB)\r\nSETTING CONFIGURATION\r\nDatabase set to MariaDB\r\nBenchmark set to TPC-C for MariaDB\r\nValue localhost for connection:maria_host is the same as existing value localhost, no change made\r\nValue 3306 for connection:maria_port is the same as existing value 3306, no change made\r\nValue \/tmp\/mariadb.sock for connection:maria_socket is the same as existing value \/tmp\/mariadb.sock, no change made\r\nValue root for tpcc:maria_user is the same as existing value root, no change made\r\nValue maria for tpcc:maria_pass is the same as existing value maria, no change made\r\nValue tpcc for tpcc:maria_dbase is the same as existing value tpcc, no change made\r\nValue timed for tpcc:maria_driver is the same as existing value timed, no change made\r\nValue 2 for tpcc:maria_rampup is the same as existing value 2, no change made\r\nValue 5 for tpcc:maria_duration is the same as existing value 5, no change made\r\nChanged tpcc:maria_allwarehouse from false to true for MariaDB\r\nChanged tpcc:maria_timeprofile from false to true for MariaDB\r\nScript loaded, Type \"print script\" to view\r\nTEST STARTED\r\nVuser 1 created MONITOR - WAIT IDLE\r\nVuser 2 created - WAIT IDLE\r\nVuser 3 created - WAIT IDLE\r\nVuser 4 created - WAIT IDLE\r\nVuser 5 created - WAIT IDLE\r\n5 Virtual Users Created with Monitor VU\r\nTransaction Counter Started\r\nTransaction Counter thread running with threadid:tid0x7f554ffff700\r\nVuser 1:RUNNING\r\nVuser 1:Initializing xtprof time profiler\r\nVuser 1:Ssl_cipher\r\nVuser 1:Beginning rampup time of 2 minutes\r\n0 MariaDB tpm\r\nVuser 2:RUNNING\r\nVuser 2:Initializing xtprof time profiler\r\nVuser 2:Ssl_cipher\r\nVuser 2:VU 2 : Assigning WID=1 based on VU count 4, Warehouses = 20 (1 out of 5)\r\nVuser 2:VU 2 : Assigning WID=5 based on VU count 4, Warehouses = 20 (2 out of 5)\r\nVuser 2:VU 2 : Assigning WID=9 based on VU count 4, Warehouses = 20 (3 out of 5)\r\nVuser 2:VU 2 : Assigning WID=13 based on VU count 4, Warehouses = 20 (4 out of 5)\r\nVuser 2:VU 2 : Assigning WID=17 based on VU count 4, Warehouses = 20 (5 out of 5)\r\nVuser 2:Processing 10000000 transactions with output suppressed...\r\nVuser 3:RUNNING\r\nVuser 3:Initializing xtprof time profiler\r\nVuser 3:Ssl_cipher\r\nVuser 3:VU 3 : Assigning WID=2 based on VU count 4, Warehouses = 20 (1 out of 5)\r\nVuser 3:VU 3 : Assigning WID=6 based on VU count 4, Warehouses = 20 (2 out of 5)\r\nVuser 3:VU 3 : Assigning WID=10 based on VU count 4, Warehouses = 20 (3 out of 5)\r\nVuser 3:VU 3 : Assigning WID=14 based on VU count 4, Warehouses = 20 (4 out of 5)\r\nVuser 3:VU 3 : Assigning WID=18 based on VU count 4, Warehouses = 20 (5 out of 5)\r\nVuser 3:Processing 10000000 transactions with output suppressed...\r\nVuser 4:RUNNING\r\nVuser 4:Initializing xtprof time profiler\r\nVuser 4:Ssl_cipher\r\nVuser 4:VU 4 : Assigning WID=3 based on VU count 4, Warehouses = 20 (1 out of 5)\r\nVuser 4:VU 4 : Assigning WID=7 based on VU count 4, Warehouses = 20 (2 out of 5)\r\nVuser 4:VU 4 : Assigning WID=11 based on VU count 4, Warehouses = 20 (3 out of 5)\r\nVuser 4:VU 4 : Assigning WID=15 based on VU count 4, Warehouses = 20 (4 out of 5)\r\nVuser 4:VU 4 : Assigning WID=19 based on VU count 4, Warehouses = 20 (5 out of 5)\r\nVuser 4:Processing 10000000 transactions with output suppressed...\r\nVuser 5:RUNNING\r\nVuser 5:Initializing xtprof time profiler\r\nVuser 5:Ssl_cipher\r\nVuser 5:VU 5 : Assigning WID=4 based on VU count 4, Warehouses = 20 (1 out of 5)\r\nVuser 5:VU 5 : Assigning WID=8 based on VU count 4, Warehouses = 20 (2 out of 5)\r\nVuser 5:VU 5 : Assigning WID=12 based on VU count 4, Warehouses = 20 (3 out of 5)\r\nVuser 5:VU 5 : Assigning WID=16 based on VU count 4, Warehouses = 20 (4 out of 5)\r\nVuser 5:VU 5 : Assigning WID=20 based on VU count 4, Warehouses = 20 (5 out of 5)\r\nVuser 5:Processing 10000000 transactions with output suppressed...\r\n77694 MariaDB tpm\r\n90450 MariaDB tpm\r\n81822 MariaDB tpm\r\n79800 MariaDB tpm\r\n82176 MariaDB tpm\r\nVuser 1:Rampup 1 minutes complete ...\r\n81834 MariaDB tpm\r\n81972 MariaDB tpm\r\n81648 MariaDB tpm\r\n81798 MariaDB tpm\r\n81438 MariaDB tpm\r\n80508 MariaDB tpm\r\nVuser 1:Rampup 2 minutes complete ...\r\nVuser 1:Rampup complete, Taking start Transaction Count.\r\nVuser 1:Timing test period of 5 in minutes\r\n79194 MariaDB tpm\r\n80826 MariaDB tpm\r\n79920 MariaDB tpm\r\n86010 MariaDB tpm\r\n62040 MariaDB tpm\r\n52104 MariaDB tpm\r\nVuser 1:1 ...,\r\n56982 MariaDB tpm\r\n62982 MariaDB tpm\r\n62124 MariaDB tpm\r\n66990 MariaDB tpm\r\n65562 MariaDB tpm\r\n58662 MariaDB tpm\r\nVuser 1:2 ...,\r\n58428 MariaDB tpm\r\n64380 MariaDB tpm\r\n63972 MariaDB tpm\r\n64146 MariaDB tpm\r\n66588 MariaDB tpm\r\n64188 MariaDB tpm\r\nVuser 1:3 ...,\r\n65622 MariaDB tpm\r\n66576 MariaDB tpm\r\n64860 MariaDB tpm\r\n68784 MariaDB tpm\r\n70176 MariaDB tpm\r\n69798 MariaDB tpm\r\nVuser 1:4 ...,\r\n67494 MariaDB tpm\r\n70884 MariaDB tpm\r\n70260 MariaDB tpm\r\n68388 MariaDB tpm\r\n68922 MariaDB tpm\r\n67584 MariaDB tpm\r\nVuser 1:5 ...,\r\nVuser 1:Test complete, Taking end Transaction Count.\r\nVuser 1:4 Active Virtual Users configured\r\nVuser 1:TEST RESULT : System achieved 28639 NOPM from 66769 MariaDB TPM\r\nVuser 1:Gathering timing data from Active Virtual Users...\r\n68418 MariaDB tpm\r\nVuser 2:FINISHED SUCCESS\r\nVuser 4:FINISHED SUCCESS\r\nVuser 5:FINISHED SUCCESS\r\nVuser 3:FINISHED SUCCESS\r\nVuser 1:Calculating timings...\r\nVuser 1:Writing timing data to \/home\/steve\/HammerDB-4.7\/TMP\/hdbxtprofile.log\r\nVuser 1:FINISHED SUCCESS\r\nALL VIRTUAL USERS COMPLETE\r\nvudestroy success\r\nTransaction Counter thread running with threadid:tid0x7f554ffff700\r\nStopping Transaction Counter\r\nTEST COMPLETE\r\n+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-\r\nDROP HAMMERDB SCHEMA\r\nHammerDB CLI v4.7\r\nCopyright (C) 2003-2023 Steve Shaw\r\nType \"help\" for a list of commands\r\nInitialized SQLite on-disk database \/home\/steve\/HammerDB-4.7\/TMP\/hammer.DB using existing tables (188,416 KB)\r\nSETTING CONFIGURATION\r\nDatabase set to MariaDB\r\nBenchmark set to TPC-C for MariaDB\r\nValue localhost for connection:maria_host is the same as existing value localhost, no change made\r\nValue 3306 for connection:maria_port is the same as existing value 3306, no change made\r\nValue \/tmp\/mariadb.sock for connection:maria_socket is the same as existing value \/tmp\/mariadb.sock, no change made\r\nValue root for tpcc:maria_user is the same as existing value root, no change made\r\nValue maria for tpcc:maria_pass is the same as existing value maria, no change made\r\nValue tpcc for tpcc:maria_dbase is the same as existing value tpcc, no change made\r\nDROP SCHEMA STARTED\r\nScript cleared\r\nDeleting schema with 1 Virtual User\r\nDo you want to delete the TPCC TPROC-C schema\r\nin host LOCALHOST:\/TMP\/MARIADB.SOCK under user ROOT?\r\nEnter yes or no: replied yes\r\nVuser 1 created - WAIT IDLE\r\nVuser 1:RUNNING\r\nVuser 1:Ssl_cipher\r\nDROP SCHEMA COMPLETED\r\n+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-\r\nHAMMERDB RESULT\r\nHammerDB CLI v4.7\r\nCopyright (C) 2003-2023 Steve Shaw\r\nType \"help\" for a list of commands\r\nInitialized SQLite on-disk database \/home\/steve\/HammerDB-4.7\/TMP\/hammer.DB using existing tables (188,416 KB)\r\nTRANSACTION RESPONSE TIMES\r\n{\r\n\"NEWORD\": {\r\n\"elapsed_ms\": \"419814.0\",\r\n\"calls\": \"54189\",\r\n\"min_ms\": \"1.35\",\r\n\"avg_ms\": \"4.197\",\r\n\"max_ms\": \"191.359\",\r\n\"total_ms\": \"227425.383\",\r\n\"p99_ms\": \"14.25\",\r\n\"p95_ms\": \"6.883\",\r\n\"p50_ms\": \"3.788\",\r\n\"sd\": \"3363.096\",\r\n\"ratio_pct\": \"54.162\"\r\n},\r\n\"PAYMENT\": {\r\n\"elapsed_ms\": \"419814.0\",\r\n\"calls\": \"54597\",\r\n\"min_ms\": \"0.655\",\r\n\"avg_ms\": \"1.647\",\r\n\"max_ms\": \"121.584\",\r\n\"total_ms\": \"89933.834\",\r\n\"p99_ms\": \"6.288\",\r\n\"p95_ms\": \"2.984\",\r\n\"p50_ms\": \"1.424\",\r\n\"sd\": \"1521.734\",\r\n\"ratio_pct\": \"21.418\"\r\n},\r\n\"DELIVERY\": {\r\n\"elapsed_ms\": \"419814.0\",\r\n\"calls\": \"5389\",\r\n\"min_ms\": \"4.782\",\r\n\"avg_ms\": \"13.291\",\r\n\"max_ms\": \"225.309\",\r\n\"total_ms\": \"71627.506\",\r\n\"p99_ms\": \"91.928\",\r\n\"p95_ms\": \"27.917\",\r\n\"p50_ms\": \"9.796\",\r\n\"sd\": \"14775.748\",\r\n\"ratio_pct\": \"17.058\"\r\n},\r\n\"OSTAT\": {\r\n\"elapsed_ms\": \"419814.0\",\r\n\"calls\": \"5386\",\r\n\"min_ms\": \"0.488\",\r\n\"avg_ms\": \"2.335\",\r\n\"max_ms\": \"139.762\",\r\n\"total_ms\": \"12574.798\",\r\n\"p99_ms\": \"40.093\",\r\n\"p95_ms\": \"4.248\",\r\n\"p50_ms\": \"1.153\",\r\n\"sd\": \"7285.123\",\r\n\"ratio_pct\": \"2.995\"\r\n},\r\n\"SLEV\": {\r\n\"elapsed_ms\": \"419814.0\",\r\n\"calls\": \"5367\",\r\n\"min_ms\": \"0.904\",\r\n\"avg_ms\": \"1.949\",\r\n\"max_ms\": \"93.56\",\r\n\"total_ms\": \"10459.164\",\r\n\"p99_ms\": \"6.379\",\r\n\"p95_ms\": \"3.1\",\r\n\"p50_ms\": \"1.698\",\r\n\"sd\": \"1775.819\",\r\n\"ratio_pct\": \"2.491\"\r\n}\r\n}\r\n\r\nTRANSACTION COUNT\r\n{\"MariaDB tpm\": {\r\n\"0\": \"2023-03-24 14:35:24\",\r\n\"77694\": \"2023-03-24 14:35:34\",\r\n\"90450\": \"2023-03-24 14:35:44\",\r\n\"81822\": \"2023-03-24 14:35:54\",\r\n\"79800\": \"2023-03-24 14:36:04\",\r\n\"82176\": \"2023-03-24 14:36:14\",\r\n\"81834\": \"2023-03-24 14:36:24\",\r\n\"81972\": \"2023-03-24 14:36:34\",\r\n\"81648\": \"2023-03-24 14:36:44\",\r\n\"81798\": \"2023-03-24 14:36:54\",\r\n\"81438\": \"2023-03-24 14:37:04\",\r\n\"80508\": \"2023-03-24 14:37:14\",\r\n\"79194\": \"2023-03-24 14:37:24\",\r\n\"80826\": \"2023-03-24 14:37:34\",\r\n\"79920\": \"2023-03-24 14:37:44\",\r\n\"86010\": \"2023-03-24 14:37:54\",\r\n\"62040\": \"2023-03-24 14:38:04\",\r\n\"52104\": \"2023-03-24 14:38:14\",\r\n\"56982\": \"2023-03-24 14:38:24\",\r\n\"62982\": \"2023-03-24 14:38:34\",\r\n\"62124\": \"2023-03-24 14:38:44\",\r\n\"66990\": \"2023-03-24 14:38:54\",\r\n\"65562\": \"2023-03-24 14:39:04\",\r\n\"58662\": \"2023-03-24 14:39:14\",\r\n\"58428\": \"2023-03-24 14:39:24\",\r\n\"64380\": \"2023-03-24 14:39:34\",\r\n\"63972\": \"2023-03-24 14:39:44\",\r\n\"64146\": \"2023-03-24 14:39:54\",\r\n\"66588\": \"2023-03-24 14:40:04\",\r\n\"64188\": \"2023-03-24 14:40:14\",\r\n\"65622\": \"2023-03-24 14:40:24\",\r\n\"66576\": \"2023-03-24 14:40:34\",\r\n\"64860\": \"2023-03-24 14:40:44\",\r\n\"68784\": \"2023-03-24 14:40:54\",\r\n\"70176\": \"2023-03-24 14:41:04\",\r\n\"69798\": \"2023-03-24 14:41:14\",\r\n\"67494\": \"2023-03-24 14:41:24\",\r\n\"70884\": \"2023-03-24 14:41:34\",\r\n\"70260\": \"2023-03-24 14:41:44\",\r\n\"68388\": \"2023-03-24 14:41:54\",\r\n\"68922\": \"2023-03-24 14:42:04\",\r\n\"67584\": \"2023-03-24 14:42:14\",\r\n\"68418\": \"2023-03-24 14:42:24\"\r\n}}\r\n\r\nHAMMERDB RESULT\r\n[\r\n\"641DB52C5F7A03E213335373\",\r\n\"2023-03-24 14:35:24\",\r\n\"4 Active Virtual Users configured\",\r\n\"TEST RESULT : System achieved 28639 NOPM from 66769 MariaDB TPM\"\r\n]<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>The output is also stored in the TMP directory in a file identifed by the jobid.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
~\/HammerDB-4.7\/TMP$ more maria_tprocc_641DB52C5F7A03E213335373.out\r\nTRANSACTION RESPONSE TIMES\r\n{\r\n\"NEWORD\": {\r\n\"elapsed_ms\": \"419814.0\",\r\n\"calls\": \"54189\",\r\n\"min_ms\": \"1.35\",\r\n\"avg_ms\": \"4.197\",\r\n\"max_ms\": \"191.359\",\r\n\"total_ms\": \"227425.383\",\r\n\"p99_ms\": \"14.25\",\r\n\"p95_ms\": \"6.883\",\r\n\"p50_ms\": \"3.788\",\r\n\"sd\": \"3363.096\",\r\n\"ratio_pct\": \"54.162\"\r\n},\r\n\"PAYMENT\": {\r\n\"elapsed_ms\": \"419814.0\",\r\n\"calls\": \"54597\",\r\n\"min_ms\": \"0.655\",\r\n\"avg_ms\": \"1.647\",\r\n\"max_ms\": \"121.584\",\r\n\"total_ms\": \"89933.834\",\r\n\"p99_ms\": \"6.288\",\r\n\"p95_ms\": \"2.984\",\r\n\"p50_ms\": \"1.424\",\r\n\"sd\": \"1521.734\",\r\n\"ratio_pct\": \"21.418\"\r\n},\r\n\"DELIVERY\": {\r\n\"elapsed_ms\": \"419814.0\",\r\n\"calls\": \"5389\",\r\n\"min_ms\": \"4.782\",\r\n\"avg_ms\": \"13.291\",\r\n\"max_ms\": \"225.309\",\r\n\"total_ms\": \"71627.506\",\r\n\"p99_ms\": \"91.928\",\r\n\"p95_ms\": \"27.917\",\r\n\"p50_ms\": \"9.796\",\r\n\"sd\": \"14775.748\",\r\n\"ratio_pct\": \"17.058\"\r\n},\r\n\"OSTAT\": {\r\n\"elapsed_ms\": \"419814.0\",\r\n\"calls\": \"5386\",\r\n\"min_ms\": \"0.488\",\r\n\"avg_ms\": \"2.335\",\r\n\"max_ms\": \"139.762\",\r\n\"total_ms\": \"12574.798\",\r\n\"p99_ms\": \"40.093\",\r\n\"p95_ms\": \"4.248\",\r\n\"p50_ms\": \"1.153\",\r\n\"sd\": \"7285.123\",\r\n\"ratio_pct\": \"2.995\"\r\n},\r\n\"SLEV\": {\r\n\"elapsed_ms\": \"419814.0\",\r\n\"calls\": \"5367\",\r\n\"min_ms\": \"0.904\",\r\n\"avg_ms\": \"1.949\",\r\n\"max_ms\": \"93.56\",\r\n\"total_ms\": \"10459.164\",\r\n\"p99_ms\": \"6.379\",\r\n\"p95_ms\": \"3.1\",\r\n\"p50_ms\": \"1.698\",\r\n\"sd\": \"1775.819\",\r\n\"ratio_pct\": \"2.491\"\r\n}\r\n}\r\n\r\nTRANSACTION COUNT\r\n{\"MariaDB tpm\": {\r\n\"0\": \"2023-03-24 14:35:24\",\r\n\"77694\": \"2023-03-24 14:35:34\",\r\n\"90450\": \"2023-03-24 14:35:44\",\r\n\"81822\": \"2023-03-24 14:35:54\",\r\n\"79800\": \"2023-03-24 14:36:04\",\r\n\"82176\": \"2023-03-24 14:36:14\",\r\n\"81834\": \"2023-03-24 14:36:24\",\r\n\"81972\": \"2023-03-24 14:36:34\",\r\n\"81648\": \"2023-03-24 14:36:44\",\r\n\"81798\": \"2023-03-24 14:36:54\",\r\n\"81438\": \"2023-03-24 14:37:04\",\r\n\"80508\": \"2023-03-24 14:37:14\",\r\n\"79194\": \"2023-03-24 14:37:24\",\r\n\"80826\": \"2023-03-24 14:37:34\",\r\n\"79920\": \"2023-03-24 14:37:44\",\r\n\"86010\": \"2023-03-24 14:37:54\",\r\n\"62040\": \"2023-03-24 14:38:04\",\r\n\"52104\": \"2023-03-24 14:38:14\",\r\n\"56982\": \"2023-03-24 14:38:24\",\r\n\"62982\": \"2023-03-24 14:38:34\",\r\n\"62124\": \"2023-03-24 14:38:44\",\r\n\"66990\": \"2023-03-24 14:38:54\",\r\n\"65562\": \"2023-03-24 14:39:04\",\r\n\"58662\": \"2023-03-24 14:39:14\",\r\n\"58428\": \"2023-03-24 14:39:24\",\r\n\"64380\": \"2023-03-24 14:39:34\",\r\n\"63972\": \"2023-03-24 14:39:44\",\r\n\"64146\": \"2023-03-24 14:39:54\",\r\n\"66588\": \"2023-03-24 14:40:04\",\r\n\"64188\": \"2023-03-24 14:40:14\",\r\n\"65622\": \"2023-03-24 14:40:24\",\r\n\"66576\": \"2023-03-24 14:40:34\",\r\n\"64860\": \"2023-03-24 14:40:44\",\r\n\"68784\": \"2023-03-24 14:40:54\",\r\n\"70176\": \"2023-03-24 14:41:04\",\r\n\"69798\": \"2023-03-24 14:41:14\",\r\n\"67494\": \"2023-03-24 14:41:24\",\r\n\"70884\": \"2023-03-24 14:41:34\",\r\n\"70260\": \"2023-03-24 14:41:44\",\r\n\"68388\": \"2023-03-24 14:41:54\",\r\n\"68922\": \"2023-03-24 14:42:04\",\r\n\"67584\": \"2023-03-24 14:42:14\",\r\n\"68418\": \"2023-03-24 14:42:24\"\r\n}}\r\n\r\nHAMMERDB RESULT\r\n[\r\n\"641DB52C5F7A03E213335373\",\r\n\"2023-03-24 14:35:24\",\r\n\"4 Active Virtual Users configured\",\r\n\"TEST RESULT : System achieved 28639 NOPM from 66769 MariaDB TPM\"\r\n]<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
Note that the jobs are stored in the SQLite database in the configured TMP directory and therefore this can be queried with the jobs command to view all of the data stored for the job such as the dict and the response times for all running virtual users.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
$ export TMP=`pwd`\/TMP\r\n$ .\/hammerdbcli\r\nHammerDB CLI v4.7\r\nCopyright (C) 2003-2023 Steve Shaw\r\nType \"help\" for a list of commands\r\nInitialized SQLite on-disk database \/home\/steve\/HammerDB-4.7\/TMP\/hammer.DB using existing tables (188,416 KB)\r\n\r\nhammerdb>jobs result\r\n[\r\n\"641DB40C5F7A03E273636303\",\r\n\"Jobid has no test result\"\r\n]\r\n\r\n[\r\n\"641DB52C5F7A03E213335373\",\r\n\"2023-03-24 14:35:24\",\r\n\"4 Active Virtual Users configured\",\r\n\"TEST RESULT : System achieved 28639 NOPM from 66769 MariaDB TPM\"\r\n]<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
Windows example<\/h2>\n
For a Windows example we will look at the Python scripts for TPROC-H running against SQL Server.<\/p>\n
\"\"<\/a><\/p>\n
Similarly to Linux, set the connection settings in all scripts.\u00a0 To edit the scripts within the Program Files directory run an editor such as Notepad as administrator.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
#!\/bin\/tclsh\r\n# maintainer: Pooja Jain\r\n\r\nprint(\"SETTING CONFIGURATION\")\r\ndbset('db','mssqls')\r\ndbset('bm','TPC-H')\r\n\r\ndiset('connection','mssqls_tcp','false')\r\ndiset('connection','mssqls_port','1433')\r\ndiset('connection','mssqls_azure','false')\r\ndiset('connection','mssqls_encrypt_connection','true')\r\ndiset('connection','mssqls_trust_server_cert','true')\r\ndiset('connection','mssqls_authentication','windows')\r\ndiset('connection','mssqls_server','{(local)\\SQLDEVELOP}')\r\ndiset('connection','mssqls_linux_server','{localhost}')\r\ndiset('connection','mssqls_linux_authent','sql')\r\ndiset('connection','mssqls_linux_odbc','{ODBC Driver 18 for SQL Server}')\r\ndiset('connection','mssqls_uid','sa')\r\ndiset('connection','mssqls_pass','admin')\r\n\r\nvu = tclpy.eval('numberOfCPUs')\r\ndiset('tpch','mssqls_num_tpch_threads',vu)\r\ndiset('tpch','mssqls_scale_fact','1')\r\ndiset('tpch','mssqls_maxdop','2')\r\ndiset('tpch','mssqls_tpch_dbase','tpch')\r\ndiset('tpch','mssqls_colstore','false')\r\n\r\nprint(\"SCHEMA BUILD STARTED\")\r\nbuildschema()\r\nprint(\"SCHEMA BUILD COMPLETED\")\r\nexit()<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
Note that by default running powershell scripts is disabled<\/a> and therefore to run the HammerDB scripts on Windows this functionality should be enabled as follows.<\/p>\n
\"\"<\/a><\/p>\n
To run the workload, call the powershell script from the HammerDB home directory.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
C:\\Program Files\\HammerDB-4.7>powershell .\\scripts\\python\\mssqls\\tproch\\mssqls_tproch_py.ps1<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
As on Linux, the workload will build, run, delete and report the results of the workload.<\/p>\n
\n\t\t\t
\n                
\n                    
\n                        
<\/div>\n                        
<\/div>\n                        
<\/div>\n                    <\/div>\n                    
\n                        \n                        Copy Code<\/span>\n                        Copied<\/span>\n                        Use a different Browser<\/span><\/a>\n                    <\/div>\n                <\/div>\n                
<\/p>\n
HAMMERDB RESULT\r\nHammerDB CLI v4.7\r\nCopyright (C) 2003-2023 Steve Shaw\r\nType \"help()\" for a list of commands\r\nHAMMERDB RESULT\r\n[\r\n  \"1\",\r\n  \"Executing Query 14 (1 of 22)\",\r\n  \"1\",\r\n  \"query 14 completed in 0.136 seconds\",\r\n  \"1\",\r\n  \"Executing Query 2 (2 of 22)\",\r\n  \"1\",\r\n  \"query 2 completed in 0.229 seconds\",\r\n  \"1\",\r\n  \"Executing Query 9 (3 of 22)\",\r\n  \"1\",\r\n  \"query 9 completed in 1.206 seconds\",\r\n  \"1\",\r\n  \"Executing Query 20 (4 of 22)\",\r\n  \"1\",\r\n  \"query 20 completed in 0.195 seconds\",\r\n  \"1\",\r\n  \"Executing Query 6 (5 of 22)\",\r\n  \"1\",\r\n  \"query 6 completed in 0.283 seconds\",\r\n  \"1\",\r\n  \"Executing Query 17 (6 of 22)\",\r\n  \"1\",\r\n  \"query 17 completed in 0.191 seconds\",\r\n  \"1\",\r\n  \"Executing Query 18 (7 of 22)\",\r\n  \"1\",\r\n  \"query 18 completed in 0.601 seconds\",\r\n  \"1\",\r\n  \"Executing Query 8 (8 of 22)\",\r\n  \"1\",\r\n  \"query 8 completed in 0.693 seconds\",\r\n  \"1\",\r\n  \"Executing Query 21 (9 of 22)\",\r\n  \"1\",\r\n  \"query 21 completed in 1.045 seconds\",\r\n  \"1\",\r\n  \"Executing Query 13 (10 of 22)\",\r\n  \"1\",\r\n  \"query 13 completed in 2.973 seconds\",\r\n  \"1\",\r\n  \"Executing Query 3 (11 of 22)\",\r\n  \"1\",\r\n  \"query 3 completed in 0.279 seconds\",\r\n  \"1\",\r\n  \"Executing Query 22 (12 of 22)\",\r\n  \"1\",\r\n  \"query 22 completed in 0.276 seconds\",\r\n  \"1\",\r\n  \"Executing Query 16 (13 of 22)\",\r\n  \"1\",\r\n  \"query 16 completed in 0.383 seconds\",\r\n  \"1\",\r\n  \"Executing Query 4 (14 of 22)\",\r\n  \"1\",\r\n  \"query 4 completed in 0.33 seconds\",\r\n  \"1\",\r\n  \"Executing Query 11 (15 of 22)\",\r\n  \"1\",\r\n  \"query 11 completed in 0.094 seconds\",\r\n  \"1\",\r\n  \"Executing Query 15 (16 of 22)\",\r\n  \"1\",\r\n  \"query 15 completed in 0.18 seconds\",\r\n  \"1\",\r\n  \"Executing Query 1 (17 of 22)\",\r\n  \"1\",\r\n  \"query 1 completed in 0.997 seconds\",\r\n  \"1\",\r\n  \"Executing Query 10 (18 of 22)\",\r\n  \"1\",\r\n  \"query 10 completed in 0.356 seconds\",\r\n  \"1\",\r\n  \"Executing Query 19 (19 of 22)\",\r\n  \"1\",\r\n  \"query 19 completed in 0.516 seconds\",\r\n  \"1\",\r\n  \"Executing Query 5 (20 of 22)\",\r\n  \"1\",\r\n  \"query 5 completed in 0.408 seconds\",\r\n  \"1\",\r\n  \"Executing Query 7 (21 of 22)\",\r\n  \"1\",\r\n  \"query 7 completed in 0.263 seconds\",\r\n  \"1\",\r\n  \"Executing Query 12 (22 of 22)\",\r\n  \"1\",\r\n  \"query 12 completed in 0.403 seconds\",\r\n  \"1\",\r\n  \"Completed 1 query set(s) in 12 seconds\",\r\n  \"1\",\r\n  \"Geometric mean of query times returning rows (22) is \\\"0.38278\\\"\"\r\n]<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
The temp directory used is the same default temp directory on Windows and therefore the output file can be found in this location.<\/p>\n
\"\"<\/a><\/p>\n
\"\"<\/a><\/p>\n
Summary<\/h2>\n
The example scripts and driver scripts are intended as templates to show what you can do with your own HammerDB environment to build, run and delete workloads and query the results and configuration.<\/p>\n","protected":false},"excerpt":{"rendered":"
With the HammerDB v4.5 Docker build, example CLI scripts were added to build and run the TPROC-C workload in the Tcl language. In HammerDB v4.6 these were enhanced to also add Python based scripts, and to include scripts for both TPROC-C and TPROC-H and a driver script for Linux environments. With HammerDB v4.7 these scripts … <\/p>\n
Continue reading “HammerDB v4.7 New Features Pt 2: Example CLI Scripts”<\/span><\/a><\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"ppma_author":[5],"class_list":["post-1348","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"authors":[{"term_id":5,"user_id":2,"is_guest":0,"slug":"hammerdb","display_name":"HammerDB","avatar_url":{"url":"https:\/\/www.hammerdb.com\/blog\/wp-content\/uploads\/2018\/10\/logo-white.png","url2x":"https:\/\/www.hammerdb.com\/blog\/wp-content\/uploads\/2018\/10\/logo-white.png"},"author_category":"","user_url":"http:\/\/www.hammerdb.com","last_name":"","first_name":"","job_title":"","description":""}],"_links":{"self":[{"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/posts\/1348","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/comments?post=1348"}],"version-history":[{"count":7,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/posts\/1348\/revisions"}],"predecessor-version":[{"id":1360,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/posts\/1348\/revisions\/1360"}],"wp:attachment":[{"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/media?parent=1348"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/categories?post=1348"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/tags?post=1348"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/ppma_author?post=1348"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}