MySQLのメモリ設定の勘所

MySQLサーバーをダウンさせた夜は数知れず。
その度にmy.cnfの設定を見なおしてみてはトライし、治ったと思いきや突然のダウン。

サーバーがダウンしてしまう原因は何かと聞かれれば、「メモリです」と断言しましょう。
メモリ設定は諸刃の剣。
パフォーマンスを最大に引き出すこともできればそれと引き換えにサーバーをダウンさせてしまうこともできるんです。

今回はMySQLのメモリの設定の勘所というかたちで紹介しようと思います。

グローバルバッファとスレッドバッファ

メモリの設定についてまず「グローバルバッファ」と「スレッドバッファ」について理解しておくことが大事です。バッファとは一時的な記憶領域・つまりはメモリの領域のことなのですが。

グローバルバッファ mysqlデーモン全体で一つだけ確保されるバッファ
スレッドバッファ mysqlのスレッド(コネクション)単位で確保されるバッファ

MySQLで使用する全体的なメモリ使用量を計算するには

グローバルバッファ + (スレッドバッファ × コネクション数) = メモリ使用量

という計算式に当てはめればいいことになります。この計算結果がシステムの搭載メモリ量を超えるうような設定だと、そのサーバーは運用を続けるとおそらく落ちます。

コネクション数について

メモリの使用量の計算方法の概要は分かったとして、実際のコネクション数はどれぐらいになるのでしょうか。
クローズドなシステムの場合だとある程度想定できるかもしれませんが、オープンなWEBアプリケーションなどの場合は正確な数字の想定は難しいという場合がほとんどでしょう。

運用に乗っかっているものであれば、現在のコネクション数は”SHOW STATUS”コマンドで知ることができます。具体的には以下の様にします。

mysql> SHOW STATUS LIKE '%connect%';
+-----------------------------------------------+-------+
| Variable_name                                 | Value |
+-----------------------------------------------+-------+
| Aborted_connects                              | 0     |
| Connection_errors_accept                      | 0     |
| Connection_errors_internal                    | 0     |
| Connection_errors_max_connections             | 0     |
| Connection_errors_peer_address                | 0     |
| Connection_errors_select                      | 0     |
| Connection_errors_tcpwrap                     | 0     |
| Connections                                   | 2     |
| Max_used_connections                          | 1     |
| Performance_schema_session_connect_attrs_lost | 0     |
| Ssl_client_connects                           | 0     |
| Ssl_connect_renegotiates                      | 0     |
| Ssl_finished_connects                         | 0     |
| Threads_connected                             | 1     |
+-----------------------------------------------+-------+
14 rows in set (0.01 sec)

「Connections」はmysqldを起動してから(STATUSをリセットしてから)、現在までの接続数が表示されます。
「Max_used_connections」はmysqldを起動してから(STATUSをリセットしてから)、現在までの最大同時接続数が表示されます。
「Threads_connected」は現在のコネクション数が表示されます。

実際の運用サーバーだと大きな数が表示されているかと思いますが、メモリ計算の際には「Max_used_connections」を参考にしましょう。最大同時接続数を参考にすることで最大メモリ使用量の目安になります。

また、この最大同時接続数は「max_connections」で制限することができます。

mysql> SHOW GLOBAL VARIABLES LIKE 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 151   |
+-----------------+-------+
1 row in set (0.00 sec)

「Max_used_connections」の値が「max_connections」+ 1になっていればmysqlサーバーへのコネクションが上限に達して制限されているということになります。
上限に達した時、クライアント側では”Too many connections”エラーが発生します。

以下の様に「max_connections」数を増やして様子を見ることもできますが、この場合コネクション数増加によるメモリ不足でサーバーがダウンしてしまう可能性がありますので、変更の際は注意してください。

mysql> SET GLOBAL max_connections = 200;
Query OK, 0 rows affected (0.01 sec)

※ 実際これが原因でダウンしたことが有りました。”Too many connections”エラーが発生しているからと言って安易に「max_connections」数を増やしてはいけません。

「max_connections」はmy.cnfに設定しておくこともできます。

前提条件

一応の前提条件ですが、メモリ8GB搭載マシンでの設定を想定しております。

グローバルバッファ InnoDB編

MySQL5.5以降デフォルトストレージエンジンとなったInnoDB。InnoDBのグローバルバッファを制するものがMySQLを制すると言っても過言では無いくらい(個人的主観)。

