5分でできる、MySQLのメモリ関係のチューニング！
MySQLのチューニングにおいて非常に重要となるメモリ(バッファ)関連のパラメータについて、
- チューニングのポイント
- DSASのとあるDBサーバ(実メモリ4GB)の実際の設定値
をまとめてみます。
また、必要メモリの総量の計算や限界値を越えてないかチェックしてくれるスクリプトも紹介します。
是非、参考にしてみてください！
まず最初に注意点を。
バッファには2つのタイプがあります。
- グローバルバッファ
- スレッドバッファ
グローバルバッファはmysqld全体でそのバッファが1つだけ確保されるもので、 これに対し、 スレッドバッファはスレッド(コネクション)ごとに確保されるものです。
チューニングの際にはグローバル/スレッドの違いを意識するようにしましょう。 なぜなら、スレッドバッファに多くのメモリを割り当てると、コネクションが増えたとたんにアッという間にメモリ不足になってしまうからです。
innodb_buffer_pool_size
- 用途
- InnoDBのデータやインデックスをキャッシュするためのメモリ上の領域です。
- バッファ種別
- グローバル
- DSAS値
- 512MB
グローバルバッファなので、どかんと割り当ててください。
MySQL ABのドキュメントには、搭載メモリの80%とも書かれています。
innodb_additional_mem_pool_size
- 用途
- InnoDBの内部データなどを保持するための領域です。
- バッファ種別
- グローバル
- DSAS値
- 20MB
それほど大量に割り当てる必要はありません。足りなくなったらエラーログにその旨、警告が出ますので、そしたら増やせばいいのではないかと思います。
innodb_log_buffer_size
- 用途
- InnoDBの更新ログを記録するメモリ上の領域です。
- バッファ種別
- グローバル
- DSAS値
- 16MB
たいていは8MB、多くても64MBで十分で、あんまり多くする必要はありません。 なぜなら、バッファはトランザクションがCOMMITされるごと、または毎秒ディスクにフラッシュされるので、ほかのパラメータを厚くしたほうが得策です。
innodb_log_file_size
- 用途
- InnoDBの更新ログを記録するディスク上のファイルです。
メモリではないのですがチューニングの上で重要なので解説します。 - バッファ種別
- ---
- DSAS値
- 128MB
大きくした方がパフォーマンスが向上します。
なぜなら、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のクラッシュリカバリの時間が長くかかるようになるという点です。
sort_buffer_size
- 用途
- ORDER BYやGROUP BYのときに使われるメモリ上の領域です。
- バッファ種別
- スレッド
- DSAS値
- 2MB
スレッドバッファなので、むやみに大きくするとメモリが足りなくなるので注意です。
DSASでは2MBか4MBにしています。
read_rnd_buffer_size
- 用途
- ソート後にレコードを読むときに使われるメモリ上の領域です。
ディスクI/Oが減るのでORDER BYの性能向上が期待できます。 - バッファ種別
- スレッド
- DSAS値
- 1MB
これもスレッドバッファなので、割り当て過ぎには注意が必要です。
DSASでは512KB〜2MBにしています。
join_buffer_size
- 用途
- インデックスを用いないテーブル結合のときに使われるメモリ上の領域です。
- バッファ種別
- スレッド
- DSAS値
- 256KB
スレッドバッファです。
そもそもインデックスが使われないようなテーブル結合はパフォーマンス向上の観点からすると避けるべきなので、このパラメータはそれほど大きくする必要はないでしょう。
read_buffer_size
- 用途
- インデックスを用いないテーブルスキャンのときに使われるメモリ上の領域です。
- バッファ種別
- スレッド
- DSAS値
- 1MB
これも、パフォーマンスを考えるならば、インデックスを使うようなクエリを発行するべきなので、それほど多くする必要はないでしょう。
key_buffer_size
- 用途
- MyISAMのインデックスをキャッシュするメモリ上の領域です。
- バッファ種別
- グローバル
- DSAS値
- 256MB
グローバルバッファです。
多く割り当てた方がパフォーマンスが向上します。グローバルバッファなのでどかんと割り当てられます。
もし、MyISAMを(あまり)使ってないのならば、小さくしてほかのパラメータに回すのもアリです。
ちなみに、キーキャッシュのヒット率は、SHOW STATUSの値を使って、以下の式で算出できます。
キーキャッシュのヒット率 = 100 - ( key_reads / key_read_requests × 100 )
myisam_sort_buffer_size
- 用途
- MyISAMで以下の時のインデックスのソートに使われるメモリ上の領域です。
- REPAIR TABLE
- CREATE INDEX
- ALTER INDEX
- バッファ種別
- スレッド
- DSAS値
- 1MB
通常のクエリ(DML)では使われないようなので、それほど多くする必要はないでしょう。
mymemcheck - メモリ関連のチェックツール
最後にmymemcheckという自家製のツールを紹介します。
ダウンロードはこちらから。
- mymemcheck v1.01 (gzip)
mymemcheckは、my.cnfもしくはSHOW VARIABLESの結果を元に、以下の3つのチェックを行います。
- 最低限必要な物理メモリの大きさ
- IA-32のLinuxでのヒープサイズの制限
- innodb_log_file_sizeの最大サイズ
実行結果例はこのようになります。
$ ./mymemcheck my.cnf
[ minimal memory ]
ref
* 『High Performance MySQL』, Solving Memory Bottlenecks, p125
global_buffers
key_buffer_size 268435456 256.000 [M]
innodb_buffer_pool_size 536870912 512.000 [M]
innodb_log_buffer_size 16777216 16.000 [M]
innodb_additional_mem_pool_size 20971520 20.000 [M]
net_buffer_length 16384 16.000 [K]
thread_buffers
sort_buffer_size 2097152 2.000 [M]
myisam_sort_buffer_size 1048576 1024.000 [K]
read_buffer_size 1048576 1024.000 [K]
join_buffer_size 262144 256.000 [K]
read_rnd_buffer_size 1048576 1024.000 [K]
max_connections 250
min_memory_needed = global_buffers + (thread_buffers * max_connections)
= 843071488 + 5505024 * 250
= 2219327488 (2.067 [G])
[ 32bit Linux x86 limitation ]
ref
* http://dev.mysql.com/doc/mysql/en/innodb-configuration.html
* need to include read_rnd_buffer.
* no need myisam_sort_buffer because allocate when repair, check alter.
2G > process heap
process heap = innodb_buffer_pool + key_buffer
+ max_connections * (sort_buffer + read_buffer + read_rnd_buffer)
+ max_connections * stack_size
= 536870912 + 268435456
+ 250 * (2097152 + 1048576 + 1048576)
+ 250 * 262144
= 1919418368 (1.788 [G])
2G > 1.788 [G] ... safe
[ maximum size of innodb_log_file_size ]
ref
* http://dev.mysql.com/doc/mysql/en/innodb-start.html
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
= 536870912 * 1/2
= 268435456 (256.000 [M])
innodb_log_file_size < MAX_innodb_log_file_size
134217728 < 268435456
128.000 [M] < 256.000 [M] ... safe
いずれもMySQL ABのドキュメントなどに書かれている制限事項、そのままです。
パラメータ同士が関係しあっていることが多々あるので、パラメータを変更する際は、このようなチェックツールで確認するのがいいのではないかと思います。
