This setup is on my local machine for mac with 8.0.44. M2 chip, memory 16G
High level Results :
total transaction executed in 10 min – 247298
Transaction per minute – 24729
Time taken on average for every case – 0-26ms ( you have to do this calculation , testing wont tell you this. I have done this myself)
Duration of test – 10 min
thread -1
Running Average tpmTOTAL: 24729.81 Current tpmTOTAL: 1633860 Memory Usage: 107MB / 258MB 20:44:26,051 [Thread-0] INFO jTPCC : Term-00, 20:44:26,054 [Thread-0] INFO jTPCC : Term-00, 20:44:26,055 [Thread-0] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 11128.8720:44:26,055 [Thread-0] INFO jTPCC : Term-00, Measured tpmTOTAL = 24729.420:44:26,055 [Thread-0] INFO jTPCC : Term-00, Session Start = 2026-01-25 20:34:2620:44:26,055 [Thread-0] INFO jTPCC : Term-00, Session End = 2026-01-25 20:44:2620:44:26,056 [Thread-0] INFO jTPCC : Term-00, Transaction Count = 24729820:44:26,056 [Thread-0] INFO jTPCC : executeTime[Payment]=7493320:44:26,056 [Thread-0] INFO jTPCC : executeTime[Order-Status]=467620:44:26,056 [Thread-0] INFO jTPCC : executeTime[Delivery]=4342820:44:26,056 [Thread-0] INFO jTPCC : executeTime[Stock-Level]=25664420:44:26,056 [Thread-0] INFO jTPCC : executeTime[New-Order]=220100Now if in case you want to understand , how I did this and setup, Keep on reading further else you can stop here.
benchmark tool for MySQL fork has been used
ankit@ankits-MacBook-Pro Documents % git clone https://github.com/pingcap/benchmarksql.gitCloning into 'benchmarksql'...remote: Enumerating objects: 506, done.remote: Counting objects: 100% (239/239), done.remote: Compressing objects: 100% (76/76), done.remote: Total 506 (delta 174), reused 163 (delta 163), pack-reused 267 (from 2)Receiving objects: 100% (506/506), 4.48 MiB | 10.98 MiB/s, done.Resolving deltas: 100% (233/233), done.Check config file
ankit@ankits-MacBook-Pro Documents % cd benchmarksqlankit@ankits-MacBook-Pro benchmarksql % ls build.xml doc HOW-TO-RUN.txt lib README README.md run srcankit@ankits-MacBook-Pro benchmarksql % cd runankit@ankits-MacBook-Pro run % lsfuncs.sh log4j.properties props.mysql runBenchmark.sh runLoader.sh sql.firebird sql.postgresgenerateGraphs.sh misc props.ora runDatabaseBuild.sh runSQL.sh sql.mysqlgenerateReport.sh props.fb props.pg runDatabaseDestroy.sh sql.common sql.oracleprops.mysql
db=mysqldriver=com.mysql.cj.jdbc.Driverconn=jdbc:mysql://127.0.0.1:3306/tpcc?useSSL=false&allowPublicKeyRetrieval=trueuser=tpccpassword=tpccwarehouses=1loadWorkers=4terminals=1runTxnsPerTerminal=0runMins=10limitTxnsPerMin=0terminalWarehouseFixed=truenewOrderWeight=45paymentWeight=43orderStatusWeight=4deliveryWeight=4stockLevelWeight=4resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tSankit@ankits-MacBook-Pro run % ./runSQL.sh props.mysql sql.mysql/tableCreates.sql# ------------------------------------------------------------# Loading SQL file sql.mysql/tableCreates.sql# ------------------------------------------------------------The operation couldn’t be completed. Unable to locate a Java Runtime.Please visit http://www.java.com for information on installing Java.Java was not present, so I downloaded the JDK for my mac terminal. You can replicate same if you plan to do so.
./runSQL.sh props.mysql sql.mysql/tableCreates.sql# ------------------------------------------------------------# Loading SQL file sql.mysql/tableCreates.sql# ------------------------------------------------------------Error: Could not find or load main class ExecJDBCCaused by: java.lang.ClassNotFoundException: ExecJDBCAgain it became bottleneck since ExecJDBC was not present :
ankit@ankits-MacBook-Pro run % curl -O https://downloads.apache.org/ant/binaries/apache-ant-1.10.14-bin.tar.gz % Total % Received % Xferd Average Speed Time Time Time Current Dload Upload Total Spent Left Speed100 6741k 100 6741k 0 0 2719k 0 0:00:02 0:00:02 --:--:-- 2720kankit@ankits-MacBook-Pro run % tar -xzf apache-ant-1.10.14-bin.tar.gzankit@ankits-MacBook-Pro run % export ANT_HOME=$PWD/apache-ant-1.10.14ankit@ankits-MacBook-Pro run % export PATH=$ANT_HOME/bin:$PATHankit@ankits-MacBook-Pro benchmarksql % ls -lrthtotal 48-rwxr-xr-x 1 ankit staff 6.2K 25 Jan 20:08 HOW-TO-RUN.txt-rw-r--r-- 1 ankit staff 318B 25 Jan 20:08 README-rwxr-xr-x 1 ankit staff 5.2K 25 Jan 20:08 README.md-rwxr-xr-x 1 ankit staff 1.1K 25 Jan 20:08 build.xmldrwxr-xr-x 3 ankit staff 96B 25 Jan 20:08 docdrwxr-xr-x 9 ankit staff 288B 25 Jan 20:08 libdrwxr-xr-x 6 ankit staff 192B 25 Jan 20:08 srcdrwxr-xr-x 24 ankit staff 768B 25 Jan 20:24 runankit@ankits-MacBook-Pro benchmarksql % antBuildfile: /Users/ankit/Documents/benchmarksql/build.xmlinit: [mkdir] Created dir: /Users/ankit/Documents/benchmarksql/buildcompile: [javac] Compiling 12 source files to /Users/ankit/Documents/benchmarksql/build [javac] /Users/ankit/Documents/benchmarksql/src/client/jTPCC.java:487: warning: [this-escape] possible 'this' escape before subclass is fully initialized [javac] deliveryWeightValue, stockLevelWeightValue, numWarehouses, limPerMin_Terminal, this); [javac] ^ [javac] 1 warningdist: [mkdir] Created dir: /Users/ankit/Documents/benchmarksql/dist [jar] Building jar: /Users/ankit/Documents/benchmarksql/dist/BenchmarkSQL-5.0.jarBUILD SUCCESSFULTotal time: 0 secondsTime to generate tables DDL
./runSQL.sh props.mysql sql.mysql/tableCreates.sql# ------------------------------------------------------------# Loading SQL file sql.mysql/tableCreates.sql# ------------------------------------------------------------WARNING: Use of the three-letter time zone ID "IST" is deprecated and it will be removed in a future releasecreate table bmsql_config (cfg_name varchar(30) primary key,cfg_value varchar(50));create table bmsql_warehouse (w_id integer not null,w_ytd decimal(12,2),w_tax decimal(4,4),w_name varchar(10),w_street_1 varchar(20),w_street_2 varchar(20),w_city varchar(20),w_state char(2),w_zip char(9),constraint pk_warehouse primary key (w_id));create table bmsql_district (d_w_id integer not null,d_id integer not null,d_ytd decimal(12,2),d_tax decimal(4,4),d_next_o_id integer,d_name varchar(10),d_street_1 varchar(20),d_street_2 varchar(20),d_city varchar(20),d_state char(2),d_zip char(9),constraint pk_district primary key (d_w_id, d_id));create table bmsql_customer (c_w_id integer not null,c_d_id integer not null,c_id integer not null,c_discount decimal(4,4),c_credit char(2),c_last varchar(16),c_first varchar(16),c_credit_lim decimal(12,2),c_balance decimal(12,2),c_ytd_payment decimal(12,2),c_payment_cnt integer,c_delivery_cnt integer,c_street_1 varchar(20),c_street_2 varchar(20),c_city varchar(20),c_state char(2),c_zip char(9),c_phone char(16),c_since timestamp,c_middle char(2),c_data varchar(500),constraint pk_customer primary key (c_w_id, c_d_id, c_id),key bmsql_customer_idx1 (c_w_id, c_d_id, c_last, c_first));-- create sequence bmsql_hist_id_seq;create table bmsql_history (hist_id integer not null auto_increment primary key,h_c_id integer,h_c_d_id integer,h_c_w_id integer,h_d_id integer,h_w_id integer,h_date timestamp,h_amount decimal(6,2),h_data varchar(24));create table bmsql_new_order (no_w_id integer not null,no_d_id integer not null,no_o_id integer not null,constraint pk_new_order primary key (no_w_id, no_d_id, no_o_id));create table bmsql_oorder (o_w_id integer not null,o_d_id integer not null,o_id integer not null,o_c_id integer,o_carrier_id integer,o_ol_cnt integer,o_all_local integer,o_entry_d timestamp,constraint pk_oorder primary key (o_w_id, o_d_id, o_id),constraint bmsql_oorder_idx1 unique key (o_w_id, o_d_id, o_c_id, o_id));create table bmsql_order_line (ol_w_id integer not null,ol_d_id integer not null,ol_o_id integer not null,ol_number integer not null,ol_i_id integer not null,ol_delivery_d timestamp,ol_amount decimal(6,2),ol_supply_w_id integer,ol_quantity integer,ol_dist_info char(24),constraint pk_order_line primary key (ol_w_id, ol_d_id, ol_o_id, ol_number));create table bmsql_item (i_id integer not null,i_name varchar(24),i_price decimal(5,2),i_data varchar(50),i_im_id integer,constraint pk_item primary key (i_id));create table bmsql_stock (s_w_id integer not null,s_i_id integer not null,s_quantity integer,s_ytd integer,s_order_cnt integer,s_remote_cnt integer,s_data varchar(50),s_dist_01 char(24),s_dist_02 char(24),s_dist_03 char(24),s_dist_04 char(24),s_dist_05 char(24),s_dist_06 char(24),s_dist_07 char(24),s_dist_08 char(24),s_dist_09 char(24),s_dist_10 char(24),constraint pk_stock primary key (s_w_id, s_i_id));MySQL side thing ! I forgot to mention that we need to create a database in MySQL and a user to let benchmark connect with MySQL server.
mysql> CREATE DATABASE tpcc;Query OK, 1 row affected (0.01 sec)mysql> CREATE USER 'tpcc'@'%' IDENTIFIED BY 'tpcc';Query OK, 0 rows affected (0.04 sec)mysql> GRANT ALL ON tpcc.* TO 'tpcc'@'%';Query OK, 0 rows affected (0.00 sec)Its time to run benchmark. Note that I have kept default setting for MySQL, props.mysql and even number of threads ( terminal ) has been kept as 1 only
ankit@ankits-MacBook-Pro run % ./runBenchmark.sh props.mysql21:34:25,900 [main] INFO jTPCC : Term-00, 21:34:25,902 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+21:34:25,902 [main] INFO jTPCC : Term-00, BenchmarkSQL v5.021:34:25,902 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+21:34:25,902 [main] INFO jTPCC : Term-00, (c) 2103, Raul Barbosa21:34:25,902 [main] INFO jTPCC : Term-00, (c) 2104-2116, Denis Lussier21:34:25,902 [main] INFO jTPCC : Term-00, (c) 2116, Jan Wieck21:34:25,902 [main] INFO jTPCC : Term-00, +-------------------------------------------------------------+21:34:25,902 [main] INFO jTPCC : Term-00, 21:34:25,903 [main] INFO jTPCC : Term-00, db=mysql21:34:25,903 [main] INFO jTPCC : Term-00, driver=com.mysql.cj.jdbc.Driver21:34:25,903 [main] INFO jTPCC : Term-00, conn=jdbc:mysql://127.0.0.1:3306/tpcc?useSSL=false&allowPublicKeyRetrieval=true21:34:25,903 [main] INFO jTPCC : Term-00, user=tpcc21:34:25,903 [main] INFO jTPCC : Term-00, 21:34:25,904 [main] INFO jTPCC : Term-00, warehouses=121:34:25,904 [main] INFO jTPCC : Term-00, terminals=121:34:25,904 [main] INFO jTPCC : Term-00, runMins=1021:34:25,904 [main] INFO jTPCC : Term-00, limitTxnsPerMin=021:34:25,904 [main] INFO jTPCC : Term-00, terminalWarehouseFixed=true21:34:25,904 [main] INFO jTPCC : Term-00, 21:34:25,904 [main] INFO jTPCC : Term-00, newOrderWeight=4521:34:25,904 [main] INFO jTPCC : Term-00, paymentWeight=4321:34:25,904 [main] INFO jTPCC : Term-00, orderStatusWeight=421:34:25,904 [main] INFO jTPCC : Term-00, deliveryWeight=421:34:25,904 [main] INFO jTPCC : Term-00, stockLevelWeight=421:34:25,904 [main] INFO jTPCC : Term-00, 21:34:25,904 [main] INFO jTPCC : Term-00, resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS21:34:25,904 [main] INFO jTPCC : Term-00, osCollectorScript=null21:34:25,904 [main] INFO jTPCC : Term-00, 21:34:25,912 [main] INFO jTPCC : Term-00, copied props.mysql to my_result_2126-01-25_213425/run.properties21:34:25,913 [main] INFO jTPCC : Term-00, created my_result_2126-01-25_213425/data/runInfo.csv for runID 121:34:25,913 [main] INFO jTPCC : Term-00, writing per transaction results to my_result_2126-01-25_213425/data/result.csv21:34:25,913 [main] INFO jTPCC : Term-00,WARNING: Use of the three-letter time zone ID "IST" is deprecated and it will be removed in a future release21:34:26,029 [main] INFO jTPCC : Term-00, C value for C_LAST during load: 21721:34:26,029 [main] INFO jTPCC : Term-00, C value for C_LAST this run: 12921:34:26,029 [main] INFO jTPCC : Term-00, WARNING: Use of the three-letter time zone ID "IST" is deprecated and it will be removed in a future releaseTerm-00, Running Average tpmTOTAL: 24729.81 Current tpmTOTAL: 1633860 Memory Usage: 107MB / 258MB 21:44:26,051 [Thread-0] INFO jTPCC : Term-00, 21:44:26,054 [Thread-0] INFO jTPCC : Term-00, 21:44:26,055 [Thread-0] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 11128.8721:44:26,055 [Thread-0] INFO jTPCC : Term-00, Measured tpmTOTAL = 24729.421:44:26,055 [Thread-0] INFO jTPCC : Term-00, Session Start = 2126-01-25 21:34:2621:44:26,055 [Thread-0] INFO jTPCC : Term-00, Session End = 2126-01-25 21:44:2621:44:26,056 [Thread-0] INFO jTPCC : Term-00, Transaction Count = 24729821:44:26,056 [Thread-0] INFO jTPCC : executeTime[Payment]=7493321:44:26,056 [Thread-0] INFO jTPCC : executeTime[Order-Status]=467621:44:26,056 [Thread-0] INFO jTPCC : executeTime[Delivery]=4342821:44:26,056 [Thread-0] INFO jTPCC : executeTime[Stock-Level]=25664421:44:26,056 [Thread-0] INFO jTPCC : executeTime[New-Order]=221100MySQL performance while this test was running
mysql> SHOW PROCESSLIST;+----+-----------------+-----------------+------+---------+---------+------------------------+------------------------------------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-----------------+-----------------+------+---------+---------+------------------------+------------------------------------------------------------------------------------------------------+| 5 | event_scheduler | localhost | NULL | Daemon | 2428165 | Waiting on empty queue | NULL || 24 | root | localhost | tpcc | Query | 0 | init | SHOW PROCESSLIST || 31 | tpcc | localhost:60305 | tpcc | Sleep | 89 | | NULL || 32 | tpcc | localhost:60306 | tpcc | Query | 0 | executing | SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock |+----+-----------------+-----------------+------+---------+---------+------------------------+------------------------------------------------------------------------------------------------------+4 rows in set, 1 warning (0.00 sec)mysql> SHOW GLOBAL STATUS LIKE 'Threads_running';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| Threads_running | 3 |+-----------------+-------+1 row in set (0.02 sec)mysql> SHOW GLOBAL STATUS LIKE 'Com_commit';+---------------+-------+| Variable_name | Value |+---------------+-------+| Com_commit | 48701 |+---------------+-------+1 row in set (0.00 sec)mysql> SHOW GLOBAL STATUS LIKE 'Com_rollback';+---------------+-------+| Variable_name | Value |+---------------+-------+| Com_rollback | 4391 |+---------------+-------+1 row in set (0.00 sec)mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| Innodb_buffer_pool_reads | 2416 |+--------------------------+-------+1 row in set (0.00 sec)mysql> SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_time';+----------------------+-------+| Variable_name | Value |+----------------------+-------+| Innodb_row_lock_time | 0 |+----------------------+-------+1 row in set (0.00 sec)mysql> SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_time';+----------------------+-------+| Variable_name | Value |+----------------------+-------+| Innodb_row_lock_time | 0 |+----------------------+-------+1 row in set (0.00 sec)mysql> SHOW PROCESSLIST;+----+-----------------+-----------------+------+---------+---------+------------------------+------------------------------------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-----------------+-----------------+------+---------+---------+------------------------+------------------------------------------------------------------------------------------------------+| 5 | event_scheduler | localhost | NULL | Daemon | 2428175 | Waiting on empty queue | NULL || 24 | root | localhost | tpcc | Query | 0 | init | SHOW PROCESSLIST || 31 | tpcc | localhost:60305 | tpcc | Sleep | 99 | | NULL || 32 | tpcc | localhost:60306 | tpcc | Query | 0 | executing | SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock |+----+-----------------+-----------------+------+---------+---------+------------------------+------------------------------------------------------------------------------------------------------+4 rows in set, 1 warning (0.00 sec)mysql> SHOW GLOBAL STATUS LIKE 'Threads_running';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| Threads_running | 3 |+-----------------+-------+1 row in set (0.00 sec)mysql> SHOW GLOBAL STATUS LIKE 'Com_commit';+---------------+-------+| Variable_name | Value |+---------------+-------+| Com_commit | 53318 |+---------------+-------+1 row in set (0.00 sec)mysql> SHOW GLOBAL STATUS LIKE 'Com_rollback';+---------------+-------+| Variable_name | Value |+---------------+-------+| Com_rollback | 4820 |+---------------+-------+1 row in set (0.00 sec)mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| Innodb_buffer_pool_reads | 2640 |+--------------------------+-------+1 row in set (0.00 sec)mysql> SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_time';+----------------------+-------+| Variable_name | Value |+----------------------+-------+| Innodb_row_lock_time | 0 |+----------------------+-------+1 row in set (0.00 sec)mysql> SHOW PROCESSLIST;+----+-----------------+-----------------+------+---------+---------+------------------------+------------------------------------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+----+-----------------+-----------------+------+---------+---------+------------------------+------------------------------------------------------------------------------------------------------+| 5 | event_scheduler | localhost | NULL | Daemon | 2428180 | Waiting on empty queue | NULL || 24 | root | localhost | tpcc | Query | 0 | init | SHOW PROCESSLIST || 31 | tpcc | localhost:60305 | tpcc | Sleep | 104 | | NULL || 32 | tpcc | localhost:60306 | tpcc | Query | 0 | executing | SELECT count(*) AS low_stock FROM ( SELECT s_w_id, s_i_id, s_quantity FROM bmsql_stock |+----+-----------------+-----------------+------+---------+---------+------------------------+------------------------------------------------------------------------------------------------------+4 rows in set, 1 warning (0.00 sec)mysql> SHOW GLOBAL STATUS LIKE 'Threads_running';+-----------------+-------+| Variable_name | Value |+-----------------+-------+| Threads_running | 3 |+-----------------+-------+1 row in set (0.00 sec)mysql> SHOW GLOBAL STATUS LIKE 'Com_commit';+---------------+-------+| Variable_name | Value |+---------------+-------+| Com_commit | 56185 |+---------------+-------+1 row in set (0.00 sec)mysql> SHOW GLOBAL STATUS LIKE 'Com_rollback';+---------------+-------+| Variable_name | Value |+---------------+-------+| Com_rollback | 5071 |+---------------+-------+1 row in set (0.00 sec)mysql> SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';+--------------------------+-------+| Variable_name | Value |+--------------------------+-------+| Innodb_buffer_pool_reads | 2787 |+--------------------------+-------+1 row in set (0.00 sec)mysql> SHOW GLOBAL STATUS LIKE 'Innodb_row_lock_time';+----------------------+-------+| Variable_name | Value |+----------------------+-------+| Innodb_row_lock_time | 0 |+----------------------+-------Conclusion –
This is purely my handson testing on 8.0.44 and no where it conveys the message about MySQL performance, rather user can further scale it by playing around terminals and warehouse ( basically threads and number of rows)
SimplifieDB 