【AWS】RDS for MySQLで共有テーブルスペースに構成変更する際の所要時間を実測してみた

今回の課題

こんにちは植木和樹です。RDS for MySQLを使用していたところ以下のような見慣れないメッセージが通知されてきました。

DB Instance XXXX-XXXX has a large number of tables and
has the parameter innodb_file_per_table set to 1, which can increase
database recovery time significantly.
Consider setting this parameter to 0 in the parameter group
associated with this DB instance to minimize database downtime
during reboots and failovers.

あわせてメッセージには下記URLを参照するよう指示がありました。

Working with InnoDB Tablespaces to Improve Crash Recovery Times

どうやらMySQLに大量のテーブルがあると、DBリブートやフェイルオーバー時のリカバリ処理に時間がかかるかもしれない、という警告のようです。今回はこの警告の内容と対処方法を検討してみたいと思います。

警告の内容について

MySQLにはinnodb_file_per_tableというパラメーターがあります。これが"1"（テーブル毎に分割する）の場合、テーブルごとに専用テーブルスペースが作成されます。テーブルのデータやインデックスは専用テーブルスペースに格納されるわけです。そしてテーブルごとにテーブルスペース（領域）が用意されているため、DROP TABLEした際にそのテーブルスペース分の領域がそのまま開放され再利用可能になるというメリットがあります。そのためAmazon RDSでは"1"（テーブル毎に分割する）を推奨しており、初期パラメーターでもそのようになっています。

しかし「余りにも大量のテーブルスペース」になると、リカバリ処理に予想外の時間が掛かってしまう場合があります。これはMySQLのクラッシュリカバリプロセスが各テーブルスペースのヘッダーを読み込む必要があり、大量に分割されたテーブルスペース一つ一つを読みにいくと多大な時間がかかるためです。たとえば100万個のテーブルスペースで構成されたMySQLのクラッシュリカバリに5〜8時間かかるケースもあるそうです。

「余りにも大量のテーブルスペース」の目安はRDSのストレージが「スタンダードの場合は1000テーブル以上」「Provisioned IOPSの場合1万テーブル以上」となります。

大量テーブルスペース問題の解消方法

すべてのテーブルが共有テーブルスペースを利用するよう設定を変更する

上記問題を解決する方法としてinnodb_file_per_tableを"0"（共有テーブルスペース使用）にし、すべてのテーブルを共有テーブルスペースに格納することが提案されています。このパラメーターはParameter Groupで変更することができます。

Parameter Groups変更しDBインスタンスを再起動してパラメーターを反映させた後、新規に作成されるテーブルについては共有テーブルスペースに格納されます。しかし既存のテーブルについては自動で共有テーブルスペースに格納されるよう再構成は行われません。別途ALTER TABLEを実行してテーブルごとに再構成する必要があります。

共有テーブルスペースへの再構成にあたっての新たな問題

このテーブルの再構成を行うにあたって、2つ新たな課題がでてきます。

1. 再構成中テーブルロックが発生し、テーブルに対するすべての読み書きができない
2. 再構成のために一時的に領域が必要になる

おそらくどちらの課題（時間、領域）もテーブルのデータ量に依存するものだと思うのですが、どの程度の時間と領域が必要になるのか参考値がありません。

参考値がない・・・それなら実測してみましょう。

テーブル再構成に必要な時間と領域を実測する

検証環境

RDS

インスタンスタイプ m1.small（スタンダードストレージ）

MySQL

5.6.13

データ１

日本郵政公社「ゆうびんホームページ」で公開されている住所の郵便番号（ke_all.csv、約20MB）

データ２

AWS Programmatic Billing AccessでS3に出力されるCSVデータ（aws_billing_detailed_line_items、約3.8GB）

<

h3>テスト用データベースを用意する

郵便番号データには日本語文字列が含まれていて、そのままではインポートに失敗するのでcharactersetをutf8に変更したデータベースを用意しています。DB Parameter Groupでcharactersetまわりをutf8に変更したパラメーターグループを作成してインスタンスに割り当てます。ただ初期データベースはlatin1で作成されるので、いったんDROP DATABASEしてから再作成しています。

