本文档介绍以TPC-H测试PolarDB MySQL 8.0版集群OLAP负载性能,您可以按照本文介绍自行测试对比,快速了解数据库系统的性能。

并行查询简介

PolarDB MySQL数据库8.0版推出并行查询(Parallel Query)框架。并行查询默认为关闭状态,当您开启并行查询后,查询数据量到达一定阈值,就会自动启动并行查询框架,从而使查询耗时下降。

说明 您可以通过设置loose_max_parallel_degree参数来开启并行查询,如何设置集群参数请参见设置集群参数

loose_max_parallel_degree参数说明如下:

  • 最小值为0(关闭并行查询)。
  • 最大值为1024。
  • 建议设置并行查询参数为16。

PolarDB MySQL数据库8.0版在存储层将数据分片到不同的线程上,多个线程并行计算,将结果流水线汇聚到总线程,最后由总线程做简单归并将结果返回给用户,提高查询效率。

并行查询利用多核CPU的并行处理能力,以8核32G规格的集群为例,并行查询示意图如下所示。

并行查询示意图

下文将介绍并行查询参数分别设置为16与0时,PolarDB集群负载性能的测试方式与测试结果。

测试环境

  • 测试的ECS和PolarDB均在同一地域、同一可用区。本例中为华东1(杭州)可用区I。
  • 网络类型均为VPC网络。
    说明 ESC实例和PolarDB集群需保证在同一个VPC中。
  • 测试用PolarDB集群如下:
    • 节点规格为polar.mysql.x8.4xlarge(32核256G)。
    • 数据库版本为MySQL 8.0。
    • 节点数量为2个(一个主节点,一个只读节点)。
    • 使用的连接串为主地址,如何查看PolarDB主地址请参见查看连接地址
  • 测试用ECS实例信息如下:
    • 实例规格为ecs.c5.4xlarge。
    • 实例挂载1000G高效云盘。
    • 实例所使用的镜像为CentOS 7.0 64位。

测试工具

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

安装TPC-H

  1. 在ECS上安装TPC-H。
    说明
    • 本文使用的TPC-H版本为v2.18.0。
    • TPC-H需要完成注册后才可以下载。
    下载TPC-H
  2. 打开dbgen目录。
    cd dbgen
  3. 复制makefile文件。
    cp makefile.suite makefile
  4. 修改makefile文件中的CC、DATABASE、MACHINE、WORKLOAD等参数定义。
    1. 打开makefile文件。
      vim makefile
    2. 修改CC、DATABASE、MACHINE、WORKLOAD参数的定义。
        ################
        ## CHANGE NAME OF ANSI COMPILER HERE
        ################
        CC      = gcc
        # Current values for DATABASE are: INFORMIX, DB2, ORACLE,
        #                                  SQLSERVER, SYBASE, TDAT (Teradata)
        # Current values for MACHINE are:  ATT, DOS, HP, IBM, ICL, MVS,
        #                                  SGI, SUN, U2200, VMS, LINUX, WIN32
        # Current values for WORKLOAD are:  TPCH
        DATABASE= MYSQL
        MACHINE = LINUX
        WORKLOAD = TPCH
    3. 按ECS键,然后输入:wq退出并保存。
  5. 修改tpcd.h文件,并添加新的宏定义。
    1. 打开tpcd.h文件。
      vim tpcd.h
    2. 添加如下宏定义。
      #ifdef MYSQL
      #define GEN_QUERY_PLAN ""
      #define START_TRAN "START TRANSACTION"
      #define END_TRAN "COMMIT"
      #define SET_OUTPUT ""
      #define SET_ROWCOUNT "limit %d;\n"
      #define SET_DBASE "use %s;\n"
      #endif
    3. 按ECS键,然后输入:wq退出并保存。
  6. 对文件进行编译。
    make

    编译完成后该目录下会生成两个可执行文件:

    • dbgen:数据生成工具。在使用InfiniDB官方测试脚本进行测试时,需要用该工具生成tpch相关表数据。
    • qgen:SQL生成工具。生成初始化测试查询,由于不同的seed生成的查询不同,为了结果的可重复性,请使用附件提供的22个查询。
  7. 使用TPC-H生成测试数据。
    ./dbgen -s 100

    dbgen参数-s的作用是指定生成测试数据的仓库数。

  8. 使用TPC-H生成查询。
    说明 为了测试结果可重复,您可以忽略下面的生成查询的步骤,使用附件的22个查询进行测试。
    1. qgendists.dss复制到queries目录下。
      cp qgen queries
      cp dists.dss queries
    2. 使用以下脚本生成查询。
      #!/usr/bin/bash
      for i in {1..22}
      do  
        ./qgen -d $i -s 100 > db"$i".sql
      done

