なからなLife

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

MySQL 8.0.14で全件 COUNT(*)でも加速しない!?- 「innodb_parallel_read_threads」検証その3

MySQL 8.0.14でSELECT COUNT(*)が加速する!- 「innodb_parallel_read_threads」検証その1 - なからなLife
MySQL 8.0.14でSELECT COUNT(*)が加速しない!- 「innodb_parallel_read_threads」検証その2 - なからなLife
の続きです。

まさかの、ネガティブ情報が続いてしまった。。。

パーティショニングの話をするつもりだったのですが

いろいろなパーティションを試しながら計測しつつ、そもそもパーティション前の数字ももう一度計測しておこう、と思ったら、今まで加速してたパターンで加速しないケースにぶつかってしまいました。



こんな感じです。

環境

「その1」「その2」と基本的には同じですが、データを準備する手順に、「条件」で言及するパターンをもたせています。
バージョンはMySQL8.0.14のみです。

条件

PK連番の欠番の有無

大量テストデータ生成のテクニックとして、PK列がINT AUTO_INCREMENTなテーブルに

INSERT INTO table_name () VALUES ();
INSERT INTO table_name (itemid) SELECT 0 FROM table_name; -- 繰り返すたびに倍のレコードが生まれる

でPKだけのレコードを倍々で増やしていく、というのがあります。

ただしこれ、AUTO_INCREMENTなのに、大量に欠番を生み出すんです。
最初の10件だけでも、5、10、11、12、と欠番が発生します。


欠番をなくすためには、

  • 一度上記の方で必要件数膨らませたテーブルを使って、同じ構造(AUTO INCREMENT列)をもつもう1つのテーブルにINSERTを SELECTする。
  • 倍々生成のINSERT文を select PK列+1、select PK列+2、select PK列+4、と自分で計算しながら実行する。

という方法がとれるので、それを使います。

オプティマイズの有無

「ALTER TABLE table_name ENGINE INNODB」です。
InnoDB以外のテーブルに対して発行するとInnoDBに変換するコマンドですが、もともとInnoDBなテーブルに発行すると、物理ファイルの再構成が走り、実質デフラグが行われます。

ただしこれ、ちょっとくせがあるというか、意味不明な挙動をします。
PK昇順でキレイにINSERTした=デフラグ不要な状態のときよりも、このコマンドを発行した後のほうが、ibdファイルが大きいのです。。。
未だその理由はわかってません。知っている限りMySQL5.6からMySQL8.0まで同じ挙動です。

パターン

上記の組み合わせで、こんなパターンとなりました。
これに、「innodb_parallel_read_threads」を1、4(デフォルト)、8、16で試しています。

  1. PK連番に欠番有り+オプティマイズなし
  2. PK連番に欠番なし+オプティマイズなし
  3. PK連番に欠番有り+オプティマイズあり
  4. PK連番に欠番なし+オプティマイズあり

検証結果

条件 ibd file size innodb_parallel_read_threads 1回目(秒) 2回目(秒) 3回目(秒)
1
1971322880
16
7.06
6.74
6.65
8
8.13
7.68
8.08
4
12.43
11.51
12.31
1
29.35
28.44
28.59
2
1971322880
16
7.01
6.69
7.12
8
8.07
7.54
7.62
4
11.77
12.43
12.27
1
29.78
28.48
29.26
3
1639972864
16
25.48
23.93
24.66
8
26.58
25.12
25.61
4
26.94
25.31
27.08
1
28.88
27.31
27.21
4
1635778560
16
23.53
22.14
22.97
8
26.13
24.56
25.07
4
26.36
24.95
25.14
1
28.24
26.87
28.74


オプティマイズしたテーブルに対して、全件カウントのパラレル化がほとんど効果を出していませんね。

このパターンを作るのに、ものすごく時間がかかりましたが、平たく言うと「それだけ」でした。。。

しかし、なぜこうなるのか、理屈がわかりません。。。オプティマイズ直後って、キレイなBツリーで格納されているんじゃないの?



まとめ

  • 全件カウントは、オプティマイズ(ALTER TABLE table_name ENGINE INNODB」してないほうが、パラレル化の恩恵を受けられる。
  • 理由はわからない。。。


AUTO INCREMENTの欠番の件といい、オプティマイズ後のサイズの件といい、今回のパラレル全件検索の件といい、MySQLの中の人に聞きたい。。。


で、次回こそ、パーティショニングへの効果を測りたいと思います。
今回の検証結果を踏まえて、過剰に?オプティマイズしてない状態で試したいと思います。

いや、すでに少し試している感じでは、一応効いているっぽいんだけど。


その他、「こんな観点で違いがあるか見てほしい」というのがあったら、やってみたいと思いますので、ブコメなりツイッターなりSlack(MySQL Casual)なりでリクエスト貰えれば、できるだけチャレンジシてみたいと思います。

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

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