MySQLのパフォーマンスが突然低下する理由

場合によっては、SQL ステートメントが通常どおり、非常に速く実行される状況に遭遇することがあります。しかし、ある時点で突然実行が非常に遅くなり、このシナリオは再現できず、ランダムに送信することしかできなくなります。

SQL実行が突然遅くなる理由

以前、MySQL Redo ログについて説明した際に、WAL の仕組みについて触れました。MySQL の更新速度を確保するために、更新操作を実行すると、更新内容が最初に redo ログに書き込まれ、システムがアイドル状態のときに redo ログの内容がディスクに適用されます。

メモリ データ ページ (REDO ログ) とディスク データ ページの内容が矛盾している場合、そのメモリは「ダーティ ページ」とも呼ばれます。メモリ データがディスクに書き込まれた後、データは一貫性を保ち、メモリ ページは「クリーン ページ」と呼ばれます。

メモリ データがディスクに書き込まれるプロセスはフラッシュ プロセスと呼ばれます。 SQL の実行が突然非常に遅くなり、パフォーマンスが低下します。理由はフラッシュ操作に関連している可能性があります。

フラッシュ操作が進行中の場合、更新操作は REDO ログが書き込まれるまで待機するためです。

フラッシュ操作の原因

シナリオ 1: REDO ログがいっぱいです。この時点で、システムは更新操作を停止し、チェックポイントを前方に押し出して、REDO ログに書き込みを続行するためのスペースを残します。

ここでは、CP から CP' までのギャップがディスクに書き込まれ、この部分がクリーン ページになったと仮定します。この時点で、この領域に書き込み位置を書き込むことができます。

シナリオ 2: システムのメモリが不足し、新しいメモリ ページが必要な場合、メモリが不十分であり、一部のデータ ページを削除する必要があることがわかります。データ ページが削除時にダーティである場合、ダーティ ページをディスクに書き込む必要があります。

このとき、名前付きREDOログの内容がログに記録されているのですが、メモリがいっぱいなら削除すればいいのではないでしょうか？次回読み込むときに、REDO ログの内容をディスクに適用します。

メモリを直接クリアしないことを選択した理由は、パフォーマンス上の理由です。データをクエリする場合、次の 2 つの状況があります。

• まず、データページはメモリ内にあり、メモリは正しい結果であり、直接返されます。
• メモリ内にデータがないので、データファイルからメモリに読み込みます。

したがって、これはより効率的です。

シナリオ 3: システムがアイドル状態のときに、MySQL はフラッシュ操作を開始します。

シナリオ 4: MySQL が正常にシャットダウンされると、メモリ内のダーティ ページがディスクにフラッシュされます。

フラッシュがパフォーマンスに与える影響

3 番目と 4 番目のシナリオは比較的正常であり、パフォーマンスの問題を考慮する必要はありません。

InnoDB は最初のシナリオを回避しようとします。この場合、システム全体が更新を受け入れなくなるためです。

しかし、インスタンスのメモリが 128 GB で innodb_io_capacity が 20000 に設定されているなど、人為的な構成エラーが発生することがあります。通常、REDO ログを 4 つの 1 GB ファイルに設定することをお勧めします。しかし、設定エラーのため、100M ファイルに設定されました。

REDO ログの設定が小さすぎるため、すぐにいっぱいになってしまいます。書き込み位置はチェックポイントに追いつき続けます。この時点で、システムはすべての更新を停止し、チェックポイントを進めることしかできません。

パフォーマンスは、ディスク IO が非常に小さいですが、断続的にパフォーマンスが低下します。

2 番目のシナリオでは、メモリが不足している場合、 InnoDB はバッファ プールを使用してメモリを管理します。

バッファ プール内のメモリ ページは次の 3 つの状態になります。

• 未使用のデータページ
• 使ってみましたがきれいなページです
• 使用済み、汚れたページ
  

各データ ページ ヘッダーには LSN があり、これは 8 バイトで、変更ごとに増加します。

この LSN をチェックポイント LSN と比較します。チェックポイント LSN より小さい方がクリーン ページである必要があります。

InnoDB の戦略は、可能な限り多くのメモリを使用することであるため、長時間実行されるデータベースでは未使用のページはほとんどありません。

読み取りたいデータ ページがメモリ内に存在しないことが判明した場合、バッファー プールからデータ ページを申請する必要があります。そして、最も長い間使われていないデータページはメモリから削除されます。

クリーン ページの場合は、直接解放できます。ダーティ ページの場合は、再利用する前に、まずディスクにフラッシュしてクリーン ページにする必要があります。次の状況でダーティ ページがフラッシュされると、パフォーマンスが大幅に低下します。

