innodb_buffer_pool_sizeのチューニング：どれくらい割り当てる？
2015-08-06 13:04
結論としては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の利用状況を教えてくれるコマンドがあるとのことです。
|
1 2 3 4 5 6 7 8 9 10 |
mysql> SHOW ENGINE INNODB STATUS\G *************************** 1. row *************************** Type: InnoDB Name: Status: ===================================== 150806 10:48:54 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 2 seconds |
これは公式ドキュメントによると、“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割り当てています)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
mysql> show engine innodb status\G BUFFER POOL AND MEMORY ---------------------- Total memory allocated 2195718144; in additional pool allocated 0 Dictionary memory allocated 1350348 Buffer pool size 131072 Free buffers 0 Database pages 128747 Old database pages 47505 Modified db pages 426 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 132570918902, not young 0 170.53 youngs/s, 0.00 non-youngs/s Pages read 89424375644, created 6193073, written 1057402716 157.21 reads/s, 0.00 creates/s, 2.75 writes/s Buffer pool hit rate 986 / 1000, young-making rate 15 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 109.75/s LRU len: 128747, unzip_LRU len: 0 I/O sum[3397]:cur[141], unzip sum[0]:cur[0] |
これを見ると、Free buffersが0なので、確保したバッファープールを使い切っているように見えます。さらにnot youngが0でpages made youngが132570918902になっています。つまり、常にpool内のリストは更新され、先頭に移動され続けているということになります。
|
1 |
Buffer pool hit rate 986 / 1000, young-making rate 15 / 1000 not 0 / 1000 |
上記の通り98％はヒットしているといえると思います。ヒット率はまあまあですが、buffer poolは使いきっている状態です。
適切なinnodb_buffer_pool_sizeは？（チューニング方法）
公式ドキュメントではあまり参考にならなかったので別のアプローチでチューニング方法を探してみました。
information_schemaからクエリで抽出する
RolandoMySQLDBAさんによると、innodbテーブルの全てのデータサイズとインデックスサイズを合計したものが理想値であるとのことです。具体的には下記のクエリで取得できます。
|
1 2 3 |
SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A; |
また、サービスの継続とともにデータベースは大きくなっていくので、その成長率を加味する必要もあります。また、innodbはさらに管理用として全体の10％のメモリを確保するようなので、この両方の係数を掛け合わせたものが理想値と言えると思います。(参照)
他にもスレッド単位で割り当てるバッファやmax_connectionsなどによってMySQL全体で必要とするメモリを算定していけますね。
MySQL Tunerでinnodb_buffer_pool_sizeを調べる
またMySQLチューナーでも似たような結果になります。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
$ wget https://github.com/major/MySQLTuner-perl/zipball/master $ unzip master $ cd master $ ./mysqltuner.pl #mysqlのユーザー名とパスワードを聞かれるので入力し実行します -------- Storage Engine Statistics ------------------------------------------- [--] Data in InnoDB tables: 8G (Tables: 227) -------- InnoDB Metrics ----------------------------------------------------- [--] InnoDB is enabled. [--] InnoDB BufferPool Size :2.0G [!!] InnoDB buffer pool / data size: 2.0G/8.2G [OK] InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- Variables to adjust: innodb_buffer_pool_size (>= 8G) |
つまり、８GB以上のinnodb_buffer_pool_sizeを確保せよということです。
上記の通り、MySQLTunerの場合は、innodbのテーブルのインデックスを除いたデータのみをbuffer poolにあげるべきだと判断しているようです。発想としてはRolandさんと同じですが、こちらの場合はindexはディスクにあろうと、そもそも最適化されているのでbuffer poolにあげるまでもないという発想なのかなと思いました。
まとめると、innodb_buffer_pool_sizeに関して下記の二つのシナリオがあります。
- データとインデックスを含めたサイズを割り当てる
- データのサイズを割り当てる
基本的には１が理想だとは思うのですが、実際はそこまで使用しないデータもあるでしょうし、バッファープールになくても、十分速いインデックスもあると思います。従って、１が理想値ではあるけれども、現実的な運用では１と２の間になるのかなと思いました。基本データは全部バッファープールにのっけますが、更新頻度の高いテーブルのインデックスや、検索に時間のかかるインデックスなどの分はバッファープールに入れても良いかと思います。
