InnoDB 起動オプションとシステム変数

–innodb

もしサーバが InnoDB サポートでコンパイルされると InnoDB ストレージ エンジンが有効になります。
InnoDB を無効にするには –skip-innodb を利用してください。

–innodb_status_file

InnoDB が MySQL データ ディレクトリ内にファイル名 /innodb_status. を作成するように働きかけます。InnoDB は定期的に SHOW ENGINE INNODB STATUS
のアウトプットをこのファイルに書き込みます。

mysql> SHOW ENGINE INNODB STATUS \G
*************************** 1. row ***************************
Type: InnoDB
Name:
Status:
=====================================
090204 14:52:38 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 1 seconds
———-
SEMAPHORES
———-
OS WAIT ARRAY INFO: reservation count 5, signal count 5
Mutex spin waits 0, rounds 200, OS waits 0
RW-shared spins 11, OS waits 5; RW-excl spins 1, OS waits 0
————
TRANSACTIONS
————
Trx id counter 0 1810
Purge done for trx’s n:o < 0 1293 undo n:o < 0 0 History list length 3 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: innodb_additional_mem_pool_size InnoDB がデータ辞書情報と別の内部データ構造を格納する為に利用する、メモリ プールのバイトでのサイズです。 より多くのテーブルをアプリケーション内に持っていると、ここに割り当てる為により多くのメモリが必要になります。 innodb_autoextend_increment 自動拡大テーブルスペースがいっぱいになった時にサイズを拡大する為のインクリメント サイズ(MB)。 デフォルト値は8です。 mysql> show variables like “innodb_autoextend_increment”;
+—————————–+——-+
| Variable_name | Value |
+—————————–+——-+
| innodb_autoextend_increment | 8 |
+—————————–+——-+
1 row in set (0.00 sec)

mysql>

innodb_buffer_pool_awe_mem_mb
AWE メモリ内に置かれた時の、バッファ プールのサイズ(MB)。これは32ビット Windows 内でだけ関連性があります。
indows 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_buffer_pool_size

InnoDB がそのテーブルのデータとインデックスをキャッシュする為に利用する、メモリバッファ のバイトでのサイズです。
この値を大きく設定するほど、テーブル内のデータにアクセスするのに必要なディスク I/O は少なくなります。
専用のデータベース サーバ上で、これをマシンの物理的メモリ サイズの最大80% に設定すると良いでしょう。
しかし、物理的メモリの競合が OS 内でページングを引き起こす可能性があるので、あまり大きく設定しないでください。

mysql> show variables like “innodb_buffer_pool_size”;
+————————-+———+
| Variable_name | Value |
+————————-+———+
| innodb_buffer_pool_size | 8388608 |
+————————-+———+
1 row in set (0.00 sec)

mysql>

innodb_checksums
InnoDB は、壊れたハードウェアやデータ ファイルに対する追加フォールト トレランスを保証するディスク
からの全てのページの読み込み上で、チェックサムの妥当性確認を利用する事ができます。
この妥当性確認はデフォルトで有効化されています。
ベンチマークが起動している時等、この追加安全機能は必要なく、–skip-innodb-checksums を利用して無効にする事ができます。

innodb_commit_concurrency
同時にコミットする事ができるスレッドの数。0の値は並行処理制御を無効にします。

mysql> show variables like “innodb_commit_concurrency”;
+—————————+——-+
| Variable_name | Value |
+—————————+——-+
| innodb_commit_concurrency | 0 |
+—————————+——-+
1 row in set (0.00 sec)

mysql>

innodb_concurrency_tickets
InnoDB に同時に入る事ができるスレッドの数は、innodb_thread_concurrency変数によって決められます。
スレッドが InnoDB に入ろうとする時にもし並行処理の限度までスレッド数が達していたら、
それらは列になります。スレッドが InnoDB に入るのを許可されると、innodb_concurrency_tickets
の値と同等の 「フリー チケット」 をたくさん与えられ、スレッドはそのチケットを使ってしまうまでは自由に
InnoDB に出入りできます。それ以降は、スレッドが次に InnoDB に入ろうとした時に、
再度並行処理チェックの対象となります。(または列に並ぶ可能性もある)

mysql> show variables like “innodb_concurrency_tickets”;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| innodb_concurrency_tickets | 500 |
+—————————-+——-+
1 row in set (0.00 sec)

