Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 27.1Mb
PDF (A4) - 27.2Mb
HTML Download (TGZ) - 7.2Mb
HTML Download (Zip) - 7.2Mb


MySQL 5.6 リファレンスマニュアル  /  ...  /  オンライン DDL でのパフォーマンスと並列性に関する考慮事項

14.11.2 オンライン DDL でのパフォーマンスと並列性に関する考慮事項

オンライン DDL によって、パフォーマンス、並列性、可用性、スケーラビリティーなどの MySQL 操作のいくつかの側面が改善されます。

  • テーブルでのクエリーや DML 操作は DDL の進行中も処理を続行できるため、そのテーブルにアクセスするアプリケーションの応答性が向上します。MySQL サーバー全体にわたってほかのリソースのロックや待機が削減されるため、変更されるテーブルには関連しない操作であっても、スケーラビリティーの向上がもたらされます。

  • インプレース操作では、テーブルを再構築するためのディスク I/O や CPU サイクルが回避されるため、データベースにかかる全体的な負荷が最小限に抑えられるとともに、DDL 操作中に良好なパフォーマンスと高いスループットが維持されます。

  • インプレース操作では、すべてのデータがコピーされる場合に比べてバッファープールに読み取られるデータが削減されるため、以前は DDL 操作のあとの一時的なパフォーマンス低下の原因になる可能性のあった、頻繁にアクセスされるデータのメモリーからのパージが回避されます。

オンライン操作に一時ファイルが必要な場合、InnoDB はそれらのファイルを元のテーブルを含むディレクトリではなく、一時ファイルディレクトリ内に作成します。このディレクトリがそのようなファイルを保持するほどに十分に大きくない場合は、tmpdir システム変数に別のディレクトリを設定する必要があることがあります。(セクションB.5.4.4「MySQL が一時ファイルを格納する場所」を参照してください。)

オンライン DDL のロックオプション

InnoDB テーブルが DDL 操作によって変更されている間は、その操作の内部動作や ALTER TABLE ステートメントの LOCK 句に応じて、そのテーブルはロックされる場合とされない場合があります。デフォルトでは、MySQL は DDL 操作中にできるだけ少ないロックを使用します。この句は、ロックを通常の場合より制限的にする (それによって並列 DML、または DML とクエリーを制限する) ためか、またはある操作に対して何らかの期待されるレベルのロックが確実に許可されるようにするために指定します。主キーの作成または削除中に LOCK 句がその特定の種類の DDL 操作では使用できないロックのレベル (LOCK=SHAREDLOCK=NONE など) を指定している場合は、この句が表明のように機能するため、このステートメントはエラーで失敗します。次のリストは、もっとも許容的な場合からもっとも制限的な場合までの LOCK 句のさまざまな可能性を示しています。

  • LOCK=NONE を使用した DDL 操作では、クエリーと並列 DML の両方が許可されます。この句は、要求されたロックのタイプでこの種類の DDL 操作を実行できないと ALTER TABLE を失敗させるため、LOCK=NONE は、テーブルを完全に使用可能な状態に維持することが不可欠であり、かつそれができなければ DDL を取り消してもかまわない場合に指定します。たとえば、この句は、顧客のサインアップまたは購入に関連するテーブルの DDL で、コストの高い ALTER TABLE ステートメントの誤った発行によってこれらのテーブルが使用不可能にならないようにするために使用できます。

  • LOCK=SHARED を使用した DDL 操作では、テーブルへの書き込み (つまり、DML 操作) がすべてブロックされますが、そのテーブル内のデータは読み取ることができます。この句は、要求されたロックのタイプでこの種類の DDL 操作を実行できないと ALTER TABLE を失敗させるため、LOCK=SHARED は、テーブルをクエリーに対して使用可能な状態に維持することが不可欠であり、かつそれができなければ DDL を取り消してもかまわない場合に指定します。たとえば、この句は、DDL が完了するまでデータロード操作を遅らせてもかまわないが、クエリーを長時間遅らせることはできないデータウェアハウス内のテーブルの DDL で使用できます。

  • LOCK=DEFAULT を使用するか、または LOCK 句が省略された DDL 操作では、MySQL はその種類の操作で使用可能なもっとも低いレベルのロックを使用することにより、可能な場合は常に並列クエリー、DML、またはその両方を許可します。これは、そのテーブルのワークロードに基づいて可用性に関する問題が発生しないことがわかっている、事前に計画およびテストされた変更を行う場合に使用する設定です。

  • LOCK=EXCLUSIVE を使用した DDL 操作では、クエリーと DML 操作の両方がブロックされます。この句は、要求されたロックのタイプでこの種類の DDL 操作を実行できないと ALTER TABLE を失敗させるため、LOCK=EXCLUSIVE は、主な関心事が DDL を可能性のある最短の時間で完了させることであり、かつテーブルにアクセスしようとするアプリケーションを待機させてもかまわない場合に指定します。LOCK=EXCLUSIVE はまた、テーブルへの予期しないアクセスを回避するために、サーバーがアイドル状態であると想定される場合にも使用できます。

