AWS Aurora MySQL Parallel Query の基礎研究

AWS Aurora MySQLには、高性能を期待できる Parallel Query という機能があります。

実際、良いモノっぽいのですが、非常に情報が少ないので私めがいつものように掘り下げて、お役に立てればという徳を積む行為であります。

Parallel Query とは

詳しくは下記リンクを見たほうがいいのですが、頑張って要約してみます。

通常のDB処理は、データを可能な限りメモリ上に置いておいて処理しようとしますが、オンメモリじゃないデータはストレージから取得する必要があり、データ取得後はDB本体における１スレッドがクエリ処理を行います。

Auroraのストレージは複数のノードに分かれていて、そのノードのCPUは普段は暇なので、分散処理できるクエリならば、わざわざDB本体にデータを掻き集めてから処理しなくても、ストレージ上で直にデータを扱って処理してしまい、結果だけDB本体に返せば速くなるんじゃね？っていうものです。

実際、簡単なテストデータとクエリで、通常処理よりParallel Queryの方が速いことを確認できるのですが、いかんせんAWSドキュメントは開発者目線としては十分なのでしょうが、下々のユーザー目線としては足りていないと言わざるをえません。

どのようなクエリが、どのくらい速くなるのか、といった素朴な疑問はドキュメントや既存情報では解決しないので、いい感じにブラックボックスに手を突っ込んでみることにしました。

リンク集

公式ドキュメントの１ページによくまとまっているのですが、長いし読んだとしても気軽に採用する気にはなれないでしょう。でも検討するなら読まなきゃダメです。

速度比較

まずは、どのくらい速くなるのか、を確認して興味を持ってもらうAWSの犬仕草です。

手順は省きますが、sysbench で100～1000万行・0.2～2.2GB のテーブルを10個作り、Parallel 対象と判断されるクエリと条件で記録しました。

SELECT sql_no_cache COUNT(c) FROM sbtest1 WHERE c = 'c' and pad = 'p';

1	SELECT sql_no_cache COUNT(c) FROM sbtest1 WHERE c = 'c' and pad = 'p';

判定条件はあとでまとめますが、ここではWHERE句にINDEXのないカラムを指定しています。

全てストレージからの読み込み、全てオンメモリでの処理、ParallelQueryとしての処理、の３つを４つのインスタンスタイプで記録した結果がこちら。単位は秒です。

速度

まず全体的な速度としては、From Storage は数十秒単位、On Memory では数秒単位、Parallel では１秒未満であることが読み取れます。

ストレージ読み込みが遅いのは当然として、全てオンメモリよりもParallel Queryの方が速い（可能性がある）、というのは凄いことで、これだけで十分に検討する価値があると判断できます。

Parallel Query の処理時間のブレ

Parallel Query の記録を範囲にしたのは理由があり、数回実行していると、そう少なくない確率で謎の引っかかりが発生したからです。

記録をみるに、それが発生した場合の待機時間の多くが１秒で、この発生理由は不明です。インスタンスタイプによって、同時並列クエリセッション数が決まっていますが、今回は自身の手動試験１つなので考えにくく、もしかしたらストレージ側の事情によるかもですが知る術はありません。

仮にそういう待機時間が発生するとしても、本来はより大きく長い処理を想定したものでしょうから、大局的には小さな差と受け取れるかも、というのはあります。

インスタンスタイプの差

仕組みとしてインスタンスタイプによって、同時並列クエリセッション数に差があるので、もしかしたらParallel Queryとしての性能差もあるかもしれない、と思いましたが、On Memory、Parallel での性能差は認められない程度の記録となりました。

From Storage だけはそれなりの差が出ましたが、これは過去に『Auroraを真に理解するための性能検証』でIOPS上限の差を確認したのでなんら不思議ではありません。それ以外にも並列度なり、ストレージ性能になにかしらの差をつけているのでしょう。

費用の仕組み

有効そうな雰囲気に触れたところで、先に費用について考えておきます。公式ブログでの費用の表記はこうなっています。

費用 – Parallel Query の利用には、追加料金はありません。ただし、ストレージに直接アクセスできるため、IO コストが増加する可能性があります。
新しくなった Amazon Aurora の Parallel Query | Amazon Web Services ブログ

非常に曖昧な表現ですが、元のクエリを通常処理した時と比べて、Parallel Queryにすると全データのストレージアクセスが発生するから、その差分だけIO費用が高くなる可能性があるということだと思います:-)

通常クエリのデータ読み込み状態に対して、ParallelにしたときのIOコスト差分を表にするとこんな感じ。

	OnMemory	FromStorage		差分コスト
