本文档介绍以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网络。
    说明 ECS实例和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 80.18 1290.6 16
Q2 1.44 11.8 8
Q3 25.05 244.92 10
Q4 6.91 59.61 9
Q5 24.44 231.18 9
Q6 14.51 217.42 15
Q7 51.97 410.59 8
Q8 5.61 57.52 10
Q9 37.84 415.11 11
Q10 38.72 139.73 4
Q11 11.75 30.67 3
Q12 15.89 245.19 15
Q13 104.12 718.2 7
Q14 8.31 66.66 8
Q15 32.5 123.79 4
Q16 26.9 37.54 1
Q17 16.2 54.34 3
Q18 66.77 240.28 4
Q19 1.58 18.62 12
Q20 45.88 46.91 1
Q21 53.99 253.27 5
Q22 2.07 17.08 8