これは、MariaDB Memory Allocation の日本語訳です。

この記事では「RAM」をすべて「メモリ」と訳していますが、これは言うまでもなく主記憶のことを指します。

訳注: 元の文章には書かれていないけれども注釈を与えたほうがいいと思った場所には、訳注を書いています。

MySQL へのメモリの割り当て方まとめ

もし MyISAM だけを使っているのであれば、key_buffer_size を使用可能なメモリの 20% にし、innodb_buffer_pool_size を 0 にする。

もし InnoDB だけを使っているのであれば、innodb_buffer_pool_size を使用可能なメモリの 70% にする。
key_buffer_size は 10M などの小さい値にする。ゼロにはしないこと。

訳注: ここでの「使用可能な（available）」とは、メモリ全体に対してではなく、OS や他のプロセスが使っているメモリを考慮した本当に使用可能なメモリのことを指す。

MySQL のメモリのチューニングは、だいたい次のような手順で行う。

リリースされている my.cnf / my.ini を持ってくる
key_buffer_size と innodb_buffer_pool_size を、それぞれのエンジンの使用状況や使用可能なメモリと相談して決める
スロークエリは、インデックスやスキーマなどのテーブル構造や SELECT 文を適切にすることでたいてい解決する（のでチューニングで解決するものではない）
クエリーキャッシュは、何ができて何ができないのかを理解しないまま使ってはならない
max_connections など、他のパラメータは問題が起きない限り変更してはならない
変更している箇所が、my.cnf や my.ini の [mysqld] セクションであることを確認する

訳注: デフォルトの my.cnf は、MariaDB のソースコードの debian/additions/my.cnf にある。僕の Gist にもコピーを作ってある。

メモリの 20% / 70% の値は、最低でもメモリが 4GB あるマシンでの話である。
もしこれよりもメモリが小さいオンボロマシンや VM などを用いるのであれば、この数字は大きすぎる（ので、もう少し小さくしたほうがいい）。

それでは、おぞましい詳細を述べていく。
なお、NDB Cluster（MySQL Cluster のエンジン）についてはここでは扱わない。

キーバッファ

MyISAM には、2 種類のキャッシュがある。

インデックスブロックに対するキャッシュは、キーキャッシュ（キーバッファ）によって維持される。B 木になっていて、1 ブロック 1KB になっている。インデックスブロックは .myi ファイルにある。

データブロックに対するキャッシュは OS にまかせてあるので、データブロックのキャッシュのためにメモリを空けておく必要がある。データブロックは .myd ファイルにある。
90% を超えるメモリを使うと（実際にはまだメモリがたくさん空いているのに）文句を言う OS も存在するので注意すること。

訳注: 詳細は MySQL のリファレンスを参照のこと。

以下の SQL 文を実行して、Key_read_requests / Key_reads を計算する。
この値が大きければ（10 より上とか）、key_buffer_size は十分に確保されていることを表している。

SHOW GLOBAL STATUS LIKE 'Key%';

訳注: MyISAM を使っていなければ、この値が小さくても構わないと思う。

バッファプール

InnoDB は、すべてをバッファプールにキャッシュする。バッファプールの大きさは innodb_buffer_pool_size によって決まる。
16KB ブロックのデータとインデックスのブロックが開かれている（使っている）テーブルから使われ、加えて少しオーバーヘッドで使われる。

MySQL 5.5（とプラグインを入れた 5.1）では、ブロックサイズを 8KB や 4KB にすることができる。
また、MySQL 5.5 からはバッファプールを複数持つことができる。バッファプールが複数あると、バッファプールごとに mutex を持つため mutex によるボトルネックが多少解消する。

InnoDB のチューニングに関する詳細は Optimize MySQL Database for Increased InnoDB Performance を参照のこと。

なるべくメモリを切り詰める方法

メインのキャッシュをなるべく小さくするやり方である。ここで述べることは、メモリが 2GB より小さかったり、他のプロセスがいっぱい走っているようなマシンにおいて重要である。

訳注: 使用可能メモリにくらべてはるかに大きい InnoDB のテーブルを持っている場合には、ここに書いてあるやり方はほとんど参考にならない。テーブルのサイズが小さい場合にのみ有効である。

すべてのデータベースで SHOW TABLE STATUS を走らせて、すべてのテーブルの情報を得る。