■ innodb_additional_mem_pool_size

InnoDBのデータ・ディクショナリーや内部データ構造情報を持つバッファのサイズ。基本的に多くても20MBくらいでいいらしい。

innodb_additional_mem_pool_size = 20M

■ innodb_buffer_pool_size

InnoDBのデータとインデックスをキャッシュするバッファのサイズ。
一説にはサーバーのメモリ搭載量の80%という話ですが、ここは一応50%にしておきます。

innodb_buffer_pool_size = 4G

■ innodb_log_buffer_size

ログファイルのためのバッファのサイズ。
通常ログファイルにはCOMMIT時に書き込まれるので、あまり厚く設定する必要はありません。多くても64MBでいいらしいのですが、グローバルバッファですし、64MBそのまま割り当てます。

innodb_log_buffer_size = 64MB

グローバルバッファ MyISAM編

■ key_buffer_size

MySAMのキーをキャッシュするバッファのサイズ。目安としてはシステムメモリの25%とのことらしいですが、Key_reads/Key_read_requestsの割合が1%よりも小さくなるように調整できていれば問題ありません。

mysql> SHOW STATUS LIKE "Key_read%";
+-------------------+-------+
| Variable_name     | Value |
+-------------------+-------+
| Key_read_requests | 93485 |
| Key_reads         | 262   |
+-------------------+-------+
2 rows in set (0.00 sec)

Key_read_requests・・・キャッシュへのキーブロック読み取り要求回数

Key_reads・・・ディスクへのキーブロック読み取り要求回数

上記の場合だと262 / 93485 = 0.0028・・・となりますので、ほぼ問題ありません。
MyISAMはほとんど使用していないのですが、グローバルバッファですし、mysqlスキーマはMyISAM使ってますし、大きめに割り当てておいて問題は無いでしょう。

key_buffer_size = 256M
mysql> SET GLOBAL key_buffer_size = 256 * 1024 * 1024;
Query OK, 0 rows affected (0.04 sec)

スレッドバッファ

ストレージエンジン共通のスレッドバッファです。スレッド(コネクション)毎に消費されるので、あまり大きな値を設定しないように注意しましょう。

■ join_buffer_size

インデックスが使用されていないカラムで内部結合するときにしようされるバッファ。インデックスが張られていないカラムでJOINすること自体がなんだかなぁ、という気がしますのでこちらの値は少なめに設定します。

join_buffer_size = 256K
mysql> SET GLOBAL join_buffer_size = 256 * 1024;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SESSION join_buffer_size = 256 * 1024;
Query OK, 0 rows affected (0.00 sec)

もしjoin_buffer_sizeの割り当て容量を増やしたらパフォーマンスが改善したという場合は、テーブル設計かアプリケーション(から発行されるクエリ)を見なおしたほうがいいかと思います。

■ max_allowed_packet

クライアントからサーバーに送信できるパケットの最大長。デフォルトは1MBですが、少し大きなクエリを発行してしまうとエラーになってしまいますので、ちょっと大きめに設定します。

max_allowed_packet = 8M
mysql> SET GLOBAL max_allowed_packet = 8 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SESSION max_allowed_packet = 8 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

■ read_buffer_size

全件検索時のレコードがキャッシュされるバッファのサイズです。こちらの値もインデックスを使用しないスキャン時に使用されますので、このパラメーターを変更してパフォーマンスが改善したという場合はテーブル設計もしくはアプリケーションを見直しましょう。
あまり大きな値の設定は必要ないでしょう。

read_buffer_size = 256K
mysql> SET GLOBAL read_buffer_size = 256 * 1024;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SESSION mread_buffer_size = 256 * 1024;
Query OK, 0 rows affected (0.00 sec)

■ read_rnd_buffer_size

キーを使用したソートで読み込まれた行がキャッシュされるバッファのサイズ。こちらはソート処理のパフォーマンス向上が期待できるため、他のスレッドバッファの設定より多めに割り当てます。

read_rnd_buffer_size = 2M
mysql> SET GLOBAL read_rnd_buffer_size = 2 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SESSION read_rnd_buffer_size = 2 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

■ sort_buffer_size

ソート時に使用されるバッファのサイズ。こちらもソート処理のパフォーマンス向上が期待できるため、他のスレッドバッファの設定より多めに割り当てます。

sort_buffer_size = 4M
mysql> SET GLOBAL sort_buffer_size = 4 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SESSION sort_buffer_size = 4 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

