本文介绍如何使用TPC-C工具测试PolarDB-X数据库的联机交易处理(偏向OLTP)的能力,您可以按照本文的介绍自行测试进行对比,快速了解数据库系统的性能。
背景信息
TPC-C是业界常用的一套Benchmark,由TPC委员会制定发布,用于评测数据库的联机交易处理(偏向OLTP)能力。主要涉及10张表,包含了NewOrder(新订单的生成)、Payment(订单付款)、OrderStatus(最近订单查询)、Delivery(配送)和StockLevel(库存缺货状态分析)等五类业务事务模型。TPC-C使用tpmC值(Transactions per Minute)来衡量系统最大有效吞吐量(MQTh,Max Qualified Throughput),其中Transactions以NewOrder Transaction为准,即最终衡量单位为每分钟处理的新订单数。
说明 本文中TPC-C的实现基于TPC-C的基准测试,并不能与已发布的TPC-C基准测试结果相比较,本文中的测试并不符合TPC-C基准测试的所有要求。
测试设计
- 测试数据量基于1000 Warehouse,其中主要表的数据量如下:
- bmsql_order_line表3亿行
- bmsql_stock表1亿行
- bmsql_customer、bmsql_history、bmsql_oorder各3000万行
- 测试所用实例规格
节点规格 节点数 4C32G 2 4C32G 4 8C64G 2 8C64G 4 - 测试所用压力机规格
ecs.g6.8xlarge(32 vCPU,128 GB内存)
测试方法
- 准备压力机ECS
准备一个ECS(建议配置为32 vCPU 128 GB内存,避免在测试高规格集群时,压力机成为瓶颈),后续操作步骤中涉及的数据准备、运行压测等使用的都是这台ECS机器。
说明 测试所用ECS需要部署在VPC网络内。请记录该VPC的名称和ID,后续的所有实例都将部署在该VPC内。 - 准备压测所用PolarDB-X实例
- 创建PolarDB-X实例,详细操作步骤请参见创建实例。说明 需保证ECS和PolarDB-X实例在同一个VPC中。
- 在实例中创建一个待压测的数据库(本测试中数据库名为tpcc_1000)。
CREATE DATABASE tpcc_1000 MODE = 'auto';
- 创建PolarDB-X实例,详细操作步骤请参见创建实例。
- 调整实例参数说明 为了在压测场景下达到最佳性能,需要调整部分PolarDB-X计算层实例参数。
- 修改参数ENABLE_COROUTINE的值为true,XPROTO_MAX_DN_CONCURRENT和XPROTO_MAX_DN_WAIT_CONNECTION的值为4000,详细操作步骤请参见参数设置。
- 通过命令行连接到PolarDB-X实例,在同一会话内执行如下SQL语句,关闭日志记录与CPU采样统计:
set global RECORD_SQL=false; set global MPP_METRIC_LEVEL=0; set global ENABLE_CPU_PROFILE=false; set global ENABLE_BACKGROUND_STATISTIC_COLLECTION=false; set global ENABLE_STATISTIC_FEEDBACK=false; set global ENABLE_DEADLOCK_DETECTION=false; set global ENABLE_TRANS_LOG=false; set global GROUP_PARALLELISM=1; set global CONN_POOL_MAX_POOL_SIZE=500; set global ENABLE_STATEMENTS_SUMMARY=false; set global ENABLE_AUTO_SAVEPOINT=false; set global INNODB_ADAPTIVE_HASH_INDEX=off; set global TABLE_OPEN_CACHE = 20000; set global SHARE_READ_VIEW = false; set global CONN_POOL_XPROTO_XPLAN = true; set global NEW_SEQ_GROUPING_TIMEOUT=30000;
- 准备压测数据
- 准备压测工具说明 BenchmarkSQL默认不支持MySQL协议,需要进行工具改造适配(此处提供了基于开源BenchmarkSQL 5.0改动后的源码以及编译后的jar包)。
下载压测工具包benchmarksql.tar.gz,并解压。
tar xzvf benchmarksql.tar.gz
- 压测配置在ECS上执行如下命令,修改props.mysql配置文件,并填入对应的PolarDB-X实例连接信息:
cd benchmarksql/run vi props.mysql
其中, 配置文件以及主要参数解释如下:
db=mysql driver=com.mysql.jdbc.Driver conn=jdbc:mysql://{HOST}:{PORT}/tpcc?readOnlyPropagatesToServer=false&rewriteBatchedStatements=true&failOverReadOnly=false&connectTimeout=3000&socketTimeout=90000&allowMultiQueries=true&clobberStreamingResults=true&characterEncoding=utf8&netTimeoutForStreamingResults=0&autoReconnect=true user={USER} password={PASSWORD} warehouses=1000 loadWorkers=100 terminals=128 //To run specified transactions per terminal- runMins must equal zero runTxnsPerTerminal=0 //To run for specified minutes- runTxnsPerTerminal must equal zero runMins=5 //Number of total transactions per minute limitTxnsPerMin=0 //Set to true to run in 4.x compatible mode. Set to false to use the //entire configured database evenly. terminalWarehouseFixed=true //The following five values must add up to 100 //The default percentages of 45, 43, 4, 4 & 4 match the TPC-C spec newOrderWeight=45 paymentWeight=43 orderStatusWeight=4 deliveryWeight=4 stockLevelWeight=4 // Directory name to create for collecting detailed result data. // Comment this out to suppress. resultDirectory=my_result_%tY-%tm-%td_%tH%tM%tS // osCollectorScript=./misc/os_collector_linux.py // osCollectorInterval=1 // osCollectorSSHAddr=user@dbhost // osCollectorDevices=net_eth0 blk_sda
- conn:连接串配置,需填入主机名{HOST}、端口号{PORT}
- user:用户名
- password:密码
- warehouses:仓库数
- loadWorkers:导入数据并发数
- terminals:压测并发数
- runMins:压测时间
- 导入压测数据
在压力机ECS中执行如下命令导入压测数据:
cd benchmarksql/run/sql.common cp tableCreates.sql.auto tableCreates.sql cd .. nohup ./runDatabaseBuild.sh props.mysql &
说明 默认按照100并发导入,总共5亿多条记录,整体导入时间在小时级别,建议通过nohup推到后台运行,避免ssh命令行断开导致导入中断。 - 验证数据完整性
通过命令行连接到PolarDB-X实例执行以下SQL,若返回结果集均为空,证明数据完整。
select a.* from (Select w_id, w_ytd from bmsql_warehouse) a left join (select d_w_id, sum(d_ytd) as d_ytd_sum from bmsql_district group by d_w_id) b on a.w_id = b.d_w_id and a.w_ytd = b.d_ytd_sum where b.d_w_id is null; select a.* from (Select d_w_id, d_id, D_NEXT_O_ID - 1 as d_n_o_id from bmsql_district) a left join (select o_w_id, o_d_id, max(o_id) as o_id_max from bmsql_oorder group by o_w_id, o_d_id) b on a.d_w_id = b.o_w_id and a.d_id = b.o_d_id and a.d_n_o_id = b.o_id_max where b.o_w_id is null; select a.* from (Select d_w_id, d_id, D_NEXT_O_ID - 1 as d_n_o_id from bmsql_district) a left join (select no_w_id, no_d_id, max(no_o_id) as no_id_max from bmsql_new_order group by no_w_id, no_d_id) b on a.d_w_id = b.no_w_id and a.d_id = b.no_d_id and a.d_n_o_id = b.no_id_max where b.no_id_max is null; select * from (select (count(no_o_id)-(max(no_o_id)-min(no_o_id)+1)) as diff from bmsql_new_order group by no_w_id, no_d_id) a where diff != 0; select a.* from (select o_w_id, o_d_id, sum(o_ol_cnt) as o_ol_cnt_cnt from bmsql_oorder group by o_w_id, o_d_id) a left join (select ol_w_id, ol_d_id, count(ol_o_id) as ol_o_id_cnt from bmsql_order_line group by ol_w_id, ol_d_id) b on a.o_w_id = b.ol_w_id and a.o_d_id = b.ol_d_id and a.o_ol_cnt_cnt = b.ol_o_id_cnt where b.ol_w_id is null; select a.* from (select d_w_id, sum(d_ytd) as d_ytd_sum from bmsql_district group by d_w_id) a left join (Select w_id, w_ytd from bmsql_warehouse) b on a.d_w_id = b.w_id and a.d_ytd_sum = b.w_ytd where b.w_id is null;
- 准备压测工具
- 执行压测
执行如下命令运行TPC-C测试:
cd benchmarksql/run ./runBenchmark.sh props.mysql
运行后可以看到实时的tpmC数值,运行结束后会显示平均的tpmC数值。
[2022/01/26 22:00:22.995] Average tpmC: 228425.52 Current tpmC: 235152.00 Memory Usage: 1001MB / 3584MB [2022/01/26 22:00:27.995] Average tpmC: 228641.71 Current tpmC: 235764.00 Memory Usage: 571MB / 3584MB [2022/01/26 22:00:32.995] Average tpmC: 228816.06 Current tpmC: 234744.00 Memory Usage: 101MB / 3584MB [2022/01/26 22:00:37.995] Average tpmC: 229017.73 Current tpmC: 236076.00 Memory Usage: 705MB / 3584MB 22:00:38,071 [Thread-132] INFO jTPCC : Term-00, 22:00:38,071 [Thread-132] INFO jTPCC : Term-00, 22:00:38,071 [Thread-132] INFO jTPCC : Term-00, Measured tpmC (NewOrders) = 228974.04 22:00:38,071 [Thread-132] INFO jTPCC : Term-00, Measured tpmTOTAL = 509473.72 22:00:38,071 [Thread-132] INFO jTPCC : Term-00, Session Start = 2022-01-26 21:57:37 22:00:38,072 [Thread-132] INFO jTPCC : Term-00, Session End = 2022-01-26 22:00:38 22:00:38,072 [Thread-132] INFO jTPCC : Term-00, Transaction Count = 1529074
测试结果(5.4.15-16704996)

