大規模ソーシャルゲーム運用で経験した MySQL(RDS) のストレージ管理で押さえておくべき勘所

はじめに

この文章では, 大規模ソーシャルゲーム運用で実際に経験したストレージ管理の話を書いています. ソーシャルゲームに限らず, ユーザーの情報をデータベースに保持しておく必要があるサービスを運用していると, データベースのストレージ容量が足りなくなるといった問題が発生することがあります. 運用中にそういった問題を解決する経験を通じて, いろいろ学ぶことがあったのでまとめておきたいと思います.

背景

割りと大きめなサービスを運用中に, MySQL(RDS) のストレージ容量が枯渇しそうになったことがあります. そのサービスではデータが溜まるスピードがとても速く（見積もりが甘かったのか, されていなかったのか）, 監視ツールからはアラート通知が発砲され, どんどんストレージ容量を食っていきます. パーティショニングもされていません. そんな状況で, ストレージ容量を確保するための対応方法を策定し, 安全に実施する必要がありました.

環境

• Amazon RDS for MySQL
• MySQL5.6
• ストレージエンジン InnoDB

今回のケース

さて, 今回紹介するケースを簡単に説明します. 状況としては, ストレージの空き容量が足りなくなってきたので不要データを削除してストレージ容量を確保しないといけないという状況です. どのテーブルの不要データを削除するかを精査し（精査方法は下記）, 以下のような特徴を持ったテーブルに対して実施することになりました.

• ユーザーが行動した履歴情報を保持している
• 仕様上ユーザーは X ヶ月以上前の履歴情報は閲覧できなくなる
• 一日の中で INSERT や UPDATE が行われない, SELECT だけが行われる時間帯がある（つまりユーザーは履歴情報の閲覧のみしか行わない時間帯がある）

もしかしたら今回のケースは一般的には稀なケースかもしれませんが, これから紹介する対応方法などは他のケースでも応用できると思うので, 参考になれば良いかなと思います.

削除対象のテーブルとデータを決める

上記した今回のケースで不要データを削除するテーブルの特徴を挙げましたが, この不要データを削除するテーブルはどうやって決めるのかを説明します. 不要データを削除することでストレージ容量をたくさん確保できるようなテーブルに対して行わないと意味がないので, 以下のような手順で精査して決めると良いでしょう.

1. 削除できる不要なデータがたくさんあるテーブルを探す

下記のようにテーブル毎で使用しているデータ容量を確認するクエリを発行し, どのテーブルにデータが溜まっているのかを確認します.

その際に,

• 削除できる不要なデータが存在する（削除できるというのは, サービスのシステム面, 仕様面の両面で削除できることが確認できている状態）
• 不要データを削除するとストレージ容量が大幅に確保できる

といった点に着目して不要データを削除するテーブルを決めると良いでしょう.

SELECT
  table_name,
  engine,
  table_rows,
  avg_row_length,
  data_free,
  floor((data_length + index_length) / 1024 / 1024) AS all_MB, # 総容量
  floor((data_length) / 1024 / 1024) AS data_MB, # データ容量
  floor((index_length) / 1024 / 1024) AS index_MB # インデックス容量
FROM information_schema.tables
WHERE table_schema = database()
ORDER BY (data_length + index_length) DESC;

データベースとテーブルのサイズを確認する方法

2016/9/20 追記
data_free を含めると delete などで未使用になった領域の容量も見れるので, 不要データを削除するテーブルを決めるときの材料になると思います.

2. 残すデータと削除するデータを決める

対象のテーブルが決まったら, そのテーブルのデータの中からどのデータを削除するかを決めます. 例えば今回のケースだと, 直近 X ヶ月以上前のデータは削除できるので, 現在から X ヶ月以上前のある時点のデータより古いデータを削除して, ある時点のデータより新しいデータを残せば良いでしょう.

