MySQLのメモリ使用量に関するトラブルシューティングTips

January 24, 2014 By Nilnandan Joshi

「MySQLサーバのメモリ使用量」に関連するトピックスを書いたブログ記事は既にたくさんあるにも関わらず、MySQLのメモリ関連の問題のトラブルシューティングで混乱しなかった人はいないだろう。Perconaサポートのエンジニアとして、高負荷のサーバに関することや、メモリ使用量高騰でOOM killerが発動してMySQLサーバが停止したこと、あるいは「MySQLがどうしてこんなにメモリを食うのか分からない。どうやったら何にメモリが使われてるか分かるんだ？助けてくれ！」と言った問題を数多く見ている。

MySQLのメモリ使用量をチェックする方法はたくさんある。ここでは、私の知っている限りの情報をまとめて説明してみようと思う。

メモリ関連のグローバル/セッション変数をチェックする

MyISAMを使っているのなら、 key_buffer_size をチェックする必要がある。InnoDBならば、

• innodb_buffer_pool_size
• innodb_additional_memory_pool_size
• innodb_log_buffer_size
• innodb_sort_buffer_size (MySQL 5.6で導入された変数。InnoDBでインデックスを作る際のソーティングにのみ使用される)

をチェックしよう。

• max_connections
• query_cache_size
• table_cache

といった変数も重要だ。

MySQLにスレッドが作られた時は、FTSやソーティング、テンポラリテーブルの作成など特定の複雑な処理を行う時にスレッドごとのバッファが必要になる。従って、

• read_buffer_size
• sort_buffer_size
• read_rnd_buffer_size
• tmp_table_size

のサイズをチェックするのも必要だ。

ここで、実践ハイパフォーマンスSQL第3版に書かれていることが分かりやすいので引用してみよう。

MySQLのメモリ使用量は2つのカテゴリに分けられることに気づくだろう。ユーザが制御できるメモリと、制御できないメモリだ。MySQLがMySQLサーバを実行するため、クエリをパースするため、内部処理を管理するために使うメモリは制御できない。しかし、特定の処理にどのくらいのメモリを使うかといったことの多くは、制御できるようになっている。

つまり、グローバル変数かセッション変数かに関わらず、各変数の設定の目的を理解する必要があるということだ。これについてもう少し詳しくみてみよう。

key_buffer_size や query_cache_size などのグローバル変数に関しては、MySQLサーバを起動したときに1回だけ指定された容量のメモリを確保し、初期化する。ただし、グローバルなデフォルトだがセッション変数としても設定できるものについてはこの限りではない。 read_buffer_size 、 sort_buffer_size 、 join_buffer_size に関しては、クエリが必要とするまではメモリの割り当てはされない。しかし、クエリが必要としたときには、指定されたメモリ量の全てをすぐに割り当てる。つまり、小さなソートしか行わない場合でも、ほとんどメモリの無駄になってしまう完全なバッファサイズが割り当てられることになる。いくつかのバッファについては、複数回使われることもある。複数回ジョインを行うクエリを例にとってみると、 join_buffer はジョインされるテーブルごとに割り当てられる。サブクエリを含むある種の複雑なクエリについては、大量のメモリを使用することにつながることもある、複数の sort_buffers を同時に使う場合もある。いくつかの場面では、クエリは sort_buffer のサイズに関わらずそれを使わないこともある。これはプライマリキーはこのバッファを割り当てないためであり、プライマリキーのみでセレクトする場合だ。このように、環境によって色々に変わるものではあるが、通常、安全な値にしておき、必要であればデフォルト値よりも大きくしてもよい。しかし、サーバのメモリの全てを使ってしまわない程度のサイズにしておこう。

もうひとつ言っておこう。スレッドごとにメモリを割り当てるもの全てが変数で設定できるわけではない。スレッドごとのメモリ割り当てのいくつかは、ストアドプロシージャのような複雑なクエリやプロセスを実行する時にMySQLが独自に割り当てる。そしてその場合、実行中は無限にメモリを使用してしまう。また場合によっては、オプティマイザが非常に複雑なクエリを扱う際には、通常ユーザがパラメータによる制御ができない大量のメモリを必要とする。