InnoDB テーブルに対するオンライン DDL ステートメントは、DDL ステートメントの準備中に短時間だけテーブルへの排他的アクセスが必要なため、そのテーブルにアクセスしている現在実行中のトランザクションがコミットまたはロールバックするのを常に待機します。同様に、完了前にも、短時間だけテーブルへの排他的アクセスが必要です。そのため、オンライン DDL ステートメントは、その DDL が完了する前に、DDL の進行中に開始され、テーブルをクエリーまたは変更するすべてのトランザクションがコミットまたはロールバックするのを待機します。

並列 DML 操作によって行われた変更を記録したあと、最後にこれらの変更を適用するにはある程度の処理が必要であるため、オンライン DDL 操作には、ほかのセッションからのテーブルアクセスをブロックする古いスタイルのメカニズムに比べて全体的に長い時間がかかる可能性があります。raw パフォーマンスの低下は、そのテーブルを使用するアプリケーションの応答性の向上とバランスがとれています。テーブル構造を変更するための理想的な手法を評価する場合は、Web ページのロード時間などの要因に基づいて、エンドユーザーのパフォーマンスの認識を考慮してください。

新しく作成された InnoDB セカンダリインデックスには、CREATE INDEX または ALTER TABLE ステートメントが実行を完了した時点でのテーブル内のコミットされたデータのみが含まれています。コミットされていない値や古いバージョンの値、または削除対象としてマークされているが、まだ古いインデックスから削除されていない値は含まれていません。

インプレース DDL 操作とテーブルコピー DDL 操作のパフォーマンスの比較

オンライン DDL 操作の raw パフォーマンスは、その操作がインプレースで実行されるか、またはテーブル全体のコピーと再構築が必要かによってほとんど決定されます。インプレースで実行できる操作の種類や、テーブルコピー操作を行わないための何らかの要件を確認するには、表14.5「DDL 操作のオンラインステータスのサマリー」を参照してください。

インプレース DDL のパフォーマンス向上は、主キーのインデックスではなく、セカンダリインデックスに対する操作に適用されます。InnoDB テーブルの行は、主キーに基づいて編成されたクラスタ化されたインデックスに格納されます。これにより、一部のデータベースシステムでインデックス編成テーブルと呼ばれるものが形成されます。このテーブル構造は主キーにきわめて密接に結び付けられているため、主キーの再定義にはデータのコピーが引き続き必要です。

主キーに対する操作で ALGORITHM=INPLACE が使用される場合は、データが引き続きコピーされるにもかかわらず、次の理由で ALGORITHM=COPY を使用するより効率的です。

  • ALGORITHM=INPLACE には、Undo ロギングやそれに関連する Redo ロギングが必要ありません。これらの操作は、ALGORITHM=COPY を使用する DDL ステートメントのオーバーヘッドを増やします。

  • セカンダリインデックスエントリは事前にソートされているため、順番にロードできます。

  • セカンダリインデックスへのランダムアクセス挿入は存在しないため、変更バッファーは使用されません。

オンライン DDL 操作の相対的なパフォーマンスを評価するには、現在のバージョンと以前のバージョンの MySQL を使用して、このような操作を大きな InnoDB テーブルで実行できます。また、すべてのパフォーマンステストを最新の MySQL バージョンで実行することもできます。つまり、old_alter_table システム変数を設定することにより、以前の DDL 動作をシミュレートして前の結果を求めます。セッション内でステートメント set old_alter_table=1 を発行し、DDL パフォーマンスを測定して前の数値を記録します。次に、set old_alter_table=0 を発行して新しい、高速な動作を再度有効にし、DDL 操作を再度実行してあとの数値を記録します。

DDL 操作がその変更をインプレースで行うか、またはテーブルコピーを実行するかの基本的な考え方については、コマンドが完了したあとに表示されるrows affectedの値を見てください。たとえば、さまざまなタイプの DDL 操作を実行したあとに表示される可能性のある行を次に示します。

  • カラムのデフォルト値の変更 (非常に高速であり、テーブルデータにはまったく影響を与えません):

    Query OK, 0 rows affected (0.07 sec)
  • インデックスの追加 (時間はかかりますが、0 rows affected はテーブルがコピーされないことを示しています):

    Query OK, 0 rows affected (21.42 sec)
  • カラムのデータ型の変更 (かなりの時間がかかり、テーブルのすべての行の再構築が必要です):

    Query OK, 1671168 rows affected (1 min 35.54 sec)

たとえば、大きなテーブルで DDL 操作を実行する前に、その操作が速いか遅いかを次のようにチェックできます。

  1. テーブル構造をクローニングします。

  2. クローニングされたテーブルに非常に少量のデータを移入します。

  3. クローニングされたテーブルで DDL 操作を実行します。

  4. rows affectedの値が 0 かどうかをチェックします。0 以外の値は、この操作にはテーブル全体の再構築が必要なため、特別な計画が必要になる可能性があることを示します。たとえば、この DDL 操作をスケジュールされたダウンタイムの期間中に、または各レプリケーションスレーブサーバー上で一度に 1 つずつ実行することができます。

MySQL 処理の削減をより深く理解するには、DDL 操作の前後に InnoDB に関連した performance_schema および INFORMATION_SCHEMA テーブルを検査して、物理的な読み取り、書き込み、メモリー割り当てなどの数を確認します。


User Comments
Sign Up Login You must be logged in to post a comment.