全部產品
Search

搜尋本產品

    PolarDB：並行查詢測試方法

    文件中心

    PolarDB：並行查詢測試方法

    更新時間：Feb 13, 2025

    本文將基於TPC-H基準測試，對PolarDB MySQL 8.0叢集版進行OLAP負載效能測試。您可以按照本文所述的方法自行進行測試對比，從而快速瞭解該叢集的效能。

    測試環境

    • ECS執行個體和PolarDB MySQL版叢集位於同一地區，同一Virtual Private Cloud內。

    • PolarDB MySQL版叢集配置如下：

      • 叢集個數：1個

      • 資料庫引擎：MySQL 8.0.1或MySQL 8.0.2

      • 產品版本：企業版

      • 系列：叢集版

      • 子系列：獨享規格

      • 節點規格：polar.mysql.x8.4xlarge（32核256 GB）

      • 節點數量：2個（1個讀寫節點、1個唯讀節點）

      說明

      並行測試使用的串連地址為主地址。如何查看PolarDB MySQL版叢集的主地址，請參見管理串連地址。

    • ECS執行個體配置如下：

      • 執行個體個數：1台

      • 執行個體規格：ecs.c5.4xlarge（16 vCPU 32 GiB）

      • 鏡像：CentOS 7.0 64位

      • 系統硬碟：ESSD雲端硬碟1000 GB

    測試載入器

    TPC-H是業界常用的一套基準，由TPC委員會制定發布，用於評測資料庫的分析型查詢能力。TPC-H查詢包含8張資料表、22條複雜的SQL查詢，大多數查詢包含若干表Join、子查詢和Group By彙總等。

    說明

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

    安裝TPC-H

    重要

    • 請使用root使用者登入ECS執行個體並執行以下命令。

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

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

    1. 註冊並下載TPC-H，並將下載的TPC-H壓縮包上傳至ECS執行個體。上傳操作步驟，請參見上傳檔案。

    2. 解壓縮上傳的TPC-H壓縮包。此處以TPC-H-Tool.zip為例，執行命令時，請根據實際情況修改壓縮包名稱。

      unzip TPC-H-Tool.zip

    3. 解壓完成後，請進入檔案夾並找到dbgen檔案夾，隨後進入該dbgen檔案夾。此處以TPC-H-Tool/dbgen為例，執行命令時，請根據實際情況修改目錄。

      cd TPC-H-Tool/dbgen

    4. 複製makefile檔案。

      cp makefile.suite makefile

    5. 安裝GCC。

      sudo yum install gcc
      說明

      此處以CentOS系統為例。如果您安裝的是其他動作系統，請根據實際情況調整相應的安裝命令。例如，在Ubuntu系統中，安裝命令為sudo apt install gcc。

    6. 修改makefile檔案中的CC、DATABASE、MACHINE和WORKLOAD參數的值。

      1. 開啟makefile檔案。

        vim makefile

      2. 按i鍵，修改參數值。

        ################
## CHANGE NAME OF ANSI COMPILER HERE
################
CC      = gcc
# Current values for DATABASE are: INFORMIX, DB2, TDAT (Teradata)
#                                  SQLSERVER, SYBASE, ORACLE, VECTORWISE
# 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. 按Esc鍵，輸入:wq儲存並退出。

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

      1. 開啟tpcd.h檔案。

        vim tpcd.h

      2. 按i鍵，在database portability defines下添加如下宏定義。

        #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

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

    8. 對檔案進行編譯。

      make

      編譯完成後該目錄下會產生兩個可執行檔：

      • dbgen：資料產生工具。在使用InfiniDB官方測試指令碼進行測試時，需要用該工具產生TPC-H相關表資料。

      • qgen：SQL產生工具。產生初始化測試查詢，由於不同的seed產生的查詢不同，為了結果的可重複性，請使用附件提供的22個查詢語句。

    9. 使用TPC-H產生測試資料。

      ./dbgen -s 100
      說明

      -s 參數用於指定產生資料的比例因素（Scale Factor），表示基準測試資料集的大小。-s 100 表示產生一個約100 GB的測試資料集（具體大小可能因表結構和資料分布略有差異）。

    10. （可選）使用TPC-H產生查詢。

      說明

      為了測試結果可重複，您可以忽略下面產生查詢的步驟，使用附件的22個查詢進行測試。

      1. 將qgen與dists.dss複製到queries目錄下。

        cp qgen queries
cp dists.dss queries

      2. 產生查詢。

        1. 建立generate_queries.sh指令碼。

          vim generate_queries.sh

        2. 按i鍵，添加如下指令碼內容。

          #!/usr/bin/bash

# 進入查詢目錄
cd queries

# 產生 22 條查詢
for i in {1..22}
do
  ./qgen -d $i -s 100 > db"$i".sql
