この章では、InnoDB
に関連するコマンド
オプションとシステム変数を紹介します。虚実であるシステム変数は、それらを名づける事によってサーバ起動時に有効にされるか、または
skip-
プリフィックスを利用する事で無効にされます。例えば、InnoDB
チェックサムを有効、または無効にするには、コマンド
ライン上で --innodb_checksums
か
--skip-innodb_checksums
を、またはオプション ファイル内で
innodb_checksums
か
skip-innodb_checksums
を利用する事ができます。数字値を取るシステム変数は、コマンド
ライン上で
--
として、またはオプション ファイル内で
var_name
=value
として指定する事ができます。オプションとシステム変数を指定する事に関する更なる情報は、項3.3. 「プログラム・オプションの指定」
を参照してください。多くのシステム変数は起動時に変更できます。(項4.2.4.2. 「動的システム変数」
を参照してください。)
var_name
=value
MySQL Enterprise. MySQL ネットワーク モニタリングとアドバイス サービスは、InnoDB 起動オプションと関連するシステム変数に関する専門家のアドバイスを提供します。追加情報については http://www-jp.mysql.com/products/enterprise/advisors.html を参照してください。
InnoDB
コマンド オプション:
InnoDB
システム変数:
innodb_additional_mem_pool_size
InnoDB
がデータ辞書情報と別の内部データ構造を格納する為に利用する、メモリ
プールのバイトでのサイズです。より多くのテーブルをアプリケーション内に持っていると、ここに割り当てる為により多くのメモリが必要になります。もし
InnoDB
がこのプール内のメモリを使い果たしてしまったら、これは
OS からメモリを割り当て始め、MySQL エラー
ログに警告メッセージを書きます。デフォルト値は1MB
です。
自動拡大テーブルスペースがいっぱいになった時にサイズを拡大する為のインクリメント サイズ(MB)。デフォルト値は8です。
AWE メモリ内に置かれた時の、バッファ
プールのサイズ(MB)。これは32ビット Windows
内でだけ関連性があります。もしお使いの32ビット
Windows OS が4GB
以上のメモリをサポートするなら、いわゆる
「Address Windowing Extensions,」
を利用する事で、この変数を利用して
InnoDB
バッファプールを AWE
物理的メモリに割り当てる事ができます。この変数の最大可能値は63000です。
もしこれが0以上なら、innodb_buffer_pool_size
は InnoDB
がその AWE メモリを
マップする mysqld の32ビット
アドレス領域内のウィンドウです。innodb_buffer_pool_size
の適正な値は 500MB です。
AWE メモリを活用するには、自分で MySQL
をリコンパイルする必要があります。これを行うのに必要な現在のプロジェクト設定は、storage/innobase/os/os0proj.c
ソース ファイル内で見つける事ができます。
InnoDB
がそのテーブルのデータとインデックスをキャッシュする為に利用する、メモリバッファ
のバイトでのサイズです。この値を大きく設定するほど、テーブル内のデータにアクセスするのに必要なディスク
I/O は少なくなります。専用のデータベース
サーバ上で、これをマシンの物理的メモリ
サイズの最大80%
に設定すると良いでしょう。しかし、物理的メモリの競合が
OS
内でページングを引き起こす可能性があるので、あまり大きく設定しないでください。
InnoDB
は、壊れたハードウェアやデータ
ファイルに対する追加フォールト
トレランスを保証するディスクからの全てのページの読み込み上で、チェックサムの妥当性確認を利用する事ができます。この妥当性確認はデフォルトで有効化されています。しかし、まれに(ベンチマークが起動している時等
)、この追加安全機能は必要なく、--skip-innodb-checksums
を利用して無効にする事ができます。
同時にコミットする事ができるスレッドの数。0の値は並行処理制御を無効にします。
InnoDB
に同時に入る事ができるスレッドの数は、innodb_thread_concurrency
変数によって決められます。スレッドが
InnoDB
に入ろうとする時にもし並行処理の限度までスレッド数が達していたら、それらは列になります。スレッドが
InnoDB
に入るのを許可されると、innodb_concurrency_tickets
の値と同等の 「フリー チケット」
をたくさん与えられ、スレッドはそのチケットを使ってしまうまでは自由に
InnoDB
に出入りできます。それ以降は、スレッドが次に
InnoDB
に入ろうとした時に、再度並行処理チェックの対象となります。(または列に並ぶ可能性もある)
独立したデータ
ファイルとそれらのサイズへのパス。各データ
ファイルへの完全ディレクトリ
パスは、ここに指定された各パスへの
innodb_data_home_dir
を結合する事によって形作られます。ファイル
サイズは、サイズ値に M
か
G
を付加して、MB か GB
(1024MB)で指定されます。ファイル
サイズの合計は最低10MB 必要です。もし
innodb_data_file_path
を指定しなければ、デフォルト動作で
ibdata1
と名づけられた10MB
の単一自動拡大データ
ファイルが作成されます。各ファイルのサイズ制限は
OS
によって決定されます。大きいファイルをサポートする
OS のサイズを4GB
以上に設定する事ができます。未加工ディスク
パーティションをデータ
ファイルとして利用する事もできます。詳しくは
項13.5.3.2. 「共有テーブルスペースに未加工デバイスを利用する」
を参照してください。
全ての InnoDB
データ
ファイルのディレクトリ
パスの主な部分。もしこの値を設定しなければ、デフォルトは
MySQL データ
ディレクトリになります。値を空の文字列として指定する事もでき、その場合は
innodb_data_file_path
内で完全なファイル
パスを利用する事ができます。
デフォルトで、InnoDB
は全てのデータを2回格納します。一回目は二重書き込み
バッファに、そして次に実際のデータ
ファイルに格納します。この変数はデフォルトで有効化されています。それは、データの整合性や起こり得る失敗に対する心配よりも、ベンチマークや最高性能が要求される時に、--skip-innodb_doublewrite
を利用して止める事ができます。
もしこの変数を0に設定すると、InnoDB
はシャットダウンの前に完全消去と挿入バッファ
マージを行います。これらの操作には数分間、または極端な場合には数時間かかる事があります。もしこの変数を1に設定すると、InnoDB
はこれらの操作をシャットダウンの時にスキップします。デフォルト値は1です。もしこれを2に設定すると、InnoDB
はそのログをフラッシュし、まるで MySQL
がクラッシュしたかのように急にシャットダウンします。コミットされたトランザクションはなくなりませんが、次の起動の際にクラッシュ復旧が行われます。
2の値は NetWare 上では利用できません。
InnoDB
内のファイル I/O
スレッド数。通常、これはデフォルト値である4のままにしておくべきですが、Windows
上のディスク I/O
にとってはそれよりも大きい値の方がよいかもしれません。Unix
上では、数値を増やしても効果はありません。InnoDB
は必ずデフォルト値を利用します。
この変数が有効になると、InnoDB
はデータとインデックスを共有テーブルスペースに格納するのではなく、それ自体の
.ibd
ファイルを利用してそれぞれの新しいテーブルを作成し、そこに格納します。デフォルトでは、共有テーブルスペースにテーブルを作成するという事になっています。詳しくは
項13.5.3.1. 「Per-Table テーブルスペースを利用する」
を参照してください。
innodb_flush_log_at_trx_commit
innodb_flush_log_at_trx_commit
が0に設定された時は、ログ
バッファは1秒に一回ログ
ファイルに書き込まれ、ディスク操作へのフラッシュはログ
ファイル上で行われますが、トランザクション
コミットの際には何も行われません。この値が1(デフォルト)の時は、ログ
ファイルは各トランザクション
コミットの時にログ
ファイルに書き込まれ、ディスク操作へのフラッシュはログ
ファイル上で行われます。2に設定された時は、ログ
バッファはコミット毎にファイルに書き込まれますが、ディスク操作へのフラッシュはそこでは行われません。しかし、値が2の時もログ
ファイル上でのフラッシュは1秒に1回行われます。1秒に1回のフラッシュは、処理スケジュールの発行の為100%
保証された物ではないという事に注意してください。
この変数のデフォルト値は1です。これは ACID
整合性に要求されている値です。より良い性能の為に1以外の値を設定する事もできますが、その場合1つのクラッシュの中で最大1秒分のトランザクションを失う可能性があります。もし値を0に設定すると、全ての
mysqld プロセス
クラッシュは最後の秒のトランザクションを消す場合があります。もし値を2に設定すると、OS
のクラッシュか停電によって、最後の秒のトランザクションが消されてしまいます。
しかし、InnoDB
のクラッシュ復旧は影響を受けないので、値に関係なくクラッシュ復旧は行われます。多くの
OS といくつかのディスク
ハードウェアはディスクへのフラッシュ操作を欺く事があると覚えておいてください。それらはフラッシュが行われていなくても、行われたと
mysqld
に伝える可能性があります。1の設定がしてあってもトランザクションの耐久力が保証されないという事になり、さらに悪い事に、停電によって
InnoDB
データベースが破損する可能性もあります。SCSI
ディスク
コントローラ内やディスク自体の中での、バッテリーに頼っているディスク
キャッシュの利用はファイル
フラッシュのスピートを上げ、操作を安全に行う事ができます。ハードウェア
キャッシュ内でディスク書き込みのキャッシュを無効にする為に、Unix
コマンド hdparm
を利用してみたり、ハードウェア
ベンダに対しての特定の別のコマンドを利用したりもできます。
注意:InnoDB
とトランザクションを共に利用して複製設定内で最大の耐久力と一貫性を得る為に、お使いのマスタ
サーバ my.cnf
内で
innodb_flush_log_at_trx_commit=1
と
sync_binlog=1
を利用しなければいけません。
もし fdatasync
(デフォルト)に設定すると、InnoDB
はデータとログ
ファイルの両方をフラッシュする為に
fsync()
を利用します。もし
O_DSYNC
に設定すると、InnoDB
はログ
ファイルをオープン、フラッシュする為に
O_SYNC
を利用しますが、データ
ファイルをフラッシュする為には
fsync()
を利用します。もし
O_DIRECT
が指定されると(GNU/Linux
バージョン上で有効)、InnoDB
はデータ ファイルをオープンする為に
O_DIRECT
を利用し、データとログ
ファイルの両方をフラッシュする為に
fsync()
を利用します。InnoDB
は
fdatasync()
の代わりに
fsync()
を利用する事、また様々な種類の Unix
上で問題があった為、デフォルトで
O_DSYNC
は利用しないという事に注意してください。この変数は
Unix に対してだけ関連があります。Windows
上では、フラッシュの方法は毎回
async_unbuffered
で、変更する事はできません。
この変数の異なる値は InnoDB
performance
上で著しい影響を持ちます。例えば、InnoDB
データとログ ファイルが SAN
上に位置するいくつかのシステム上では、innodb_flush_method
を O_DIRECT
に設定する事は、3つの要因によってシンプルな
SELECT
ステートメントの性能を劣らせる可能性があるという事が発見されました。
クラッシュ復旧モード。警告:この変数は、破損したデータベースからテーブルを捨てたいという緊急の場合のみ、0以降の値に設定しなければいけません!可能な値は1から6です。これらの値の意味は、項13.5.8.1. 「InnoDB
復旧の強制」
で説明されています。安全策として、InnoDB
はこの変数が0以上の時はそのデータへの変更を阻止します。
InnoDB
トランザクションがロール
バックされる前に、ロックを待つ秒数でのタイムアウト。InnoDB
は自動的にそれ自体のロック
テーブル内でトランザクション
デッドロックを検出し、トランザクションをロールバックします。InnoDB
は LOCK TABLES
ステートメントを利用してロック
セットを通知します。デフォルトは50秒です。
innodb_locks_unsafe_for_binlog
この変数は InnoDB
サーチとインデックス スキャン内でネクスト
キー
ロックをコントロールします。デフォルトによってこの変数は0(無効)であり、それはネクスト
キー ロックが有効であると意味します。
通常、InnoDB
は next-key
locking
と呼ばれるアルゴリズムを利用します。InnoDB
は、それがテーブル
インデックスを検索やスキャンする時に、遭遇した全てのインデックス
レコード上で共有または専用ロックを設定する、という方法で行レベル
ロックを実行します。従って、行レベル
ロックは実際はインデックス レコード
ロックであるという事になります。InnoDB
がインデックス
レコード上で設定するロックは、そのインデックス
レコードに先行する 「ギャップ」
にも影響を与えます。もしユーザがインデックス内のレコード
R
上に共有または専用ロックを持っていたら、別のユーザはインデックスの順番で
R の直前に新しいインデックス
レコードを挿入する事はできません。この変数を有効にすると、InnoDB
が検索やインデックス スキャン内でネクスト
キー
ロックを利用しないよう働きかけます。ネクスト
キー ロックは外部キー制約と複製キー
チェックを保証する為にはまだ利用されます。この変数を有効にすると、ファントムを引き起こす可能性がある事に注意してください:後で選択した行内のいくつかのカラムを更新するつもりで、100よりも大きい値の識別子を持つ
child
テーブルから全ての子供を読み、ロックしたいと仮定します:
SELECT * FROM child WHERE id > 100 FOR UPDATE;
id
カラム上にインデックスがあると仮定してください。id
が100以上の最初のレコードから、そのインデックスをクエリがスキャンします。もしインデックス
レコード上に設定されたロックがギャップに挿入された物をロックしなければ、別のクライアントがテーブルに新しい行を挿入する事ができます。
もし同じトランザクション内で同じ
SELECT
を実行すると、クエリから返された結果セット内に新しい行を見つける事ができます。これは、もしデータベースに新しい項目が追加されると、InnoDB
はシリアリザビリティを保証しないという事も意味します。従って、もしこの変数が有効になると、InnoDB
は最高の分離レベル READ COMMITTED
を保証します。(コンフリクト
シリアリザビリティは保証されたままです。)
この変数を有効にすると、追加効果があります:UPDATE
や DELETE
内の InnoDB
は、更新や削除を行う行だけをロックします。このおかげでデッドロックの可能性が大幅に低くなりますが、それでもまだ起こります。この変数を有効にしても、UPDATE
のような操作が別の似た操作(別の
UPDATE
のような)
を追い越す事は、たとえそれらが別の行に影響を与えるとしても許されていない事に注意してください。このテーブルから始まる、次の例を検討してみてください:
CREATE TABLE A(A INT NOT NULL, B INT) ENGINE = InnoDB; INSERT INTO A VALUES (1,2),(2,3),(3,2),(4,3),(5,2); COMMIT;
1つのクライアントがこれらのステートメントを実行すると仮定してください:
SET AUTOCOMMIT = 0; UPDATE A SET B = 5 WHERE B = 3;
そして、別のクライアントが、最初のクライアントの後にこれらのステートメントを実行すると仮定してください:
SET AUTOCOMMIT = 0; UPDATE A SET B = 4 WHERE B = 2;
この場合、2つ目の UPDATE
は、最初の UPDATE
のコミットかロールバックを待つ必要があります。最初の
UPDATE
は行(2、3)上に専用ロックを持ち、2つ目の
UPDATE
も行をスキャンしている間に同じ行に専用ロックを得ようとしますが、それはできません。これは、2つの
UPDATE
のうち最初の物が行に専用ロックを得て、その行が結果セットに属しているかどうかを決める為に起こります。もしそうでなければ、それは
innodb_locks_unsafe_for_binlog
変数が有効になった時に、不必要なロックを解除します。
従って、InnoDB
は次のように
UPDATE
1を実行します:
x-lock(1,2) unlock(1,2) x-lock(2,3) update(2,3) to (2,5) x-lock(3,2) unlock(3,2) x-lock(4,3) update(4,3) to (4,5) x-lock(5,2) unlock(5,2)
InnoDB
は UPDATE
2を次のように実行します:
x-lock(1,2) update(1,2) to (1,4) x-lock(2,3) - wait for query one to commit or rollback
InnoDB
アーカイブ
ファイルをログするかどうか。この変数は歴史的理由により存在していますが、利用はされていません。バックアップからの復旧は
MySQL がそれ自身のログ
ファイルを利用して行っていますので、InnoDB
ログ
ファイルをアーカイブに保管する必要はありません。この変数のデフォルトは0です。
InnoDB
がディスク上のログ
ファイルに書き込む為に利用するバッファのバイトでのサイズ。実用的な値の範囲は1MB
から8MB です。デフォルトは1MB
です。大きいログ
バッファは、トランザクション
コミットの前にディスクにログを書き込む必要なく、大きいトランザクションが起動する事を許容します。従って、もし大きいトランザクションを持っていたら、ログ
ファイルを大きくしておく事でディスク I/O
を節約する事ができます。
ログ
グループ内のそれぞれの長いファイルのバイトでのサイズ。ログ
ファイルの結合したサイズは32ビット
コンピュータ上で 4GB
以下でなければいけません。デフォルトは5MB
です。実用的な値は、N
がグループ内のログ
ファイル数だとして、バッファ
プールのサイズの1MB から
1/N
-th です。
値が大きいほど、ディスク I/O
を節約し、バッファ
プール内で必要とされるチェックポイント
フラッシュ活動は少なくなります。しかし、ログ
ファイルが大きいという事はクラッシュした時の復旧のスピードが遅いという事も意味します。
ログ グループ内のログ
ファイル数。InnoDB
はファイルに輪状に書き込みをします。デフォルト(そして推奨)は2です。
InnoDB
ログ
ファイルへのディレクトリ パス。もし
InnoDB
ログ変数を何も指定しなければ、デフォルトで
MySQL データ ディレクトリ内に
ib_logfile0
と
ib_logfile1
という名前の2つの5MB
ファイルを作成します。
これは0から100の範囲の間の整数です。デフォルトは90です。InnoDB
内の主スレッドは、ダーティ
(まだ書き込まれていない)ページの割合がこの値を超えないようにバッファ
プールからページを書くように試みます。
この変数は、消去操作が遅れている時に(項13.5.12. 「マルチバージョンの実装」
参照) INSERT
、UPDATE
そして DELETE
操作をどのように遅らせるかをコントロールします。この変数のデフォルト値は0で、これは遅れは無いという事を意味します。
InnoDB
トランザクション
システムは UPDATE
か
DELETE
操作によって削除マークが付けられたインデックス
レコードを持つトランザクションのリストを保持します。このリストの長さを
purge_lag
にして下さい。purge_lag
が
innodb_max_purge_lag
を上回る時、各
INSERT
、UPDATE
そして DELETE
操作は((purge_lag
/innodb_max_purge_lag
)×10)–5
ミリ秒遅れます。遅れは消去バッチの最初に、10秒ごとに計算されます。もし消去される行をを知る事ができる、古い一貫した読み取りビューの為に消去が起動しなかったら、その操作は遅れません。
トランザクション サイズがたったの100バイトと小さく、テーブル内に消去されていない行を100MB 許容できると仮定した時、問題を引き起こす可能性のある作業負荷の典型的な設定は100万でしょう。
データベースの為に残すログ グループの同一コピー数。現在は、この値は1に設定しなければいけません。
この変数は InnoDB
内で複数のテーブルスペースを利用する場合のみ関連があります。それは
InnoDB
が同時にオープンしておける
.ibd
ファイルの最大数を指定します。最小値は10です。デフォルトは300です。
.ibd
ファイルに利用されるファイル記述子は、InnoDB
に対しての物のみです。それらは、--open-files-limit
サーバ
オプションによって指定された物からは独立していて、テーブル
キャッシュの操作に影響を与えません。
MySQL 5.1 内で、InnoDB
はトランザクション
タイムアウト上で最後のステートメントだけをロールバックします。このオプションが与えられると、トランザクション
タイムアウトは InnoDB
がトランザクション全体を異常終了し、ロールバックするよう働きかけます。(MySQL
4.1と同じ動作です。)この変数は、MySQL
5.1.15で追加されました。
ON
か1(デフォルト)に設定されると、この変数は
InnoDB
が XA
トランザクション内の二相コミット
サポートを有効にします。innodb_support_xa
を有効にすると、トランザクションの準備でディスク
フラッシュが余計に起こります。 XA
を利用する事を気にしないのであれば、この変数を
OFF
か0に設定してこれを無効にする事ができ、ディスク
フラッシュの数を減らし、InnoDB
操作性能を向上させる事ができます。
スレッドが、サスペンドされる前に
InnoDB
ミューテックスが開放されるのを待つ回数。
もし
AUTOCOMMIT=0
、InnoDB
が LOCK TABLES
を支持すると、MySQL
は全てのスレッドがそれら全てのロックをテーブルにリリースするまで
LOCK TABLE .. WRITE
から戻りません。innodb_table_locks
のデフォルト値は1です。それはもし
AUTOCOMMIT=0
なら LOCK
TABLES
は InnoDB
がテーブルを内部的にロックするよう働きかける事を意味します。
InnoDB
は、この変数から与えられた制限よりも少ない、またはそれと同等の制限の
InnoDB
内部に多くの OS
スレッドを一斉に保存しようと試みます。性能に関する問題を持ち、多くのスレッドがセマフォを待っているという事が
SHOW ENGINE INNODB STATUS
によって明らかにされたのなら、スレッド
「thrashing」
を持ち、この変数を低くまたは高く設定するよう試みる必要があります。もしたくさんのプロセッサとディスクがあるコンピュータをお持ちであれば、それを有効に活用する為に値を高く設定する事もできます。推奨値はお使いのシステムのプロセッサとディスク数の合計値です。
この変数の範囲は0から1000です。20以上の値は無限並行処理として読み取られます。 無限というのは、並行チェックが無効になり、ミューテックスを獲得、リリースする事で発生するであろう、多量の負荷を防ぐという意味です。
MySQL 5.1.11以前はデフォルト値は20で、5.1.11以降は8となっています。
InnoDB
スレッドは
InnoDB
の列に加わるまでに、マイクロ秒で何秒間スリープ状態にあるか。デフォルト値は10,000です。0の値ではスリープ状態にはなりません。
sync_binlog
もし変数値が正数であれば、MySQL
サーバはバイナリ ログへの毎
sync_binlog
書き込みごとに、ディスク(fdatasync()
)にそのバイナリ
ログを同期化します。オート コミット
モードでは、各ステートメントにつきバイナリ
ログへの書き込みが1つあり、そうでなければ各トランザクションにつき1つの書き込みがあると覚えて置いてください。デフォルトは、ディスクへの同期化を行わない0です。
クラッシュしてしまった場合には、バイナリ
ログから最大1つのステートメントかトランザクションが失われてしまう為、1の値が一番安全な値です。しかしこれは同時に、一番スピードが遅い物になります。(ディスクが、同期化の作業を大変速くする事ができる、バッテリで起動するキャッシュを搭載していない限り)