なからなLife

geekに憧れと敬意を抱きながら、SE、ITコンサル、商品企画、事業企画、管理会計、総務・情シス、再び受託でDB屋さんと流浪する人のブログです。

MySQL 8.0.14でSELECT COUNT(*)がパーティションテーブルでも加速する！ - 「innodb_parallel_read_threads」検証その４

MySQL

ココまでのおさらい

MySQL 8.0.14でSELECT COUNT(*)が加速する！- 「innodb_parallel_read_threads」検証その１ - なからなLife
MySQL 8.0.14でSELECT COUNT(*)が加速しない！- 「innodb_parallel_read_threads」検証その２ - なからなLife
MySQL 8.0.14で全件 COUNT(*)でも加速しない！？- 「innodb_parallel_read_threads」検証その３ - なからなLife


では、「innodb_parallel_read_threads」というパラメータによって

  • SELECT COUNT(*)のテーブル全件カウントが加速する
  • WHERE条件等を付けたら加速しない。
  • COUNTではない場合も加速しない
  • ALTER TABLE table_name ENGINE INNODBした直後のテーブルだと効果がでない（なぜ？）

となっていることがわかりました。


これに続く４本目は、
4.「innodb_parallel_read_threads」は、パーティショニングしたテーブルに効くか
を扱います。

環境

HW、OS、Versionともにコレまでと同じです。
使うテーブルも同じ、16,777,216件のデータが入ったテーブルです。
テーブルは予め、検証するパーティショニング４種＋パーティショニングなしの全パターンのものを用意しています。


条件

今回は、以下のケースを扱います。

まずは、パーティションの種類。

  1. RANGEパーティション構成のテーブルを全件カウント
  2. HASHパーティション構成のテーブルを全件カウント
  3. KEYパーティション構成のテーブルを全件カウント
  4. LISTパーティション構成のテーブルを全件カウント
  5. パーティションなしを全件カウント（比較用）

RANGE COLUMNSとLIST COLUMNSパーティションは、まあ、元となるRANGEやLISTと特性それほど変わらないでしょう、ということで、省略。

また、ついでに
「特定パーティションだけの全件検索は加速するか？（SELECT * FROM table_name PARTITION (partition_name);）」
も計測します。



共通する条件として、どのタイプのパーティションでも、分割数は「５」で統一します。
分割結果、微妙に各パーティションへの格納件数、ibdファイルサイズに誤差が出てますが、ご容赦を。
RANGEは4,000,000件刻み、LISTはmod(itemid,5)で分散させます。