オンメモリ型	◎	☓	→	◎
ストレージ型	☓	◎	→	☓
混合型	○	△	→	○

元のクエリとデータの状況によってコスト差が変わるから、”可能性”って表現になってるわけですね。

まぁ、コストのことを考えることも大事ですが、多少のコストよりも処理時間の短縮の方が重要な場面も多いので、Parallelを適用しつつも極端にコストが高騰していないかを、適用後に注視しておくとよいでしょう。

設定による有効・無効

Parallel Queryを有効にするには、特定のAuroraバージョン以上で起動する必要があります。ただし、それ未満のバージョンでも今はParallel対応のエンジンバージョンがあるので、新旧の２種類があり、それぞれ設定方法が異なるという、わかりづらい仕様になっています。

新バージョン

新しい方というか通常は、Aurora MySQL 1.23 (v5.6) または 2.09 (v5.7) 以上を条件としています。

この場合、aurora_parallel_query パラメータの ON/OFF でParallel Queryの利用を切り替えられ、パラメータグループで永続的に設定することも、SET SESSIONで一時的に有効にすることも可能になっています。

mysql> SET SESSION aurora_parallel_query=1;

1	mysql> SET SESSION aurora_parallel_query=1;

また、旧バージョンで使うパラメータ aurora_pq はこちらでは無関係になります。

旧バージョン

通常条件外であるバージョンでも、例えばエンジン名『Aurora (MySQL 5.6) parallelquery_1.22.3』を選ぶことでParallelを使うことができます。

起動時にあえてこれを選ぶことはおそらくないので、稼働中のDBでマイナーバージョンを変えたくないけどParallel対応したい場合に、これを選ぶことがあるでしょう。

こちらの場合、パラメータグループに関係なく常時機能がONになっており、ON/OFFの切り替えは aurora_pq で行います。

mysql> SET SESSION aurora_pq =1;

1	mysql> SET SESSION aurora_pq =1;

強制的な実行

EXPLAINすると、Extraでparallelとして実行されるかどうかを確認できるのですが、データとクエリ次第ではParallel有効の判断がされないことがあります。

その場合、テストとしてや、本番でも強制的にParallelとしたい場合は、aurora_pq_force によって確実に選択できます。

mysql> SET SESSION aurora_pq_force=1;

1	mysql> SET SESSION aurora_pq_force=1;

aurora_pq_force を実際に使うかどうか、はこの後に記述する様々な条件を見て判断するとよいです。

ハッシュ結合の最適化

パラメータ aurora_disable_hash_join=0 にすることで、ハッシュ結合を最適化できるかもしれません。

詳しくは試していないので、Aurora MySQL でのハッシュ結合の使用を参照してください。

オプティマイザ

上記に関連して、オプティマイザの動作制御で役立つかもしれないやつです。

SET SESSION optimizer_switch='hash_join=on';
SET SESSION optimizer_switch='hash_join_cost_based=off';

1 2	SET SESSION optimizer_switch='hash_join=on'; SET SESSION optimizer_switch='hash_join_cost_based=off';

設定の確認

一通り、設定を確認するためのクエリを置いておきます。エンジンバージョンによっては存在しないものもあります。

mysql>
select aurora_version();
select @@aurora_parallel_query;
select @@aurora_pq;
select @@aurora_pq_supported;
select @@aurora_pq_force;
select @@aurora_disable_hash_join;

mysql>
select @@query_cache_type;
show status like 'Aurora_pq_max_concurrent_requests';

mysql>

select aurora_version();

select @@aurora_parallel_query;

select @@aurora_pq;

select @@aurora_pq_supported;

select @@aurora_pq_force;

select @@aurora_disable_hash_join;

mysql>

select @@query_cache_type;

show status like 'Aurora_pq_max_concurrent_requests';

有効にできない条件

設定以外に、様々な条件でParallelを有効にできないので、まとめておきます。見落としあったらスマソ。

インスタンスタイプ

db.r* のタイプでしか利用できません。t系はダメです。

ROW_FORMAT

COMPACT または DYNAMIC のみです。COMPRESSED または REDUNDANT の行形式を使用するテーブルには適用されません。

パーティション

Partition が切られているテーブルには適用されません。

カラム型

TEXT、BLOB、JSON、GEOMETRY データ型は、並列クエリではサポートされていません。これらの型の列を参照するクエリは、並列クエリを使用できません。

DML

UPDATE / DELETE はダメです。SELECT と INSERT～SELECT に適用できます。

LIMIT

LIMIT句を含むと適用できません。ただ、ドキュメントの説明がこんな感じなのと、

