MySQLサーバが消費するメモリ

出典: MySQL Practice Wiki

非常によく聞かれる質問として、MySQLサーバーが消費するメモリ量はどのぐらいかというものがある。サーバーのサイジングにおいて、メモリ量の見積もりはもっとも核となる部分の一つである。が、不確定要素が多く一筋縄ではいかない。また、メモリを大きく確保し過ぎるとMySQLサーバーのクラッシュの原因になってしまう。このページではMySQLサーバーが消費するメモリ量について概要を紹介するので、サーバーサイジングの際に参考にしてほしい。

MySQLサーバーは大きく分けて以下の用途でメモリを必要とする。

  • サーバー全体で割り当てられるバッファ
  • セッションごとに割り当てられるバッファ
  • その他

それぞれで必要なメモリを合計すれば、サーバーマシンにいくらメモリを搭載すれば良いのかが分かる。

目次

サーバー全体で割り当てられるバッファ

サーバー全体で割り当てられるバッファは、サーバーを起動してからすぐに割り当てられることが多いので、負荷に応じて変動するということが少ない。従ってその大きさの見積もりは比較的容易であると言える。変動分が少ないならば決めうちでも問題がないぐらいである。

InnoDB用バッファ

InnoDB用のバッファとして割り当てられるものの中で、最も大きいのはバッファプールであろう。 InnoDBバッファプールは、InnoDBが利用するメモリバッファである。以下の情報を蓄える。

バッファプールを大きくとればとるほどディスクに対するI/Oが減るのでパフォーマンスが向上する。ただしInnoDBのバッファプールをきっちりと大きく使う場合、UNIX系のOSにおいてはファイルシステムのキャッシュを使わないようにする必要がある。両方で同じデータをキャッシュするダブルキャッシュの問題を解消するためである。（InnoDBは元々Windows上でファイルシステムのキャッシュを使わない。）--innodb-flush-methodオプションを調整しよう。


InnoDBはデータディクショナリ情報を蓄えるためのメモリプールを別に用意する。そのバッファは--inodb-additional-memory-pool-sizeオプションで調整することができる。

また、InnoDBのログファイルへ書き込みを行う際にバッファリングに用いられるメモリである。--innodb-log-buffer-sizeにより設定することが出来る。8MB以上に大きくしても効果はないと言われている。

その他にもバッファはあるので、詳細についてはMySQLのリファレンスマニュアルを参照しよう。

http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#option_mysqld_innodb_additional_mem_pool_size

MyISAM用バッファ

MyISAMにはサーバー全体で割り当てられるバッファとセッションごとに割り当てられるバッファがある。

サーバー全体で割り当てられるバッファで最も大きいのはキーバッファだろう。キーバッファの大きさは--key-buffer-sizeで調整することができる。キーバッファは全体で共有されるものの他に、特定のテーブルに対して追加的に割り当てることも出来る。MyISAMをメインで利用する場合にはシステムメモリ全体の30%程度を割り当てるのがいいとされている。これは比較的小さな数字であるが、MyISAMがデータをキャッシュしないからである。

MyISAMはデータのキャッシュをファイルシステムのキャッシュに依存している。言い換えると、ファイルシステムが上手にデータをキャッシュするという（非常に楽観的な？）前提の元に作られているのである。そして、これは割とうまく機能する。（そこそこ・・・いやかなり良いパフォーマンスが出る。）UNIX系のOSにおいては特に設定しなくてもファイルシステムキャッシュが効率的に割り当てられるが、Windowsではサーバー版でない場合レジストリにおいてLargeSystemCacheを変更する必要がある。

クエリキャッシュ

クエリキャッシュは実行されたクエリをキャッシュしておき、その後キャッシュされたクエリと寸分違わず同じクエリが来た場合にクライアントへ結果を返すためのキャッシュである。その際、実際にSQL文が実行（構文解析すら）されることはない。うまく使えばMySQLサーバーの性能を大きく上げることが出来る。クエリキャッシュはアクセスするテーブルが変更されると関連するクエリをキャッシュから破棄する。そのため、変更の多いサーバーでは効果が低い。（ただのオーバーヘッドにしかならないような場合もある。）とりあえず100MBぐらい割り当てて様子を見ながらサイズを調整するのがいいだろう。