规格 | 64并发 | 128并发 | 256并发 | 512并发 | 1024并发 |
---|---|---|---|---|---|
4C32G*2 | 45678.47 | 53782.37 | 57207.99 | 58555.13 | 58480.75 |
4C32G*4 | 70967.94 | 93894.19 | 103129.87 | 108333.55 | 109577.55 |
8C64G*2 | 69844.47 | 83187.97 | 85713.53 | 84503.02 | 88038.26 |
8C64G*4 | 89328.72 | 136759.65 | 167809.46 | 175860.5 | 175353.3 |
测试结果(5.4.15-16715927)

规格 | 64并发 | 128并发 | 256并发 | 512并发 | 1024并发 |
---|---|---|---|---|---|
4C32G*2 | 48302.1 | 54025.71 | 56791.2 | 57970.9 | 58525.67 |
4C32G*4 | 70136.84 | 93234.02 | 105241.67 | 108523.41 | 110534.65 |
8C64G*2 | 67648.21 | 81783.57 | 86289.67 | 84795.9 | 85566.34 |
8C64G*4 | 89747.01 | 139412.56 | 166902.1 | 175364.84 | 175885.95 |
测试结果(5.4.16-16717637)

规格 | 64并发 | 128并发 | 256并发 | 512并发 | 1024并发 |
---|---|---|---|---|---|
4C32G*2 | 49446.92 | 51847.91 | 55700.14 | 59902.5 | 61152.11 |
4C32G*4 | 65141.8 | 93897.91 | 106942.28 | 112468.97 | 108613.16 |
8C64G*2 | 68142.57 | 81461.78 | 84999.79 | 84479.3 | 86595.75 |
8C64G*4 | 89103.31 | 138964.28 | 170095.07 | 176831.98 | 178269.1 |
测试结果(5.4.17-16835173)

规格 | 64并发 | 128并发 | 256并发 | 512并发 | 1024并发 |
---|---|---|---|---|---|
4C32G*2 | 47269.9 | 52457.07 | 56329.34 | 57915.96 | 58362.74 |
4C32G*4 | 77575.18 | 99646.09 | 111541.96 | 119054.9 | 120512.74 |
8C64G*2 | 79745.5 | 93985.89 | 102638.59 | 109948.82 | 109605.32 |
8C64G*4 | 99152.31 | 150250.64 | 175110.19 | 199713.18 | 202801.12 |