DBチューニングできますか？(4)

MySQL

実はもうちょっとだけ続くシリーズ第四弾です。

概要

MySQLサーバーが目的となるデータを探す場合はインデックスを活用すると効率が良い、という事を前回まとめてみました。
さらにインデックスはその構造上よく使われるルートにより近いノードからメモリ上キャッシュされやすい構造になっていることをまとめました。
という事は、よりたくさんキャッシュできるようにするとさらに効率が良くできます。

そこで今回はよりたくさんキャッシュする方法をまとめます。

my.cnfをチューニングできますか？

MySQLサーバーを起動する際にメモリをどの処理にどれぐらい割り振るか、という選択ができます。
このときにインデックスがメモリにキャッシュされる設定をしておくとよいわけです。

my.cnfには他にも様々な設定ができるのですが、今回はインデックスファイルをたくさんキャッシュするための重要なパラメータについてまとめます。

innodb_buffer_pool_size

innodbでMySQLサーバーを立てる場合はこのパラメータを調整すると思えばいいと思います。DB専用機として使うなら最大80%まで割り振れるそうです。

innodb_buffer_pool_size=1GB # 参考値です

こんな感じでしょうか

ダイレクトI/O

前回、ファイルシステムは複数のセクタをまとめて一つのデータブロックとして扱う、という説明をしたと思いますが、ファイルシステムはこのデータブロックをまとめて読み書きし、この単位でキャッシュしたりします。
ページキャッシュのほうがわかりやすいかもしれません。

ただし、これはMySQL側のキャッシュ機構ではなく、OS側のキャッシュ機構です。innodb_buffer_poolでキャッシュしたほうがMySQLに特化したメモリ機構なので効率がよいです。
というわけでOS側がこれをキャッシュしないようにするための設定を行います。

innodb_flush_method=O_DIRECT

こうしておくとよいでしょう。

WEB+DB構成の場合はどうする？

前述の構成は一台のサーバーをMySQL専用機として扱う場合の話ですが、小規模サイトの場合はWEBサーバーとDBサーバーを1台のサーバーに同居させている場合があります。
以下の記事を参考にして下さい。

http://mysql-casual.org/2010/12/mysql-casual-advent-calendar-2010-12-09.html

この場合はMySQLに特化したメモリキャッシュ機構であるinnodb_buffer_poolにメモリまわすより、汎用的なシステムファイルキャッシュにお任せしたほうがよいでしょう。

まとめ

「my.cnfを調整できますか？」「パラメータチューニングはできますか？」という質問ではいろいろなケースが想定されるのでそもそもどのように回答すれば良いのか分かりません。
質問者も「my.cnfいじるとDBが速くなるんじゃないの？」ぐらいの認識だと思います。

「メモリが不足するとスワップが発生するなど速度低下が起こり得えます。そうした場合、ハードウェアの限界を疑う前にパラメータを調査して改善できるかどうか善処します。」

のような感じでとりあえず面接官は納得してくれる、かも