TPC-C Benchmark Test #

TPC-C test scenario simulates how the online e-commerce transaction works.

Suppose there’s a large commodity wholesaler and its businesses stretch across multiple districts and are managed through warehouses. When its business expands, the company will add new warehouses.

Each warehouse supplies 10 areas and each area serves 3,000 customers. All its warehouses maintain and record the stock of the 100,000 items the company is selling.

Each customer order includes 10 order lines on average. And around 1% of the order lines are out of stock in the warehouses they belong to, which have to be supplied by other warehouses.

Customers issue new orders to the company’s system or query their order status in the system.

The system is also used to deal with payments and orders that have been delivered, examining the stock to find potential supply insufficiency.

The following figure illustrates the relationships between warehouses, districts, and customers.

Test Plan A: performance test with a single storage node and a computing node #

Use BenchmarkSQL to test DBPlusEngine-Proxy and DBPlusEngine-Driver with a single storage node and compare their performance with MySQL’s.

Test Objective #

BenchmarkSQL stress testing model is used to compare the performance of DBPlusEngine-Proxy, DBPlusEngine-Driver, and MySQL with the same amount of data so that users can have a detailed understanding of the performance of DBPlusEngine-Proxy and DBPlusEngine-Driver.

Test Tool #

BenchmarkSQL is a typical open-source database test tool with embedded TPC-C test scripts, which can test PostgreSQL, MySQL, Oracle, SQL Server, and other databases.

Test Environment #

Application	IP Address	Port	Version
DBPlusEngine-Proxy	192.168.xx.25	3307	1.2
DBPlusEngine-Driver	192.168.xx.24	-	1.2
MySQL	192.168.xx.20	13306	8.0.29

Server Configuration

Item	Configuration
CPU	48 C
Memory	96 G
Hard Disk	SSD 820 G
JDK	17.0.2

Test Procedure #

Create a test database.

mysql -utest -h192.168.xx.20 -P13306 -p

DROP DATABASE IF EXISTS test_tpcc;

CREATE DATABASE IF NOT EXISTS test_tpcc;

Install test tools.

wget https://udomain.dl.sourceforge.net/project/benchmarksql/benchmarksql-5.0.zip
yum -y install ant
unzip benchmarksql-5.0.zip -d  /usr/local
cd /usr/local/benchmarksql-5.0
ant
cp mysql-connector-java-8.0.24.jar benchmarksql-5.0/run/lib

Create props.proxy file.

db=postgres
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://192.168.xx.25:3307/sharding_db?useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=8192&prepStmtCacheSqlLimit=8000
user=root
password=root
warehouses=200
loadWorkers=100
terminals=200
runTxnsPerTerminal=0
runMins=10
limitTxnsPerMin=0
terminalWarehouseFixed=true
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
shardingNumber=1

YAML

rules:
  - !SHARDING
    bindingTables:
      - bmsql_warehouse, bmsql_customer
      - bmsql_stock, bmsql_district, bmsql_order_line
    defaultDatabaseStrategy:
      none:
    defaultTableStrategy:
      none:
    keyGenerators:
      snowflake:
        type: SNOWFLAKE
    tables:
      bmsql_config:
        actualDataNodes: ds_0.bmsql_config

      bmsql_warehouse:
        actualDataNodes: ds_${0..0}.bmsql_warehouse
        databaseStrategy:
          standard:
            shardingColumn: w_id
            shardingAlgorithmName: mod_1

      bmsql_district:
        actualDataNodes: ds_${0..0}.bmsql_district
        databaseStrategy:
          standard:
            shardingColumn: d_w_id
            shardingAlgorithmName: mod_1

      bmsql_customer:
        actualDataNodes: ds_${0..0}.bmsql_customer
        databaseStrategy:
          standard:
            shardingColumn: c_w_id
            shardingAlgorithmName: mod_1

      bmsql_item:
        actualDataNodes: ds_${0..0}.bmsql_item
        databaseStrategy:
          standard:
            shardingColumn: i_id
            shardingAlgorithmName: mod_1

      bmsql_history:
        actualDataNodes: ds_${0..0}.bmsql_history
        databaseStrategy:
          standard:
            shardingColumn: h_w_id
            shardingAlgorithmName: mod_1

      bmsql_oorder:
        actualDataNodes: ds_${0..0}.bmsql_oorder
        databaseStrategy:
          standard:
            shardingColumn: o_w_id
            shardingAlgorithmName: mod_1

      bmsql_stock:
        actualDataNodes: ds_${0..0}.bmsql_stock
        databaseStrategy:
          standard:
            shardingColumn: s_w_id
            shardingAlgorithmName: mod_1

      bmsql_new_order:
        actualDataNodes: ds_${0..0}.bmsql_new_order
        databaseStrategy:
          standard:
            shardingColumn: no_w_id
            shardingAlgorithmName: mod_1

      bmsql_order_line:
        actualDataNodes: ds_${0..0}.bmsql_order_line
        databaseStrategy:
          standard:
            shardingColumn: ol_w_id
            shardingAlgorithmName: mod_1

    shardingAlgorithms:
      mod_1:
        type: MOD
        props:
          sharding-count: 1

