次の方法で共有

Azure Database for MySQL - フレキシブル サーバーでのメモリ不足に関する問題のトラブルシューティング

  • [アーティクル]

Azure Database for MySQL フレキシブル サーバー インスタンスが最適に実行されるようにするには、メモリの割り当てと使用が適切に行われるようにすることが非常に重要です。 既定では、Azure Database for MySQL フレキシブル サーバーのインスタンスを作成する場合、使用可能な物理メモリは、ワークロード用に選択したレベルとサイズによって異なります。 また、データベース操作を向上させるために、バッファーとキャッシュにメモリが割り当てられます。 詳細については、「How MySQL Uses Memory」を参照してください。

Azure Database for MySQL フレキシブル サーバーでは、可能な限り多くのキャッシュ ヒットを実現するためにメモリが消費されます。 その結果、メモリ使用率は、インスタンスの使用可能な物理メモリの 80% から 90% の間になることが多くなります。 クエリ ワークロードの進行状況に問題がない限り、これは問題ではありません。 ただし、次のような理由でメモリ不足の問題が発生する可能性があります。

  • 構成されたバッファーが大きすぎる。
  • 準最適なクエリが実行されている。
  • クエリで結合が実行され、大規模なデータ セットの並べ替えが行われている。
  • データベース サーバーの最大接続数の設定が高すぎる。

サーバーのメモリの大部分は、InnoDB のグローバル バッファーとキャッシュによって使用されます。これには、innodb_buffer_pool_size、innodb_log_buffer_size、key_buffer_size、query_cache_size などのコンポーネントが含まれます。

innodb_buffer_pool_size パラメーターの値は、InnoDB がデータベース テーブルとインデックス関連データをキャッシュするメモリの領域を指定します。 MySQL では、できるだけ多くのテーブルとインデックス関連のデータをバッファー プールに格納しようとします。 バッファー プールが大きくなると、ディスクに転送する必要がある I/O 操作が少なくなります。

メモリ使用率の監視

Azure Database for MySQL フレキシブル サーバーは、データベース インスタンスのパフォーマンスを測定するためのさまざまなメトリックを備えています。 データベース サーバーのメモリ使用率をより深く理解するには、ホストのメモリ使用率またはメモリ使用率のメトリックを確認してください。

メモリ使用率メトリックの表示のスクリーンショット。

メモリ使用率が急激に増加し、使用可能なメモリが急速に減少していることに気づいた場合は、他のメトリック (ホストの CPU 使用率、合計接続数、IO の割合など) を監視して、ワークロードの急激な増加が問題の原因かどうかを判断します。

データベース サーバーとの接続が確立されるたびに、一定量のメモリの割り当てが必要になることに注意することが重要です。 その結果、データベース接続が急増すると、メモリ不足が発生する可能性があります。

メモリ使用率が高くなる原因

MySQL でメモリ使用率が高くなるその他の原因をいくつか見てみましょう。 これらの原因は、ワークロードの特性によって異なります。

一時テーブルの増加

MySQL では、一時結果セットを格納するように設計された特別な種類のテーブルである "一時テーブル" が使われます。 一時テーブルは、セッション中に複数回再利用できます。 作成されたすべての一時テーブルはセッションに対してローカルであるため、セッションごとに異なる一時テーブルを使用できます。 大規模な一時結果セットのコンパイルを実行する多数のセッションが存在する運用システムでは、グローバル状態カウンター created_tmp_tables を定期的に確認する必要があります。これは、ピーク時に作成される一時テーブルの数を追跡します。 メモリ内一時テーブルの数が多いと、Azure Database for MySQL フレキシブル サーバーのインスタンスで使用可能なメモリが短時間で不足する可能性があります。

MySQL では、次の表に示すように、一時テーブルのサイズは 2 つのパラメーターの値によって決まります。

パラメーター 説明
tmp_table_size 内部メモリ内一時テーブルの最大サイズを指定します。
max_heap_table_size ユーザーが作成する MEMORY テーブルの最大サイズを指定します。

注意

内部メモリ内一時テーブルの最大サイズを決定するときに、MySQL では、tmp_table_size と max_heap_table_size のパラメーターに設定された値のうち低い方が考慮されます。

