MySQLのメモリ関係のシステム変数

概要

MySQLのメモリ関係のシステム変数について、まとめてみました。



構成

MySQL Ver 14.14 Distrib 5.5.14, for Linux (x86_64) using EditLine wrapper



メモリサイズの見積もり

はじめに、MySQLのプロセスが必要とするメモリサイズの見積もり方法を確認します。

必要なメモリサイズ = グローバルバッファのサイズ +(スレッドバッファのサイズ × 最大同時接続数)

グローバルバッファのサイズは以下の方法で計算します。
max_heap_table_sizeは必ずしも割り当てられる訳ではありませんが、安全側に倒すため、計算に含めています。

グローバルバッファのサイズ = key_buffer_size
                           + innodb_buffer_pool_size
                           + innodb_additional_mem_pool_size
                           + innodb_log_buffer_size
                           + max_heap_table_size
                           + query_cache_size

スレッドバッファのサイズは以下の方法で計算します。
myisam_sort_buffer_sizeは通常のクエリでは使われないので、計算に含めていません。また、パケットメッセージバッファは必ずしも最大まで拡張される訳ではありませんが、安全側に倒すため、max_allowed_packetで計算しています。あまり大きなパケットを送受信しないのであれば、代わりにnet_buffer_lengthを使うと(やや安全性が犠牲になりますが)より実際に近い値になるかもしれません。

スレッドバッファのサイズ = sort_buffer_size
                         + read_rnd_buffer_size
                         + join_buffer_size
                         + read_buffer_size
                         + max_allowed_packet
                         + thread_stack



メモリサイズの見積もりに使用するシステム変数

一覧

デフォルト値はmy.cnfがない(オプションを渡していない)状態でmysqld --verbose --helpに表示された値をもとにしています。

変数 区分 デフォルト値 目安
key_buffer_size グローバル 8MB 空きメモリの30%
innodb_buffer_pool_size グローバル 8MB 空きメモリの70〜80%
innodb_additional_mem_pool_size グローバル 1MB 16MB
innodb_log_buffer_size グローバル 1MB 普通は8MB、多くても64MB
max_heap_table_size グローバル 16MB -
tmp_table_size グローバル 16MB -
query_cache_size グローバル 0MB -
max_connections グローバル 20個 -
myisam_sort_buffer_size スレッド 8MB 1MB
sort_buffer_size スレッド 2MB OLTPでは256KB〜1MB(漢のコンピュータ道)
2MB or 4MB(DSAS開発者の部屋)
read_rnd_buffer_size スレッド 256KB OLTPでは256K〜1M(漢のコンピュータ道)
512KB〜2MB(DSAS開発者の部屋)
join_buffer_size スレッド 128KB 256KB
read_buffer_size スレッド 128KB OLTPでは128K〜512K(漢のコンピュータ道)
1MB(DSAS開発者の部屋)
net-buffer-length スレッド 16KB -
max_allowed_packet スレッド 1MB 16MB
thread_stack スレッド 256KB デフォルトのまま