Obtain data

Initialize the database, create tables through Proxy and insert data.

cd /usr/local/benchmarksql/run

./runDatabaseDestroy.sh props.proxy

./runDatabaseBuild.sh props.proxy

Carry out stress testing.

./runBenchmark.sh props.proxy

Test Result #

Test Object	tpmC
MySQL	180,300
DBPlusEngine-Proxy	122,299
DBPlusEngine-Driver	175,392

Result: with a single storage node, DBPlusEngine-Driver is slightly lower than MySQL and higher than DBPlusEngine-Proxy in terms of performance.

Monitoring Information #

BenchmarkSQL ——> MySQL #

MySQL

BenchmarkSQL ——> Proxy ——> MySQL #

MySQL

Proxy

BenchmarkSQL ——> Driver ——> MySQL #

MySQL

Driver

Glossary #

Warehouses: the number of warehouses, which determines the amount of data.

Terminals: the number of threads

runMins: test duration

tpmC: transactions per min

Test Plan B: Scale-out the storage node #

Use the BenchmarkSQL tool to carry out stress testing on two and four storage nodes respectively.

Test Objective #

With the same amount of data, the BenchmarkSQL stress testing model is used to compare the results of DBPlusEngine-Proxy with two or four storage nodes. By analyzing the results, it’s easier for users to understand the advantages of sharding scenarios.

Test Tool #

BenchmarkSQL

Test Environment #

Application	IP Address	Port	Version
DBPlusEngine-Proxy	192.168.xx.25	3307	1.2
MySQL	192.168.xx.20	13306	8.0.29
MySQL	192.168.xx.21	13306	8.0.29
MySQL	192.168.xx.22	13306	8.0.29
MySQL	192.168.xx.23	13306	8.0.29

Server Configuration

Item	Configuration
CPU	48 C
Memory	96 G
Hard Disk	SSD 820 G
JDK	17.0.2

Test Procedure #

Create a test table.

mysql -utest -h192.168.xx.20 -P13306 -p

DROP DATABASE IF EXISTS test_tpcc;

CREATE DATABASE IF NOT EXISTS test_tpcc;

mysql -utest -h192.168.xx.21 -P13306 -p

DROP DATABASE IF EXISTS test_tpcc;

CREATE DATABASE IF NOT EXISTS test_tpcc;

mysql -utest -h192.168.xx.22 -P13306 -p

DROP DATABASE IF EXISTS test_tpcc;

CREATE DATABASE IF NOT EXISTS test_tpcc;

mysql -utest -h192.168.xx.23 -P13306 -p

DROP DATABASE IF EXISTS test_tpcc;

CREATE DATABASE IF NOT EXISTS test_tpcc;

Install test tools.

wget https://udomain.dl.sourceforge.net/project/benchmarksql/benchmarksql-5.0.zip
yum -y install ant
unzip benchmarksql-5.0.zip -d  /usr/local
cd /usr/local/benchmarksql-5.0
ant
cp mysql-connector-java-8.0.24.jar benchmarksql-5.0/run/lib

Create props.proxy file.

db=postgres
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://192.168.xx.25:3307/sharding_db?useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=8192&prepStmtCacheSqlLimit=8000
user=root
password=root
warehouses=200
loadWorkers=100
terminals=200
runTxnsPerTerminal=0
runMins=10
limitTxnsPerMin=0
terminalWarehouseFixed=true
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
shardingNumber=2

YAML