Recommendations

一時テーブルに関連するメモリ不足の問題をトラブルシューティングするには、次の推奨事項を考慮してください。

  • tmp_table_size 値を増やす前に、データベースが適切にインデックス付けされていることを確認します。特に、結合に関連し、操作別にグループ化された列について確認してください。 基になるテーブルで適切なインデックスを使用すると、作成される一時テーブルの数が制限されます。 インデックスを確認せずにこのパラメーターと max_heap_table_size パラメーターの値を増やすと、インデックスなしで非効率的なクエリが実行され、必要以上に多くの一時テーブルが作成される可能性があります。
  • ワークロードのニーズに対応するように、max_heap_table_size と tmp_table_size のパラメーターの値を調整します。
  • max_heap_table_size と tmp_table_size パラメーターに設定した値が低すぎると、一時テーブルがたびたびストレージにあふれ、クエリの待ち時間が長くなる可能性があります。 グローバル状態カウンターの created_tmp_disk_tables を使用すると、ディスクにあふれている一時テーブルを追跡できます。 created_tmp_disk_tables と created_tmp_tables の変数の値を比較して、作成された内部一時テーブルの合計数に対する、作成された内部のディスク上の一時テーブルの数を確認します。

テーブル キャッシュ

MySQL は、マルチスレッド システムとして、テーブルを複数のセッションで同時に別々に開くことができるように、テーブル ファイル記述子のキャッシュを保持します。 MySQL では、一定量のメモリと OS ファイル記述子を使用して、このテーブル キャッシュを保持します。 table_open_cache 変数は、テーブル キャッシュのサイズを定義します。

Recommendations

テーブル キャッシュに関連するメモリ不足の問題をトラブルシューティングするには、次の推奨事項を考慮してください。

  • パラメーター table_open_cache は、すべてのスレッドについて開いているテーブルの数を指定します。 この値を増やすと、mysqld で必要なファイル記述子の数が増えます。 グローバル状態表示カウンターの opened_tables 状態変数を確認することで、テーブル キャッシュを増やす必要があるかどうかを確認できます。 ワークロードに合わせて、このパラメーターの値を増分単位で増やします。
  • table_open_cache の設定が低すぎると、Azure Database for MySQL フレキシブル サーバーがクエリ処理に必要なテーブルを開いたり閉じたりする際にかかる時間が長くなることがあります。
  • この値の設定が高すぎると、メモリの使用量が増え、オペレーティング システムでファイル記述子が不足し、接続が拒否されたり、クエリの処理が失敗したりする可能性があります。

その他のバッファーとクエリ キャッシュ

メモリ不足に関連する問題をトラブルシューティングする場合、その他のいくつかのバッファーとキャッシュを使用して解決に役立てることができます。

ネット バッファー (net_buffer_length)

ネット バッファーは、各クライアント スレッドの接続とスレッドのバッファーのサイズであり、max_allowed_packet に指定された値まで拡張できます。 たとえば、大規模なクエリ ステートメントの場合、すべての挿入/更新の値が非常に大きいため、net_buffer_length パラメーターの値を大きくすると、パフォーマンスの向上に役立ちます。

結合バッファー (join_buffer_size)

結合バッファーは、結合でインデックスを使用できない場合に、テーブル行をキャッシュするために割り当てられます。 データベースにインデックスなしで実行される多数の結合がある場合、より高速な結合のためにインデックスを追加することを検討してください。 インデックスを追加できない場合は、接続ごとに割り当てられるメモリの量を指定する join_buffer_size パラメーターの値を増やすことを検討します。

並べ替えバッファー (sort_buffer_size)

並べ替えバッファーは、一部の ORDER BY と GROUP BY のクエリに対して並べ替えを実行するために使用されます。 SHOW GLOBAL STATUS の出力で 1 秒間に多数の Sort_merge_passes が表示される場合、クエリの最適化やより適切なインデックスの作成を使っても改善できない ORDER BY または GROUP BY 操作を高速化するには、sort_buffer_size の値を増やすことを検討します。

