Amazon RDS for MySQL データベースの空きメモリが少ない場合のトラブルシューティング方法を教えてください。

簡単な説明

Amazon RDS for MySQL では、次の 4 つのメモリステータスをモニタリングできます。

• **アクティブ:**データベースプロセスまたはスレッドによってアクティブに消費されているメモリ。
• **バッファ:**バッファは、データブロックを保持するために使用されるメモリ内の一時的なスペースです。
• **空きメモリ:**使用可能なメモリです。
• **キャッシュ:**キャッシュは、データを一時的にメモリに保存して、データをすばやく取得できるようにする手法です。

デフォルトでは、Amazon RDS for MySQL インスタンスを作成すると、データベース操作を改善するためにバッファとキャッシュが割り当てられます。Amazon RDS for MySQL には、特定のオペレーションを実行するための内部一時テーブルを作成する内部メモリコンポーネント (key_buffers_size or query_cache_size など) もあります。

Amazon RDS for MySQL を使用するときは、MySQL がメモリをどのように使用および割り当てるかを必ず理解してください。メモリを使用しているコンポーネントを特定したら、インスタンスレベルとデータベースレベルでボトルネックを探すことができます。次に、それらの特定のメトリックを監視し、最適なパフォーマンスが得られるようにセッションを構成します。

解決策

MySQL でメモリを使用する方法

Amazon RDS for MySQL では、インスタンスで使用可能なメモリの 80% から 90% がデフォルトパラメータで割り当てられます。この割り当てはパフォーマンスに最適ですが、より多くのメモリを使用するパラメータを設定する場合は、それを補うためにメモリの使用量が少なくなるように他のパラメータを変更します。

RDS for MySQL DB インスタンスのおおよそのメモリ使用量は、次のように計算できます。

Maximum MySQL Memory Usage = innodb_buffer_pool_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_log_buffer_size、key_buffer_size、および query_cache_size などのコンポーネントが含まれます。innodb_buffer_pool_size パラメータは、innodb がデータベーステーブルとインデックス関連データをキャッシュする RAM のメモリ領域です。バッファプールが大きいほど、ディスクに戻される I/O 操作が少なくなります。デフォルトでは、innodb_buffer_pool_size は Amazon RDS DB インスタンスに割り当てられた使用可能なメモリの最大 75% を使用します。

innodb_buffer_pool_size = {DBInstanceClassMemory*3/4}

最初にこのパラメータを確認して、メモリ使用量の原因を特定してください。次に、カスタムパラメータグループのパラメータ値を変更して、innodb_buffer_pool_size の値を減らすことを検討してください。

たとえば、デフォルトの DBInstanceClassMemory*3/4 は、*5/8 または *1/2 に減らすことができます。インスタンスの ** BufferCacheHitRatio ** 値が低すぎないことを確認してください。BufferCacheHitRatio の値が小さい場合は、RAM を増やすためにインスタンスサイズを増やす必要がある場合があります。詳細については、「Best practices for configuring parameters for Amazon RDS for MySQL, part 1: Parameters related to performance」を参照してください。

MySQL スレッド

MySQL DB インスタンスに接続されている各 MySQL スレッドにもメモリが割り当てられます。次のスレッドには割り当てられたメモリが必要です。

• thread_stack
• net_buffer_length
• read_buffer_size
• sort_buffer_size
• join_buffer_size
• max_heap_table_size
• tmp_table_size

さらに、MySQL はいくつかの操作を実行するために内部一時テーブルを作成します。これらのテーブルは、最初はメモリベースのテーブルとして作成されます。テーブルが tmp_table_size または **max_heap_table_size ** (どちらか小さい値) で指定されたサイズに達すると、テーブルはディスクベースのテーブルに変換されます。複数のセッションで内部一時テーブルが作成されると、メモリ使用量が増加することがあります。メモリ使用量を減らすには、クエリでテンポラリテーブルを使用しないでください。

**注:**tmp_table_size と max_heap_table_size の上限を増やすと、メモリ内でより大きな一時テーブルを使用できるようになります。暗黙の一時テーブルが作成されたかどうかを確認するには、created_tmp_tables 変数を使用します。この変数の詳細については、MySQL ウェブサイトの「created_tmp_tables」を参照してください。

JOIN および SORT 操作

JOIN または SORT 操作中に join_buffer_size や sort_buffer_size などの同じタイプの複数のバッファが割り当てられると、メモリ使用量が増加します。たとえば、MySQL は 2 つのテーブル間の JOIN を実行するために JOIN バッファを 1 つ割り当てます。クエリに複数テーブルの JOIN が含まれ、すべてのクエリに JOIN バッファが必要な場合、MySQL はテーブルの総数よりも 1 つ少ない数の JOIN バッファを割り当てます。セッション変数に高すぎる値を設定すると、クエリが最適化されていない場合、問題が発生する可能性があります。join_buffer_size および join_buffer_size および sort_buffer_size などのセッションレベルの変数に最小メモリを割り当てることができます。.詳細については、「パラメータグループを使用する」を参照してください。

MYISAM テーブルへの一括挿入を実行すると、bulk_insert_buffer_size バイトのメモリが使用されます。「MySQL ストレージエンジンを使用する際のベストプラクティス」を参照してください。

パフォーマンススキーマ

Amazon RDS for MySQL の Performance Insights でパフォーマンススキーマを有効にすると、パフォーマンススキーマでメモリが消費される可能性があります。パフォーマンススキーマが有効になっている場合、MySQL はインスタンスの起動時とサーバー操作中に内部バッファを割り当てます。パフォーマンススキーマがメモリを使用する方法の詳細については、MySQL ウェブサイトの「The Performance Schema Memory-Allocation Model」を参照してください。

