13.5.3 InnoDB 設定

13.5.3. `InnoDB` 設定

13.5.3.1. Per-Table テーブルスペースを利用する
13.5.3.2. 共有テーブルスペースに未加工デバイスを利用する

InnoDB ストレージエンジンはデフォルトにて有効になっています。もし InnoDB テーブルを利用したくなければ、お使いの MySQL オプションファイルに skip-innodb オプションを追加する事ができます。

注意:InnoDB は MySQL に、コミット、ロールバック、クラッシュ復旧機能を持つトランザクションセーフ(ACID 適合)ストレージエンジンを提供します。しかし、もし基礎となる OS やハードウェアが広告どおりに機能しなければ、それを行う事はできません。多くの OS やディスクサブシステムが、性能を向上させる為に書き込み操作を遅らせたり再オーダしたりするでしょう。いくつかの OS 上で、ファイルの全ての未書き込みデータがフラッシュされるまで待つ必要があるそのシステムコール、— fsync() — は、実際にデータが安定したストレージにフラッシュされる前に返されます。この為、OS のクラッシュや停電によって最近コミットされたデータが破損したり、さらに最悪の場合、書き込み操作が再オーダされた為にデータベースが破損する事もありますもしデータの整合性が重要であるなら、製造で何かを利用する前に「pull-the-plug」テストを行うべきです。Mac OS X 10.3 以降のバージョンでは、InnoDB は特別な fcntl() ファイルフラッシュ法を利用します。Linux 下では、ライトバックキャッシュを無効にする 事をお勧めします。

ATAPI ハードディスク上では、hdparm -W0 /dev/hda のようなコマンドがライトバックキャッシュを無効にする働きをします。いくつかのドライブやディスクコントローラでは、ライトバックキャッシュを無効にできない可能性が有るので注意してください。

InnoDB ストレージエンジンによって管理されている2つの重要なディスクベースリソースは、そのテーブルスペースデータファイルとログファイルです。

注意:もし InnoDB 設定オプションを全く指定しなければ、MySQL は MySQL データディレクトリ内に、ibdata1 という名前の 10MB の自動延長データファイルと、ib_logfile0 と ib_logfile1 という名前の 5MB のログファイルを作成します。高性能を得るには、次の例にあるように InnoDB パラメータを明示的に提供する必要があります。当然ながら、お使いのハードウェアとその要求に合うように、設定を編集する必要があります。

MySQL Enterprise. ご自分専用の環境に適応する設定に関するアドバイスの為に、MySQL ネットワークモニタリングとアドバイスサービスの購読をお勧めします。追加情報については http://www-jp.mysql.com/products/enterprise/advisors.html を参照してください。

ここに表されている例は代表的な物です。InnoDB に関連した設定パラメータに関する追加情報は、項13.5.4. 「InnoDB 起動オプションとシステム変数」を参照してください。

InnoDB テーブルスペースファイルを設定する為には、my.cnf オプションファイルの [mysqld] セクション内の innodb_data_file_path オプションを利用してください。Windows 上では、代わりに my.ini を利用する事ができます。innodb_data_file_path の値は、1つまたは複数のデータファイル仕様のリストでなければいけません。複数のデータファイルに名前を付けたら、セミコロン文字(‘;’)でそれらを区切ってください:

innodb_data_file_path=datafile_spec1[;datafile_spec2]...

例えば、デフォルトと同じ特徴を持つテーブルスペースを明示的に作成する設定は、次のようになります:

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend

この設定は、ibdata1 と名づけられた自動延長の単一10MB データファイルを構成します。そのファイルの場所は指定されない為、InnoDB がデフォルトでそれを MySQL データディレクトリ内に作成します。

MB か GB の単位を指定する為に、M か G のサフィックス文字を利用してサイズが指定されます。

データディレクトリ内で、ibdata1 と名づけられた固定サイズ50MB のデータファイルと、ibdata2 と名づけられた50MBの自動拡大ファイルを含むテーブルスペースは、次のように設定されます:

[mysqld]
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

データファイル仕様の完全構文は、ファイル名、そのサイズ、そしていくつかの任意の属性を含んでいます:

file_name:file_size[:autoextend[:max:max_file_size]]

autoextend 属性やそれらに続く物は、innodb_data_file_path ライン内の最後のデータファイルにのみ利用できます。

