MySQLアラームの詳細な分析と処理

最近、あるサービスにアラームが発生し、耐えられなくなっています。アラーム情報は次のとおりです。

メトリック:mysql.innodb_row_lock_waits タグ:port=4306、service=xxxx diff(#1): 996>900

一般的な意味は、データベース監視インジケータinnodb_row_lock_waitsが現在900のしきい値を超えているということです。

しかし、恥ずかしいことに、アラーム発生後に環境を確認するたびに、得られる情報が非常に限られていました。スローログとエラーログには分析に十分な情報がありませんでした。しばらくして、落ち着いて問題の原因を分析し始めました。

まず、このアラーム情報の時刻は、ある程度規則的であるようです。最近数日間のアラーム時刻を比較したところ、まだ比較的規則的であることがわかりました。では、システムレベルではどのようなタスクがトリガーされるのでしょうか。関連するタスク構成を調べて比較したところ、1 分ごとに実行されるスケジュールされたタスクがあることがわかりました。しかし、ここで疑問が生じます。1 分ごとに実行される場合、特定の時間に処理結果に大きな違いが生じるのはなぜですか?もちろん、この現象の説明はまだ始まりに過ぎません。

実は、この点を証明するのは非常に簡単です。今日は、様子見モードを採用しました。アラームが発生した時刻の前後に一般ログを開きました。ログ出力から、操作の頻度は比較的限られていることがわかります。

すぐに定期的なアラームが届いたので、関連する一般ログ レコードをキャプチャし始めました。たとえば、11:18 では、次のモデルを使用して関連するログを取得できます。まず、さまざまな DML および実行操作をキャプチャするための一時的な一般ログ ファイルを取得します。

cat general.log|grep -E "挿入|削除|更新|選択|実行" > general_tmp.log

11:18 を例に挙げてみましょう。1 分または 2 分前と後の時間を比較してみましょう。結果は次のようになります。

# general_tmp.log を省略 |grep "11:18"|wc -l

400

# general_tmp.log を省略 |grep "11:17"|wc -l

666

# general_tmp.log を省略 |grep "11:16"|wc -l

15

警報が鳴ってから約1分後に数字が一致したことが判明した。

このテーブルのデータ量は 200 万を超えており、テーブル構造は次のとおりです。

テーブル `task_queue` を作成します (
 `AccID` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自動増分ID',
 `TaskStepID` bigint(20) デフォルト NULL コメント 'タスクステップID task_step_conf',
 `QOrder` int(11) DEFAULT NULL COMMENT 'キューソートtask_step_confi.Step_ID',
 `QState` tinyint(4) DEFAULT '1' COMMENT 'キューの状態 1: 実行待ち 2: 実行中 3: 実行成功 4: 実行失敗',
 `QExcCount` int(11) デフォルト '1' コメント '実行回数',
 `CrtTime` datetime DEFAULT NULL COMMENT '作成時刻',
 `ModTime` datetime DEFAULT NULL COMMENT '変更時刻',
 主キー (`AccID`)、
 キー `idx_taskstepid` (`TaskStepID`)、
 キー `idx_qstate` (`QState`)
) ENGINE=InnoDB AUTO_INCREMENT=3398341 デフォルト文字セット=utf8

ログの分析と比較に基づいて、基本的に SQL が更新操作中であると識別できます。SQL 実行プランは次のとおりです。

>>説明 update task_queue set QState=1,QExcCount=QExcCount+1,modtime=now() ここで、QState=0 かつ taskstepid =411\G
************************** 1. 行 ****************************
   id: 1
 選択タイプ: 更新
  テーブル: task_queue
 パーティション: NULL
   タイプ: インデックスマージ
可能なキー: idx_taskstepid、idx_qstate
   キー: idx_qstate、idx_taskstepid
  キーの長さ: 2,9
   参照: NULL
   行数: 11
  フィルター: 100.00
  追加: intersect(idx_qstate,idx_taskstepid); の使用 where の使用 temporary の使用

この実行結果では、key_lenは2,9であり、これは以前のken_lenの計算ルールとは異なります。 Extra 列には、これが交差プロセスであることが明確に示されています。特別なのは、セカンダリ インデックス レベルに基づくプロセスであることです。オプティマイザー レベルには、関連するパラメーター index_merge_intersection があります。

MySQL では、主キーが第一級市民であり、セカンダリ インデックスは最終的に処理のために主キー レベルにマッピングされることがわかっています。インデックス レベルの交差は、実際には左手と右手に少し似ています。左手は主キー ID のバッチにマッピングされたデータ結果に対応し、右手は別の主キー ID のバッチにマッピングされたデータ結果に対応します。2 つの主キー ID 値は交差計算されます。では、現在のシナリオでは、インデックス レベルの交差は良いアイデアでしょうか?

ここでは、分析のために 3 つの比較シナリオを想定しました。まず、これは更新ステートメントです。後続のテストの再現性を確保するために、これを選択ステートメントに変換できます。

QState=0かつtaskstepid=411の場合、task_queueから*を選択します。

したがって、私たちの比較テストは、比較と分析のためのクエリ ステートメントに基づいています。

シナリオ1: オプティマイザはデフォルトのindex_merge_intersectionを有効にしたまま、プロファイルに基づいて実行の詳細を抽出します。

> select * from task_queue where QState=0 and taskstepid =411\G を説明します
************************** 1. 行 ****************************
   id: 1
 選択タイプ: シンプル
  テーブル: task_queue
 パーティション: NULL
   タイプ: インデックスマージ
可能なキー: idx_qstate、idx_taskstepid
   キー: idx_qstate、idx_taskstepid
  キーの長さ: 2,9
   参照: NULL
   行数: 11
  フィルター: 100.00
  追加: intersect(idx_qstate,idx_taskstepid); の使用 where の使用
セットに 1 行、警告 1 件 (0.00 秒)

プロフィール情報は次のとおりです。

シナリオ2: オプティマイザはindex_merge_intersectionをオフにし、プロファイルに基づいて比較します。

>セッションoptimizer_switch='index_merge_intersection=off'を設定します。


> select * from task_queue where QState=0 and taskstepid =411\G を説明します
************************** 1. 行 ****************************
   id: 1
 選択タイプ: シンプル
  テーブル: task_queue
 パーティション: NULL
   タイプ: ref
可能なキー: idx_qstate、idx_taskstepid
   キー: idx_qstate
  キーの長さ: 2
   参照: 定数
   行数: 1451
  フィルター: 0.82
  追加: where の使用
セットに 1 行、警告 1 件 (0.00 秒)

プロフィール情報は次のとおりです。

シナリオ3: インデックスを再構築し、比較分析を実行する

ビジネス ロジックによると、複合インデックスを作成すると、結果セットのサイズを大幅に削減できますが、idx_qstat インデックスは保持されるため、一部のビジネスは引き続き正常に使用できます。

> テーブル task_queue を変更してキー idx_taskstepid を削除します。
> テーブル task_queue を変更し、キー `idx_taskstepid` (`TaskStepID`,QState) を追加します。
QState=0 かつ taskstepid =411\G の場合、task_queue から * を選択します。
************************** 1. 行 ****************************
      id: 1
 選択タイプ: シンプル
    テーブル: task_queue
  パーティション: NULL
     タイプ: ref
可能なキー: idx_qstate、idx_taskstepid
     キー: idx_taskstepid
   キーの長さ: 11
     参照: const、const
     行数: 1
   フィルター: 100.00
    追加: NULL
セットに 1 行、警告 1 件 (0.00 秒)

プロフィール情報は次のとおりです。

インデックス再構築により、「データ送信」部分が 2 桁削減されたことがはっきりとわかります。

したがって、次に行うことは、理由と証拠に基づいてさらに分析と検証を行うことであり、待機プロセスはもう躊躇しません。 1 日が経過しましたが、アラームは受信されていません。これは、職場でこれらのアラームを過小評価すべきではないことを改めて示しています。

要約する

これで、MySQL アラーム分析と処理に関するこの記事は終了です。MySQL アラーム処理に関するより関連性の高いコンテンツについては、123WORDPRESS.COM の以前の記事を検索するか、以下の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。