MySQL によるメモリの使われ方
MySQL インスタンスでは、多くのメモリが消費されることや、メモリ不足（OOM）の問題が発生することがよくあります。メモリが不足しがちなデータベース インスタンスの実行では、パフォーマンスの問題、動作速度の低下、さらにはアプリケーションの停止さえ引き起こすことがよくあります。
MySQL インスタンスのメモリを割り当てる前に、MySQL によるメモリの使われ方を理解することが重要です。この記事では、メモリを消費する MySQL の機能について説明します。こうした機能は多くの場合、OOM の問題につながります。また、Google Cloud のフルマネージド サービスである Cloud SQL for MySQL では、メモリ管理に関してどのように設定されているかを説明します。
グローバル バッファ
MySQL は、サーバーの起動時にグローバル バッファを割り当て、すべての接続で共有されます。MySQL のメモリの大部分は、グローバル バッファ（innodb_buffer_pool_size、innodb_log_buffer_size、key_buffer_size など）によって消費されます。
InnoDB バッファプール
通常は、InnoDB のバッファプールが、MySQL インスタンス内で最大のメモリを消費します。その構成には、innodb_buffer_pool_size パラメータを使用します。このバッファプールは、テーブルデータとインデックス、バッファの変更、適応ハッシュ インデックス、他の内部構造のキャッシュ保存に使用されます。Google Cloud のマネージド MySQL サービスである Cloud SQL for MySQL は、インスタンスのサイズに応じて、innodb_buffer_pool_size をインスタンス メモリの 72% を上限に構成します。
InnoDB は、バッファおよび関連するデータ構造用に追加のメモリを予約します。割り当てられるメモリの合計は、指定したバッファプールのサイズよりも約 10% 大きくなります。InnoDB のバッファプールのメモリ使用量は、show Engine innodb status\G の出力で確認できます。
|
mysql> show engine innodb status\G … ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 11511349248 … |
InnoDB ログバッファ
InnoDB のログバッファは、ディスク上の InnoDB REDO ログファイルに書き込まれる変更を保持するために使用されます。innodb_log_buffer_size を使用して構成されます。MySQL コミュニティのデフォルト値は 16 MB です。Cloud SQL for MySQL も同じ値を使用します。
キーバッファのサイズ
キーバッファは MySQL が使用し、メモリに MyISAM インデックスをキャッシュ保存します。key_buffer_size を使用して構成されます。MySQL コミュニティのデフォルト値は 8 MB です。Cloud SQL for MySQL も同じ値を使用します。Cloud SQL for MySQL は MyISAM テーブルをサポートしていないため、デフォルトのままで問題ありません。
クエリ キャッシュのサイズ
query_cache_size 構成変数は、クエリ結果をキャッシュに保存するために割り当てられるメモリ量を定めます。MySQL コミュニティ 5.7 と Cloud SQL for MySQL 5.7 では、デフォルトで無効になっています。
クエリ キャッシュは重大なスケーラビリティの問題があることが知られていたため、MySQL 5.7.20 でサポートが終了し、MySQL 8.0 で削除されました。MySQL 5.7 インスタンスで引き続き使用している場合は、ワークロードにとって本当に有用かどうかを確認してください。詳細は、こちらのブログをご覧ください。
グローバル キャッシュ
MySQL は、すべての接続で共有されるグローバル キャッシュを割り当てます。これらは動的に割り当てられ、構成変数によってその上限が定められます。
テーブル キャッシュ
MySQL では、テーブル キャッシュを使用してテーブルへのアクセスを高速化します。MySQL テーブル キャッシュには、開いているテーブルのキャッシュと、テーブル定義のキャッシュの 2 つがあり、それぞれ table_open_cache と table_definition_cache で構成されます。
table_open_cache は、接続されたすべてのスレッドが開いているテーブルのファイル記述子を保存するメモリ キャッシュです。この値を大きくすると、mysqld プログラム（MySQL サーバー）が必要とするファイル記述子の数が増加します。お使いのオペレーティング システムが、table_open_cache の設定によって暗黙的に指定されるオープン ファイル記述子の数を扱えることを確認してください。
複数のクライアント セッションが特定のテーブルに同時にアクセスでき、そのテーブルは同時クライアント セッションごとに独立して開きます。このため、開いているテーブルの数がサーバー内のテーブル数より多くなることがあります。テーブル キャッシュがいっぱいになると、サーバーは現在使用されていないテーブルを、最も長い間使われていないテーブルから順に解放します。
table_definition_cache は、テーブル定義を保存するメモリ キャッシュです。これはグローバルなもので、すべての接続で共有されます。
CloudSQL for MySQL 5.7 では、table_open_cache と table_definition_cache のデフォルト値として 2,000 と 1,400 が使用されます。
CloudSQL for MySQL 8.0 では、table_open_cache と table_definition_cache のデフォルト値として 4,000 と 2,000 が使用されます。
スレッド キャッシュ
MySQL はクライアント接続ごとに、クライアントが切断するまですべてのクエリを実行しクライアントに結果を返す専用のスレッドを割り当てます。MySQL は、接続ごとにスレッドを作成して破棄する必要がないように、スレッドをキャッシュに保存します。スレッド キャッシュ内のスレッド数は、thread_cache_size 変数を使用して構成されます。
CloudSQL for MySQL では、thread_cache_size にデフォルトで 48 を使用します。
InnoDB データ ディクショナリ キャッシュ
InnoDB は、テーブル定義を保存するための独自のキャッシュを備えています。これは、テーブル オープン キャッシュやテーブル定義キャッシュとは異なります。InnoDB データ ディクショナリに割り当てられたメモリは、show engine innodb status\G の出力で確認できます。
|
---------------------- BUFFER POOL AND MEMORY ---------------------- … Dictionary memory allocated 65816817 |
table_definition_cache 設定では、InnoDB データ ディクショナリ キャッシュのテーブル インスタンス数に対してソフト上限を設定します。InnoDB データ ディクショナリ キャッシュのテーブル数が table_defined_cache 上限を超過すると、LRU メカニズムがエビクションの印をテーブル インスタンスに付け始め、それらは最終的にこのキャッシュから削除されます。
これは InnoDB データ ディクショナリ キャッシュのテーブル インスタンス数が、table_definition_cache の上限よりも常に少なくなることを意味するのでしょうか。そういうわけではなく、外部キーの関係を持つテーブル インスタンスは、LRU リストに配置されません。それらはキャッシュに残り、テーブル インスタンスが table_definition_cache の上限を超えて大きくなり、メモリ使用量の増加につながります。外部キーの関係にあるテーブルで消費されたメモリは、MySQL のシャットダウン / 再起動イベントでなければ解放されません。この問題は、MySQL 5.7 と 8.0 の両方に存在し、確認済みのバグがあります。
MySQL インスタンスに外部キー関係を持つテーブルが多数ある場合、InnoDB データ ディクショナリ キャッシュは数 GB のメモリを消費する可能性があります。MySQL のバッファ / キャッシュを構成する際に、見落とされがちですが、予期しない高メモリ使用量やメモリ不足（OOM）の問題の原因の 1 つとなる可能性があります。
サンプル出力では、小規模なインスタンスによる 4.16 GB の InnoDB データ ディクショナリ キャッシュの消費を示します。
|
$ mysql -e "show engine innodb status\G" | grep -i memory BUFFER POOL AND MEMORY Total large memory allocated 7696023552 Dictionary memory allocated 4465193358 |
セッション バッファ
メモリを消費する MySQL のもう一つの機能は、セッション バッファです。このバッファは、セッションごとに割り当てられ、場合によっては複数のクエリを単一のクエリ（特に join_buffer_size）に割り当てることができます。
これらのバッファは、クエリ（並べ替え、結合、インデックス/フルテーブル スキャンなど）で必要な場合にのみ割り当てられますが、必要な場合は、ごく小さい割り当てが必要だとしてもフルサイズで割り当てられます。これらのバッファを大きな値に設定すると、メモリが無駄になる可能性があります。
デフォルト値は、MySQL コミュニティと Cloud SQL for MySQL で同じです。
バイナリログ キャッシュ
MySQL では、バイナリログ キャッシュで、トランザクションの実行中にバイナリログに加えられた変更を保持します。これは、binlog_cache_size を使用して構成します。バイナリ ロギングが有効になっている場合（log_bin=ON）は、各クライアントに割り当てられます。
binlog_cache_size のデフォルト値は、MySQL コミュニティと Cloud SQL for MySQL で同じです。
一時テーブル
MySQL は、GROUP BY、ORDER BY、DISTINCT、UNION などの一部のタイプのクエリを処理しながら、中間結果を保存するための内部一時テーブルを作成します。これらの内部一時テーブルは、最初にメモリに作成され、最大サイズに達するとディスク上のテーブルに変換されます。内部一時テーブルの最大サイズは、tmp_table_size 変数と max_heap_table_size 変数の最小値として決まります。
tmp_table_size と max_heap_table_size のデフォルト値は、MySQL コミュニティと Cloud SQL for MySQL で同じです。
注: セッションごとのバッファとメモリ内一時テーブルは、接続ごとにメモリを個別に割り当てるため、多くの接続がそれを必要とする場合、全体的なメモリ使用量は非常に大きくなります。これらの値を高く設定しすぎないようにして、ワークロードに最適な値を見つけることをおすすめします。
接続メモリごと
すべてのスレッドが、クライアント接続を管理するためのメモリをほとんど必要としません。そのサイズを制御する変数は次のとおりです。
- thread_stack: 各スレッドのスタックサイズは、デフォルトで 256 KB です。
- net_buffer_length: 各クライアントは net_buffer_length の接続バッファと結果バッファに関連付けられます。これにより、最大 max_allowed_packet のサイズまで拡張できます。
Performance_schema
performance_schema が有効になっている場合は、MySQL サーバーの実行を低レベルでモニタリングできます。Performance_schema は、メモリを動的に割り当て、MySQL のシャットダウン / 再起動時にしか解放されません。
Cloud SQL for MySQL では、RAM サイズが 15 GB 以上のインスタンスで performance_schema を有効にできます。これは、MySQL 8.0.26 バージョンからデフォルトで有効になっています。Performance_schema は、MySQL 5.6、5.7、8.0.18 バージョンではデフォルトで無効になっていますが、データベース フラグを使用して有効にできます。
まとめ
Cloud SQL for MySQL は、すぐに使用できる適切なパフォーマンスのためにメモリ関連のパラメータを自動的に構成します。ワークロードで多数のテーブルや接続をサポートするためにより大きなキャッシュが必要な場合、インスタンスで OOM の問題が発生する可能性があります。セッション バッファを同時に割り当てるスレッドの多くは、メモリ関連の問題につながることもあります。グローバル バッファ / キャッシュに割り当てるメモリが多いほど、接続に使用できるメモリが少なくなり（セッション バッファも同様）、また逆も同様で、良好なバランスを取ることが鍵になります。
ワークロードでより大きなキャッシュ / セッション バッファが必要な場合は、innodb_buffer_pool_size でグローバル バッファのサイズを小さくできます。Cloud SQL for MySQL インスタンスの構成パラメータは、データベース フラグを使用して変更できます。引き続き OOM やパフォーマンスの問題が発生する場合は、インスタンス サイズをアップグレードしてメモリを増やすことが可能です。
関連プロダクトとサービス
Google Cloud は、オンプレミスのデータセンターの廃止から SaaS アプリケーションの実行、基幹業務システムの移行まで、お客様のビジネスニーズに合わせて構築されたマネージド MySQL データベースを提供します。