mysql>

innodb_data_file_path
独立したデータ ファイルとそれらのサイズへのパス。
各データ ファイルへの完全ディレクトリ パスは、ここに指定された各パスへの
innodb_data_home_dir を結合する事によって形作られます。ファイル サイズは、
サイズ値に M か G を付加して、MB か GB (1024MB)で指定されます。ファイル サイズの合計
は最低10MB 必要です。もし innodb_data_file_path を指定しなければ、デフォルト動作で
ibdata1 と名づけられた10MB の単一自動拡大データ ファイルが作成されます。
各ファイルのサイズ制限は OS によって決定されます。

mysql> show variables like “innodb_data_file_path”;
+———————–+————————+
| Variable_name | Value |
+———————–+————————+
| innodb_data_file_path | ibdata1:10M:autoextend |
+———————–+————————+
1 row in set (0.00 sec)

mysql>

innodb_data_home_dir
全ての InnoDB データ ファイルのディレクトリ パスの主な部分。
もしこの値を設定しなければ、デフォルトは MySQL データ ディレクトリになります。

innodb_doublewrite
デフォルトで、InnoDB は全てのデータを2回格納します。
一回目は二重書き込み バッファに、そして次に実際のデータ ファイルに格納します。
この変数はデフォルトで有効化されています。それは、データの整合性や起こり得る
失敗に対する心配よりも、ベンチマークや最高性能が要求される時に、
–skip-innodb_doublewrite を利用して止める事ができます。

mysql> show variables like “innodb_doublewrite”;
+——————–+——-+
| Variable_name | Value |
+——————–+——-+
| innodb_doublewrite | ON |
+——————–+——-+
1 row in set (0.00 sec)

mysql>

innodb_fast_shutdown
もしこの変数を0に設定すると、InnoDB はシャットダウンの前に完全消去と挿入バッファ マージを行います。
デフォルト値は1です。もしこれを2に設定すると、コミットされたトランザクションはなくなりませんが、
次の起動の際にクラッシュ復旧が行われます。

innodb_file_io_threads
InnoDB 内のファイル I/O スレッド数。通常、これはデフォルト値である4のままにしておくべきですが、
Windows 上のディスク I/O にとってはそれよりも大きい値の方がよいかもしれません。
Unix 上では、数値を増やしても効果はありません。

mysql> show variables like “innodb_file_io_threads”;
+————————+——-+
| Variable_name | Value |
+————————+——-+
| innodb_file_io_threads | 4 |
+————————+——-+
1 row in set (0.00 sec)

mysql>

innodb_file_per_table
この変数が有効になると、InnoDB はデータとインデックスを共有テーブルスペースに格納
するのではなく、それ自体の .ibd ファイルを利用してそれぞれの新しいテーブルを作成し、
そこに格納します。デフォルトでは、共有テーブルスペースにテーブルを作成するという事になっています。

mysql> show variables like “innodb_file_per_table”;
+———————–+——-+
| Variable_name | Value |
+———————–+——-+
| innodb_file_per_table | OFF |
+———————–+——-+
1 row in set (0.01 sec)

mysql>

innodb_flush_log_at_trx_commit
※1秒毎、またはコミット毎にトランザクションをログファイルにフラッシュ

0 = コミットされたトランザクションは1秒毎にログファイルにフラッシュされ、
コミット毎にはフラッシュされません。


1 = コミットされたクエリはCOMMIT毎に、ログファイルにフラッシュされる
ので、失われるデータは”0”となります。(例外も起こるとは思いますが)

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 のクラッシュ復旧は影響を受けないので、値に関係なくクラッシュ復旧は行われます。

注意:InnoDB とトランザクションを共に利用して複製設定内で
最大の耐久力と一貫性を得る為に、お使いのマスタ サーバ my.cnf 内で
innodb_flush_log_at_trx_commit=1 と sync_binlog=1 を利用
しなければいけません。

mysql> show variables like “innodb_flush_log_at_trx_commit”;
+——————————–+——-+
| Variable_name | Value |
+——————————–+——-+
| innodb_flush_log_at_trx_commit | 1 |
+——————————–+——-+
1 row in set (0.01 sec)

mysql>

