{"id":1713,"date":"2024-07-06T15:51:36","date_gmt":"2024-07-06T15:51:36","guid":{"rendered":"https:\/\/www.hammerdb.com\/blog\/?p=1713"},"modified":"2024-07-06T15:51:36","modified_gmt":"2024-07-06T15:51:36","slug":"comparing-hammerdb-tproc-c-results-with-sysbench-tpcc","status":"publish","type":"post","link":"https:\/\/www.hammerdb.com\/blog\/uncategorized\/comparing-hammerdb-tproc-c-results-with-sysbench-tpcc\/","title":{"rendered":"Comparing HammerDB TPROC-C results with sysbench-tpcc"},"content":{"rendered":"

In a recent project comparing systems for MariaDB performance, a user had originally been using a tool called sysbench-tpcc<\/a> to compare hardware platforms before migrating to HammerDB.\u00a0 However, the user was not aware that the performance could be compared between the 2. This is a brief post to highlight the metrics to use to do the comparison using a separate hardware platform for illustration purposes.<\/p>\n

Firstly, it is worth noting that both HammerDB TPROC-C and sysbench-tpcc run workloads based on the TPC-C specification, however as described here<\/a> HammerDB is called TPROC-C to correctly comply with the TPC fair use rules.\u00a0 <\/a><\/p>\n

Also note that whereas HammerDB offers a feature to do a fixed throughput workload close to the specification. In this case, we will only show the workloads run without keying and thinking time, as only HammerDB offers both. HammerDB also runs natively on Windows and Linux with GUI, CLI and Web interfaces on multiple databases, but in this case the example will be on MariaDB on Linux with the CLI.<\/p>\n

Prepare or build the schema<\/h2>\n

Firstly, before running a workload, you need to build or prepare the schema. sysbench-tpcc offers the ability to build multiple schemas to work around scalability issues, however the TPC-C specification uses a single set of tables which can be built as follows.<\/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
.\/tpcc.lua --mysql-socket=\/tmp\/mariadb.sock --mysql-user=root --mysql-password=maria --mysql-db=tpccsb --time=300 --threads=64 --report-interval=1 --tables=1 --scale=400 --db-driver=mysql prepare<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
The equivalent in HammerDB is the buildschema command, with example settings as below. The scripts can be in Python or Tcl format.<\/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\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\nset vu 100\r\nset warehouse 400\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
and run as follows from the command line.<\/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
 .\/hammerdbcli auto .\/scripts\/tcl\/maria\/tprocc\/maria_tprocc_build.tcl<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
You can see that both schemas are similar when built, with the main difference being that sysbench adds prefixes to a number of columns to aid with compression whereas HammerDB is closer to the specification.<\/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
MariaDB [tpccsb]> show tables;\r\n+------------------+\r\n| Tables_in_tpccsb |\r\n+------------------+\r\n| customer1        |\r\n| district1        |\r\n| history1         |\r\n| item1            |\r\n| new_orders1      |\r\n| order_line1      |\r\n| orders1          |\r\n| stock1           |\r\n| warehouse1       |\r\n+------------------+\r\n9 rows in set (0.000 sec)\r\n\r\nMariaDB [tpccsb]> select * from warehouse1 limit 1;\r\n+------+------------+--------------------+--------------------+-----------------+---------+-----------+-------+------------+\r\n| w_id | w_name     | w_street_1         | w_street_2         | w_city          | w_state | w_zip     | w_tax | w_ytd      |\r\n+------+------------+--------------------+--------------------+-----------------+---------+-----------+-------+------------+\r\n|    1 | name-ussgn | street1-suwfdxnitk | street2-sdptwkrcjd | city-wowgpzhpmq | fu      | zip-12460 |  0.12 | 8398416.00 |\r\n+------+------------+--------------------+--------------------+-----------------+---------+-----------+-------+------------+\r\n\r\nMariaDB [tpcc]> show tables;\r\n+----------------+\r\n| Tables_in_tpcc |\r\n+----------------+\r\n| customer       |\r\n| district       |\r\n| history        |\r\n| item           |\r\n| new_order      |\r\n| order_line     |\r\n| orders         |\r\n| stock          |\r\n| warehouse      |\r\n+----------------+\r\n9 rows in set (0.000 sec)\r\n\r\nMariaDB [tpcc]> select * from warehouse limit 1;\r\n+------+-----------+--------+----------+---------------+---------------------+--------------------+---------+-----------+\r\n| w_id | w_ytd     | w_tax  | w_name   | w_street_1    | w_street_2          | w_city             | w_state | w_zip     |\r\n+------+-----------+--------+----------+---------------+---------------------+--------------------+---------+-----------+\r\n|    1 | 300000.00 | 0.1800 | kyKhVJqn | ukYR4HaaEJLVi | icFhnjwgqE3cexTJFwR | Kxf1T7pcaHNyvELEIx | lH      | 358511111 |\r\n+------+-----------+--------+----------+---------------+---------------------+--------------------+---------+-----------+<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
Running the workloads<\/h2>\n
We can run the workloads as follows, and in the example on both we will use 80 threads or Virtual Users in HammerDB terminology. To run the workload on sysbench-tpcc is the following.<\/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
.\/tpcc.lua --mysql-socket=\/tmp\/mariadb.sock --mysql-user=root --mysql-password=maria --mysql-db=tpccsb --time=300 --threads=80 --report-interval=1 --tables=1 --scale=400 --db-driver=mysql run<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
As the workloads are based on the same specification, you can use HammerDB to monitor the sysbench-tpcc workload with the metstart command starting the CPU monitor and tcstart the transaction counter.<\/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>metstart\r\nStarting Local Metrics Agent on ubuntu\r\nafter#1\r\nhammerdb>Connecting to Agent to Display CPU Metrics\r\nMetric receive port open @ 27702 on ubuntu\r\nConnecting to HammerDB Agent @ localhost:10000\r\nTesting Agent Connectivity...OK\r\nMetrics Connected\r\nStarted CPU Metrics for Intel(R) Xeon(R) Platinum 8280L CPU @ 2.70GHz:(112 CPUs)\r\nhammerdb>tcstart\r\nTransaction Counter Started\r\n\r\nhammerdb>0 MariaDB tpm\r\nCPU all usr%-0.00 sys%-0.02 irq%-0.00 idle%-99.97\r\n0 MariaDB tpm<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
and a HammerDB example script as follows:<\/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\nset tmpdir $::env(TMP)\r\nputs \"SETTING CONFIGURATION\"\r\njobs profileid 0\r\ndbset db maria\r\ndbset bm TPC-C\r\ngiset timeprofile xt_gather_timeout 1200\r\ngiset commandline keepalive_margin 1200\r\n\r\ndiset connection maria_host 127.0.0.1\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_no_stored_procs false\r\ndiset tpcc maria_allwarehouse false\r\ndiset tpcc maria_timeprofile true\r\ndiset tpcc maria_purge true\r\n#start CPU\r\nmetstart\r\nputs \"TEST STARTED\"\r\nloadscript\r\nvuset vu 80\r\nvuset logtotemp 1\r\nvucreate\r\ntcstart\r\ntcstatus\r\nvurun\r\ntcstop\r\nvudestroy\r\n#stop CPU\r\nmetstop\r\nputs \"TEST COMPLETE\"<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
Note that a key parameter here is setting maria_no_stored_procs to true or false. HammerDB uses stored procedures for higher throughput as fully explained here<\/a>, but also offers a client SQL based version for comparison, sysbench implements a client SQL based version and therefore this is a key understanding for the difference between the 2 workloads. The HammerDB workload is run as shown:<\/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
.\/hammerdbcli auto .\/scripts\/tcl\/maria\/tprocc\/maria_tprocc_run.tcl<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
Comparing the results<\/h2>\n
When running the 80 thread sysbench-tpcc workload, monitoring with HammerDB we can see the following output.<\/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>0 MariaDB tpm\r\nCPU all usr%-17.58 sys%-4.18 irq%-0.00 idle%-78.21\r\n485784 MariaDB tpm\r\nCPU all usr%-17.87 sys%-4.30 irq%-0.00 idle%-77.80\r\n497376 MariaDB tpm\r\nCPU all usr%-17.79 sys%-4.36 irq%-0.00 idle%-77.84\r\n478644 MariaDB tpm\r\nCPU all usr%-17.74 sys%-4.35 irq%-0.00 idle%-77.88\r\n485718 MariaDB tpm<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
and when it has finshed it prints output as follows:<\/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
SQL statistics:\r\n    queries performed:\r\n        read:                            31779084\r\n        write:                           32983004\r\n        other:                           4898362\r\n        total:                           69660450\r\n    transactions:                        2449061 (8162.57 per sec.)\r\n    queries:                             69660450 (232173.91 per sec.)\r\n    ignored errors:                      10685  (35.61 per sec.)\r\n    reconnects:                          0      (0.00 per sec.)\r\n\r\nThroughput:\r\n    events\/s (eps):                      8162.5668\r\n    time elapsed:                        300.0356s\r\n    total number of events:              2449061\r\n\r\nLatency (ms):\r\n         min:                                    0.35\r\n         avg:                                    9.80\r\n         max:                                  307.66\r\n         95th percentile:                       41.10\r\n         sum:                             23997083.58\r\n\r\nThreads fairness:\r\n    events (avg\/stddev):           30613.2625\/297.35\r\n    execution time (avg\/stddev):   299.9635\/0.01<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
The key figure here is 8162.57 per sec, multiplied by 60 gives us 489,754 TPM (transactions per minute) and is the figure we can use for comparison as can be seen from the HammerDB transaction output giving the same data.<\/p>\n
Running HammerDB with stored procedures we can see the difference in CPU utilisation and transactions.<\/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
CPU all usr%-52.76 sys%-6.52 irq%-0.00 idle%-40.52\r\n1512462 MariaDB tpm\r\nCPU all usr%-52.85 sys%-6.43 irq%-0.00 idle%-40.55\r\n1519824 MariaDB tpm\r\nCPU all usr%-53.01 sys%-6.47 irq%-0.00 idle%-40.35\r\n1515888 MariaDB tpm\r\nCPU all usr%-52.99 sys%-6.36 irq%-0.00 idle%-40.51\r\n1524312 MariaDB tpm<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
and it is the TPM value that we use for comparison and not NOPM, as both tools are measuring transactions per second\/minute.<\/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
Vuser 1:80 Active Virtual Users configured\r\nVuser 1:TEST RESULT : System achieved 632885 NOPM from 1471425 MariaDB TPM<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
if we use the HammerDB no stored procedures option we can see that performance drops as would be expected.<\/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
Vuser 1:Test complete, Taking end Transaction Count.\r\nVuser 1:80 Active Virtual Users configured\r\nVuser 1:TEST RESULT : System achieved 491964 NOPM from 1143635 MariaDB TPM<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
and refering to the previous article we can see that we are using more system time as we are now spending more time in the network.<\/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
1155714 MariaDB tpm\r\nCPU all usr%-52.57 sys%-11.44 irq%-0.00 idle%-35.90\r\n1163382 MariaDB tpm\r\nCPU all usr%-52.73 sys%-11.57 irq%-0.00 idle%-35.61\r\n1154976 MariaDB tpm\r\nCPU all usr%-52.60 sys%-11.71 irq%-0.00 idle%-35.60\r\n1153836 MariaDB tpm\r\nCPU all usr%-52.56 sys%-11.67 irq%-0.00 idle%-35.68<\/pre>\n
<\/code><\/pre>\n\t\t\t<\/div>\n        <\/div>\n
Analyzing results<\/h2>\n
HammerDB will also automatically generate graphs for you to analyze your workload and detailed response times per transaction.<\/p>\n
\"\"<\/a> \"\"<\/a> \"\"<\/a> \"\"<\/a><\/h2>\n
Summary<\/h2>\n
Of course the more benchmarks and workloads you run against a system, the more insights you can get<\/a>. All benchmarks are valuable, however it is important to ensure that you deriving accurate results.<\/p>\n
In our example a user was initially using sysbench-tpcc to compare different hardware systems for MariaDB however was drawing conclusions about both the hardware and database software capabilities that was not in keeping with our observations. Using this approach we provided an alternative measurement and showed how both approaches compared to illustrate the capabilities of both the hardware and software.<\/p>\n
 <\/p>\n","protected":false},"excerpt":{"rendered":"
In a recent project comparing systems for MariaDB performance, a user had originally been using a tool called sysbench-tpcc to compare hardware platforms before migrating to HammerDB.\u00a0 However, the user was not aware that the performance could be compared between the 2. This is a brief post to highlight the metrics to use to do … <\/p>\n
Continue reading “Comparing HammerDB TPROC-C results with sysbench-tpcc”<\/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-1713","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\/1713","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=1713"}],"version-history":[{"count":9,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/posts\/1713\/revisions"}],"predecessor-version":[{"id":1727,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/posts\/1713\/revisions\/1727"}],"wp:attachment":[{"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/media?parent=1713"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/categories?post=1713"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/tags?post=1713"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/ppma_author?post=1713"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}