MySQL のメモリに関する問題のトラブルシューティング

メモリ不足（OOM）状態は、プロセスがメモリを割り当てようとし、サーバーに使用可能なメモリがないために失敗した場合に発生します。その結果、多くの場合、Linux OOM キラーが発生します。これは、システムのメモリが非常に少ない場合に Linux カーネルが使用するプロセスであり、データベース プロセスが終了します。

OOM 状態によるデータベース クラッシュは理想的ではありません。データベース プロセスが SIGKILL ディレクティブを使用して終了されるため、アプリケーションに通常の MySQL がシャットダウンする可能性はないためです。これにより、次の問題が発生します。

• MySQL サーバーがダウンし（障害復旧が完了するまでには数分かかる場合があります）、ユーザーとビジネスに影響する
• MySQL サーバーでバッファとキャッシュが完全にウォームアップされ、ピーク パフォーマンスに達するまでに時間がかかる
• 予期しないクラッシュによるデータの破損の可能性

MySQL のメモリ割り当ての把握に役立つ 5.7 バージョン以降、MySQL の performance_schema にメモリ計測機能が追加されました。この記事では、performance_schema を使用する MySQL のメモリに関する問題のトラブルシューティングについて説明します。

各 Cloud SQL インスタンスは、ホストである Google Cloud サーバー上で実行されている仮想マシン（VM）を利用しています。各 VM は、MySQL サーバーなどのデータベース プログラムと、ロギングやモニタリングなどのサポート サービスを提供するサービス エージェントを実行します。Cloud SQL は、メモリの一部をオペレーティング システム（OS）とサービス エージェント用に予約し、残りのメモリを mysqld などのデータベース プログラムに割り当てます。

次の表に、mysqld プロセスのメモリ割り当てと、各マシンタイプのデフォルトの InnoDB バッファプール構成を示します。

RAM が 10 GB 以上のインスタンスの場合、mysqld プロセスはインスタンス RAM の 92% を使用するように制限され、InnoDB バッファプールは mysqld プロセスで使用可能なメモリの 75～80% の範囲に設定されます。InnoDB バッファプールに加えて、MySQL は、パフォーマンスを向上させるために複数のバッファとキャッシュを割り当てます。MySQL でのメモリ割り当てについては、こちらの記事をご覧ください。

mysqld のメモリ使用量が mysqld プロセスに設定された上限を超えると、インスタンスで最もメモリ消費量の多い mysqld プロセスが Linux OOM キラーによって強制終了されます。

performance_schema は、サーバーの実行を低レベルでモニタリングするための MySQL の機能です。Cloud SQL for MySQL では、RAM サイズが 3 GB 以上のインスタンスで performance_schema を有効にできます。MySQL 8.0 バージョン以降の場合、15GB 以上の RAM を持つインスタンスでデフォルトで有効になっています。

performance_schema は、MySQL 5.6 および 5.7 バージョンではデフォルトで無効になっていますが、データベース フラグを使用して有効にできます。

performance_schema を有効にすると、パフォーマンスのオーバーヘッドが生じ、メモリにも影響します。メモリ割り当ての詳細については、パフォーマンス スキーマのメモリ割り当てモデルをご覧ください。

performance_schema のメモリ インストルメンテーションは、MySQL のメモリ使用量のモニタリングに役立ちます。メモリ インストルメントの名前は、memory/code_area/instrument_name の形式です。ここで code_area は、sql や Innodb などの値、instrument_name はインストルメントの詳細です。

MySQL 5.7 では、デフォルトで一部のメモリ インストルメンテーションが有効になっていますが、すべてではありません。それらを有効にするには、performance_schema.setup_instruments テーブルの ENABLED 列を更新します。

mysql> select version();

+-------------------+

| version()    |

+-------------------+

| 5.7.39-google-log |

+-------------------+

1 row in set (0.00 sec)

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| ○  |   70 |

| ×   |   306 |

+---------+----------+

2 rows in set (0.00 sec)

mysql> UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';

Query OK, 306 rows affected (0.00 sec)

Rows matched: 376 Changed: 306 Warnings: 0

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| ○  |   376 |

+---------+----------+

1 row in set (0.00 sec)

ランタイムにメモリ インストルメンテーションを有効にすると、インストルメンテーションを有効にした後に作成されたメモリ オブジェクトのみがカウントされます。そのため、トラブルシューティングを実施するためにサーバーが十分なデータを収集するまで待つことをおすすめします。

MySQL 8.0 では、デフォルトですべてのメモリ インストルメンテーションが有効になっています。

mysql> select version();

+---------------+

| version()  |

+---------------+

| 8.0.28-google |

+---------------+

1 row in set (0.00 sec)

mysql> SELECT enabled, count(*) FROM performance_schema.setup_instruments WHERE NAME LIKE '%memory%' group by enabled;

