MySQL 8.0.14でSELECT COUNT(*)が加速する！- 「innodb_parallel_read_threads」検証その１

それは突然やってきた

MySQL 8.0.14がGAされました。

dev.mysql.com

まあ、MySQLは結構な頻度でリリースがありますし、「GAとはなんぞや」との名言が生まれる程度に、マイナーリリースでも機能が増える、パラメータが増える、既存パラメータのデフォルト値が変わる、といったことが発生するかわいいヤツです。

まあ、あとでリリースノート読んでみるか、と思いつつ、ビールを飲みながらYoutube垂れ流してボケーっとしているところに、新しいものが出てくるとリリースノートとソースコードを読み漁る某APIの人のツイートが深夜に流れてきて、ふと気になったのが、今回のテーマである「innodb_parallel_read_threads」です。

InnoDB: InnoDB now supports parallel clustered index reads, which can improve CHECK TABLE performance. This feature does not apply to secondary index scans. The innodb_parallel_read_threads session variable must be set to a value greater than 1 for parallel clustered index reads
— yoku0825 (@yoku0825) January 21, 2019

テーブルスキャンがパラレルに…
— yoku0825 (@yoku0825) January 21, 2019

正確な機能名はわかりませんが、「parallel clustered index reads」という記述があります。
端的にいうと、「インデックスを並列読み込み」して加速する機能です。。

リリースノート、公式ドキュメントの記述ともに「CHECK TABLEの性能を改善する」って書いてあるんです。

でも自分、CHECK TABLEなんて使わない。（暴言？）

なんてスルーしかかってました。

よく読んでみると、「This feature does not apply to secondary index scans.」って書いてあります。
つまり、セカンダリインデックスには効かないよ＝プライマリインデックスのクラスタに効くよ、って言ってます。

MySQL InnoDBの基本として、「テーブルの実体は、PKによるBツリーのクラスタインデックスである」というのがあります。

つまり、PKインデックスクラスタのスキャン性能改善は、データアクセス速度の向上に直結する話なんです。

そして、MySQL InnoDBの基本その２として「SELECT COUNT(*)は、テーブルフルスキャン（PKインデックスのフルスキャン）である」というのがあります。
正確な件数を静的に保持してないので、COUNT()で毎回数え上げてるんです。（information_schema.tablesのtable_rows列の値は「概算値」。）

なので、遅いんです。遅くなるんです。件数増えれば増えるほどに。

そう、この３つの点「テーブルの実体は、PKによるBツリーのクラスタインデックスである」「SELECT COUNT(*)は、テーブルフルスキャン（PKインデックスのフルスキャン）であり、遅い。」「新機能 parallel clustered index readsが、PKインデックスのスキャンを並列化して加速する」が、私の頭の中で重なりました。

「あれ、CHECK TABLEじゃなくて、SELECT COUNT(*)も速くなるんじゃね？コレって！」

思い立ったら即ツイート、じゃなくて、検証環境を作り始めてました（実際ツイートもしてるけど）。

さっそく検証

とりあえずやりたいことは以下の通り。

「innodb_parallel_read_threads」で、SELECT COUNT(*)は過去のバージョンより速くなるか
並列度の変更が可能だが、どの程度の並列度までイケるか
「innodb_parallel_read_threads」は、フルスキャン以外のPKスキャンに効くか
「innodb_parallel_read_threads」は、パーティショニングしたテーブルに効くか
COUNT以外のデータアクセスに、影響はあるか

今回は１番と２番の検証記録です。

結論だけ知りたい人は、タイトルだけ読んでどうぞ。（違

環境

足回り

まずは、いつものVirtualBox環境です。

ホストマシン：Core i7（7th Gen）＋メモリ16GB ＋ SSD 1TB ＋ Windows10 Pro のノートPC
ゲストマシン：2vCPU ＋メモリ2GB ＋ 20GB 仮想ディスク（vdi）＋ CentOS 6.10（Minimalインストール）

ゲストマシンは、OSインストール直後に以下のことだけやってます。

yum -y update
service iptables stop
service ip6table stop
chkconfig iptables off
chkconfig ip6tables off
vi /etc/selinux/config
 SELINUX=disabled に書き換え

後述する３バージョンを独立して検証するため、OS設定まで全く同じゲストマシンを３つ用意しました。

検証するMySQLのバージョン

以下３つを比較します。

