re:dashでAWSのコストを分析してみた | ヌーラボ

re:dashでAWSのコストを分析してみた | ヌーラボ. Backlog SREチームの山崎 ( @revsystem )です。2017年11月に開催された Geeks Who Drink in Tokyo –AWS … 続きを表示. Backlog SREチームの山崎 ( @revsystem )です。2017年11月に開催された Geeks Who Drink in Tokyo –AWS & 神楽坂 Edition- … 続きを読む

UnityからAWS Cognito Identity Providerで認証機能を実装する(User_Auth_Flow)

前置き:AWSSDK for Unityでは現状,公式にCognito Identity Providerの機能をサポートしていません.紹介した方法は自己責任で参考にしてください.

UnityからCognito User Poolを使って認証する

この記事ではAWSのCognito User Poolを使用してログイン機能をUnityのアプリケーションに実装し,AWSのリソースにアクセスするためのトークンを取得する方法を紹介します.

Cognito User Poolとは

https://docs.aws.amazon.com/ja_jp/cognito/latest/developerguide/cognito-user-identity-pools.html

自分でサーバー側の実装を書くことなくユーザー認証機能を実装できます.

手順

環境

AWSの設定

User Poolの作成と設定

UserPool.png

Cognitoからユーザーを管理するためのUser Poolを作成し,以下のように設定します.

  • プール名

    • わかりやすい任意の名前を設定します.
  • 属性
    • 標準属性のチェックを全て外し,ユーザー名とパスワードだけでログインできるように設定します.
  • アプリクライアントの追加

    • “アプリクライアントの追加”をクリックします.
    • アプリクライアント名を設定します.
    • “クライアントシークレットを生成する”をオフにします.

設定し終わったら”確認タブ”からプール作成を終了し,

  • プールIDとプール名 (リージョン名と固有なプール名の組み合わせ)

    • 例) ap-northeast-1_Hoge0Hoge
      を確認します.

さらにアプリの統合>アプリクライアントの設定タブから,

  • “有効なIDプロバイダ”のチェック欄に表示されている”Cognito User Pool”にチェックを入れます.
  • アプリクライアントID (25桁のAlphanumericな値)を確認します.

Identity Poolの作成と設定

IdentityPool.png

Cognitoから”フェデレーテッドアイデンティティ”のページに移動し,”新しいIDプールの作成”をクリックするとIDプール作成ウィザードが開始します.

  • IDプール名を入力します.
  • “認証されていないIDに対してアクセスを有効にする”をオンにするとゲストユーザーとしてアプリを使用することが可能です.ここでは必要ないのでオフのままで大丈夫です.
  • 認証プロバイダーのセクションで”Cognito”タブをクリックし,先ほど確認したユーザープールのIDとアプリクライアントIDを入力します.

Createpolicy.png

“プールの作成”をクリックすると,”AWSの諸々にアクセスするための権限の設定をする必要がある”ということで,ゲストユーザーと認証ユーザーの二つのユーザーについてIAMロールを設定するように言われます.

ここではどちらも”新しいIAMロールの作成”を選択し,そのまま右下の許可を押します.ここで作成した二つのIAMロール(Cognito_*Hoge*Auth_RoleCognito_*Hoge*Unauth_Role)はあとで編集するので名前を覚えておきます.

IAMロールの編集

先ほど作成したロールの権限を編集し,Cognito User Poolに対して認証リクエストを送る権限を付与します.

CogitoIdentityProvider.dll for Unityのビルド

AWS Mobile SDK for UnityではCognito User Poolがサポートされておらず,自分でCognitoIdentityProvider.dllをビルドする必要があります.dotNet版のコードジェネレーターの設定をいじることでUnityに対応したdllを得ることができます.

GitHubのissueに具体的な方法が書かれており,それを翻訳すると以下のようになります.

  1. AWS SDK dot netのリポジトリをクローンします.

  2. aws-sdk-netgeneratorServiceModelscognito-idpmetadata.jsonを編集し,"platforms": ["Unity"],をjsonのrootに追加します.

  3. aws-sdk-netgeneratorServiceClientGeneratorLib/Generators/ProjectFiles/UnityProjectFile.csの265行目でUnityEngine.dllのパスを修正します. UnityEngine.dllの場所はMacOSとWindowsで異なるので注意.ここではWindowsでビルドします.修正後のパスはC:/Program Files/Unity/Editor/Data/Managed/UnityEngine.dllとなります.

  4. aws-sdk-netgeneratorAWSSDKGenerator.slnをVisual Studioで開きます.実行ボタン(上の緑の三角ボタン)を押すとビルドされたコード生成プログラムが実行され,上手くいけばaws-sdk-netsdksrcServicesCognitoIdentityProvider内部にAWSSDK.CognitoIdentityProvider.Unity.csprojというファイルが生成されます.

  5. 次にaws-sdk-netsdkAWSSDK.Unity.slnを開き,Build TypeをReleaseに設定してからビルド(ctrl + shift + B)します.すると新しくaws-sdk-netsdksrcServicesCognitoIdentityProviderbinReleaseunityフォルダが作られ,中にCognitoIdentityProvider.dllが生成されます.

  6. UnityプロジェクトのAssets/下任意の場所にdllを置きます.

