Amazon Aurora(MySQL互換)でAsynchronous Key Prefetchその3/解決編

と、

で、立て続けに検証に失敗した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倍以上速くなりました!
※もう一方のインスタンスでも同様でした。

3. まとめ

  • 正しいテストケースでは、しっかり効果がありました(すみません…)。
  • 曖昧な経験則はあてにせず、きちんと検証!
  • ただ、「バッファキャッシュに載っている状態で実行するとデフォルトより遅くなる」点は間違いないのが悩ましいところ…。データ量があまり多くない場合は遅くなったとしても許容範囲なので、使ってみてもいいかもしれない。

続きを読む

サーバー持ってない人のためのユドナリウム設置手順

はじめに

ユドナリウムをS3上にデプロイする方法をまとめています。(情報をまとめただけで、詳細な手順はないです)
公式情報ではないので参考程度に。

ユドナリウムとは

ユドナリウム(Udonarium)は、ボードゲームやオンラインセッション支援ツールです。
ユドナリウムはhtmlやjsといったサーバー側で処理しない静的なファイルなので静的ウェブサイトホスティングサービスを利用することができます。

デプロイの方法

デプロイ方法もいくつかあります。

  • サーバーを立ててファイル配信

    • すでにサーバーや独自ドメインを持っている人にはおすすめ
  • 静的ウェブサイトホスティングサービス(AWS S3など)を使う(後述)
    • サーバーを立てたり、メンテナンスしたりする必要がない

設置手順

  • AWSのアカウントの取得
  • AWSユーザーの設定
  • S3バケット/ドメインの確認
  • SkyWayのAPIキー取得
  • S3のバケット設定
  • ドメインの取得
  • ユドナリウムのダウンロードと設定
  • S3にアップロード
  • Route53の設定

AWSアカウントの取得

AWS アカウント作成の流れを参考にしてください。

AWSユーザーの設定

最初に作成したルートアカウントで操作することはなるべくなら避けておきたいところです。以下を参考にルートアカウント以外のユーザーを作成し、MFAも有効にしておきます。
Qiita:AWSアカウントを取得したら速攻でやっておくべき初期設定まとめ
Qiita:AWSアカウント作ったらこれだけはやっとけ!IAMユーザーとAuthyを使ったMFAで2段階認証

SkyWayへの登録

Skywayの登録だけ先に済ませておきます。

  • Community Editionの新規登録をします。

S3バケット/ドメインの確認

S3の静的ウェブサイトホスティングを利用する際には、ドメインと同じバケット名を利用する必要があります。
どちらも空いているものであるかをまず確認し、空いていればバケットの作成とドメインの取得まで済ませておきます。

バケットの作成

S3 バケットを作成する方法に従ってバケットを作成します。
バケットは以下の2つを作成します。

ドメイン名の取得

恒常的にユドナリウムを利用する場合、他人も使えるドメインだと同じAPIキーを使えてしまうので独自ドメインの取得をお勧めします。
ドメインを取得するサービスは色々ありますが、AWSで完結させるのであれば後で使うRoute53のドメイン取得を利用してもよいでしょう。

SkyWayのAPIキー取得

SkyWayのAPIキーを取得します

  • 「新しいアプリケーションを追加する」をクリックして登録します。
  • 利用可能ドメインに取得済みのドメイン(example.com)を記載しておきます。
  • 後で使うのでAPIキーをメモしておきます。

ユドナリウムの設定

  • ユドナリウムのreleasesPageから最新版のファイルを取得しておきます。
  • .zipファイルを展開します。
  • 先ほど取得したSkyWayのAPIキーをassets/config.yamlに記載します。

S3にアップロード

  • udonarium以下のファイルをドメイン名のバケット(example.com)にアップロードします。
  • ドメイン名のバケット(example.com)にアクセス許可を設定します。
  • ドメイン名のバケットにウェブサイトホスティングの設定を適用します。
  • www.ドメイン名のバケット (www.example.com)にリダイレクトの設定をします。

例: 独自ドメインを使用して静的ウェブサイトをセットアップする

Route53の設定

取得したドメインの名前解決のためRoute53の設定をします。

  • ドメインの反映までには結構時間がかかります。安定してつながるまでには10時間くらいはかかりましたが、最大で48時間ほどかかることもあるようです。

例: 独自ドメインを使用して静的ウェブサイトをセットアップする

できあがり

お疲れさまでした。

HTTPS化

通常使う分にはHTTP接続で問題ないと思いますが、HTTPS接続で使う場合は以下を行います。

