MySQL によるメモリの使われ方

MySQL は、サーバーの起動時にグローバル バッファを割り当て、すべての接続で共有されます。MySQL のメモリの大部分は、グローバル バッファ（innodb_buffer_pool_size、innodb_log_buffer_size、key_buffer_size など）によって消費されます。

通常は、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 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 Community 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 のデフォルト値として 2000 と 1400 が使用されます。

CloudSQL for MySQL 8.0 では、table_open_cache と table_definition_cache のデフォルト値として 4000 と 2000 が使用されます。

MySQL はクライアント接続ごとに、クライアントが切断するまですべてのクエリを実行しクライアントに結果を返す専用のスレッドを割り当てます。MySQL は、接続ごとにスレッドを作成して破棄する必要がないように、スレッドをキャッシュに保存します。スレッド キャッシュ内のスレッド数は、thread_cache_size 変数を使用して構成されます。

CloudSQL for MySQL では、thread_cache_size にデフォルトで 48 を使用します。

InnoDB は、テーブル定義を保存するための独自のキャッシュを備えています。これは、テーブル オープン キャッシュやテーブル定義キャッシュとは異なります。InnoDB データ ディクショナリに割り当てられたメモリは、show engine innodb status\G の出力で確認できます。

----------------------

BUFFER POOL AND MEMORY

----------------------

…

Dictionary memory allocated 65816817

table_definition_cache 設定では、InnoDB データ ディクショナリ キャッシュのテーブル インスタンス数に対してソフト上限を設定します。InnoDB データ ディクショナリ キャッシュのテーブル数が table_definition_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）に割り当てることができます。

• sort_buffer_size 
• join_buffer_size
• read_buffer_size
• read_rnd_buffer_size

これらのバッファは、クエリで必要な場合にのみ割り当てられますが（並べ替え、結合、インデックス/フルテーブル スキャンなど）、必要な場合は、ごく小さい割り当てが必要だとしてもフルサイズで割り当てられます。これらのバッファを大きな値に設定すると、メモリが無駄になる可能性があります。

デフォルト値は、MySQL コミュニティと Cloud SQL for MySQL で同じです。

MySQL では、バイナリログ キャッシュを使用して、トランザクションの実行中にバイナリログに加えられた変更を保持します。これは、binlog_cache_size を使用して構成されます。バイナリ ロギングが有効（log_bin=ON）の場合は、クライアントごとに割り当てられます。

binlog_cache_size のデフォルト値は、MySQL Community と 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 が有効になっている場合は、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 やパフォーマンスの問題が発生する場合は、インスタンス サイズをアップグレードしてメモリを増やすことが可能です。