はじめに
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インスタンスの復元」をクリック
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コマンド流すとかはなし）