14.3 InnoDB の構成

14.3.1 読み取り専用操作用の InnoDB の構成

InnoDB 構成について最初に決定することは、InnoDB データファイルのレイアウト方法、および InnoDB ストレージエンジン用に割り当てるメモリーの量です。これらの選択を記録するには、起動時に MySQL で読み取られる構成ファイル内に記録するか、または起動スクリプトのコマンド行オプションとして指定します。オプション、説明、および許可されるパラメータ値の完全なリストについては、セクション14.12「InnoDB の起動オプションおよびシステム変数」を参照してください。

InnoDB テーブルスペースおよびログファイルの概要

InnoDB ストレージエンジンで管理されている 2 つの重要なディスクベースのリソースは、そのテーブルスペースデータファイルとログファイルです。InnoDB 構成オプションを指定しない場合は、MySQL によって MySQL データディレクトリ内に、わずかに 12M バイトよりも大きい ibdata1 という名前の自動拡張データファイルと、ib_logfile0 および ib_logfile1 という名前の 2 つのログファイルが作成されます。これらのサイズは、innodb_log_file_size システム変数のサイズで指定されます。適切なパフォーマンスを実現するには、次の例で説明するように、InnoDB パラメータを明示的に指定します。当然、ハードウェアおよび要件に合うように設定を編集します。

ここで示す例は代表的なものです。InnoDB に関連する構成パラメータに関する追加情報については、セクション14.12「InnoDB の起動オプションおよびシステム変数」を参照してください。

ストレージデバイスに関する考慮事項

場合によっては、一部のデータが同じ物理ディスク上に配置されてない場合に、データベースのパフォーマンスが改善されることがあります。非常に多くの場合、ログファイルをデータとは別のディスク上に配置すると、パフォーマンスの改善に役立ちます。次の例で、この方法を示します。2 つのデータファイルが別々のディスク上に配置され、ログファイルが 3 台目のディスク上に配置されています。InnoDB では、1 番目のデータファイルから順番にテーブルスペースに収容されます。InnoDB データファイルとして、RAW ディスクパーティション (RAW デバイス) を使用することもできます。これにより、I/O の速度が上がる可能性があります。セクション14.5.8「共有テーブルスペースでの RAW ディスクパーティションの使用」を参照してください。

注意

InnoDB はトランザクションセーフな (ACID に準拠した) MySQL 用のストレージエンジンであり、ユーザーデータを保護するためのコミット、ロールバック、およびクラッシュリカバリ機能を備えています。ただし、ベースとなるオペレーティングシステムやハードウェアが公表どおりに機能しない場合は、実行できません。多くのオペレーティングシステムやディスクサブシステムでは、パフォーマンスを改善するために書き込み操作が遅延したり、再指示されたりする可能性があります。一部のオペレーティングシステムでは、まさに fsync() システムコールは、ファイルのすべての未書き込みデータがフラッシュされるまで待機するべきですが、実際には、データが安定したストレージにフラッシュされる前に返される可能性があります。このため、オペレーティングシステムのクラッシュや停電によって最近コミットされたデータが破損したり、さらに最悪の場合、書き込み操作が再指示されたためにデータベースが破損したりすることもあります。データの完全性が重要である場合は、本番環境で何かを使用する前に、何らかの形で「電源プラグを抜く」テストを実行してください。OS X 10.3 以降の InnoDB では、特別な fcntl() ファイルフラッシュ方式が使用されます。Linux では、ライトバックキャッシュを無効にすることが推奨されています。

ATA/SATA ディスクドライブ上で hdparm -W0 /dev/hda のようなコマンドを使用すると、ライトバックキャッシュを無効にできる場合があります。一部のドライブやディスクコントローラでは、ライトバックキャッシュを無効にできない可能性があることに注意してください。

ユーザーを保護する InnoDB のリカバリ機能に関しては、InnoDB では二重書き込みバッファーと呼ばれる構造に関連したファイルフラッシュ技術が使用されています。これは、デフォルトで有効になっています (innodb_doublewrite=ON)。二重書き込みバッファーを使用すると、クラッシュや停電のあとのリカバリの安全性が高まるだけでなく、fsync() 操作の必要性が減るため、ほとんどの種類の Unix でパフォーマンスが向上します。データの完全性またはエラーの可能性に関心がある場合は、innodb_doublewrite オプションを有効のままにすることが推奨されています。二重書き込みバッファーの追加情報については、セクション14.10「InnoDB のディスク I/O とファイル領域管理」を参照してください。

注意

データの信頼性が考慮事項となっている場合は、NFS ボリューム上でデータファイルやログファイルが使用されるように InnoDB を構成しないでください。発生する可能性のある問題は、OS および NFS のバージョンによって異なります。これらの問題には、競合する書き込みからの保護が不足しているなどの問題や、最大ファイルサイズ上の制限などが含まれます。

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:12M:autoextend

この設定では、ibdata1 という名前の 12M バイトの自動拡張データファイルが 1 つ構成されます。ファイルの場所が指定されていないため、InnoDB によって、デフォルトで MySQL データディレクトリ内に作成されます。

K バイト、M バイト、または G バイトの単位を指定するために、サイズは K、M、または G のサフィクス文字を使用して指定されます。

データディレクトリ内にある ibdata1 という名前の 50M バイトの固定サイズデータファイルと、ibdata2 という名前の 50M バイトの自動拡張ファイルを含むテーブルスペースは、次のように構成できます。

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

データファイルを指定するための完全な構文には、ファイル名、そのサイズ、および複数のオプション属性が含まれています。

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

autoextend および max 属性は、innodb_data_file_path 行内の最後のデータファイルでのみ使用できます。

