Alibaba Cloud PolarDB for MySQL 8.0パラレルクエリを検証してみました

2023年3月31日掲載

皆さんこんにちは。クラウドエンジニアのKangです。

パラレルクエリというのはSQLのクエリを並列で複数のスレッドにより同時に実行することで、データベースの読み取り・書き込みパフォーマンスを向上させることを目的としています。データベースに対する、テーブルのスキャン、結合、アペンド、Selectなどの操作が大量に発生するようなシナリオの場合、パラレルクエリで実行すれば、SQL文の実行時間が大幅に短縮できるというメリットがあります。

Alibaba Cloud PolarDB for MySQL 8.0では、全てのノードはこのパラレルクエリ機能もサポートしているので、本記事では、パラレルクエリ機能を有効にして、SQLの実行時間がどのくらい短縮できるかを検証したいと思います。

この記事ではAlibabaCloud PolarDBのパラレルクエリパフォーマンスを検証します。
ある程度データベース構造とSQLクエリの知識がある事を前提に書いています。
本検証を元に、ある程度PolarDBの同時実行パフォーマンスを把握することが可能です。

1. はじめに

この記事では、AlibabaCloud PolarDB-MySQL8.0 クラスター環境を構築し、パラレルSQLクエリのパフォーマンスに対して検証しました。

まず、AlibabaCloud PolarDB for MySQLに接続するにはMysqlのクライアントが必要ですので、そのため、ECSインスタンスを1台作成し、MySQL Clientをインストールします。

次に、AlibabaCloud PolarDB for MySQLインスタンスを作成して、Clientからのアクセスが許可されるように、ホワイトリストにClientのIPアドレスを追加します。その後、データベースとユーザーアカウントを作成して、インターネットからアクセスできるようにパブリックエンドポイントを申請します。

最後に、検証用のデータをDBにinsertして、パラレル機能の有効・無効時、それぞれのSQL実行時間を比較し、パラレル機能の有効性を検証したいと思います。

2. ECSインスタンス環境を用意する

2.1 ECSインスタンスを作成します

①ECSを事前に作成します

検証用のECS仕様は下記の通りです。

VM：Specifications: 2 vCPU 8GB ecs.g6.large

OS：CentOS

②MySQLをインストールします

yum install mysql

3. PollarDB-Mysql8.0インスタンスを作成します

①PollarDB-MySQL8.0インスタンスを作成します

仕様は下記の通りです。

Specifications: 8 cores 32GB polar.mysql.x4.xlarge

PolarDBの購入ページで、以下の通りのスペックを選択します。

これで、PolarDBインスタンスの作成が完了です。

②ホワイトリストを設定します

ここでECSのIPをホワイトリストに追加し、ECSからPollarDBインスタンスへ接続できるように設定します。

③アカウントを作成します

アカウント：sbtest

④データベースを作成します

データベース：sbdb

⑤パブリックエンドポイントを設定します

以下の通り、プライマリノードとクラスターそれぞれのパブリックアドレスを作成します。

⑥PollarDBに接続します

ClusterEndpointへ接続します

mysql -h polardb-cluster.rwlb.japan.rds.aliyuncs.com -P 3306 -u sbtest -pTest1234 -D sbdb

⑦パラメータを設定します

パラレルクエリを実行する為、次のようにパラメータを変更します。

loose_parallel_degree_policy：TYPICAL（デフォルト：REPLICA_AUTO）

loose_max_parallel_degree：16（デフォルト:２）

connect_timeout:3600（デフォルト：10）

innodb_lock_wait_timeout: 31536000（デフォルト：50）

4.パラレルクエリ用データを準備します

４.1データを準備します

①テーブルを作成します