rules:
  - !SHARDING
    bindingTables:
      - bmsql_warehouse, bmsql_customer
      - bmsql_stock, bmsql_district, bmsql_order_line
    defaultDatabaseStrategy:
      none:
    defaultTableStrategy:
      none:
    keyGenerators:
      snowflake:
        type: SNOWFLAKE
    tables:
      bmsql_config:
        actualDataNodes: ds_0.bmsql_config

      bmsql_warehouse:
        actualDataNodes: ds_${0..1}.bmsql_warehouse
        databaseStrategy:
          standard:
            shardingColumn: w_id
            shardingAlgorithmName: mod_2

      bmsql_district:
        actualDataNodes: ds_${0..1}.bmsql_district
        databaseStrategy:
          standard:
            shardingColumn: d_w_id
            shardingAlgorithmName: mod_2

      bmsql_customer:
        actualDataNodes: ds_${0..1}.bmsql_customer
        databaseStrategy:
          standard:
            shardingColumn: c_w_id
            shardingAlgorithmName: mod_2

      bmsql_item:
        actualDataNodes: ds_${0..1}.bmsql_item
        databaseStrategy:
          standard:
            shardingColumn: i_id
            shardingAlgorithmName: mod_2

      bmsql_history:
        actualDataNodes: ds_${0..1}.bmsql_history
        databaseStrategy:
          standard:
            shardingColumn: h_w_id
            shardingAlgorithmName: mod_2

      bmsql_oorder:
        actualDataNodes: ds_${0..1}.bmsql_oorder
        databaseStrategy:
          standard:
            shardingColumn: o_w_id
            shardingAlgorithmName: mod_2

      bmsql_stock:
        actualDataNodes: ds_${0..1}.bmsql_stock
        databaseStrategy:
          standard:
            shardingColumn: s_w_id
            shardingAlgorithmName: mod_2

      bmsql_new_order:
        actualDataNodes: ds_${0..1}.bmsql_new_order
        databaseStrategy:
          standard:
            shardingColumn: no_w_id
            shardingAlgorithmName: mod_2

      bmsql_order_line:
        actualDataNodes: ds_${0..1}.bmsql_order_line
        databaseStrategy:
          standard:
            shardingColumn: ol_w_id
            shardingAlgorithmName: mod_2

    shardingAlgorithms:
      mod_2:
        type: MOD
        props:
          sharding-count: 2

Obtain data.

Initialize the database, create tables through Proxy and insert data.

cd /usr/local/benchmarksql/run

./runDatabaseDestroy.sh props.proxy

./runDatabaseBuild.sh props.proxy

Carry out stress testing.

./runBenchmark.sh props.proxy

Test Result #

Test Object	Storage Node AVG(CPU Load)	tpmC
DBPlusEngine-Proxy(1 storage node)	73%	122,299
DBPlusEngine-Proxy(2 storage nodes)	30%	122,580
DBPlusEngine-Proxy(4 storage nodes)	12.5%	128,035

Result: with one Proxy node plus one storage node, it can be seen that the performance bottleneck is located on the CPU of the Proxy server. Therefore, scaling out the storage node only witnessed a slight improvement in performance, and its advantage lies in the decrease of the CPU load of each storage node.

The result is for reference only. Users can carry out a test based on actual business scenarios.

Monitoring Information #

BenchmarkSQL ——> Proxy ——> MySQL(1) #

MySQL

Proxy

BenchmarkSQL ——> Proxy ——> MySQL(2) #

MySQL(1)

MySQL(2)

Proxy

BenchmarkSQL ——> Proxy ——> MySQL(4) #

MySQL(1)

MySQL(2)

MySQL(3)

MySQL(4)

Proxy

Glossary #

Warehouses: the number of warehouses, which determines the amount of data.

Terminals: the number of threads.

runMins: test duration.

tpmC: transactions per min.

Test Plan C: Scale-out the computing node. #

Use BenchmarkSQL tools to carry out stress testing on four storage nodes with one computing node and two computing nodes respectively.

Test Objective #

With the same amount of data, the BenchmarkSQL stress testing model is used to compare the performance of DBPlusEngine-Proxy under four storage nodes with one or two computing nodes. By analyzing the results, it’s easier for users to understand the advantages of computing node scale-out.

Test Tool #

BenchmarkSQL

Test Environment #

Application	IP Address	Port	Version
DBPlusEngine-Proxy	192.168.xx.25	3307	1.2
DBPlusEngine-Proxy	192.168.xx.19	3307	1.2
MySQL	192.168.xx.20	13306	8.0.29
MySQL	192.168.xx.21	13306	8.0.29
MySQL	192.168.xx.22	13306	8.0.29
MySQL	192.168.xx.23	13306	8.0.29

Server Configuration

Item	Configuration
CPU	48 C
Memory	96 G
Hard Disk	SSD 820 G
JDK	17.0.2

Test Procedure #

Create a test table.

mysql -utest -h192.168.xx.20 -P13306 -p

DROP DATABASE IF EXISTS test_tpcc;

CREATE DATABASE IF NOT EXISTS test_tpcc;

mysql -utest -h192.168.xx.21 -P13306 -p

DROP DATABASE IF EXISTS test_tpcc;

CREATE DATABASE IF NOT EXISTS test_tpcc;

mysql -utest -h192.168.xx.22 -P13306 -p

DROP DATABASE IF EXISTS test_tpcc;

CREATE DATABASE IF NOT EXISTS test_tpcc;

mysql -utest -h192.168.xx.23 -P13306 -p

DROP DATABASE IF EXISTS test_tpcc;