innodb_force_recovery
クラッシュ復旧モード。警告:この変数は、破損したデータベースからテーブルを捨てたいという
緊急の場合のみ、0以降の値に設定しなければいけません!可能な値は1から6です。
安全策として、InnoDB はこの変数が0以上の時はそのデータへの変更を阻止します

innodb_lock_wait_timeout
InnoDB トランザクションがロール バックされる前に、ロックを待つ秒数でのタイムアウト。
InnoDB は自動的にそれ自体のロック テーブル内でトランザクション デッドロックを検出し、
トランザクションをロールバックします。InnoDB は LOCK TABLES ステートメントを利用してロック
セットを通知します。デフォルトは50秒です。

mysql> show variables like “innodb_lock_wait_timeout”;
+————————–+——-+
| Variable_name | Value |
+————————–+——-+
| innodb_lock_wait_timeout | 50 |
+————————–+——-+
1 row in set (0.00 sec)

mysql>

innodb_locks_unsafe_for_binlog
この変数は InnoDB サーチとインデックス スキャン内でネクスト キー ロックをコントロールします。
デフォルトによってこの変数は0(無効)であり、それはネクスト キー ロックが有効であると意味します。

innodb_log_archive
InnoDB アーカイブ ファイルをログするかどうか。
この変数は歴史的理由により存在していますが、利用はされていません。

innodb_log_buffer_size
InnoDB がディスク上のログ ファイルに書き込む為に利用するバッファのバイトでのサイズ。
実用的な値の範囲は1MB から8MB です。デフォルトは1MB です。大きいログ バッファは、
トランザクション コミットの前にディスクにログを書き込む必要なく、大きいトランザクションが起動する事を許容します。
従って、もし大きいトランザクションを持っていたら、ログ ファイルを大きくしておく事でディスク I/O を節約する事ができます。

mysql> show variables like “innodb_log_buffer_size”;
+————————+———+
| Variable_name | Value |
+————————+———+
| innodb_log_buffer_size | 1048576 |
+————————+———+
1 row in set (0.00 sec)

mysql>

innodb_log_file_size

ログ グループ内のそれぞれの長いファイルのバイトでのサイズ。
ログ ファイルの結合したサイズは32ビット コンピュータ上で 4GB 以下でなければいけません。
デフォルトは5MB です。実用的な値は、N がグループ内のログ ファイル数だとして、
バッファ プールのサイズの1MB から 1/N-th です。
値が大きいほど、ディスク I/O を節約し、バッファ プール内で必要とされる
チェックポイント フラッシュ活動は少なくなります。

mysql> show variables like “innodb_log_file_size”;
+———————-+———+
| Variable_name | Value |
+———————-+———+
| innodb_log_file_size | 5242880 |
+———————-+———+
1 row in set (0.00 sec)

mysql>

innodb_log_files_in_group

ログ グループ内のログ ファイル数。InnoDB はファイルに輪状に書き込みをします。
デフォルト(そして推奨)は2です。

innodb_log_group_home_dir
InnoDB ログ ファイルへのディレクトリ パス。もし InnoDB ログ変数を何も指定しなければ、
デフォルトで MySQL データ ディレクトリ内に ib_logfile0 と ib_logfile1
という名前の2つの5MB ファイルを作成します。

innodb_max_dirty_pages_pct
これは0から100の範囲の間の整数です。デフォルトは90です。
InnoDB 内の主スレッドは、ダーティ (まだ書き込まれていない)ページの割合がこの値を超えないように
バッファ プールからページを書くように試みます。

mysql> show variables like “innodb_max_dirty_pages_pct”;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| innodb_max_dirty_pages_pct | 90 |
+—————————-+——-+
1 row in set (0.00 sec)

mysql>

innodb_max_purge_lag
この変数は、消去操作が遅れている時に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秒ごとに計算されます。もし消去される行をを知る事ができる、古い一貫した読み取りビューの為に消去が起動しなかったら、
その操作は遅れません。

mysql> show variables like “innodb_max_purge_lag”;
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| innodb_max_purge_lag | 0 |
+———————-+——-+
1 row in set (0.00 sec)

mysql>

innodb_mirrored_log_groups
データベースの為に残すログ グループの同一コピー数。現在は、この値は1に設定しなければいけません。