訳注: information_schema、performance_schema などは含まない。

MyISAM のテーブルは、Index_length を足し合わせる。key_buffer_size はこの値だけにする。

InnoDB のテーブルは、Data_length と Index_length を全部足し合わせる。innodb_buffer_pool_size は合計の 110% だけにする。

もしスワップが必要になる（ほどメモリを使ってしまう）のであれば、key_buffer_size と innodb_buffer_pool_size を同じ割合で減らしていくとよい。

下の SQL 文を走らせることで、上で述べた値の和を計算できる。
（テーブルがたくさんある場合には時間がかかる可能性がある。）

SELECT  ENGINE,
        ROUND(SUM(data_length) /1024/1024, 1) AS "Data MB",
        ROUND(SUM(index_length)/1024/1024, 1) AS "Index MB",
        ROUND(SUM(data_length + index_length)/1024/1024, 1) AS "Total MB",
        COUNT(*) "Num Tables"
    FROM  INFORMATION_SCHEMA.TABLES
    WHERE  table_schema not in ("information_schema", "PERFORMANCE_SCHEMA", "SYS_SCHEMA", "ndbinfo")
    GROUP BY  ENGINE;

mutex によるボトルネック

訳注: これを読んでいる人には釈迦に説法であろうが、mutex とはマルチスレッド処理の際、矛盾が発生しないようにするために単一スレッドしか走らせない時間を作るロックの手法の 1 つである。

MySQL は CPU がシングルコアだった時代に作られており、様々なアーキテクチャで簡単に使えるように設計されている。
不幸なことに、こうした設計により、インタロックが発生するような操作に対して若干ザコな部分がある。
（mutex のボトルネックはそうしたクソザコ仕様の 1 つである。）

訳注: ここでのインタロックとは、現在行われている処理が終了するまで操作を受け付けないこと。

MySQL ではいくつかのクリティカルなプロセスのために、若干（本当に若干だが）mutex を使っている。

MyISAM のキーバッファ
クエリキャッシュ
マルチコアでの InnoDB のバッファプール

マルチコアでの InnoDB のバッファプールは、かなり mutex による問題が発生しやすい。
一般に、4〜8 コアを超えてくると、MySQL が速くなるどころか遅くなる。
MySQL 5.5 および Percona XtraDB では、そういった InnoDB のクソ仕様に多少対処しており、32 コアくらいまでコア数の実質的な制限が増え、それ以上増やしても性能が落ちることはなく一定になる。MySQL 5.6 では 48 コアくらいまで対応しているとしている。

ハイパースレッディングとマルチコア

結論から言うと、

ハイパースレッディングはやめろ
8 コアより多いコア数はやめろ
ハイパースレッディングとかいうのは明らかに過去の遺産なので、この節は別に適用されることがないだろう

ハイパースレッディングはマーケティング的にはいいんだろうが、パフォーマンスを見るとゴミである。
なにせ、2 つの CPU を（仮想的にだが）持っているのに、ハードウェアのキャッシュは共有しているのである。
当然 2 つがまったく同じことをするならキャッシュは使い物になるのだろうが、2 つが違う計算をすればお互いのキャッシュを破壊し合うだけである。

そもそも MySQL はマルチコアでの動作はあまりよろしくない。ハイパースレッディングを切ったほうが多少は速くなる。

32bit OS と MySQL

まず、そういう OS（とハードウェアかな?）を持っている場合は、メモリが 4GB あっても使うことができない。4GB よりも多いメモリを積んでいても、32bit OS からでは決してアクセスすることはできない。

そして、そういう OS の場合はおそらく各プロセスが使えるメモリを制限している。

たとえば、FreeBSD の maxdsiz では、デフォルトが 512MB になっている。

$ ulimit -a
...
max memory size (kbytes, -m) 524288

そのため、mysqld のプロセスがどれくらいのメモリを使えるかをまず確認して、それに対して 20% / 70% ルールを適用する。実際はもう少しだけ減らしたほうがいいだろう。

もし [ERROR] /usr/libexec/mysqld: Out of memory (Needed xxx bytes) などのエラーが起きたらそれは MySQL がメモリを使いすぎということなので、キャッシュの設定を減らしたほうがよい。

