innodbのこれだけはやっておこうチューニング

MySQL

概要†

innodbのこれだけはやっておこうチューニングメモです。

内容†

参考サイト：http://dsas.blog.klab.org/archives/50860867.html

デフォルト値†

innodb_additional_mem_pool_size         | 1048576 1M
innodb_buffer_pool_size                 | 8388608 8M
innodb_log_buffer_size                  | 1048576 1M
innodb_log_file_size                    | 5242880 5M

innodb_buffer_pool_size†

InnoDBのデータやインデックスをキャッシュするためのメモリ上の領域です。
グローバルバッファなので、どかんと割り当ててください。
MySQL ABのドキュメントには、搭載メモリの80%とも書かれています。

innodb_additional_mem_pool_size†

InnoDBの内部データなどを保持するための領域です。
それほど大量に割り当てる必要はありません。足りなくなったらエラーログにその旨、警告が出ますので、そしたら増やせばいいのではないかと思います。

innodb_log_buffer_size†

InnoDBの更新ログを記録するメモリ上の領域です。
たいていは8MB、多くても64MBで十分で、あんまり多くする必要はありません。
なぜなら、バッファはトランザクションがCOMMITされるごと、または毎秒ディスクにフラッシュされるので、ほかのパラメータを厚くしたほうが得策です。

innodb_log_file_size†

InnoDBの更新ログを記録するディスク上のファイルです。
メモリではないのですがチューニングの上で重要なので解説します。
大きくした方がパフォーマンスが向上します。
なぜなら、innodb_log_fileがいっぱいになると、メモリ上のinnodb_buffer_poolの中の更新された部分のデータを、ディスク上のInnoDBのデータファイルに書き出すしくみになっているからです。
ですので、innodb_buffer_pool_sizeを大きくしたら、innodb_log_file_sizeもあわせて調整しないとパフォーマンスが向上しません。

innodb_log_file_sizeの値は、1MB以上で、32bitマシンの場合は4GB以下にしなければならないとMySQL ABのドキュメントには書いてあります。
また、もうひとつ上限があります。innodb_log_fileはinnodb_log_files_in_groupの数(デフォルトは2)だけつくられるのですが、innodb_log_file_size×innodb_log_files_in_groupがinnodb_buffer_pool_sizeを越えてもいけません。
まとめるとこのようになります。

1MB < innodb_log_file_size < MAX_innodb_log_file_size < 4GB

MAX_innodb_log_file_size = innodb_buffer_pool_size * 1/innodb_log_files_in_group†

もうひとつ注意しなければならないのは、innodb_log_file_sizeを大きくすればするほど、InnoDBのクラッシュリカバリの時間が長くかかるようになるという点です。