最後のデータファイルに autoextend オプションを指定すると、テーブルスペースに空き領域がなくなった場合に、InnoDB はデータファイルを拡張します。デフォルトでは、一度に 8M バイトずつ増分されます。増分を変更するには、innodb_autoextend_increment システム変数を変更します。

ディスクがいっぱいになったら、別のディスク上に別のデータファイルを追加するといいでしょう。テーブルスペースを再構成する手順については、セクション14.5.7「InnoDB ログファイルの数またはサイズの変更、および InnoDB テーブルスペースのサイズの変更」を参照してください。

InnoDB ではファイルシステムの最大ファイルサイズが認識されないため、最大ファイルサイズが 2G バイトのような小さい値になっているファイルシステムでは注意してください。自動拡張データファイルの最大サイズを指定するには、autoextend 属性のあとに max 属性を使用してください。最大サイズを超えると致命的なエラーが発生し、クラッシュする可能性もあるため、ディスクの使用率を制約することが非常に重要である場合に限り、max 属性を使用してください。次の構成では、ibdata1 が最大で 500M バイトの制限まで増大することが許可されます。

[mysqld]
innodb_data_file_path=ibdata1:12M: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 コマンドを使用します。

MySQL サーバーがデータディレクトリ内にファイルを作成するための適切なアクセス権を持っていることを確認してください。さらに一般的に言えば、サーバーはデータファイルやログファイルを作成する必要のあるディレクトリ内に、アクセス権を持っている必要があります。

InnoDB は innodb_data_home_dir の値をテキストとしてデータファイル名に連結させ、必要に応じてパス名の区切り文字 (スラッシュまたはバックスラッシュ) を値の間に追加することで、各データファイルのディレクトリパスを形成します。my.cnf に innodb_data_home_dir オプションがまったく指定されていない場合は、デフォルト値が「ドット」ディレクトリ ./ (つまり、MySQL データディレクトリ) になります。(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

InnoDB 構成オプションの指定

小規模なシステム向けのサンプル my.cnf ファイル。512M バイトの 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:12M:autoextend
#
# Set buffer pool size to 50-80% of your computer's memory
innodb_buffer_pool_size=256M
innodb_additional_mem_pool_size=20M
#
# Set the log file size to about 25% of the buffer pool size
innodb_log_file_size=64M
innodb_log_buffer_size=8M
#
innodb_flush_log_at_trx_commit=1

一部のファイルシステムでは、データファイルを 2G バイト未満にする必要があることに注意してください。ログファイルを結合したサイズは、最大で 512G バイトまでにすることができます。データファイルを結合したサイズは、10M バイトをわずかに超える大きさにする必要があります。

InnoDB システムテーブルスペースの設定

はじめて InnoDB システムテーブルスペースを作成する際は、コマンドプロンプトから MySQL サーバーを起動する方法が最適です。そのあと、データベースの作成に関する情報が InnoDB の画面に出力されるため、何が発生しているのかを確認できます。たとえば、Windows の場合、mysqld が C:\Program Files\MySQL\MySQL Server 5.6\bin に配置されていれば、次のように起動できます。

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

サーバーの出力が画面に送信されない場合は、サーバーのエラーログをチェックして、起動プロセス中に InnoDB から出力された内容を確認してください。

InnoDB で表示される情報の出力例については、セクション14.5.1「InnoDB テーブルスペースの作成」を参照してください。

MySQL 構成ファイルの編集

サーバーの起動時に読み取られる任意のオプションファイルの [mysqld] グループ内に、InnoDB オプションを配置できます。オプションファイルの場所については、セクション4.2.6「オプションファイルの使用」で説明されています。

インストールおよび構成ウィザードを使用して、MySQL を Windows 上にインストールした場合は、オプションファイルが MySQL インストールディレクトリ内に配置された my.ini ファイルになります。セクション2.3.3「MySQL Installer を使用した MySQL の Microsoft Windows へのインストール」を参照してください。

C: ドライブがブートドライブではないブートローダーが PC で使用されている場合は、Windows ディレクトリ (通常は C:\WINDOWS) 内の my.ini ファイルを使用することが唯一のオプションとなります。コンソールウィンドウ内のコマンドプロンプトで SET コマンドを使用すると、WINDIR の値を出力できます。

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

mysqld で特定のファイルからのオプションのみが読み取られることを確認するには、サーバーの起動時に --defaults-file オプションをコマンド行の最初のオプションとして使用します。

mysqld --defaults-file=your_path_to_my_cnf

大規模なシステム向けのサンプル my.cnf ファイル。ディレクトリパス /、/dr2、および /dr3 に 2G バイトの RAM と 3 台の 60G バイトハードディスクが搭載された 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
#
# 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 line if you want to use it
#innodb_thread_concurrency=5

InnoDB の最大メモリー割り当ての決定

警告

32 ビット版の GNU/Linux x86 では、高すぎるメモリー使用率を設定しないように注意してください。glibc では、プロセスヒープがスレッドスタック上で増加することが許可されている可能性があるため、サーバーがクラッシュします。次の式の値が 2G バイトに近づいていたり、超えていたりする場合は危険です。

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

各スレッドではスタックが使用され (多くの場合は 2M バイトですが、Oracle Corporation が提供する MySQL バイナリでは 256K バイトだけです)、最悪のケースでは、sort_buffer_size + read_buffer_size の追加メモリーも使用されます。

その他の 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

Linux の場合、カーネルで大規模ページのサポートが有効になっていれば、InnoDB は、バッファープールや追加メモリープールのメモリーを割り当てる際に大規模なページを使用できます。セクション8.11.4.2「ラージページのサポートの有効化」を参照してください。