64bit OS で 32bit MySQL を動かす

この場合、たとえ OS は 4GB 以上のメモリを使えても、MySQL は 4GB のメモリしか使えなくなる。

もし少なくとも 4GB のメモリがあるのであれば、次のように設定するとよいだろう。

key_buffer_size をメモリ全体の 20% にする（ただし 3G よりは大きくしない）
innodb_buffer_pool_size = 3G

というか、64bit 版の MySQL を使えばよい。

64bit OS と MySQL

MyISAM だけを使っている場合について。
MySQL 5.0.52 / 5.1.23 以前では、key_buffer_size に 4G の制限があった。4G の制限を守るか、20% ルールで値を決めればよい。innodb_buffer_pool_size は 0 にする。

訳注: この部分は文が崩壊しているのだが、たぶんこういうことがいいたいんだと思う。

InnoDB だけを使っている場合について。
70% ルールで値を決めればよい。
大量にメモリがあり、MySQL 5.5 以上であれば、複数のバッファプールを用いるとよい。innodb_buffer_pool_instances を 1〜16 くらいで決めるとよいが、1 つあたりの大きさは最低 1GB にする。
（複数のバッファプールを用いることによる効果はどれくらいなのかをちゃんと計測していないのだが、そんなに効果があるわけでもないと思う。）
key_buffer_size は 20M などの小さい値にするが、ゼロにしてはいけない。

訳注: 最初と言ってることが違う。

もし両方のエンジンを用いているのであれば、両方とも値を小さくする。

各スレッドは、（スレッドが存在するだけで）多少のメモリを使う。
この値はだいたい 200KB くらいなので、100 スレッドあれば 20MB 使うことになる。これはそんなに大きな値ではない。
ただ、max_connections を 1000 にすれば、200MB やそれ以上使う可能性があることを考慮しなければいけない。
接続数を増やせば、その分他の問題に対処しなければいけなくなる可能性があることを忠告しておく。

訳注: スレッド数は max_connections、各スレッドで使う最低のメモリが thread_stack によって決まる。

MySQL 5.6 / MariaDB 5.5 では、max_connections とスレッドプーリングが相互に作用する。これはちょっと話が面倒くさい。

thread_stack が足りなくなることはほとんどないが、もし足りなくなったら 256K とかにするとよい。

訳注: thread_stack は 64bit ではデフォルトで 256K。基本的にはデフォルトのままでよい、ということになる。

max_connections、wait_timeout、コネクションプールなどの詳細については MySQL Error: Too many connections を参照のこと。

table_cache / table_open_cache

バージョンによって名前が違う。

オープンできるファイルの数は、OS によって制限される。
テーブルあたり 1〜3 つのファイルを開く。当然パーティションごとにその数のファイルが開かれる。パーティション分けされたテーブルに対するほとんどの操作は、すべてのパーティションを開いてしまう。

訳注: パーティションが適切に決められる SELECT 文などでは必要なパーティションのみ開かれるらしい。

*nix では、ulimit コマンドでファイルの数を確認できる。最大は数万とかであるが、1024 の場合もある。1024 の場合、テーブルが 300 しか使えないことになる。

訳注: MySQL がオープンできるファイルの数は、設定値に応じて自動で増やされる。詳細は MySQL の max_connections, table_open_cache, open_files_limit の関係 - @tmtms のメモを参照のこと。

一方、テーブルキャッシュは、検索にリニアスキャンが必要など、非効率的な実装になっている（なっていた）。そのため、table_cache を数千などの値の大きい値にしてしまうと、実は MySQL のパフォーマンスが落ちうる。
（ベンチマークが結果を示しているのだが、この話は議論中である。）

SHOW GLOBAL STATUS を実行して Opened_files / Uptime を計算することで、1 秒あたりに開かれたテーブル数を計算できる。
もしこれが、たとえば 5 とかより大きい場合には、table_cache を増やしたほうがいい。もし、たとえば 1 とかよりも小さい場合には、table_cache を減らすことでパフォーマンスが改善する。

訳注: table_cache を減らすべきである、という議論はあまり見たことがない。

クエリキャッシュ

結論: query_cache_type = OFF と query_cache_size = 0

クエリキャッシュは、実質的に SELECT 文と結果の key-value ストアである。

