MySQL とメモリに関するまとめ

mysql tuning os

前回のエントリーデータベースを用いたセッションデータ管理についてで、MySQL とメモリの関係について良く分からない部分があると書きました。

実はここに関する理解はかなり曖昧な部分があって、調査して追記します。とくにメモリ利用量について。mysqld のプロセスが利用できるメモリの上限が、32bit OS の場合は3G 程度ということは、innodb_buffer_pool_size もこの制限を受け、これについての警告が、先に紹介したリファレンスマニュアルのものという理解だけどいいのだろうかというのが1つ。

2 つ目は、この理解があっているとすると、4G 以上のクラスのメモリをつんだサーバをDB サーバとして利用する場合、64 bit OS でないとリソースの有効活用ができないか。それとも、先に書いたとおり、OS レベルのキャッシュとして利用できるから、結果としてデータファイルを読み込む際のI/O 負荷軽減になるか


ここも含めMySQL とメモリの関係について調査しました。最初にメモリの基本的な部分を述べて、次にMySQL と関係ある部分を述べます。上の2つの疑問についての自分なりの解はMySQL の部分で一緒に書きたいと思います。
OS はLinux を前提とします。

OS とメモリ

MySQL 云々の前に、まずOS とメモリについてです。

32bit OS が認識できるメモリ容量

一般的なWindows クライアントが扱えるメモリは3GB 程度とかその辺りの話です。

まず始めに、OS がメモリを認識するには「物理アドレス空間」が必要になります。32bit OS の場合、4G(= 2^32)Bの物理アドレス空間を持っています。よって、32bit OS が認識できるメモリ容量は最大4GB ということになります。

  • MMIO(Memory Mapped IO)

先に書いた通り、32bit OS は4GB の物理アドレス空間を持ちますが、実際に認識できるメモリ容量は4GB より小さくなります。その理由はがMemory Mapped IO という仕組みによるものです。

MMIOは有限であるアドレス空間に、メモリと様々なデバイス制御用の空間を共有させる仕組みです。接続しているデバイスの制御(使用)をするには、アドレス空間が必要になるということです。

PCはまずデバイス制御用に必要な空間をアドレス空間に予約し、残りをメインメモリに割り当てます。領域を被って使用することはできないからです。デバイス制御用に割り当てられる領域が多ければ、その分使用できるメインメモリは少なくなります。

アドレス空間(4GB)−デバイス制御用予約分(1GB前後)＝使用できるメモリ量(3GB前後)


これが、Windows クライアントが認識できるメモリの限界が3GB 程度な理由です。x86 アーキテクチャのPC ならLinux でも同様の仕組みを使っていて、同様の制限を受けます。

ここまでで、32bit OS では3GB 程度のメモリしか認識できないことが分かりました。ですが、Linux では32bit OS を使っている場合でも3GB を超えるメモリを認識できる場合があります。この機能をPAE と言い、物理アドレス空間を64GB まで拡張してくれます。
PAE が有効になっているかは、「uname コマンド」で調べることができ、最近は最初から有効になっていることが多いみたいです。僕の周りのマシンでは全て有効になっていました。

# カーネルバージョンの最後に「PAE」がついていれば有効になっている
$ uname -r
2.6.18-92.el5PAE


ここでのポイントは、最近のLinux を使っていれば32bit OS でも16GB 等の大容量メモリを扱うことができるということです。

MySQL とメモリ

続いて今回の本題のMySQL とメモリの話です。
Linux が32bit OS でも大容量メモリを扱かえるなら、メモリに関して64bit OS を使うメリットがあまりないように思えます。8GB くらいのメモリを積んで、innodb_buffer_pool_size=4G とかにすればいいんじゃないでしょうか。
実はこれは無理です。OS が扱えるメモリ容量と、mysqld のような個々のプロセスが扱えるメモリ容量は同じではないのです。