また, 削除作業の都合上, どのデータを削除するかよりもどのデータを残しておくかが重要なので, 以下のようなクエリを実行して残すデータの抽出にどれくらい時間が掛かるかも見ておきましょう（重いクエリなので検証用のスナップショットで）.

SELECT * FROM target_table WHERE created_at >= '2015-XX-XX XX:XX:XX';

ここで気を付けることは, データ量が多く created_at にインデックスが付いていない場合, このデータ抽出のクエリにとても時間が掛かってしまいます. なるべく主キーやインデックスが付いているカラムを使って残すデータを抽出すると時間も短縮できてデータベースにも負荷が掛からないので良いでしょう.

SELECT * FROM target_table WHERE id >= XXXXXXXX;

これで id >= XXXXXXXX のデータを残しておこう, と決めれます.

不要データを削除してストレージ容量を確保する

上記の手順で削除対象のテーブルとデータが決まったので, ここからは実際に不要データを削除してストレージ容量を確保する方法を説明します（下記でも補足しますが, 本番で実施する前には検証環境で確認し, バックアップをしっかりとってから実施しましょう）.

1. 同じスキーマの新しいテーブルを作成する

不要データを削除するテーブルに対して SHOW CREATE TABLE を発行し, その CREATE TABLE 文を使って同じスキーマの新しいテーブルを作成します. その際に気を付けることは, AUTO_INCREMENT = XXXXXXXX を削除して AUTO_INCREMENT 値をリセットするかどうか, そこはサービスによりけりだと思うので精査してください（例えば AUTO_INCREMENT 値をコード内で使っている場合は, 簡単にはリセットできないはずです）.

CREATE TABLE `target_table_new` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  ...
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2. INSERT ... SELECT する

新しいテーブルを作ったら, INSERT ... SELECT を使って残しておくデータだけを WHERE 句で指定して新しいテーブルに INSERT します. ここで注意することは以下の点です.

• データ量が多くなればなるほど時間が掛かる（ただし WHERE 句で指定するカラムにインデックスが付いていれば当然速くなる）
• 新しいテーブルに必要なデータを INSERT するということは, その分だけストレージ容量を消費するので, その分のストレージ容量が必要となる（この手順に沿って INSERT したらストレージ容量が足りなくなってしまったということにならないように注意すること）

INSERT INTO target_table_new SELECT * FROM target_table WHERE id >= XXXXXXXX;

INSERT ... SELECT 構文

3. RENAME TABLE する

INSERT ... SELECT が終わったら, RENAME TABLE して古いテーブルと新しいテーブルを入れ替えます. これは一瞬で終わります.

RENAME TABLE target_table TO target_table_old,
target_table_new TO target_table;

RENAME TABLE 構文

4. DROP TABLE する

テーブルの入れ替えが終わったら, エラーや不具合が発生していないことを確認し, 最後は DROP TABLE で古いテーブルを削除してしまいましょう. これでストレージ容量が確保することができます.

DROP TABLE target_table_old;

間違って必要な方のテーブルを DROP TABLE しないように気をつけましょう.

その他の対応方法

新しいテーブルを作成して古いテーブルと入れ替える方法

今回のケースとは違い, 削除対象のテーブル内に残しておくべきデータがない場合（全部データを削除できる場合）は, この方法が適用できるはずです.

1. 削除対象のテーブルを古いテーブルとする
2. 古いテーブルと同じスキーマの新しいテーブルを CREATE する
3. RENAME TABLE して, 古いテーブルと新しいテーブルを入れ替える
4. 問題なければ, 古いテーブルを DROP して削除する

DELETE + ALTER TABLE を行う方法

今回のケースで紹介した方法以外にも不要なデータを DELETE して, DELETE 完了後に ALTER TABLE で内部的に新しいテーブルを作り直すことで削除したデータ分のストレージ容量を確保するという方法もあります. しかし, 削除するデータ量が多いと時間が掛かりますし, ALTER TABLE にも時間が掛かるのであまりおすすめではありません（実際今回のケースでも時間が掛かってしまうのでこの方法は却下しました）. 別のケースでは有効な場合もあるかと思うので, 選択肢として持っておくと良いでしょう.