CREATE TABLE sbdb.polardb_test_table (
id INT PRIMARY KEY AUTO_INCREMENT,
unique_name VARCHAR(50),
START_DATE DATETIME, 
END_DATE DATETIME, 
field1 INT UNSIGNED, 
field2 INT UNSIGNED, 
field3 INT UNSIGNED, 
field4 INT UNSIGNED, 
field5 INT UNSIGNED, 
field6 INT UNSIGNED, 
field7 INT UNSIGNED, 
field8 INT UNSIGNED, 
field9 INT UNSIGNED, 
field10 INT UNSIGNED, 
field11 VARCHAR(100), 
field12 VARCHAR(100), 
field13 VARCHAR(100), 
field14 VARCHAR(100), 
field15 VARCHAR(100), 
field16 VARCHAR(100), 
field17 VARCHAR(100), 
field18 VARCHAR(100), 
field19 VARCHAR(100), 
field20 VARCHAR(100)
);

②インデックスを作成します

create index id_index on sbdb.polardb_test_table(unique_name);
create index unique_name_index on sbdb.polardb_test_table(unique_name);
create index START_DATE_index on sbdb.polardb_test_table(START_DATE);
create index END_DATE_index on sbdb.polardb_test_table(END_DATE);

③5000万の空のレコードを一括で挿入します。

INSERT INTO sbdb.polardb_test_table () VALUES ();
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;
INSERT INTO sbdb.polardb_test_table (id) SELECT 0 FROM sbdb.polardb_test_table;

④5000万以上のレコードを挿入しました。

⑤データを設定します。

5000万件を超えるレコード（67,108,864レコード）は全てどれも空なので、スクリプトを使用してランダムデータを追加します。

ここで、2つのSQLファイルを作成します。

update-polardb.sqlファイルは1行おきに全てのフィールドに乱数を入れながらレコードをを更新するスクリプトです。

do-update-polardb.shファイルはupdate-polardb.sqlを全てのレコード（67,108,864レコード）に実施する為のスクリプトです。

do-update-polardb.shファイルはupdate-polardb.sqlを全てのレコード(67,108,864レコード)に適用する為のスクリプトです。

# update-polardb.sql
UPDATE polardb_test_table SET
unique_name = CONCAT('unique_', id),
START_DATE = ADDTIME(CONCAT_WS(' ','2021-01-01' + INTERVAL RAND() * 365 DAY, '00:00:00'), SEC_TO_TIME(FLOOR(0 + (RAND() * 86401)))),
END_DATE = ADDTIME(CONCAT_WS(' ','2022-01-01' + INTERVAL RAND() * 365 DAY, '00:00:00'), SEC_TO_TIME(FLOOR(0 + (RAND() * 86401)))),
field1 = CEIL(RAND() * 1000000),
field2 = CEIL(RAND() * 1000000),
field3 = CEIL(RAND() * 1000000),
field4 = CEIL(RAND() * 1000000),
field5 = CEIL(RAND() * 1000000),
field6 = CEIL(RAND() * 1000000),
field7 = CEIL(RAND() * 1000000),
field8 = CEIL(RAND() * 1000000),
field9 = CEIL(RAND() * 1000000),
field10 = CEIL(RAND() * 1000000),
field11 = SUBSTRING(MD5(RAND()), 1, 1000),
field12 = SUBSTRING(MD5(RAND()), 1, 1000),
field13 = SUBSTRING(MD5(RAND()), 1, 1000),
field14 = SUBSTRING(MD5(RAND()), 1, 1000),
field15 = SUBSTRING(MD5(RAND()), 1, 1000),
field16 = SUBSTRING(MD5(RAND()), 1, 1000),
field17 = SUBSTRING(MD5(RAND()), 1, 1000),
field18 = SUBSTRING(MD5(RAND()), 1, 1000),
field19 = SUBSTRING(MD5(RAND()), 1, 1000),
field20 = SUBSTRING(MD5(RAND()), 1, 1000)
WHERE unique_name IS NULL
LIMIT 10000;

#do-update-polardb.sh

for i in `seq 6710`
do
  mysql -h polardb-cluster.rwlb.japan.rds.aliyuncs.com -P 3306 -D sbdb -u sbtest -pTest1234 < /root/pushdata/update-polardb.sql
  echo "Updated for the $i time, response result is $?"
done

mysql -h polardb-cluster.rwlb.japan.rds.aliyuncs.com -P 3306 -D sbdb -u sbtest -pTest1234 -e "SELECT COUNT(*) FROM polardb_test_table WHERE unique_name IS NULL;"

