Amazon Aurora（MySQL互換）でAsynchronous Key Prefetchその2／有効パターンを探って返り討ちに遭った

先の記事、

• Amazon Aurora（MySQL-compatible edition）で新しくサポートされたAsynchronous Key Prefetchを試してみる

で、単純な結合でAsynchronous Key Prefetch（AKP）を試してみたところ有効に働かなかった（実行計画が変わり、かえって遅くなった）ので、

• Working with Asynchronous Key Prefetch in Amazon Aurora（Best Practices with Amazon Aurora MySQL）

で示されたSQL例に近い（と思う）状況を作って試してみました。

※タイトルでわかる通り、結局、有効に働かず…。

11/25追記：
確実にバッファキャッシュをクリアして試したところ、効果を確認しました。

• Amazon Aurora（MySQL互換）でAsynchronous Key Prefetchその3／解決編

1. テーブル・データとSQL

先のベストプラクティスに示されたSQLについて、細かい前提条件等は全く分かりませんが、部品か何かの供給者に関するデータを一定の条件で集計するもののようなので、そのまま使うのはイメージしづらいような気がします（と、勝手に思いました）。

というわけで、似たような集計パターンになるように、以下のようなテーブルとデータを用意しました。

• 部門テーブル：会員を担当する部門（担当地域別に存在するイメージ）→30レコード
• 会員テーブル：商品購入ユーザ→80,000レコード
• 販売テーブル：会員の商品購入額：取引単位で合計→1,000,000レコード

※テスト用なのでカラムは思い切り省略しています。

ここから、SQLで

• 「部門015」が担当する会員の購入額（総合計）を
• 購入額（総合計）が多いほうから降順で
• 但し、部門売上合計額の一定割合（0.07%）以下の会員を除いて

抽出します。

テーブル作成

mysql> CREATE DATABASE akptest2 CHARACTER SET utf8mb4;
Query OK, 1 row affected (0.01 sec)

mysql> USE akptest2;
Database changed
mysql> CREATE TABLE dept (dept_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, dept_name VARCHAR(40) NOT NULL) DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE member (member_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, member_name VARCHAR(40) NOT NULL, dept_id INT UNSIGNED NOT NULL, INDEX (dept_id, member_id)) DEFAULT CHARSET=
utf8mb4;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE sales (sales_id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY, member_id INT UNSIGNED NOT NULL, total_value INT UNSIGNED NOT NULL, note VARCHAR(200), INDEX (member_id)) DEFAULT CHARSET=utf8mb4;
Query OK, 0 rows affected (0.03 sec)

データ内容

mysql> SELECT * FROM dept LIMIT 3;
+---------+-----------+
| dept_id | dept_name |
+---------+-----------+
|       1 | 部門001   |
|       2 | 部門002   |
|       3 | 部門003   |
+---------+-----------+
3 rows in set (0.01 sec)

mysql> SELECT COUNT(*) FROM dept;
+----------+
| COUNT(*) |
+----------+
|       30 |
+----------+
1 row in set (0.00 sec)

mysql> SELECT * FROM member LIMIT 3;
+-----------+-------------+---------+
| member_id | member_name | dept_id |
+-----------+-------------+---------+
|         1 | 会員00001   |      14 |
|         2 | 会員00002   |      18 |
|         3 | 会員00003   |      15 |
+-----------+-------------+---------+
3 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM member;
+----------+
| COUNT(*) |
+----------+
|    80000 |
+----------+
1 row in set (0.01 sec)

mysql> SELECT * FROM sales LIMIT 3;
+----------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| sales_id | member_id | total_value | note                                                                                                                                                                          |
+----------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|        1 |     39636 |       82700 | 6020ec0f5f80acbe4dfd4d6c9fab4bdcb7af4692423ae00383150f85a535224b31bed76f42ebd7e448dfac86904c37b5cb6d787ce5d51d4f61a9c6089f60dc4bbeca7026dc22ddc91132a4eb5c8b49ea2b6ec4b721867 |
|        2 |     65875 |       67300 | ff3c14005f0abf06d1c4b1863efbb5e69821413e39b004f1a32142e746a46b9e7b2b903c79bb7f6dda3669cf6f6010e90333043780d79bad1006016be442b                                                 |
|        3 |     14251 |       40500 | c77545113e764e1f48436d5a93f0f94c17b182a7728d4abdc112056a6322098e3dbd8691271c07d989c52472ebcee2fd7864ce62ad576f1a5e72ac5029d9b91e5267dffef1a9f01a90971d                        |
+----------+-----------+-------------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.00 sec)

mysql> SELECT COUNT(*) FROM sales;
+----------+
| COUNT(*) |
+----------+
|  1000000 |
+----------+
1 row in set (0.15 sec)