仮想メモリとは、各プロセス専用に与えられるメモリのようなもので、メモリというリソースを効率良く利用するための仕組みです。1つのプロセスが利用できるメモリの最大容量は、この仮想メモリの最大容量と同じです。この容量は「仮想アドレス空間」のサイズで、32bit 版Linux なら4GB となります。先に述べたPAE を使うと物理アドレス空間の容量は大きくなるのですが、この仮想アドレス空間のサイズは大きくなりません。

先の「仮想アドレス空間」はさらに、「カーネル空間」と「ユーザ空間」に分かれています。容量はそれぞれ1GB、3GB となります。mysqld のようなユーザプロセスはユーザ空間のみしか使用することができません。


これらにより、32bit OS の場合、1プロセスで使用できるメモリの最大容量は3GB ということが分かりました。ここで、1つ目の疑問について考えてみようと思います。

  • mysqld が消費するメモリ容量とglibc の制約

mysqld のプロセスが利用できるメモリの上限が、32bit OS の場合は3G 程度ということは、innodb_buffer_pool_size もこの制限を受け、これについての警告が、先に紹介したリファレンスマニュアルのものという理解だけどいいのだろうかというのが1つ。

前半はyes です。mysqld は1プロセス/マルチスレッドなので、innodb_buffer_pool, key_buffer のようなグローバルバッファ、スレッド毎に確保されるsort_buffer, read_buffer のようなスレッドバッファ全ての合計が3GB に収まっていないといけません。
当然、innodb_buffer_pool_size ようなパラメータはこの制限を気にして設定してやらなければならず、またinnodb_buffer_pool_size=3G ようにしてしまうと、残りのバッファにあてるメモリがまるっきりなくなってしまします。


mysqld が消費するメモリ容量の算出式は以下のようになります。

innodb_buffer_pool_size + key_buffer_size + max_connections * (sort_buffer_size + read_buffer_size) + max_connections * 2 MB


これが3GB 以内になればいいのかというとそうではありません。(なんなんだ!!!)
これが疑問の後半で言っている、「これについての警告が、先に紹介したリファレンスマニュアルのものという理解だけどいいのだろうか」という部分です。先程のリファレンスマニュアルと同様のページに以下のようにあります。

警告: Linux x86 では、メモリ使用率の設定を高くし過ぎないように注意してください。glibc はプロセスヒープがスレッドスタックよりも大きくなることを許可しており、その場合にサーバがクラッシュします。

この値(メモリ容量の計算式の値)が、2 GB に近いか、2 GB を超えていると危険です。各スレッドはスタックを使用し（通常は 2 MB。ただし MySQL AB バイナリでは 256 KB のみ）、最悪の場合、sort_buffer_size + read_buffer_size の大きさの追加メモリも使用します。


この制限により、結局mysqld は2GB までしかメモリを扱うことができません。仮想アドレス空間の制限とかではなくglibc の実装上の制限ですね。ちなみに、この制限にひっかからないかチェックするスクリプトがDSAS開発者の部屋:5分でできる、MySQLのメモリ関係のチューニング！で紹介されています。


続いて2つめの疑問について考えます。

この理解(mysqld が使用できるメモリ容量が2GB ということ)があっているとすると、4G 以上のクラスのメモリをつんだサーバをDB サーバとして利用する場合、64 bit OS でないとリソースの有効活用ができないか。それとも、先に書いたとおり、OS レベルのキャッシュとして利用できるから、結果としてデータファイルを読み込む際のI/O 負荷軽減になるか

  • OS のキャッシュ機構(ページキャッシュ)

先の疑問について考えるにあたって、ページキャッシュについて知っておく必要があります。
ページキャッシュはディスクIO 負荷を最小限に抑える機能でLinux を普通に使っていれば意識することなく利用しています。簡単に言えば、「ディスクから初めて読んだデータをメモリ上にキャッシュしておき、次に同じデータにアクセスする際にはメモリにだけアクセスすれば良い」ということです。これによって、ディスクアクセスの回数を減らしIO パフォーマンスを向上させることができます。
詳しくは、naoyaのはてなダイアリー - Linux のページキャッシュ, Linux I/O のお話 write 編 - naoyaのはてなダイアリーあたりを読んでみて下さい。
ページキャッシュのポイントは、カーネルは使っていないメモリを最大限ページキャッシュに使うように設計されていることと、ページキャッシュで利用できるメモリ容量はOS が認識しているメモリ容量であるということです。