所感

我が家のうどんスープはヒガシマルなので…

その他参考

【非公式】ユドナリウムの導入(インストール)手順

続きを読む

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

先の記事、

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

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

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

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 WARNINGSG
*************************** 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 WARNINGSG
*************************** 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が有効なケース」を見出すことができませんでした。

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


続きを読む

OpenFaaSを使ってGo言語でFunctionを書いて、AWSに展開したDocker環境にデプロイするまで

OpenFaaSは聞いたことがあるでしょうか。
まだ生まれて半年ほどしか経っていませんが、GithubStar数は6000を超える勢いで成長している有望なフレームワークです。

2017年10月18日に行われたServerless Meetup Osaka #4でOpenFaaSのことを聞いたので早速試してみました。

どこまでやるかというと、
OpenFaaSのAuthorであるAlex Ellisのブログで紹介されている、OpenFaaSでGo言語の関数を動かして、さらにクラウドに展開するところまでやってみたいと思います。

今回やってみて、日本語の情報がほぼ皆無で、英語でも情報がかなり少なかったので、丁寧に手順を載せておきます。

OpenFaaSいいところ

まとめとして先に良かったことを書いておきます。

  • Dockerイメージになるなら何でも動かせる
  • とはいえ、メジャーな言語のテンプレートを用意している
  • 手軽さは既存のFunction as a Serviceと変わらない

スケール・コストなどの観点はチュートリアルでは評価しきれないので、言及しません。

Faas CLIのインストール

Docker CE 17.05以上がインストールされていることが必要です。

コマンドでさくっとインストールできます。

curl -sSL https://cli.openfaas.com | sudo sh

brewコマンドが使えるなら、

brew install faas-cli

Selection_002.png

OpenFaaSデプロイ環境をローカルに追加

Kubernetesに比べると簡単に扱えると感じたDocker Swarmにデプロイします。

まずはDocker Swarm自体の初期化して、managerとworkerを動作させます。

docker swarm init

以下のコマンドでFaaSスタックをデプロイします。

git clone https://github.com/openfaas/faas && \
  cd faas && \
  git checkout 0.6.5 && \
  ./deploy_stack.sh

このデプロイした環境はどこにいるかというと、Docker Service (Swarm Mode) として動いているので、docker service lsのコマンドで確認できます。

image.png

ローカルのデプロイ環境にはhttp://localhost:8080でアクセスできるので開くと、次のようなFunction管理ポータルが表示されます。
Linuxの場合はhttp://127.0.0.1:8080で開く必要があるかもしれません。

Selection_006.png

Go言語のインストール

こちらの公式インストールガイドを参考にインストールしてください。

v1.8.3かそれ以降が必要です。
gvmでインストールしても問題ありません。

Selection_003.png

$GOPATHが設定されているか確認しておきます。
Selection_004.png

OpenFaaSプロジェクトの生成

まずはプロジェクトフォルダーを作成します。

mkdir -p $GOPATH/src/functions
cd $GOPATH/src/functions

続いてFaaS CLIを使ってプロジェクトテンプレートを生成します。
名前は参考記事通りgohashです。

faas-cli new --lang go gohash

Selection_005.png

プロジェクトの中身を確認する

gohash.ymlファイルにはテンプレートで作成されたFunctionとローカルの実行環境についての設定が書き込まれています。

gohash.yml
provider:
  name: faas
  gateway: http://localhost:8080

functions:
  gohash:
    lang: go
    handler: ./gohash
    image: gohash

gohash/handler.goにはHello Worldなコードが書かれています。

gohash/handler.go
package function

import (
    "fmt"
)

// Handle a serverless request
func Handle(req []byte) string {
    return fmt.Sprintf("Hello, Go. You said: %s", string(req))
}

そしてtemplate内には、各言語のテンプレートもありますが、Go言語のものがちゃんとあります。
main.goではOpenFaaSの仕様通り、 STDIN を受け取って、対応するFunctionを呼び出した結果を STDOUT に送るというシンプルな作りとなっています。

template/go/main.go
package main

import (
    "fmt"
    "io/ioutil"
    "log"
    "os"

    "handler/function"
)

func main() {
    input, err := ioutil.ReadAll(os.Stdin)
    if err != nil {
        log.Fatalf("Unable to read standard input: %s", err.Error())
    }

    fmt.Println(function.Handle(input))
}

Dockerfileが各言語の環境ごとに用意されていて、ミニマルな実行環境を作成し、開発者が用意したFunctionコードをコンテナ内に格納してから最後にWatchdogを起動するという動きになっているようです。