⑥ ファイルを権限設定します

chmod a+x *.sh

※nancy-update.shの内容は do-update-polardb.sh と同じです。

※注意として、一度に10万100万レコードの更新処理を実施したらサーバに大きな負荷がかかるため、update-polardb.sqlは1度の実行にあたり10,000レコードを実行するよう、LIMIT 10,000を付け加えています。

その為、do-update-polardb.shで6710回分ループしながらupdate-ploardb.sqlを実行させます。6710回は67,108,864レコード全てに対し、update-ploardb.sql

1回あたり10,000レコードをキリ良く実施するための回数です。残り8864レコードをは以下の⑦の箇所で別途実行する必要があります。

--------------

67,108,864 全レコード = update-polardb.sql １回あたり10,000レコード = 67,100,000 レコード + 8864レコード

--------------

⑦残りの8864データも更新します

⑧データを設定しました

データ件数：

select count(*) from polardb_test_table;

⑨データ内容を確認：

⑩すべてのデータが設定されていることを確認：

SELECT COUNT(*) FROM sbdb.polardb_test_table WHERE unique_name is NOT null;

5. パラレルクエリを検証します

５.1　クエリSQL文

次の表に記載されている6種類のSQLクエリを実行し、パラレルクエリの設定がOFFの場合とONの場合、それぞれ実行してみました。

データ量：67108864件

Query番号	SQL文	内容
Query1	SELECT count(*) FROM sbdb.polardb_test_table;	全レコード件数
Query2	SELECT Sum(field1), AVG(field2), Max(field3), Min(field4), Count(field11) FROM sbdb.polardb_test_table;	全レコードの集計
Query3	SELECT count(*) FROM sbdb.polardb_test_table WHERE START_DATE BETWEEN '2021-10-01' and '2022-03-30' and field4 > 30000 and field11 in ('bd');	WHERE句
Query4	SELECT unique_name, START_DATE, field2 FROM sbdb.polardb_test_table ORDER BY field2 DESC limit 100;	降順および上位
Query5	SELECT b.unique_name, b.START_DATE, b.field15 FROM (SELECT AVG(field2) as field2_avg FROM sbdb.polardb_test_table ) a, sbdb.polardb_test_table b WHERE a.field2_avg < b.field2 limit 100;	FROM句で相関サブクエリ
Query6	WITH test1 as(SELECT AVG(field1) as field1_avg FROM sbdb.polardb_test_table ) SELECT b.unique_name, b.field1, b.START_DATE FROM test1 a, sbdb.polardb_test_table b WHERE a.field1_avg < b.field1 LIMIT 100;	WITH句

5.2 パラレルクエリ「OFF」を検証

５.2.1 パラレルクエリOFFに設定します

①パラレルクエリOFFの状態に設定します

SET force_parallel_mode = OFF;

SET max_parallel_degree = 0;

5.2.2 Query① ~ Query⑥を検証します。

Query①

SELECT count(*) FROM sbdb.polardb_test_table;

1 row in set (15.86 sec)

EXPLAIN SELECT count(*) FROM sbdb.polardb_test_table;

Query②

SELECT Sum(field1), AVG(field2), Max(field3), Min(field4), Count(field11) FROM sbdb.polardb_test_table;

1 row in set (8 min 53.01 sec)

EXPLAIN SELECT Sum(field1), AVG(field2), Max(field3), Min(field4), Count(field11) FROM sbdb.polardb_test_table;

Query③

SELECT count(*) FROM sbdb.polardb_test_table WHERE START_DATE BETWEEN '2021-10-01' and '2022-03-30' and field4 > 30000 and field11 in ('bd');

1 row in set (5 min 31.51 sec)

EXPLAIN SELECT count(*) FROM sbdb.polardb_test_table WHERE START_DATE BETWEEN '2021-10-01' and '2022-03-30' and field4 > 30000 and field11 in ('bd');

Query④

SELECT unique_name, START_DATE, field2 FROM sbdb.polardb_test_table ORDER BY field2 DESC limit 100;

100 rows in set (5 min 35.95 sec)

EXPLAIN SELECT unique_name, START_DATE, field2 FROM sbdb.polardb_test_table ORDER BY field2 DESC limit 100;