MySQL 5.7.25（2019/01/21 GA）
MySQL 8.0.13（2018/10/22 GA）
MySQL 8.0.14（2019/01/21 GA）

yumからインストールです。

MySQLの設定

my.cnfは、以下３つ以外はインストールデフォルトのままです。

バージョン間でデフォルトパラメータ自体の違いが微妙にありますが、全部あわせてられないので、あくまで「デフォルト」を使ってます。

その細かい差異は、こちらをどうぞ。（とみたさん、めちゃめちゃ助かってます！）
tmtm.github.io

追記設定したのは、キャッシュの影響を排除するためのものです。

「innodb_buffer_pool_dump_at_shutdown」と「innodb_buffer_pool_load_at_startup」は「mysqld再起動により、バッファプール上のデータを追い出した状態からの速度を確認するため」です。
MySQL 5.7.7以降、バッファプールに乗ったデータが再起動しても飛ばない良い子モード有効がデフォルトになってるんです。

「innodb_flush_method=O_DIRECT」は、OSのファイルシステムレベルでのキャッシュを使わない設定ですね。
最初、これ入れ忘れてました。
再起動後してバッファからデータを追い出したつもりなのに、並列度を下げた1回目の速度が速すぎておかしいなー、と思ってたら、コイツでした。

innodb_buffer_pool_dump_at_shutdown=OFF
innodb_buffer_pool_load_at_startup=OFF
innodb_flush_method=O_DIRECT

なお、今回検証するパラメータ「innodb_parallel_read_threads」って、セッションレベルで動的に変えられるんですね。使いやすい！検証が捗る！

用意したデータ

レコード件数：2^24＝16,777,216件
ibdファイルサイズ：1.9GB（ALTER TABLE ENGINE INNODB未実行）

テーブルはこんな感じです。

CREATE TABLE item (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(10),
  description VARCHAR(30),
  price INT UNSIGNED,
  created_at DATETIME
);

id列のみでレコード増幅させたあと、Updateでname,description,price,created_atの各列に機械的ながらもランダムな値を入れてます。
AUTO_INCREMENTの欠番をなくすために、ひと手間加えてます。

検証結果

単純な「SELECT COUNT(*) FROM テーブル」は、以下の通りとなりました。

バージョン	並列度	1回目（秒）	2回目（秒）	3回目（秒）
8.0.14	32	7.16	6.88	6.93
8.0.14	16	7.43	6.93	7.15
8.0.14	8	8.81	8.12	8.12
8.0.14	4	13.16	12.16	12.55
8.0.14	2	19.65	18.67	18.93
8.0.14	1	33.07	30.18	29.51
8.0.13	1	36.79	31.6	30.28
5.7.25	1	32.94	33.16	33.03

並列度１の状態における、バージョン間の差異は、誤差レベルでしょう。
そして、並列処理を有効にしたMySQL 8.0.14、確実に速くなってます。

今回の環境「2vCPU」では、8並列程度で性能が頭打ちになっていますが、これはCPU増やすともっと伸びそうです。

ただし、CPU起因の部分もそうですが、その他にも並列度の有効性に影響する条件が、ドキュメントに書いてあります。

The actual number of threads used to perform a parallel clustered index read is determined by the innodb_parallel_read_threads setting or the number of index subtrees to scan, whichever is smaller.
MySQL :: MySQL 8.0 Reference Manual :: 15.13 InnoDB Startup Options and System Variables

「パラメータの値か、インデックスのサブツリーの数の、どちらか小さい方」ということです。

ツリーのルートから、ブランチ単位で担当を分けて並列に処理していく、という挙動のようですね。
なので、ブランチが少ない＝データが少ないうちは、並列設定を引き上げても、担当を割り振りきれない、と。そこは現実に即してよろしくやっておくよ、と。

ソースレベルでさらっと解説してみたいですが、まだそのレベルに達していないので、「誰か」に期待。

なお、このSELECT COUNT(*)の速度だけに限って言うと、
innodb_flush_method=O_DIRECT
を入れない（Unix系のデフォルトは「fsync」）状態でOSファイルシステムのキャッシュに乗ってしまっている状態の方が速いです。。。

そのへんの話は、MySQL 5.5ベースの記述ですが、SH2さんのこちらのエントリで解説されています。
sh2.hatenablog.jp

MySQLのバッファ（キャッシュ）と、OSファイルシステムのキャッシュと2段階で持つことの弊害もありますので、そのへんはよく考えて設計しましょう。