
## 概要

仕事でAurora MySQLを3系にアップグレードで失敗してたくさんの学びを得たのでその振り返り

## 背景

Aurora MySQLのメジャーバージョンが標準サポートが終了するにあたって、社内のAurora MySQLを3系にバージョンアップグレードする必要があった。

## 対応流れ

1. 影響範囲の洗い出し
2. スケジュール決め
3. テスト環境にてテストのアップグレードを行う
4. 関係者にリスクと対応方針の説明会
5. 実施
6. 残対応

ざっくり上記の流れで進めていった。

## やったこと - タイムライン -

前提としてEC2のRailsアプリケーションがAurora MySQLとコネクションを確立している。

### 1. 影響範囲の洗い出し

MySQLコミュニティ公式のドキュメントや、AWSの公式ドキュメントを読み込んで影響範囲を調査していった。

https://dev.mysql.com/doc/refman/8.0/ja/upgrading-from-previous-series.html

https://dev.mysql.com/doc/refman/8.0/ja/mysql-nutshell.html

https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/AuroraMySQL.MySQL80.html

自社のアプリケーションはtoBということもあり、COLLATIONに関する要件は高くないので特に設定等変えていない。

### 2. テストケースの用意と実施

テスト環境にて動作テストを行うためにテストケースを用意していった。

主要な機能のテストケースは特に丁寧にケースを洗い出して整理した。

テスト環境のAurora MySQLを3系にアップグレードして動作確認を進めていった。

### 3. アップグレード方法の検討

並行してアップグレード方法の検討をしていった。

当初はBlue/Green Deploymentを利用してアップグレードを考えていた。

その時はマルチAZのAurora MySQLはBlue/Green Deploymentできないと勘違いしており、インプレースアップグレードの検討をしていった。

ただ、事業部長などとも相談した結果、インプレースアップグレードだとサービスダウンの時間が長すぎるので別の方法を再検討した。

> その時はマルチAZのAurora MySQLはBlue/Green Deploymentできないと勘違いしており

レプリケーションを使ってエンドポイントで切り替える方が良いかと考えていたが、再調査の結果上記が勘違いと分かり、Blue/Green Deploymentを使ってアップグレードを実施することに決定。

＊マルチAZのRDSクラスターはBlue/Green Deploymentを利用できないが、AuroraクラスターはマルチAZであっても利用できる。

https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/AuroraUserGuide/blue-green-deployments-overview.html#blue-green-deployments-limitations-general

### 4. パフォーマンステスト

パフォーマンステストのために本番のRDSをクローンしてデータをマスクしていった。

クローンDBをテスト環境のEC2に接続し直すと、CPU使用率が100%で高止まりしてしまいテスト環境で動かせなかった。

原因としてはEC2のスペックが本番と同等になっていないこと。

EC2のスペックを検討して構築しなおした上でテストする時間がなかったため、Green環境で`OPTIMIZE TABLE`を実行して実行計画を最適化した上で、移行後に遅いクエリがあったら適宜対応する方針で進めていった。

### 5. 移行テスト

Blue/Green Deploymentを利用して環境移行の動作確認を行なった。

確認してみると、Blue環境からGreen環境の切り替え時に10分間程度、Green環境のエンドポイントを参照できずにエラーとなる時間帯があった。

おそらくRails側でDBのコネクションキャッシュが効いている可能性があり、その時間はどうしてもサービスダウンせざるを得ないということ分かった。

### 6. 本番のBlue/Green環境作成

Blue/Green環境を作成し、Green環境で`OPTIMIZE TABLE`を実行した。

### 7. 本番移行

深夜時間帯に自分一人でBlue/Green Deploymentを行なった。

### 8. 移行後のスロークエリを修正

`COUNT(*)` 系のクエリや集計系のクエリが重すぎてタイムアウトするという問い合わせが多発。

データが多い企業は画面を開くとタイムアウトするという報告が上がってきていた。

まず、発行されているクエリの実行計画を確認して、他のデータを使って対象件数を絞り込めないか検討してhotfixでリリース。

さらにBIツールのクエリもタイムアウトしていたので、クエリの実行計画を確認しながらクエリを最適化したり、ヒント句を使ってINDEXを使うように指定したりして対応した。

### 9. 移行後にGlueジョブからRDSのリーダーインスタンスに接続できない

Glueジョブで集計処理を行っており、RDSのリーダーインスタンスに接続して集計している。

その集計処理がタイムアウトしてしまっており、集計数値が欠損する事象も発生した。

ひとまずライターインスタンスに接続して集計処理を再開させ、過去文のデータをもとにGlueジョブ動かし、欠損した集計数値の復旧を行った。

### 10. テスト用に作成したRDSの削除

テスト用に作成したRDSを削除する必要があったので削除していった。

ここで誤って本番RDSを誤停止してしまい、1時間ほどサービスダウンさせてしまった。

すぐに復旧させたが、エンジニア人生で中々経験できないミスをしてしまった。

## 次同じ失敗を起こさないために

- 移行タスクはPJとして複数人で進めていくこと
- インフラ更新系は複数人で作業すること
- パフォーマンステスト環境を用意しておくこと

ここまでの作業はほぼ自分一人で進めてしまっており、移行日も当初の予定より遅れて実施した。

自分がリーダーポジションであり、チームメンバーも自分以外ジュニアで、PMもおらずという状況だったので、自分一人で背負いすぎてしまった感じが否めない。

これをタスクとして捉えてしまったのが良くなかった。こういったタスクはPJとして複数人を無理にでもアサインして進めるべきだった。