現在のところ、LIMIT 句を含むクエリブロックでは、並列クエリは使用されません。GROUP by、ORDER BY、または結合を使用して、以前のクエリフェーズでも並列クエリを使用することができます。

ドキュメント内でもLIMITを使った例が記述されているので、早とちりせず注意していきたい部分です。

また、これも aurora_pq_force=1 にすることで無理やりParallelとして実行することができますが、不安定な結果が返ることがあるので、aurora_pq_force の取り扱いには注意が必要です。

実際に結果が変わることを確認した例です。

mysql> SET SESSION aurora_pq_force=0;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT id FROM sbtest1 ORDER BY k DESC LIMIT 1 OFFSET 5000000;
+---------+
| id      |
+---------+
| 4051619 |
+---------+
1 row in set (1.21 sec)

mysql> SET SESSION aurora_pq_force=1;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id FROM sbtest1 ORDER BY k DESC LIMIT 1 OFFSET 5000000;
+---------+
| id      |
+---------+
| 1440388 |
+---------+
1 row in set (4.82 sec)

mysql> SET SESSION aurora_pq_force=0;

Query OK, 0 rows affected (0.01 sec)

mysql> SELECT id FROM sbtest1 ORDER BY k DESC LIMIT 1 OFFSET 5000000;

+---------+

| id |

+---------+

| 4051619 |

+---------+

1 row in set (1.21 sec)

mysql> SET SESSION aurora_pq_force=1;

Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id FROM sbtest1 ORDER BY k DESC LIMIT 1 OFFSET 5000000;

+---------+

| id |

+---------+

| 1440388 |

+---------+

1 row in set (4.82 sec)

Parallel判定されるクエリ

EXPLAINするとオプティマイザ先生が判断して、もしParallel Queryとして処理すべきとなったら、このようにExtraに parallel query の文字列が表示されます。

mysql> EXPLAIN SELECT COUNT(*) FROM sbtest1 WHERE c like '%z%';
+----+-------------+---------+------+...+---------+----------------------------------------------------------------------------+
| id | select_type | table   | type |   | rows    | Extra                                                                      |
+----+-------------+---------+------+   +---------+----------------------------------------------------------------------------+
|  1 | SIMPLE      | sbtest1 | ALL  |   | 9865092 | Using where; Using parallel query (1 columns, 0 filters, 1 exprs; 0 extra) |
+----+-------------+---------+------+...+---------+----------------------------------------------------------------------------+

mysql> EXPLAIN SELECT COUNT(*) FROM sbtest1 WHERE c like '%z%';

+----+-------------+---------+------+...+---------+----------------------------------------------------------------------------+

+----+-------------+---------+------+ +---------+----------------------------------------------------------------------------+

| 1 | SIMPLE | sbtest1 | ALL | | 9865092 | Using where; Using parallel query (1 columns, 0 filters, 1 exprs; 0 extra) |

+----+-------------+---------+------+...+---------+----------------------------------------------------------------------------+

では、どのようなクエリ内容の場合にParallel判定されるのか、というのは、ドキュメントに書いてはあるのですが実際にシンプルなクエリで確認していくことにしましょう。もちろん aurora_pq_force=0 です。では画像で失礼。

シンプルなクエリではありますが、これらからわかることは

WHERE句がないと☓
INDEXを使う系統は☓
ORDER BY は WHERE がつけば○だけど、LIMITは☓
GROUP BY は WHERE がつけば○だし、LIMIT も○

を基本として抑えておけばよさげです。

結合クエリ

次にシンプルな結合クエリでは、どのようなParallel判定になるのかを確認します。

後述する条件になりますが、テーブルのデータ容量でも判定されるので、そもそも単体テーブルでParallel判定されるものを２つと、されないものを２つ用意し、２テーブルを４パターンで結合してみました。

まず a_1, a_2 が通常判定で、b_1, b_2 が並列判定な証拠です。

次に、結合した結果です。

ちなみに、aurora_pq_force=1 にすると、結合時も全テーブルがParallel Queryの対象として判定されます。

これらからわかることは、

結合時、単発が○のテーブルは parallel として、☓のは通常通り処理される
force は全処理が parallel になるので、本来☓判定が○になることで非効率になる可能性がありそう

innodb_buffer_pool_size との関係

この検証を始めた当初は、aurora_pq_force=1 で強制的に試して速いことを確認しましたが、じゃあどういうクエリや条件でEXPLAINにて判定○になるのか、をいざ aurora_pq_force=0 で調べると全然ダメで、なんじゃコリャとなりました。

