TPC-H测试

背景信息

TPC-H是业界常用的一套Benchmark，由TPC委员会制定发布，用于评测数据库的分析型查询能力。TPC-H查询包含8张数据表、22条复杂的SQL查询，大多数查询包含若干表Join、子查询和Group-by聚合等。

测试设计

• 测试数据量

  测试基于100 GB数据量（Scalar Factor=100），其中主要表数据量如下：

  • LINEITEM表约6亿行

  • ORDERS表1.5亿行

  • PART_SUPP表8000万行

• 测试所用实例规格

  节点规格	节点数	数据集大小
  8C64G	6	100 GB

• 测试所用压力机规格

  ecs.g7.4xlarge（16 vCPU，64 GB内存，存储盘大于200 GB）

测试方法

1. 准备压力机ECS

   准备一个ECS（存储盘要求大于200 GB，需要存放工具生成的csv格式数据集），后续操作步骤中涉及的数据准备、运行压测等使用的都是这台ECS机器。

   说明

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

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

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

      说明

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

   2. 在实例中创建一个待压测的数据库（本测试中数据库名为tpch_100g），详细操作步骤请参见创建数据库。

      CREATE DATABASE tpch_100g;

   3. 在数据库tpch_100g中创建对应的表，方法如下：

      CREATE TABLE `customer` (
        `c_custkey` int(11) NOT NULL,
        `c_name` varchar(25) NOT NULL,
        `c_address` varchar(40) NOT NULL,
        `c_nationkey` int(11) NOT NULL,
        `c_phone` varchar(15) NOT NULL,
        `c_acctbal` decimal(15,2) NOT NULL,
        `c_mktsegment` varchar(10) NOT NULL,
        `c_comment` varchar(117) NOT NULL,
        PRIMARY KEY (`c_custkey`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 dbpartition by hash(`c_custkey`) tbpartition by hash(`c_custkey`) tbpartitions 4;
      
      CREATE TABLE `lineitem` (
        `l_orderkey` bigint(20) NOT NULL,
        `l_partkey` int(11) NOT NULL,
        `l_suppkey` int(11) NOT NULL,
        `l_linenumber` bigint(20) NOT NULL,
        `l_quantity` decimal(15,2) NOT NULL,
        `l_extendedprice` decimal(15,2) NOT NULL,
        `l_discount` decimal(15,2) NOT NULL,
        `l_tax` decimal(15,2) NOT NULL,
        `l_returnflag` varchar(1) NOT NULL,
        `l_linestatus` varchar(1) NOT NULL,
        `l_shipdate` date NOT NULL,
        `l_commitdate` date NOT NULL,
        `l_receiptdate` date NOT NULL,
        `l_shipinstruct` varchar(25) NOT NULL,
        `l_shipmode` varchar(10) NOT NULL,
        `l_comment` varchar(44) NOT NULL,
        KEY `IDX_LINEITEM_SUPPKEY` (`l_suppkey`),
        KEY `IDX_LINEITEM_PARTKEY` (`l_partkey`),
        KEY `IDX_LINEITEM_SHIPDATE` (`l_shipdate`),
        PRIMARY KEY (`l_orderkey`,`l_linenumber`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 dbpartition by RIGHT_SHIFT(`l_orderkey`,6) tbpartition by RIGHT_SHIFT(`l_orderkey`,6) tbpartitions 4;
      
      CREATE TABLE `orders` (
        `o_orderkey` bigint(20) NOT NULL,
        `o_custkey` int(11) NOT NULL,
        `o_orderstatus` varchar(1) NOT NULL,
        `o_totalprice` decimal(15,2) NOT NULL,
        `o_orderdate` date NOT NULL,
        `o_orderpriority` varchar(15) NOT NULL,
        `o_clerk` varchar(15) NOT NULL,
        `o_shippriority` bigint(20) NOT NULL,
        `o_comment` varchar(79) NOT NULL,
        PRIMARY KEY (`O_ORDERKEY`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 dbpartition by RIGHT_SHIFT(`O_ORDERKEY`,6) tbpartition by RIGHT_SHIFT(`O_ORDERKEY`,6) tbpartitions 4;
      
      CREATE TABLE `part` (
        `p_partkey` int(11) NOT NULL,
        `p_name` varchar(55) NOT NULL,
        `p_mfgr` varchar(25) NOT NULL,
        `p_brand` varchar(10) NOT NULL,
        `p_type` varchar(25) NOT NULL,
        `p_size` int(11) NOT NULL,
        `p_container` varchar(10) NOT NULL,
        `p_retailprice` decimal(15,2) NOT NULL,
        `p_comment` varchar(23) NOT NULL,
        PRIMARY KEY (`p_partkey`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 dbpartition by hash(`p_partkey`) tbpartition by hash(`p_partkey`) tbpartitions 4;
      
      CREATE TABLE `partsupp` (
        `ps_partkey` int(11) NOT NULL,
        `ps_suppkey` int(11) NOT NULL,
        `ps_availqty` int(11) NOT NULL,
        `ps_supplycost` decimal(15,2) NOT NULL,
        `ps_comment` varchar(199) NOT NULL,
        KEY `IDX_PARTSUPP_SUPPKEY` (`PS_SUPPKEY`),
        PRIMARY KEY (`ps_partkey`,`ps_suppkey`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 dbpartition by hash(`ps_partkey`) tbpartition by hash(`ps_partkey`) tbpartitions 4;
      
      CREATE TABLE `supplier` (
        `s_suppkey` int(11) NOT NULL,
        `s_name` varchar(25) NOT NULL,
        `s_address` varchar(40) NOT NULL,
        `s_nationkey` int(11) NOT NULL,
        `s_phone` varchar(15) NOT NULL,
        `s_acctbal` decimal(15,2) NOT NULL,
        `s_comment` varchar(101) NOT NULL,
        PRIMARY KEY (`s_suppkey`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 dbpartition by hash(`s_suppkey`) tbpartition by hash(`s_suppkey`) tbpartitions 4;
      
      CREATE TABLE `nation` (
        `n_nationkey` int(11) NOT NULL,
        `n_name` varchar(25) NOT NULL,
        `n_regionkey` int(11) NOT NULL,
        `n_comment` varchar(152) DEFAULT NULL,
        PRIMARY KEY (`n_nationkey`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 broadcast;
      
      CREATE TABLE `region` (
        `r_regionkey` int(11) NOT NULL,
        `r_name` varchar(25) NOT NULL,
        `r_comment` varchar(152) DEFAULT NULL,
        PRIMARY KEY (`r_regionkey`)
      ) ENGINE=InnoDB DEFAULT CHARSET=latin1 broadcast;

3. 调整实例参数

   说明

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

   1. 修改参数XPROTO_MAX_DN_CONCURRENT和XPROTO_MAX_DN_WAIT_CONNECTION的值为4000，详细操作步骤请参见参数设置。

   2. 通过命令行连接到PolarDB-X实例，在同一会话内执行如下SQL语句，关闭日志记录与CPU采样统计：

      set GLOBAL RECORD_SQL = false;
      set GLOBAL ENABLE_HTAP=true;
      set GLOBAL ENABLE_MASTER_MPP=true;
      set GLOBAL MPP_METRIC_LEVEL = 0;
      set GLOBAL ENABLE_CPU_PROFILE = false;
      set GLOBAL ENABLE_SORT_AGG=false;
      set GLOBAL MPP_PARALLELISM=192;
      set GLOBAL GROUP_PARALLELISM=8;

4. 数据准备

   1. 下载脚本tpchData.tar.gz至压力机ECS上，并解压：

      tar xzvf tpchData.tar.gz
      cd tpchData/
      vi params.conf

      修改params.conf配置文件，填入PolarDB-X实例的连接信息：

      #!/bin/bash
      
      ### remote generating directory
      export remoteGenDir=./
      
      ### target path
      export targetPath=../tpch/tpchRaw
      
      ### cores per worker, default value is 1
      export coresPerWorker=`cat /proc/cpuinfo| grep "processor"| wc -l`
      
      ### threads per worker, default value is 1
      export threadsPerWorker=`cat /proc/cpuinfo| grep "processor"| wc -l`
      #export threadsPerWorker=1
      
      export hint=""
      
      
      export insertMysql="mysql -h{HOST} -P{PORT} -u{USER} -p{PASSWORD} -Ac --local-infile tpch_100g -e"

      具体填入的值包括：

      • {HOST}：主机名

      • {PORT}：端口号

      • {USER}：用户名

      • {PASSWORD}：密码

      如果希望更高效地生成数据，可调大脚本中threadsPerWorker的值（如调整为压测机的CPU核数）。

   2. 执行脚本，多进程生成100 GB的数据：

      cd datagen
      sh generateTPCH.sh 100

      可以在tpch/tpchRaw/SF100/目录下查看到生成的数据：

      ls ../tpch/tpchRaw/SF100/
      customer  lineitem  nation  orders  part  partsupp  region  supplier

   3. 导入数据到PolarDB-X实例：

      cd ../loadTpch
      sh loadTpch.sh 100

   4. 校验数据完整性

      通过命令行连接到PolarDB-X实例，查询每张表的数据量是否符合预期：

      MySQL [tpch_100g]> select (select count(*) from customer) as customer_cnt,
       (select count(*)  from lineitem) as lineitem_cnt,
       (select count(*)  from nation) as nation_cnt,
       (select count(*)  from orders) as order_cnt,
       (select count(*) from part) as part_cnt,
       (select count(*) from partsupp) as partsupp_cnt,
       (select count(*) from region) as region_cnt,
       (select count(*) from supplier) as supplier_cnt;
      +--------------+--------------+------------+-----------+----------+--------------+------------+--------------+
      | customer_cnt | lineitem_cnt | nation_cnt | order_cnt | part_cnt | partsupp_cnt | region_cnt | supplier_cnt |
      +--------------+--------------+------------+-----------+----------+--------------+------------+--------------+
      |     15000000 |    600037902 |         25 | 150000000 | 20000000 |     80000000 |          5 |      1000000 |
      +--------------+--------------+------------+-----------+----------+--------------+------------+--------------+

   5. 采集统计信息

      通过命令行连接到PolarDB-X实例，执行analyze table收集表的统计信息：

      analyze table customer;
      analyze table lineitem;
      analyze table nation;
      analyze table orders;
      analyze table part;
      analyze table partsupp;
      analyze table region;
      analyze table supplier;

5. 进行测试

   1. 下载测试脚本tpch-queries.tar.gz并解压：

      tar xzvf tpch-queries.tar.gz

   2. 运行脚本，执行查询并计时：

      cd tpch-queries
      'time' -f "%e" sh all_query.sh {HOST} {USER} {PASSWORD} {DB} {PORT}

测试结果