最後のデータファイルに autoextend オプションを指定すると、InnoDB はもしテーブルスペースの中に空き領域がなければデータファイルを拡大します。デフォルトで、インクリメントは一回に付き8MB となっています。それは innodb_autoextend_increment システム変数を変更する事で修正できます。

もしディスクがいっぱいになると、別のディスク上に別のデータファイルを追加したくなるでしょう。項13.5.7. 「InnoDB データとログファイルの追加と削除」に既存のテーブルスペースの再設定に関する説明があります。

InnoDB にはファイルシステムの最大サイズが分からないので、それが 2GB のような小さい値の場合は注意してください。自動拡大データファイルの最大サイズを指定するには、max 属性を利用してください。次の設定は、ibdata1 が最大500MB まで大きくなる事を許容します:

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend:max:500M

InnoDB はデフォルトで MySQL データディレクトリ内にテーブルスペースファイルを作成します。場所を明示的に指定するには、innodb_data_home_dir オプションを利用してください。例えば、ibdata1 と ibdata2 と名づけられた2つのファイルを、/ibdata ディレクトリ内で作成して利用するには、InnoDB をこのように設定してください:

[mysqld]
innodb_data_home_dir = /ibdata
innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

注意:InnoDB はディレクトリを作成しないので、サーバを起動する前に必ず /ibdata ディレクトリが存在する事を確認してください。これは、ご自分が設定する別のログファイルにも全て当てはまります。必要なディレクトリを作成する為に、Unix か DOS mkdir コマンドを利用してください。

InnoDB は innodb_data_home_dir の値を原文どおりにデータファイル名に連結させ、必要に応じてパス名セパレータ（スラッシュまたはバックスラッシュ）を値の間に追加して、各データファイルのディレクトリパスを形作ります。もし innodb_data_home_dir オプションについて my.cnf 内で全く触れられなければ、MySQL データディレクトリを意味する「dot」 directory ./ がデフォルト値になります。 (MySQL サーバは、実行を始める時にその時起動しているディレクトリを、それ自体のデータディレクトリに変更します。)

もし innodb_data_home_dir を空の文字列として指定すれば、innodb_data_file_path 値内にリストされたデータファイルに完全なパスを指定する事ができます。次の例は、前出の物と同等です:

[mysqld]
innodb_data_home_dir =
innodb_data_file_path=/ibdata/ibdata1:50M;/ibdata/ibdata2:50M:autoextend

シンプルなmy.cnf 例。128MB RAM とハードディスクが1つあるコンピュータを持っていると仮定してください。次の例は、autoextend 属性を含む、InnoDB の為の my.cnf か、 my.ini 内で可能な設定パラメータを表しています。この例は、InnoDB データファイルとログファイルをいくつかのディスクに分散する事を希望しない、Unix と Windows 両方のほとんどのユーザに適しています。これは、MySQL データディレクトリ内に、自動拡大データファイル ibdata1 と、2つの InnoDB ログファイル ib_logfile0 と ib_logfile1 を作成します。

[mysqld]
# You can write your other MySQL server options here
# ...
# Data files must be able to hold your data and indexes.
# Make sure that you have enough free disk space.
innodb_data_file_path = ibdata1:10M:autoextend
#
# Set buffer pool size to 50-80% of your computer's memory
innodb_buffer_pool_size=70M
innodb_additional_mem_pool_size=10M
#
# Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=20M
innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1

MySQL サーバがデータディレクトリ内にファイルを作成する為の正当なアクセス権を持っている事を確認してください。さらに一般的には、サーバはデータファイルとログファイルを作成しなければいけないディレクトリ内にアクセス権を持っている必要があります。

データファイルはいくつかのファイルシステム内で 2GB 以下でなければいけない事に注意してください。結合したログファイルのサイズは 4GB 以下でなければいけません。結合したデータファイルのサイズは最低 10MB でなければいけません。

InnoDB テーブルスペースを初めて作成する時は、MySQL サーバをコマンドプロンプトから起動するのが一番良い方法です。その時 InnoDB はデータベース作成に関する情報をスクリーンにプリントする事ができる為、何が起こっているかを知る事ができるのです。例えば、もし Windows 上で mysqld が C:\Program Files\MySQL\MySQL Server 5.1\bin 内にあったら、次ように起動する事ができます:

C:\> "C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld" --console

もしスクリーンにサーバアウトプットを送らないなら、InnoDB がスタートアップ作業の最中に何をプリントするのかを確認する為にサーバのエラーログを確認してください。

