TDSQL Boundless TPC-C 测试

一、测试概述

注意：说明：

注意：TPC-C 是业界常用的一套 Benchmark，由 TPC 委员会制定发布，用于评测数据库的联机交易处理（偏向 OLTP）能力。主要涉及10张表，包含了 NewOrder（新订单的生成）、Payment（订单付款）、OrderStatus（最近订单查询）、Delivery（配送）和 StockLevel（库存缺货状态分析）等五类业务事务模型。TPC-C 使用 tpmC 值（Transactions per Minute）来衡量系统最大有效吞吐量（MQTh，Max Qualified Throughput），其中Transactions 以 NewOrder Transaction 为准，即最终衡量单位为每分钟处理的新订单数。

二、硬件环境

三、软件版本

四、参数配置

set persist audit_log_policy = "NONE";set persist max_prepared_stmt_count = 1000000;set persist temptable_max_mmap = 214748364800

五、准备压测工具

下载社区的开源的 BenchmarkSQL 压测工具，解压缩并进行测试。

六、准备测试数据

基于1000 Warehouse 定量进行。在压测机上执行如下命令，修改 props.mysql配置文件，并填写对应的实例连接信息：

cd benchmarksql/runvi props.mysql

其中，配置文件 props.mysql及主要参数如下：

db=mysqldriver=com.mysql.jdbc.Driver conn=jdbc:mysql://{ip}:{port}/tpcc?useSSL=false&useServerPrepStmts=true&useConfigs=maxPerformance&rewriteBatchedStatements=true&cachePrepStmts=true&prepStmtCacheSize=1000&prepStmtCacheSqlLimit=2048user={user}password={password}warehouses=1000 loadWorkers=20 terminals={terminals} runTxnsPerTerminal=0runMins=5 limitTxnsPerMin=0 terminalWarehouseFixed=truenewOrderWeight=45paymentWeight=43orderStatusWeight=4deliveryWeight=4stockLevelWeight=4

创建数据库：

create database tpcc;

开始创建表和索引，修改核心表的表定义为 Hash 分区表，默认创建9个分区：

cd benchmarksql/run/sql.common# 修改表结构，核心表定义为Hash分区表，默认9个分区cp tableCreates.sql tableCreates_tdsql.sqlvi tableCreates_tdsql.sql CREATE 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), primary key(w_id)) partition by hash(w_id) partitions 9;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), PRIMARY KEY (d_w_id, d_id)) partition by hash(d_w_id) partitions 9;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), PRIMARY KEY (c_w_id, c_d_id, c_id)) partition by hash(c_w_id) partitions 9;CREATE TABLE bmsql_history ( hist_id integer, 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)) partition by hash(h_w_id) partitions 9;CREATE TABLE bmsql_new_order ( no_w_id integer not null , no_d_id integer not null, no_o_id integer not null, PRIMARY KEY (no_w_id, no_d_id, no_o_id)) partition by hash(no_w_id) partitions 9;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, PRIMARY KEY (o_w_id, o_d_id, o_id)) partition by hash(o_w_id) partitions 9;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), PRIMARY KEY (ol_w_id, ol_d_id, ol_o_id, ol_number)) partition by hash(ol_w_id) partitions 9;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, 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), PRIMARY KEY (s_w_id, s_i_id)) partition by hash(s_w_id) partitions 9;

库表创建完毕后，开始导入压测数据：

cd ..nohup ./runLoader.sh props.mysql &

数据导入完成后，执行如下 SQL 创建索引：

create index bmsql_customer_idx1 on bmsql_customer (c_w_id, c_d_id, c_last, c_first);create unique index bmsql_oorder_idx1 on bmsql_oorder (o_w_id, o_d_id, o_carrier_id, o_id);

七、执行测试

运行以下命令运行 TPC-C 测试：

cd benchmarksql/run./runBenchmark.sh props.mysql

八、测试结果