解説
key_buffer_size
キーバッファはMyISAMのインデックスをキャッシュするメモリ上の領域です。このパラメータを大きくするとディスクI/Oが減少するため、パフォーマンスが向上します。キーバッファには空きメモリの30%と、比較的小さな数字を割り当てます。これはMyISAMがデータのキャッシュにはファイルシステムのキャッシュを使用し、キーバッファにはインデックスしかキャッシュしないためです。
innodb_buffer_pool_size
InnoDBバッファプールはInnoDBのデータとインデックスをキャッシュするメモリ上の領域です。このパラメータを大きくするとディスクI/Oが減少するため、パフォーマンスが向上します。InnoDBバッファプールを最大限に使う場合、ダブルキャッシュを回避するためにinnodb_flush_methodをO_DIRECTにします。InnoDBでは最も重要なパラメータです。
innodb_additional_mem_pool_size
InnoDBアディショナルメモリプールはInnoDBデータディクショナリ情報(InnoDBテーブルの定義情報など)をキャッシュするメモリ上の領域です。この領域が不足するとOSのメモリ領域から追加で割り当てられるとともに、エラーログに警告が出力されます。最初は大きな値を割り当てず、エラーログに警告が出力されたら増やします。テーブル数が多い場合は少し増やしておいても良いでしょう。
innodb_log_buffer_size
InnoDBログバッファはInnoDBの更新ログ(トランザクションログ)を記録するメモリ上の領域です。単一トランザクションのサイズが大きい場合、このパラメータを大きくするとディスクI/Oが減少するため、パフォーマンスが向上することがあります。しかし一般的には、コミット(ロールバック)したとき、あるいは一定時間毎にディスクにフラッシュされるため、8MB以上に大きくしても効果は少ないと言われています。
max_heap_table_size
MEMORYテーブル(旧HEAPテーブル)はMEMORYストレージエンジンによってメモリ上に作成されるテーブルです。MEMORYテーブルはmax_heap_table_sizeを超えるテーブルを作成できないようになっています(ただしこの制約はCREATE TABLE文、ALTER TABLE文を実行したときに適用されるため、max_heap_table_sizeを変更しても既存のMEMORYテーブルには影響ありません)。とくに理由がなければtmp_table_sizeと同じ値にしておきます。
tmp_table_size
テンポラリテーブルはその接続でのみ現れ、接続が終了するとドロップされる一時的なテーブルです。テンポラリテーブルはそのサイズがtmp_table_size以下であればMEMORYテーブルとしてメモリ上に作成され、tmp_table_sizeを超えるとISAMテーブルとしてディスクに書き出されます。副問い合わせを含むような複雑なクエリを実行している場合、このパラメータを大きくするとテンポラリテーブルがMEMORYテーブルとして作成されるため、パフォーマンスが向上することがあります。なお、MEMORYテーブルの大きさはmax_heap_table_sizeによっても制約されるため、tmp_table_sizeを大きくする場合は同時にmax_heap_table_sizeも大きくします。
query_cache_size
クエリキャッシュはSELECTクエリのテキストと結果をキャッシュするメモリ上の領域です。テーブルの更新が少なく、かつ同じクエリを繰り返す場合、このパラメータを大きくするとクエリの解析と実行をスキップできるため、パフォーマンスが向上します。しかし、テーブルの更新が多く、あるいは同じクエリを繰り返さない場合、オーバーヘッドによりパフォーマンスが低下(MySQLのリファレンスによると、最悪のシナリオでのオーバーヘッドは13%程度)することもあります。
max_connections
最大同時接続数は一般ユーザーが同時に接続できる最大数です。実際、同時に確立できるコネクション数はmax_connections + 1であり、+1は管理ユーザーのために予約されています。
myisam_sort_buffer_size
MyISAMソートバッファはREPAIR TABLE文がMyISAMテーブルのインデックスをソートするとき、またCREATE INDEX文、ALTER TABLE文がMyISAMテーブルのインデックスを作成するときに使うメモリ上の領域です。DMLでは使われないので、大きな値を割り当てる必要はありません。
sort_buffer_size
ソートバッファはファイルソートを実行するときに使うメモリ上の領域です。sort_buffer_sizeを超えた分はテンポラリファイルに書き出され、ソートが実行されます。大きなデータをソートする場合、このパラメータを大きくするとメモリ上でソートが実行されるため、パフォーマンスが向上します。OLTP系では概ねデフォルト(2MB)のままで問題ありません。DWH系で大きなデータをソートする場合、セッションごとに動的に調整すると良いでしょう。
read_rnd_buffer_size
このバッファはインデックスによるソート(ファイルソートではない)を実行するとき、ソートしたレコードの読み出しに使うメモリ上の領域です。大きなデータをフェッチする場合、このパラメータを大きくするとディスクI/Oが減少するため、パフォーマンスが向上します。OLTP系ではデフォルト(256KB)のままで問題ありません。DWH系で大きなデータをフェッチする場合、セッションごとに動的に調整すると良いでしょう。
join_buffer_size
JOINバッファはインデックスを使わない結合に使うメモリ上の領域です。これによりフルテーブルスキャンが実行されます。このパラメータを大きくするとメモリ上で結合が実行されるため、パフォーマンスが向上します。しかし、そもそもインデックスを使わないテーブル結合は避けるべきであり、このパラメータを操作するよりも先にインデックスの利用を検討するべきです。
read_buffer_size
リードバッファはインデックスを使わないテーブルスキャンに使うメモリ上の領域です。このパラメータを大きくするとメモリ上でテーブルスキャンが実行されるため、パフォーマンスが向上します。しかし、これもパフォーマンスを考えるならば、このパラメータを操作するよりも先にインデックスの利用を検討するべきです。OLTP系ではあまり大きな値は必要ありません。
net_buffer_length
max_allowed_packetを参照
max_allowed_packet
パケットメッセージバッファは送受信するパケットを格納するメモリ上の領域です。net_buffer_lengthで初期化され、必要に応じてmax_allowed_packetまで拡張されます。また、クライアントが実行できるSQL文の最大長はmax_allowed_packetによって制限されます。一般的にはもっとも大きいBLOBまたはTEXTと同じサイズにします。
thread_stack
スレッドスタックは各スレッドがコールスタックとして使うメモリ上の領域です。一般的にはデフォルトのままで問題ありません。