InnoDB によって表示される情報の例については、項13.5.5. 「InnoDB テーブルスペースを作成する」を参照してください。

サーバが起動した時に読み込むオプションファイルの [mysqld] グループ内にある InnoDB オプションをおく事ができます。オプションファイルの場所に関しては、項3.3.2. 「オプションファイルの使用」で紹介されています。

もし MySQL をインストールと設定ウィザードを利用して Windows 上にインストールしたら、そのオプションファイルはお使いの MySQL インストールディレクトリ内の my.ini ファイルになります。詳しくは項2.3.4.14. 「my.ini ファイルのロケーション」を参照してください。

もしお使いの PC が、C: ドライブがブートドライブではないブートローダを利用していたら、残されたオプションは Windows ディレクトリ(通常 C:\WINDOWS か C:\WINNT)内の my.ini ファイルを利用する事だけです。WINDIR の値をプリントするには、コンソールウィンドウ内のコマンドプロンプトで SET コマンドを利用する事ができます:

C:\> SET WINDIR
windir=C:\WINDOWS

mysqld が特定のファイルからだけオプションを読み込むようにしたいのであれば、サーバを起動する時 --defaults-file オプションをコマンドラインの最初のオプションとして利用する事ができます:

mysqld --defaults-file=your_path_to_my_cnf

進歩した my.cnf 例。ディレクトリパス /、/dr2 そして /dr3 に 2GB RAM と3つの 60GB ハードディスクを持つ Linux コンピュータを持っていると仮定してください。次の例は、InnoDB の my.cnf 内で可能な設定パラメータを表しています。

[mysqld]
# You can write your other MySQL server options here
# ...
innodb_data_home_dir =
#
# Data files must be able to hold your data and indexes
innodb_data_file_path = /db/ibdata1:2000M;/dr2/db/ibdata2:2000M:autoextend
#
# Set buffer pool size to 50-80% of your computer's memory,
# but make sure on Linux x86 total memory usage is < 2GB
innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=20M
innodb_log_group_home_dir = /dr3/iblogs
#
innodb_log_files_in_group = 2
#
# Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=250M
innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1
innodb_lock_wait_timeout=50
#
# Uncomment the next lines if you want to use them
#innodb_thread_concurrency=5

場合によっては、もし全てのデータが同じディスク上に置かれていなければ、データベース性能が向上します。ログファイルをデータとは別のディスク上に置く事で、性能が向上する事が多いです。どのようにするかを例で説明しています。.これは、2つのデータファイルを別々のディスクに、そしてログファイルを3つ目のディスクにおきます。InnoDB は最初のデータファイルを先に利用してテーブルスペースを埋めていきます。InnoDB データファイルとして、未加工ディスクパーティション（未加工デバイス）を利用する事ができ、そのおかげで I/O のスピードが向上します。項13.5.3.2. 「共有テーブルスペースに未加工デバイスを利用する」を参照してください。

警告: 32-bit GNU/Linux x86 上では、メモリ使用を高く設定しすぎないように注意してください。glibc はプロセスヒープがスレッドスタックよりも大きくなる事を許容する可能性があり、その為サーバがクラッシュしてしまうかもしれません。もし次の式の値が2GB に近い、またはそれを上回っていたら危険です:

innodb_buffer_pool_size
+ key_buffer_size
+ max_connections*(sort_buffer_size+read_buffer_size+binlog_cache_size)
+ max_connections*2MB

各スレッドはスタック(通常2MB ですが、MySQL AB バイナリ内ではたったの256KB)を利用し、そして最悪の場合、sort_buffer_size + read_buffer_size 追加メモリも利用します。

MySQL をご自分でコンパイルする事により、32ビット Windows 内で最高 64GB の物理メモリを利用する事ができます。項13.5.4. 「InnoDB 起動オプションとシステム変数」内の innodb_buffer_pool_awe_mem_mb に関する説明を参照してください。

別の mysqld サーバパラメータはどのように調整するのでしょう？次の値は典型的な物であり、ほとんどのユーザに適応しています:

[mysqld]
skip-external-locking
max_connections=200
read_buffer_size=1M
sort_buffer_size=1M
#
# Set key_buffer to 5 - 50% of your RAM depending on how much
# you use MyISAM tables, but keep key_buffer_size + InnoDB
# buffer pool size < 80% of your RAM
key_buffer_size=value

13.5.3. InnoDB 設定

13.5.3. `InnoDB` 設定