innodb_buffer_pool_size はハードリミットではないが、通常、InnoDBはこれで設定されたメモリよりも10%多くメモリを使用する。

MyISAMとInnoDBという2つのストレージエンジンを、本番環境のサーバで同時に使うべきでないと多くの人が言っているが、これは、別々のバッファがサーバのメモリを食いあってしまうからだ。

この問題の詳細については、Peter ZaitevのMySQLサーバのメモリ使用量という記事を参照することをおすすめする。

SHOW ENGINE INNODB STATUSのBUFFER POOL AND MEMORYセクションをチェックする

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 76056
Buffer pool size 8191
Free buffers 7804
Database pages 387
Old database pages 0
Modified db pages 0

上の例は、ネイティブなMySQLのものだが、Percona Serverの場合さらに詳しい情報が見られる。

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 137756672; in additional pool allocated 0
Total memory allocated by read views 88
Internal hash tables (constant factor + variable factor)
Adaptive hash index 2217584 (2213368 + 4216)
Page hash 139112 (buffer pool 0 only)
Dictionary cache 597885 (554768 + 43117)
File system 83536 (82672 + 864)
Lock system 333248 (332872 + 376)
Recovery system 0 (0 + 0)
Dictionary memory allocated 43117
Buffer pool size 8191
Buffer pool size, bytes 134201344
Free buffers 7760
Database pages 431
Old database pages 0
Modified db pages 0

これにより、InnoDBがどのくらいの量のメモリを割り当てているのかの情報が得られる。

• Total Memory Allocated
• Internal Hash Tables
• Dictionary Memory Allocated
• Buffer Pool Size

といった項目が分かるだろう。

Valgrind MassifでMySQLのメモリ使用量を調査する

最近、Valgrind Massifを使うことで、驚くほどメモリの使用量の非常によい統計をとることができる。唯一の問題は、MySQLをシャットダウンして、Valgrind Massifを有効にして起動、さらに統計情報を取った後、またシャットダウンと起動をしなければならないことだ。

$ /etc/init.d/mysql stop
$ valgrind --tool=massif --massif-out-file=/tmp/massif.out /usr/sbin/mysqld
$ /etc/init.d/mysql restart

massif.outファイルが生成されたら、それをms_printコマンドで読む必要がある。なかなか見やすいグラフと統計情報が出る。

[root@percona1 ~]# ms_print /tmp/massif.out
--------------------------------------------------------------------------------
Command:            /usr/sbin/mysqld
Massif arguments:   --massif-out-file=/tmp/massif.out
ms_print arguments: /tmp/massif.out
--------------------------------------------------------------------------------
    MB
50.22^         ##
     |         #
     |         #
     |         #
     |         #
     |         #
     |    :    #
     |    ::   #                      ::::::@:::::::::::::@:::@::::@:::@::::
     |    : @::# :::::@@::::::::::::::::::: @::: ::: :::::@:::@::: @:::@::::
     |    : @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::
     |    : @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::
     |    : @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::
     |    : @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::@
     |    : @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::@
     |   :: @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::@
     |  ::: @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::@
     | :::: @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::@:
     | :::: @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::@:
     | :::: @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::@:
     | :::: @::# :  ::@ :::::::::: :: ::::: @::: ::: :::::@:::@::: @:::@::::@:
   0 +----------------------------------------------------------------------->Mi
     0                                                                   575.9
Number of snapshots: 96
 Detailed snapshots: [1, 7, 11 (peak), 16, 35, 48, 58, 68, 78, 88]
--------------------------------------------------------------------------------
  n        time(i)         total(B)   useful-heap(B) extra-heap(B)    stacks(B)