metadata.json
{
  "platforms": ["Unity"],
  "active": true, 
  "synopsis": "You can create a user pool in Amazon Cognito Identity to manage directories and users. You can authenticate a user to obtain tokens related to user identity and access policies. This API reference provides information about user pools in Amazon Cognito Identity, which is a new capability that is available as a beta."
}
UnityProjectFile.cs
...
 this.Write(this.ToStringHelper.ToStringWithCulture(Path.Combine((string)this.Session["UnityPath"], "Editor", "Data", "Managed", "UnityEngine.dll")));
...

Unityで認証フローを実装する

AWS Mobile SDK for Unityを公式サイトからダウンロードし,AWSSDK.IdentityManagement.unitypackageをプロジェクトにインポートします.

以下,ブログエントリーを参考にして認証フローと暗号化処理を実装します.

AdminInitiateAuth関数を使った方法が紹介されることが多いですが,パスワードが平文で送信されるのでモバイルアプリで用いるにはセキュリティ的に非常に危険です.今回はSecure Remote Password (SRP)プロトコルをつかった認証フローであるUSER_SRP_AUTHに従います.

暗号化の実装はブログエントリーが詳しく,また依存するHkdfクラスの実装もGistに公開されているものが完動するのでコピペします.Bouncy Castle C#のライブラリが必要なのでBouncy Castle C#のGitHubリポジトリから最新リリースをダウンロードし,.net2.0版のdllをプロジェクトにコピーします.

注意すべき点として, 作成したばかりのユーザーのステータスはFORCE_CHANGE_PASSWORDと設定されており,User Poolからのレスポンス(チャレンジ)として新しいパスワードを要求してきます.下のプログラムではまだ実装しておらず,パスワード変更済みのユーザーに対してログイン処理をかけています.

認証フローが成功すると
* IdToken
* AccessToken
* RefreshToken
の三つが手に入ります.

得られたIdトークンを使ってCredential.AddLogins(IdentityProviderName , IdToken)することで,GetCredentialForIdentityAsync関数を実行した時にAWSのAPIを叩く上で必要なトークンを得ることができます.このトークンを使って認可されるアクション,アクセスできるリソースはIAMで設定したCognito_HogeAuth_Roleに従います.

リフレッシュトークンはPlayerPrefなどに保存し,アプリを起動した際にIdトークンを更新することで自動ログイン処理を行います.

CognitoUserPoolClient.cs
namespace CognitoLogInSample
{
    using System.Collections;
    using System.Collections.Generic;
    using UnityEngine;
    using UnityEngine.UI;
    using System;
    using System.Globalization;
    using Amazon;
    using Amazon.Runtime;
    using Amazon.CognitoIdentity;
    using Amazon.CognitoIdentity.Model;
    using Amazon.CognitoIdentityProvider;
    using Amazon.CognitoIdentityProvider.Model;

    public class CognitoUserPoolClient : MonoBehaviour
    {
        #region CognitoCredentials
        public string IdentityPoolId;            // IDプールのID
        public string CognitoIdentityRegion;     // リージョン名 例)ap-northeast-1

        private RegionEndpoint _CognitoIdentityRegion
        {
            get { return RegionEndpoint.GetBySystemName(CognitoIdentityRegion); }
        }
        private CognitoAWSCredentials _credentials;

        private CognitoAWSCredentials Credentials
        {
            get
            {
                if (_credentials == null)
                {
                    _credentials = new CognitoAWSCredentials(IdentityPoolId, _CognitoIdentityRegion);
                    _credentials.IdentityChangedEvent += Credentials_IdentityChangedEvent;
                }
                return _credentials;
            }
        }
        #endregion

        #region CognitoIdP
        public string CognitoIdPRegion;          // User Poolのリージョン 例)ap-northeast-1

        private RegionEndpoint _CognitoIdPRegion
        {
            get { return RegionEndpoint.GetBySystemName(CognitoIdPRegion); }
        }

        [SerializeField]
        string userPoolName;                     // User Poolの固有名 アンダーバーで区切ったうちの後半
        [SerializeField]
        string clientId;                         // User PoolのクライアントID