+---------+----------+

| enabled | count(*) |

+---------+----------+

| ○  |   490 |

+---------+----------+

1 row in set (0.00 sec)

sys スキーマには、performance_schema データをわかりやすい形式に要約するビューが多数含まれています。ほとんどのビューはペアで提供されます。同じ名前を共有し、そのうちの一つに x$ という接頭辞が付加されています。MySQL 5.7 と 8.0 では、以下のビューを使用して、さまざまなレベルでのメモリ割り当てを表示できます。

mysql> use sys;

Database changed

mysql> show tables like '%memory%';

+-------------------------------------+

| Tables_in_sys (%memory%)      |

+-------------------------------------+

| memory_by_host_by_current_bytes  |

| memory_by_thread_by_current_bytes |

| memory_by_user_by_current_bytes  |

| memory_global_by_current_bytes   |

| memory_global_total        |

| x$memory_by_host_by_current_bytes |

| x$memory_by_thread_by_current_bytes |

| x$memory_by_user_by_current_bytes |

| x$memory_global_by_current_bytes  |

| x$memory_global_total       |

+-------------------------------------+

10 rows in set (0.02 sec)

sys.memory_global_total ビューには、合計メモリ使用量が読み取り可能な形式で要約されて表示されます。

mysql> select * from sys.memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

| 12.43 GiB   |

+-----------------+

1 row in set (0.44 sec)

sys.x$memory_global_total は同じ情報をバイト単位で表示します。

mysql> select * from sys.x$memory_global_total;

+-----------------+

| total_allocated |

+-----------------+

|  13351482992 |

+-----------------+

1 row in set (1.39 sec)

x$ 接頭辞が付加されていないビューは、よりユーザー フレンドリーで読みやすい出力を提供することを目的としています。x$ 接頭辞が付加されており、同じ値を未加工の形式で表示するビューは、データに対して独自の処理を行う他のツールで使用することをより意図したものです。

sys.memory_global_by_current_bytes ビューには、現在のメモリ使用量が割り当てタイプ（event_name）ごとにグループ化されて表示されます。例: memory/innodb/buf_buf_pool（InnoDB バッファプール）。デフォルトでは、メモリ使用量が多い順に行が並べ替えられます。

各割り当てタイプをドリルダウンする前に、Innodb、sql、performance_schema などのコード領域ごとのメモリ使用量の概要を確認しておくことをおすすめします。

次のクエリは、現在割り当てられているメモリをコード領域ごとに集計します。

SELECT SUBSTRING_INDEX(event_name,'/',2) AS code_area,

FORMAT_BYTES(SUM(current_alloc)) AS current_alloc

FROM sys.x$memory_global_by_current_bytes

GROUP BY SUBSTRING_INDEX(event_name,'/',2)

ORDER BY SUM(current_alloc) DESC;

+---------------------------+---------------+

| code_area        | current_alloc |

+---------------------------+---------------+

| memory/innodb      | 11.28 GiB  |

| memory/performance_schema | 520.02 MiB  |

| memory/temptable     | 514.00 MiB  |

| memory/sql        | 106.12 MiB  |

| memory/mysys       | 43.25 MiB  |

| memory/vio        | 372.15 KiB  |

| memory/myisam      | 696 bytes  |

| memory/csv        | 88 bytes  |

| memory/blackhole     | 88 bytes  |

+---------------------------+---------------+

9 rows in set (1.02 sec)

この出力は、innodb が最大量のメモリを消費しており、その次が performance_schema であり、さらにその次が一時テーブルであることを示しています。

これで、sys.memory_global_by_current_bytes に対して直接クエリを実行すると、event_name 列で説明されているように、MySQL 内部構造のメモリ使用量が表示されます。メモリの異常な増加をトラブルシューティングする場合は、このビューの出力を確認することでソースを特定できます。

mysql> select event_name,current_alloc,high_alloc from sys.memory_global_by_current_bytes limit 5;

+-------------------------------+---------------+------------+

| event_name          | current_alloc | high_alloc |

+-------------------------------+---------------+------------+

| memory/innodb/buf_buf_pool  | 10.72 GiB  | 10.72 GiB |

| memory/temptable/physical_ram | 514.00 MiB  | 515.00 MiB |

| memory/innodb/hash0hash   | 292.69 MiB  | 292.69 MiB |

| memory/innodb/memory     | 191.77 MiB  | 197.94 MiB |

| memory/sql/TABLE       | 60.39 MiB  | 62.35 MiB |

+-------------------------------+---------------+------------+

5 rows in set (0.45 sec)

各割り当てタイプの詳細は以下のとおりです。

このビューには、ユーザー別にグループ化されたメモリ使用量の概要が表示されます。ユーザーに複数の接続がある場合は、ユーザーのすべてのインスタンスのメモリ使用量の合計が表示されます。

