Amazon Auroraクラスタへの接続にコネクションプーリングを使うときのフェイルオーバー対応

Amazon Auroraでは、フェイルオーバーが発生してWriterとReaderが切り替わる場合、サーバ自体のIPアドレスは変わらず、クラスタエンドポイントおよび読み取りエンドポイントのFQDN（CNAME相当）が指すIPアドレスが入れ替わります。

また、エンドポイントが指すIPアドレスは、フェイルオーバー発生時に数回フラッピングします。

そのため、アプリケーションサーバからコネクションプーリングを使ってAmazon Auroraに接続していると、意図せずWriterからReaderに「降格」した側のサーバ（レプリカ）に再接続してしまい、更新系クエリがエラーになってしまうことがあります。

※反対に、Readerへの接続を意図していたのに誤ってWriterに接続されてしまうことについては、ある程度までなら許容できると思いますが。

これを避けるため、コネクションプーリングをやめて都度接続に変更するか、もしくは（Javaであれば）MariaDB Connector/Jのような特別なフェイルオーバー対応処理が入ったコネクターを使ってプーリングすることにするのが一般的だと思いますが、

 * Javaではなく、プーリングがないと速度的にキツい（特に、アプリケーション処理の実装の都合上、コネクションの取得～クローズを細かい単位に区切って行っている場合）
 * Javaだが、細かい挙動がMariaDB Connector/JとMySQL Connector/Jで違うために、MariaDB Connector/Jを採用できない

など、どちらの回避策を取るのも難しい場合の対処法を考えてみました。

---

**2018/01/23追記：**
Java（Tomcatなど）をお使いの方はこちらもご覧ください。

 * **[Amazon Auroraクラスタへのプーリング接続にHikariCPを使う](https://qiita.com/hmatsu47/items/40b6a272df4fca6e6e54)**

# コネクションプーリングのValidationクエリ

コネクションプーリング環境では、プールされているコネクションが実際に利用可能かチェックするために、プールからコネクションをBorrowする際のValidationクエリを設定できるのが一般的です。
通常、MySQLでは、Validationクエリに「SELECT 1」（または「/* ping */ SELECT 1」など）を指定しますが、

 * DB接続に使うユーザに「EXECUTE」権限を付与しておく
 * information_schema経由でGLOBAL変数「innodb_read_only」を取得し、「OFF」なら「1」を返し、「ON」なら接続エラーを返すストアドファンクションを定義しておく
 * Validationクエリに↑のストアドファンクションを呼び出すSQLを記述する

という方法で、プールからのBorrow時に、誤ってReaderに接続されたコネクションを閉じて再接続するようにします。

※Tomcatで「Tomcat JDBC Pool」を使う場合は、Validationクエリの代わりにValidation用のクラスを定義して、同じようなことをすることも可能です。

# Validation用ストアドファンクションを定義する

まず、ストアドファンクションを設置するデータベースを作成します。DB接続するユーザにEXEC権限も付けておきます。

```mysql:DB作成（例：concheck）・GRANT
mysql> CREATE DATABASE concheck;
mysql> GRANT EXECUTE ON concheck.* TO '接続ユーザ名'@'IPアドレス範囲等';
```

バイナリログを有効にしている場合は、非決定的なクエリの実行を許可する状態にします。

```mysql:バイナリログ有効の場合
mysql> SET GLOBAL log_bin_trust_function_creators = 1;
```

ストアドファンクションを定義します。

```mysql:ストアドファンクション
mysql> DELIMITER //
mysql> CREATE FUNCTION concheck.validation() RETURNS INT NOT DETERMINISTIC
    -> BEGIN
    -> SELECT @@GLOBAL.innodb_read_only INTO @flag;
    ->   IF @flag = 'OFF' THEN
    ->     RETURN 1;
    ->   ELSE
    ->     SIGNAL SQLSTATE '08S01'
    ->       SET MESSAGE_TEXT = 'A handshake error occured', MYSQL_ERRNO = 1043;
    ->   END IF;
    -> END;
    -> //
mysql> DELIMITER ;
```

# コネクションプーリングの設定でValidationクエリを指定する

例えば、Apache Commons DBCP(DBCP2)の場合は、「validaitonQuery」に「SELECT concheck.validation()」を指定し、「testOnBorrow」に「true」を指定します。
その他、各種タイムアウト値（「validationQueryTimeout」、「maxWaitMillis」など）やプールされているコネクションの検査・異常コネクションの除去に関する設定値も必要に応じて調整します。

※MySQL Connector/Jを使う場合は、接続用URL中に「connectTimeout」および「socketTimeout」を適切に指定します（いずれも単位はミリ秒）。

# 制限（限界）

使用するコネクションプーリングの種類やサーバの負荷状況等によっては、これだけでは対応が不十分な場合もあります。

例えば、Apache Commons DBCP2では、「maxTotal」や「maxIdle」を大きな値（無制限を示す「-1」を含む）に指定していたとしても、異常コネクションを除去（Eviction）する設定にしていないとフェイルオーバー後のコネクション数が十分に回復しないことがありますし（場合によってはすべてのコネクションがリークした時と同様のロック状態になります）、除去する設定にした場合も、除去を早めるために処理頻度を高くすると、Webアプリケーションサーバの処理が追いつかなくなる（ロック状態ではないが、リクエストがキャンセルされて負荷が下がるまでまともな時間内に応答できない状態になる）ことがあります。

その場合は、異常コネクションの検査・除去については処理頻度を控えめにしておく一方、「二次対応」として「エラーログ等を確認し、必要に応じてWebアプリケーションサーバの再起動やWebアプリケーションのリロードをする」ような外部処理を実装しておくことになると思います。

---

**【おまけ】**
Amazon Aurora関連投稿記事へのリンクを集めました。

 * **[Qiitaに投稿したAmazon Aurora関連記事](http://hmatsu47.hatenablog.com/aurora)**