MySQL 容量確保のためのデータ削除方式

パーティショニングを適用する方法

ぶっちゃけ最初からパーティショニングを適用しておくべきだと思います. しかし, パーティショニングされていない, パーティショニングし忘れていた, 何らかの理由でパーティショニングができない, などといった場合は今回紹介した方法を参考にして対応すれば良いでしょう. パーティショニングについて詳しくは述べませんが, 大きいサービスになるととても便利な機能なので, 覚えておくと良いでしょう.

MySQLパーティショニングの設定、追加、削除、再構成

ストレージ管理において大切なこと（勘所）

最後にストレージ管理において大切なことを挙げておきます. なにやら前後してしまった部分もあるのですが, ここで挙げることをしっかり理解した上で, 削除作業などのストレージに対する作業を行うようにしましょう.

削除作業前に検証とバックアップをしっかりやる

当然ですが, ユーザーの大切な情報やデータが入っているストレージに対して作業を行うときは慎重に行わなければなりません. 作業前に本番 RDS のスナップショットをとって, 検証し, 確実に作業が行えることを確認してから実施しましょう. バックアップもしっかりとっておきましょう.

メンテナンスの有無を見極める

今回のケースではメンテナンスは必要ありませんでした. 一日の中で INSERT や UPDATE が行われない時間帯があったためです. INSERT や UPDATE が随時行われるテーブルの場合, 今回の方法で対応を行うと INSERT ... SELECT と RENAME TABLE の間で必要なデータが古いテーブルのみに INSERT されて, RENAME TABLE した後にデータが欠損している状態になります. 必要ならばメンテナンスを入れて対応を行いましょう.

サービスの可用性は高く保つ

とはいえサービスの可用性はできるだけ高く保つべきでしょう. メンテナンスでサービスを止めることは, 大きな機会損失となります. 止むを得ない理由がある場合以外は, メンテナンスでサービスを止めることなく対応できる方法を模索するべきでしょう.

ALTER TABLE の動作を知っておく

ALTER TABLE は内部的に CREATE TABLE と DROP TABLE を行っています. 一見スキーマの変更だけを行っているように見えますが, 実際には新しいスキーマを持った新しいテーブルが作られているのです. このように動作するが故に DELETE + ALTER TABLE によるストレージ容量確保が可能になります.

ALTER TABLEを上手に使いこなそう。

削除作業中のコピー分の容量を忘れないようにする

今回のケースで紹介した INSERT ... SELECT や DELETE + ALTER TABLE を行う方法では, いずれの場合でも新しいテーブルを作成し必要なデータを新しいテーブルに入れ直す作業（コピー）が発生します（ALTER TABLE の場合は内部的に）. つまりコピー分のストレージ容量が必要となるので, しっかり頭に入れておきましょう. コピー分のストレージ容量もないという状態になってしまったら, もう恐らくストレージの増設しか手がないでしょう.

MySQL のストレージ容量は DELETE しただけでは空かない

基本的に MySQL では DELETE してデータを物理的に削除しても, 削除した分のストレージ容量が増えることはありません. これは MySQL のデータベースのデータを保持しておくための ibdata というファイルがどんどん拡張してストレージの容量を食っていくのですが, データを DELETE しても ibdata ファイル上ではその削除データ分の空き容量は確保されたままの状態になり, 削除しても容量は減らないということだそうです.

行を削除する時、その行はディスク上で削除されたとマークされるだけで、実際は後で行をinsert/updateした時に使われるようInnoDBのファイル内では確保されたままになり、容量は減らない。これは非常に古いMySQLのバグだ。

innodb_file_per_tableが有効な時にディスク容量を開放するには

MySQLで一度レコードが増えたら不要なレコードを消してもディスク容量が減らない

RDS のストレージ容量は減らせない

