MySQLのテーブルオープン、クローズとテーブルキャッシュのチューニングについてまとめました。
※目次をクリックすると目次の下部にコンテンツが表示されます。
複数クライアントセッションが同一テーブルに対して異なる状態を持つことに伴う問題を少しでも少なくするため、テーブルはセッションごとに別々に開かれる。
これはメモリーの消費を増やすが、一般にパフォーマンスは向上する。
・MyISAMテーブルの場合は、テーブルを開いたそれぞれのクライアントにデータファイルに対するファイル記述子が必要になる。
これに対し、インデックスファイルに対するファイル記述子はすべてのセッションで共有できる。
・MySQLでは、テーブルはMySQLプロセスによってファイルハンドルを介して読み書きされる。
・テーブルキャッシュは、メモリ内にオープンしたテーブルを維持する事によって、オープン時のオーバーヘッドを軽減し、性能を向上させる。
テーブルのヘッダーはテーブルがオープンされるたびにカウンターの値を更新する必要があるのでオープンを維持すると効果が大きい。
・キャッシュがフルになったり、キャッシュ内に存在しないテーブルにアクセスがあった場合は、LRUアルゴリズムで古いキャッシュエントリを削除して置き換える。
削除されるテーブルは、テーブルをクローズしてディスクに書き戻す。
・FRUSH TABLESを実行するとキャッシュ内のすべてのテーブルがキャッシュから削除される。
２）テーブルのクローズ、キャッシュから削除
次の状況では、使用されていないテーブルが閉じられ、テーブルキャッシュから削除される。
・キャッシュが満杯のときに、キャッシュにないテーブルをスレッドが開こうとした場合。
・キャッシュにtable_open_cacheを超えるエントリがあり、あるスレッドがテーブルの使用を終えた場合。
・テーブルフラッシュオペレーションが起きたとき。いずれかのユーザーが FLUSH TABLES、mysqladmin flush-tables または mysqladmin refresh を実行した場合。
３）テーブルキャッシュ満杯時の割当て方法
・現在使用中でないテーブルは、最後に使用したときが古いものから順にキャッシュから削除される。
・キャッシュが満杯で削除可能なテーブルがなく、新たにテーブルを開く必要がある場合は、必要に応じてキャッシュが一時的に拡張される。
キャッシュが一時的に拡張された状況で、使用中のテーブルが使用されなくなったときは、そのテーブルが閉じられ、キャッシュから削除される。
・テーブルキャッシュに保持できる最大のオープンテーブル数。
・設定値を大きくするとテーブルに必要なファイルディスクリプタ数が増加する。使用メモリ量も増大する。
●チューニングにおける注意点
・複数のユーザーが同じテーブルにアクセスした際、各クライアント接続毎にオープンしたテーブルのコピーを使って処理するので、最大同時接続数（max_connections）と各接続で必要なテーブル数の積の数だけあるとテーブルオープンに伴う性能劣化を防ぐ事が出来る。
ただし、メモリ使用量が増えすぎないか注意する必要がある。
・オープンしているテーブル数はOpen_tablesステータス変数、Opened_tablesステータス変数でリスタート以降にオープンしたテーブル数を確認できる。
・テーブルキャッシュの値が少ないと新たにオープンするテーブルの数が増えるのでOpened_tablesステータス変数の値が増加する。
Opened_tablesステータス変数の値の増加状況を調べて増加していたらtable_open_cache変数の設定値を増やす事が検討する。
※テーブルキャッシュは最初は空でリスタート直後は急激に増加するので、リスタートして24時間ぐらい経過してからの値を調査する事を推奨。
・オープンしているテーブル数（Open_tablesステータス変数）がテーブルキャッシュの最大数（table_open_cache変数）に達していたらtable_open_cache変数の設定値を増やす事を検討する。
２）table_open_cache、max_connections、max_tmp_tablesサーバーシステム変数
・table_open_cache、max_connections、max_tmp_tablesサーバー変数は、サーバーのファイル最大数に影響し、この設定を増加する事によってOSによって制限されている1プロセスが持つことができるファイルディスクリプタの最大数まで実行が可能になる。
・table_open_cacheはmax_connectionsと関係する。
たとえば同時接続数が200の場合、最低200×Nのテーブルキャッシュサイズを用意するようにする。
Nは結合で使用するテーブル数の最大値を示す。
一時テーブルとファイル用のファイル記述子も必要なので注意する。
・MyISAMストレージエンジンでは1つのテーブルごとに2つのファイルディスクリプタが必要な事に注意。
・OSがtable_open_cacheで指定したファイルディスクリプタの数を処理できることを確認する。
table_open_cacheの設定が高すぎると、MySQLがファイルディスクリプタを使い果たして接続を拒否し、クエリーの実行ができなくなる。
–open-files-limitスタートアップオプションを使用すると、mysqldで使用可能なファイルディスクリプタの数を増やす事が出来る。
３）Opened_tablesサーバーステータス変数
mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | Opened_tables | 3241 | +---------------+-------+
この値はサーバーリスタート後の積算値。
たとえ多くのFLUSH TABLESを実行していない場合でも、この値が非常に大きい場合や急速に大きくなる場合は、テーブルキャッシュサイズを拡張する事を検討する。
４）table_definition_cacheサーバーシステム変数
・テーブルキャッシュのデメリットとして多くのファイルディスクリプタが必要でメモリ使用量が増えるという事がある。
・table_definition_cacheは、テーブル構造、カラムの型、インデックスなどのテーブル定義のみキャッシュし、メモリ上で行う。
このキャッシュ内のエントリは、ファイルディスクリプタを使用しないのでtable_cacheよりメモリ使用量が少なくなる。
・table_cacheと比べると大きな性能上の向上はないので、メモリ使用量に制約があったり、テーブル数が非常に多いなどtable_cacheが不足する場合の補助的な役割として使用する。
５）open_files_limitの設定とファイルディスクリプタ
テーブルキャッシュの設定値が高い場合、ファイルディスクリプタを多く使用し、OSの制限に影響を受ける場合がある。
①Linuxのファイルディスクリプタの制限
以下、CentOS6.6の環境で確認。
●ファイルディスクリプタの最大数
システムでサポートされているファイル・ディスクリプタの最大数
$ cat /proc/sys/fs/file-max
188314
●ファイルディスクリプタの現在のシステム制限を確認
$ cat /proc/sys/fs/file-nr
1568 0 188314
上記表示結果は左から順番に下記を示す。
・割当て済ファイルディスクリプタ数
・空きの割当て済ファイルディスクリプタ数
・システムでサポートされているファイルディスクリプタの最大数
（/proc/sys/fs/file-max と同じ値）
●最大ファイルディスクリプタの設定を変更
rootユーザーで/etc/sysctl.confに下記設定
fs.file-max = value
次のコマンドを実行して、変更を適用。
# /sbin/sysctl -p
●プロセスあたりの最大ファイルディスクリプタを確認
$ ulimit -n
1024
# cat /proc/1456/limits | grep “Max open files”
Max open files 1024 4096 files
※1456はmysqldのプロセスID。1024がsoft limit。4096がhard limit
②MySQLでプロセスあたりの最大ファイルディスクリプタを設定
my.cnfに下記設定
[mysqld_safe]
open_files_limit = 設定値
rootユーザーでmysqld_safe経由でmysqldを起動すると、mysqld_safeがmy.cnfを読んでulimit -n に設定値を渡して実行する。
③チューニングに関する注意点
MySQLがオープンする最大テーブル数がtable_open_cacheの設定値によって決められていて、MyISAMテーブルは一つのテーブルあたり二つのファイルディスクリプタが必要なので、この点を考慮してチューニングする。