削除する必要があるダーティ ページが多すぎると、クエリの応答時間が長くなります。
ログがいっぱいなので更新がブロックされています。
この問題を解決するために、InnoDB はダーティ ページの割合を制御するメカニズムを使用して、上記の状況を回避します。

ダーティページをフラッシュするための InooDB 制御戦略

InnoDB では、innodb_io_capacity パラメータを使用して、ホストの現在のディスク容量を InnoDB に伝えます。この値をディスクの IOPS に設定することをお勧めします。

fio ツールを使用してテストできます。

fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

innodb_io_capacityによって発生するパフォーマンスの問題はよくあります。たとえば、システム スループット (TPS) が低く、書き込みが遅いのに、ディスク IO が高くない場合があります。パラメータが誤って設定されている可能性があります。たとえば、 innodb_io_capacityの値が非常に低く設定されているが、使用されているディスクが SSD であるため、InnoDB はシステム容量が非常に少ないと判断し、ダーティ ページのフラッシュが非常に遅くなります。これにより、ダーティ ページが蓄積され、クエリと更新のパフォーマンスに影響します。

InnoDB は、ディスクをフラッシュする際に主に次の 2 つの要素を考慮します。

1. ダーティページの割合
   
2. REDOログ書き込み速度
   

まず、これら 2 つの要素を個別に使用して 2 つの数値が計算されます。

innodb_max_dirty_pages_pctダーティ ページ比率の上限、デフォルトは 75%。

InnoDB は、ダーティ ページ (M) の割合に基づいて 0 ～ 100 の範囲の数値を計算します。このプロセスはF1（M）と呼ばれます。

# M ダーティページ比率 F1(M)
{
 M>=innodb_max_dirty_pages_pctの場合
  100 を返します。
 100*M/innodb_max_dirty_pages_pct を返します。
}

さらに、InnoDB がログを書き込むたびに、シーケンス番号 N が付けられます。次に、N に基づいて 0 から 100 までの数値が計算されます。この計算プロセスは F2(N) と呼ばれます。

N: 現在書き込まれているシーケンス番号とチェックポイントに対応するシーケンス番号の差。

最後に、F1(M)とF2(N)の2つの値に基づいて、大きい方の値がRとして取得され、エンジンはinnodb_io_capacity * Rに応じてダーティページのフラッシュ速度を制御できます。

したがって、クエリを実行するかどうかに関係なく、メモリ データ ページにデータをロードし、ダーティ ページを削除する必要があります。アップデート中であろうとディスクフラッシュ中であろうと、MySQL のパフォーマンスが低下する可能性があります。

この状況を回避するには、 innodb_io_capacityの値を適切に設定し、ダーティ ページの割合が 75% に近づかないように注意する必要があります。

ダーティ ページ比率は、次の方法で取得できます。

mysql> performance_schema を使用します。
mysql> global_status から @a に VARIABLE_VALUE を選択します。ここで、VARIABLE_NAME は 'Innodb_buffer_pool_pages_dirty' です。
VARIABLE_NAME = 'Innodb_buffer_pool_pages_total' の場合、 global_status から @b に VARIABLE_VALUE を選択します。
@a/@b を選択します。

初回だけでなく、クエリ操作の進行中にダーティ ページをフラッシュする必要がある場合、ダーティ ページの隣接ページもダーティ ページであれば、隣接ページも一緒にフラッシュされます。その隣にもダーティ ページがある場合は、引き続きフラッシュされます。これがフラッシュが遅すぎる理由です。

この動作はinnodb_flush_neighborsによって制御できます。値が 1 の場合、上記のメカニズムがオンになり、値が 0 の場合、オフになります。

機械式ハードディスクの場合、IOPS は一般的に数百程度であるため、ランダム IO を大幅に削減できます。ランダム IO を削減するとパフォーマンスが向上します。

ただし、SSD などの IOPS が高いデバイスを使用する場合は、IOPS がボトルネックにならないことが多く、IOPS をオフにするだけで SQL ステートメントの応答時間を短縮できます。

8.0 では、デフォルトは 0 です。

要約する

この記事では主に、WAL 中のフラッシュ操作によって MySQL のパフォーマンスが突然低下する可能性がある理由について説明します。

原因は通常、メモリ不足によるもので、適切なinnodb_io_capacityパラメータを設定することで InnoDB フラッシュ プロセスを制御できます。

以上がMySQLのパフォーマンスが急激に低下した原因の詳細です。MySQLのパフォーマンス低下の詳細については、123WORDPRESS.COMの他の関連記事にも注目してください。