MySQL5.6 performance_schemaとtable_definition_cacheの調整

MySQLを5.6にアップデートしてからメモリ使用量が明らかに増えました。メモリが少ないサーバでは無駄なメモリの消費は死活問題です。パラメータを調整していくうちに色々わかったことをまとめてみます。

MySQL5.6の新機能がメモリを消費する

MySQL5.1のコンフィグでMySQL5.6を起動するとメモリ使用量が跳ね上がります。
原因は2つ。1つはDBの低レベルモニタリングを行うパフォーマンススキーマがデフォルトで有効になったこと。もう1つはパフォーマンススキーマが有効かつtable_definition_cacheの値が801を超えると300MB以上メモリ消費が増えるバグがあること。

デフォルト有効なので対策をしないと下のグラフの7日のようになります。これに対してtable_definition_cache=400を追加してまずは解決しましたが、それでもアップデート前よりはまだ高い状態です。

MySQL5.6からデフォルト有効になった機能を調べてみると、パフォーマンススキーマ(performance_schema)が影響しているらしいことがわかりました。

パフォーマンススキーマ有りなしのメモリ使用量と速度比較

パフォーマンススキーマの影響と、MySQL5.6アップデート直後のメモリ使用量増加原因だったtable_definition_cacheの調査のため、各パラメータ値でapachebenchを実施しmysqldのメモリ使用量とapachebenchのRequests per secondをまとめてみました。

table_open_cache	table_definition_cache	performance_schema	mysqld 消費メモリ	ab -n 1000 -c 15 (rps)
1024	400	0	30	18.2
1024	656	0	32	18.26
1024	801	0	31	18.38
1024	912	0	32	18.44
1024	1024	0	33	18.4
1024	400	1	61	17.99
1024	656	1	94	17.83
1024	801	1	390	18.2
1024	Auto(912)	1	392	18.17

結論から言うと、「performance_schemaが有効かつ table_definition_cacheが801以上の場合にメモリを大量に使用する。」ということがわかりました。
またperformance_schemaが有効だとmysqlのパフォーマンスが若干(1.5%程度)低下し、table_definition_cacheはデフォルト値（400+table_open_cache/2)が最も良い、ということも見えてきます。

performance_schemaは必要？

performance_schemaは開発者や商用DBサーバのチューニングには必要ですが、一方でWordPressのような完成品でMySQLを利用するだけなら不要だと思います。ということで、本サイトではperformance_schema = 0（オフ）、table_definition_cache = 912の設定をつかうことにします。

MySQL5.6へアップデートした効果は

トップページのhttpレスポンスで効果を確認します。
7日の窪みの前がMySQL5.1(InnoDB plugin)、後がMySQL5.6にアップデート後のもの。比較するとMySQL5.6はブレ幅が小さくなっているので平均としては速くなっています。実はapachebenchのRequests per secondは5.1から5.6にアップデートしたことで5%ほど低下しているので、平均も遅くなると思っていたのですが意外な結果になりました。