もう少しちゃんと説明すれば、クエリキャッシュのいろんな側面が見えてくる。負の側面が。

クエリキャッシュは、key_buffer_size や innodb_buffer_pool_size の値とまったく関係なく動作する
場合によってはクエリキャッシュはかなり使い物になる。ベンチマークで 1000 倍速い値を出すことは難しくない
クエリキャッシュは、1 つの mutex でコントロールされている
クエリキャッシュは、query_cache_type = OFF かつ query_cache_size = 0 でない限り、すべての SELECT 文で動作する
当然 query_cache_type = DEMAND であっても mutex は使われる
当然 SQL_NO_CACHE にしても mutex は使われる
（SQL 文にスペースが増えたなど）ほんの少しでもクエリが変われば、異なるクエリキャッシュのエントリとなる
クエリキャッシュを後から OFF にしても、クエリキャッシュは完全には OFF にならない
- https://bugs.mysql.com/bug.php?id=60696

クエリキャッシュの削除は、とても重く、しかも頻繁に起きる。

テーブルに対してどのような書き込みが起きても、そのテーブルに対するすべてのクエリキャッシュは削除される
これは、読み込み専用のスレーブでも発生する
クエリキャッシュの削除は線形に時間がかかるので、クエリキャッシュが大きいほど（200MB とかでも）重くなる

どの程度クエリキャッシュが使われているかは、SHOW GLOBAL STATUS LIKE 'Qc%' を実行して Qcache_hits / Qcache_inserts を計算することでわかる。
この値がヒット率となる。もしこの値が、たとえば 5 とかより大きいのであれば、クエリキャッシュを維持する価値はあるだろう。

もしクエリキャッシュが有効であると思ったのであれば、次のようにすることをおすすめする。

query_cache_size は 50M を限度とする
query_cache_type = DEMAND とする
すべての SELECT 文で、キャッシュする価値があるかどうかにもとづいて SQL_CACHE と SQL_NO_CACHE を書く

クエリキャッシュについては、次の資料も参考になる。

thread_cache_size

これはチューニングが難しい。ゼロだとスレッド（コネクション）を作るのが遅くなる。0 でない小さい数字（10 とか）であればよいだろう。この設定はほとんどメモリの使用には影響しない。

この値は、スレッドとは異なる別に維持されるプロセスの値である。そのため、max_connections の値には制限されない。

訳注:デフォルトは 8 + max_connections / 100。

バイナリログ

もしバイナリログをレプリケーションや即時リカバリのために作るようにすると、システムは永久にバイナリログを作り続ける。つまり、ストレージをゆっくりと消費していく。
expire_logs_days = 14 などとして 14 日分だけ残すなどしておくとよい。

Swappiness

訳注: swappiness とは、スワップの起きやすさを制御できるパラメータ。最近の Linux であれば /proc/sys/vm/swappiness にそれを定義しているファイルがある。0 から 100 まで指定でき、大きいほどスワップが起きやすくなる。デフォルトは 60 となっていることが多い。

Red Hat Enterprise Linux は、何を考えたらそうなったのかわからないが、なるべくスワップを使うように設定することができる。別に構わないのだが、MySQL では使い物にならない（のでやめるべきである）。

MySQL は、メモリがちゃんと安定して確保できることを前提としている。どのようなスワップであっても、おそらく MySQL のパフォーマンスを著しく落とすことになる。

訳注: ダッシュの後が何をいいたいのかよくわからなかったので省略している。

もし swappiness を大きくすると、後に確保されることを想定してメモリを残すため、OS はメモリに空き領域を作りがちになり、結果的にメモリの一部を失うことになる。そういう仕様は MySQL には必要がない。

swappiness を 0 にするとスワップができなくなるので、メモリがあふれると OS が死ぬ。死ぬよりは MySQL が若干不安定になる方がマシなので、最終的には swappiness を 1 とすることをおすすめする（2015 年現在）。

詳細は OOM relation to vm.swappiness=0 in new kernel; kills MySQL server process を参照のこと。

もう少し多め（5 くらい?）が MySQL オンリーのサーバーには適しているかもしれない。

NUMA

訳注: NUMA（Non-Uniform Memory Access）とは、簡単にいうと、CPU やメモリの場所によってメモリへのアクセス速度がバラバラなアーキテクチャのこと。