それ以外のシステム変数

一覧
変数 区分 デフォルト値 目安
innodb_log_file_size グローバル 5MB -
innodb_log_files_in_group グローバル 2個 -
thread_cache_size グローバル 0個 -
table_open_cache グローバル 400個 -
wait_timeout グローバル 8時間 -
binlog_cache_size スレッド 32768個 -


解説
innodb_log_file_size
InnoDBログファイルはWAL(Write Ahead Log)と呼ばれるもので、コミットされたトランザクションをテーブルスペースに反映する前に、いったんすべて書き出しておくためのファイルです。トランザクションログをInnoDBログファイルに書き出し、後からまとめてテーブルスペースへと反映することで、高コストなテーブルスペースへの反映を減らし、書き込み性能の向上を図る目的があります(テーブルスペースへの反映は、複数のインデックス情報の更新複数のテーブルの更新それら離散して格納された情報へのランダムアクセスなどを伴う、とてもコストのかかる処理です。一方、InnoDBログファイルへの書き込みはトランザクションログをシーケンシャルに書き込むため、ずっと少ないコストで実現できます)。このパラメータを大きくするとテーブルスペースへの反映が少なくなるため、パフォーマンスが向上します。一方、InnoDBログファイルの大きさに比例してクラッシュリカバリにかかる時間が長くなる点には注意が必要です。なお、InnoDBログファイルの大きさは以下の制約を受けます。
    • 1MB ≦ innodb_log_file_size ≦ 4GB(32bit OS)
    • innodb_log_file_size × innodb_log_files_in_group < innodb_buffer_pool_size
innodb_log_files_in_group
この値はInnoDBログファイルの数を意味します。
thread_cache_size
スレッドキャッシュは接続終了後のサーバースレッドを解放せず、次の接続時に再利用できるようにキャッシュするメモリ上の領域です。値はキャッシュに保持するスレッドの最大数を意味します。最大同時接続数が多い場合、このパラメータを大きくすると再接続時のオーバーヘッドが軽減されるため、パフォーマンスが向上します。
table_open_cache
テーブルキャッシュは接続終了後もテーブルをメモリ上に維持しておき、次の接続時に再利用できるようにキャッシュするメモリ上の領域です。値はキャッシュに保持するテーブルの最大数を意味します。5.1以前はtable_cacheという名前でした。最大同時接続数が多い場合、このパラメータを大きくすると再接続時のオーバーヘッドが軽減されるため、パフォーマンスが向上します。注意すべきは、MySQLは同じテーブルに対して同時にアクセスするスレッドがあった場合、それぞれが同じテーブルを重複して開く、ということです。こうすることによってマルチスレッド環境でのパフォーマンスを向上させています。そのため、MySQLが開くテーブルの最大数は最低でもmax_connectionsと同数になります。最低でもというのは、joinを含むクエリでは複数のテーブルが開かれることがあるためで、その場合はmax_connections × n(nはjoinによって結合されるテーブル数)が最大数となります。
wait_timeout
タイムアウト待ち時間は反応のない接続を終了する前に、サーバーが待機する時間です。最後の実行からこの秒数が経過すると、サーバーは接続を終了します。この値はTCP/IPUnixソケットによる接続だけに適用されます。
binlog_cache_size
バイナリログキャッシュは未コミットのトランザクション情報(単一トランザクションの中で実行されたSQLステートメント)をキャッシュするメモリ上の領域です。値はキャッシュに保持するSQL文の最大数を意味します。InnoDBなどトランザクションに対応したストレージエンジンを使用し、かつバイナリログを有効にしている場合にのみ割り当てられ、バイナリログファイルへの出力はトランザクションがコミットされるまでバイナリログキャッシュに保留されます。トランザクション情報がbinlog_cache_sizeを超えると、スレッドはトランザクション情報を保存するためにテンポラリテーブルを作成します。大きなトランザクションを実行する場合、このパラメータを大きくするとテンポラリテーブルの作成を回避できるため、パフォーマンスが向上します。