パフォーマンススキーマテーブルに加えて、MySQL sys スキーマも使用できます。たとえば、performance_schema イベントを使用して、パフォーマンススキーマが使用する内部バッファに割り当てられているメモリの量を表示できます。あるいは、次のようなクエリを実行して、どれだけのメモリが割り当てられているかを確認することもできます。

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

メモリインストゥルメントは、「memory/code_area/instrument_name」の形式で setup_instruments テーブルにリストされています。メモリインストルメンテーションを有効にするには、setup_instruments テーブルの該当するインストゥルメントの ENABLED 列を更新します。

UPDATE performance_schema.setup_instruments SET ENABLED = 'YES' WHERE NAME LIKE 'memory/%';

インスタンスのメモリ使用量のモニタリング

Amazon CloudWatch メトリクス

使用可能なメモリが少ない場合は、Amazon CloudWatch の DatabaseConnections、CPUUtilization、ReadIOPS、WriteIOPS のメトリクスをモニタリングします。

DatabaseConnections では、データベースに接続するたびに、ある程度のメモリを割り当てる必要があることに注意することが重要です。そのため、データベース接続の急増により、解放可能なメモリが減少する可能性があります。Amazon RDS では、max_connections のソフトリミットは次のように計算されます。

{DBInstanceClassMemory/12582880}

Amazon CloudWatch の DatabaseConnections メトリクスを確認して、このソフトリミットを超えているかどうかをモニタリングします。

さらに、FreeableMemory に加えて SwapUsage の CloudWatch メトリクスをモニタリングして、メモリ負荷をチェックします。大量のスワップが使用されていて、FreeableMemory が少ない場合は、インスタンスに高いメモリ負荷がかかっている可能性があります。メモリ負荷が高いと、データベースのパフォーマンスに影響します。メモリ負荷レベルを 95% 未満に抑えるのがベストプラクティスです。詳細については、「十分なメモリがあるのに Amazon RDS インスタンスがスワップメモリを使用するのはなぜですか?」を参照してください。

拡張モニタリング

DB インスタンスのリソース使用率を監視するには、拡張モニタリングを有効にします。次に、1 秒または 5 秒の精度を設定します (デフォルトは 60 秒)。拡張モニタリングでは、空きメモリとアクティブメモリをリアルタイムで監視できます。

DB インスタンスのスレッドを一覧表示することで、CPU とメモリを最大限消費しているスレッドを監視することもできます。

mysql> select THREAD_ID, PROCESSLIST_ID, THREAD_OS_ID from performance_schema.threads;

次に、thread_OS_ID を thread_ID にマッピングします。

select p.* from information_schema.processlist p, performance_schema.threads t
where p.id=t.processlist_id and t.thread_os_id=<Thread ID from EM processlist>;

空きメモリが少ない場合のトラブルシューティング

空きメモリが少ないという問題が発生した場合は、次のトラブルシューティングのヒントを検討してください。

• クエリを実行するのに十分なリソースがデータベースに割り当てられていることを確認してください。Amazon RDS では、割り当てられるリソースの量はインスタンスタイプによって異なります。また、ストアドプロシージャなどの特定のクエリは、実行中に無制限の量のメモリを消費する可能性があります。
• 大きなクエリを小さなクエリに分割して、実行時間の長いトランザクションを避けてください。
• データベース内のすべてのアクティブな接続とクエリを表示するには、SHOW FULL PROCESSLIST コマンドを使用します。JOIN または SORTS 操作で実行時間が長いクエリが発生する場合は、オプティマイザがプランを計算するのに十分な RAM が必要です。また、一時テーブルを必要とするクエリを特定した場合は、テーブルに割り当てる追加のメモリが必要です。
• 長時間実行されているトランザクション、メモリ使用量の統計情報、およびロックを表示するには、SHOW ENGINE INNODB STATUS コマンドを使用します。出力を確認し、バッファプールとメモリのエントリを確認します。BUFFER POOL AND MEMORY エントリは、「合計メモリ割り当て量」、「内部ハッシュテーブル」、「バッファプールサイズ」など、InnoDB のメモリ割り当てに関する情報を提供します。InnoDB Status は、ラッチ、ロック、デッドロックに関する追加情報を提供するのにも役立ちます。
• ワークロードでデッドロックが頻繁に発生する場合は、カスタムパラメータグループの innodb_lock_wait_timeout パラメータを変更してください。InnoDB は、デッドロックが発生すると、innodb_lock_wait_timeout 設定を使用してトランザクションをロールバックします。
• データベースのパフォーマンスを最適化するには、クエリが適切に調整されていることを確認してください。そうしないと、パフォーマンスの問題が発生し、待ち時間が長くなる可能性があります。
• Amazon RDS Performance Insights を使用すると、DB インスタンスを監視し、問題のあるクエリを特定できます。
• インスタンスがスロットリングしないように、CPU 使用率、IOPS、メモリ、スワップ使用量などの Amazon CloudWatch メトリックスを監視します。
• 使用可能なメモリが 95% に達したときに通知を受け取れるように、FreeableMemory メトリックスに CloudWatch アラームを設定します。インスタンスメモリの少なくとも 5% は空けておくのがベストプラクティスです。
• インスタンスをより新しいマイナーバージョンの MySQL に定期的にアップグレードしてください。古いマイナーバージョンには、メモリリーク関連のバグが含まれている可能性が高くなります。

関連情報

Amazon RDS のモニタリングの概要

十分なメモリがあるのに Amazon RDS DB インスタンスがスワップメモリを使用するのはなぜですか?