えっと、じゃあ CPU がどうやってメモリとやり取りするかの仕組みを複雑にしていこう。

まず NUMA（Non-Uniform Memory Access）について。
NUMA では、CPU（もしくはコア）それぞれに対して、メモリの一部がぶら下がっている。こうすると、自分の持っているメモリに対してはアクセスが速くなり、一方他の CPU が持っているメモリに対しては（数十サイクルくらい）遅くなる。

次に OS について。どうやら 2 つのことが起きているらしい。少なくとも 1 つのケースで確認している（Red Hat Enterprise Linux だったかな?）。

OS のメモリアロケーションが、どれかの CPU のメモリに紐付けられる
その CPU のメモリがいっぱいになるまで、他のプロセスもそのメモリに割り当てられる

訳注: ここでの「どれか」とは first のことなのだが、ここでは少し曖昧な表現に変えている。

すると、次のような問題が起きる。

OS と MySQL が、ある同じ CPU のメモリに割り当てられる（MySQL のサーバーはたいてい OS と同時に起動するので）
MySQL の一部はそのうち次の CPU のメモリに割り当てられる
そして OS が何かのためにメモリを確保しようとする
CPU はその OS にメモリを確保させようとするだろうから、MySQL が追いやられてしまいスワップさせられてしまう
クソ

NUMA を持ったアーキテクチャがあれば、次のコマンドで NUMA の詳細を確認できる。

dmesg | grep -i numa

訳注: Google Cloud Platform の Compute Engine では、NUMA が設定されている。

解決策はおそらく次のとおりである。

メモリの確保をインターリーブにする。こうすれば、異なる CPU に紐付いたメモリにアクセスするコストが起きやすくなるものの、早すぎるスワップは抑制できる。メモリの全領域を使いたいと思ってしまうと、どのみちメモリのアクセスコストはかかってしまう（ので、この解決策で問題ないだろう）。
MySQL が古ければ、numactl --interleave=all としてシステムでインターリーブさせる。
MySQL 5.6.27 では、innodb_numa_interleave = 1 とすることで（システムを操作しなくても）インターリーブにできる。

訳注: インターリーブとは、わざと不連続に確保することで、性能を向上させる方法のこと。

もう 1 つの解決策は簡単である。NUMA を切ろう。OS にその機能があればだが。

これらに対処することで、パフォーマンスは数パーセントくらいは変わる。

ページサイズを大きくする

これもまた、ハードウェアのパフォーマンスを上げるための怪しい方法の 1 つである。

CPU がメモリにアクセスするためには TLB（translation lookup buffer）が用いられる。
TLB が仮想アドレスから物理アドレスを得るためのハードウェア上にあるメモリのルックアップテーブルだと考えよう。

訳注: 仮想アドレス→物理アドレスの対応を取っているのはページテーブル。TLB はページテーブルのキャッシュ。ページテーブルへのアクセスは遅いため、TLB のミス（欲しい仮想→物理の対応が TLB に載っておらずページテーブルを参照しなくてはいけなくなること）は好ましくない。特定のページにアクセスされやすい（局所性がある）場合には TLB のミスが起きにくくなる。

TLB の大きさは有限なので、物理アドレスのルックアップを行うときにミスが発生することがある。ミスをすると（ページテーブルへアクセスするのが）重いため、これは避けたい。

メモリは普通 4KB でページングされているので、実際には TLB は上位 64 - 12 = 52bit に対してマッピングをとっている。そして、仮想アドレスの下位 12bit は（アクセスのために）そのままにしておく。

たとえば、128GB メモリがあったとして、4KB でページングされると、32M 個のページテーブルのエントリがあることになる。これはちょっと多すぎて、TLB の容量を超えてしまう。そこで、ページサイズを大きくするというトリックを使う。

ハードウェアと OS が対応していれば、（4KB ではなく）4MB のようなページサイズでメモリを扱うことができる。これによって TLB のエントリが少なくて済むが、ページングの単位が 4MB になってしまう（それはそう）。そうすると、非ページ領域が大きくなりやすい。

訳注: 非ページ領域（non-pagable / non-paged pool）とは、カーネルなどが用いるページアウトしてはいけないメモリ領域のこと。

