MySQL 8.0.14でSELECT COUNT(*)は、バッファに載っていても載っていなくても加速する！ - 「innodb_parallel_read_threads」検証その５

今回は軽め？のテーマと思ったら、やりながら色々疑問が湧いてきて、結局データ準備にかなり時間のかかる検証になってしまいました。

今まで、サーバーパラメーターは、ほぼデフォルトで試験をしてきていましたので、innodb_buffer_pool_sizeは128MBでした。
それに対して、用意しているテーブルのデータは1,700万件弱、ibdファイルで1.4～1.9GBに相当していますので、バッファには乗り切らない状態でした。

過去の検証で3回ずつ計測しても、対して差がでいないのは、バッファプールの恩恵をほぼ享受できていないから、と見て良いかと思います。

なので、今回は、メモリに乗り切った状態でも効果がでるか？というところを検証します。

これまでと同じ仮想マシンです。

ゲストマシンにメモリを4GB割り当ててあるので、innodb_buffer_pool_sizeの値として、ベストプラクティスな「物理メモリの75％」に相当する3GBで設定しています。

違いはそれだけ。

パーティショニングは切りません。1テーブル1パーティションです。
データ件数は16,777,216件、これも今までの検証と同じです。

初回同様、シンプルに「innodb_parallel_read_threads」を変えて計測します。

条件変更の都度、以下のことを行います。

mysqldの再起動をかけてバッファプールの内容をクリア
「information_schema.innodb_buffer_page」を覗き込んで、SELECT COUNT(*)するテーブルのデータがBuffer_poolに載っていないことを確認。
ステータス変数「Innodb_buffer_pool_pages_dirty」を確認して、バックグラウンドでの動機処理等が残っていないことを確認。
「innodb_parallel_read_threads」を変更
SELECT COUNT(*)による全件カウントを実行

なお、1回目を実行すると、information_schema.innodb_buffer_pageで対象テーブルの合計が実件数／サイズとほぼ同じになっていることを確認しています。

Innodb_buffer_pool_reads（バッファ上のデータでは足りなくてストレージからの読み込みが発生した回数）も、1回目で一気にカウントが増え、2回目、3回目は値が変化しないことを確認しています。

データパターンの条件は以下の通り。

条件	PK列の欠番	オプティマイズ	ibdファイルサイズ
1	欠番あり	未	1971322880
2	欠番なし	未	1971322880
3	欠番あり	実施直後	1639972864
4	欠番なし	実施直後	1635778560

なんてわかりやすい結果なんでしょう。

条件	innodb_parallel_read_threads	1回目	2回目	3回目
1	16	6.87	0.98	0.96
1	8	8.12	0.95	0.99
1	4	12.56	0.99	0.91
1	1	29.14	2.65	2.46
2	16	11.52	0.92	0.97
2	8	8.47	1.01	0.99
2	4	12.39	0.95	0.95
2	1	31.31	2.56	2.51
3	16	26.54	0.91	0.94
3	8	26.75	0.96	0.95
3	4	26.69	0.87	0.94
3	1	28.21	2.46	2.46
4	16	25.07	1.01	0.92
4	8	26.47	0.91	0.86
4	4	26.29	0.93	0.85
4	1	28.02	2.48	2.51

並列化なしと並列化ありでは、1回目（メモリの載っていないケース）と、2回目／3回目（メモリに載っているケース）ともに、効果が現れていることが確認できます。
メモリに載ってしまうと、並列度を上げても、それほどリニアには性能向上しないですね。

で、前から気になっている「オプティマイズ（ALTER TABLE ENGINE INNODB）」した後が遅い件、やっぱり出ますね。
メモリに載った後は気にならないレベルですが、ファイルからの読み取りが異様に遅いです。

もうこれ「innodb_parallel_read_threads」関係ない話だよね。
まさかこの機能が追加された反動なんてことがないよね。（フラグ

で、確認してみた結果がコレ。データ用意するのに時間がかかった。。。

条件	ibd file size	1回目	2回目	3回目
5.7.25ｰ1	1979711488	37.75	4.64	4.61
5.7.25ｰ2	1979711488	32.37	4.61	4.84
5.7.25ｰ3	1644167168	30.59	5.29	4.89
5.7.25ｰ4	1644167168	30.81	4.97	4.61
8.0.13ｰ1	1971322880	30.74	2.51	2.51
8.0.13ｰ2	1971322880	30.44	2.51	2.54
8.0.13ｰ3	1639972864	28.29	2.56	2.52
8.0.13ｰ4	1635778560	28.06	2.55	2.56