概要
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ログファイルの大きさは以下の制約を受けます。
- 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/IPとUnixソケットによる接続だけに適用されます。
- binlog_cache_size
- バイナリログキャッシュは未コミットのトランザクション情報（単一トランザクションの中で実行されたSQLステートメント）をキャッシュするメモリ上の領域です。値はキャッシュに保持するSQL文の最大数を意味します。InnoDBなどトランザクションに対応したストレージエンジンを使用し、かつバイナリログを有効にしている場合にのみ割り当てられ、バイナリログファイルへの出力はトランザクションがコミットされるまでバイナリログキャッシュに保留されます。トランザクション情報がbinlog_cache_sizeを超えると、スレッドはトランザクション情報を保存するためにテンポラリテーブルを作成します。大きなトランザクションを実行する場合、このパラメータを大きくするとテンポラリテーブルの作成を回避できるため、パフォーマンスが向上します。