mysql> select user,current_allocated from memory_by_user_by_current_bytes;

+-----------------+-------------------+

| user      | current_allocated |

+-----------------+-------------------+

| sbtest     | 60.30 MiB    |

| background   | 2.68 MiB     |

| root      | 1.52 MiB     |

| event_scheduler | 16.38 KiB    |

+-----------------+-------------------+

4 rows in set (1.16 sec)

アプリケーション / ジョブごとに異なるユーザーを使用する場合（推奨）、このビューは、特定のアプリケーションまたはジョブがメモリを大量に消費しているかどうかを特定するのに役立ちます。

このビューには、ホスト名別にグループ化されたメモリ使用量のサマリーが表示されます。

mysql> select host,current_allocated from memory_by_host_by_current_bytes;

+------------+-------------------+

| host   | current_allocated |

+------------+-------------------+

| 10.128.0.8 | 62.10 MiB    |

| background | 2.70 MiB     |

| 127.0.0.1 | 311.01 KiB    |

| localhost | 16.38 KiB    |

+------------+-------------------+

4 rows in set (1.58 sec)

このビューは、クライアント ホストごとのメモリ使用量を確認する際に活用できます。最近では、複数のクライアント / アプリケーション サーバーを使用するのが一般的です。このビューは、特定のクライアントまたはワークロードがメモリ使用量の増加を引き起こしているかどうかを特定するのに役立ちます。また、最初に 1 つのクライアント / アプリケーション サーバーに変更をデプロイし、メモリ使用量を他のクライアント ホストと比較してメモリへの影響をモニタリングして、情報に基づいた意思決定を行うこともできます。

このビューでは、メモリ使用量がスレッドごとにグループ化されて要約されるため、メモリの大部分を消費しているスレッドを特定できます。

mysql> select thread_id,user,current_allocated from sys.memory_by_thread_by_current_bytes limit 5;

+-----------+-------------------+-------------------+

| thread_id | user       | current_allocated |

+-----------+-------------------+-------------------+

|  12999 | sbtest@10.128.0.8 | 3.80 MiB     |

|  12888 | sbtest@10.128.0.8 | 3.75 MiB     |

|  12760 | sbtest@10.128.0.8 | 3.68 MiB     |

|  13128 | sbtest@10.128.0.8 | 3.67 MiB     |

|  13221 | sbtest@10.128.0.8 | 3.47 MiB     |

+-----------+-------------------+-------------------+

5 rows in set (2.29 sec)

特定のスレッドがメモリを大量に消費していることが判明した場合は、thread_id を使用して MySQL の低速クエリログまたは一般ログをフィルタし、メモリ増加の原因となったセッションまたはクエリを特定できます。

メモリ使用量が多い場合のトラブルシューティングは容易ではありません。幸いなことに、performance_schema のメモリ計測により、ユーザーは内部 MySQL 構造でメモリがどのように使用されているかを把握できます。sys スキーマ ビューを使用すると、情報を解釈することが容易になります。メモリ関連の問題が発生した場合は、この機能が問題の原因を突き止めるのに役立ち、構成 / クエリを変更することで同じ問題を解決できます。

例:

1. 内部一時テーブルが大量のメモリを必要とする場合は、tmp_table_size を使用して一時テーブルのサイズを制限するか、innodb_buffer_pool_size を小さくしてセッションごとのバッファの容量を確保します。
2. 特定のスレッドが大量のメモリを消費している場合は、thread_id を使用して低速のクエリログをフィルタし、問題のあるクエリを特定し、そのスレッド / セッションのメモリ使用量を削減するようにチューニングできます。

MySQL インスタンスが必要以上にメモリを使用しており、サーバーのメモリが不足するまでメモリ使用量が継続的に増加し、performance_schema がインストルメント化されたメモリと合計メモリでのギャップを示すか、メモリがどこで使用されているかわからない場合は、メモリリークの兆候を示している可能性があります。メモリリークが疑われる場合は、次の手順を実施します。

• MySQL の構成を慎重に確認し、バッファやキャッシュが過剰に割り当てられていないことを確認します。こちらの記事では、MySQL がさまざまなバッファやキャッシュにメモリを割り当てる仕組みについて説明しています。
• メモリの問題の原因となっている機能、構成、クエリを特定し、問題を再現してみます。
• 同じシリーズの次のマイナー バージョンの MySQL リリースノートで、同様の動作のバグが修正されたかどうかを確認します。
• MySQL を最新のマイナー バージョンにアップグレードして、メモリの問題が解決するかどうかを確認します。
• MySQL のバグデータベースを検索して、同様の問題が他のコミュニティ ユーザーから報告されていないか確認します。
• 同じ問題の既存のバグがない場合は、再現可能なテストケースで新しいバグを作成します。