文档

TPC-C测试

更新时间:

本文介绍如何使用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内存)

测试方法

  1. 准备压力机ECS

    准备一个ECS(建议配置为32 vCPU 128 GB内存,避免在测试高规格集群时,压力机成为瓶颈),后续操作步骤中涉及的数据准备、运行压测等使用的都是这台ECS机器。

    说明

    测试所用ECS需要部署在VPC网络内。请记录该VPC的名称和ID,后续的所有实例都将部署在该VPC内。

  2. 准备压测所用PolarDB-X实例

    1. 创建PolarDB-X实例,详细操作步骤请参见创建实例

      说明

      需保证ECS和PolarDB-X实例在同一个VPC中。

    2. 在实例中创建一个待压测的数据库(本测试中数据库名为tpcc_1000)。

      CREATE DATABASE tpcc_1000  MODE = 'auto';
  3. 调整实例参数

    说明

    为了在压测场景下达到最佳性能,需要调整部分PolarDB-X计算层实例参数。

    1. 修改参数ENABLE_COROUTINE的值为true,XPROTO_MAX_DN_CONCURRENTXPROTO_MAX_DN_WAIT_CONNECTION的值为4000,详细操作步骤请参见参数设置

    2. 通过命令行连接到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;
  4. 准备压测数据

    1. 准备压测工具

      说明

      BenchmarkSQL默认不支持MySQL协议,需要进行工具改造适配(此处提供了基于开源BenchmarkSQL 5.0改动后的源码以及编译后的jar包)。

      下载压测工具包benchmarksql.tar.gz,并解压。

      tar xzvf benchmarksql.tar.gz
    2. 压测配置

      在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:压测时间

    3. 导入压测数据

      在压力机ECS中执行如下命令导入压测数据:

      cd benchmarksql/run/sql.common
      cp tableCreates.sql.auto  tableCreates.sql
      cd ..
      nohup ./runDatabaseBuild.sh props.mysql &
      说明

      默认按照100并发导入,总共5亿多条记录,整体导入时间在小时级别,建议通过nohup推到后台运行,避免ssh命令行断开导致导入中断。

    4. 验证数据完整性

      通过命令行连接到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;
  5. 执行压测

    执行如下命令运行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)

456789

规格

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)

456789

规格

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)

456789

规格

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)

45678

规格

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

测试结果(5.4.18-16977056)

image.png

规格

64并发

128并发

256并发

512并发

1024并发

4C32G*2

47314.31

55319.77

57606.85

57029.15

56038.31

4C32G*4

79258.68

106685.44

117155.52

124144.74

131597.53

8C64G*2

76933.01

88738.15

97725.2

103391.1

106294.78

8C64G*4

106772.47

159845.31

182079.45

197052.19

198381.5

  • 本页导读 (0)
文档反馈