はじめに

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

前提条件

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

流れ

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

現状確認

RDSの空き容量確認方法

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

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

DB毎のデータ量確認SQL

どのDBが容量を食っているのか確認。

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丸ごと削除できない場合などは、更にどのテーブルの容量が大きいのか確認。

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やテーブル、データがあれば削除

データ削除の場合は最適化コマンドも一緒に試すといい。

-- 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';」で確認可能なので、大きいテーブルで試す際は最適化コマンド実行前に確認しておくといい。

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

参考：mysql 5.6 の場合の仕様
　　　 MySQLでALTER TABLE文の進捗状況を確認する
　　　 MySQLをOPTIMIZEしてフラグメント化を解消する

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句追加で必要なデータのみを入れることも出来る。

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 インスタンスでのストレージ不足時に発生する問題を解決する方法を教えてください。

RDSのスナップショットからの復元

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

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

参考：DB スナップショットからの復元

RDSのスナップショットの作成

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

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

参考：DB スナップショットの作成
　　　特定の時点への DB インスタンスの復元

RDSのストレージ容量の拡張

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

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

パラメータチューニング

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

便利：パラメータ一覧（日本語だし、静的かどうかも分かる）

(追記)クラウドへ移行することで解決できる部分とそうでない部分

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

参考：AWS Black Belt Online Seminar RDBのAWSへの移行

その他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について各クラスのパフォーマンス測定

まとめ

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

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