サイズが適切かどうかの判断にはSHOW GLOBAL STATUSのQcache_*を参照すること。

クエリキャッシュの大きさは--query-cache-sizeで調整する。

セッションごとに割り当てられるバッファ

MySQLではセッションごとにも多くのメモリを消費する。場合によってはサーバー全体で使用するバッファよりもたくさん消費することもある。セッション数は--max-connectionsまで増やすことが可能であるので、各セッションが同時にソート処理を実行したりすると一気にメモリ消費量が跳ね上がるとうような事態も起こりうるので注意が必要だ。

セッションごとのバッファは、実際にソートやJOINなどの処理で使用するメモリが都度割り当てられ、処理が終了した時点で解放される。

スレッドそのものに割り当てられるメモリ

各々のコネクションスレッドは、セッションを確立しているだけで約256KB程度のメモリを消費する。その内訳は、スレッド（--thread-stackで調節できる。デフォルトは192KBで通常は大きくする必要はない。）、接続バッファと結果バッファ（--net_buffer_lengthで調節できる。デフォルトは16KB。）、およびその他の各構造体のためのメモリ領域となる。

大規模なサーバーでは--max-connectionsが300ぐらいになったりするので、300×256KB=75MB程度アイドル時に必要となる。規模の小さいサーバーでは--max-connectionsも小さく絞るといいだろう。

結果バッファは必要に応じて--max-allowed-packetまで拡張される。--max-allowed-packetは、最も大きなカラム（BLOBなどの）が格納されるように設定する必要がある。

必要に応じて割り当てられるバッファ

以下は全て必要に応じて割り当てられるバッファであり、クエリが実行されてから完了するまで確保され、必要がなくなれば解放される。従って、どれだけメモリが必要になるかはクエリと負荷とクライアント数次第であると言える。端的に言うと、以下のような計算式で算出できることになる。 

セッションあたりのバッファ × セッション数 × 負荷率

実際にクエリを実行してみて、さらに負荷試験を行い、SHOW GLOBAL STATUSの出力結果を吟味してバッファの大きさを調整するといい。このような作業はそれなりにノウハウが必要なので、MySQLのサポートサービスやコンサルティングサービスに頼むのも有効だ。

ソートバッファ、JOIN用バッファ

ソート処理で使用するバッファは--sort-buffer-sizeで調整する。このバッファを超える分のデータは一時ファイルへ格納される。ソート処理を極力高速化したい場合は--sort-buffer-sizeを増やすと良いだろう。ソートバッファは必要に応じて確保されるため、実際に必要なサイズはクエリに因る。データウェアハウス（接続数小、結果セット大）でない限り、ソートバッファは1MBかそこらで十分である。

JOINバッファはインデックスを利用できる場合は必要ない。つまり、テーブルスキャンが必要なJOINを処理する際のバッファである。（通常、インデックスを利用できないJOINは希であり、そのようなクエリはWIREDであると言える。）もしどうしてもテーブルスキャンが必要なJOINを使いたい場合（全件組み合わせるような場合）は、--join-buffer-sizeを増やすとスピードアップさせることができる。実際には、JOINバッファが必要になるようなクエリを書かないことが最も重要であると言える。

シーケンシャルスキャン用バッファ

通常は避けなければいけないテーブルスキャンであるが、どうしてもインデックスを利用することが出来ない時やテーブルサイズが小さい時などはテーブルスキャンをすることがある。その処理を高速化したければ--read-buffer-sizeを増やすと良い。テーブルスキャンにさらにORDER BYの処理が加わると、MySQLは読み込んだ行をいったん一時ファイルへ格納する。そのファイルからデータを読み込む処理をスピードアップさせたいなら、--read-rnd-buffer-sizeを増やす必要がある。read bufferもソートバッファと同じく、データウェアハウス（接続数小、結果セット大）でない限り、1MBかそこらで十分である。

