このトピックでは、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 のインストール
- ECS インスタンスに TPC-H をインストールします。
注
- dbgen ディレクトリに移動します。
makefile ファイルをコピーします。
cp makefile.suite makefile
- CC、DATABASE、MACHINE および WORKLOAD などの
makefile ファイルのパラメーターを変更します。
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
- Esc キーを押下し、
: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
- Esc キーを押下し、
:wq を入力してファイルを保存し、終了します。
- makefile ファイルをコンパイルします。
make
ファイルがコンパイルされると、以下のディレクトリに 2 つの実行可能ファイルが生成されます。
dbgen：データを生成するツール InfiniDB テストスクリプトを使用する場合、このツールを使用して TPC-H データを生成する必要があります。
qgen：SQL クエリを生成するツール シードが異なれば、生成されるクエリも異なります。 再現性を確保するために、添付ファイルで提供されている 22 個のクエリを使用します。
- TPC-H を使用してテストデータを生成します。
./dbgen -s 100
-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
テスト手順
- 並列クエリが ApsaraDB PolarDB MySQL クラスターで有効になっていることを確認します。
- ApsaraDB for PolarDB コンソールにログインします。
- コンソールの左上で、クラスターがデプロイされているリージョンを選択します。
- クラスターの ID をクリックします。
- 左側のナビゲーションウィンドウで、をクリックします。
- loose_max_parallel_degree を検索ボックスに入力し、検索アイコンをクリックします。
- [現在値] を 16 に設定します。
注 並列クエリによるクラスターのパフォーマンス向上を明確に理解するために、比較テストを実行します。このパラメーターは、16 に設定し、その後 0 に設定します。
- パラメーターの変更後ろ、ページ左上の [変更を適用] をクリックします。
- 表示される [変更の保存] ダイアログボックスで、[OK] をクリックします。
- ECS インスタンスの ApsaraDB PolarDB MySQL データベースへ接続します。 詳細については、「データベースクラスターへの接続」をご参照ください。
- データベースを作成します。
- テーブルを作成します。
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
タスクの結果
下図では、並列クエリを有効にした場合、クエリのパフォーマンスが向上していることが分かります。
注 上図の文字 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 |
