本文档介绍以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核32 GB规格的集群为例,并行查询示意图如下所示。

下文将介绍并行查询参数分别设置为16与0时,PolarDB集群负载性能的测试方式与测试结果。
测试环境
- 测试的ECS和PolarDB均在同一地域、同一可用区。本例中为华东1(杭州)可用区I。
- 网络类型均为VPC网络。
说明 ECS实例和PolarDB集群需保证在同一个VPC中。
- 测试用PolarDB集群如下:
- 节点规格为polar.mysql.x8.4xlarge(32核256 GB)。
- 数据库版本为MySQL 8.0。
- 节点数量为2个(一个主节点,一个只读节点)。
- 使用的连接串为主地址,如何查看PolarDB主地址请参见查看或申请连接地址。
- 测试用ECS实例信息如下:
- 实例规格为ecs.c5.4xlarge。
- 实例挂载1000 GB高效云盘。
- 实例所使用的镜像为CentOS 7.0 64位。
测试工具
TPC-H是业界常用的一套基准,由TPC委员会制定发布,用于评测数据库的分析型查询能力。TPC-H查询包含8张数据表、22条复杂的SQL查询,大多数查询包含若干表Join、子查询和Group
by聚合等。
安装TPC-H
- 在ECS上安装TPC-H。
- 打开dbgen目录。
- 复制
makefile
文件。cp makefile.suite makefile
- 修改
makefile
文件中的CC、DATABASE、MACHINE、WORKLOAD等参数定义。
- 打开
makefile
文件。
- 修改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
- 按ECS键,然后输入
:wq
退出并保存。
- 修改
tpcd.h
文件,并添加新的宏定义。
- 打开
tpcd.h
文件。
- 添加如下宏定义。
#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
- 按ECS键,然后输入
:wq
退出并保存。
- 对文件进行编译。
make
编译完成后该目录下会生成两个可执行文件:
dbgen
:数据生成工具。在使用InfiniDB官方测试脚本进行测试时,需要用该工具生成tpch相关表数据。
qgen
:SQL生成工具。生成初始化测试查询,由于不同的seed生成的查询不同,为了结果的可重复性,请使用附件提供的22个查询。
- 使用TPC-H生成测试数据。
./dbgen -s 100
dbgen参数-s
的作用是指定生成测试数据的仓库数。
- 使用TPC-H生成查询。
说明 为了测试结果可重复,您可以忽略下面的生成查询的步骤,使用
附件的22个查询进行测试。
- 将
qgen
与dists.dss
复制到queries目录下。cp qgen queries
cp dists.dss queries
- 使用以下脚本生成查询。
#!/usr/bin/bash
for i in {1..22}
do
./qgen -d $i -s 100 > db"$i".sql
done
测试方法
- 配置loose_max_parallel_degree参数来开启或关闭并行查询。
说明 本例用于对比开启和关闭并行查询的效果,分别设置
loose_max_parallel_degree参数为
16和
0进行对比测试。关于如何配置参数,详情操作步骤请参见
设置集群参数。
- 在ECS上连接PolarDB数据库,具体操作请参见连接数据库集群。
- 创建数据库。
- 创建表。
source ./dss.ddl
说明 dss.ddl
在TPC-H中dbgen目录下。
- 加载数据。
- 创建
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 '|';
- 加载数据。
- 创建主外键。
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;
- 创建索引。
#!/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
- 运行查询。
#!/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 |
在文档使用中是否遇到以下问题
更多建议
匿名提交