データベースが作成されたらテーブルを作成しデータをインポートします。説明すると長いので作業の内容については省略させていただきます。

パラメーターを変更する

Parameters Groupを変更し、インスタンスをリブートします。

リブート後innodb_file_per_tableがオフになっていることを確認します。

mysql> show variables like 'innodb_file_per_table';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| innodb_file_per_table | OFF   |
+-----------------------+-------+
1 row in set (0.00 sec)

テーブルが共有テーブルスペースを利用するよう再構成する

それではデータ１（20MB）のテーブルを再構成してみましょう。

mysql> alter table ken_all engine='InnoDB';
Query OK, 123417 rows affected (4.13 sec)
Records: 123417  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='ken_all'\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: testdb
     TABLE_NAME: ken_all
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 124373
 AVG_ROW_LENGTH: 164
    DATA_LENGTH: 20496384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 53477376
 AUTO_INCREMENT: 131071
    CREATE_TIME: 2013-09-17 16:18:51
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.00 sec)

20MBデータが入ったテーブルの再構成は4秒で完了しました。

次にデータ２（3.8GB）のテーブルを再構成してみましょう。こちらはデータで3.8GB、インデックスで2.8GBの計6.6GBのデータ量です。

mysql> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='aws_billing_detailed_line_items'\G
*************************** 1. row ***************************
  TABLE_CATALOG: def
   TABLE_SCHEMA: testdb
     TABLE_NAME: aws_billing_detailed_line_items
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 7268328
 AVG_ROW_LENGTH: 525
    DATA_LENGTH: 3822059520
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 2810019840
      DATA_FREE: 5242880
 AUTO_INCREMENT: 12517276
    CREATE_TIME: 2013-09-18 15:15:26
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_bin
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.29 sec)

mysql> alter table aws_billing_detailed_line_items engine='InnoDB';
Query OK, 8172366 rows affected (18 min 27.05 sec)
Records: 8172366  Duplicates: 0  Warnings: 0

6.6GBのデータとインデックスの再構成は18分30秒で完了しました。これだけの時間テーブルへのIOができないとなるとサービス稼働中のメンテナンスは難しそうです。

また処理中の空き領域をCloudWatchで見た時の画像が以下になります。

14GBほどあった空き領域が7GBほどに減り、処理完了後に元に戻っています。データとインデックスの合計6.6GBとほぼ同じ領域を消費しています。

まとめ

m1.smallインスタンス（スタンダードストレージ）でテーブル再構成にかかる時間は3分/GB、100MBあたり20秒前後といったところでしょうか。また再構成に必要なデータ領域は元のデータ＋インデックスと同程度になるようです。データ領域についてはデータ量だけでなく、インデックス量も忘れずに考慮してください。

複数テーブルで大量データを扱うデータベースの場合は、かなり長い時間が再構成処理に必要になるためサービスを止めずに行うのは難しいと思われます。

サービス停止時間を最小限にしつつ再構成を行う方法としてリードレプリカを用いた方法もドキュメントでは提案されています。（Working with InnoDB Tablespaces to Improve Crash Recovery Times）

1. マスターデータベースのbackup retentionを有効にし、バイナリロギングを有効にしておく。
2. マネージメントコンソールやAWS CLIでリードレプリカを作成する。ただしリードレプリカの作成はクラッシュリカバリと同様の処理のため作成に時間がかかる場合がある。また再構成を行うためマスターデータベースよりも多いデータ領域にしておくこと。
3. read_only=0、 innodb_file_per_table=0に変更したパラメーターグループを作成し、レプリカのインスタンスに適用する。
4. ALTER TABLEを実行しテーブル再構成を行う。
5. テーブル再構成が終わったら、マスターデータベースとレプリカの同期がされていることを確認する。
6. （マスターデータベースへの書き込みをすべて停止したら）マネージメントコンソールやAWS CLIを使ってレプリカをマスターに昇格（Promote Replica）させる。その後アプリケーションの接続先を新マスター（元レプリカ）に切り替える。

リードレプリカを用いて、別のDBインスタンスで再構成をしておき、再構成完了後にアプリケーションの接続先を変更することでサービス停止を最小限にするわけです。ただし、これについても切り替え手順を事前に検証してから実施しましょう。