全テーブルとも、以下の手順で作成しています

  • CREATE TABLE (パーティション定義なし）
  • ALTER TABLE table_name PARTITION BYでパーティション作成
  • PK値のみでレコード生成
  • UPDATE文で、PK以外の列にもそれっぽい値を格納

MySQL 8.0.14で全件 COUNT(*)でも加速しない！？- 「innodb_parallel_read_threads」検証その３ - なからなLife


の教訓から、オプティマイズは実行していません。



検証結果

パーティションあり／なしの比較用に、前と同じ「パーティションなし」での全件カウントを計測したのですが、以前の倍くらい遅いです。
違いといえば、「PK列に欠番があるかないか」なんですが。
欠番なくしたら、激遅になりました。
一連の検証後、「欠番あり」のデータを作って計測したら、やっぱり速かった。件数同じなのに。わけわからん。。。

とはいえ、今回の検証に閉じてみれば、全く同じ条件でできているので、スルーします。。。



全件カウント
条件 innodb_parallel_read_threads 1回目 2回目 3回目
16
9.12
9.52
8.93
8
10.61
9.38
10.28
4
20.07
18.84
17.93
1
105.95
102.17
92.55
RANGE
16
11.88
12.68
11.98
RANGE
8
12.73
13.74
12.16
RANGE
4
21.21
23.97
22.97
RANGE
1
93.01
94.08
99.37
HASH
16
10.99
10.77
10.99
HASH
8
12.74
11.93
11.75
HASH
4
19.23
17.56
21.62
HASH
1
98.89
83.99
98.41
KEY
16
10.74
10.71
9.43
KEY
8
11.95
11.49
11.14
KEY
4
20.06
16.35
19.44
KEY
1
100.27
94.69
114.01
LIST
16
10.77
10.34
10.26
LIST
8
12.5
11.87
11.55
LIST
4
21.62
21.78
22.83
LIST
1
114.25
98.61
100.34

どのタイプのパーティショニングでも、「innodb_parallel_read_threads」による並列処理、しっかり効いてますね。
ただ、パーティションあり／なしの差がほとんどない。。。

なお、MySQL5.6に書いてあるコレ、5.6から特に変化していないようですね。
下調べの段階では、なんかうまく効いてるように見えたケースがあったのですが、目の錯覚か、環境整備中のミスだったようです。

通常はパーティショニングに関連付けられるその他の利点を次に示します。これらの機能は MySQL パーティショニングに現在実装されていませんが、オラクルの優先事項リストの上位にあります。

  • SUM()、COUNT() などの集約関数を含むクエリーを簡単に並列化できます。そのようなクエリーの簡単な例として、SELECT salesperson_id, COUNT(orders) as order_total FROM sales GROUP BY salesperson_id; を挙げられます。「並列化」することで、クエリーを各パーティションで同時に実行してから、すべてのパーティションで取得される結果を単に集約するだけで最終的な結果が得られることを意味します。
  • 複数のディスクにデータシークを分散することによって、クエリーのスループットが向上します。

MySQL パーティショニング開発は継続されているため、このセクションおよび章が更新されているかどうかを頻繁にチェックしてください。
https://dev.mysql.com/doc/refman/5.6/ja/partitioning-overview.html

しかも、この記述、MySQL5.6ドキュメントの日本語版にしか記載がない（英語版5.6、5.7、8.0に一切記述がない）ことに今気がついた。。。

これはつまり「（もう）期待するなよ」ってことですかね。。。

特定パーティションを全件検索
条件 innodb_parallel_read_threads 1回目 2回目 3回目
RANGE
16
2.51
2.82
2.81
RANGE
8
3.27
2.65
3.55
RANGE
4
5.27
4.17
5.12
RANGE
1
19.04
16.18
20.85
HASH
16
2.21
2.32
2.23
HASH
8
2.32
2.61
2.36
HASH
4
4.27
4.06
3.79
HASH
1
18.23
19.14
20.46
KEY
16
2.03
1.8
1.92
KEY
8
1.97
1.97
2.12
KEY
4
3.2
3.01
3.27
KEY
1
19.44
18.67
19.55
LIST
16
2.32
2.21
2.27
LIST
8
2.83
2.22
2.44
LIST
4
3.62
4.43
4.83
LIST
1
18.03
19.13
17.14

こちらも、「innodb_parallel_read_threads」による並列処理、しっかり効いてますね。
WHERE句があると並列化の恩恵を受けられないけど、PATITION句ならば恩恵を受けられる、ということのようです。


そういえば、MySQLのインデックス、グローバルインデックスをサポートしていないですね。
それで、パーティショニングの為の分散キーはPK列、あるいは、複合列PKの一部というルールがあるわけです。
そして、パーティション毎にインデックスを持っていて、そのインデックスのフルスキャンとなることから、パーティションプルーニングでも「innodb_parallel_read_threads」が効くんじゃないか、と推測しています。

まとめ

「innodb_parallel_read_threads」による全件カウントの並列化は


次回以降ですが、第１回で言及していた「5. COUNT以外のデータアクセスに、影響はあるか」は「ないよ」ってドキュメントで宣言されてしまいました。（第２回の終盤を参照）
そこで、「バッファプールを広げて、全部データが載った状態でどれくらい影響があるか」を見てみたいと思います。

これまで、「パラメータは、起動時のバッファダンプの扱い以外は、原則デフォルト」でやってきましたが、これ、バッファプールが128MBで、かなり小さいです。
なので、過去の検証は、起動直後も３回目も、ギガ単位のデータの読み込みにおいてはバッファの影響はほとんど受けていないように見えますね。

しかし、MySQLは、大量データロードでバッファが洗い流されるのを回避する仕組みが前のバージョンから存在してるので、全部バッファに乗せるまでが大変そう。。。



あとは、mysqldumpって加速するの？って話をいただいたので、それも続けてやってみたいと思います。
が、dumpってSELECT COUNT(*)って走るんだっけ？とか、セッションレベル変数「innodb_parallel_read_threads」をどうやって変えられるんだろう、とか、いろいろ悩むところがありますが。


詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE)

詳解MySQL 5.7 止まらぬ進化に乗り遅れないためのテクニカルガイド (NEXT ONE)