MySQL での大規模トランザクションによって発生する挿入の遅延ケースの分析

【質問】

INSERT 文は最も一般的な SQL 文の 1 つです。最近、MySQL サーバーが同時スレッド アラートを定期的に報告するようになりました。ログ情報から、実行に数十秒かかり、ログのフラッシュを待機し、クエリ終了状態にある、低速の挿入クエリが多数あることがわかります。

[予備分析]

リソース待ちの観点から見ると、ほとんどの時間が innodb_log_file ステージで費やされており、ディスクの問題が原因ではないかと疑われています。調査の結果、サーバー自体にハードウェアの問題は見つかりませんでした。

その後、スレッドが増えると、MySQL スレッドが待機している位置を特定するために、pstack の自動収集が有効になります。

【分析プロセス】

pstackの自動クロールを導入後、スレッド同時実行数>=50のアラーム（アラームが発行されるたびに大量の低速クエリが生成される）が6件発生し、そのうち3件は現場で捕捉されました。

同時スレッド数が増えると、 Stage_manager::enroll_for関数のグループコミットステージで50以上のスレッドがスタックします。

スレッド0x519c5940に対応するSQL文は次の通りであり、18秒間実行されている。

Stage_manager::enroll_for関数の目的は、flush_stage ステージで複数のスレッドのキューイングを実装することです。簡単に言えば、グループ化されたトランザクションの場合、リーダー スレッドによって送信され、他のスレッドはキュー待機状態になり、リーダー スレッドがスレッドのトランザクションを送信するのを待機します。

最初のスレッドの実行が遅い場合、後続のスレッドは待機状態になり、トランザクションのグループ全体をコミットできません。

このプロセスは次のように理解することもできます。

セッション A COMMIT --> ロックを取得 --> binlog を書き込む --> コミットが完了

セッション B COMMIT-->ロックを待機--------------------------->ロックを取得-->binlog を書き込む-->コミット完了

最初のスレッドの実行が遅いのはなぜでしょうか? アラーム期間中にログ ファイルを分析したところ、ログ ファイル内に 15M と 20M の 2 つの大きなトランザクションがあることがわかりました。

ログの詳細を確認してください。約 230,000 件のレコードを含む「delete from」という大規模なトランザクション削除ステートメントがあります。ROW モードで 230,000 件のレコードを削除すると、約 20 MB のログ ファイルが生成されます。ディスク フラッシュ時間が長いため、同じグループ内の他のトランザクションの送信がブロックされます。

取引の開始時間はアラーム時間と一致する

バックログされたグループ化されたトランザクションは、集中的にディスクにフラッシュされます。これはディスク インデックスに反映されます。問題発生期間中、disk_write_kbytes インデックスは大幅に増加します。

【最適化計画】

1. 開発者は、テーブル全体の大規模なトランザクションを削除するために delete from ステートメントを使用しないことをお勧めします。

[その他の回避策]

2. ROW モードの Binlog レコードでは大量のログが生成されます。MIXED モードに変更すると、理論的には問題を解決できます。

3. よりパフォーマンスの良いディスクに交換する

要約する

上記はこの記事の全内容です。この記事の内容が皆さんの勉強や仕事に一定の参考学習価値を持つことを願っています。ご質問があれば、メッセージを残してコミュニケーションしてください。123WORDPRESS.COM を応援していただきありがとうございます。