tencent cloud

メモリは、TencentDB for MySQLの重要なパフォーマンスパラメータです。例外的なSQLリクエストおよび最適化するデータベースにより、メモリ使用率が高くなりすぎる状況がしばしば発生します。極端な場合にはOOMによってマスター/スレーブの切り替えが発生し、ビジネスの安定性及び可用性に影響することもあります。　

MySQLのメモリは主にグローバル共有メモリとセッションレベルのプライベートメモリの2つに分かれます。共有メモリはインスタンス作成時に割り当てられたメモリ領域で、すべての接続で共有されます。プライベートメモリは、MySQLサーバーへの接続時にキャッシュを割り当てるために使用されます。特殊な一部のSQLまたはフィールドタイプは、単一スレッドが複数のキャッシュに割り当てられることがあります。このため、OOM例外は、各接続のプライベートメモリによるものです。以下、各部の構成の詳細について説明します。

共有メモリ

以下のコマンドを実行し、例示した共有メモリの割り当て状況を照会します。

show variables where variable_name in ('innodb_buffer_pool_size','innodb_log_buffer_size','innodb_additional_mem_pool_size','key_buffer_size','query_cache_size');

説明：

5.7バージョンではinnodb_additional_mem_pool_sizeをサポートしていません。

以下のパラメータは、メモリの仕様が1000MBのインスタンスの共有メモリの割り当て状況の照会結果（以下はテストインスタンスの設定）です：
1. +---------------------------------+-----------+
2. | Variable_name | Value |
3. +---------------------------------+-----------+
4. | innodb_additional_mem_pool_size | 8388608 |
5. | innodb_buffer_pool_size | 524288000 |
6. | innodb_log_buffer_size | 67108864 |
7. | key_buffer_size | 16777216 |
9. | query_cache_size | 0 |
10. +---------------------------------+-----------+
11. 5 rows in set (0.01 sec)

パラメータの説明：

• innodb_buffer_pool_size
  この部分のキャッシュはInnodbエンジンで最も重要なバッファー領域で、メモリによって物理データファイルを補完する重要な手段です。TencentDB for MySQLではインスタンス仕様設定の50%～80%をこの部分の容量（上図では1000MB * 0.5 = 500MB）にしています。これには主に、データページ、インデックスページ、undoページ、insert buffer、アダプティブハッシュインデックス、ロック情報およびデータディクショナリ等の情報が含まれます。SQLの読み書き操作では、先に物理データファイルの操作は行わず、buffer_poolの操作を先に行ってから、checkpoint等のメカニズムによってデータファイルに書き戻す必要があります。この領域の利点はデータベースのパフォーマンスを向上させ、SQL稼働速度を速められることで、欠点は障害から回復する速度が遅いことです。

• innodb_log_buffer_size
  この部分は主にredo ログの情報を保存し、TencentDB for MySQLでは64MBに設定します。　InnoDBは、最初にここにredoログを書き込み、一定の頻度でredoログファイルに更新します。領域は大きくしすぎる必要はありません。一般にはこの部分のキャッシュは比較的高い頻度でredo ログを更新するためです（Master Threadは毎秒、トランザクションのコミット時、および使用可能なスペースが1/2未満の場合に更新されます。）。　　　

• innodb_additional_mem_pool_size
  この部分はInnoDB内の一部のデータ構造を保存し、TencentDB for MySQLで8MBに統一して設定します。通常、buffer_poolにメモリ申請するときは、別のメモリにそのオブジェクトの構造情報をストレージする領域も必要とします。この部分のサイズはテーブル数と関係し、テーブル数が大きくなるほど大きな領域が必要になります。

• key_buffer_size
  この部分はMyISAMテーブルの重要なバッファ領域で、主にMyISAMテーブルのキーを格納するために使用されます。すべてのインスタンスは16Mに統一しています。MylSAMテーブルはInnoDBテーブルとは異なり、そのインデックスキャッシュはkey_bufferに保存され、データキャッシュはOSのメモリに保存されています。TencentDB for MySQLのシステムはMylSAMエンジンを使用するため、この部分に一定量の領域を割り当てる必要があります。

• query_cache_size
  この部分はクエリ結果をキャッシュして、SQLの解析と実行のオーバーヘッドを削減するために使用されます。この部分のキャッシュ機能は、TencentDB for MySQLでは無効になっています。SQL言語のハッシュ値に基づいて結果をキャッシュし、テーブルデータに変化が生じると無効になるため、主に読み出しが多く書き込みが少ないアプリケーションシナリオに適しています。

プライベートメモリ

以下のコマンドを実行し、例示したセッションプライベートメモリの割り当て状況を照会します：

show variables where variable_name in ('read_buffer_size','read_rnd_buffer_size','sort_buffer_size','join_buffer_size','binlog_cache_size','tmp_table_size');

照会結果は以下のとおりです（以下はテストインスタンスの設定）。
1. +----------------------+-----------+
2. | Variable_name | Value |
3. +----------------------+-----------+
4. | binlog_cache_size | 32768 |
5. | join_buffer_size | 262144 |
6. | read_buffer_size | 262144 |
7. | read_rnd_buffer_size | 524288 |
8. | sort_buffer_size | 524288 |
9. | tmp_table_size | 209715200 |
10. +----------------------+-----------+
11. 6 rows in set (0.00 sec)

パラメータの説明：

• read_buffer_size
  インデックスを使用しない検索時に確保されるメモリバッファの容量です。シーケンシャルスキャンのキャッシュをそれぞれ保存します。threadがデータをシーケンシャルスキャンするとき、まずそのbuffer領域をスキャンして物理的な読み取りが増えるのを回避します。

• read_rnd_buffer_size
  ランダムスキャンへのキャッシュをそれぞれ保存します。threadがデータをランダムスキャンするとき、まずそのbuffer領域をスキャンして物理的な読み取りが増えるのを回避します。

• sort_buffer_size
  ソートバッファはファイルソートを実行するときに使うメモリ上の領域です。sort_buffer_sizeを超えた分はテンポラリファイルに書き出され、ソートが実行されます。大きなデータをソートする場合、このパラメータを大きくするとメモリ上でソートが実行されるため、パフォーマンスが向上します。

• join_buffer_size
  MySQLはNested Loop Join (NLJ) アルゴリズムのみをサポートします。JOINバッファはインデックスを使わない結合に使うメモリ上の領域です。これによりフルテーブルスキャンが実行されます。このパラメータを大きくするとメモリ上で結合が実行されるため、パフォーマンスが向上します。

• binlog_cache_size
  スレッドのbinlogをキャッシュするために使用されます。トランザクションがコミットされる前に、先にそのログをbinlog_cacheに保存します。トランザクションがコミットされた後、そのbinlogは、永続ストレージ用にディスク上のbinlogファイルに更新されます。

• tmp_table_size
  上記各セッションレベルのbufferとは異なり、このパラメータはコンソール上で修正できます。このパラメータはユーザーメモリの一時テーブルの大きさを指し、このtreadが作成した一時テーブルが設定した大きさを超過すると、一時テーブルをディスク上のMyISAM一時テーブルに転換します。