Alibaba Cloud の POLARDB を試してみる（５）MySQL 8.0 互換版でパラレルクエリ（２）

この記事は MySQL Advent Calendar 2019 10 日目のエントリとなります。

昨日は tom__bo さんでした。

• Dive into Binary logs（tom__bo’s Blog）

そして、以下の記事の続きでもあります。

• Alibaba Cloud の POLARDB を試してみる（１）MySQL 8.0 互換版起動編
• Alibaba Cloud の POLARDB を試してみる（２）MySQL 8.0 互換版のバッファプールまわりを中心に
• Alibaba Cloud の POLARDB を試してみる（３）MySQL 8.0 互換版でパラレルクエリを試す
• Alibaba Cloud の POLARDB を試してみる（４）PostgreSQL 11 互換版起動＋パラレルスキャン編

この記事では、その３の続きとして、MySQL 8.0 互換版のパラレルクエリのうち文字列を扱うパターンを試してみます。

試すこと

• パラレルクエリ無しとパラレルクエリ 4 並列で、以下を比較
  • 文字列のソート
  • 文字列のLIKE検索
• COLLATION（照合順序）による速度向上の違いを見る
  • utf8mb4_0900_ai_ci（MySQL 8.0 デフォルト・アクセントと大文字小文字を区別しない）
  • utf8mb4_0900_as_cs（アクセントと大文字小文字を区別する）
  • utf8mb4_bin（文字コードの違いを区別する）
• インスタンスタイプは前回同様 4 Core 16 GB（polar.mysql.x4.large）を選択

※COLLATION についてはこちらが参考になります。

• MySQL Innovation Day Tokyo で MySQL 8 の文字コードについて話した（＠tmtms のメモ）

テーブル定義とテストデータ

こちらの記事のものを COLLATION にあわせて変更し、データ行数を 10 倍しました（各テーブル 100 万行）。

テーブル定義

テーブル定義

mysql> CREATE DATABASE `utf8mb4_0900_ai_ci_test`;
Query OK, 1 row affected (0.03 sec)

mysql> USE `utf8mb4_0900_ai_ci_test`;
Database changed
mysql> CREATE TABLE `utf8mb4_0900_ai_ci_test`.`utf8mb4_test` (
    ->   `id` int(10) NOT NULL AUTO_INCREMENT,
    ->   `str` varchar(1000) COLLATE utf8mb4_0900_ai_ci NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `str` (`str`(767))
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
Query OK, 0 rows affected (0.43 sec)

mysql> CREATE DATABASE `utf8mb4_0900_as_cs_test`;
Query OK, 1 row affected (0.01 sec)

mysql> USE `utf8mb4_0900_as_cs_test`;
Database changed
mysql> CREATE TABLE `utf8mb4_0900_as_cs_test`.`utf8mb4_test` (
    ->   `id` int(10) NOT NULL AUTO_INCREMENT,
    ->   `str` varchar(1000) COLLATE utf8mb4_0900_as_cs NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `str` (`str`(767))
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_as_cs;
Query OK, 0 rows affected (0.02 sec)

mysql> CREATE DATABASE `utf8mb4_bin_test`;
Query OK, 1 row affected (0.03 sec)

mysql> USE `utf8mb4_bin_test`;
Database changed
mysql> CREATE TABLE `utf8mb4_bin_test`.`utf8mb4_test` (
    ->   `id` int(10) NOT NULL AUTO_INCREMENT,
    ->   `str` varchar(1000) COLLATE utf8mb4_bin NOT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `str` (`str`(767))
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
Query OK, 0 rows affected (0.94 sec)

データ（一部）

データ（計100万行分）

SET AUTOCOMMIT=0;
INSERT INTO utf8mb4_test SET str=REPEAT('１２３４５６７８９０', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('ＡＢＣＤＥＦＧＨＩＪＫＬＭＮＯＰＱＲＳ ＴＵＶＷＸＹＺABCDEFGHIJKLMN', 25);
INSERT INTO utf8mb4_test SET str=REPEAT('ａｂｃｄｅｆｇｈｉｊｋｌｍｎｏｐｑｒｓ ｔｕｖｗｘｙｚabcdefghijklmn', 25);
INSERT INTO utf8mb4_test SET str=REPEAT('あいうえおかきくけこ', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('さしすせそたちつてと', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('なにぬねのはひふへほ', 100);
（中略）
INSERT INTO utf8mb4_test SET str=REPEAT('まみむめもやゆよらり', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('るれろわをんっゃゅょ', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('がぎぐげござじずぜぞ', 100);
INSERT INTO utf8mb4_test SET str=REPEAT('だぢづでどばびぶべぼ', 100);
COMMIT;

ソート用 SQL

ソート用SQL

mysql> SELECT id, LEFT(str, 30) FROM `utf8mb4_test` ORDER BY str, id LIMIT 100;

※バッファプールに載っていない状態と載っている状態で試すために 2 回連続で実行しています。

LIKE検索用 SQL

LIKE検索用SQL

SELECT COUNT(*) FROM `utf8mb4_test` WHERE str LIKE '%かきくけこ%';

※同じく 2 回連続で実行しています。

結果

それぞれのセットを 2 回実行した平均を取りました。
※変化が少なかったため 2 回で打ち切りました（時間とお金の関係上…）。

• バッファプールに載っていない状態で約 3 倍、載っている状態で約 2 倍パラレルクエリによって高速化した
  • 前回、数値カラムの集計で試したときとほぼ同じ傾向
• 高速化の比率については COLLATION による差はなかった
• バッファプールに載っていない状態での比率のばらつきが多少大きくなった
  • I/O が発生するため当然の結果？

utf8mb4_0900_ai_ci

パラレルクエリ有無	ソート・バッファプール無	ソート・バッファプール有	LIKE・バッファプール無	LIKE・バッファプール有
無効 (a)	65.60s	10.14s	62.81s	9.55s
有効・4 並列 (b)	19.46s	5.14s	19.85s	4.62s
(a) / (b)	3.37	1.97	3.16	2.07

utf8mb4_0900_as_cs

パラレルクエリ有無	ソート・バッファプール無	ソート・バッファプール有	LIKE・バッファプール無	LIKE・バッファプール有
無効 (a)	88.79s	33.00s	63.35s	10.76s
有効・4 並列 (b)	31.82s	16.54s	20.23s	5.38s
(a) / (b)	2.79	2.00	3.13	2.00

utf8mb4_bin

パラレルクエリ有無	ソート・バッファプール無	ソート・バッファプール有	LIKE・バッファプール無	LIKE・バッファプール有
無効 (a)	57.81s	3.18s	66.10s	12.92s
有効・4 並列 (b)	17.44s	1.53s	23.31s	6.21s
(a) / (b)	3.31	2.08	2.84	2.08

次回は

PostgreSQL 11 互換版で GIS 機能を試します。

• Alibaba Cloud の POLARDB を試してみる（６）PostgreSQL 11 互換版で Ganos を試す

---

MySQL Advent Calendar 2019 明日（11 日目）は keny_lala さんです。

• mysqldumpがFLUSH TABLESでタイムアウトしたので調べた話（kenken0807_DBメモ）