これで先の疑問に答えられます。例えば4GB のメモリを積んだサーバでMySQL を使っていて、mysqld が2GB のメモリを確保しているとしましょう。この場合、残りの2GB含めmysqld が実際に使っていない実メモリは全てキャッシュとして利用されます。なので、実際には32bit OS を使っていても、メモリを多くつめば、それだけIO 負荷を軽減させることができます。


ここで疑問は解決したのですが、もう少しMySQL とメモリについて書きます。

  • MyISAM とInnoDB のメモリの使い方

実は、メモリの使い方はストレージエンジンレベルでも異なります。
MyISAM はMySQL 独自のバッファ(key_buffer)上にテーブルのインデックス情報をキャッシュするように設計されています。一方、InnoDB は、インデックスとデータを両方とも独自バッファ(innodb_buffer_pool)上にキャッシュするように設計されています。
漢(オトコ)のコンピュータ道: MySQLを高速化する10の方法等を始めMySQL のチューニングでよく言われる、key_buffer_size にはメモリの25%, innodb_buffer_pool_size にはメモリの70-80% くらいあてるというのは、MyISAM の場合はデータをkey_buffer 上にキャッシュしないため、ページキャッシュ用のメモリを残しておく必要があるためです。

  • MySQL 独自のキャッシュ機構

MySQL は独自のバッファをメモリ上に持ち、その中にデータをキャッシュすることは既に書きました。この機構はinnoDB では特に、読み/書き動作ともにページキャッシュ機構と似ています。すなわち、一回読み出したデータをメモリ上にキャッシュすること。そして、書き込みは非同期に実行します。詳しくは漢(オトコ)のコンピュータ道: InnoDBのログとテーブルスペースの関係を参照下さい。
これは無駄があることを示しています。実際にMySQL には、OS のページキャッシュの利用を抑制する設定があり、以下のように設定します。

innodb_flush_method = O_DIRECT


これを使用する場合、MySQL が使えるバッファは独自バッファのみになるのでinnodb_buffer_pool_size を大きめにとることになります。実際この方法はInnoDB のチューニングに使われている方法で、show innodb status でチェックする値 - id:kazuhookuのメモ置き場ã‚„MySQL Conference & Expo 2007 - とあるはてな社員の日記を読むとPathtraq ã‚„Youtube でも使われていると想像できます。

  • 64bit OS

32bit OS でも、「メモリ積む」+「PAE」+「mysqld で2GB 使う(残りページキャッシュ)」にすれば良いパフォーマンスを出すことはできると思います。しかし、大容量のデータを扱う場合ではやはりmysqld にメモリをもっとあてたい場合があるわけです。特にInnoDB の場合はMySQL に最適化されている独自のバッファを使う方がいいわけで。
スケーラビリティも考えるとDB サーバでは64bit OS を使うのが良いということになると思います。

まとめ

  • 32bit 版Linux ではPAE が有効になっていれば64GB までメモリを扱える
  • 32bit 版Linux ではユーザプロセスが使用できるメモリは3GB まで
  • 32bit 版Linux ではmysqld が使用できるメモリは2GB まで
  • 使用されていないメモリはページキャッシュとしてIO 負荷軽減に利用される
  • 32bit 版Linux でMySQL サーバチューニングを頑張るなら「メモリ積む」+「PAE」+「mysqld で2GB 使う(残りページキャッシュ)」
  • 64bit 版Linux で制限から脱却する
  • 64bit 版Linux でスケーラビリティを確保する
記事中で紹介した以外の参考記事&本