MySQL の遅いクエリの遅いコミットと、binlog の遅いトランザクションの違いは何ですか?

1. 問題の原因

遅いクエリとバイナリログの遅いトランザクションは、パフォーマンスの問題を分析するときによく使用される方法です。最近、遅いクエリを分析していたところ、低速なコミット ステートメントが多数含まれていることがわかりましたが、binlog の遅いトランザクションを分析する際にマッチングを完了できませんでした。たとえば、この期間中にコミット ステートメントが 1,000 件ある場合でも、遅いトランザクションは 100 件しかない可能性があります。これは大きすぎる差ですが、なぜこの現象が発生するのでしょうか。

2. それぞれの判定方法

• 明示的に送信(挿入)したトランザクションの場合、トランザクションが遅い場合は通常次のとおりです。

GTID_LOG_EVENT と XID_EVENT は、コマンド「COMMIT」が開始された時刻です。

• 「INSERT」コマンドが初めて開始されるのは QUERY_EVENT です。

• MAP_EVENT/WRITE_ROWS_EVENT は、各 ‘Insert’ コマンドが開始される時刻です。

• したがって、通常、XID_EVENT の時間から QUERY_EVENT の時間を減算すると、トランザクション時間が遅くなります。

  もちろん、自動的に送信される場合、このように計算することはできません。
、各イベントはステートメントが開始される時刻であるためです。

スローコミットの可能性

• スローコミットが発生する可能性が最も高いのは、バイナリログのフラッシュまたは半同期スレーブ ACK の待機であることがわかっています。ただし、binlog の XID EVENT の時間にはこの部分の時間は含まれていないため、binlog の低速トランザクションと低速クエリのコミット レコードは同じ期間ではありません。

簡単な説明

• 次のトランザクションを例に簡単に説明します

  begin;
  insert into it values(10);
  commit;        
  -- insert语句执行      -> QUERY_EVENT时间(T1)  
  -- insert语句执行完成,判定insert语句是否为慢查询(T2)          
  -- commit语句执行      -> GTID_LOG_EVENT和XID_EVENT时间(T3)
     flush
     fsync
                    -----> 传输binlog (sync_binlog=1)
                    <----   等待ACK   (rpl_semi_sync_master_wait_point=AFTER_SYNC)
     commit
  -- commit语句执行完成,判定commit语句是否为慢查询(T4)

insert文が遅いかどうかの判断基準はT2-T1(-lock time)

• commit文が遅いかどうかの判断基準はT4-T3

• 遅いトランザクションを判断する基準は T3-T1

• 遅いトランザクションの判断と遅いコミットの判断にはほとんど重複がありません。クエリが遅いため、この状況は正常であることが次のように証明されます。

3. 証拠

メイン データベース: 準同期タイムアウトは 999999999 です。

• スレーブ ライブラリ: sync_relay_log=1 を設定し、MYSQL_BIN_LOG::flush_and_sync 関数にブレークポイントを設定します。この関数は、各イベントがリレー ログに書き込まれた後、sync_relay_log=1 の影響を受けます。ライブラリ 市場に投入する必要がある決定機能に影響を与えます。

• このように、ブレークポイントで待機するとコミット時間が大幅に長くなります。また、次のように、遅い半同期が遅いコミットに影響することも証明されています。 ## 遅いクエリと binlog を分析しましょう sleep(10) を追加すると、挿入が速すぎるため、トランザクションのコミット時間が長くなります。

#binlog 遅いトランザクション 22:20:54(T2) - 22:20:43(T1) = 約 11 秒 (sleep(10) を追加しました)

begin;
select now();   -T1
insert into it values(10);
select sleep(10);
select now();   -T2
commit; (断点在从库生效卡主ack) -T3
select now();   -T4

结果
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select now();      -T1
+---------------------+
| now()               |
+---------------------+
| 2022-06-12 22:20:43 |
+---------------------+
1 row in set (0.00 sec)

mysql> insert into it values(10);
Query OK, 1 row affected (0.10 sec)

mysql> select sleep(10);

+-----------+
| sleep(10) |
+-----------+
|         0 |
+-----------+
1 row in set (10.01 sec)

mysql> select now();      -T2 AND T3
+---------------------+
| now()               |
+---------------------+
| 2022-06-12 22:20:54 |
+---------------------+
1 row in set (0.00 sec)

mysql> commit;         
Query OK, 0 rows affected (21.64 sec)

mysql> select now();    -T4
+---------------------+
| now()               |
+---------------------+
| 2022-06-12 22:21:15 |
+---------------------+
1 row in set (0.00 sec)

• スロークエリのコミットが遅い 22:21:15(T4) - 22:20:54(T3) = 21 秒

# at 12221
#220612 22:20:54 server id 613306  end_log_pos 12286 CRC32 0x3e019332   GTID    last_committed=40       sequence_number=41      rbr_only=yes
/*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/;
SET @@SESSION.GTID_NEXT= '00320cc8-39f9-11ec-b5ba-000c2929706d:41'/*!*/;
# at 12286
#220612 22:20:43 server id 613306  end_log_pos 12360 CRC32 0x8dcde193   Query   thread_id=43    exec_time=1     error_code=0
SET TIMESTAMP=1655043643/*!*/;
BEGIN
/*!*/;
# at 12360
#220612 22:20:43 server id 613306  end_log_pos 12409 CRC32 0x0db68582   Rows_query
# insert into it values(10)
# at 12409
#220612 22:20:43 server id 613306  end_log_pos 12456 CRC32 0x363a48c7   Table_map: `mysemi`.`it` mapped to number 124
# at 12456
#220612 22:20:43 server id 613306  end_log_pos 12496 CRC32 0xd44e43f3   Write_rows: table id 124 flags: STMT_END_F
### INSERT INTO `mysemi`.`it`
### SET
###   @1=10 /* INT meta=0 nullable=1 is_null=0 */
# at 12496
#220612 22:20:54 server id 613306  end_log_pos 12527 CRC32 0x4d8d2c64   Xid = 547
COMMIT/*!*/;

ここで、スロー クエリ レコードのスロー コミットが明らかにスロー トランザクションに含まれていないことは明らかです。

以上がMySQL の遅いクエリの遅いコミットと、binlog の遅いトランザクションの違いは何ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。