        string UserPoolId
        {
            get
            {
                return string.Format("{0}_{1}", _CognitoIdPRegion, userPoolName);
            }
        }

        public string CognitoIdentityProviderName
        {
            get
            {
                return string.Format("cognito-idp.{0}.amazonaws.com/{1}", _CognitoIdentityRegion.SystemName, UserPoolId);
            }
        }
        #endregion

        AmazonCognitoIdentityProviderClient idpClient;
        AmazonCognitoIdentityClient cognitoIdentityClient;

        public InputField IdInputField;
        public InputField passwordInputField;
        public Toggle clearCredentialToggle;

        TokenCacheManager tokenCacheManager;
        public bool cleanPlayerPrefSetting;

        string currentSession;
        string currentUserName;

        private void Start()
        {
            tokenCacheManager = new TokenCacheManager();
            if (cleanPlayerPrefSetting)
            {
                tokenCacheManager.DeleteCachedToken();
            }
            //AdminAuthenticateWithRefreshToken();
            SignInWithRefreshToken();
        }

        public void OnButtonClick()
        {
            if (clearCredentialToggle.isOn)
            {
                Credentials.Clear();
            }
            SignIn(IdInputField.text, passwordInputField.text);
        }

        #region InitiateAuthFlow

        /// <summary>
        /// Signs In with refresh token (USER_AUTH_FLOW).
        /// </summary>
        void SignInWithRefreshToken()
        {
            tokenCacheManager.GetCachedTokens((getCachedTokensResult) =>
            {
                if (getCachedTokensResult.IsCacheAvailable)
                {
                    Debug.Log(getCachedTokensResult.Token.ToString());
                    // RefreshToken
                    idpClient = new AmazonCognitoIdentityProviderClient(Credentials, _CognitoIdentityRegion);
                    InitiateAuthRequest initiateAuthRequest = new InitiateAuthRequest()
                    {
                        ClientId = clientId,
                        AuthFlow = AuthFlowType.REFRESH_TOKEN_AUTH,
                    };
                    initiateAuthRequest.AuthParameters.Add("REFRESH_TOKEN", getCachedTokensResult.Token.refreshToken);

                    idpClient.InitiateAuthAsync(initiateAuthRequest, (initiateAuthResponse) =>
                    {
                        if (initiateAuthResponse.Exception != null) return;
                        CognitoIdentityProviderToken cognitoIdentityProviderToken = new CognitoIdentityProviderToken
                        {
                            accessToken = initiateAuthResponse.Response.AuthenticationResult.AccessToken,
                            idToken = initiateAuthResponse.Response.AuthenticationResult.IdToken ?? getCachedTokensResult.Token.idToken,
                            refreshToken = initiateAuthResponse.Response.AuthenticationResult.RefreshToken ?? getCachedTokensResult.Token.refreshToken,
                            expireTime = initiateAuthResponse.Response.AuthenticationResult.ExpiresIn
                        };
                        tokenCacheManager.CacheTokens(cognitoIdentityProviderToken);

                        Credentials.AddLogin(CognitoIdentityProviderName, initiateAuthResponse.Response.AuthenticationResult.IdToken);
                        Credentials.GetIdentityIdAsync(responce =>
                        {
                            Debug.Log("Logged In with refreshed IdToken : " + responce.Response);
                        });
                    });
                    idpClient.Dispose();
                }
                else
                {
                    Credentials.Clear();
                    // Redirect to LogIn Dialog
                    Debug.Log("RefreshToken is not available");
                }
            });
        }


