MySQL 8.0 では、テーブル全件に対するSELECT COUNT(*)がパラレルスキャンによって高速化されましたが、MySQL 5.7 以前よりも遅くなるケースが発生したので、少し調べてみました。

タイトルの元ネタ

テストの内容

インスタンスを再起動してバッファプールをクリアした状態から、3 つあるテーブルの全件SELECT COUNT(*)を続けて実行し、実行時間を計測
- ①〜④ ではテストデータのサイズ＞バッファプールのサイズ
- ⑤ ではテストデータのサイズ＜バッファプールサイズ
テストケースは 5 つ

テスト環境

AWS の RDS for MySQL 8.0.28
インスタンスタイプはテスト ①〜④ が db.r6g.large（2vCPU・Mem 16GiB）、テスト ⑤ が db.r6g.2xlarge（8vCPU・Mem 64GiB）
ストレージは gp2 の 700GB
Single-AZ
③ 以外のパラメータグループ（設定）は RDS のデフォルトを使用

テストデータ

以下の SQL 文で生成しました。実データのサイズは 16 〜 17GiB 程度ですが、データページ（セカンダリインデックス含む）全体では 20GiB 以上になりました。

データ生成

データ生成

CREATE DATABASE count_test;
USE count_test;

CREATE TABLE test_short (id INT PRIMARY KEY, val1 INT NOT NULL, val2 INT NOT NULL);
CREATE TABLE test_long (id INT PRIMARY KEY, val1 INT NOT NULL, val2 INT NOT NULL, str1 TEXT);
CREATE TABLE test_long_uk (id INT PRIMARY KEY, val1 INT NOT NULL, val2 INT NOT NULL, str1 TEXT, UNIQUE (val1));

INSERT INTO test_short SET id = 1, val1 = 1, val2 = FLOOR(RAND() * 10);
INSERT INTO test_short SET id = 2, val1 = 2, val2 = FLOOR(RAND() * 10);
（中略）
INSERT INTO test_short SET id = 10, val1 = 10, val2 = FLOOR(RAND() * 10);

INSERT INTO test_long SELECT id, val1, val2, REPEAT('a', FLOOR(100 + RAND() * 9901)) FROM test_short;
INSERT INTO test_long_uk SELECT * FROM test_long;

INSERT INTO test_short SELECT id + 10, val1 + 10, val2 FROM test_short ORDER BY id;
INSERT INTO test_long SELECT id + 10, val1 + 10, val2, str1 FROM test_long ORDER BY id;
INSERT INTO test_long_uk SELECT id + 10, val1 + 10, val2, str1 FROM test_long_uk ORDER BY id;

INSERT INTO test_short SELECT id + 20, val1 + 20, val2 FROM test_short ORDER BY id;
INSERT INTO test_long SELECT id + 20, val1 + 20, val2, str1 FROM test_long ORDER BY id;
INSERT INTO test_long_uk SELECT id + 20, val1 + 20, val2, str1 FROM test_long_uk ORDER BY id;
（中略）
INSERT INTO test_short SELECT id + 1310720, val1 + 1310720, val2 FROM test_short ORDER BY id;
INSERT INTO test_long SELECT id + 1310720, val1 + 1310720, val2, str1 FROM test_long ORDER BY id;
INSERT INTO test_long_uk SELECT id + 1310720, val1 + 1310720, val2, str1 FROM test_long_uk ORDER BY id;

結果（単位：秒）

結果表（全体）

実行回数	①short	①long	①long_uk	②short	②long_uk	②long	③short	③long	③long_uk	④short	④long	④long_uk	⑤short	⑤long	⑤long_uk
1 回目	0.93	72.30	62.56	0.40	68.50	58.21	0.42	38.28	36.10	0.81	51.94	58.15	0.84	66.37	63.46
2 回目	0.08	0.33	45.13	0.08	0.34	46.88	0.34	0.85	77.66	0.64	80.50	77.20	0.04	0.18	0.17
3 回目	0.09	0.36	46.65	0.08	0.34	46.70	0.34	0.84	73.98	0.64	47.61	76.93	0.04	0.17	0.17
4 回目	0.08	0.32	52.16	0.09	0.33	49.13	0.34	0.85	17.55	0.63	22.79	44.45	0.04	0.18	0.17
5 回目	0.09	0.33	43.03	0.08	0.33	49.49	0.34	0.84	10.92	0.63	14.38	43.64	0.04	0.18	0.18
6 回目	0.08	0.35	40.14	0.09	0.33	51.86	0.34	0.84	10.23	0.63	14.28	44.40	0.04	0.17	0.17
7 回目	0.08	0.34	42.18	0.08	0.33	51.62	0.34	0.84	8.96	0.63	14.18	44.06	0.05	0.17	0.17
8 回目	0.08	0.33	43.05	0.09	0.32	57.11	0.34	0.85	1.32	0.64	14.26	44.60	0.04	0.17	0.17
9 回目	0.10	0.33	42.12	0.08	0.33	61.55	0.34	0.83	1.01	0.63	14.42	53.88	0.05	0.17	0.17
10 回目	0.11	0.33	39.24	0.09	0.33	55.76	0.34	0.84	1.00	0.63	14.27	51.60	0.04	0.18	0.18

