> > > innodb_buffer_pool_sizeのチューニング:どれくらい割り当てる?

innodb_buffer_pool_sizeのチューニング:どれくらい割り当てる?

2015-08-06 13:04 - NozawaTakeshi

結論としてはinnodbテーブルの全データ量だそうです。

ここ最近はデータベースサーバのリプレースの案件の担当をしているのですが、サーバのサイジングをしていてふと、「どれくらいメモリあれば足りるんだろう」と疑問に思いました。

よく「サーバの全メモリの50%から70%くらい」とか「80%」くらいとか言われますが、それはどちらかというとスワップしないための限界値を示すものであって、理想値ではないですよね。それでいろいろ調べてみたところ、なかなか日本語で良いまとめがなかったので、まとめてみようと思います。

目次

  • そもそもinnodb_buffer_poolとは?
  • 使用状況を確認する(SHOW ENGINE INNODB STATUSでの計測)
  • 適切なinnodb_buffer_pool_sizeは?(チューニング方法)

そもそもinnodb_buffer_poolとは?

InnoDB maintains a buffer pool for caching data and indexes in memory.

MySQLの公式ドキュメントによると、「データとindexをメモリ上にキャッシュする領域」とのことです。(そりゃそうだ・・・。)

MySQLのinnodb_buffer_pool_sizeは、ディスクイメージをメモリ上にバッファさせる値をきめる設定値で、この設定が、いかにディスクIOを押さえるかに繋がったりするので、コストパフォーマンス向上を考えるうえで重要な設定のひとつだったりします。

こちらの方のブログでは何故innodb_buffer_pool_sizeが大事なのかを簡単にまとめてくれていました。

つまりここで設定した容量分だけ、ディスクに書き込まれているデータを吸い上げてメモリに乗っけてくれるということですね。そのおかげで、データの検索も高速で行えるということです。とても基本的なことかもしれませんが、とても大事なことですね。

The buffer pool even caches data changed by insert and update operations, so that disk writes can be grouped together for better performance.

さらに、 MySQLの公式ドキュメントには、innodb_buffer_pool_sizeを大きく確保することは、データを読み取る時だけでなく、「書き込むとき」ときのパフォーマンスの向上にも繋がると書かれています。恥ずかしながら私は知りませんでした・・・。

また、こんなことも書かれていました。

InnoDB manages the pool as a list, using a variation of the least recently used (LRU) algorithm…

要はbuffer_poolは一連の「リスト(マニュアルでは”page”という概念としても説明されています)」になっていて、時系列に並んでいるそうです。
そして、poolが一杯になると、古くて使わないものから削除していくアルゴリズムになっているようです。

使用状況を確認する(SHOW ENGINE INNODB STATUSでの計測)

さて、ここからが本題です。いかにチューニングをしていくかですが、InnoDBには、InnoDBの利用状況を教えてくれるコマンドがあるとのことです。

これは公式ドキュメントによると、“InnoDB Standard Monitor”と呼ばれるツールだそうです。

なかでもinnodb_buffer_pool_sizeのチューニングに関係する項目としては下記があるようです。

Old database pages
buffer pool内の古いページ数
Pages made young, not young
バッファープールのリストの先頭に移動された古いページの数(=つまり新しいリスト。再利用されるページの数)。と更新されることもなく古いリストに入ったままのページの数(=つまりpool内にあってもほとんど再利用されないページの数)。
youngs/s, non-youngs/s
それぞれのページに対する秒間アクセス数。
young-making rate
ヒット数(率?)(つまりbuffer poolの先頭に移動されたページの割合)

“not”という項目もありますが、すみません。ちょっと意味が分からなかったです・・・。「 バッファープールの先頭に移動させなかったhit数」だそうですが・・・

手元にある、とあるmysqlサーバでモニターツールを実行してみました。(innodb_buffer_pool_sizeは2GB割り当てています)

これを見ると、Free buffersが0なので、確保したバッファープールを使い切っているように見えます。さらにnot youngが0でpages made youngが132570918902になっています。つまり、常にpool内のリストは更新され、先頭に移動され続けているということになります。

上記の通り98%はヒットしているといえると思います。ヒット率はまあまあですが、buffer poolは使いきっている状態です。

適切なinnodb_buffer_pool_sizeは?(チューニング方法)

公式ドキュメントではあまり参考にならなかったので別のアプローチでチューニング方法を探してみました。

information_schemaからクエリで抽出する

RolandoMySQLDBAさんによると、innodbテーブルの全てのデータサイズとインデックスサイズを合計したものが理想値であるとのことです。具体的には下記のクエリで取得できます。

また、サービスの継続とともにデータベースは大きくなっていくので、その成長率を加味する必要もあります。また、innodbはさらに管理用として全体の10%のメモリを確保するようなので、この両方の係数を掛け合わせたものが理想値と言えると思います。(参照)

他にもスレッド単位で割り当てるバッファやmax_connectionsなどによってMySQL全体で必要とするメモリを算定していけますね。

MySQL Tunerでinnodb_buffer_pool_sizeを調べる

またMySQLチューナーでも似たような結果になります。

つまり、8GB以上のinnodb_buffer_pool_sizeを確保せよということです。

上記の通り、MySQLTunerの場合は、innodbのテーブルのインデックスを除いたデータのみをbuffer poolにあげるべきだと判断しているようです。発想としてはRolandさんと同じですが、こちらの場合はindexはディスクにあろうと、そもそも最適化されているのでbuffer poolにあげるまでもないという発想なのかなと思いました。

まとめると、innodb_buffer_pool_sizeに関して下記の二つのシナリオがあります。

  1. データとインデックスを含めたサイズを割り当てる
  2. データのサイズを割り当てる

基本的には1が理想だとは思うのですが、実際はそこまで使用しないデータもあるでしょうし、バッファープールになくても、十分速いインデックスもあると思います。従って、1が理想値ではあるけれども、現実的な運用では1と2の間になるのかなと思いました。基本データは全部バッファープールにのっけますが、更新頻度の高いテーブルのインデックスや、検索に時間のかかるインデックスなどの分はバッファープールに入れても良いかと思います。