クラッシュした時のトラブルシューティングが楽しいタスクであったためしはありませんが、クラッシュの原因をMySQLが教えてくれない時はなおさらです。例えばそれはMySQLがメモリー不足になった時です。Peter Zaitsevが2012年にTroubleshooting MySQL Memory Usageという役立つヒントがたくさん含まれるブログ記事を書いています。MySQLの新しいバージョン(5.7以上)でperformance_schemaを使えば、もっと簡単にMySQLのメモリ割り当てのトラブルシューティングができます。
この記事では、その機能をどう使えばいいか紹介します。
まず最初に、MySQLがメモリー不足でクラッシュする3つのよくあるケースを見てみましょう。
- MySQLの設定のせいで、使用可能なメモリー以上の量をMySQLが割り当てようとしてしまうケース。例えば
innodb_buffer_pool_sizeを正しく設定しなかった時です。これは簡単に修正できます。 - RAMを使用する他のプロセスがサーバー上に存在しているケース。そのプロセスとは、アプリケーション(Java、Python、PHPなど)、Webサーバー、バックアップのプロセス(
mysqldumpなど)があり得ます。問題の原因がわかれば、修正方法は単純です。 - MySQLがメモリーリークしているケース。これは一番厄介なシナリオで、トラブルシューティングが必要です。
MySQLのメモリーリークのトラブルシューティングの始め方
始め方は以下の通り(Linuxサーバーを使用している前提です) :
その1 : Linux OSと設定のチェック
- MySQLのエラーログとLinuxのログファイル(
/var/log/messagesや/var/log/syslogなど)を確認してクラッシュを特定。OOM KillerがMySQLを停止したというログがあるかもしれません。MySQLがOOMに停止される時は、それに関する状況の詳細が常にdmesgに書かれます。 - 使用可能なRAMを確認
free -gcat /proc/meminfo
- どのアプリケーションがRAMを使用しているか :
topあるいはhtopを使いましょう(実使用メモリ(rss)と仮想メモリ(vss)を見比べてください)。 - MySQLの設定を確認 :
/etc/my.cnfあるいはより一般的に言えば/etc/my*(/etc/mysql/*なども含む)を確認。MySQLが違うmy.cnfで動いている可能性もあります(ps ax | grep mysqlを実行してみましょう)。 vmstat 5 5を実行して、システムが仮想メモリから読み込み・書き込みしているか、スワップしているかどうかを確認。- 本番環境でないなら、MySQLの使用状況を確認するのに他のツール(Valgrindやgdbなど)を使用
その2 : MySQLの内部のチェック
MySQLのメモリリークの可能性を探るため、MySQLの中のあれこれを確認しましょう。
MySQLは、あちこちにメモリ割り当てを行います。
- テーブルキャッシュ
- performance_schema(
show engine performance_schema statusを実行して、最後の行を見てください)。RAMの量が少ない(例えば1GB以下)システムでは問題になる可能性があります。 - InnoDB(
show engine innodb statusを実行してバッファープールの項、すなわちbuffer_poolへのメモリー割り当てと関連キャッシュを確認してください。 - RAM上のテンポラリーテーブル(
select * from information_schema.tables where engine='MEMORY'を実行して、インメモリーテーブル全てを見てください) - 解放されていないプリペアードステートメント(
show global status like 'Com_prepare_sql';show global status like 'Com_dealloc_sql'を実行して、deallocateコマンドを使ったプリペアードステートメントの数を確認してください)
いいニュースのお知らせ : MySQL 5.7から、performance_schemaにメモリー割り当て情報があります。使い方は以下の通り。
まずメモリー関連メトリクスの収集を有効にする必要があります。
UPDATE setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';sysスキーマからレポートを実行します。
select event_name, current_alloc, high_alloc from sys.memory_global_by_current_bytes where current_count > 0;これは、メモリーが割り当てられた時のコードの場所を示します。通常これは一目見て分かります。場合によってはバグを検索したりMySQLのソースコードを確認する必要があるかもしれません。
例えば、以下のselect文が表示するトリガーがメモリを割り当てすぎるというバグhttps://bugs.mysql.com/bug.php?id=86821だと、
mysql> select event_name, current_alloc, high_alloc from memory_global_by_current_bytes where current_count > 0;
+--------------------------------------------------------------------------------+---------------+-------------+
| event_name | current_alloc | high_alloc |
+--------------------------------------------------------------------------------+---------------+-------------+
| memory/innodb/buf_buf_pool | 7.29 GiB | 7.29 GiB |
| memory/sql/sp_head::main_mem_root | 3.21 GiB | 3.62 GiB |
...
一番大きくRAMを確保しているのは普通はバッファープールですが、3GB以上もストアドプロシジャーに割り当てられているのは多すぎるように思えます。
MySQLのソースコードドキュメントによると、sp_headとはなんらかの種類のストアドプログラム(ストアドプロシジャー、関数、トリガー、イベント)のインスタンスを表しています。上のケースだと、メモリーリークが起きている可能性があります。
さらに、俯瞰して何がメモリーを使用しているのか見たいなら、高レベルなイベントの全体的なレポートを見ることもできます。
mysql> select substring_index(
-> substring_index(event_name, '/', 2),
-> '/',
-> -1
-> ) as event_type,
-> round(sum(CURRENT_NUMBER_OF_BYTES_USED)/1024/1024, 2) as MB_CURRENTLY_USED
-> from performance_schema.memory_summary_global_by_event_name
-> group by event_type
-> having MB_CURRENTLY_USED>0;
+--------------------+-------------------+
| event_type | MB_CURRENTLY_USED |
+--------------------+-------------------+
| innodb | 0.61 |
| memory | 0.21 |
| performance_schema | 106.26 |
| sql | 0.79 |
+--------------------+-------------------+
4 rows in set (0.00 sec)
このシンプルな手順が、メモリ不足によるMySQLのクラッシュをトラブルシューティングする手助けになることを願っています。
興味を引くかもしれないリソースへのリンク
- Webinar : 同僚のSveta SmirnovaによるTrableshooting MySQL Crashes
- E-books : Peter Zaitsevと私Alexander RubinによるPractical MySQL Performance Optimization part 1、part 2、part 3
- ホワイトペーパー : A Checklist for Preventing Common but Deadly MySQL Problems