WatchdogはGo言語で書かれた小さなHttpサーバーです。

template/go/Dockerfile
FROM golang:1.8.3-alpine3.6
# ---------- 略 -----------
WORKDIR /go/src/handler
COPY . .
# ---------- 略 -----------
CMD ["./fwatchdog"]

まずはデプロイしてみる

テンプレートを導入した時点で、環境構築が無事に完了しているか確認するために、HelloWorldのままデプロイしてみます。

faas-cli build -f gohash.yml
faas-cli deploy -f gohash.yml

BuildはDockerのイメージをダウンロードするところから始まるので、最初の実行は1分ほど待つかもしれません。

とくにエラーが表示されずに、
Selection_009.png
という風な表示がされたら成功です。

http://localhost:8080にアクセスしてgohashが追加されているか確認します。
左のFunction一覧からgohashを見つけたら適当なRequest bodyを打ち込んで INVOKE します。

Selection_007.png

これで無事にデプロイできることが確認できました。

Functionを実装していく

今回はgo1.9を使ったので、コンテナ内のgoも同じバージョンにしておきます。
今回のコードでは変更しなくても特に問題にはならないと思います。
コンテナ生成Dockerfileを書き換えてしまいます。

template/go/Dockerfile変更前
FROM golang:1.8.3-alpine3.6
template/go/Dockerfile変更後
FROM golang:1.9-alpine3.6

go言語のパッケージ管理ツールとしてdepをインストールします。

go get -u github.com/golang/dep/cmd/dep

そして、Goのデータ(Struct)からハッシュを生成するstructhashdepを使ってインストールします。
ただし、faas-cliがDockerコンテナをビルドするときに、template/go/をワーキングディレクトリとしてビルドを行うため、depの実行はこのディレクトリに移動して行う必要があります。

cd template/go/
dep init
dep ensure -add github.com/cnf/structhash

こうすると、Gopkg.lockGopkg.tomlvernder/template/go/以下に生成されます。
ちなみに、gvm環境だとdep ensureでライブラリがうまくインストールされないことがありますが、goのコードがビルドされるのはコンテナ内なので、一応そのまま進めても大丈夫です。

きちんと開発を進める場合はgvm linkthisdepがしっかりと使えるようにします。

それでは、受け取った文字列データからハッシュを生成するコードに変更しましょう。

gohash/handler.go
package function

import (
    "fmt"

    "github.com/cnf/structhash"
)

// S is sample struct
type S struct {
    Str string
    Num int
}

// Handle a serverless request
func Handle(req []byte) string {
    s := S{string(req), len(req)}

    hash, err := structhash.Hash(s, 1)
    if err != nil {
        panic(err)
    }
    return fmt.Sprintf("%s", hash)
}

プロジェクトのデプロイ

では、作成したコードをデプロイしてみましょう。

faas-cli build -f gohash.yml
faas-cli deploy -f gohash.yml

ポータルの実行結果はFunctionのデプロイで新しいものに置き換えたので結果が変わっています。

Selection_010.png

また、もちろんAPI URLも用意されているので、直接呼び出すこともできます。

curl -X POST -d "てすとめっせーじ" http://localhost:8080/function/gohash

Selection_011.png

テストを追加する

テンプレートのDockerfileにはgo testでテストを実施しているのですが、今のところテスト用のgoファイルは生成されないようです。

今は自分で作りましょう。

touch gohash/handler_test.go
gohash/handler_test.go
package function

import "testing"

func TestHandleReturnsCorrectResponse(t *testing.T) {  
    expected := "v1_b8dfcbb21f81a35afde754b30e3228cf"
    resp := Handle([]byte("Hello World"))

    if resp != expected {
        t.Fatalf("Expected: %v, Got: %v", expected, resp)
    }
}

テストを作ったところで、わざとgohash Functionを間違えて修正してしまいましょう。

handler.go修正前
hash, err := structhash.Hash(s, 1)
handler.go修正ミス
hash, err := structhash.Hash(s, 2)

この状態で再びビルドを実行します。

faas-cli build -f gohash.yml

ちゃんとビルド中にテストで失敗してくれます。
Selection_012.png

コードを元に戻してビルドが成功することを確認します。

DockerHubにビルドしたイメージをPUSH

リモート環境でOpenFaaSを動作させるためには、FunctionのDockerイメージをDockerHubまたは別のレジストリに登録しておく必要があります。

まずは、[DockerHubのユーザ名]/gohashとなるように、gohash.ymlを書き換えます。

