インスタンスの大きいメモリ使用量を最適化する

インスタンスでメモリを大量に使用する問題や、メモリ不足（OOM）の問題が発生することがよくあります。メモリ使用率が高いデータベースインスタンスを実行すると、パフォーマンスの問題や機能の停止、さらにはデータベース停止の原因となることがよくあります。

MySQL メモリブロックの一部は、グローバルに使用されます。そのため、すべてのクエリワークロードはメモリ位置を共有し、常に占有され、MySQL プロセスが停止したときにのみ解放されます。メモリブロックの一部はセッションベースです。セッションが終了すると、直ちにそのセッションで使用されたメモリもシステムに解放されます。

Cloud SQL for MySQL インスタンスでのメモリ使用量が多い場合、Cloud SQL では、常に大量のメモリを使用しているクエリまたはプロセスを特定して解放することをおすすめします。MySQL のメモリ消費量は、主に次の 3 つに分けられます。

スレッドとプロセスメモリの使用量
バッファメモリの使用量
キャッシュメモリの使用量

スレッドとプロセスメモリの使用量

各ユーザーセッションは、セッションで使用される実行中のクエリ、バッファ、キャッシュに応じてメモリを消費し、MySQL のセッションパラメータで制御されます。主なパラメータは次のとおりです。

thread_stack
net_buffer_length
read_buffer_size
read_rnd_buffer_size
sort_buffer_size
join_buffer_size
max_heap_table_size
tmp_table_size

特定の時間に N 個のクエリが実行される場合、セッション中に各クエリはこれらのパラメータに従ってメモリを使用します。

バッファメモリの使用量

メモリのこの部分はすべてのクエリに共通で、Innodb_buffer_pool_size、Innodb_log_buffer_size、key_buffer_size などのパラメータで制御されます。

キャッシュメモリの使用量

キャッシュメモリには、クエリとその結果を保存するために使用されるクエリキャッシュが含まれ、後続の同じクエリのデータ取得を高速にします。また、トランザクションの実行中にバイナリログに加えた変更を保持する binlog キャッシュも含まれており、binlog_cache_size によって制御されます。

その他のメモリ使用量

メモリは結合オペレーションや並べ替えオペレーションでも使用されます。クエリで結合オペレーションや並べ替えオペレーションを使用している場合、これらのクエリでは join_buffer_size と sort_buffer_size に基づいてメモリが使用されます。

これとは別に、パフォーマンススキーマが有効な場合は、それによってもメモリが使用されます。パフォーマンススキーマごとにメモリ使用量を確認するには、次のクエリを使用します。

SELECT *
FROM
  performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/performance_schema/%';

MySQL では多くのインストゥルメンテーションを利用でき、パフォーマンススキーマによってメモリ使用量をモニタリングするよう設定できます。詳しくは、MySQL のドキュメントをご覧ください。

バルクデータ挿入のための MyISAM 関連パラメータは bulk_insert_buffer_size です。

MySQL によるメモリの使用については、MySQL のドキュメントをご覧ください。

推奨事項

Metrics Explorer を使用してメモリ使用量を確認する

インスタンスのメモリ使用量は、Metrics Explorer の database/memory/components.usage 指標で確認できます。

database/memory/components.cache と database/memory/components.free を合わせてメモリが 5% 未満の場合、OOM イベント発生のリスクが高まります。メモリ使用量をモニタリングし、OOM イベントを防ぐには、database/memory/components.usage の指標しきい値の条件が 95% 以上のアラートポリシーを設定することをおすすめします。

次の表に、インスタンスのメモリと推奨されるアラートのしきい値の関係を示します。

インスタンスのメモリ	推奨されるアラートしきい値
最大 100 GB	95%
100 GB～200 GB	96%
200 GB～300 GB	97%
300 GB 超	98%

メモリ使用量を計算する

MySQL データベースに適したインスタンスタイプを選択するために、MySQL データベースの最大メモリ使用量を計算します。次の計算式を使用します。

MySQL の最大メモリ使用量 = innodb_buffer_pool_size + innodb_additional_mem_pool_size + innodb_log_buffer_size + tmp_table_size + key_buffer_size + ((read_buffer_size + read_rnd_buffer_size + sort_buffer_size + join_buffer_size) x max_connections)

この計算式で使用されているパラメータは次のとおりです。

innodb_buffer_pool_size: バッファプールのサイズ（バイト）で、InnoDB がテーブルおよびインデックスデータをキャッシュに保存するメモリ領域。
innodb_additional_mem_pool_size: InnoDB がデータディクショナリ情報やその他の内部データ構造の格納に使用するメモリプールのサイズ（バイト）。
innodb_log_buffer_size: InnoDB がディスク上のログファイルへの書き込みに使用するバッファのサイズ（バイト）。
tmp_table_size: MEMORY ストレージエンジンおよび MySQL 8.0.28 以降の TempTable ストレージエンジンによって作成される内部メモリ内一時テーブルの最大サイズ。
Key_buffer_size: インデックスブロックに使用されるバッファのサイズ。MyISAM テーブルのインデックスブロックはバッファされ、すべてのスレッドで共有されます。
Read_buffer_size: MyISAM テーブルに対して順次スキャンを行う各スレッドは、スキャンする各テーブル用にこのサイズ（バイト）のバッファを割り当てます。
Read_rnd_buffer_size: この変数は、MyISAM テーブルからの読み取り、任意のストレージエンジン、複数範囲の読み取りの最適化に使用されます。
Sort_buffer_size: 並べ替えを実行する必要があるセッションごとに、このサイズのバッファが割り当てられます。sort_buffer_size は、ストレージエンジンに固有ではなく、最適化に一般的な方法で適用されます。
Join_buffer_size: プレーンインデックススキャン、範囲インデックススキャン、インデックスを使用せずフルテーブルスキャンを行う結合で使用されるバッファの最小サイズ。
Max_connections: 同時に許可されるクライアント接続の最大数。

メモリ使用量が多い場合のトラブルシューティング

SHOW PROCESSLIST を実行し、現在メモリを使用している動作中のクエリを確認します。接続されているすべてのスレッドと、現在実行中の SQL ステートメントを表示して、それらの最適化を試みます。state 列と duration 列に注意してください。
```
mysql> SHOW [FULL] PROCESSLIST;
```

BUFFER POOL AND MEMORY セクションの SHOW ENGINE INNODB STATUS をチェックして現在のバッファプールとメモリ使用量を確認します。これは、バッファプールサイズの設定に役立ちます。

mysql> SHOW ENGINE INNODB STATUS \G
----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 398063986; in additional pool allocated 0
Dictionary memory allocated 12056
Buffer pool size 89129
Free buffers 45671
Database pages 1367
Old database pages 0
Modified db pages 0

MySQL の SHOW variables コマンドを使用してカウンタ値を確認します。これにより、一時テーブル数、スレッド数、テーブルキャッシュ数、ダーティページ、オープンテーブル、バッファプール使用量などの情報を取得できます。
```
mysql> SHOW variables like 'VARIABLE_NAME'
```

変更を適用

さまざまなコンポーネントでメモリ使用量を分析したら、MySQL データベースに適切なフラグを設定します。Cloud SQL for MySQL インスタンスでフラグを変更するには、Google Cloud コンソールまたは gcloud CLI を使用します。Google Cloud コンソールを使用してフラグの値を変更するには、[フラグ] セクションを編集してフラグを選択し、新しい値を入力します。

最後に、メモリ使用量がまだ大きく、クエリを実行し、フラグの値が最適化されていると判断される場合は、OOM を避けるためにインスタンスサイズを増やすことを検討してください。