全部まとめて見るのは難しいので順に見ていきます。

パラレルスキャンかつ「テストデータのサイズ＞バッファプールのサイズ」の場合

実行回数	①short	①long	①long_uk	②short	②long_uk	②long
1 回目	0.93	72.30	62.56	0.40	68.50	58.21
2 回目	0.08	0.33	45.13	0.08	0.34	46.88
3 回目	0.09	0.36	46.65	0.08	0.34	46.70
4 回目	0.08	0.32	52.16	0.09	0.33	49.13
5 回目	0.09	0.33	43.03	0.08	0.33	49.49
6 回目	0.08	0.35	40.14	0.09	0.33	51.86
7 回目	0.08	0.34	42.18	0.08	0.33	51.62
8 回目	0.08	0.33	43.05	0.09	0.32	57.11
9 回目	0.10	0.33	42.12	0.08	0.33	61.55
10 回目	0.11	0.33	39.24	0.09	0.33	55.76

① では、test_shortおよびtest_longについては 1 回目だけが遅く、2 回目以降が速い（かつ、ほぼ同じ所要時間）です。所要時間より 2 回目以降のSELECT COUNT(*)がバッファプールからの読み出しなのが分かります。

ところが、test_long_ukについては 2 回目にわずかに速くなったもののその後は横ばいでそれ以上高速化していません。CloudWatch のモニタリングメトリクスで確認してみたところ、2 回目以降もストレージからの読み出しが発生しており、

test_long_ukのデータページを読み出す途中でバッファプールがいっぱいになった
SELECT COUNT(*)の処理でtest_long_ukをバッファプールに載せる処理に何らかの問題が生じているので 2 回目以降もストレージからの読み出しが必要になっている

ことが推察できます。

続いて ② で順番を変えてtest_longより前にtest_long_ukをSELECT COUNT(*)したところ、今度はtest_long_ukが 2 回目以降も高速化した（バッファプールからの読み出しになった）一方で、後から実行したtest_longのほうで 2 回目以降が十分に高速化しない事象が発生しました。
やはりtest_long・test_long_ukの構造に起因する問題ではなく、バッファプールがいっぱいになるタイミング以降に問題が生じていると見て間違いなさそうです。

どうやら、 MySQL 8.0 ではテーブル全件に対するSELECT COUNT(*)の処理で、バッファプールがいっぱいになり古いデータページを追い出す必要がある状況下の処理に問題を抱えている様子です。

なお、test_long_ukにはセカンダリインデックスがあります。MySQL 5.7 以前の動作ではセカンダリインデックスがある場合はセカンダリインデックスを全件スキャンする仕様で、 MySQL 8.0 でもEXPLAINで見る限りではプライマリインデックスの全件スキャン（＝パラレルスキャンの対象）ではなくセカンダリインデックスの全件スキャン（＝パラレルスキャンの対象外）になっていましたが、セカンダリインデックスの有無に関わらず、同様の結果になりました。

2020/07/23 追記：RDS MySQL 5.7.37（db.r5.large）での ①・② の結果

太字が MySQL 8.0.28 より速かった回です。

実行回数	①short	①long	①long_uk	②short	②long_uk	②long
1 回目	0.78	368.93	0.39	0.60	1.10	327.33
2 回目	0.40	0.83	0.38	0.41	0.38	0.84
3 回目	0.42	0.83	0.38	0.40	0.41	0.85
4 回目	0.41	0.83	0.38	0.40	0.38	0.84
5 回目	0.41	0.83	0.38	0.41	0.38	0.85
6 回目	0.42	0.87	0.38	0.41	0.39	0.83
7 回目	0.40	0.83	0.38	0.42	0.37	0.83
8 回目	0.40	0.83	0.38	0.40	0.38	0.83
9 回目	0.41	0.84	0.37	0.40	0.37	0.83
10 回目	0.40	0.87	0.38	0.40	0.38	0.83