Query⑤

SELECT b.unique_name, b.START_DATE, b.field15 FROM (SELECT AVG(field2) as field2_avg FROM sbdb.polardb_test_table ) a, sbdb.polardb_test_table b WHERE a.field2_avg < b.field2 limit 100;

100 rows in set (4 min 54.15 sec)

EXPLAIN SELECT b.unique_name, b.START_DATE, b.field15 FROM (SELECT AVG(field2) as field2_avg FROM sbdb.polardb_test_table ) a, sbdb.polardb_test_table b WHERE a.field2_avg < b.field2 limit 100;

Query⑥

WITH test1 as(SELECT AVG(field1) as field1_avg FROM sbdb.polardb_test_table ) SELECT b.unique_name, b.field1, b.START_DATE FROM test1 a, sbdb.polardb_test_table b WHERE a.field1_avg < b.field1 LIMIT 100;

100 rows in set (5 min 30.94 sec)

EXPLAIN WITH test1 as(SELECT AVG(field1) as field1_avg FROM sbdb.polardb_test_table ) SELECT b.unique_name, b.field1, b.START_DATE FROM test1 a, sbdb.polardb_test_table b WHERE a.field1_avg < b.field1 LIMIT 100;

5.3 パラレルクエリ「ON」を検証

５.3.1 パラレルクエリONに設定します

① パラレルクエリONの場合、innodb_adaptive_hash_indexがOFFに設定する必要があります

②下記のコマンドでパラレルクエリONに設定します

SET force_parallel_mode = ON;

SET max_parallel_degree = 8 ;

５.3.2 パラレルクエリONに設定します

1．Query①～Query⑥を検証します

Query①

SELECT count(*) FROM sbdb.polardb_test_table;

1 row in set (4.84 sec)

EXPLAIN SELECT count(*) FROM sbdb.polardb_test_table;

Query②

SELECT Sum(field1), AVG(field2), Max(field3), Min(field4), Count(field11) FROM sbdb.polardb_test_table;

1 row in set (54.57 sec)

EXPLAIN SELECT Sum(field1), AVG(field2), Max(field3), Min(field4), Count(field11) FROM sbdb.polardb_test_table;

Query③

SELECT count(*) FROM sbdb.polardb_test_table WHERE START_DATE BETWEEN '2021-10-01' and '2022-03-30' and field4 > 30000 and field11 in ('bd');

1 row in set (48.22 sec)

EXPLAIN SELECT count(*) FROM sbdb.polardb_test_table WHERE START_DATE BETWEEN '2021-10-01' and '2022-03-30' and field4 > 30000 and field11 in ('bd');

Query④

SELECT unique_name, START_DATE, field2 FROM sbdb.polardb_test_table ORDER BY field2 DESC limit 100;

100 rows in set (46.60 sec)

EXPLAIN SELECT unique_name, START_DATE, field2 FROM sbdb.polardb_test_table ORDER BY field2 DESC limit 100;

Query⑤

SELECT b.unique_name, b.START_DATE, b.field15 FROM (SELECT AVG(field2) as field2_avg FROM sbdb.polardb_test_table ) a, sbdb.polardb_test_table b WHERE a.field2_avg < b.field2 limit 100;

100 rows in set (44.97 sec)

EXPLAIN SELECT b.unique_name, b.START_DATE, b.field15 FROM (SELECT AVG(field2) as field2_avg FROM sbdb.polardb_test_table ) a, sbdb.polardb_test_table b WHERE a.field2_avg < b.field2 limit 100;

Query⑥

WITH test1 as(SELECT AVG(field1) as field1_avg FROM sbdb.polardb_test_table ) SELECT b.unique_name, b.field1, b.START_DATE FROM test1 a, sbdb.polardb_test_table b WHERE a.field1_avg < b.field1 LIMIT 100;

100 rows in set (44.57 sec)

EXPLAIN WITH test1 as(SELECT AVG(field1) as field1_avg FROM sbdb.polardb_test_table ) SELECT b.unique_name, b.field1, b.START_DATE FROM test1 a, sbdb.polardb_test_table b WHERE a.field1_avg < b.field1 LIMIT 100;

