この記事では、最適なMySQLのメモリー使用量を設定するためのベストプラクティスを扱おうと思います。
使用できるメモリーのリソースをどのように使うか正しく設定するのは、MySQLを最適なパフォーマンスでかつ安定して使うために最も重要なことのひとつです。MySQL 5.7では、デフォルトの設定では非常に少ない量のメモリしか使いません。デフォルトのままにしておくのは、最も良くないことのひとつでしょう。しかし、不適切に設定してしまうと、パフォーマンスを更に悪くする(あるいはクラッシュする)ことにもなりかねません。
MySQLのメモリ使用量を設定するにあたっての最初のルールは、オペレーティングシステムがスワップするようにMySQLを設定しないということです。ほんの少しのスワップ動作でも、MySQLのパフォーマンスを劇的に悪化させてしまいます。ここでの「動作」という言葉に注意しましょう。スワップファイルが空でない場合でも、MySQLが動作している時に使用していないOSの一部がそこに置かれているのなら問題はなく、むしろスワップしておくのは良いことでもあります。やってはいけないのは、処理の間に継続的にスワップが行われることです。これは、vmstatの出力のsiとso列で簡単に見られます。
スワップしていない時の例 :
激しくスワップしている時の例 :
Percona Monitoring and Managementを使っているなら、System Overview DashboardのSwap Activityグラフでも確認できます。
1MB/sec以上のスパイクがあるか、継続してスワップ動作が行われているなら、メモリー設定を見直す必要があると言えるでしょう。
MySQLのメモリー割当は複雑です。グローバルバッファー、コネクションごとのバッファー(負荷に依存)の他に制御できないメモリーの割当(例えばストアドプロシージャーなど)もあり、実際の負荷に対してMySQLがどのくらいのメモリを使うのかを計算するのを難しくしています。MySQLが使う仮想メモリのサイズ(VSZ)を確認してそれをチェックするのがよいでしょう。この値は、topコマンドか、ps aux | grep mysqlコマンドで得られます。
mysql 3939 30.3 53.4 11635184 8748364 ? Sl Apr08 9106:41 /usr/sbin/mysqld
5列目がVSZの使用量(約11GB)を示しています。
VSZは時間とともに変わりやすい点に注意しましょう。監視システムでグラフにしたり、指定の閾値を超えたらアラートを飛ばすように設定しておくのはいいアイディアです。MySQLプロセスのVSZがシステムメモリの90%を超えないようにしましょう(システム上でMySQL以外も動かしている場合はもっと小さくしましょう)。
グローバルバッファーやコネクションごとのバッファーは保守的に設定して安全側に振った状態で起動し、後で変更していくのが良いでしょう。MySQL 5.7でのinnodb_buffer_pool_sizeを含め、オンラインで変更できるものが多いです。
では、MySQLとそれ以外に対してどのくらいずつメモリを割り当てるかどのように判断すればよいでしょうか？多くの場合、物理メモリの90%をそのままMySQLに割り当てるべきではなく、OSが使用する分やバイナリログ、一時ソートファイルなどのキャッシュのためにも残しておく必要があります。
MySQLへの割り当てをメモリーの90%よりかなり少なくする必要があるのは以下のようなケースです。
- 常時か定期的かによらず、同じサーバー上で他の重要なプロセスが動く場合。cronで起動されるメモリを大量に使う重いバッチがあるなら、それについても考える必要があるでしょう。
- なんらかのストレージエンジンでOSキャッシュを使いたい場合。InnoDBでは、多くの場合OSのファイルキャッシュを使わない
innodb_flush_method=O_DIRECTを推奨していますが、InnoDBでもバッファーありのIOを使うのが理に適っている場合もあるでしょう。MyISAMをまだ使っているなら、テーブルの「データ」部分についてはOSキャッシュが必要になります。TokuDBでは、一定のワークロードではOSキャッシュを使った方が良い場合があります。 - ワークロード的に重要なものをキャッシュしたい場合。例えばMyISAMのデータ、ディスク上の一時ファイル、ソートファイル、その他MySQLが作る一時ファイルなどは、パフォーマンス最適化のためにはしっかりとキャッシュされている必要があります。
MySQLプロセスに必要なメモリーがどのくらいなのかを全体的に判断するには、MySQL内部でメモリーがどのような目的で使われるのかを知る必要があります。メモリーの使用量の一番の部分はワークロードに関係します。ソートや一時ファイルに多くのメモリーを使う重いselectを行うアクティブなコネクションが同時に多数作られるなら、メモリーはたくさん必要になるでしょう(パフォーマンス・スキーマが有効な時は特に)。そうでないならメモリーは最低限でよくなります。この場合は一般的に1GBから10GBの間ぐらいのメモリーが必要でしょう。
もうひとつ考えなくてはならないのが、メモリーのフラグメンテーションです。使用しているメモリー割り当てライブラリー(glibc、TCMalloc、jemallocなど)によりますが、Transparent Huge Pages(THP)などのOS設定やワークロードで、時間の経過とともにメモリーの使用量が(ある一定の値に達するまで)増えていくでしょう。メモリーフラグメンテーションは、さらに10%あるいはそれ以上に余計なメモリーを必要とします。
最後に、色々なグローバルバッファーとキャッシュについて考えましょう。一般的なケースでは、innodb_buffer_pool_sizeだけ気にしておけばよいでしょう。しかし、key_buffer_size、 tokudb_cache_size、 query_cache_size、 table_cache、 table_open_cacheについても考える必要がある場合もあります。バイト単位で考えるものではないにしても、グローバルメモリーの割り当てに影響してくる設定です。パフォーマンススキーマも同じく多くのメモリを使いますが、特にコネクションやテーブルが多い場合は注意しましょう。
バッファーやキャッシュのサイズを設定する時は、何を設定しているのか理解しましょう。innodb_buffer_pool_sizeなら、追加のデータ構造に使うために5から10%余計に割り当てる必要があることに注意します。この数字は、圧縮していたりinnodb_page_sizeが16Kよりも小さい場合には大きくする必要があります。tokudb_cache_sizeで言えば、この値は目安であって「厳密な」制限ではないのを覚えておかなくてはなりません。キャッシュサイズは指定した値よりも少し多くまで増えます。
メモリーがたくさん積まれているシステムでは、データベースキャッシュが圧倒的に一番メモリーを使うものになるでしょうし、ほとんどのメモリーをそこに割り当てることになるでしょう。システムにメモリーを追加する際は、データベースのキャッシュサイズも増やすのが一般的になります。
ある例での計算をしてみましょう。16GBのメモリーを持つシステム(物理か仮想かは問いません)があると考えましょう。MySQLをシステム上で動かしていて、InnoDBをストレージエンジンに使い、innodb_flush_method=O_DIRECTにしているので、MySQLにはメモリーの90%(14.4GB)を割り当てられます。ここで考えるワークロードでは、コネクションの取り扱いやその他のMySQLのコネクションごとのオーバーヘッドを1GBと見積もり(残り13.4GB)、その他のグローバルバッファー(innodb_log_buffer_size、テーブルキャッシュ、その他)に0.4GBを使うとして、13GBが残ります。InnoDBバッファープールのオーバーヘッドを5から7%と考えて、innodb_buffer_pool_size=12Gとなります。これは、16GBメモリーのシステムでよく見るたいてい問題なく動く値です。
これでMySQLのメモリーの使用量は設定できましたが、OSの設定も見る必要があります。まず考えるべきは、MySQLにスワップを使って欲しくない時、スワップファイルを有効にするべきかどうかということです。多くの場合、この答えはイエスです。2つの理由からスワップファイルを有効にした方がいいと思うでしょう(最低でも4GB、かつ搭載メモリの25%を下回らないようにすべきです)。
- データベースサーバーとして運用していると、OSは使用していないメモリーを確保することがよくあります。そういったものは、無理にメモリーに置かずにスワップしてしまう方がよいでしょう。
- MySQLの設定を間違ったり、予期せぬメモリー量を行儀の悪いプロセスが使ってしまうような場合、パフォーマンスを犠牲にしてでもスワップした方が、out of memory(OOM)エラーでMySQLを殺す(ダウンタイムが発生するでしょう)よりもよい場合があります。
メモリーが足りない時やアイドル中のプロセスがスワップする時ような緊急時にだけスワップファイルを使って欲しいので、OSのスワップしやすさの設定値を小さくした方がよいでしょう(echo 1 > /proc/sys/vm/swappiness)。この設定をしないと、ファイルキャッシュを増やしたい理由だけでMySQLの使っているメモリーをスワップしようとする(これはMySQLにとって常によくないことです)のが分かるでしょう。
もうひとつOSの設定でやっておきたいのは、Out Of Memoryキラー(OOMキラー)です。カーネルログにこんなメッセージがあるのを見たことがあるでしょう。
Apr 24 02:43:18 db01 kernel: Out of memory: Kill process 22211 (mysqld) score 986 or sacrifice child
MySQL自体が問題なら、こういったこともあり得るでしょう。しかし、スクリプトを実行したりバックアップを実行したりといったバッチ的な処理が本当の問題であることもあります。この場合、システムに十分なメモリがない時でも、MySQLではなくそういったプロセスの方を停止して欲しいと思うでしょう。
MySQLをなるべくOOMキラーに殺されないようにするには、以下のようにしてMySQLが選ばれないように振る舞いを変更すべきでしょう。
echo '-800' > /proc/$(pidof mysqld)/oom_score_adj
これにより、LinuxカーネルはMySQL以外にたくさんメモリを使っているプログラムから停止していくようになります。
最後に、1つ以上のCPUソケットを持っているシステムでは、MySQLのメモリー割り当てにも影響があるNUMAにも注意すべきです。新しいバージョンのMySQLでは、innodb_numa_interleave=1を設定しましょう。古いバージョンでは、MySQLサーバーを起動する前に手動でnumactl --interleave=allを実行するか、Percona Serverのnuma_interleaveオプションを使いましょう。