测试方法

  1. 检查并行查询是否开启。
    1. 登录PolarDB控制台
    2. 在控制台左上角,选择集群所在地域。
    3. 单击目标集群ID。
    4. 在左侧导航栏中,选择配置与管理 > 参数配置
    5. 在搜索栏输入loose_max_parallel_degree后,单击放大镜图标进行搜索。
      修改并行查询参数
    6. 设置当前值16
      说明 本例用于对比开启和关闭并行查询的效果,分别设置为160进行对比测试。
    7. 参数设置完成后,单击页面左上方提交修改
    8. 在弹出的保存改动对话框中,单击确定
  2. 在ECS上连接PolarDB数据库,具体操作请参见连接数据库集群
  3. 创建数据库。
    create database tpch100g
  4. 创建表。
    source ./dss.ddl
    说明 dss.ddl在TPC-H中dbgen目录下。
  5. 加载数据。
    1. 创建load.ddl,脚本内容如下:
      load data local INFILE 'customer.tbl' INTO TABLE customer FIELDS TERMINATED BY '|';
      load data local INFILE 'region.tbl' INTO TABLE region FIELDS TERMINATED BY '|';
      load data local INFILE 'nation.tbl' INTO TABLE nation FIELDS TERMINATED BY '|';
      load data local INFILE 'supplier.tbl' INTO TABLE supplier FIELDS TERMINATED BY '|';
      load data local INFILE 'part.tbl' INTO TABLE part FIELDS TERMINATED BY '|';
      load data local INFILE 'partsupp.tbl' INTO TABLE partsupp FIELDS TERMINATED BY '|';
      load data local INFILE 'orders.tbl' INTO TABLE orders FIELDS TERMINATED BY '|';
      load data local INFILE 'lineitem.tbl' INTO TABLE lineitem FIELDS TERMINATED BY '|';
    2. 加载数据。
      source ./load.ddl
  6. 创建主外键。
    source ./dss.ri

    以创建的数据库tpch100g为例,将TPC-H的dss.ri文件中的内容替换成如下内容。

    use TPCH100G;
    -- ALTER TABLE REGION DROP PRIMARY KEY;
    -- ALTER TABLE NATION DROP PRIMARY KEY;
    -- ALTER TABLE PART DROP PRIMARY KEY;
    -- ALTER TABLE SUPPLIER DROP PRIMARY KEY;
    -- ALTER TABLE PARTSUPP DROP PRIMARY KEY;
    -- ALTER TABLE ORDERS DROP PRIMARY KEY;
    -- ALTER TABLE LINEITEM DROP PRIMARY KEY;
    -- ALTER TABLE CUSTOMER DROP PRIMARY KEY;
    -- For table REGION
    ALTER TABLE REGION
    ADD PRIMARY KEY (R_REGIONKEY);
    -- For table NATION
    ALTER TABLE NATION
    ADD PRIMARY KEY (N_NATIONKEY);
    ALTER TABLE NATION
    ADD FOREIGN KEY NATION_FK1 (N_REGIONKEY) references REGION(R_REGIONKEY);
    COMMIT WORK;
    -- For table PART
    ALTER TABLE PART
    ADD PRIMARY KEY (P_PARTKEY);
    COMMIT WORK;
    -- For table SUPPLIER
    ALTER TABLE SUPPLIER
    ADD PRIMARY KEY (S_SUPPKEY);
    ALTER TABLE SUPPLIER
    ADD FOREIGN KEY SUPPLIER_FK1 (S_NATIONKEY) references NATION(N_NATIONKEY);
    COMMIT WORK;
    -- For table PARTSUPP
    ALTER TABLE PARTSUPP
    ADD PRIMARY KEY (PS_PARTKEY,PS_SUPPKEY);
    COMMIT WORK;
    -- For table CUSTOMER
    ALTER TABLE CUSTOMER
    ADD PRIMARY KEY (C_CUSTKEY);
    ALTER TABLE CUSTOMER
    ADD FOREIGN KEY CUSTOMER_FK1 (C_NATIONKEY) references NATION(N_NATIONKEY);
    COMMIT WORK;
    -- For table LINEITEM
    ALTER TABLE LINEITEM
    ADD PRIMARY KEY (L_ORDERKEY,L_LINENUMBER);
    COMMIT WORK;
    -- For table ORDERS
    ALTER TABLE ORDERS
    ADD PRIMARY KEY (O_ORDERKEY);
    COMMIT WORK;
    -- For table PARTSUPP
    ALTER TABLE PARTSUPP
    ADD FOREIGN KEY PARTSUPP_FK1 (PS_SUPPKEY) references SUPPLIER(S_SUPPKEY);
    COMMIT WORK;
    ALTER TABLE PARTSUPP
    ADD FOREIGN KEY PARTSUPP_FK2 (PS_PARTKEY) references PART(P_PARTKEY);
    COMMIT WORK;
    -- For table ORDERS
    ALTER TABLE ORDERS
    ADD FOREIGN KEY ORDERS_FK1 (O_CUSTKEY) references CUSTOMER(C_CUSTKEY);
    COMMIT WORK;
    -- For table LINEITEM
    ALTER TABLE LINEITEM
    ADD FOREIGN KEY LINEITEM_FK1 (L_ORDERKEY)  references ORDERS(O_ORDERKEY);
    COMMIT WORK;
    ALTER TABLE LINEITEM
    ADD FOREIGN KEY LINEITEM_FK2 (L_PARTKEY,L_SUPPKEY) references 
            PARTSUPP(PS_PARTKEY,PS_SUPPKEY);
    COMMIT WORK;
  7. 创建索引。
    #!/usr/bin/bash
    host=$1
    port=$2
    user=$3
    password=$4
    db=$5
    sqls=("create index i_s_nationkey on supplier (s_nationkey);"
    "create index i_ps_partkey on partsupp (ps_partkey);"
    "create index i_ps_suppkey on partsupp (ps_suppkey);"
    "create index i_c_nationkey on customer (c_nationkey);"
    "create index i_o_custkey on orders (o_custkey);"
    "create index i_o_orderdate on orders (o_orderdate);"
    "create index i_l_orderkey on lineitem (l_orderkey);"
    "create index i_l_partkey on lineitem (l_partkey);"
    "create index i_l_suppkey on lineitem (l_suppkey);"
    "create index i_l_partkey_suppkey on lineitem (l_partkey, l_suppkey);"
    "create index i_l_shipdate on lineitem (l_shipdate);"
    "create index i_l_commitdate on lineitem (l_commitdate);"
    "create index i_l_receiptdate on lineitem (l_receiptdate);"
    "create index i_n_regionkey on nation (n_regionkey);"
    "analyze table supplier"
    "analyze table part"
    "analyze table partsupp"
    "analyze table customer"
    "analyze table orders"
    "analyze table lineitem"
    "analyze table nation"
    "analyze table region")
    for sql in "${sqls[@]}"
    do
        mysql -h$host -P$port -u$user -p$password -D$db  -e "$sql"
    done
    说明 为了更有效地衡量并行查询带来的性能提升,您可以通过如下查询将使用到的索引数据预载到内存池中。
    #!/bin/bash
    host=$1
    port=$2
    user=$3
    password=$4
    dbname=$5
    MYSQL="mysql -h$host -P$port -u$user -p$password -D$dbname"
    if [ -z ${dbname} ]; then
        echo "dbname not defined."
        exit 1
    fi
    table_indexes=(
            "supplier PRIMARY"
            "supplier i_s_nationkey"
            "part PRIMARY"
            "partsupp PRIMARY"
            "partsupp i_ps_partkey"
            "partsupp i_ps_suppkey"
            "customer PRIMARY"
            "customer i_c_nationkey"
            "orders PRIMARY"
            "orders i_o_custkey"
            "orders i_o_orderdate"
            "lineitem PRIMARY"
            "lineitem i_l_orderkey"
            "lineitem i_l_partkey"
            "lineitem i_l_suppkey"
            "lineitem i_l_partkey_suppkey"
            "lineitem i_l_shipdate"
            "lineitem i_l_commitdate"
            "lineitem i_l_receiptdate"
            "nation i_n_regionkey"
            "nation PRIMARY"
            "region PRIMARY"
    )
    for table_index in "${table_indexes[@]}"
    do
        ti=($table_index)
        table=${ti[0]}
        index=${ti[1]}
        SQL="select count(*) from ${table} force index(${index})"
        echo "$MYSQL -e '$SQL'"
        $MYSQL -e "$SQL"
    done
  8. 运行查询。
    #!/usr/bin/env bash
    host=$1
    port=$2
    user=$3
    password=$4
    database=$5
    resfile=$6
    echo "start test run at"`date "+%Y-%m-%d %H:%M:%S"`|tee -a ${resfile}.out
    for (( i=1; i<=22;i=i+1 ))
    do
    queryfile="Q"${i}".sql"
    start_time=`date "+%s.%N"`
    echo "run query ${i}"|tee -a ${resfile}.out
    mysql -h ${host}  -P${port} -u${user} -p${password} $database -e" source $queryfile;" |tee -a ${resfile}.out
    end_time=`date "+%s.%N"`
    start_s=${start_time%.*}
    start_nanos=${start_time#*.}
    end_s=${end_time%.*}
    end_nanos=${end_time#*.}
    if [ "$end_nanos" -lt "$start_nanos" ];then
            end_s=$(( 10#$end_s -1 ))
            end_nanos=$(( 10#$end_nanos + 10 ** 9))
    fi
    time=$(( 10#$end_s - 10#$start_s )).`printf "%03d\n" $(( (10#$end_nanos - 10#$start_nanos)/10**6 ))`
    echo ${queryfile} "the "${j}" run cost "${time}" second start at"`date -d @$start_time "+%Y-%m-%d %H:%M:%S"`" stop at"`date -d @$end_time "+%Y-%m-%d %H:%M:%S"` >> ${resfile}.time
    done

执行结果

将并行查询参数分别设置为16与0的对比结果如下图所示。

并行查询对比并行查询提升多少
说明 Q1为第一个查询,Q2为第二个查询,以此类推。

测试结果具体信息如下表所示:

查询 耗时(秒)

并行度=16

耗时(秒)

并行度=0

提高倍数

(并行度=0/并行度=16)

Q1 103.068 1621.451 15.73
Q2 15.199 15.191 1.00
Q3 22.745 285.272 12.54
Q4 43.216 67.401 1.56
Q5 21.157 267.635 12.65
Q6 18.166 268.486 14.78
Q7 13.999 183.094 13.08
Q8 4.833 64.858 13.42
Q9 35.451 479.18 13.52
Q10 51.343 160.923 3.13
Q11 10.985 34.691 3.16
Q12 19.688 300.606 15.27
Q13 89.748 831.702 9.27
Q14 6.531 75.95 11.63
Q15 26.925 141.13 5.24
Q16 44.539 45.42 1.02
Q17 68.775 68.462 1.00
Q18 190.715 292.518 1.53
Q19 1.568 21.87 13.95
Q20 150.667 149.914 1.00
Q21 295.39 295.099 1.00
Q22 8.231 21.287 2.59