このトピックでは、TPC-H を使用して ApsaraDB PolarDB MySQL クラスター 8.0 のオンライン分析処理 (OLAP) パフォーマンスをテストする方法について説明しています。

並列クエリについて

ApsaraDB PolarDB MySQL 8.0 は、並列クエリフレームワークを起動します。 デフォルトでは、並列クエリは無効です。 並列クエリを有効にした後に、照会されたデータ量が指定したしきい値に達すると、並列クエリフレームワークは自動的に有効になります。 これにより、クエリの実行に必要な時間を短縮することができます。

loose_max_parallel_degree パラメーターを設定して、並列クエリを有効にします。 詳細については、「 クラスターパラメータの設定」をご参照ください。

以下のルールに従って、loose_max_parallel_degree パラメーターを設定します。

  • 最小値は 0 です。これは並列クエリを無効にすることを指定します。
  • 最大値は 1024 です。
  • このパラメーター値を 16 に設定することを推奨します。

ApsaraDB PolarDB MySQL 8.0 では、データをストレージレイヤーにあるさまざまなスレッドへ分散します。 複数のスレッドが並列計算を実行し、リーダースレッドへ結果を返します。 そして、リーダースレッドはその結果を結合し、最終結果をユーザーへ返します。 これにより、クエリの速度と精度が向上します。

並列クエリは、マルチコア CPU の並列処理能力に基づいて実行されています。 下図に、8 コア CPU と 32 GB メモリを備えたクラスターでの並列クエリの動作を示します。

並列クエリ

以下のセクションでは、loose_max_parallel_degree パラメーターがそれぞれ 16 と 0 に設定されているさまざまな状況下で、ApsaraDB PolarDB MySQL クラスターのパフォーマンスをテストする方法について説明します。 テスト結果も示しています。

テスト環境

  • Elastic Compute Service (ECS) インスタンスと ApsaraDB PolarDB MySQL クラスターを使用してテストを行います。これらは、同じリージョンとゾーンにデプロイされている必要があります。 このテストでは、これらは中国 (杭州) リージョンのゾーン I にデプロイされています。
  • ECS インスタンスと ApsaraDB PolarDB MySQL クラスターは、両方ともVPC ネットワークタイプです。
    ECS インスタンスと ApsaraDB PolarDB MySQL クラスターが同じ VPC ネットワークに接続されていることを確認します。
  • テスト用の ApsaraDB PolarDB MySQL クラスターは以下のとおりです。
    • ノードの仕様は、polar.mysql.x8.4xlarge (32 コア 256 GB) です。
    • データベースエンジンは MySQL 8.0 です。
    • クラスターには、プライマリノードと読み取り専用ノードの 2 つのノードがあります。
    • プライマリエンドポイントを使用してクラスターへ接続します。 プライマリエンドポイントの照会方法については、「エンドポイントの表示」をご参照ください。
  • テスト用の ECS インスタンスは以下のとおりです。
    • インスタンスタイプは ecs.c5.4xlarge です。
    • 1,000 GiB のウルトラディスクがインスタンスへ接続されます。
    • インスタンスで使用されるイメージは、64 ビット CentOS 7.0 です。

テストツール

TPC-H は標準のベンチマークです。 データベースのクエリ能力を評価するために、トランザクション処理パフォーマンス (TPC) 評議会によって開発、リリースされました。 TPC-H ベンチマークには、8 つのテーブルが含まれており、22 の複雑な SQL クエリが定義されています。 ほとんどのクエリには、いくつかのテーブル、サブクエリ、Group by 句の結合操作が含まれています。

TPC-H のインストール

  1. ECS インスタンスに TPC-H をインストールします。
    • 本ページで使用している TPC-H のバージョンは v2.18.0 です。 TPC-H v2.18.0 をダウンロードします
    • TPC-H をダウンロードする前に、実名登録が済んでいることを確認します。
    TPC-H をダウンロードします。
  2. dbgen ディレクトリに移動します。
    cd dbgen
  3. makefile ファイルをコピーします。
    cp makefile.suite makefile
  4. CC、DATABASE、MACHINE および WORKLOAD などの makefile ファイルのパラメーターを変更します。
    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. Esc キーを押下し、: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. Esc キーを押下し、:wq を入力してファイルを保存し、終了します。
  6. makefile ファイルをコンパイルします。
    make

    ファイルがコンパイルされると、以下のディレクトリに 2 つの実行可能ファイルが生成されます。

    • dbgen:データを生成するツール InfiniDB テストスクリプトを使用する場合、このツールを使用して TPC-H データを生成する必要があります。
    • qgen:SQL クエリを生成するツール シードが異なれば、生成されるクエリも異なります。 再現性を確保するために、添付ファイルで提供されている 22 個のクエリを使用します。
  7. TPC-H を使用してテストデータを生成します。
    ./dbgen -s 100

    -s パラメーターを使用して、生成されるデータ量を指定します。

  8. TPC-H を使用してクエリを生成します。
    テスト結果の再現性を確保するため、この手順をスキップして、 添付の 22 個のクエリを使用することもできます。
    1. qgendists.dssqueries ディレクトリにコピーします。
      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. 並列クエリが ApsaraDB PolarDB MySQL クラスターで有効になっていることを確認します。
    1. ApsaraDB for PolarDB コンソールにログインします。
    2. コンソールの左上で、クラスターがデプロイされているリージョンを選択します。
    3. クラスターの ID をクリックします。
    4. 左側のナビゲーションウィンドウで、[設定と管理] > [パラメーター]をクリックします。
    5. loose_max_parallel_degree を検索ボックスに入力し、検索アイコンをクリックします。
      loose_max_parallel_degree パラメーターを変更します。
    6. [現在値]16 に設定します。
      並列クエリによるクラスターのパフォーマンス向上を明確に理解するために、比較テストを実行します。このパラメーターは、 16 に設定し、その後 0 に設定します。
    7. パラメーターの変更後ろ、ページ左上の [変更を適用] をクリックします。
    8. 表示される [変更の保存] ダイアログボックスで、[OK] をクリックします。
  2. ECS インスタンスの ApsaraDB PolarDB MySQL データベースへ接続します。 詳細については、「データベースクラスターへの接続」をご参照ください。
  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

タスクの結果

下図では、並列クエリを有効にした場合、クエリのパフォーマンスが向上していることが分かります。

比較 並列クエリによる改善
上図の文字 Q はクエリを示しています。 たとえば、Q1 は最初のクエリを表しています。

結果は以下のとおりです。

クエリ 消費時間 (秒)

並列度 (DOP) = 16

消費時間 (秒)

並列度 (DOP) = 0

パフォーマンスの向上

(DOP 0/DOP 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