sort_buffer_size の値は、特に関連する情報がない限り、任意に増やさないようにしてください。 このバッファーのメモリは、接続ごとに割り当てられます。 MySQL のドキュメントのサーバー システム変数に関する記事に、Linux には 2 つのしきい値 (256 KB と 2 MB) があり、大きな値を使用するとメモリ割り当てが大幅に遅くなる可能性があることが明記されています。 そのため、sort_buffer_size 値は 2M を超えないようにしてください。パフォーマンスの低下によるデメリットがメリットより大きいためです。

クエリ キャッシュ (query_cache_size)

クエリ キャッシュは、クエリ結果セットのキャッシュに使用されるメモリ領域です。 query_cache_size パラメーターは、クエリ結果をキャッシュするために割り当てられるメモリの量を決定します。 既定では、クエリ キャッシュは無効になっています。 また、クエリ キャッシュは MySQL バージョン 5.7.20 では非推奨となり、MySQL バージョン 8.0 では削除されています。 現在、ソリューションでクエリ キャッシュが有効になっている場合は、無効にする前に、それに依存するクエリがないことを確認してください。

バッファー キャッシュ ヒット率の計算

Azure Database for MySQL フレキシブル サーバー環境でバッファー プールがワークロード要求に対応できるかどうかを把握するには、バッファー キャッシュ ヒット率が重要です。一般的な経験則として、バッファー プールキャッシュ ヒット率は常に 99% を超えることが良いとされています。

読み取り要求の InnoDB バッファー プール ヒット率を計算するには、SHOW GLOBAL STATUS を実行してカウンター "Innodb_buffer_pool_read_requests" と "Innodb_buffer_pool_reads" を取得し、次に示す数式を使用して値を計算します。

InnoDB Buffer pool hit ratio = Innodb_buffer_pool_read_requests / (Innodb_buffer_pool_read_requests + Innodb_buffer_pool_reads) * 100

次の例を考えてみます。

show global status like "innodb_buffer_pool_reads";
+--------------------------+-------+
| Variable_name | Value |
| +--------------------------+-------+ |
| Innodb_buffer_pool_reads | 197 |
| +--------------------------+-------+ |
| 1 row in set (0.00 sec) |

show global status like "innodb_buffer_pool_read_requests";
+----------------------------------+----------+
| Variable_name | Value |
| +----------------------------------+----------+ |
| Innodb_buffer_pool_read_requests | 22479167 |
| +----------------------------------+----------+ |
| 1 row in set (0.00 sec) |

上記の値を使用して、読み取り要求の InnoDB バッファー プール ヒット率を計算すると、次の結果が得られます。

InnoDB Buffer pool hit ratio = 22479167/(22479167+197) * 100
Buffer hit ratio = 99.99%

select ステートメントのバッファー キャッシュ ヒット率に加えて、DML ステートメントでは、InnoDB バッファー プールへの書き込みがバックグラウンドで行われます。 ただし、ページの読み取りまたは作成が必要なときに、クリーンなページが使用できない場合、最初にページがフラッシュされるのを待つ必要もあります。

Innodb_buffer_pool_wait_free カウンターは、これが発生した回数をカウントします。 0 より大きい Innodb_buffer_pool_wait_free は、InnoDB バッファー プールが小さすぎて、データベースに行われる書き込みに対応するためにバッファー プールのサイズまたはインスタンスのサイズを増やす必要があることを示す強力なインジケーターです。

Recommendations

  • クエリを実行するのに十分なリソースがデータベースに割り当てられていることを確認します。 場合によっては、バッファーとキャッシュがワークロードに対応するように、インスタンスのサイズをスケールアップして物理メモリを多くすることが必要になる場合があります。
  • 小さいトランザクションに分割することにより、大規模または実行時間の長いトランザクションを回避します。
  • システムが指定されたしきい値のいずれかを超えた場合に通知を受け取るように、"ホストのメモリ使用率" アラートを使用します。
  • Query Performance Insights または Azure Workbooks を使用して、問題が発生していたり実行に時間がかかったりするクエリを特定し、それらを最適化します。
  • 運用データベース サーバーの場合は、診断を定期的に収集して、すべてがスムーズに実行されていることを確認します。 そうでない場合は、特定した問題をトラブルシューティングして解決します。

次のステップ

Stack Overflow