gohash.yml書換前
    image: gohash
gohash.yml書換後
    image: gcoka/gohash

Docker Hubの登録が済んでいれば、

docker login

でログインし、

faas-cli push -f gohash.yml

でビルドしたイメージをPUSHします。

AWSにデプロイしてみる

AWS EC2コンソールで SSH KeyPairsを作成しておいてください。
ssh-addもお忘れなく。

ssh-add ~/.ssh/yourkey.pem

AWSにDockerをデプロイするためのテンプレートが用意されているので、ここにアクセスして、
Deploy Docker Community Edition (CE) for AWS (stable)をクリックします。
use your existing VPCを選択すると、Docker用ネットワークの設定をいろいろやらないといけなくなり、VPC内のネットワーク構築の知識が必要となるようです。

https://docs.docker.com/docker-for-aws/#docker-community-edition-ce-for-aws

以下の設定は環境に合わせて変更が必要です。

  • SSHキーにKeyPairsで作成したものを指定。

また、このテンプレートでのCloudFormation実行には、以下のCreateRoleが必要です。
正確な一覧はこちら

  • EC2 instances + Auto Scaling groups
  • IAM profiles
  • DynamoDB Tables
  • SQS Queue
  • VPC + subnets and security groups
  • ELB
  • CloudWatch Log Group

特に設定は変更していません。
デプロイを試すだけなので、Swarm ManagerとWorkerの数は1ずつにしました。

Selection_029.png

CloudFormationのStackデプロイが完了したら、デプロイ結果のOutputsタブから、Swarm Managerのインスタンスへのリンクが参照できるので、開きます。
Selection_030.png

Selection_031.png

このインスタンスにSSH接続を行います。
ユーザーはdockerを指定します。

ssh docker@54.159.253.49

OpenFaaSのスタックを導入するために、Gitが必要なので、インストールします。

中身はAlpine Linuxなので、apkコマンドでパッケージをインストールします。

sshコンソール
sudo apk --update add git

ローカルでにインストールしたときと同じコマンドですが再掲。

sshコンソール
git clone https://github.com/openfaas/faas && \
  cd faas && \
  git checkout 0.6.5 && \
  ./deploy_stack.sh

ではOpenFaaSスタックが無事デプロイされているか確認します。

AWSに構築したテンプレートはインターネットからはLoadBalancerを通してアクセスできるので、LoadBalancerのURLを調べます。DNS name: に表示されているものがそれです。

Selection_034.png

URLがわかったら、ポート8080を指定してアクセスします。

Selection_035.png

うまくいっていることを確認したらAWSにデプロイします。
--gatewayオプションを使えばgohash.ymlファイルを書き換える必要がありません。

faas-cli build -f gohash.yml --gateway http://your.amazon.aws.loadbalancer.url:8080

Selection_038.png

Selection_039.png

無事にAWSで動きました。

トラブルシューティング(詰まったところ)

faas-cli buildしてもコード変更が反映されない

--no-cacheをつけることで、すべてのビルドをやり直してくれます。

faas-cli build -f gohash.yml --no-cache

structhashパッケージがvenderディレクトリにコピーされない

$GOPATHが正しく設定されているか確認してください。

gvmを使ってgoをインストールしている場合は、$GOPATH/src/functionsgvm linkthisを実行すると解決するかもしれません。

AWS CloudFormationのデプロイに失敗する

use your existing VPCのテンプレートを使っている場合は、使わないでVPCの作成もDockerテンプレートに任せてください。

AWSにデプロイしたFunctionを実行しても500: Internal Server Errorになる

Docker HubにDockerイメージをPUSHしたか確認してください。

ちゃんと動いているかわからない・・・

docker serviceとして動いているのでdockerコマンドからいくつか情報を得ることができます。

指定したFunctionのプロセス動作情報を表示
docker service ps gohash
指定したFunctionのログを表示
docker service logs gohash
gatewayのログを表示
docker service logs func_gateway

最後に

Docker SwarmはMicrosoft Azure Container Serviceもサポートしているのですが、完全なマネージドの場合OpenFaaSが必要とするDockerバージョンが足りていないようです。

今回はDocker Swarmに対してデプロイしましたが、Kubernetesもサポートしているので、そちらも試してみたいと思います。
Kubernetesの場合は、Swarmよりもホスティング対応しているクラウドがいくつかあるようなので、期待が持てますね。

情報がとても少ないので、どんどん試してどんどん情報公開していきましょう!

続きを読む