{"id":1047,"date":"2021-07-16T15:55:45","date_gmt":"2021-07-16T15:55:45","guid":{"rendered":"https:\/\/www.hammerdb.com\/blog\/?p=1047"},"modified":"2022-11-30T14:55:34","modified_gmt":"2022-11-30T14:55:34","slug":"hammerdb-using-mysql-5-7-vs-8-0-to-understand-performance-profiles","status":"publish","type":"post","link":"https:\/\/www.hammerdb.com\/blog\/uncategorized\/hammerdb-using-mysql-5-7-vs-8-0-to-understand-performance-profiles\/","title":{"rendered":"HammerDB: Using MySQL 5.7 vs 8.0 to understand performance profiles"},"content":{"rendered":"

One of the most important concepts in analysing database performance is that of understanding scalability. When a system ‘scales’ it is able to deliver higher levels of performance proportional to the system resources available to it.\u00a0 In particular, this means as we add CPU cores and increase the system load, we see higher performance. The way we measure this is through a ‘performance profile’. This simply means that we run a series of tests incrementally increasing the system load until we find the peak performance. Plotting these data points enables us to understand the scalability of the database software being tested on that system.<\/p>\n

In this example, we will compare MySQL 5.7.33 and MySQL 8.0.25 with the TPROC-C workload on a system with 2 sockets of Intel Xeon 8280L<\/a> that means we have 28 cores per socket, with 56 physical CPUs and 112 logical CPUs with Hyper-Threading.<\/p>\n

So as a first step we will install MySQL 5.7.33 and MySQL 8.0.25 and build a schema on both. In this example, we will use the HammerDB CLI and build the schema with the script as follows in both databases.<\/p>\n

dbset db mysql\r\ndbset bm TPC-C\r\nvuset logtotemp 1\r\ndiset connection mysql_socket \/tmp\/mysql.sock\r\ndiset tpcc mysql_count_ware 800\r\ndiset tpcc mysql_num_vu 64\r\ndiset tpcc mysql_partition true\r\nbuildschema\r\nwaittocomplete\r\nquit<\/pre>\n
Then we can run an interactive workload for a single Virtual User as follows for MySQL 8.0.25<\/p>\n
.\/hammerdbcli\r\nHammerDB CLI v4.2\r\nCopyright (C) 2003-2021 Steve Shaw\r\nType \"help\" for a list of commands\r\nThe xml is well-formed, applying configuration\r\nhammerdb>dbset db mysql\r\nDatabase set to MySQL\r\nhammerdb>diset tpcc mysql_driver timed\r\nClearing Script, reload script to activate new setting\r\nScript cleared\r\nChanged tpcc:mysql_driver from test to timed for MySQL\r\nhammerdb>vuset logtotemp 1\r\nhammerdb>vuset unique 1\r\nhammerdb>vuset vu 1\r\nhammerdb>loadscript\r\nScript loaded, Type \"print script\" to view\r\nhammerdb>vucreate\r\nVuser 1 created MONITOR - WAIT IDLE\r\nVuser 2 created - WAIT IDLE\r\nLogging activated\r\nto \/tmp\/hammerdb_60F169935C7303E293236333.log\r\n2 Virtual Users Created with Monitor VU\r\nhammerdb>vurun\r\nVuser 1:RUNNING\r\nVuser 1:Beginning rampup time of 2 minutes\r\nVuser 2:RUNNING\r\nVuser 2:Processing 10000000 transactions with output suppressed...\r\n...\r\nhammerdb>Vuser 1:Rampup 1 minutes complete ...\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\nVuser 1:1 ...,\r\nVuser 1:2 ...,\r\nVuser 1:3 ...,\r\nVuser 1:4 ...,\r\nVuser 1:5 ...,\r\nVuser 1:Test complete, Taking end Transaction Count.\r\nVuser 1:1 Active Virtual Users configured\r\nVuser 1:TEST RESULT : System achieved 25469 NOPM from 76873 MySQL TPM\r\nVuser 1:FINISHED SUCCESS\r\nVuser 2:FINISHED SUCCESS\r\nALL VIRTUAL USERS COMPLETE<\/pre>\n
and MySQL 5.7.33<\/p>\n
hammerdb>dbset db mysql\r\nDatabase set to MySQL\r\nhammerdb>diset tpcc mysql_driver timed\r\nClearing Script, reload script to activate new setting\r\nScript cleared\r\nChanged tpcc:mysql_driver from test to timed for MySQL\r\nhammerdb>vuset logtotemp 1\r\nhammerdb>vuset unique 1\r\nhammerdb>loadscript\r\nScript loaded, Type \"print script\" to view\r\nhammerdb>vuset vu 1\r\nhammerdb>vucreate\r\nVuser 1 created MONITOR - WAIT IDLE\r\nVuser 2 created - WAIT IDLE\r\nLogging activated\r\nto \/tmp\/hammerdb_60F183575C7303E273030333.log\r\n2 Virtual Users Created with Monitor VU\r\nhammerdb>vurun\r\nVuser 1:RUNNING\r\nVuser 1:Beginning rampup time of 2 minutes\r\nVuser 2:RUNNING\r\nVuser 2:Processing 10000000 transactions with output suppressed...\r\nhammerdb>Vuser 1:Rampup 1 minutes complete ...\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\nVuser 1:1 ...,\r\nVuser 1:2 ...,\r\nVuser 1:3 ...,\r\nVuser 1:4 ...,\r\nVuser 1:5 ...,\r\nVuser 1:Test complete, Taking end Transaction Count.\r\nVuser 1:1 Active Virtual Users configured\r\nVuser 1:TEST RESULT : System achieved 27154 NOPM from 82383 MySQL TPM\r\nVuser 1:FINISHED SUCCESS\r\nVuser 2:FINISHED SUCCESS\r\nALL VIRTUAL USERS COMPLETE<\/pre>\n
Extracting the NOPM value from the CLI or from the log file, we found that MySQL 8.0.25 returned 25469 NOPM to MySQL 5.7.33’s 27154 NOPM meaning the older version of MySQL gave a slightly better result.<\/p>\n
\"\"<\/a><\/p>\n
So does this mean that we can conclude that MySQL 5.7 is 1.06X faster than MySQL 8.0? Of course not, we have only run a single Virtual User and therefore not tested MySQL’s ability to manage multiple sessions running the same workload concurrently.\u00a0 Therefore, again using the interactive shell let us now run more tests, increasing the Virtual User count to 20.<\/p>\n
\"\"<\/a><\/p>\n
Now at 20 Virtual Users MySQL 8.0 is at 390014 TPM with MySQL 5.7 at 379972 putting MySQL 8.0 at 1.02X better than MySQL 5.7. However, again this might not be giving us the full picture, so let’s run a fully automated test up to 120 Virtual Users using the script as follows:<\/p>\n
puts \"MySQL Test Started\"\r\ndbset db mysql\r\ndbset bm TPC-C\r\ndiset connection mysql_socket \/tmp\/mysql.sock\r\ndiset tpcc mysql_driver timed\r\ndiset tpcc mysql_rampup 2\r\ndiset tpcc mysql_duration 5\r\nvuset logtotemp 1\r\nvuset unique 1\r\nloadscript\r\nforeach z {1 2 4 8 12 16 20 24 28 32 36 40 44 48 52 56 60 64 68 72 76 80 84 88 92 96 100 104 108 112 116 120} {\r\nputs \"$z VU test\"\r\nvuset vu $z\r\nvucreate\r\nvurun\r\nruntimer 480\r\nvudestroy\r\n}\r\nputs \"MySQL Test Complete\"<\/pre>\n
When we graph the result, we now get a much better picture of the full capabilities of the MySQL 5.7 and 5.8 software running on the same server.<\/p>\n
\"\"<\/a><\/p>\n
Now we can see that MySQL 8.0 reaches peak performance at 1006469 NOPM at 112 Virtual Users, with MySQL 5.7 peaking at 666407 at 68 Virtual Users, putting MySQL 8.0.25 at 1.51X over MySQL 5.7.33.<\/p>\n
\"\"<\/a><\/p>\n
It should be clear from the performance profile that the difference in performance can be attributed to the scalability of the database software, in this case MySQL. This is the same server, same OS and same HammerDB client, the only difference is in the server software.<\/p>\n
Crucially HammerDB can also run against Oracle, SQL Server, Db2, MariaDB and PostgreSQL on Windows and Linux, so we know that the capabilities of the server with commercial database software is actually a lot higher and also that the capabilities of the HammerDB client is also a lot higher – it is the database software and the database software alone that limits the scalability in this case.<\/p>\n
Also important is the user experience, so we have also gathered the transaction response times with the summaries shown below for both MySQL versions at the peak performance of MySQL 5.7.33.<\/p>\n
MySQL 8.0.25<\/p>\n
Vuser 1:68 Active Virtual Users configured\r\nVuser 1:TEST RESULT : System achieved 841337 NOPM from 2550191 MySQL TPM\r\n+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+\r\n>>>>> SUMMARY OF 68 ACTIVE VIRTUAL USERS : MEDIAN ELAPSED TIME : 403363ms\r\n>>>>> PROC: NEWORD\r\nCALLS: 5775293 MIN: 0.810ms AVG: 2.401ms MAX: 57.323ms TOTAL: 13867195.952ms\r\nP99: 4.005ms P95: 3.698ms P50: 2.316ms SD: 792.316 RATIO: 50.557%\r\n>>>>> PROC: PAYMENT\r\nCALLS: 5775133 MIN: 0.435ms AVG: 1.042ms MAX: 56.802ms TOTAL: 6022416.890ms\r\nP99: 2.052ms P95: 1.576ms P50: 0.978ms SD: 401.213 RATIO: 21.957%\r\n>>>>> PROC: DELIVERY\r\nCALLS: 578532 MIN: 4.001ms AVG: 8.929ms MAX: 67.479ms TOTAL: 5165817.194ms\r\nP99: 12.438ms P95: 11.282ms P50: 9.063ms SD: 1831.663 RATIO: 18.834%\r\n>>>>> PROC: SLEV\r\nCALLS: 578436 MIN: 0.752ms AVG: 2.468ms MAX: 56.045ms TOTAL: 1427926.455ms\r\nP99: 3.585ms P95: 3.230ms P50: 2.459ms SD: 560.309 RATIO: 5.206%\r\n>>>>> PROC: OSTAT\r\nCALLS: 576834 MIN: 0.286ms AVG: 0.955ms MAX: 39.063ms TOTAL: 551043.284ms\r\nP99: 1.796ms P95: 1.432ms P50: 0.912ms SD: 334.873 RATIO: 2.009%\r\n+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+<\/pre>\n
MySQL 5.7.33<\/p>\n
Vuser 1:68 Active Virtual Users configured\r\nVuser 1:TEST RESULT : System achieved 655295 NOPM from 1986131 MySQL TPM\r\n+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+\r\n>>>>> SUMMARY OF 68 ACTIVE VIRTUAL USERS : MEDIAN ELAPSED TIME : 403998ms\r\n>>>>> PROC: NEWORD\r\nCALLS: 4542033 MIN: 0.714ms AVG: 3.082ms MAX: 42.174ms TOTAL: 14000329.326ms\r\nP99: 5.124ms P95: 4.720ms P50: 3.001ms SD: 994.571 RATIO: 50.962%\r\n>>>>> PROC: DELIVERY\r\nCALLS: 453821 MIN: 3.020ms AVG: 13.931ms MAX: 58.857ms TOTAL: 6322207.859ms\r\nP99: 19.838ms P95: 17.926ms P50: 13.975ms SD: 2826.392 RATIO: 23.013%\r\n>>>>> PROC: PAYMENT\r\nCALLS: 4544693 MIN: 0.352ms AVG: 1.157ms MAX: 37.108ms TOTAL: 5261866.428ms\r\nP99: 2.231ms P95: 1.734ms P50: 1.096ms SD: 429.929 RATIO: 19.154%\r\n>>>>> PROC: SLEV\r\nCALLS: 455338 MIN: 0.686ms AVG: 2.407ms MAX: 54.447ms TOTAL: 1096000.372ms\r\nP99: 3.253ms P95: 3.048ms P50: 2.381ms SD: 501.128 RATIO: 3.990%\r\n>>>>> PROC: OSTAT\r\nCALLS: 454731 MIN: 0.252ms AVG: 0.946ms MAX: 28.175ms TOTAL: 430240.916ms\r\nP99: 1.856ms P95: 1.458ms P50: 0.905ms SD: 321.024 RATIO: 1.566%\r\n+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+<\/pre>\n
What we can see is that with MySQL 8.0.25 we have both higher throughput and lower response times. MySQL 8.0 therefore offers a higher capacity for load than MySQL 5.7 on this server.<\/p>\n
It should also be clear that testing on a single socket environment may not give you an accurate comparison of database software designed to take advantage of multiple CPU sockets. In this case we tested MySQL vs MySQL however you should exercise caution in drawing conclusions about database software capabilities without having first tested in a server environment.<\/p>\n","protected":false},"excerpt":{"rendered":"
One of the most important concepts in analysing database performance is that of understanding scalability. When a system ‘scales’ it is able to deliver higher levels of performance proportional to the system resources available to it.\u00a0 In particular, this means as we add CPU cores and increase the system load, we see higher performance. The … <\/p>\n
Continue reading “HammerDB: Using MySQL 5.7 vs 8.0 to understand performance profiles”<\/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-1047","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\/1047","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=1047"}],"version-history":[{"count":10,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/posts\/1047\/revisions"}],"predecessor-version":[{"id":1062,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/posts\/1047\/revisions\/1062"}],"wp:attachment":[{"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/media?parent=1047"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/categories?post=1047"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/tags?post=1047"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.hammerdb.com\/blog\/wp-json\/wp\/v2\/ppma_author?post=1047"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}