{"id":12,"date":"2018-10-12T17:19:00","date_gmt":"2018-10-12T17:19:00","guid":{"rendered":""},"modified":"2019-05-01T17:28:28","modified_gmt":"2019-05-01T17:28:28","slug":"hammerdb-mysql-and-mariadb-best-practice-for-performance-and-scalability","status":"publish","type":"post","link":"https:\/\/www.hammerdb.com\/blog\/uncategorized\/hammerdb-mysql-and-mariadb-best-practice-for-performance-and-scalability\/","title":{"rendered":"HammerDB MySQL and MariaDB Best Practice for Performance and Scalability"},"content":{"rendered":"
This post complements the previous best practice guides this time with the focus on MySQL and MariaDB and achieving top levels of performance with the HammerDB MySQL TPC-C test.\u00a0\u00a0As with the previous guides as an Intel employee (#IAMINTEL) the examples are taken from a MySQL 8 on Linux on Intel system and the approach is the same for whatever system you are testing although some of the settings you see may be different. Similarly for this guide MySQL can be swapped for a mySQL based databases such as MariaDB.<\/span><\/span><\/div>\n

\n<\/span>As is exactly the same with PostgreSQL for system choice a 2 socket system is optimal for MySQL OLTP performance. As is also the case this limitation is at the database level (especially the storage engine) rather than the hardware level. InnoDB is the storage engine that will deliver the best OLTP throughput and should be chosen for this test.\u00a0<\/span><\/span>\u00a0<\/span><\/b><\/div>\n
\n

HammerDB difference from Sysbench<\/span><\/b><\/p>\n

For anyone benchmarking MySQL with HammerDB it is important to understand the differences from sysbench workloads as HammerDB is targeted at a testing a different usage model from sysbench. Historically MySQL has been positioned for supporting <\/span>web-based applications<\/a>\u00a0this is in contrast to enterprise based database workloads that have been served by commercial databases such as Oracle, Db2 and SQL Server. For this reason sysbench presents a vastly more simplified workload than HammerDB.\u00a0\u00a0<\/span><\/p>\n<\/div>\n

By default sysbench creates a single table with an ascending primary key and an identical pad column.\u00a0<\/span><\/span>
\n<\/span><\/b><\/div>\n
MariaDB [sbtest]> select * from sbtest limit 10<\/span>\n\u00a0\u00a0\u00a0 -> ;<\/span><\/span>\n+----+---+---+----------------------------------------------------+<\/span><\/span>\n| id | k | c | pad\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span><\/span>\n+----+---+---+----------------------------------------------------+<\/span><\/span>\n|\u00a0 1 | 0 |\u00a0\u00a0 | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |<\/span><\/span>\n|\u00a0 2 | 0 |\u00a0\u00a0 | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |<\/span><\/span>\n|\u00a0 3 | 0 |\u00a0\u00a0 | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |<\/span><\/span>\n|\u00a0 4 | 0 |\u00a0\u00a0 | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |<\/span><\/span>\n|\u00a0 5 | 0 | \u00a0\u00a0| qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |<\/span><\/span>\n|\u00a0 6 | 0 |\u00a0\u00a0 | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |<\/span><\/span>\n|\u00a0 7 | 0 |\u00a0\u00a0 | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |<\/span><\/span>\n|\u00a0 8 | 0 |\u00a0\u00a0 | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |<\/span><\/span>\n|\u00a0 9 | 0 |\u00a0\u00a0 | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |<\/span><\/span>\n| 10 | 0 |\u00a0\u00a0 | qqqqqqqqqqwwwwwwwwwweeeeeeeeeerrrrrrrrrrtttttttttt |<\/span><\/span>\n+----+---+---+----------------------------------------------------+<\/span><\/span>\n10 rows in set (0.02 sec)<\/span><\/span><\/span><\/pre>\n

\n<\/span>The workload then interacts with the database with simple SQL statements, for example with the read-only workload statements such as follows:<\/span>
\n<\/span><\/span><\/div>\n
SELECT SUM(K) from sbtest where id between ? and ?<\/span><\/span>\nSELECT DISTINCT c from sbtest where id between ? and ? order by c<\/span>\nSELECT c from sbtest where id=?<\/span><\/span><\/pre>\n
<\/div>\n
Consequently there is no contention and the workload will scale according to the ability of the database to handle these isolated statements.\u00a0<\/span><\/span>
\n
\n<\/span><\/span>HammerDB on the other hand is based on the TPC-C specification<\/a>\u00a0(but not identical to a full TPC-C workload) and more closely represents an enterprise type workload. There are 9 tables:<\/span><\/span><\/div>\n
\n
<\/div>\n
MariaDB [tpcc]> show tables;<\/span>\n+----------------+<\/span>\n| Tables_in_tpcc |<\/span>\n+----------------+<\/span>\n| customer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span>\n| district\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span>\n| history\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span>\n| item\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span>\n| new_order\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span>\n| order_line\u00a0\u00a0\u00a0\u00a0 |<\/span>\n| orders\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span>\n| stock\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span>\n| warehouse\u00a0\u00a0\u00a0\u00a0\u00a0 |<\/span>\n+----------------+<\/span>\n9 rows in set (0.00 sec)<\/span><\/span><\/pre>\n
<\/div>\n
The tables have more complexity than sbtest:<\/span><\/div>\n
\u00a0<\/span><\/span><\/div>\n
MariaDB [tpcc]> select * from customer limit 1 G\n*************************** 1. row ***************************\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c_id: 1\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c_d_id: 1\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c_w_id: 1\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c_first: 4fTYMkzYdy8EbABc\n\u00a0\u00a0\u00a0\u00a0\u00a0 c_middle: OE\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c_last: BARBARBAR\n\u00a0\u00a0\u00a0 c_street_1: G30TzdyBM1xx\n\u00a0\u00a0\u00a0 c_street_2: fRveiqUZDzz54pt9FK8\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c_city: jVRLzrSBopMpcmN4WYO2\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c_state: JK\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c_zip: 603111111\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c_phone: 4610643910936129\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 c_since: 2018-10-11 08:48:51\n\u00a0\u00a0\u00a0\u00a0\u00a0 c_credit: BC\n\u00a0 c_credit_lim: 50000.00\n\u00a0\u00a0\u00a0 c_discount: 0.2000\n\u00a0\u00a0\u00a0\u00a0 c_balance: -10.00\nc_ytd_payment: 10.00\nc_payment_cnt: 1\nc_delivery_cnt: 0\nc_data: hjtWypvebZZgVhNCdKOhHd50Wn7HQG8XAm9cSHkXTf73KqBHot7IvHq8PtaHdaJ9oMXqFx6aUXrBRxQ44gqLf0k04gkPVWc6Lx3q71gFCu1vZlLhmmIaWQf5zyDD4AAqejVcwYKi50P9rHFegjzURpTf6c9SPEfpupc7378uekwBYTj4Xfm0Od3ukiQIKto8Nlx1Is51pC4qynxLEWWGULhXdBipYckk5EjpbpdKzUjEcMGJ6nCmFpNgXfKDIUpaYsw1dWwgCEhPfXiKjXMO0v0iF56wzD6AOF4w7m8CXSw0x5zKB7URuTqlGedSYK8EvmylYudXLF\n1 row in set (0.00 sec)<\/span><\/pre>\n
<\/div>\n
and the workload is driven by stored procedures:<\/span><\/div>\n
\u00a0<\/span><\/div>\n
MariaDB [tpcc]> show procedure status<\/span>\n\u00a0\u00a0\u00a0 -> ;<\/span>\n+-------+--------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+<\/span>\n| Db\u00a0\u00a0\u00a0 | Name\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Type\u00a0\u00a0\u00a0\u00a0\u00a0 | Definer\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Modified\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Created\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | Security_type | Comment | character_set_client | collation_connection | Database Collation |<\/span>\n+-------+--------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+<\/span>\n| tpcc\u00a0 | DELIVERY\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | PROCEDURE | @\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 2018-10-11 08:57:34 | 2018-10-11 08:57:34 | DEFINER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | latin1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | latin1_swedish_ci\u00a0\u00a0\u00a0 | latin1_swedish_ci\u00a0 |<\/span>\n| tpcc\u00a0 | NEWORD\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | PROCEDURE | @\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0| 2018-10-11 08:57:34 | 2018-10-11 08:57:34 | DEFINER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | latin1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | latin1_swedish_ci\u00a0\u00a0\u00a0 | latin1_swedish_ci\u00a0 |<\/span>\n| tpcc\u00a0 | OSTAT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | PROCEDURE | @\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 2018-10-11 08:57:34 | 2018-10-11 08:57:34 | DEFINER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | latin1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | latin1_swedish_ci\u00a0\u00a0\u00a0 | latin1_swedish_ci\u00a0 |<\/span>\n| tpcc\u00a0 | PAYMENT\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | PROCEDURE | @\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 2018-10-11 08:57:34 | 2018-10-11 08:57:34 | DEFINER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | latin1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | latin1_swedish_ci\u00a0\u00a0\u00a0 | latin1_swedish_ci\u00a0 |<\/span>\n| tpcc\u00a0 | SLEV\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | PROCEDURE | @\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | 2018-10-11 08:57:34 | 2018-10-11 08:57:34 | DEFINER\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 |\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | latin1\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 | latin1_swedish_ci\u00a0\u00a0\u00a0 | latin1_swedish_ci\u00a0 |<\/span>\n+-------+--------------------+-----------+----------------+---------------------+---------------------+---------------+---------+----------------------+----------------------+--------------------+<\/span>\n7 rows in set (0.00 sec)<\/span><\/span><\/pre>\n
an example is shown of the shortest of these:\u00a0<\/span><\/span><\/div>\n
<\/div>\n
CREATE DEFINER=`` PROCEDURE `slev`(<\/span>\nst_w_id\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INTEGER,<\/span>\nst_d_id\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INTEGER,<\/span>\nthreshold\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INTEGER<\/span>\n)<\/span>\nBEGIN<\/span>\nDECLARE st_o_id\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 INTEGER;<\/span>\nDECLARE stock_count\u00a0\u00a0\u00a0\u00a0 INTEGER;<\/span>\nDECLARE `Constraint Violation` CONDITION FOR SQLSTATE '23000';<\/span>\nDECLARE EXIT HANDLER FOR `Constraint Violation` ROLLBACK;<\/span>\nDECLARE EXIT HANDLER FOR NOT FOUND ROLLBACK;<\/span>\nSELECT d_next_o_id INTO st_o_id<\/span>\nFROM district<\/span>\nWHERE d_w_id=st_w_id AND d_id=st_d_id;<\/span>\nSELECT COUNT(DISTINCT (s_i_id)) INTO stock_count<\/span>\nFROM order_line, stock<\/span>\nWHERE ol_w_id = st_w_id AND<\/span>\nol_d_id = st_d_id AND (ol_o_id < st_o_id) AND<\/span>\nol_o_id >= (st_o_id - 20) AND s_w_id = st_w_id AND<\/span>\ns_i_id = ol_i_id AND s_quantity < threshold;<\/span><\/span><\/pre>\n
As a result there is deliberate contention in the HammerDB workload that is not seen in a sysbench one. Also as HammerDB is making one call to\u00a0<\/span>stored procedure that then contains multiple SQL statements the workload throughput is significantly higher than sysbench.\u00a0<\/span><\/span>
\n
\n<\/span><\/span>More recently MySQL and MariaDB has been positioned in the enterprise space competing with commercial database such as Oracle with features such as PL\/SQL compatibility.\u00a0 Therefore if you are testing MySQL or MariaDB’s ability to support web-based applications that use\u00a0 SQL statements with minimal contention then sysbench is likely to present the best assessment of the system for this type of workload. On the other hand if testing MySQL or MariaDB for the ability to handle a more complex workload such as the use of stored procedures and in particular if looking to compare scalability with a traditional database then HammerDB is focused more towards testing those enterprise features. Finally it is also important to note that this comparison is focused around OLTP based workloads, HammerDB also supports a TPC-H based workload for analytics with complex ad-hoc queries. Traditionally MySQL has not supported such workloads however features such as columnstore in MariaDB now make this possible.\u00a0<\/span><\/span>\u00a0<\/span><\/b><\/p>\n
System Setup: CPU, Memory and\u00a0<\/span><\/b>I\/O\u00a0<\/span><\/b>Configuration<\/span><\/b><\/p>\n<\/div>\n
System setup is covered on the PostgreSQL Best Practice post<\/a>\u00a0so it will not be repeated here as the steps are the same. Make sure that the<\/span>\u00a0I\/O that is able to keep up with writing to the redo log. Also make sure that if using an Intel CPU it is correctly configured with the right drivers and setup for turbo boost with cpupower output as follows:\u00a0<\/span><\/span><\/div>\n
<\/div>\n
.\/cpupower frequency-info\n<\/span><\/span>analyzing CPU 0:<\/span><\/span> \u00a0 \ndriver:\u00a0intel_pstate\n\u00a0 CPUs which run at the same hardware frequency: 0 \u00a0 \nCPUs which need to have their frequency coordinated by software: 0 \u00a0 \nmaximum transition latency:\u00a0 Cannot determine or is not supported. \u00a0 \nhardware limits: 1000 MHz - 3.80 GHz\navailable cpufreq governors: performance powersave \u00a0 \ncurrent policy: frequency should be within\u00a01000 MHz and 3.80 GHz.\n \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 The governor\u00a0\"performance\"\u00a0may decide which speed to use\n \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 within this range.\n current CPU frequency: Unable to call hardware \u00a0 \n current CPU frequency: 1.99 GHz (asserted by call to kernel) \u00a0\u00a0\nboost state support:\n \u00a0 \u00a0\u00a0Supported: yes\n \u00a0 \u00a0\u00a0Active: yes<\/span><\/b><\/span><\/span><\/span>\n<\/b><\/pre>\n
\n
Finally in testing Huge Pages has less of an impact than with PostgreSQL, although performance should not be lower than having Huge Pages disabled. Just note that if using MariaDB in some versions<\/a> a bug means that Huge Pages cannot be enabled although this does not greatly impact performance.<\/span><\/p>\n
Install MySQL or MariaDB from Source<\/span><\/b><\/p>\n
(Note that the following section applies in particular to pre-2019 versions of MySQL and MariaDB and more recent versions of MySQL 8 have already been updated for optimal performance on multiple platforms and therefore the change is this section is not required)<\/p>\n
<\/div>\n
<\/div>\n
The MySQL manual<\/a> <\/span>advises that building from source allows you customize build parameters and as with PostgreSQL this can also be the best way to achieve maximum performance. In particular with a workload such as HammerDB there is a lot of emphasis on locking and latching that is not seen in a sysbench workload and therefore you may see gains in modifying the InnoDB storage engine code. In InnoDB some of this behaviour is hardcoded however the CPU PAUSE instruction that is used by the UT_RELAX_CPU macro can and does vary between CPUs.\u00a0 In particular on Intel Scalable Processors (Skylake architecture) the PAUSE instruction is much longer than previous architectures and therefore calling UT_RELAX_CPU can consume a lot more time resulting in reduced performance.\u00a0 It may be the case that by the time you are testing this you can modify this behaviour with a parameter. Otherwise find the file\u00a0ut0ut.cc in the directory\u00a0storage\/innobase\/ut and modify the value accordingly, by default it is set to 50, for Skylake 5 is more appropriate for the delay multiplier.\u00a0<\/span><\/div>\n<\/div>\n
<\/div>\n
for (i = 0; i <delay * 5<\/span>; i++) {\n\u00a0\u00a0\u00a0 j += i;<\/span>\n\u00a0\u00a0\u00a0 UT_RELAX_CPU();<\/span>\n\u00a0 }<\/span>\n<\/span><\/pre>\n
<\/div>\n
If unsure on whether to do this run the workload and use the perf command to observe the top functions. If “ut_delay” is consuming most of the CPU (in most cases it will still be one of the top functions) then potentially modifying this code can increase performance.\u00a0<\/span><\/span><\/p>\n
Configure MySQL<\/span><\/b><\/p>\n
\n
An example MySQL my.cnf file is shown.\u00a0 Note that innodb_file_per_table is set then when we use partition in HammerDB it can insert into multiple files one per partition. \u00a0This gives a performance gain. One configured start the MySQL or MariaDB database.\u00a0 For more details on MySQL and optimization see the website by Dimitri Kravtchuk<\/a>.\u00a0<\/span><\/p>\n<\/div>\n
[mysqld]<\/span>\nlarge-pages<\/span>\nskip-log-bin<\/span>\ndatadir=\/home\/mysql\/data<\/span>\nlanguage=\/homemysql\/bld\/share\/english<\/span>\ndefault_authentication_plugin=mysql_native_password<\/span>\nsocket=\/tmp\/mysql.sock<\/span>\nport=3306<\/span>\nbind_address=192.168.1.1<\/span>\n# general<\/span>\nmax_connections=4000<\/span>\ntable_open_cache=8000<\/span>\ntable_open_cache_instances=16<\/span>\nback_log=1500<\/span>\ndefault_password_lifetime=0<\/span>\nssl=0<\/span>\nperformance_schema=OFF<\/span>\nmax_prepared_stmt_count=128000<\/span>\nskip_log_bin=1<\/span>\ncharacter_set_server=latin1<\/span>\ncollation_server=latin1_swedish_ci<\/span>\ntransaction_isolation=REPEATABLE-READ<\/span>\n# files<\/span>\ninnodb_file_per_table<\/span>\ninnodb_log_file_size=1024M<\/span>\ninnodb_log_files_in_group=32<\/span>\ninnodb_open_files=4000<\/span>\n# buffers<\/span>\ninnodb_buffer_pool_size=64000M<\/span>\ninnodb_buffer_pool_instances=16<\/span>\ninnodb_log_buffer_size=64M<\/span>\n# tune<\/span>\ninnodb_doublewrite=0<\/span>\ninnodb_thread_concurrency=0<\/span>\ninnodb_flush_log_at_trx_commit=0<\/span>\ninnodb_max_dirty_pages_pct=90<\/span>\ninnodb_max_dirty_pages_pct_lwm=10<\/span>\njoin_buffer_size=32K<\/span>\nsort_buffer_size=32K<\/span>\ninnodb_use_native_aio=1<\/span>\ninnodb_stats_persistent=1<\/span>\ninnodb_spin_wait_delay=6<\/span>\ninnodb_max_purge_lag_delay=300000<\/span>\ninnodb_max_purge_lag=0<\/span>\ninnodb_flush_method=O_DIRECT_NO_FSYNC<\/span>\ninnodb_checksum_algorithm=none<\/span>\ninnodb_io_capacity=4000<\/span>\ninnodb_io_capacity_max=20000<\/span>\ninnodb_lru_scan_depth=9000<\/span>\ninnodb_change_buffering=none<\/span>\ninnodb_read_only=0<\/span>\ninnodb_page_cleaners=4<\/span>\ninnodb_undo_log_truncate=off<\/span>\n# perf special<\/span>\ninnodb_adaptive_flushing=1<\/span>\ninnodb_flush_neighbors=0<\/span>\ninnodb_read_io_threads=16<\/span>\ninnodb_write_io_threads=16<\/span>\ninnodb_purge_threads=4<\/span>\ninnodb_adaptive_hash_index=0<\/span>\n# monitoring<\/span>\ninnodb_monitor_enable='%'<\/span><\/span><\/pre>\n
\n
Configure the HammerDB Client<\/span><\/b><\/span><\/h6>\n<\/div>\n
\n
Download and install HammerDB on a test client system, Like PostgreSQL another 2 socket server is ideal. You need the client libraries so\u00a0\u00a0ensure the MySQL 5.7 library file \u201clibmysqlclient.so.20\u201d is findable \u2013 you only need this one file and then run hammerdbcli to check for this file:\u00a0<\/span><\/p>\n
export LD_LIBRARY_PATH=\/home\/mysql:$LD_LIBRARY_PATH<\/span>\n<\/span><\/pre>\n
H<\/span>ammerDB CLI v3.1<\/span>\nCopyright (C) 2003-2018 Steve Shaw<\/span>\nType \"help\" for a list of commands<\/span>\nThe xml is well-formed, applying configuration<\/span>\nhammerdb>librarycheck<\/span>\n..<\/span>\nChecking database library for MySQL<\/span>\nSuccess ... loaded library mysqltcl for MySQL<\/span><\/span><\/pre>\n<\/div>\n
\u00a0<\/span><\/div>\n
Create the Schema and Run the Test<\/span><\/b><\/b><\/h6>\n
<\/div>\n
Just before running the schema creation double check that you have initialised mysql correctly and the mysql directory exists, otherwise the tables will be created and loaded with data but you will get the following error when creating the stored procedures<\/span><\/div>\n
Error in Virtual User 1: mysqlexec\/db server: Table 'mysql.proc' doesn't exist<\/pre>\n
an example of how to do this is as follows:<\/span><\/div>\n
$ .\/scripts\/mysql_install_db --srcdir=\/home\/mariadb\/mariadb-10.2 --defaults-file=.\/..\/my.cnf\nInstalling MariaDB\/MySQL system tables in '\/home\/mariadb\/data' ...\nOK<\/pre>\n
If using the HammerDB CLI an example script saved as innodbbuild.tcl is shown update accordingly for your system and make sure partitioning is included:<\/span><\/span><\/div>\n
#!\/bin\/tclsh<\/span>\nputs \"SETTING CONFIGURATION\"<\/span>\ndbset db mysql<\/span>\ndiset connection mysql_host 192.168.1.1<\/span><\/span>\ndiset connection mysql_port 3306<\/span><\/span>\ndiset tpcc mysql_count_ware 800<\/span>\ndiset tpcc mysql_partition true<\/span>\ndiset tpcc mysql_num_vu 64<\/span>\ndiset tpcc mysql_storage_engine innodb<\/span>\nprint dict<\/span>\nbuildschema<\/span><\/span><\/pre>\n
\n
$ .\/hammerdbcli<\/span>\nHammerDB CLI v3.1<\/span>\nCopyright (C) 2003-2018 Steve Shaw<\/span>\nType \"help\" for a list of commands<\/span>\nThe xml is well-formed, applying configuration \nhammerdb>source innodbbuild.tcl<\/span><\/span><\/pre>\n<\/div>\n
\n
It will return to the prompt when built \u2013 then restart the MySQL instance and run a single test. On an up to date system around mid-2018 expect to see up to 2M MySQL TPM and around 650K NOPM.\u00a0<\/span><\/p>\n<\/div>\n
Vuser 1:56 Active Virtual Users configured<\/span>\nVuser 1:TEST RESULT : System achieved 1994844 MySQL TPM at 658082 NOPM<\/span><\/span><\/pre>\n
 \u00a0<\/span>Then on a load test client run a script such as the following at the command line this does the same as autopilot in the GUI so make sure you log to temp.<\/span><\/div>\n
 \u00a0<\/span><\/div>\n
#!\/bin\/tclsh<\/span>\nproc runtimer { seconds } {<\/span>\nset x 0<\/span>\nset timerstop 0<\/span>\nwhile {!$timerstop} {<\/span>\nincr x<\/span>\nafter 1000<\/span>\n\u00a0 if { ![ expr {$x % 60} ] } {<\/span>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 set y [ expr $x \/ 60 ]<\/span>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 puts \"Timer: $y minutes elapsed\"<\/span>\n\u00a0 }<\/span>\nupdate<\/span>\nif {\u00a0 [ vucomplete ] || $x eq $seconds } { set timerstop 1 }<\/span>\n\u00a0\u00a0\u00a0 }<\/span>\nreturn<\/span>\n}<\/span>\nputs \"SETTING CONFIGURATION\"<\/span>\ndbset db mysql<\/span>\ndiset connection mysql_host 192.168.142.1<\/span>\ndiset connection mysql_port 3307<\/span>\ndiset tpcc mysql_driver timed<\/span>\ndiset tpcc my_rampup 2<\/span>\ndiset tpcc my_duration 5<\/span>\nvuset logtotemp 1<\/span>\nloadscript<\/span>\nputs \"SEQUENCE STARTED\"<\/span>\nforeach z { 1 2 4 8 12 16 20 24 28 32 36 40 44 48 52 56 60 64 68 72 76 } {<\/span>\nputs \"$z VU TEST\"<\/span>\nvuset vu $z<\/span>\nvucreate<\/span>\nvurun<\/span>\nruntimer 600<\/span>\nvudestroy<\/span>\nafter 5000<\/span>\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 }<\/span>\nputs \"TEST SEQUENCE COMPLETE\"<\/span><\/span><\/pre>\n
\n
\u00a0<\/span>Test Results<\/span><\/b><\/h6>\n<\/div>\n
\n
Grab the TPM or NOPM from the logfile. If everything went well you should get a sequence as follows peaking at around 650,000 NOPM on an up to date system in mid-2018.\u00a0\u00a0<\/span>To be clear this chart shows the data from 20 performance tests, each with a 2 minute rampup time and 5 minute test. After each test completed HammerDB then increased the virtual user count and repeated the test. Fortunately HammerDB allows all of this process to be automated.\u00a0 Each data point shows the average transaction rate captured over the 5 minute test so not the peak performance that may be higher.\u00a0<\/span><\/span><\/span><\/p>\n<\/div>\n
\n
<\/a><\/div>\n
Note that after a point, in this case after 56 virtual users, performance will decrease as you increase the number of virtual users. This is to be expected and is due to the limitations of the scalability of the storage engine. If you report the stored procedure response times (see the HammerDB manual for how to do this) you will see the time increase especially for the DELIVERY stored procedure that is updating the ORDER_LINE table while the NEWORD stored procedure is trying to insert into it. As a result the NOPM or New Orders per minute reduces as more contention is experienced. This is not a limitation of HammerDB that has been observed to drive significantly higher transaction rates.\u00a0 Nevertheless around 2M MySQL TPM is very respectable throughput for an enterprise workload and as there is a lot more CPU available the potential is there to go a lot higher.\u00a0<\/span><\/p>\n<\/div>\n