Google Cloud の Cloud SQL for MySQL は、Google Cloud Platform で MySQL、PostgreSQL、SQL Server データベースを設定、維持、運用、管理できるよう支援するフルマネージド データベース サービスです。Cloud SQL はフリート内で多数の MySQL データベースを実行しますが、メモリ不足(OOM)エラーが原因で一部の MySQL インスタンスがメモリを大量に消費してクラッシュすることがよく見られます。この記事では、MySQL のメモリに関する問題のトラブルシューティングについて説明します。
メモリ不足(OOM)状態は、プロセスがメモリを割り当てようとし、サーバーに使用可能なメモリがないために失敗した場合に発生します。その結果、多くの場合、Linux OOM キラーが発生します。これは、システムのメモリが非常に少ない場合に Linux カーネルが使用するプロセスであり、データベース プロセスが終了します。
OOM 状態によるデータベース クラッシュは理想的ではありません。データベース プロセスが SIGKILL ディレクティブを使用して終了されるため、アプリケーションに通常の MySQL がシャットダウンする可能性はないためです。これにより、次の問題が発生します。
MySQL のメモリ割り当ての把握に役立つ 5.7 バージョン以降、MySQL の performance_schema にメモリ計測機能が追加されました。この記事では、performance_schema を使用する MySQL のメモリに関する問題のトラブルシューティングについて説明します。
各 Cloud SQL インスタンスは、ホストである Google Cloud サーバー上で実行されている仮想マシン(VM)を利用しています。各 VM は、MySQL サーバーなどのデータベース プログラムと、ロギングやモニタリングなどのサポート サービスを提供するサービス エージェントを実行します。Cloud SQL は、メモリの一部をオペレーティング システム(OS)とサービス エージェント用に予約し、残りのメモリを mysqld などのデータベース プログラムに割り当てます。
次の表に、mysqld プロセスのメモリ割り当てと、各マシンタイプのデフォルトの InnoDB バッファプール構成を示します。
マシンタイプ | インスタンスの RAM GiB(バイト) | mysqld のメモリ(GB) | InnoDB バッファプール GiB(バイト) | バッファプール(インスタンス RAM の割合(%)) | バッファプール(mysqld メモリの割合(%)) |
db-g1-small | 1.7(1825361101) | 1.11 | 0.625(671088640) | 37% | 56% |
db-custom-1-3840 | 3.75(4026531840) | 2.97 | 1.375(1476395008) | 37% | 46% |
db-custom-2-7680 | 7.5(8053063680) | 6.72 | 4(4294967296) | 53% | 60% |
db-custom-2-13312 | 13(13958643712) | 12 | 9(9663676416) | 69% | 75% |
db-custom-4-15360 | 15(16106127360) | 14 | 10.5(11274289152) | 70% | 76% |
db-custom-4-26624 | 26(27917287424) | 24 | 19(20401094656) | 73% | 79% |
db-custom-8-30720 | 30(32212254720) | 28 | 22(23622320128) | 73% | 80% |
db-custom-8-53248 | 52(55834574848) | 48 | 38(40802189312) | 73% | 79% |
db-custom-16-61440 | 60(64424509440) | 55 | 44(47244640256) | 73% | 80% |
db-custom-16-106496 | 104(111669149696) | 96 | 75(80530636800) | 72% | 78% |
db-custom-32-122880 | 120(128849018880) | 110 | 87(93415538688) | 73% | 79% |
db-custom-32-212992 | 208(223338299392) | 191 | 150(161061273600) | 72% | 78% |
db-custom-64-245760 | 240(257698037760) | 221 | 173(185757335552) | 72% | 78% |
db-custom-96-368640 | 360(386547056640) | 331 | 260(279172874240) | 72% | 79% |
db-custom-64-425984 | 416(446676598784) | 383 | 300(322122547200) | 72% | 78% |
db-custom-96-638976 | 624(670014898176) | 574 | 450(483183820800) | 72% | 78% |
マシンタイプ
インスタンスの RAM GiB(バイト)
mysqld のメモリ(GB)
InnoDB バッファプール GiB(バイト)
バッファプール(インスタンス RAM の割合(%))
バッファプール(mysqld メモリの割合(%))
db-g1-small
1.7(1825361101)
1.11
0.625(671088640)
37%
56%
db-custom-1-3840
3.75(4026531840)
2.97
1.375(1476395008)
37%
46%
db-custom-2-7680
7.5(8053063680)
6.72
4(4294967296)
53%
60%
db-custom-2-13312
13(13958643712)
12
9(9663676416)
69%
75%
db-custom-4-15360
15(16106127360)
14
10.5(11274289152)
70%
76%
db-custom-4-26624
26(27917287424)
24
19(20401094656)
73%
79%
db-custom-8-30720
30(32212254720)
28
22(23622320128)
73%
80%
db-custom-8-53248
52(55834574848)
48
38(40802189312)
73%
79%
db-custom-16-61440
60(64424509440)
55
44(47244640256)
73%
80%
db-custom-16-106496
104(111669149696)
96
75(80530636800)
72%
78%
db-custom-32-122880
120(128849018880)
110
87(93415538688)
73%
79%
db-custom-32-212992
208(223338299392)
191
150(161061273600)
72%
78%
db-custom-64-245760
240(257698037760)
221
173(185757335552)
72%
78%
db-custom-96-368640
360(386547056640)
331
260(279172874240)
72%
79%
db-custom-64-425984
416(446676598784)
383
300(322122547200)
72%
78%
db-custom-96-638976
624(670014898176)
574
450(483183820800)
72%
78%
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)
各割り当てタイプの詳細は以下のとおりです。
割り当てタイプ | 詳細 |
memory/innodb/buf_buf_pool | InnoDB バッファプールが使用するメモリ |
memory/temptable/physical_ram | 内部一時テーブルで使用されているメモリ |
memory/innodb/hash0hash | InnoDB ハッシュ テーブルが使用するメモリ |
memory/innodb/memory | さまざまな InnoDB バッファで使用されているメモリ |
memory/sql/TABLE | TABLE オブジェクトとそれらのメモリルートで使用されているメモリ |
割り当てタイプ
詳細
memory/innodb/buf_buf_pool
InnoDB バッファプールが使用するメモリ
memory/temptable/physical_ram
内部一時テーブルで使用されているメモリ
memory/innodb/hash0hash
InnoDB ハッシュ テーブルが使用するメモリ
memory/innodb/memory
さまざまな InnoDB バッファで使用されているメモリ
memory/sql/TABLE
TABLE オブジェクトとそれらのメモリルートで使用されているメモリ
このビューには、ユーザー別にグループ化されたメモリ使用量の概要が表示されます。ユーザーに複数の接続がある場合は、ユーザーのすべてのインスタンスのメモリ使用量の合計が表示されます。
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 スキーマ ビューを使用すると、情報を解釈することが容易になります。メモリ関連の問題が発生した場合は、この機能が問題の原因を突き止めるのに役立ち、構成 / クエリを変更することで同じ問題を解決できます。
例:
MySQL インスタンスが必要以上にメモリを使用しており、サーバーのメモリが不足するまでメモリ使用量が継続的に増加し、performance_schema がインストルメント化されたメモリと合計メモリでのギャップを示すか、メモリがどこで使用されているかわからない場合は、メモリリークの兆候を示している可能性があります。メモリリークが疑われる場合は、次の手順を実施します。