InnoDB テーブルスペースの操作による RDS for MySQL のクラッシュリカバリ時間の短縮
MySQL のすべてのテーブルは、テーブル定義、データ、およびインデックスから構成されます。MySQL のストレージエンジン InnoDB は、tablespace にテーブルのデータとインデックスを格納します。InnoDB は、データディクショナリおよびその他の関連メタデータを含むグローバル共有データスペースを作成し、テーブルのデータとインデックスを含めることができます。また、InnoDB は、テーブルおよびパーティションごとに個別のテーブルスペースを作成することもできます。これらの個別のテーブルスペースは、.ibd の拡張子を持つファイルに格納され、各テーブルスペースのヘッダーには、それを一意に識別する数値が含まれます。
Amazon RDS は innodb_file_per_table
と呼ばれる MySQL パラメータグループにパラメータを提供します。このパラメータは、InnoDB が、新しいテーブルデータとインデックスを共有テーブルスペースに追加するか (パラメータ値を 0 に設定)、または個別のテーブルスペースに追加するか (パラメータ値を 1 に設定) を制御します。Amazon RDS は、innodb_file_per_table
パラメータのデフォルト値を 1 に設定します。この設定により、個別に InnoDB テーブルを削除し、それらのテーブルで使用していたストレージを、DB インスタンス用として再要求することができます。ほとんどの場合、innodb_file_per_table
パラメータは 1 に設定することをお勧めします。
多数のテーブルがある場合 (スタンダード (磁気) または汎用 SSD ストレージを使用するときは 1,000 以上のテーブル、プロビジョンド IOPS ストレージを使用するときは 10,000 以上のテーブル) は、innodb_file_per_table
パラメータを 0 に設定する必要があります。このパラメータを 0 に設定すると、個別のテーブルスペースは作成されないため、データベースのクラッシュ回復時間を短縮できます。
MySQL は、クラッシュ回復サイクル中に個々のメタデータファイルを処理して、そこにテーブルスペースを格納します。MySQL が共有テーブルスペース内のメタデータ情報を処理するために必要な時間は、複数のテーブルスペースが存在するときに数千のテーブルスペースファイルを処理するために必要な時間と比べるとごく僅かです。テーブルスペース番号は、各ファイルのヘッダーに保存されているため、すべてのテーブルスペースファイルを読み込むための時間を集計すると、数時間かかる可能性があります。例えば、クラッシュ回復サイクル中に、スタンダードストレージの 100 万の InnoDB テーブルスペースを処理するには、5 ~ 8 時間かかる可能性があります。場合によっては、InnoDB がクラッシュ回復サイクル後に、追加クリーンアップが必要であると判断し、再度クラッシュ回復サイクルをスタートすることがあり、それによって回復時間が長くなります。クラッシュ回復サイクルには、テーブルスペース情報の処理以外に、トランザクションのロールバック、損傷したページの修復、およびその他の処理も必要であることに注意してください。
innodb_file_per_table
パラメータはパラメータグループ内にあるため、DB インスタンスを再起動しなくても、DB インスタンスが使用するパラメータグループを編集することで、このパラメータ値を変更できます。例えば、設定が 1 (個別のテーブルを作成する) から 0 (共有テーブルスペースを使用する) に変更されると、その後、新しい InnoDB テーブルが共有テーブルスペースに追加されますが、既存のテーブルには個別のテーブルスペースがそのまま残ります。InnoDB テーブルを共有テーブルスペースに移動するには、ALTER TABLE
コマンドを使用する必要があります。
複数のテーブルスペースを共有テーブルスペースに移行する
InnoDB テーブルのメタデータを固有のテーブルスペースから共有テーブルスペースに移動することができます。このコマンドは、innodb_file_per_table
パラメータ設定に従って、テーブルメタデータを再構築します。まず MySQL DB インスタンスに接続し、次に以下に示す適切なコマンドを発行します。詳細については、「MySQL データベースエンジンを実行している DB インスタンスへの接続」を参照してください。
ALTER TABLE
table_name
ENGINE = InnoDB, ALGORITHM=COPY;
例えば、次のクエリは共有テーブルスペースにない InnoDB テーブルごとに ALTER TABLE
ステートメントを返します。
MySQL 5.7 DB インスタンスの場合:
SELECT CONCAT('ALTER TABLE `', REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');
MySQL 8.0 DB インスタンスの場合:
SELECT CONCAT('ALTER TABLE `', REPLACE(LEFT(NAME , INSTR((NAME), '/') - 1), '`', '``'), '`.`', REPLACE(SUBSTR(NAME FROM INSTR(NAME, '/') + 1), '`', '``'), '` ENGINE=InnoDB, ALGORITHM=COPY;') AS Query FROM INFORMATION_SCHEMA.INNODB_TABLES WHERE SPACE <> 0 AND LEFT(NAME, INSTR((NAME), '/') - 1) NOT IN ('mysql','');
テーブルのメタデータを共有テーブルスペースに移動するために MySQL テーブルを再構築するには、一時的にテーブルの再構築のための追加ストレージ領域が必要になるため、DB インスタンスには使用可能なストレージ領域が必要です。再構築中は、テーブルがロックされ、クエリのアクセスが制限されます。小さなテーブルや、頻繁にアクセスされないテーブルの場合、これは問題にはならないことがあります。大きなテーブルや過酷な同時実行環境で頻繁にアクセスされるテーブルの場合は、リードレプリカでテーブルを再構築できます。
リードレプリカを作成し、リードレプリカの共有テーブルスペースにテーブルのメタデータを移行できます。ALTER TABLE ステートメントはリードレプリカのアクセスをブロックしますが、出典 DB インスタンスは影響を受けません。テーブルの再構築中はリードレプリカが停滞しますが、出典 DB インスタンスはバイナリログを生成し続けます。再構築には追加ストレージ領域が必要で、再生ログファイルは大きくなる可能性があるため、出典 DB インスタンスより大きなストレージが割り当てられたリードレプリカを作成する必要があります。
リードレプリカを作成し、共有テーブルスペースを使用する InnoDB テーブルを再構築するには、次のステップに従ってください。
-
バイナリログ作成が有効になるように、出典 DB インスタンスでバックアップ保持が有効になっていることを確認します。
-
AWS Management Console または AWS CLI を使用して、出典 DB インスタンスのリードレプリカを作成します。リードレプリカの作成にはクラッシュ回復と同じプロセスが多く含まれているため、InnoDB テーブルスペースの数が多い場合は、作成プロセスに時間がかかることがあります。出典 DB インスタンスで現在使用しているよりも大きいストレージ領域をリードレプリカに割り当てます。
-
リードレプリカが作成されたら、パラメータを
read_only = 0
およびinnodb_file_per_table = 0
に設定したパラメータグループを作成します。次に、パラメータグループをリードレプリカに関連付けます。 -
レプリカで移行するすべてのテーブルに対して、次の SQL ステートメントを発行します。
ALTER TABLE
name
ENGINE = InnoDB -
リードレプリカですべての
ALTER TABLE
ステートメントが完了したら、リードレプリカが出典 DB インスタンスに接続され、2 つのインスタンスが同期していることを確認します。 -
コンソールまたは CLI を使用して、リードレプリカをインスタンスに昇格します。新しいスタンドアロン DB インスタンスに使用されるパラメータグループの
innodb_file_per_table
パラメータが 0 に設定されていることを確認します。新しいスタンドアロン DB インスタンスの名前を変更し、アプリケーションを新しいスタンドアロン DB インスタンスにします。