innodb_open_files
この変数は InnoDB 内で複数のテーブルスペースを利用する場合のみ関連があります。
それは InnoDB が同時にオープンしておける .ibd ファイルの最大数を指定します。
最大値は10です。デフォルトは300です。
.ibd ファイルに利用されるファイル記述子は、InnoDB に対しての物のみです。
それらは、–open-files-limit サーバ オプションによって指定された物からは独立していて、
テーブル キャッシュの操作に影響を与えません。

mysql> show variables like “innodb_open_files”;
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| innodb_open_files | 300 |
+——————-+——-+
1 row in set (0.00 sec)

mysql>

innodb_rollback_on_timeout
MySQL 5.1 内で、InnoDB はトランザクション タイムアウト上で最後のステートメントだけを
ロールバックします。このオプションが与えられると、トランザクション タイムアウトは InnoDB
がトランザクション全体を異常終了し、ロールバックするよう働きかけます。(MySQL 4.1と同じ動作です。)
この変数は、MySQL 5.1.15で追加されました。

mysql> show variables like “innodb_rollback_on_timeout”;
+—————————-+——-+
| Variable_name | Value |
+—————————-+——-+
| innodb_rollback_on_timeout | OFF |
+—————————-+——-+
1 row in set (0.00 sec)

mysql>

innodb_support_xa
ON か1(デフォルト)に設定されると、この変数は InnoDB が XA トランザクション内の二相コミット サポートを有効にします。
innodb_support_xa を有効にすると、トランザクションの準備でディスク フラッシュが余計に起こります。
XA を利用する事を気にしないのであれば、この変数を OFF か0に設定してこれを無効にする事ができ、
ディスク フラッシュの数を減らし、InnoDB 操作性能を向上させる事ができます。

innodb_table_locks
もし AUTOCOMMIT=0、InnoDB が LOCK TABLES を支持すると、MySQL は全てのスレッドがそれら
全てのロックをテーブルにリリースするまで LOCK TABLE .. WRITE から戻りません。
innodb_table_locks のデフォルト値は1です。それはもし AUTOCOMMIT=0 なら LOCK TABLES は
InnoDB がテーブルを内部的にロックするよう働きかける事を意味します。

innodb_thread_concurrency
InnoDB は、この変数から与えられた制限よりも少ない、またはそれと同等の制限の InnoDB
内部に多くの OS スレッドを一斉に保存しようと試みます。性能に関する問題を持ち、
多くのスレッドがセマフォを待っているという事が SHOW ENGINE INNODB STATUS によって明らかにされたのなら、
スレッド 「thrashing」 を持ち、この変数を低くまたは高く設定するよう試みる必要があります。
もしたくさんのプロセッサとディスクがあるコンピュータをお持ちであれば、それを有効に活用する為に
値を高く設定する事もできます。推奨値はお使いのシステムのプロセッサとディスク数の合計値です。
この変数の範囲は0から1000です。20以上の値は無限並行処理として読み取られます。 無限というのは、
並行チェックが無効になり、ミューテックスを獲得、リリースする事で発生するであろう、多量の負荷を防ぐという意味です。
MySQL 5.1.11以前はデフォルト値は20で、5.1.11以降は8となっています。

mysql> show variables like “innodb_thread_concurrency”;
+—————————+——-+
| Variable_name | Value |
+—————————+——-+
| innodb_thread_concurrency | 8 |
+—————————+——-+
1 row in set (0.00 sec)
mysql>

innodb_thread_sleep_delay
InnoDB スレッドは InnoDB の列に加わるまでに、マイクロ秒で何秒間スリープ状態にあるか。
デフォルト値は10,000です。0の値ではスリープ状態にはなりません。

sync_binlog
もし変数値が正数であれば、MySQL サーバはバイナリ ログへの毎 sync_binlog 書き込みごとに、
ディスク(fdatasync())にそのバイナリ ログを同期化します。オート コミット モードでは、
各ステートメントにつきバイナリ ログへの書き込みが1つあり、そうでなければ各トランザクションに
つき1つの書き込みがあると覚えて置いてください。デフォルトは、ディスクへの同期化を行わない0です。
クラッシュしてしまった場合には、バイナリ ログから最大1つのステートメントかトランザクション
が失われてしまう為、1の値が一番安全な値です。

Comments are closed.

Post Navigation