--------------------------------------------------------------------------------
  0              0                0                0             0            0
  1      6,090,089          195,648          194,590         1,058            0
99.46% (194,590B) (heap allocation functions) malloc/new/new[], --alloc-fns, etc.
->48.79% (95,458B) 0x7A1D20: my_malloc (my_malloc.c:38)
| ->25.08% (49,060B) 0x6594F1: read_texts(char const*, char const*, char const***, unsigned int) (derror.cc:160)
| | ->25.08% (49,060B) 0x6597C2: init_errmessage() (derror.cc:69)
| |   ->25.08% (49,060B) 0x506232: init_common_variables() (mysqld.cc:3414)
| |     ->25.08% (49,060B) 0x508CBB: mysqld_main(int, char**) (mysqld.cc:4461)
| |       ->25.08% (49,060B) 0x5B2CD1B: (below main) (in /lib64/libc-2.12.so)
| |
| ->09.36% (18,317B) 0x789571: my_read_charset_file (charset.c:364)
| | ->09.36% (18,317B) 0x789DEC: init_available_charsets (charset.c:458)
| |   ->09.36% (18,317B) 0x4E35D31: pthread_once (in /lib64/libpthread-2.12.so)
| |     ->09.36% (18,317B) 0x789C80: get_charset_by_csname (charset.c:644)
| |       ->09.36% (18,317B) 0x5062E9: init_common_variables() (mysqld.cc:3439)
| |         ->09.36% (18,317B) 0x508CBB: mysqld_main(int, char**) (mysqld.cc:4461)
| |           ->09.36% (18,317B) 0x5B2CD1B: (below main) (in /lib64/libc-2.12.so)
| |
| ->08.37% (16,384B) 0x79DEEF: my_set_max_open_files (my_file.c:105)
| | ->08.37% (16,384B) 0x506169: init_common_variables() (mysqld.cc:3373)
| |   ->08.37% (16,384B) 0x508CBB: mysqld_main(int, char**) (mysqld.cc:4461)
| |     ->08.37% (16,384B) 0x5B2CD1B: (below main) (in /lib64/libc-2.12.so)
| |
| ->04.36% (8,536B) 0x788DB4: init_dynamic_array2 (array.c:70)
| | ->02.45% (4,800B) 0x5CD51A: add_status_vars(st_mysql_show_var*) (sql_show.cc:2062)
| | | ->02.45% (4,800B) 0x505E68: init_common_variables() (mysqld.cc:3245)
| | |   ->02.45% (4,800B) 0x508CBB: mysqld_main(int, char**) (mysqld.cc:4461)
| | |     ->02.45% (4,800B) 0x5B2CD1B: (below main) (in /lib64/libc-2.12.so)
| | |

関数など、メモリの割り当てている部分の出力を見ればよい。このツールは、メモリリークしている箇所を発券するのにも使える。インストール方法や使い方の情報は、Roel Van de Paarの、MySQLメモリ使用量をValgrind Massifで調査するがある。

注意 プロファイル中はパフォーマンスが低下するので、本番環境の高負荷なサーバではValgrind Massifは可能な限り使うべきでない。一般的にこのツールは、テスト環境やステージング環境でミラー環境を作り、そこで実行するものである。パフォーマンスの劣化が激しいのは、デバッグバイナリを必要とするためだ。従って、調査目的に使うもので、通常用途ではない。

psコマンドの出力をモニタリングする

MySQLがどのくらいの仮想メモリ(VSZ)と実メモリ(RSS)を使っているかを知るには良い方法だ。以下のような簡単なbashスクリプトを実行すればよい。

while true
do
  date >> ps.log
  ps aux | grep mysqld >> ps.log
  sleep 60
done

あるいは必要な時に「ps aux | grep mysqld」コマンドを実行してもよい。

MySQL 5.7のメモリテーブルを見る

MySQL 5.7では、performance_schemaに、メモリ使用量を確認するための興味深いメモリ統計情報テーブルが導入された。まだ詳しいドキュメントは提供されていないが、ある程度の情報はこちらにある。