CREATE DATABASE IF NOT EXISTS test_tpcc;

Install test tools.

wget https://udomain.dl.sourceforge.net/project/benchmarksql/benchmarksql-5.0.zip
yum -y install ant
unzip benchmarksql-5.0.zip -d  /usr/local
cd /usr/local/benchmarksql-5.0
ant
cp mysql-connector-java-8.0.24.jar benchmarksql-5.0/run/lib

Create props.proxy file.

db=postgres
driver=com.mysql.jdbc.Driver
conn=jdbc:mysql://192.168.xx.25:3307/sharding_db?useSSL=false&useServerPrepStmts=true&cachePrepStmts=true&prepStmtCacheSize=8192&prepStmtCacheSqlLimit=8000
user=root
password=root
warehouses=200
loadWorkers=80
terminals=200
runTxnsPerTerminal=0
runMins=10
limitTxnsPerMin=0
terminalWarehouseFixed=true
newOrderWeight=45
paymentWeight=43
orderStatusWeight=4
deliveryWeight=4
stockLevelWeight=4
resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS
shardingNumber=4
connBalance=192.168.xx.25:3333,192.168.xx.19:3333

YAML

rules:
  - !SHARDING
    bindingTables:
      - bmsql_warehouse, bmsql_customer
      - bmsql_stock, bmsql_district, bmsql_order_line
    defaultDatabaseStrategy:
      none:
    defaultTableStrategy:
      none:
    keyGenerators:
      snowflake:
        type: SNOWFLAKE
    tables:
      bmsql_config:
        actualDataNodes: ds_0.bmsql_config

      bmsql_warehouse:
        actualDataNodes: ds_${0..3}.bmsql_warehouse
        databaseStrategy:
          standard:
            shardingColumn: w_id
            shardingAlgorithmName: mod_4

      bmsql_district:
        actualDataNodes: ds_${0..3}.bmsql_district
        databaseStrategy:
          standard:
            shardingColumn: d_w_id
            shardingAlgorithmName: mod_4

      bmsql_customer:
        actualDataNodes: ds_${0..3}.bmsql_customer
        databaseStrategy:
          standard:
            shardingColumn: c_w_id
            shardingAlgorithmName: mod_4

      bmsql_item:
        actualDataNodes: ds_${0..3}.bmsql_item
        databaseStrategy:
          standard:
            shardingColumn: i_id
            shardingAlgorithmName: mod_4

      bmsql_history:
        actualDataNodes: ds_${0..3}.bmsql_history
        databaseStrategy:
          standard:
            shardingColumn: h_w_id
            shardingAlgorithmName: mod_4

      bmsql_oorder:
        actualDataNodes: ds_${0..3}.bmsql_oorder
        databaseStrategy:
          standard:
            shardingColumn: o_w_id
            shardingAlgorithmName: mod_4

      bmsql_stock:
        actualDataNodes: ds_${0..3}.bmsql_stock
        databaseStrategy:
          standard:
            shardingColumn: s_w_id
            shardingAlgorithmName: mod_4

      bmsql_new_order:
        actualDataNodes: ds_${0..3}.bmsql_new_order
        databaseStrategy:
          standard:
            shardingColumn: no_w_id
            shardingAlgorithmName: mod_4

      bmsql_order_line:
        actualDataNodes: ds_${0..3}.bmsql_order_line
        databaseStrategy:
          standard:
            shardingColumn: ol_w_id
            shardingAlgorithmName: mod_4

    shardingAlgorithms:
      mod_4:
        type: MOD
        props:
          sharding-count: 4

Obtain data.

Initialize the database, create tables through Proxy and insert data.

cd /usr/local/benchmarksql/run

./runDatabaseDestroy.sh props.proxy

./runDatabaseBuild.sh props.proxy

Carry out stress testing.

./runBenchmark.sh props.proxy

Test Result #

Test Object	tpmC	Storage Node AVG(CPU Load)	Computing Node AVG(CPU Load)
4 storage nodes + 1 DBPlusEngine-Proxy	128,035	12.5%	98%
4 storage nodes + 2 DBPlusEngine-Proxy	244,837	28%	78%

Result: scaling out computing nodes can improve the overall throughput linearly.

Monitoring Information #

BenchmarkSQL ——> Proxy ——> MySQL(4) #

MySQL(1)

MySQL(2)

MySQL(3)

MySQL(4)

Proxy

BenchmarkSQL ——> Proxy(2) ——> MySQL(4) #

MySQL(1)

MySQL(2)

MySQL(3)

MySQL(4)

Proxy(1)

Proxy(2)

Glossary #

Warehouses: the number of warehouses, which determines the amount of data.

Terminals: the number of threads.

runMins: test duration.

tpmC: transactions per min.