~Linux、MySQLからAJAXまでMySQLのテーブルオープン、クローズとテーブルキャッシュのチューニングMySQLのテーブルオープン、クローズとテーブルキャッシュのチューニングについてまとめました。※目次をクリックすると目次の下部にコンテンツが表示されます。1.クライアント接続とマルチスレッド、セッション、ファイルディスクリプタ2.MySQLのテーブルキャッシュの概要3.テーブルキャッシュの設定値、チューニングクライアント接続とマルチスレッド、セッション、ファイルディスクリプタ・MySQLはマルチスレッド化されているため、多数のクライアントが同時に同じテーブルに対してクエリーを使用すること出来る。複数クライアントセッションが同一テーブルに対して異なる状態を持つことに伴う問題を少しでも少なくするため、テーブルはセッションごとに別々に開かれる。これはメモリーの消費を増やすが、一般にパフォーマンスは向上する。・MyISAMテーブルの場合は、テーブルを開いたそれぞれのクライアントにデータファイルに対するファイル記述子が必要になる。これに対し、インデックスファイルに対するファイル記述子はすべてのセッションで共有できる。・MySQLでは、テーブルはMySQLプロセスによってファイルハンドルを介して読み書きされる。・テーブルキャッシュは、メモリ内にオープンしたテーブルを維持する事によって、オープン時のオーバーヘッドを軽減し、性能を向上させる。テーブルのヘッダーはテーブルがオープンされるたびにカウンターの値を更新する必要があるのでオープンを維持すると効果が大きい。・キャッシュがフルになったり、キャッシュ内に存在しないテーブルにアクセスがあった場合は、LRUアルゴリズムで古いキャッシュエントリを削除して置き換える。削除されるテーブルは、テーブルをクローズしてディスクに書き戻す。TABLESを実行するとキャッシュ内のすべてのテーブルがキャッシュから削除される。次の状況では、使用されていないテーブルが閉じられ、テーブルキャッシュから削除される。・キャッシュが満杯のときに、キャッシュにないテーブルをスレッドが開こうとした場合。・キャッシュにtable_open_cacheを超えるエントリがあり、あるスレッドがテーブルの使用を終えた場合。・テーブルフラッシュオペレーションが起きたとき。・現在使用中でないテーブルは、最後に使用したときが古いものから順にキャッシュから削除される。・キャッシュが満杯で削除可能なテーブルがなく、新たにテーブルを開く必要がある場合は、必要に応じてキャッシュが一時的に拡張される。キャッシュが一時的に拡張された状況で、使用中のテーブルが使用されなくなったときは、そのテーブルが閉じられ、キャッシュから削除される。・テーブルキャッシュに保持できる最大のオープンテーブル数。・設定値を大きくするとテーブルに必要なファイルディスクリプタ数が増加する。使用メモリ量も増大する。・複数のユーザーが同じテーブルにアクセスした際、各クライアント接続毎にオープンしたテーブルのコピーを使って処理するので、最大同時接続数(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サーバー変数は、サーバーのファイル最大数に影響し、この設定を増加する事によって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で使用可能なファイルディスクリプタの数を増やす事が出来る。|+---------------+-------+この値はサーバーリスタート後の積算値。TABLESを実行していない場合でも、この値が非常に大きい場合や急速に大きくなる場合は、テーブルキャッシュサイズを拡張する事を検討する。・テーブルキャッシュのデメリットとして多くのファイルディスクリプタが必要でメモリ使用量が増えるという事がある。・table_definition_cacheは、テーブル構造、カラムの型、インデックスなどのテーブル定義のみキャッシュし、メモリ上で行う。このキャッシュ内のエントリは、ファイルディスクリプタを使用しないのでtable_cacheよりメモリ使用量が少なくなる。・table_cacheと比べると大きな性能上の向上はないので、メモリ使用量に制約があったり、テーブル数が非常に多いなどtable_cacheが不足する場合の補助的な役割として使用する。5)open_files_limitの設定とファイルディスクリプタテーブルキャッシュの設定値が高い場合、ファイルディスクリプタを多く使用し、OSの制限に影響を受ける場合がある。以下、CentOS6.6の環境で確認。188314上記表示結果は左から順番に下記を示す。value次のコマンドを実行して、変更を適用。files※1456はmysqldのプロセスID。設定値を渡して実行する。3チューニングに関する注意点MySQLがオープンする最大テーブル数がtable_open_cacheの設定値によって決められていて、MyISAMテーブルは一つのテーブルあたり二つのファイルディスクリプタが必要なので、この点を考慮してチューニングする。コメントをキャンセルメールアドレスが公開されることはありません。コメントデータの処理方法の詳細はこちらをご覧ください。