Amazon RDS for MySQL データベースの確保できるメモリが少ないことをトラブルシューティングするにはどうすればよいですか?

簡単な説明

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

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

デフォルトでは、Amazon RDS for MySQL インスタンスを作成すると、データベースオペレーションを改善するためにバッファとキャッシュが割り当てられます。Amazon RDS for MySQL には、特定のオペレーションを実行するために内部一時テーブルを作成する内部メモリコンポーネント (key_buffers_size や 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 を実行するために 1 つの JOIN バッファを割り当てます。クエリが複数テーブルの JOIN を含み、すべてのクエリが JOIN バッファを必要とする場合、MySQL は、テーブルの総数より少ない 1 つの JOIN バッファを割り当てます。セッション変数を大きすぎる値に設定すると、クエリが最適化されていない場合に問題が発生する可能性があります。join_buffer_size、join_buffer_size、sort_buffer_size など、セッションレベルの変数に最小メモリを割り当てることができます。詳細については、DB パラメータグループを使用するを参照してください。

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

パフォーマンススキーマ

Amazon RDS for MySQL で Performance Insights のパフォーマンススキーマを有効にした場合、パフォーマンススキーマによってメモリが消費される可能性があります。パフォーマンススキーマが有効になっている場合、MySQL はインスタンスの起動時およびサーバー操作中に内部バッファを割り当てます。パフォーマンススキーマがメモリを使用する方法についての詳細は、パフォーマンススキーマのメモリ割り当てモデルに関する MySQL のドキュメントを参照してください。

パフォーマンススキーマのテーブルとともに、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 メトリクス

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

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 または SORT オペレーションで長時間クエリが実行されている場合、オプティマイザがプランを計算するのに十分な量の RAM が必要です。また、一時テーブルを必要とするクエリを特定した場合は、テーブルに割り当てる追加のメモリが必要です。
• 長時間実行されているトランザクション、メモリ使用率の統計、およびロックを表示するには、SHOW ENGINE INNODB STATUS コマンドを使用します。出力を確認し、BUFFER POOL AND MEMORY を確認します。BUFFER POOL AND MEMORY エントリは、[Total Memory Allocated] (割り当てられた合計メモリ)、[Internal Hash Tables] (内部ハッシュテーブル)、[Buffer Pool Size] (バッファプールサイズ) など、InnoDB のメモリ割り当てに関する情報を提供します。InnoDB Status は、ラッチ、ロック、デッドロックに関する追加情報を提供するのにも役立ちます。
• ワークロードでデッドロックが発生することが多い場合は、カスタムパラメータグループの innodb_lock_wait_timeout パラメータを変更します。InnoDB は、デッドロックが発生したときにトランザクションをロールバックするために innodb_lock_wait_timeout 設定を使用します。
• データベースのパフォーマンスを最適化するには、クエリが適切に調整されていることを確認してください。適切に調整されていない場合、パフォーマンスの問題が発生し、待機時間が長くなる可能性があります。
• Amazon RDS Performance Insights を使用すると、DB インスタンスをモニタリングし、問題のあるクエリを特定できます。
• CPU 使用率、IOPS、メモリ、スワップ使用率などの Amazon CloudWatch メトリクスをモニタリングして、インスタンスがスロットルしないようにします。
• FreeableMemory メトリクスに CloudWatch アラームを設定して、利用可能なメモリが 95% に到達したときに通知を受け取るようにします。インスタンスメモリを 5% 以上空けておくのがベストプラクティスです。
• MySQL のより新しいマイナーバージョンにインスタンスを定期的にアップグレードします。より古いマイナーバージョンでは、メモリリーク関連のバグが含まれる可能性が高くなります。

---

関連情報

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

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