MySQL table_cacheの設定に関して

久しぶりにMySQLネタ。今回はtable_cacheの設定に関するお話。

table_cacheの意味と仕組み

まずはじめにtable_cacheの意味をおさらい。table_cacheは1度開かれたテーブルをメモリ上に維持しておき、再利用することでテーブルを開くことによる負荷を低減するためのもの。MySQLではオープンしているテーブル数は「show open tables」コマンドで確認できる。

リファレンスマニュアルでも説明されているが、MySQLでは同じテーブルに対して同時にアクセスするスレッドがあった場合、それぞれが同じテーブルを重複して開く仕様となっており、これによりマルチスレッド環境でのパフォーマンスを向上させている。このことから、MySQLサーバで開かれるテーブル数の最大値は最低でもmax_connectionsと同数であることがわかる。「最低でも」と制限がつくのはjoinを含むクエリによって複数のテーブルが開かれるケースがあるためで、その場合にはmax_connections×n（nは joinによって結合されるテーブル数）が最大値となる。

table_cache, max_connections, open_files_limitの関係

【自宅サーバでの事象】

自宅サーバ(Linux+MySQL 5.0)で、ふとmysqld_safeのログを見るとこんなメッセージが。



[Warning] Changed limits: max_open_files: 65535  max_connections: 100  table_cache: 32712

当該サーバの設定ファイル(/etc/my.cnf)で、関連するパラメータの設定値は以下の通り。この例では、設定ファイルにはopen_files_limitパラメータは設定されておらず、mysqldにより65535にセットされていた(show variables like 'open_files_limit'」で確認)。



max_connections=100

table_cache=64000

さて、ここで table_cache, max_connections, open_files_limit の関係について考える。マニュアルを探したところ、何故か見当たらない(昔はあったような？？？)。

【table_cache自動補正の動作】

mysqld が同時に使用可能なファイル数は open_files_limit というパラメータで指定する。MySQLにおけるtable_cacheの値は、基本的には下表の大きい値が後勝ちルールで適用・自動補正される。

A	table_cache*2+max_connections+10
B	max_connections*5
C	現在のファイル記述子の制限値(getrlimit(2) の rlim_cur の値)

mysqld は最低でも「(A) table_cache*2+max_connections + 10」は必要だと考えるため、open_files_limit が (A) よりも小さければ、自動的に (A) の値まで大きくする。table_cache を2倍しているのは、MyISAM が1テーブルにつき2ファイル(*.MYD、*.MYI)を使用するため、また10 を足しているのは標準入出力エラー出力と、ログファイル等に安全率を乗じたものと考えられる。次の評価ルールとして、「(B) max_connections * 5」の方が (A) よりも大きければ、open_files_limit は (B) になる。

(A), (B) よりも「(C)現在のファイル記述子の制限値(getrlimit(2) の rlim_cur の値)」が大きければ、(C) が採用される。open_files_limit の最大値は 65535。また、setrlimit(2) で open_files_limit 分のファイル記述子が割り当てられない場合(おそらくrootで起動しなかった場合※)は、(C) になる。※RHELやCentOSのRPMパッケージに付属している起動停止スクリプトでは、mysqld_safeをrootユーザでキック(mysqld自体はmysqlユーザで起動)するため問題ない。

そのため結果的に (A) よりも小さな値になってしまう場合がある。open_files_limit パラメータを指定しておらず (A) よりも小さい値になった場合は、max_connections と table_cache が次のように調整される。

max_connections が open_files_limit-10-64*2 よりも大きい場合は、その値まで小さくなる。
table_cache が (open_files_limit-10-max_connections)/2 よりも大きい場合は、その値まで小さくなる。ただし 64 よりは小さくならない。

この調整が行われると、エラーログに次のように出力される(前述のログの通り)。



Changed limits: max_open_files: XXX  max_connections: XXX  table_cache: XXX

open_files_limit パラメータを指定している場合は、エラーログに次のように出力されるだけで、table_cache と max_connections の調整は行われない。この場合は稼働中にファイル記述子が足りなくなる可能性があるため、注意が必要。



Could not increase number of max_open_files to more than XXX (request: XXX)

私の環境では、table_cacheはやっぱり大きめ(65536)に取りたい！でもmax_connectionsは100で保ちたい！･･･という設定で見事前述の警告メッセージ「Changed limits: max_open_files: 65535 max_connections: 100 table_cache: 32712」の通りtable_cacheの値が65536⇒32712に補正されていたわけだが、mysqld_safeを起動する起動スクリプトにrootユーザのulimit -n(open files)の上限値を拡張してあげて、無事解決。
# ulimitの設定値はopen_files_limitに設定すべき「table_cache*2 + max_connections +10」の値。