攻めあぐねた末に勘で、IOコスト量が高くなればOK判定くれんじゃね？って考え、innodb_buffer_pool_size に手を出してみたら大当たりでした。

容量による判定基準

100万行単位でテーブルを作り、innodb_buffer_pool_size を極端に下げてEXPLAINしていきます。pool size がどのくらいの時、どのテーブルに対してParallelが○☓になるのかをチェックした表がこちら。☓より上は全て☓で、○より下は全て○になることは、およそですが確認して記述を省略しています。

結果だけ見ると綺麗になりましたが、この規則性を見つけて記録するまで、マジで大変でMPめっちゃ減りました。

これから読み取れることは、２つあります。

テーブルの Data_length ＜ innodb_buffer_pool_size だとParallel判定にならない
Data_length が1GB 未満だと判定は☓になる

大変なのでこれ以上はやめましたが、１つのテストデータでの推測としては、それなりに信憑性のある結果になったかと思います。そりゃ r5.2xlarge のデフォルト pool size 42GB じゃParallel判定○にならないよな、と。

結合テストの時にも、わざとギリギリの境目の容量で試したわけですが、結合対象のテーブルのData_length合計はこの仕様には関係なく、テーブル単体の容量しか関係ないことも判明しています。

もしこの性質が実践的データとクエリでも強く適用されるとしたら、インスタンスタイプを上げるほど Parallel Query として扱われる度合いが減少する、ということになるので、せっかくParallelで速く動いていたものがタイプを上げることで動かなくなり、aurora_pq_force=1 で強制せざるをえなくなる、というような美しくない運用になる可能性も頭の片隅に置いておく必要があることになります。

そう考えると、最小1GBに対して、上の方でも何GB以上ならParallel対象とする。みたいな条件があってもおかしくないかもですね。やらんけど。

最小判定の境目

100万単位では少々心もとないので、境目を掘り下げるとこんな感じ。

Data_length が 1GiB を超えたところから、ちょうどParallel判定されることがわかりましたが、いかんせん

SHOW TABLE STATUS FROM sbtest WHERE Name = 'sbtest1'

1	SHOW TABLE STATUS FROM sbtest WHERE Name = 'sbtest1'

の結果数値はわりとブレるので、1GB なのか 1GiB なのか、そもそも境目の区切りがよいのは偶然なのか、はわかりません。

わかりませんが、仮にこうだとすると、Index_length の容量を考慮しないことには理屈が通ります。Parallel Queryの処理にはインデックスが無関係だからです。

その他

判定の変化タイミング

今回の pool size を変える実験は再起動を必要としますが、一度Parallel判定○とされたクエリは、再起動後に☓となる条件でも判定○のままであることを確認しました。Auroraは再起動しても残るデータがあるはずなので、それはいいんですが、じゃあどのタイミングで判定結果が変わるかという疑問が残ります。

これは追求していないのでメモになりますが、１行INSERTした程度では判定が更新されることはありませんでした。

しかし、TRUNCATE するとさすがに次のEXPLAINで判定が○から☓に変わったので、オプティマイザ先生の都合でよしなにやってくれるんだろうと思います。

実践では

Parallel Query のアピール記事として、2年前ですがNetflixの例があります。

Query response time reduction
　* Peak speed up ~120x
　* > 10x speedup: 8 of 22 queries
Auroraのパラレルクエリ機能をテストし、パフォーマンス向上は非常に優れていました。インスタンスタイプをr3.8xlargeからr3.2xlargeに減らすことができました。このユースケースでは、パラレルクエリは私たちにとって大きな利点でした。
(PDF) Deep Dive on Amazon Aurora – Parallel Query performance results

正しく適用できれば、これくらいの効果があるだろうことは想像に難くないです。
(邪推すると、インスタンスタイプを下げないと有効にならなかった、とも読めます;-)

ただ、今回検証したとおり、複雑な要素が絡み合った結果、速くなるのか、コスパは良いのか、そもそも強制せず適用されるのか、が非常にわかりづらい仕組みとなっています。

もしかしたらペッとグローバルパラメータで有効にしておくだけで、上手いこと改善されるかもしれませんが、我々が想像するより遥かに大きなデータ・複雑なクエリを想定したシステムであり、ちまちま細かくチューニングしていく用途ではないかもしれません。

本記事はあくまで最底辺のベース検証なので、本番サービスによってどう転ぶかはわかりませんが、少なくとも適用するだけでハッピーになれる仕組みではなく、十分な理解と検証、そして監視の設定、の先にある大きな改善の可能性、といった立ち位置になると思われます。

ということで、いつものことですが、素敵なシステム・楽しいオモチャを

Thanks AWS, Thanks Aurora:-)