抽出SQLのEXPLAIN（非AKPとAKPの比較）

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=on,batched_key_access=off';
Query OK, 0 rows affected (0.04 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  |    6 |   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 |    6 |   100.00 | NULL                                         |
+----+-------------+-------+------+-----------------+-----------+---------+-----------------------+------+----------+----------------------------------------------+
6 rows in set, 1 warning (0.00 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `akptest2`.`s`.`member_id` AS `memb`,sum(`akptest2`.`s`.`total_value`) AS `tval` from `akptest2`.`dept` `d` join `akptest2`.`member` `m` join `akptest2`.`sales` `s` where ((`akptest2`.`s`.`member_id` = `akptest2`.`m`.`member_id`) and (`akptest2`.`m`.`dept_id` = `akptest2`.`d`.`dept_id`) and (`akptest2`.`d`.`dept_name` = '部門015')) group by `memb` having (`tval` > (/* select#2 */ select (sum(`akptest2`.`s2`.`total_value`) * 0.0007) from `akptest2`.`dept` `d2` join `akptest2`.`member` `m2` join `akptest2`.`sales` `s2` where ((`akptest2`.`s2`.`member_id` = `akptest2`.`m2`.`member_id`) and (`akptest2`.`m2`.`dept_id` = `akptest2`.`d2`.`dept_id`) and (`akptest2`.`d2`.`dept_name` = '部門015')))) order by `tval` desc
1 row in set (0.00 sec)

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> 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  |    6 |   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 |    6 |   100.00 | Using join buffer (Batched Key Access with Key Prefetching) |
+----+-------------+-------+------+-----------------+-----------+---------+-----------------------+------+----------+-------------------------------------------------------------+
6 rows in set, 1 warning (0.01 sec)

mysql> SHOW WARNINGS\G
*************************** 1. row ***************************
  Level: Note
   Code: 1003
Message: /* select#1 */ select `akptest2`.`s`.`member_id` AS `memb`,sum(`akptest2`.`s`.`total_value`) AS `tval` from `akptest2`.`dept` `d` join `akptest2`.`member` `m` join `akptest2`.`sales` `s` where ((`akptest2`.`s`.`member_id` = `akptest2`.`m`.`member_id`) and (`akptest2`.`m`.`dept_id` = `akptest2`.`d`.`dept_id`) and (`akptest2`.`d`.`dept_name` = '部門015')) group by `memb` having (`tval` > (/* select#2 */ select (sum(`akptest2`.`s2`.`total_value`) * 0.0007) from `akptest2`.`dept` `d2` join `akptest2`.`member` `m2` join `akptest2`.`sales` `s2` where ((`akptest2`.`s2`.`member_id` = `akptest2`.`m2`.`member_id`) and (`akptest2`.`m2`.`dept_id` = `akptest2`.`d2`.`dept_id`) and (`akptest2`.`d2`.`dept_name` = '部門015')))) order by `tval` desc
1 row in set (0.00 sec)

今回は、AKP（とBatched Key Access Joinアルゴリズム、以降BKAJ）の有無でスキャンするインデックスが変わることもなさそうなので、期待が持てます。

※前回と同様にr4.largeインスタンスで試しています。

2. やってみた

Auroraを再起動して、まずは非AKPから。

非AKP（非BKAJ）でSELECT

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=on,batched_key_access=off';
Query OK, 0 rows affected (0.01 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.member_id AND d2.dept_name = '部門015') ORDER BY tval DESC;
+-------+---------+
| memb  | tval    |
+-------+---------+
| 28942 | 1530300 |
（中略）
| 70092 | 1176300 |
+-------+---------+
41 rows in set (0.27 sec)

続いて、（Auroraを再起動して）BKAJのみ（非AKP）。

BKAJのみ（非AKP）でSELECT

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.member_id AND d2.dept_name = '部門015') ORDER BY tval DESC;
+-------+---------+
| memb  | tval    |
+-------+---------+
| 28942 | 1530300 |
（中略）
| 70092 | 1176300 |
+-------+---------+
41 rows in set (0.25 sec)

今度はBKAJのほうが速くなりました！
そして、（Auroraを再起動して）AKPをONにすると…

AKPでSELECT

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.01 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.member_id AND d2.dept_name = '部門015') ORDER BY tval DESC;
+-------+---------+
| memb  | tval    |
+-------+---------+
| 28942 | 1530300 |
（中略）
| 70092 | 1176300 |
+-------+---------+
41 rows in set (0.40 sec)

…。
やっぱり遅くなってしまいました。

インスタンスが小さく、バックで非同期読み込みをするのが遅いのかと思い、インスタンスタイプをr4.2xlargeにしてみましたが、結果はOFF時0.20秒→BKAJのみ0.22秒（遅くなった…）→AKP時0.29秒。

※実行毎のばらつきは多少ありましたので、確実に「BKAJが遅くなった」とは言い切れませんが。

なお、各テーブルのデータ量が少なかった時には、これよりも差が大きかったので（OFFよりBKAJが常に遅く、AKPがもっと遅かった）、データ量が増えることで結果が変わってくる可能性はあります。

3. まとめ…られなかった

結局、目的としていた「AKPが有効なケース」を見出すことができませんでした。

データ量が増えれば有効になるんでしょうかね…。

11/25追記：
冒頭に追記した通り、確実にバッファキャッシュをクリアして試したところ、効果を確認しました。

• Amazon Aurora（MySQL互換）でAsynchronous Key Prefetchその3／解決編

---

• Qiitaに投稿したAmazon Aurora関連記事