そうすれば、移行前の準備作業や予定日調整を行えるリソースの余白が生まれていたと思う。

ただ、詰まるところ自分の調整力不足の無さを痛感したので、この教訓を次に活かしたい。


Aurora MySQL 3系へのアップグレードで失敗した話


## 概要
失敗から学ぶRDBの正しい歩き方を再度読み直したら、MySQLのギャップロックとネクストキーロックの理解が曖昧になっていたので、復習がてらまとめてみた


## ギャップロック
ギャップロックとは

> ギャップロックは、インデックスレコード間のギャップのロック、または最初のインデックスレコードの前または最後のインデックスレコードの後のギャップのロックです。 たとえば、SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;では、範囲内の既存のすべての値間のギャップがロックされているため、カラムにそのような値がすでに存在するかどうかにかかわらず、他のトランザクションが 15 の値をカラム t.c1 に挿入できなくなります。

https://dev.mysql.com/doc/refman/8.0/ja/innodb-locking.html#innodb-gap-locks


存在しない単一レコードの取得や削除、範囲指定でレコード取得などを行う場合に発生する。

### 具体例
下記のようなテーブルを考える。

```plaintext
mysql> desc test;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int          | NO   | PRI | NULL    | auto_increment |
| value | varchar(256) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
2 rows in set (0.01 sec)

mysql> select * from test;
+----+-------+
| id | value |
+----+-------+
|  1 | foo   |
|  5 | test  |
|  6 | test  |
|  8 | baz   |
|  9 | baz   |
+----+-------+
```

#### 存在しない単一レコードの取得/削除

トランザクションAで存在しないレコードを取得(空振り)すると、id=2..4にギャップロックが発生する。

id=3を中心にして、存在するレコードのid=1,5の間は全てギャップロックされる。

削除の場合も同じ事象。

##### トランザクションA

```plaintext
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select test from a where id = 3 for update;
Empty set (0.00 sec)
```

##### トランザクションB

```plaintext
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

# トランザクションAのギャップロックによって待機が発生
mysql> insert into test values (2,"test");
mysql> insert into test values (3,"test");
mysql> insert into test values (4,"test");
```

#### 範囲指定でレコード取得

範囲指定でレコードを取得した場合、存在しないレコード列までギャップロックするので、先ほどと同じようにINSERTするとロック待ちが発生。

トランザクションAでid=5..9を範囲指定して取得すると、id=6,7もギャップロックされる。

そのため、トランザクションBでロック待ちが発生する。

また、ギャップロックは競合しないのでレコード取得は可能。

##### トランザクションA

```plaintext
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where id between 5 and 9 for update;
+----+-------+
| id | value |
+----+-------+
|  5 | bar   |
|  8 | baz   |
|  9 | baz   |
+----+-------+
3 rows in set (0.00 sec)
```

##### トランザクションB

```plaintext
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

# トランザクションAのギャップロックによって待機が発生
mysql> insert into test values (6,"test");

# ギャップロックは競合しないので取得は可能
select * from a where id = 6 for update;
```

### ネクストキーロック

ネクストキーロック = レコードロック + ギャップロック

> インデックスレコードのレコードロックと、インデックスレコードの前のギャップのギャップロックの組み合わせです。

https://dev.mysql.com/doc/refman/8.0/ja/innodb-locking.html#innodb-next-key-locks

ギャップロックでロックした後、次の実レコードが存在する行をレコードロックしたものがネクストキーロックになる。

```plaintext
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from test where id < 4 for update;
+----+-------+
| id | value |
+----+-------+
|  1 | foo   |
+----+-------+
1 row in set (0.00 sec)
```

```plaintext
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

# id=1はレコードロックがかかっているのでロック待ち
mysql>　select * from test where id = 2 for update;

# id=2 はギャップなのでギャップロックがかかるが、ギャップロックは競合しないので取得可能
mysql> select * from test where id = 2 for update;
Empty set (0.00 sec)

# id=3はギャップロックがかかっているのでロック待ち
mysql> insert into test values (3,"test");

# id=5はid=2-4の次に存在するレコードなので、ネクストキーロックがかかっているのでロック待ち
mysql>　select * from test where id = 5 for update;

# id=6は今回ロックされたギャップではないのでinsert可能
mysql> insert into test values (6,"test");
Query OK, 1 row affected (0.00 sec)
```

## ロックの存在理由
ファントムリード(他のトランザクションで挿入してコミットしたレコードを読み込めてしまう)の発生を防ぐため。

InnoDBのデフォルトのトランザクション分離レベルは、`REPEATABLE-READ`なのでそれが発生しないようにするためのロック。

`READ COMMITTED`場合は、ギャップロックを無効にできる。

https://dev.mysql.com/doc/refman/8.0/ja/innodb-locking.html#innodb-gap-locks


## 所感

Railsアプリケーションでギャップロックを発生させるようなコードを書いて、指摘をもらったことを思い出した。

不要なレコードを定期的に削除して3ヶ月分だけ残すためのworkerを実装していて、下記のようなクエリを書いていた。

```ruby
Model.where(created_at: ..Time.zone.now.ago(3.month)).limit(1000).delete_all
```

これがslow queryになっていたので、id直指定で取ってきてdelete_allするように修正した。

これにより存在するレコードだけを取得してギャップロックを発生させないようにしたのを思い出した。

```ruby
ids = Model.where(created_at: ..Time.zone.now.ago(3.month)).limit(1000)
return if ids.blank?
Model.where(id: ids).delete_all
```


Kdevlog

Aurora MySQL 3系へのアップグレードで失敗した話

InnoDBのギャップロックとネクストキーロックについておさらい