さて、メモリがページ領域と非ページ領域に分かれてしまった。どのページを非ページ領域にするべきだろうか。MySQL においては、InnoDB のバッファプールが完璧な選択肢である。
だから、正しく次のように設定を行うことで、InnoDB を少しだけ速くできる。

大きなページを有効化する
OS に適切な量をバッファプールに確保させる
MySQL に大きなページを使わせる

詳細は MySQL :: Re: innodb memory usage vs swap を参照のこと。
このスレッドでは、何を探して何をすればいいのかが詳細に書いてある。

全体として得られるパフォーマンスは、数パーセント程度である。設定の面倒にしては利益が少なすぎる。

ページサイズは普通で構わない。

ENGINE=MEMORY

MEMORY は、MyISAM や InnoDB の代わりのエンジンとして稀に使われる手段である。データは揮発してしまうので、使い方は限られる。
MEMORY テーブルのサイズは max_heap_table_size によって制限されており、デフォルトは 16MB である。
もしこの値を大きな値に変えてしまうと、本来使えたはずのメモリが使えなくなってしまうので注意すること。

変数の設定方法

my.cnf（Windows では my.ini）を開いて、次のように行を編集するか追加する。

innodb_buffer_pool_size = 5G

すなわち、変数名 = 値で設定する。
K（1024）、M（1048576）、G（1073741824）などの省略が使える。

訳注: あらゆる項目で K、M、G などがすべて使えるかどうかは確認していないので、注意して設定したほうがいいと思う。

サーバーがこの値を見るためには、設定が [mysqld] のセクションに書いてある必要がある。

my.cnf や my.ini に書いた設定は、サーバー（mysqld）を再起動するまで反映されない。

だいたいの設定は、root ユーザ（か SUPER 権限があるユーザ）で接続して、次のようにすることで変更できる。ここでは M とか G とかで数値を指定することはできない。

SET @@global.key_buffer_size = 77000000;

設定されたグローバル変数を見たいときは、次のようにすればよい。
なお下の例では、上で設定した値よりも MySQL の都合で小さくなっている。

mysql> SHOW GLOBAL VARIABLES LIKE "key_buffer_size";
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| key_buffer_size | 76996608 |
+-----------------+----------+

今すぐ反映させたくて、その上何かの理由でサーバーが再起動してもその値を保持して起きたい場合には、SET 文と my.cnf の編集を両方行う必要がある。

Apache などの Web サーバは、マルチスレッドで動いている。もしすべてのスレッドが MySQL にアクセスすると、コネクションが足りなくなる場合がある。そのような場合には、Apache の MaxClients などをマシな値（50 以下とか）にする。

ツール

MySQLTuner
TUNING-PRIMER

こういった、メモリの使用に対してアドバイスを与えてくれるツールがある。ただ、次のような誤解を招くメッセージを出す場合がある。

Maximum possible memory usage: 31.3G (266% of installed RAM)

怖がらなくていい。こういった数字はかなり保守的に計算して出している。max_connections で指定されたコネクションが、すべてメモリを大量に消費するようなやり方で使われていると仮定しているはずである。

Total fragmented tables: 23

こういうのもある。OPTIMIZE TABLE するとマシになるかもしれない。
SHOW TABLE STATUS をしてみて free space がかなり多い場合や、DELETE 文や UPDATE 文などを大量に走らせてしまったテーブルに対しては、これを行ってもいい。ただ、わざわざ OPTIMIZE を頻繁にやる必要はない。1 ヶ月に 1 度で十分である。

MySQL 5.7

MySQL 5.7 は、5.6 にくらべてメモリの使用量が多く、500 MB 以上多く食うかもしれない。
詳細は Memory summary tables in Performance Schema in MySQL 5.7 | Oracle Sveta's Blog を確認のこと。

Postlog

2010 年初版。
2012 年 10 月更新。
2014 年 1 月さらに更新。

ここで示した内容は、MySQL、MariaDB、Percona のどれにも適用できる。

このドキュメントは Rick James が書いたもので、彼は喜んでこの内容をナレッジベースに転載することを許してくれた。
Rick James のウェブサイトには、他にも便利な tips や、how-to や、最適化・デバッグに関する記事がある。

MariaDB Memory Allocation 日本語訳

MySQL へのメモリの割り当て方 まとめ