メモリ上に作成されるテンポラリテーブル

複雑なJOINやGROUP BYの処理などは、テンポラリテーブルを用いて行われる。テンポラリテーブルはまずMEMORYストレージエンジンとして作成されるが、--tmp-table-sizeバイトまたは--max-heap-table-sizeバイトを超えるとMyISAMに変換される。もちろん、MyISAMはディスクアクセスを発生させるため処理はスローダウンしてしまう。それを避けるためには、それらの値を大きくすると良い。どの程度まで大きく出来るかは実際に搭載されているメモリ量と相談である。

MEMORYテーブルからMyISAMへの変換はオーバーヘッドになるので、元からテーブルのサイズが大きいと分かっている場合には、SELECTにおいてBIG_TABLEオプションをつけるといいだろう。

バルクINSERT用バッファ

MyISAMストレージエンジンでは、LOAD DATA INFILE、INSERT ... VALUES(...),(...),(...)...、INSERT ... SELECTなどの処理はバルクINSERTと呼ばれる。その際に使用するバッファは--bulk-insert-buffer-sizeにより大きさを調節できる。もちろん大きくすると処理が速くなる。

その他

バッファ以外にメモリを使用する箇所を以下に列挙する。

MEMORYテーブル

MEMORYストレージエンジン

mysqldとそれにリンクされたライブラリ

細かい話ではあるが、mysqld本体とそれにリンクされたライブラリもメモリを消費する。Linuxなら以下のように/proc配下のファイルを参照することで、mysqld本体（およびライブラリ）がどれだけメモリを消費しているのかが分かる。 

shell> cat /proc/<pid of mysqld>/smap

大抵の場合、メモリ消費量はさほど多くないので組み込み用途以外で問題になることはないだろう。

ストアドプロシージャ＆ファンクション

ストアドプロシージャとストアドファンクションも変数やカーソル、ロジック実行時のバッファ用いくらかのメモリを消費する。ロジックが複雑になればなるほど、また変数やカーソルが多くなればなるほど多くのメモリを消費する。コード量が増えた場合には注意が必要である。同様に、トリガもメモリを消費するので注意されたい。

プリペアードステートメント

プリペアードステートメントが作成される度にMySQL内部でメモリが割り当てられる。一つ一つのメモリ消費量はそれほど多くはないが、接続ごとに割り当てられるためサーバー接続数が多い場合には注意が必要だ。

コネクションプールを使用する際は特に注意が必要である。コネクションプールを用いない場合、プリペアードステートメントは接続終了と同時に自動的に破棄されるが、コネクションプールでは接続自身が破棄されないためプリペアードステートメントを明示的に破棄する必要がある。明示的な解放をしない場合、プリペアードステートメントの数によっては一見メモリリークのような状態に陥ってしまう場合もある。

mod_dbdを用いる場合は、DBDPrepareSQLディレクティブを用いることで接続開始と同時にプリペアードステートメントを作成してくれる。アプリケーション側で独自にプリペアードステートメントを作成せず、DBDPrepareSQLディレクティブを用いれば解放を忘れることもなく安全であろう。

FEDERATEDストレージエンジン

FEDERATEDストレージエンジンはC API（libmysqlclient）を用いて実装されている。C APIのmysql_store_result()は結果セットをバッファリングするため、場合によってはかなりの量のメモリを消費してしまう。FEDERATEDストレージエンジンは、handlertonではなくhandlerインスタンスごとに接続を確立するため、接続数が多いとメモリ消費量が膨大になる場合がある。

どの程度のメモリが必要になるのかは、完全に実行するクエリに依存する。特に結果セットの行数の影響が最も大きいので、FEDERATEDストレージエンジンにおいては抽出する行をしっかりと絞り込むように心がけたい。

FEDERATEDストレージエンジン

INSERT DELAYED

