と、
で、立て続けに検証に失敗したAsynchronous Key Prefetch（AKP）ですが、ようやく検証に成功したので結果を記しておきます。
1. 過去の検証でミスっていた原因
過去にデータ量が多い／バッファキャッシュサイズが大きいプロダクト環境で性能テストをした際、**「再起動直後は再起動前に比べてクエリの実行が遅くなった」という経験をしていたために、「データを多く用意しておけば再起動でもバッファキャッシュのウォームアップ完了前にテストクエリを投げられるだろう」**と安易に考えたのが原因でした（SHOW ENGINE INNODB STATUSをしっかり見ていなかった）。
実際、以前のテストデータ（記事に書いた時からさらに増量したもの）を使って、
のテストをしてみた際、少なくとも用意したデータ量では**「再起動直後にまず間違いなくウォームアップは完了する」**ことがわかり、判断の誤りに気づきました。
※テストを速く回そうとしてミスりました。
今回、レプリカを用意して、ファイルオーバーを繰り返すことにより、レプリカ側になったインスタンスで確実にバッファキャッシュがクリアされていることを確認したので、あらためて検証してみました。
2. 今回の検証
↑に記したことと重複しますが、
- プライマリインスタンス（Writer）／レプリカインスタンス（Reader）でクラスタを形成
- フェイルオーバーでレプリカ側になったインスタンスで、以下の3つのテストを実施
- デフォルト設定（非AKPで非BKA=Batched Key Access）でクエリ実行
- BKAのみ有効にしてクエリ実行
- AKPを有効にしてクエリ実行
- 結果として、↑の3通りを2回ずつ（各々のインスタンスで）実行
- 使用するテーブル・データ等はその2で使用したものとほぼ同じ
各々のインスタンスで結果のばらつきが小さかったので、以下、片側のインスタンスの結果だけ記載します。
2-1. デフォルト（非AKP・非BKA）
まず、フェイルオーバーでレプリカ側になったところからスタートです。
デフォルト（非AKP・非BKA）
mysql> USE akptest2;
No connection. Trying to reconnect...
Connection id: 4
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SELECT s.member_id memb, SUM(s.total_value) tval FROM dept d, member m, sales s WHERE d.dept_id = m.dept_id AND m.member_id = s.member_id AND d.dept_name = ' 部門015' GROUP BY memb HAVING tval > (SELECT SUM(s2.total_value) * 0.0007 FROM dept d2, member m2, sales s2 WHERE d2.dept_id = m2.dept_id AND m2.member_id = s2.member_id AND d2.dept_name = '部門015') ORDER BY tval DESC;
+-------+---------+
| memb | tval |
+-------+---------+
| 28942 | 1530300 |
（中略）
| 70092 | 1176300 |
+-------+---------+
41 rows in set (26.43 sec)
mysql> EXPLAIN EXTENDED SELECT s.member_id memb, SUM(s.total_value) tval FROM dept d, member m, sales s WHERE d.dept_id = m.dept_id AND m.member_id = s.member_id AND d.dept_name = '部門015' GROUP BY memb HAVING tval > (SELECT SUM(s2.total_value) * 0.0007 FROM dept d2, member m2, sales s2 WHERE d2.dept_id = m2.dept_id AND m2.member_id = s2.member_id AND d2.dept_name = '部門015') ORDER BY tval DESC;
+----+-------------+-------+------+-----------------+-----------+---------+-----------------------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+-----------------+-----------+---------+-----------------------+------+----------+----------------------------------------------+
| 1 | PRIMARY | d | ALL | PRIMARY | NULL | NULL | NULL | 30 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | m | ref | PRIMARY,dept_id | dept_id | 4 | akptest2.d.dept_id | 1385 | 100.00 | Using index |
| 1 | PRIMARY | s | ref | member_id | member_id | 4 | akptest2.m.member_id | 5 | 100.00 | NULL |
| 2 | SUBQUERY | d2 | ALL | PRIMARY | NULL | NULL | NULL | 30 | 100.00 | Using where |
| 2 | SUBQUERY | m2 | ref | PRIMARY,dept_id | dept_id | 4 | akptest2.d2.dept_id | 1385 | 100.00 | Using index |
| 2 | SUBQUERY | s2 | ref | member_id | member_id | 4 | akptest2.m2.member_id | 5 | 100.00 | NULL |
+----+-------------+-------+------+-----------------+-----------+---------+-----------------------+------+----------+----------------------------------------------+
6 rows in set, 1 warning (0.02 sec)
遅いです。
2-2. BKAのみ（非AKP）
フェイルオーバーを2回繰り返し、再びレプリカ側にします。
※2回のフェイルオーバーの間にもう一方のインスタンスでデフォルト設定でテストを行い、2-1. とほぼ同じ結果であることを確認。以降同じ。
BKA（非AKP）
mysql> USE akptest2;
No connection. Trying to reconnect...
Connection id: 4
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SET @@session.aurora_use_key_prefetch=off;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @@session.optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT s.member_id memb, SUM(s.total_value) tval FROM dept d, member m, sales s WHERE d.dept_id = m.dept_id AND m.member_id = s.member_id AND d.dept_name = '
部門015' GROUP BY memb HAVING tval > (SELECT SUM(s2.total_value) * 0.0007 FROM dept d2, member m2, sales s2 WHERE d2.dept_id = m2.dept_id AND m2.member_id = s2.membe
r_id AND d2.dept_name = '部門015') ORDER BY tval DESC;
+-------+---------+
| memb | tval |
+-------+---------+
| 28942 | 1530300 |
（中略）
| 70092 | 1176300 |
+-------+---------+
41 rows in set (25.95 sec)
mysql> EXPLAIN EXTENDED SELECT s.member_id memb, SUM(s.total_value) tval FROM dept d, member m, sales s WHERE d.dept_id = m.dept_id AND m.member_id = s.member_id AND d.dept_name = '部門015' GROUP BY memb HAVING tval > (SELECT SUM(s2.total_value) * 0.0007 FROM dept d2, member m2, sales s2 WHERE d2.dept_id = m2.dept_id AND m2.member_id = s2.member_id AND d2.dept_name = '部門015') ORDER BY tval DESC;
+----+-------------+-------+------+-----------------+-----------+---------+-----------------------+------+----------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+-----------------+-----------+---------+-----------------------+------+----------+----------------------------------------------+
| 1 | PRIMARY | d | ALL | PRIMARY | NULL | NULL | NULL | 30 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | m | ref | PRIMARY,dept_id | dept_id | 4 | akptest2.d.dept_id | 1385 | 100.00 | Using index |
| 1 | PRIMARY | s | ref | member_id | member_id | 4 | akptest2.m.member_id | 5 | 100.00 | Using join buffer (Batched Key Access) |
| 2 | SUBQUERY | d2 | ALL | PRIMARY | NULL | NULL | NULL | 30 | 100.00 | Using where |
| 2 | SUBQUERY | m2 | ref | PRIMARY,dept_id | dept_id | 4 | akptest2.d2.dept_id | 1385 | 100.00 | Using index |
| 2 | SUBQUERY | s2 | ref | member_id | member_id | 4 | akptest2.m2.member_id | 5 | 100.00 | Using join buffer (Batched Key Access) |
+----+-------------+-------+------+-----------------+-----------+---------+-----------------------+------+----------+----------------------------------------------+
6 rows in set, 1 warning (0.00 sec)
デフォルトより少し速くなりました（もう一方のインスタンスでは少しだけ遅くなりました）。
2-3. AKP
フェイルオーバー×2の後、最後にAKPを試します。
AKP
mysql> USE akptest2;
No connection. Trying to reconnect...
Connection id: 4
Current database: *** NONE ***
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> SET @@session.aurora_use_key_prefetch=on;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @@session.optimizer_switch='mrr=on,mrr_cost_based=off,batched_key_access=on';
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT s.member_id memb, SUM(s.total_value) tval FROM dept d, member m, sales s WHERE d.dept_id = m.dept_id AND m.member_id = s.member_id AND d.dept_name = '
部門015' GROUP BY memb HAVING tval > (SELECT SUM(s2.total_value) * 0.0007 FROM dept d2, member m2, sales s2 WHERE d2.dept_id = m2.dept_id AND m2.member_id = s2.membe
r_id AND d2.dept_name = '部門015') ORDER BY tval DESC;
+-------+---------+
| memb | tval |
+-------+---------+
| 28942 | 1530300 |
（中略）
| 70092 | 1176300 |
+-------+---------+
41 rows in set (6.02 sec)
mysql> EXPLAIN EXTENDED SELECT s.member_id memb, SUM(s.total_value) tval FROM dept d, member m, sales s WHERE d.dept_id = m.dept_id AND m.member_id = s.member_id AND d.dept_name = '部門015' GROUP BY memb HAVING tval > (SELECT SUM(s2.total_value) * 0.0007 FROM dept d2, member m2, sales s2 WHERE d2.dept_id = m2.dept_id AND m2.member_id = s2.member_id AND d2.dept_name = '部門015') ORDER BY tval DESC;
+----+-------------+-------+------+-----------------+-----------+---------+-----------------------+------+----------+-------------------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------+-----------------+-----------+---------+-----------------------+------+----------+-------------------------------------------------------------+
| 1 | PRIMARY | d | ALL | PRIMARY | NULL | NULL | NULL | 30 | 100.00 | Using where; Using temporary; Using filesort |
| 1 | PRIMARY | m | ref | PRIMARY,dept_id | dept_id | 4 | akptest2.d.dept_id | 1385 | 100.00 | Using index |
| 1 | PRIMARY | s | ref | member_id | member_id | 4 | akptest2.m.member_id | 5 | 100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
| 2 | SUBQUERY | d2 | ALL | PRIMARY | NULL | NULL | NULL | 30 | 100.00 | Using where |
| 2 | SUBQUERY | m2 | ref | PRIMARY,dept_id | dept_id | 4 | akptest2.d2.dept_id | 1385 | 100.00 | Using index |
| 2 | SUBQUERY | s2 | ref | member_id | member_id | 4 | akptest2.m2.member_id | 5 | 100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+-------+------+-----------------+-----------+---------+-----------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.00 sec)
4倍以上速くなりました！（バッファキャッシュに載っている場合は1秒掛からないので、それと比較すると格段に遅いですが）。
※もう一方のインスタンスでも同様でした。
3. まとめ
- 正しいテストケースでは、しっかり効果がありました（すみません…）。
- 曖昧な経験則はあてにせず、きちんと検証！
- ただ、「バッファキャッシュに載っている状態で実行するとデフォルトより遅くなる」点は間違いないのが悩ましいところ…。クエリ発行数が少なく（バッファキャッシュに載っていない可能性がそこそこ高く）、かつデータ量が極端に多くない場合は遅くなったとしても許容範囲なので、使ってみてもいいかもしれない。