        /// <summary>
        /// Sign In.
        /// </summary>
        /// <param name="userName">User name.</param>
        /// <param name="password">Password.</param>
        void SignIn(string userName, string password)
        {
            Debug.Log("Initiate Authentication Flow");

            var cred = new AnonymousAWSCredentials();

            idpClient = new AmazonCognitoIdentityProviderClient(cred, _CognitoIdentityRegion);
            var TupleAa = AuthenticationHelper.CreateAaTuple();

            var initiateAuthRequest = new InitiateAuthRequest
            {
                AuthFlow = AuthFlowType.USER_SRP_AUTH,
                ClientId = clientId,
                AuthParameters = new Dictionary<string, string>(){
                            {"USERNAME", userName},
                            {"SRP_A", TupleAa.Item1.ToString(16)},
                        }
            };

            Debug.Log(initiateAuthRequest.AuthParameters["SRP_A"]);

            idpClient.InitiateAuthAsync(initiateAuthRequest, (initiateAuthResponse) =>
            {
                var challengeName = initiateAuthResponse.Response.ChallengeName;
                if (challengeName == ChallengeNameType.NEW_PASSWORD_REQUIRED)
                {
                    // newPasswordRequired
                    idpClient.Dispose();
                }
                else if (challengeName == ChallengeNameType.PASSWORD_VERIFIER)
                {
                    DateTime timestamp = TimeZoneInfo.ConvertTimeToUtc(DateTime.Now);
                    var usCulture = new CultureInfo("en-US");
                    string timeStr = timestamp.ToString("ddd MMM d HH:mm:ss "UTC" yyyy", usCulture);

                    byte[] claim = AuthenticationHelper.authenticateUser(initiateAuthResponse.Response.ChallengeParameters["USERNAME"],
                                                                         password,
                                                                         userPoolName,
                                                                         TupleAa,
                                                                         initiateAuthResponse.Response.ChallengeParameters["SALT"],
                                                                         initiateAuthResponse.Response.ChallengeParameters["SRP_B"],
                                                                         initiateAuthResponse.Response.ChallengeParameters["SECRET_BLOCK"],
                                                                         timeStr
                                                                        );
                    string claimBase64 = Convert.ToBase64String(claim);

                    var respondToAuthChallengeRequest = new RespondToAuthChallengeRequest()
                    {
                        ChallengeName = initiateAuthResponse.Response.ChallengeName,
                        ClientId = clientId,
                        ChallengeResponses = new Dictionary<string, string>(){
                                    { "PASSWORD_CLAIM_SECRET_BLOCK", initiateAuthResponse.Response.ChallengeParameters["SECRET_BLOCK"]},
                                    { "PASSWORD_CLAIM_SIGNATURE", claimBase64 },
                                    { "USERNAME", userName },
                                    { "TIMESTAMP", timeStr }
                        }
                    };

                    Debug.Log(timeStr);

                    idpClient.RespondToAuthChallengeAsync(respondToAuthChallengeRequest, respondToAuthChallengeResponse =>
                   {
                       try
                       {
                           Debug.LogFormat("User was verified in SRP Auth Flow : {0}", respondToAuthChallengeResponse.Response.AuthenticationResult.IdToken);
                           Credentials.AddLogin(CognitoIdentityProviderName, respondToAuthChallengeResponse.Response.AuthenticationResult.IdToken);

                           tokenCacheManager.CacheTokens(new CognitoIdentityProviderToken()
                           {
                               accessToken = respondToAuthChallengeResponse.Response.AuthenticationResult.AccessToken,
                               idToken = respondToAuthChallengeResponse.Response.AuthenticationResult.IdToken,
                               refreshToken = respondToAuthChallengeResponse.Response.AuthenticationResult.RefreshToken,
                               expireTime = respondToAuthChallengeResponse.Response.AuthenticationResult.ExpiresIn,
                           });
                       }
                       catch (Exception e)
                       {
                           Debug.LogErrorFormat("Encountered exception: {0}", e);
                       }
                       finally
                       {
                           idpClient.Dispose();
                       }
                   });
                }
            });

            currentUserName = userName;
        }
        #endregion

        private void Credentials_IdentityChangedEvent(object sender, CognitoAWSCredentials.IdentityChangedArgs e)
        {
            Debug.Log(string.Format("Identity has changed from {0} to {1}", e.OldIdentityId, e.NewIdentityId));
        }
    }
}

参考資料

続きを読む

Windows Server で動的コンテンツを EC2 + RDS で作ってみるの巻

目的

検証用のアプリケーションを作成する

目標

Web 動的コンテンツから、RDS Mysql へデータ書き込みを行う

環境

EC2 t2.miclo WindowsServer2016
Eclipse 4.7 Oxygen の Windows 64bit Full Edition Java
Tomcat 8

その他、下のサイト参照
https://qiita.com/hatakkkk/items/cb8dd22041d75952c8d7

Eclipse 動かすのに t2.miclo は小さいかも。。。

JDBC の導入

お勉強用に下のサイトを参考にした
http://web.sfc.wide.ad.jp/~tinaba/tutorials/mysql-j/
https://qiita.com/ononoy/items/4961ce6d5b12aff6c108
https://www.qoosky.io/techs/d2beb9dc80

Mysql の JDBCドライバについて、Aamazon から情報が提供されているようなので、下のサイトを確認する。
https://docs.aws.amazon.com/ja_jp/elasticbeanstalk/latest/dg/java-rds.html

Mysql のJDBC ドライバをインストールする

下のサイトから、”mysql-connector-java-5.1.45.zip” をダウンロードする
https://dev.mysql.com/downloads/file/?id=474258

「あれ?ダウンロードが始まらないな?」というときは、
Web サイトの中央下付近にある文章”No thanks, just start my download.” をクリック

今日はここまで。

続きを読む

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

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


続きを読む