5.4 検証結果のまとめ

ここまでで準備したデータに対し6種類のSQL文を用いてパラレルクエリの効果を検証しました。結果は次の表の通りで、結論としては、パラレルクエリを使うことでパフォーマンスに対し大きな効果がありました。

データ量：67108864件

Query番号	SQL文	内容	parallel_mode = OFF	parallel_mode = ON
Query1	SELECT count(*) FROM sbdb.polardb_test_table;	全レコード件数	15.86 sec	4.84 sec
Query2	SELECT Sum(field1), AVG(field2), Max(field3), Min(field4), Count(field11) FROM sbdb.polardb_test_table;	全レコードの集計	8 min 53.01 sec	54.57 sec
Query3	SELECT count(*) FROM sbdb.polardb_test_table WHERE START_DATE BETWEEN '2021-10-01' and '2022-03-30' and field4 > 30000 and field11 in ('bd');	WHERE句	5 min 31.51 sec	48.22 sec
Query4	SELECT unique_name, START_DATE, field2 FROM sbdb.polardb_test_table ORDER BY field2 DESC limit 100;	降順および上位	5 min 35.95 sec	46.60 sec
Query5	SELECT b.unique_name, b.START_DATE, b.field15 FROM (SELECT AVG(field2) as field2_avg FROM sbdb.polardb_test_table ) a, sbdb.polardb_test_table b WHERE a.field2_avg < b.field2 limit 100;	FROM句で相関サブクエリ	4 min 54.15 sec	44.97 sec
Query6	WITH test1 as(SELECT AVG(field1) as field1_avg FROM sbdb.polardb_test_table ) SELECT b.unique_name, b.field1, b.START_DATE FROM test1 a, sbdb.polardb_test_table b WHERE a.field1_avg < b.field1 LIMIT 100;	WITH句	5 min 30.94 sec	44.57 sec

6. さいごに

・パラレルクエリの適用シナリオ

パラレルクエリは、大規模なテーブルクエリ、複数テーブルの結合クエリ、計算量の多いクエリなど、ほとんどの SELECTステートメントに適用できます。ただ、非常に短いクエリに対する効果はそんなに著しくないです。

・パラレルクエリの利用料金について

パラレルクエリ機能自体が無料ですので、よりよいパフォーマンスを得たい場合、全環境にてパラレルクエリ機能を有効にすることをお勧めいたします。

・纏め

本章ではAlibabaCloud PolarDBのパラレルクエリパフォーマンスについて検証してみました。parallelモードを有効にすることで、SQLクエリの処理時間が大幅に短縮することができました。興味を持っている方はぜひお試しいただければ幸いです。

Alibaba Cloud PolarDB for MySQL 8.0パラレルクエリを検証してみました

1. はじめに

2. ECSインスタンス環境を用意する

2.1 ECSインスタンスを作成します

3. PollarDB-Mysql8.0インスタンスを作成します

4.パラレルクエリ用データを準備します

４.1データを準備します

5. パラレルクエリを検証します

５.1　クエリSQL文

5.2 パラレルクエリ「OFF」を検証

5.3 パラレルクエリ「ON」を検証

5.4 検証結果のまとめ

6. さいごに

関連サービス

Alibaba Cloud

MSPサービス

＼業務課題をデジタルで支援／

おすすめの記事

Alibaba Cloud PolarDB for MySQL 8.0パラレルクエリを検証してみました

1. はじめに

2. ECSインスタンス環境を用意する

2.1 ECSインスタンスを作成します

3. PollarDB-Mysql8.0インスタンスを作成します

4.パラレルクエリ用データを準備します

４.1データを準備します

5. パラレルクエリを検証します

５.1 クエリSQL文

5.2 パラレルクエリ「OFF」を検証

5.3 パラレルクエリ「ON」を検証

5.4 検証結果のまとめ

6. さいごに

関連サービス

Alibaba Cloud

MSPサービス

＼ 業務課題をデジタルで支援 ／

おすすめの記事

５.1　クエリSQL文

＼業務課題をデジタルで支援／