MySQL DB インスタンスの一般的な DBA タスク - Amazon Relational Database Service

MySQL DB インスタンスの一般的な DBA タスク

このセクションでは、MySQL データベースエンジンを実行している DB インスタンスに関するいくつかの一般的な DBA タスクの Amazon RDS 固有の実装について説明します。マネージド型サービスの操作性を実現するために、Amazon RDS では DB インスタンスへのシェルアクセスはできません。また、上位の権限を必要とする特定のシステムプロシージャやシステムテーブルへのアクセスが制限されます。

Amazon RDS での MySQL ログファイルの操作に関する詳細は、「MySQL データベースログファイル」を参照してください。

セッションやクエリの終了

DB インスタンス上のユーザーセッションまたはクエリは、rds_kill コマンドおよび rds_kill_query コマンドを使用して終了することができます。まず MySQL DB インスタンスに接続し、次に以下に示す適切なコマンドを発行します。詳細については、「MySQL データベースエンジンを実行している DB インスタンスへの接続」を参照してください。

CALL mysql.rds_kill(thread-ID) CALL mysql.rds_kill_query(thread-ID)

例えば、スレッド 99 で実行中のセッションを終了するには、次のように入力します。

CALL mysql.rds_kill(99);

スレッド 99 で実行中のクエリを終了するには、次のように入力します。

CALL mysql.rds_kill_query(99);

現在のレプリケーションエラーのスキップ

Amazon RDS は、リードレプリカが反応を停止する原因となるエラーがデータの整合性に影響しない場合、リードレプリカでそのエラーをスキップするためのメカニズムを提供します。まず MySQL DB インスタンスに接続し、次に以下に示す適切なコマンドを発行します。詳細については、「MySQL データベースエンジンを実行している DB インスタンスへの接続」を参照してください。

注記

最初にそのエラーを安全にスキップできることを確認する必要があります。MySQL ユーティリティで、リードレプリカに接続し、次の MySQL コマンドを実行します。

SHOW REPLICA STATUS\G

返される値の詳細については、MySQL ドキュメントを参照してください。

MySQL の旧バージョンは SHOW SLAVE STATUS ではなく SHOW REPLICA STATUS を使用していました。8.0.23 より前の MySQL バージョンを使用している場合は、SHOW SLAVE STATUS を使用します。

エラーをスキップするには、次のコマンドを実行します。

CALL mysql.rds_skip_repl_error;

このコマンドは、ソース DB インスタンス、またはレプリケーションエラーが発生していないリードレプリカに対して実行しても効果はありません。

mysql.rds_skip_repl_error をサポートする MySQL のバージョンなどの詳細については、「mysql.rds_skip_repl_error」を参照してください。

重要

mysql.rds_skip_repl_error を呼び出そうとして、次のエラー ERROR 1305 (42000): PROCEDURE mysql.rds_skip_repl_error does not exist が発生した場合、MySQL DB インスタンスを「mysql.rds_skip_repl_error」にリストされている最新のマイナーバージョン、または最小のマイナーバージョンの 1 つにアップグレードします。

InnoDB テーブルスペースの操作によるクラッシュリカバリ時間の短縮

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.6 および 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 テーブルを再構築するには、次のステップに従ってください。

  1. バイナリログ作成が有効になるように、ソース DB インスタンスでバックアップ保持が有効になっていることを確認します。

  2. AWS Management Console または AWS CLI を使用して、ソース DB インスタンスのリードレプリカを作成します。リードレプリカの作成にはクラッシュ回復と同じプロセスが多く含まれているため、InnoDB テーブルスペースの数が多い場合は、作成プロセスに時間がかかることがあります。ソース DB インスタンスで現在使用しているよりも大きいストレージ領域をリードレプリカに割り当てます。

  3. リードレプリカが作成されたら、パラメータを read_only = 0 および innodb_file_per_table = 0 に設定したパラメータグループを作成します。次に、パラメータグループをリードレプリカに関連付けます。

  4. レプリカで移行するすべてのテーブルに対して、次の SQL ステートメントを発行します。

    ALTER TABLE name ENGINE = InnoDB
  5. リードレプリカですべての ALTER TABLE ステートメントが完了したら、リードレプリカがソース DB インスタンスに接続され、2 つのインスタンスが同期していることを確認します。

  6. コンソールまたは CLI を使用して、リードレプリカをインスタンスに昇格します。新しいスタンドアロン DB インスタンスに使用されるパラメータグループの innodb_file_per_table パラメータが 0 に設定されていることを確認します。新しいスタンドアロン DB インスタンスの名前を変更し、アプリケーションを新しいスタンドアロン DB インスタンスにします。

Global Status History の管理

MySQL はオペレーションに関する情報を提供する多くのステータス変数を維持しています。その値は、DB インスタンスのロックまたはメモリ問題の検出に役立ちます。これらのステータス変数の値は、最後に DB インスタンスが開始されてからの累積です。ほとんどのステータス変数は、FLUSH STATUS コマンドを使用して 0 にリセットできます。

これらの値を経時的にモニタリングできるように、Amazon RDS は、これらのステータス変数値のスナップショットを作成し、前回のスナップショット以降に行われた変更と共にテーブルに書き込む一連の手順を提供します。このインフラストラクチャは Global Status History (GoSH) と呼ばれ、5.5.23 で始まるバージョンのすべての MySQL DB インスタンスにインストールされています。GoSH は、デフォルトでは無効化されています。

GoSH を有効にするには、最初に event_scheduler パラメータを ON に設定し、DB パラメータグループからイベントスケジューラーを有効にします。DB パラメータグループの作成と変更の詳細については、「DB パラメータグループを使用する」を参照してください。

次に、以下の表の手順を使用して、GoSH を有効化し、設定します。まず MySQL DB インスタンスに接続し、次に以下に示す適切なコマンドを発行します。詳細については、「MySQL データベースエンジンを実行している DB インスタンスへの接続」を参照してください。各手順で次のように入力します。

CALL procedure-name;

ここで、procedure-name は、表の手順の 1 つです。

手順

説明

mysql.rds_enable_gsh_collector

GoSH のデフォルトのスナップショット作成を有効化します。間隔は rds_set_gsh_collector で指定します。

mysql.rds_set_gsh_collector

スナップショット作成の間隔を分単位で指定します。デフォルト値は 5 です。

mysql.rds_disable_gsh_collector

スナップショットを無効にします。

mysql.rds_collect_global_status_history

オンデマンドでスナップショットを作成します。

mysql.rds_enable_gsh_rotation

mysql.rds_global_status_history テーブルから mysql.rds_global_status_history_old へのコンテンツのローテーションを有効化します。間隔は rds_set_gsh_rotation で指定します。

mysql.rds_set_gsh_rotation

テーブルのローテーション間隔を日単位で指定します。デフォルト値は 7 です。

mysql.rds_disable_gsh_rotation

テーブルのローテーションを無効にします。

mysql.rds_rotate_global_status_history

mysql.rds_global_status_history テーブルのコンテンツを mysql.rds_global_status_history_old にオンデマンドでローテーションします。

GoSH の実行中は、書き込んでいるテーブルに対してクエリを実行できます。例えば、Innodb バッファープールのヒット率を照会するには、次のクエリを実行します。

select a.collection_end, a.collection_start, (( a.variable_Delta-b.variable_delta)/a.variable_delta)*100 as "HitRatio" from mysql.rds_global_status_history as a join mysql.rds_global_status_history as b on a.collection_end = b.collection_end where a. variable_name = 'Innodb_buffer_pool_read_requests' and b.variable_name = 'Innodb_buffer_pool_reads'