Amazon RDS for MySQLの空き容量の確認と対策

# はじめに
RDSの[RI](https://aws.amazon.com/jp/rds/reserved-instances/)の期限が近づいてきて、今年も去年と同じでいいかなと、ふとRDSを確認したところ、RDSの空き容量がかなり減っていたことが発覚。(←社内用サーバだから普段放置だったけど、何気に空き容量3%切ったアラームもRDSダッシュボード上に出ていた。) RDSの空き容量の確認方法と空き容量を増やすための対策（主にフラグメンテーションの解消法）について、自分用メモ。

## 前提条件
* RDS
* MySQL 5.6
* InnoDB （長期運用によりdata_freeの容量が増える。）
* 社内用サーバ （重要度：低。シングル構成。データ容量は数百GB。複数DBが同居。データの挿入は分単位、削除は時単位でやってるようなサーバ。）

## 流れ
* 現状確認 : 状況・原因の把握とスケジュール決め。
* 暫定対応 : 不要なDBやテーブルがあれば手っ取り早かったが今回はなかったし、まだ期限に余裕もあったので、何もせず。
* 妥協案検討 : 優先順位が低かったため、タイムアップ用の、とりあえずの妥協案作成。（今回だと「RDSのストレージ容量の拡張」案）
* 対策案検討 : 時間が取れる範囲でより良い案を検討。
* 対策案実施 : 関係者に連絡して作業実施！
* 残処理 : そもそも監視されてなかったので監視設定入れたり。データ容量を増やさないにはどうしたらいいか考えたり。


# 現状確認

## RDSの空き容量確認方法
RDSの空き容量は、RDSダッシュボード、もしくは、CloudWatchダッシュボードから確認することが出来る。（AWS外で別途監視ツールを導入している場合はそっちでもいい。）時系列で確認し、どのくらいのペースで容量が減っているのか把握、猶予期間を計算する。以下はRDSダッシュボードからの確認方法。

1. RDSダッシュボード>インスタンス
2. 空き容量を表示させたいインスタンスを選択
3. 「モニタリングを表示」ボタンをクリック
4. 「Free Strage Space (MB)」の項目を確認


## DB毎のデータ量確認SQL
どのDBが容量を食っているのか確認。

```sql
SELECT 
  table_schema "DB", 
  sum( table_rows ) "Table Rows",
  sum( data_length + index_length ) / 1024 / 1024 / 1024 "Data (GB)", 
  sum( data_free )/ 1024 / 1024 / 1024 "Free Space (GB)" 
FROM information_schema.TABLES 
GROUP BY table_schema;
```

## テーブル毎のデータ量確認SQL
特定のDBが容量を占めていて、DB丸ごと削除できない場合などは、更にどのテーブルの容量が大きいのか確認。

```sql
use データベース名;
SELECT 
  table_name "Table",
  engine,table_rows "Table Rows", 
  ( data_length + index_length ) / 1024 / 1024 / 1024 "Data (GB)",  
  ( data_free )/ 1024 / 1024 / 1024 "Free Space (GB)" 
FROM information_schema.TABLES 
WHERE table_schema=database() 
ORDER BY (data_length+index_length) desc limit 10;
```

# 対策
## 不要なDBやテーブル、データがあれば削除
データ削除の場合は最適化コマンドも一緒に試すといい。

```sql
-- DBを削除する場合
SHOW DATABASES;
DROP DATABASE データベース名;
SHOW DATABASES;

-- テーブルを削除する場合
use データベース名;
SHOW TABLES;
DROP TABLE テーブル名;
SHOW TABLES;

-- データを削除する場合
use データベース名;
SELECT count(*) FROM テーブル名 [WHERE 条件];
DELETE FROM テーブル名 [WHERE 条件];
SELECT count(*) FROM テーブル名 [WHERE 条件];
```

## data_freeの容量が多かった時は最適化コマンドを試す
MySQLはDELETEしただけでは、容量が増えない。フラグメント化（歯抜け）の状態になってしまうらしい。ibdataというファイルが拡張しているらしい。テーブル毎のデータ量確認SQLの結果data_freeの容量が多く、長期間DBを運用している場合、最適化コマンドを実行し、空き容量が増えるか試して見るといい。後に記述した、DBやテーブルを作成し直すのも有効。また、最適化コマンドを実行する際は、対象テーブルの容量程度の空き容量が必要なのとテーブルのロックに注意。
コマンドの進捗状況については、「SHOW GLOBAL STATUS LIKE 'Handler_write';」で確認可能なので、大きいテーブルで試す際は最適化コマンド実行前に確認しておくといい。

```sql
OPTIMIZE TABLE テーブル名;
　か
ALTER TABLE テーブル名 ENGINE InnoDB;
```

参考：[mysql 5.6 の場合の仕様](https://dev.mysql.com/doc/refman/5.6/ja/optimize-table.html)
　　　[MySQLでALTER TABLE文の進捗状況を確認する](http://d.hatena.ne.jp/sh2/20110615)
　　　[MySQLをOPTIMIZEしてフラグメント化を解消する](http://imys.la/shun/archives/3318)


## DB再作成
DB再作成でもdata_freeの容量を減らせる。
dumpのオプションはやろうと思えば細かく指定できるので、便利。まるごと他のRDSに移行させたり、特定DBだけ移行したり、同じRDSにDB名を変えて入れ直したり出来る。
大規模DBを移行する時は、パラメータ変更が必要になることも。
※今回は結局DB単位・一部はテーブル単位で移行して、旧RDS→新RDSに切り替えた。パラメータも適宜変更した。

```
# 旧RDS→新RDSにまるごと移行
mysqldump -h old.X.X.rds.amazonaws.com -u ユーザ名 -pパスワード | mysql -h new.X.X.rds.amazonaws.com -u ユーザ名 -pパスワード

# DB単位で移行(新RDSでDB作成してから)
CREATE DATABASE データベース名
mysqldump -h old.X.X.rds.amazonaws.com -u zabbix -pパスワード データベース名 | mysql -h new.X.X.rds.amazonaws.com -u zabbix -pパスワード データベース名
```

## テーブル再作成
テーブル再作成でもdata_freeの容量を減らせる。
INSERTの時、WHERE句追加で必要なデータのみを入れることも出来る。

```sql
CREATE TABLE テーブル名_new LIKE テーブル名;
INSERT INTO テーブル名_new SELECT * FROM テーブル名;
RENAME TABLE テーブル名 TO テーブル名_old, テーブル名_new TO テーブル名;
DROP TABLE テーブル名_old;
```


# RDSお役立ちTips
## RDSで容量が不足したらどうなるのか？
RDSでストレージ不足（STORAGE_FULL）になると、インスタンスに接続できないか、インスタンスを再起動出来なくなったりするらしい。この状態までにはならなかったが、恐ろしい。。

参考：[RDS DB インスタンスでのストレージ不足時に発生する問題を解決する方法を教えてください。](https://aws.amazon.com/jp/premiumsupport/knowledge-center/rds-out-of-storage/)

## RDSのスナップショットからの復元
RDSは設定すれば自動バックアップをとってくれるので便利。簡単に復元もできる。
削除や最適化を試すときは、本番稼働中のRDSではなく、スナップショットから復元したRDSで試した方がいい。

1. RDSダッシュボード>スナップショット
2. 復元したいスナップショットを選択
3. 「スナップショットの復元」をクリック
4. DBインスタンス識別子などを入力
5. 「DBインスタンスの復元」をクリック

参考：[DB スナップショットからの復元](https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/USER_RestoreFromSnapshot.html)


## RDSのスナップショットの作成
直前にスナップショットを作成しておけばその時点に戻せる。または、1日1回の自動バックアップから特定の時点に復元出来るらしい(未検証)。

1. RDSダッシュボード>インスタンス
2. インスタンスを選択
3. インスタンスの操作>スナップショットの取得

参考：[DB スナップショットの作成](
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/USER_CreateSnapshot.html)
　　　[特定の時点への DB インスタンスの復元](
https://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/USER_PIT.html)


## RDSのストレージ容量の拡張
色々試してみて、やっぱり容量を増やしたい時は、以下の方法でインスタンスのクラスやストレージのサイズを変更可能。ただし、インスタンスのクラスやストレージ容量の範囲には限りがあり、無限に増やせるものではない。また、拡張には時間がかかり（t2.microでも5GB->6GBの変更が10分程度。再起動は発生していなかった）、一度拡張すると縮小はできない（エラー「ストレージ割り当ての量を減らすことはできません」）ので注意。[料金はGB単位](https://aws.amazon.com/jp/rds/pricing/)。

1. RDS>インスタンス
2. ストレージ容量を変更したいインスタンスを選択
3. 右クリック
4. 「変更」を選択
5. ストレージ割り当て部分を変更（現在は、最小: 5 GB、最大: 6144 GB）


## パラメータチューニング
RDSのパラメータは、インスタンス毎に「パラメータグループ」で設定したものが適用される。パラメータグループ自体はRDSダッシュボード>パラメータグループ>名前の左側のアイコンをクリックすることで詳細を確認出来る。
RDSのパラメータはデフォルトのパラメータグループを使用しているとパラメータの変更が出来ない。RDSでインスタンス作成する際は、デフォルト以外に変更する気がなくても、デフォルト以外のパラメータグループを作成し設定した方が無難。パラメータグループの変更は再起動不要だが、パラメータ変更＆適用には再起動が必要なものもある（[動的なパラメーター変更：再起動不要、静的なパラメーター変更：再起動必要](http://docs.aws.amazon.com/ja_jp/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html)）ので、注意。SQLコマンド「set global X=XX;」で変更することは、RDSでは権限がないため出来ない。

便利：[パラメータ一覧](https://dev.mysql.com/doc/refman/5.6/ja/server-system-variables.html)（日本語だし、静的かどうかも分かる）

## (追記)クラウドへ移行することで解決できる部分とそうでない部分
「SQLチューニング」「統計情報の更新」「フラグメンテーションの解消」の3つはクラウド移行後も変わらず残る部分。今まで明確に認識していなかったので、気をつけたい。

参考：[AWS Black Belt Online Seminar RDBのAWSへの移行](https://d0.awsstatic.com/webinars/jp/pdf/solution-casestudy/20160920-_AWS-BlackBelt-RDB-migration.pdf)

# その他Tips
## 容量の監視
CloudWatchからもアラームを設定可能。100%になってからでは遅いので、とりあえず何らかの監視は入れておくべき。

## 容量見積り
RDSを導入時に容量は見積もっておくと思うが、長期間運用していると当初とずれが出てきたりするので、適度なタイミングで容量再見積もりした方が良さげ。

## ベンチマーク測定
RDSでもmysqlslapでベンチマーク測定出来る。空き容量を増やし終わった後、動作が早くなったような気がしたので、前後で測定してみたが、ほぼ変わらなかった。。

```
mysqlslap \
  --host=X.X.X.rds.amazonaws.com \
  --port=3306 \
  --engine=innodb \
  --auto-generate-sql \
  --auto-generate-sql-load-type=read \
  --auto-generate-sql-add-autoincrement \
  --number-char-cols=3 \
  --number-int-cols=5 \
  --number-of-queries=1000 \
  --concurrency=3 \
  --iterations=10 \
  --user=ユーザ名 \
  --password=パスワード
```

参考：[mysqlslapを使ってRDSのMySQLについて各クラスのパフォーマンス測定](http://dev.classmethod.jp/cloud/aws/amazon-rds-performance-test-by-mysqlslap/)


# まとめ
* RDSをお掃除したら、データ容量が半分未満になった。
* たまたまだけど、データ容量100%になるのを事前に防げた。（監視大事！）
* お掃除せずにデータ容量増加した場合に発生する無駄な課金を防げた。RIも更新できた。
* RDS便利だけど、EC2やオンプレミスと一緒のところは一緒。
* もっと楽ないい方法ないのかな？（数年後に誰かが同じようなことをやるハメになりそう。。cronでOPTIMIZEコマンド流すとかはなし）