MySQL DB インスタンスの一般的な DBA タスク - Amazon Relational Database Service
セッションやクエリの終了現在のレプリケーションエラーのスキップInnoDB テーブルスペースの操作によるクラッシュリカバリ時間の短縮Global Status History の管理

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

以下では、Oracle データベースエンジンを実行する RDS 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);

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

リードレプリカが反応を停止する原因となるエラーがデータの整合性に影響しない場合、リードレプリカでそのエラーをスキップすることができます。

注記

まず、そのエラーを安全にスキップできることを確認します。MySQL ユーティリティで、リードレプリカに接続して以下の MySQL コマンドを実行します。

SHOW REPLICA STATUS\G

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

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

リードレプリカのエラーは、次の方法でスキップできます。

mysql.rds_skip_repl_error の手順を呼び出します。

Amazon RDS では、リードレプリカのエラーをスキップするために呼び出すことができるストアドプロシージャを提供しています。まず、リードレプリカに接続してから以下のように適切なコマンドを発行します。詳細については、「MySQL データベースエンジンを実行している DB インスタンスへの接続」を参照してください。

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

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つにアップグレードします。

slave_skip_errors パラメータの設定

1 つ以上のエラーをスキップするには、slave_skip_errorsリードレプリカに静的パラメータを設定します。このパラメータでは、1 つ以上の特定のレプリケーションエラーコードをスキップするように設定できます。現在、このパラメータは RDS for MySQL 5.7 DB インスタンスに対してのみ設定できます。パラメータの設定変更後に新しい設定を有効にするには、DB インスタンスを必ず再起動してください。これらのパラメータ設定の詳細については、MySQL のドキュメントを参照してください。

このパラメータは別の DB パラメータグループに設定することをお勧めします。この DB パラメータグループは、エラーをスキップする必要があるリードレプリカにのみ関連付けることができます。このベストプラクティスに従うことで、他の DB インスタンスやリードレプリカに与える潜在的な影響が軽減されます。

重要

このパラメータにデフォルト以外の値を設定すると、レプリケーションの不整合につながることがあります。問題を解決するための他のオプションを使い果たし、リードレプリカのデータに潜在的な影響が確実である場合にのみ、このパラメータをデフォルト以外の値に設定してください。

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.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 パラメータグループの作成と変更の詳細については、「パラメータグループを使用する」を参照してください。

次に、以下の表の手順を使用して、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'