MyISAM、ARCHIVE、MEMORYテーブルへのINSERTステートメントに対してDELAYEDオプションをつけると、INSERTの処理がバックグラウンドで（遅れて）処理される。バックグラウンドの処理はそれぞれテーブルごとに専用に作成されるスレッド上で行われる。MySQLはスレッドに処理を渡すと即座にクライアントに対して応答を返し、クライアントは次の処理にとりかかることが可能となる。


キューイングされた行はメモリ上に保持される。そのため各チューニングパラメーターに応じたメモリが必要となる。

BLOB用のバッファ

大きなBLOBまたはTEXTを含む行を処理するときは注意が必要だ。MySQLサーバーの内部でデータが処理される過程において、メモリ間でデータがコピーされるため、BLOB/TEXTはそのサイズの3倍のメモリを消費してしまう。

INFORMATION_SCHEMA

SHOW ... コマンドやINFORMATION_SCHEMAに対する操作を行うと、MySQLサーバーの内部にMEMORYストレージエンジンによる一時的なテーブルが作成される。特にテーブルが多数ある環境でINFORMATION_SCHEMAを使う場合は注意が必要だ。

スレッドキャッシュ

一般的にメモリの割り当てや割り当てた後の領域の初期化は時間のかかる処理である。それにスレッドの生成が加わればかなり性能が低下してしまう。それを防ぐため、MySQLにはスレッドキャッシュという仕組みがあり、文字通りスレッドをキャッシュする。MySQLの内部では、スレッドはひとつのセッションに対して一つずつ割り当てられるのだが、セッション終了時にスレッドを完全に破棄せずに再利用（キャッシュ）するのである。キャッシュ内にスレッドがある場合、セッション開始時にそちらからスレッドが割り当てられる。そうすることで、スレッドの生成、データ構造体の初期化などの処理をスキップすることができる。

各スレッドは、スレッドキャッシュに入る前に各種バッファの解放処理を行う。キャッシュに入っているスレッドはそれぞれ約256KB程度のメモリを消費する。スレッドキャッシュの大きさは--thread-cache-sizeで調節する。

スレッドキャッシュはサーバーがアイドル時に使われることになるので、メモリを最大でどの程度消費するかということにはあまり関係がないといえる。

テーブルキャッシュ

テーブルキャッシュは、テーブルハンドラのキャッシュである。一つあたりのメモリ消費量はそれほど多くない（数KB程度）が、同時に扱うテーブル数が多い時にはテーブルキャッシュ数を増やす必要があり、その場合にはメモリの消費量もバカにはならない。ハンドラは各スレッドごとに必要になるが、合計の数はサーバー全体で決まっていて、--table-(open-)cache変数で調整することが出来る。

Repair by sorting

MyISAMのリペアの方法であるRepair by sortingをバッファリングして高速化させたい場合には、--myisam-sort-buffer-sizeを大きくすれば良い。大きくすると、ALTER/OPTIMIZE/REPAIR TABLE等の処理を行っている場合にその分だけメモリを消費するので、そのような処理はサーバーの負荷が下がっている（メモリ消費量が少なくバッファを安全に確保できる）タイミングを見計らって実行すると良い。

ツール

Estimate MySQL Memory Consumption
http://forge.mysql.com/tools/tool.php?id=44
my.cnf/iniから消費するメモリ量を見積もるツール。ただし精度は高くないので注意が必要。特にセッションごとに割り当てるメモリを多めに見積もる傾向がある。

参考サイト

MySQL 5.1 リファレンスマニュアル
http://dev.mysql.com/doc/refman/5.1/ja/memory-use.html
MySQL Performance Blog
http://www.mysqlperformanceblog.com/2006/05/17/mysql-server-memory-usage/
"http://www.mysqlpracticewiki.com/index.php/MySQL%E3%82%B5%E3%83%BC%E3%83%90%E3%81%8C%E6%B6%88%E8%B2%BB%E3%81%99%E3%82%8B%E3%83%A1%E3%83%A2%E3%83%AA" より作成
個人用ツール