一度割り当てたストレージ容量を, 使わなくなったからといって減らすことはできません. ストレージ容量が枯渇しそうになったときにとりあえず増やしておいて, 後で不要データを削除してから減らそうなんてことはできません.

DB インスタンスに割り当てるストレージの量をギガバイト単位で指定します。許容される最小値は 5 GB、最大値は 6 TB です。DB インスタンスの変更時、ストレージの量を増やすことができますが、割り当てたストレージの量を減らすことはできません。

MySQL データベースエンジンを実行する DB インスタンスを変更する

RDS の増設できるストレージ容量には最大値がある

当然の話ではありますが, 増設できるストレージ容量には最大値があり, 現在では最大 6TB まで増設することが可能です.

Provisioned IOPS および汎用（SSD）ストレージタイプを使用した場合、MySQL、MariaDB、PostgreSQL、Oracle RDS の各 DB インスタンスで最大 6 TB のストレージ、SQL Server RDS の DB インスタンスで最大 4 TB のストレージを作成できます。

Amazon RDS のストレージ

大量データ（レコード）への DELETE は危険

検証用のスナップショットで大きなテーブルの不要なデータを削除してストレージ容量を確保しようとしたことがあります. DELETE して ALTER TABLE tbl1 ENGINE InnoDB; とかをすれば不要なデータがなくなった状態で新しいテーブルに入れ替えることができるので, ストレージ容量を確保することができます.

しかし, 大きいテーブルの大量のデータに対する DELETE はとても危険です. なぜなら RDS のマシンパワーをとても使ってしまうからです. 細かいことは忘れてしまいましたが, CPU 使用率が 100% に張り付いてしまったり, DELETE が完了した後数十分の間対象のテーブルに接続できなくなったりと, 危険だなあと体感しました. 大きなテーブルの大量データに対する DELETE + ALTER TABLE は現実的な手段ではないと思います.

実際のデータ量以上にストレージを消費する現象

たまにデータベースに載せているデータ量以上にストレージを消費している現象が見られます. 原因は良く分からないのですが, 運用に入ってから一度も ALTER TABLE を掛けていない古いテーブルとかがある場合, そのテーブルの内部の何者かがストレージを少しずつ使っているようです. そのときは ALTER TABLE とかでテーブルを作り直すと解決することがあるので、検証して確かめて実施すると良いでしょう.

適切な閾値を決めて監視とアラートをしっかりやる

気付いたらストレージ容量が枯渇してサービスが停止していました...なんてことにならないよう, 残りストレージ容量の監視と適切な閾値を決めてアラートを発砲して通知するような環境を作りましょう.

事前に見積もりや対策をする

これは正直難しいです. 数ヶ月も先のことに対応しておくというのは難しいことです. しかし, できるだけの努力はしましょう. サービスの仕様で直近一ヶ月以上前のデータは閲覧できなくしたり, データベースに保持するデータが多くならないように設計したり, 保持するデータを圧縮して小さくしたり, パーティショニングを最初から適用しておいたり, いろいろできることはあるのでしっかりやっておくと良いでしょう.

コストを意識する

不要データを削除するための対応方法を策定する作業にも当然人件費が発生します. この策定作業（検証やデバッグなども含む）に数日も掛かるようであれば, お金でストレージ容量を増やすという手段をとるべきかもしれません. 将来のために数日掛けてでも良いからしっかり対応方法を策定しておくべきかもしれません. いずれにせよ, 人件費というコストが掛かっていることをしっかり意識し, 適切な対応を行うべきでしょう.

時間がないときは素直にストレージを増設しよう

とはいえ時間がないときは, 素直にストレージを増設することを考えましょう. 時間がない中, 焦って対応して取り返しの付かないミスを起こすと最悪です. ビジネスサイドや上司に相談し, 無理しないようにしましょう. 実際ストレージは安いですし＼(^o^)／

さいごに

長くなりましたが, 同じような問題に直面した人のお役に立てれば幸いです.