MySQLのメモリ設定を追求してみよう
MySQLのメモリの話を考えていたら何が何だか分からなくなってきたので、
my.cnfでの設定に絡めてまとめてみようと思う。
そもそも、MySQLサーバにおいてMySQLのプロセスがトータルで使用するメモリは、
どれくらいに見積もっておけばいいだろうか。
参考書やネット上では以下のような計算式が紹介されている。
max_connections x [スレッド領域用メモリ合計値]
に、以下をプラス。
[グローバル領域用メモリ合計値]
DB専用サーバの場合だとこの値をマシン搭載メモリの8〜9割くらいにする、と想定するのが
ひとつの指針となるようだ。
しかし32bit版Linux OSの場合は2〜3GBまでの制限があるため、搭載メモリがそれ以上あったとしても、
この制限以内に収めなければいけない（64bit版は制限なし。とはいえ、スワップ発生には注意）。
他のミドルウェアが混在する場合は、別途調整となる。
グローバル領域とは、MySQLインスタンス全体で共有するメモリ領域のこと。
スレッド領域はコネクションごとに確保される領域となる。チューニングの際は、
この違いに注意しておく。スレッド領域用にあまり多くのメモリを割り当てると、
コネクションが増大した際にメモリ不足になったりする、らしい。
MySQLのメモリ構成を図にすると以下のようになる。
以下にグローバルとスレッド、それぞれのパラメータを挙げる。
以前の記事と重複するが、詳細も改めて書いておく。
グローバル領域用パラメータ
(1) innodb_buffer_pool_size
(2) innodb_log_buffer_size
(3) innodb_additional_mem_pool_size
(4) key_buffer_size
(5) query_cache_size
(6) table_cashe_size
(7) thread_cache_size
(1) innodb_buffer_pool_size
InnoDBをメインで使うなら、一番重要なパラメータ。
InnoDBに対する各種操作の他、レコードデータなどのキャッシュ領域として利用され、
非同期I/Oスレッドやサーバスレッドがアクセスする。InnoDBバッファプール上
データへのアクセスにはディスクI/Oが発生しないが、バッファプール内に収まり
きらなくなったデータはディスク上に退避されることになる。
(2) innodb_log_buffer_size
InnoDBログパッファはInnoDBテーブルに対する更新ログ、いわゆるトランザクション
ログを管理し、InnoDB専用スレッドやサーバスレッドがアクセスする領域。
Oracleで言うとREDOログに相当する。トランザクション終了時（コミット時やロールバック時）、
バッファがいっぱいになった時、一定時間ごとにディスクに書き込まれる。
実行途中のトランザクションの多くはこのバッファで管理される。
１つのトランザクション内で多くのデータを更新するのであればサイズを大きくした方がいいが、
それほど気にしなくてもよさそうではある。1〜8Mの範囲が推奨されている。
(3) innodb_additional_mem_pool_size
InnoDBテーブルの定義情報など、データディクショナリ情報を格納する。
この領域が足りなくなったらエラーログに警告を出すとともに、OSのメモリ領域
から追加で割り当てられる。それほど気にする必要はないが、InnoDBテーブルの
数を多くする場合はこの値も多少増やした方がいい、かも。
(4) key_buffer_size
MyISAMテーブルに対して索引検索をする際にそのインデックス情報を格納する領域。
MyISAMテーブルを多用する場合はこの値もある程度増やす。
(5) query_cache_size
MySQLの性能向上を図る上でかなり重要なメモリ領域で、SELECT文の実行結果をメモリ内にキャッシュする。100〜200くらいが推奨されているよう。
(6) table_cashe_size
MySQLではひとつのテーブルにつきひとつのファイルが割り当てられるが、table_cashe_sizeは
それらのテーブルのキャッシュに使用され、同時実効性をあげるための重要なパラメータとなる。
MySQLサーバーは一度開いたファイルのポインタをtable_cacheに保存し、次回からの
アクセスを高速化するのである。目安は同時接続数 x テーブル数。1024〜2048が一般的。
(7) thread_cache_size
MySQLは接続終了後のサーバスレッドをいきなり解放せず、次の接続時に再利用
できるようにキャッシュしておける。このため、切断後の再接続時のオーバーヘッド
をより軽減することができるのだ。デフォルトは0だが数百程度でもいいようだ。
細かいチューニングは実際の稼働状況を把握しないと何とも言えないが、とりあえず
max_connectionsと同じ値にしておくとか、、、
参考：[ThinkIT] 第3回：max_connectionsとthread_cacheのチューニングを行う
（番外）innodb_log_file_size
メモリ関連ではないのだが、重要なので。デフォルトの値では小さすぎるようだ。こちらからの抜粋なのだが、「innodb_log_fileがいっぱいになると、メモリ上のinnodb_buffer_poolの中の更新された部分のデータを、ディスク上のInnoDBのデータファイルに書き出すしくみ」になっているそうだ。
なので、innodb_buffer_pool_sizeの値を大きくしたらinnodb_log_file_sizeの値も大きめに調整しないと、パフォーマンス
向上が望めない。
スレッド領域用パラメータ
(1) binlog_cache_size
(2) sort_buffer_size
(3) join_buffer_size
(4) read_buffer_size
(5) read_rnd_buffer_size
(6) max_allowed_packet
(7) thread_stack_size
(1) binlog_cache_size
未コミットのトランザクション情報をキャッシュしておく。InnoDBなどトランザクション対応のストレージエンジンを使用し、かつバイナリログを有効にしている場合にのみ使用される。このサイズを大きくすると、大規模なトランザクション処理を実行する場合にパフォーマンス低下を防ぐ効果があるようだ。
(2) sort_buffer_size
ソートの際に利用される領域。ORDER BYやGROUP BYを多用するのであればこの値も増やした方がいい。
(3) join_buffer_size
インデックスを用いない全文検索を伴うテーブル結合（フルジョイン）において、レコードデータのキャッシュに使われる領域。一回のフルジョインにつき１つのジョインバッファが確保され、SQL文の実行終了とともに解放される。インデックスを用いないテーブル結合はパフォーマンスの観点からして避けるべきなので、この値は大きくする必要はないようだ。
(4) read_buffer_size
インデックスを使用する／しないにかかわらず、全文検索の時に使用される領域。テーブル全体をスキャンするSQL文を何度も実行する場合はこの値を調整した方がよい。
※「インデックスを使用しない場合に使われる」と書かれている場合もあり、どちらだか分からない。後で調べようと思う。
(5) read_rnd_buffer_size
ソート後にレコードを読むときに使われる領域で、ディスクI/Oが減るためORDER BYの性能向上が期待できる。 1〜2Mくらいが妥当？
(6) max_allowed_packet
ユーザプロセスとサーバスレッドの間で、SQL文による問合せやその結果をやりとりする際に使用される領域の最大サイズを指定する。これより大きなサイズのデータ通信はできない。デフォルトは1Mだが、大きなサイズのデータを扱う場合はこの値も大きくした方がいい。
(7) thread_stack_size
スタック領域と呼ばれる、スレッド固有の領域。基本的にはデフォルト値のままでよいとされる。
長かった。。。
追求すればするほどドツボにはまるMySQLのメモリ仕様、恐るべし、である。
主な出典
「現場で使えるMySQL」松信嘉範著
参考URL
5分でできる、MySQLのメモリ関係のチューニング！
［MySQLウォッチ］第14回 サーバー設定を見直してMySQLの性能を引き出す
MySQLちょっと上級チューニング
限界までMySQLを使い尽くす!!
InnoDBのパフォーマンスチューニング
[MySQL] パフォーマンス関連メモ