done

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

        4. 設定指令碼執行許可權。

          chmod +x generate_queries.sh

        5. 執行generate_queries.sh指令碼。

          ./generate_queries.sh

    測試步驟

    在ECS執行個體上串連PolarDB MySQL版叢集，進行初始化資料及測試，操作步驟如下：

    1. 建立載入資料的執行指令碼。

      1. 在TPC-H的dbgen目錄下，建立load.ddl檔案。

        vim load.ddl

      2. 按i鍵，添加如下指令碼內容。

        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 '|';

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

    2. 修改dss.ri檔案，用於後續建立表的主外鍵。

      1. 複製dss.ri檔案，並清空dss.ri檔案。

        cp dss.ri dss_bk.ri
        > dss.ri

      2. 開啟dss.ri檔案。

        vim dss.ri

      3. 按i鍵，在檔案中添加如下內容。

        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;

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

    3. 安裝MySQL用戶端。

      sudo yum install mysql
      說明

      此處以CentOS系統為例。如果您安裝的是其他動作系統，請根據實際情況調整相應的安裝命令。

    4. 使用高許可權帳號及叢集主地址串連PolarDB MySQL版叢集。更多資訊，請參見建立高許可權帳號和串連資料庫。

      mysql -h<PolarDB叢集主地址> -P<連接埠> -u<資料庫使用者名稱> -p<資料庫使用者密碼>
      說明

      如果出現類似Access denied for user 'xxx'@'xxx' (using password: YES)的錯誤提示，說明資料庫使用者名稱或密碼不正確，請檢查您輸入的資訊是否正確。

    5. 建立tpch100g資料庫，並切換至當前資料庫。

      CREATE DATABASE tpch100g;

use tpch100g;

    6. 建立表。

      source ./dss.ddl
      說明

      dss.ddl位於TPC-H的dbgen目錄中。

    7. 載入資料。

      source ./load.ddl
      說明

      load.ddl位於TPC-H的dbgen目錄中，並在第一步進行了建立。

    8. 建立主外鍵。

      source ./dss.ri
      說明

      dss.ri位於TPC-H的dbgen目錄中，並在第二步進行了修改。

    9. （可選）建立索引。

      您需要先執行exit;命令退出MySQL用戶端。隨後在ECS執行個體中，建立指令碼並執行。指令碼內容如下：

      #!/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
      說明

      • 建立指令碼時，請確保指令碼具有可執行許可權。此處以create_indexes.sh指令碼為例，您可以使用命令chmod +x create_indexes.sh來添加指令碼的可執行許可權。

      • 執行指令碼時，您需要傳遞相應的參數。此處以create_indexes.sh指令碼為例，執行命令為./create_indexes.sh <host> <port> <user> <password> <database>。參數說明如下：

        • <host>：叢集串連地址

        • <port>：叢集連接埠

        • <user>：資料庫使用者名稱

        • <password>：資料庫使用者密碼

        • <database>：資料庫名稱

      • 在執行指令碼時，如果出現警告資訊[Warning] Using a password on the command line interface can be insecure，是由於指令碼內部使用了mysql -h<資料庫連接地址> -P<資料庫連接埠> -u<資料庫帳號> -p<資料庫密碼> -e<SQL>命令。因此，系統會提示在命令列輸入密碼可能會導致系統上的其他使用者通過執行ps等命令查看到密碼。這一警告不會影響您的操作，操作完成後，您可以前往PolarDB控制台修改資料庫帳號密碼。

      • 為了更有效地衡量並行查詢帶來的效能提升，您可以通過如下指令碼將使用到的索引資料預先載入到記憶體池中。

        #!/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

    10. 執行查詢

      您需要先執行exit;命令退出MySQL用戶端。隨後在ECS執行個體中，建立指令碼並執行。指令碼內容如下：

      #!/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="./queries/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
      說明

      • 執行指令碼前，確認事項如下：

        • 請確保指令碼具有可執行許可權。此處以run_queries.sh指令碼為例，您可以使用命令chmod +x run_queries.sh來添加指令碼的可執行許可權。

        • 請確認您當前所在的目錄，該指令碼內容是以在TPC-H的dbgen目錄下執行為例進行編寫的。請根據實際情況對queryfile="./queries/Q"${i}".sql"的內容進行相應修改。否則，您將遇到如下錯誤提示：ERROR at line 1: Failed to open file 'QXX.sql'。

        • 請確認您已將附件的22個查詢上傳並解壓至dbgen/queries目錄下。若需使用其他查詢語句，請根據實際情況對queryfile="./queries/Q"${i}".sql"的內容進行相應修改。否則，您將遇到如下錯誤提示：ERROR at line 1: Failed to open file 'QXX.sql'。

      • 執行指令碼時，您需要傳遞相應的參數。此處以run_queries.sh指令碼為例，執行命令為./run_queries.sh <host> <port> <user> <password> <database> <resfile>。參數說明如下：

        • <host>：叢集串連地址

        • <port>：叢集連接埠

        • <user>：叢集使用者名稱

        • <password>：叢集使用者密碼

        • <database>：資料庫名稱

        • <resfile>：執行結果輸出檔案名稱

      • 在執行指令碼時，如果出現警告資訊[Warning] Using a password on the command line interface can be insecure，是由於指令碼內部使用了mysql -h<資料庫連接地址> -P<資料庫連接埠> -u<資料庫帳號> -p<資料庫密碼> -e<SQL>命令。因此，系統會提示在命令列輸入密碼可能會導致系統上的其他使用者通過執行ps等命令查看到密碼。這一警告不會影響您的操作，操作完成後，您可以前往PolarDB控制台修改資料庫帳號密碼。

    測試結果

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