test_long_ukについては主キーのクラスタインデックス（＝テーブルデータ本体）よりはるかに小さいセカンダリインデックスをスキャンするだけで済むため、SELECT COUNT(*)の実行順に関わらず test_shortとほぼ同等の所要時間 になっています。

パラレルスキャンをやめてみる

前述のとおり「セカンダリインデックスがあるtest_long_ukについても後からSELECT COUNT(*)すると遅い」時点ですでに怪しい雰囲気はあるのですが、念のためパラレルスキャンをやめるとどうなるのかを確かめてみます。

パラレルスキャンをやめる方法として、

③：innodb_parallel_read_threadsを1にする
④：innodb_parallel_read_threadsを変えずにWHERE id > 0を追加する

の 2 とおりで試してみた結果が以下の表です。

実行回数	③short	③long	③long_uk	④short	④long	④long_uk
1 回目	0.42	38.28	36.10	0.81	51.94	58.15
2 回目	0.34	0.85	77.66	0.64	80.50	77.20
3 回目	0.34	0.84	73.98	0.64	47.61	76.93
4 回目	0.34	0.85	17.55	0.63	22.79	44.45
5 回目	0.34	0.84	10.92	0.63	14.38	43.64
6 回目	0.34	0.84	10.23	0.63	14.28	44.40
7 回目	0.34	0.84	8.96	0.63	14.18	44.06
8 回目	0.34	0.85	1.32	0.64	14.26	44.60
9 回目	0.34	0.83	1.01	0.63	14.42	53.88
10 回目	0.34	0.84	1.00	0.63	14.27	51.60

微妙な結果になりました。

③ についてはtest_long_ukの 8 回目のSELECT COUNT(*)からバッファプールを上手く使える状態になっていますが、2 回目と 3 回目が 1 回目よりも遅くなっています。

④ についてはtest_longも遅くなってしまいましたし、test_long_ukの速度が改善することはありませんでした。

パラレルスキャンかつ「テストデータのサイズ＜バッファプールのサイズ」の場合

最後に、インスタンスタイプを大きくして、バッファプールを十分な容量に増やしてみました。

実行回数	⑤short	⑤long	⑤long_uk
1 回目	0.84	66.37	*63.46*
2 回目	0.04	0.18	0.17
3 回目	0.04	0.17	0.17
4 回目	0.04	0.18	0.17
5 回目	0.04	0.18	0.18
6 回目	0.04	0.17	0.17
7 回目	0.05	0.17	0.17
8 回目	0.04	0.17	0.17
9 回目	0.05	0.17	0.17
10 回目	0.04	0.18	0.18

必要なデータページが全部バッファプールに載る場合は何の問題もありませんでした（斜体の回は MySQL 5.7 の方が圧倒的に高速ですが）。

現時点での結論

「全データのサイズ＜バッファプールのサイズ」で運用するのは現実的ではないので、有効な回避策が見つからず困っています。

「10 GiB を超えるようなテーブルの全件SELECT COUNT(*)は取るな」と言われればそのとおりなのですが。

余談

パラレルスキャンのSELECT COUNT(*)については以前からいくつかのバグが見つかって修正されています。

MySQL 8.0.24 リリースノートより

InnoDB: On Windows, stalls were caused by concurrent SELECT COUNT(*) queries where the number of parallel read threads exceeded the number of machine cores. (Bug #32224707, Bug #101789)

MySQL 8.0.26 リリースノートより

InnoDB: Stalls were caused by concurrent SELECT COUNT(*) queries where the number of parallel read threads exceeded the number of machine cores. A patch for this issue was provided for Windows builds in MySQL 8.0.24. The MySQL 8.0.26 patch addresses the same issue on other affected platforms. (Bug #32678019)

ただし、まだ Fix されていないバグも残っているようです（2022/07/22 現在）。

https://bugs.mysql.com/bug.php?id=99717

脚注

TEXTを含まないテーブル ↩︎
TEXTを含む大容量のテーブル（ページサイズ合計で 10GiB 超） ↩︎
TEXTを含みセカンダリインデックスを持つ大容量のテーブル（同上） ↩︎

Discussion