並行查詢測試方法 - PolarDB

本文檔基於TPC-H基準，測試PolarDB MySQL版8.0版本叢集版的OLAP負載效能，您可以按照本文介紹自行測試對比，以快速瞭解資料庫系統的效能。

注意事項

本文的TPC-H的實現基於TPC-H的基準測試，並不能與發行的TPC-H基準測試結果相比較，本文中的測試並不符合TPC-H基準測試的所有要求。

測試環境

用於測試的ECS執行個體和PolarDB叢集均位於同一地區、同一可用性區域。
網路類型均為VPC網路。
說明
ECS執行個體和PolarDB叢集需保證在同一個VPC中。
測試使用的PolarDB叢集如下：
- 節點規格為polar.mysql.x8.4xlarge（32核256 GB）。
- 版本為MySQL 8.0.1和MySQL 8.0.2。
- 產品系列為叢集獨享版。
- 節點數量：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

重要

本文檔中的命令只能由root許可權的使用者來執行。

本文使用的TPC-H版本為TPC-H_Tools_v2.18.0，您需要先進行註冊，註冊完成後才能下載。

在ECS執行個體上安裝TPC-H的操作步驟如下：

註冊並下載TPC-H，並將下載的TPCH-H壓縮包上傳至ECS執行個體。上傳檔案的操作步驟請參見上傳本地檔案到ECS執行個體。
執行以下命令，解壓縮上傳的TPC-H壓縮包。此處以壓縮包dbgen.zip為例，執行命令時，請根據實際情況修改壓縮包名稱。
```
unzip dbgen.zip
```
進入解壓後的目錄，此處以dbgen目錄為例。執行命令時，請根據實際情況修改目錄。
```
cd dbgen
```
執行以下命令，複製makefile檔案。
```
cp makefile.suite makefile
```
執行以下命令，安裝GCC。
- 如果您安裝的是centos系統，請執行以下命令安裝GCC。
```
sudo yum install gcc
```
- 如果您安裝的是ubuntu，請執行以下命令安裝GCC。
```
sudo apt install gcc
```

修改makefile檔案中的CC、DATABASE、MACHINE、WORKLOAD等參數定義。

開啟makefile檔案。
```
vim makefile
```

按i鍵，修改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

按Esc鍵，輸入:wq儲存並退出。

修改tpcd.h檔案，並添加新的宏定義。

開啟tpcd.h檔案。
```
vim tpcd.h
```

按i鍵，添加如下宏定義。

#ifdef MYSQL
#define GEN_QUERY_PLAN "EXPLAIN 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

按Esc鍵，然後輸入:wq儲存並退出。

對檔案進行編譯。
```
make
```
編譯完成後該目錄下會產生兩個可執行檔：
- dbgen：資料產生工具。在使用InfiniDB官方測試指令碼進行測試時，需要用該工具產生tpch相關表資料。
- qgen：SQL產生工具。產生初始化測試查詢，由於不同的seed產生的查詢不同，為了結果的可重複性，請使用附件提供的22個查詢語句。
使用TPC-H產生測試資料。
```
./dbgen -s 100
```
參數-s的作用是指定產生測試資料的倉庫數。
使用TPC-H產生查詢。
說明
為了測試結果可重複，您可以忽略下面產生查詢的步驟，使用附件的22個查詢進行測試。
1. 將qgen與dists.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
```

測試步驟

在ECS上串連PolarDB資料庫，具體操作請參見串連資料庫叢集。
建立資料庫。
```
create database tpch100g
```
建立表。
```
source ./dss.ddl
```
說明
dss.ddl在TPC-H中dbgen目錄下。

載入資料。

在ECS上建立load.ddl檔案。
```
Touch load.ddl
```
開啟load.ddl檔案。
```
vim 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 '|';

串連PolarDB資料庫載入load.ddl檔案資料。
```
source ./load.ddl
```

建立主外鍵。

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

測試結果

並行查詢效能測試請參見並行查詢效能。