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

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

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

• MySQL サーバーがダウンし（障害復旧が完了するまでには数分かかる場合があります）、ユーザーとビジネスに影響する

• MySQL サーバーがバッファとキャッシュを完全にウォームアップして、ピーク パフォーマンスに到達するまでにさらに時間がかかる
• 予期しないクラッシュによるデータ破損の可能性

バージョン 5.7 以降、MySQL のメモリ割り当ての理解に役立つ、performance_schema へのメモリ インストルメンテーションが追加されました。この記事では、performance_schema を使用して MySQL のメモリに関する問題のトラブルシューティングを行う方法について説明します。

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

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

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

mysqld のメモリ使用量が mysqld プロセスの設定上限を超えると、Linux OOM キラーはインスタンス上の最大メモリ コンシューマである mysqld プロセスを強制終了します。

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(*) |

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

| YES     |       70 |

| NO      |      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(*) |

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

| YES     |      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(*) |

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

| YES     |      490 |

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

1 row in set (0.00 sec)

sys スキーマには、performance_schema データをわかりやすい形式に要約するビューが多数含まれています。ほとんどのビューはペアで提供されます。名前が同じで、そのうちの 1 つに 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 のバグデータベースを検索して、同様の問題が他のコミュニティ ユーザーから報告されていないか確認します。
• 同じ問題に既存のバグがない場合は、再現可能なテストケースで新しいバグを作成します。