クエリキャッシュ

クエリキャッシュとはクエリの結果をメモリ上にキャッシュさせ、同じクエリが発行された際はキャッシュ上のデータを取得します。
ディスクI/Oを軽減させ、パフォーマンスアップを実現します。

■ query_cache_limit

ここで設定された値より大きな結果はキャッシュしないようにします。

query_cache_limit = 16M
mysql> SET GLOBAL query_cache_limit = 16 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

■ query_cache_size

クエリキャッシュで使用するメモリサイズ。グローバルバッファなので多めに割り当てても問題無いでしょう。

query_cache_size = 512M
mysql> SET GLOBAL query_cache_size = 512 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

■ query_cache_type

クエリキャッシュのタイプを設定します。
0・・・OFF クエリキャッシュを行いません。
1・・・ON SELECT SQL_NO_CACHE以外のクエリをキャッシュします。
2・・・DEMAND SELECT SQL_CACHEのみキャッシュします。

query_cache_type = 1

その他のパラメータ

上記までに分類できなかったけど重要なその他のパラメーターです。

■ max_connections

最大コネクション数を設定します。設定したコネクション数+1に達するとクライアント側では”Too many connections”となります。
デフォルトは100です。
相当大規模なシステムでもない限りこちらの値はそのままで大丈夫でしょう。

max_connections = 100
mysql> SET GLOBAL max_connections = 100;
Query OK, 0 rows affected (0.00 sec)

max_connectionに達した場合はサービスが成功してアクセスが増えたことよりも、スロークエリが発生してコネクションがたまってしまったことを疑うべきでしょう。大体の場合はそうです。

■ max_heap_table_size

MEMORYテーブルの最大サイズを指定する。このサイズを超えたMEMORYテーブルはディスク上に作成されます。

max_heap_table_size = 16M
mysql> SET GLOBAL max_heap_table_size = 16 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SESSION max_heap_table_size = 16 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

■ tmp_table_size

スレッド毎に作成される一時的なテーブルの最大サイズ。スレッドバッファ。
一時テーブルはmax_heap_table_sizeの値によっても制限されるため、両方に同じ値を設定しておくのが定石です。

tmp_table_size = 16M
mysql> SET GLOBAL tmp_table_size = 16 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)
mysql> SET SESSION tmp_table_size = 16 * 1024 * 1024;
Query OK, 0 rows affected (0.00 sec)

まとめ

# グローバルバッファ InnoDB編
innodb_additional_mem_pool_size = 20M
innodb_buffer_pool_size = 4G
innodb_log_buffer_size = 64MB

# グローバルバッファ MyISAM編
key_buffer_size = 256M

# スレッドバッファ
join_buffer_size = 256K
max_allowed_packet = 8M
read_buffer_size = 256K
read_rnd_buffer_size = 2M
sort_buffer_size = 4M

# クエリキャッシュ
query_cache_limit = 16M
query_cache_size = 512M #グローバルバッファ
query_cache_type = 1

# その他
max_connections = 100
max_heap_table_size = 16M
tmp_table_size = 16M #スレッドバッファ

というような形になります。これを最初の計算式に当てはめると、

・グローバルバッファ
innodb_additional_mem_pool_size : 20M + 
innodb_buffer_pool_size         : 4G +
innodb_log_buffer_size          : 64MB + 
key_buffer_size                 : 256M +
query_cache_size                : 512M
----------------------------------------
                                : 4098MB

・スレッドバッファ
join_buffer_size     : 256K
max_allowed_packet   : 8M
read_buffer_size     : 256K
read_rnd_buffer_size : 2M
sort_buffer_size     : 4M
tmp_table_size       : 16M
----------------------------------------
                     : 30.5MB

# グローバルバッファ + (スレッドバッファ × コネクション数) = メモリ使用量
4098MB + (30.5MB × 100) = 7148MB

と、なんとか前提条件の8GBに収まる形になりました。
実際にはmax_connectionの100に達することはほぼ無いと思いますので(アプリケーションが問題なければ)、max_connectionの値を減らして、バッファの値を増やすのもパフォーマンス向上には有効な手段です。

まあ、実際に運用に乗っかってしまった場合、状況の変化によってSHOW STATUSコマンドなどを使用していろいろ調整する必要が出てくるかとおもいますので、その辺りの話はまた別途まとめられたらと思います。

コメントを残す