Yakstは、海外の役立つブログ記事などを人力で翻訳して公開するプロジェクトです。
6年以上前投稿

MySQLがメモリー不足の時に何をするか : トラブルシューティングガイド

MySQLがメモリー不足で停止してしまった(OOM Killerに停止させられた)時に確認すべき項目を紹介する。特に、MySQLのバグでメモリリークが起きている可能性がある場合に手がかりを得る方法について。

原文
What To Do When MySQL Runs Out of Memory: Troubleshooting Guide - Percona Database Performance Blog (English)
原文著者
Alexander Rubin
原文公開日
2018-06-28
翻訳依頼者
D98ee74ffe0fafbdc83b23907dda3665
翻訳者
D98ee74ffe0fafbdc83b23907dda3665 doublemarket
翻訳レビュアー
C17de91fbad737faaa06173533ed323d kakuka4430
原著者への翻訳報告
2350日前 原文へのコメントで報告済み 編集


クラッシュした時のトラブルシューティングが楽しいタスクであったためしはありませんが、クラッシュの原因をMySQLが教えてくれない時はなおさらです。例えばそれはMySQLがメモリー不足になった時です。Peter Zaitsevが2012年にTroubleshooting MySQL Memory Usageという役立つヒントがたくさん含まれるブログ記事を書いています。MySQLの新しいバージョン(5.7以上)でperformance_schemaを使えば、もっと簡単にMySQLのメモリ割り当てのトラブルシューティングができます。

この記事では、その機能をどう使えばいいか紹介します。

まず最初に、MySQLがメモリー不足でクラッシュする3つのよくあるケースを見てみましょう。

  1. MySQLの設定のせいで、使用可能なメモリー以上の量をMySQLが割り当てようとしてしまうケース。例えばinnodb_buffer_pool_sizeを正しく設定しなかった時です。これは簡単に修正できます。
  2. RAMを使用する他のプロセスがサーバー上に存在しているケース。そのプロセスとは、アプリケーション(Java、Python、PHPなど)、Webサーバー、バックアップのプロセス(mysqldumpなど)があり得ます。問題の原因がわかれば、修正方法は単純です。
  3. MySQLがメモリーリークしているケース。これは一番厄介なシナリオで、トラブルシューティングが必要です。

MySQLのメモリーリークのトラブルシューティングの始め方

始め方は以下の通り(Linuxサーバーを使用している前提です) :

その1 : Linux OSと設定のチェック

  1. MySQLのエラーログとLinuxのログファイル(/var/log/messages/var/log/syslogなど)を確認してクラッシュを特定。OOM KillerがMySQLを停止したというログがあるかもしれません。MySQLがOOMに停止される時は、それに関する状況の詳細が常にdmesgに書かれます。
  2. 使用可能なRAMを確認
    • free -g
    • cat /proc/meminfo
  3. どのアプリケーションがRAMを使用しているか : topあるいはhtopを使いましょう(実使用メモリ(rss)と仮想メモリ(vss)を見比べてください)。
  4. MySQLの設定を確認 : /etc/my.cnfあるいはより一般的に言えば/etc/my*(/etc/mysql/*なども含む)を確認。MySQLが違うmy.cnfで動いている可能性もあります(ps ax | grep mysqlを実行してみましょう)。
  5. vmstat 5 5を実行して、システムが仮想メモリから読み込み・書き込みしているか、スワップしているかどうかを確認。
  6. 本番環境でないなら、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にメモリー割り当て情報があります。使い方は以下の通り。

  1. まずメモリー関連メトリクスの収集を有効にする必要があります。

    UPDATE setup_instruments SET ENABLED = 'YES'
    WHERE NAME LIKE 'memory/%';
    
  2. sysスキーマからレポートを実行します。

    select event_name, current_alloc, high_alloc
    from sys.memory_global_by_current_bytes
    where current_count > 0;
    
  3. これは、メモリーが割り当てられた時のコードの場所を示します。通常これは一目見て分かります。場合によってはバグを検索したり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のクラッシュをトラブルシューティングする手助けになることを願っています。

興味を引くかもしれないリソースへのリンク

次の記事
MySQLのストアドプロシージャ、ファンクション、トリガーのパフォーマンスが悪いのはなぜか
前の記事
MySQL5.7からMySQL8.0へのインプレースアップグレード

Feed small 記事フィード

新着記事Twitterアカウント