performance_schema内には、メモリの概要に関するテーブルが5つある。

mysql> show tables like '%memory%';
+-----------------------------------------+
| Tables_in_performance_schema (%memory%) |
+-----------------------------------------+
| memory_summary_by_account_by_event_name |
| memory_summary_by_host_by_event_name |
| memory_summary_by_thread_by_event_name |
| memory_summary_by_user_by_event_name |
| memory_summary_global_by_event_name |
+-----------------------------------------+
5 rows in set (0.00 sec)

各イベントに対して、アカウントごと、ホストごと、スレッドごと、ユーザごとにメモリ使用量の概要が見られるわけだ。さらに詳しく見たい場合、209あまりのイベントがチェックできる。ここでは、join buffer sizeに関するあるイベントをチェックしてみたい。

mysql> select * from memory_summary_by_account_by_event_name where SUM_NUMBER_OF_BYTES_ALLOC <> 0 and user = 'msandbox' and event_name = 'memory/sql/JOIN_CACHE' \G
*************************** 1. row ***************************
USER: msandbox
HOST: localhost
EVENT_NAME: memory/sql/JOIN_CACHE
COUNT_ALLOC: 2
COUNT_FREE: 2
SUM_NUMBER_OF_BYTES_ALLOC: 524288
SUM_NUMBER_OF_BYTES_FREE: 524288
LOW_COUNT_USED: 0
CURRENT_COUNT_USED: 0
HIGH_COUNT_USED: 1
LOW_NUMBER_OF_BYTES_USED: 0
CURRENT_NUMBER_OF_BYTES_USED: 0
HIGH_NUMBER_OF_BYTES_USED: 262144
1 row in set (0.00 sec)
mysql> show global variables like 'join%';
+------------------+--------+
| Variable_name | Value |
+------------------+--------+
| join_buffer_size | 262144 |
+------------------+--------+
1 row in set (0.00 sec)
mysql>

• COUNT_ALLOCとCOUNT_FREE malloc系関数、free系関数のコールされた回数の合計
• SUM_NUMBER_OF_BYTES_ALLOCとSUM_NUMBER_OF_BYTES_FREE 割り当てされたメモリブロック、解放されたメモリブロックのサイズの合計
• CURRENT_COUNT_USED 解放されておらず今も割り当てられているブロック数の合計
• CURRENT_NUMBER_OF_BYTES_USED 解放されておらず今も割り当てられているブロックのサイズの合計

LOW_とHIGH_があるものについては、それぞれの最小値と最大値を表している。

pt-summaryとpt-mysql-summaryのメモリセクション

もしこのスクリプト群に気付いているなら、MySQLに関連する全般的なメモリ使用量の非常によい概要が見られる。

pt-summaryの出力例。

# Memory #####################################################
Total | 11.8G
Free | 143.7M
Used | physical = 11.6G, swap allocated = 4.0G, swap used = 0.0, virtual = 11.6G
Buffers | 224.9M
Caches | 6.2G
Dirty | 164 kB
UsedRSS | 4.8G

pt-mysql-summaryの出力例。

# Query cache ################################################
query_cache_type | OFF
Size | 0.0
Usage | 0%
HitToInsertRatio | 0%
# InnoDB #####################################################
Version | 5.5.30-rel30.2
Buffer Pool Size | 4.0G
Buffer Pool Fill | 35%
Buffer Pool Dirty | 1%
# MyISAM #####################################################
Key Cache | 32.0M
Pct Used | 20%
Unflushed | 0%

まとめ

MySQLがどこでメモリの確保をして、それがどのようにMySQLの負荷やパフォーマンスに影響を与えるのかを知るのは、非常に重要なことだ。ここではいくつかの方法について書いてみたが、これらの情報をまとめ、MySQLのメモリ使用量に関する本当の情報を教えてくれるスクリプトやその他のツールを作る必要があるのではないかと考えている。