MySQL 느린 쿼리의 느린 커밋과 binlog의 느린 트랜잭션의 차이점은 무엇입니까?

1. 문제의 원인

성능 문제를 분석할 때 느린 쿼리와 binlog 느린 트랜잭션이 일반적으로 사용되는 방법입니다. 최근 느린 쿼리를 분석하던 중 느린 쿼리가 다수 포함되어 있는 것을 발견했는데, binlog 느린 트랜잭션을 분석할 때 매칭이 완료되지 못했습니다. 예를 들어, 이 기간 동안 커밋 문은 1,000개일 수 있지만 느린 트랜잭션은 100개만 있을 수 있습니다. 이는 너무 큰 차이인데 왜 이런 현상이 발생할까요?

2. 각각의 판단 방법

• 느린 트랜잭션 제출(삽입)을 나타내는 트랜잭션의 경우 일반적으로 다음과 같습니다.

• GTID_LOG_EVENT 및 XID_EVENT는 "COMMIT" 명령이 시작되는 시간입니다.

• "INSERT" 명령이 처음 시작되는 시간은 QUERY_EVENT입니다.

• MAP_EVENT/WRITE_ROWS_EVENT는 각 ‘삽입’ 명령이 시작되는 시간입니다.

그래서 보통 XID_EVENT 시간에서 QUERY_EVENT 시간을 빼면 거래 시간이 느려지는데요, 물론 자동으로 제출된다면 이렇게 계산할 수는 없습니다. 성명이 개시됩니다.

• 느린 커밋 가능성

느린 커밋이 발생할 가능성이 가장 높은 곳은 binlog 플러시 또는 반동기화된 슬레이브 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)

• 삽입문이 느린지 판단하는 기준은 T2-T1(-잠금 시간)

• Commit 문이 느린지 판단하려면 느림의 기준은 T4-T3

• 느린 트랜잭션의 판단 기준은 T3-T1

따라서 느린 트랜잭션의 판단과 느린 트랜잭션의 판단은 거의 겹치지 않는다. 느린 쿼리에서 느린 커밋을 판단하므로 이러한 상황은 아래에서 입증됩니다.

3. 증명

• 메인 데이터베이스: 반동기화 시간 제한은 999999999입니다.

• Slave 라이브러리: sync_relay_log=1로 설정하고, MYSQL_BIN_LOG::flush_and_sync 함수에 중단점을 설정합니다. 이 함수는 sync_relay_log=1에 의해 각 이벤트가 릴레이 로그에 기록된 후 디스크에 배치되어야 하는 판단 함수입니다. .

이런 식으로 중단점에서 인위적으로 기다리면 커밋 시간이 크게 길어집니다. 이는 또한 다음과 같이 느린 반동기화가 느린 커밋에 영향을 미친다는 것을 증명합니다.

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)

느린 쿼리와 binlog를 분석해 보겠습니다. 여기서 sleep(10)은 삽입이 너무 빠르기 때문에 트랜잭션 커밋 시간을 연장합니다.

• binlog 느린 트랜잭션 22:20:54(T2) - 22:20:43(T1) = 약 11초(sleep(10) 추가)

# 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/*!*/;

• 느린 쿼리의 커밋이 느림 22 :21:15(T4) - 22:20:54(T3) = 21초

# Time: 2022-06-12T22:21:15.746223Z
# User@Host: root[root] @ localhost []  Id:    43
# Schema: mysemi  Last_errno: 0  Killed: 0
# Query_time: 21.641090  Lock_time: 0.000000  Rows_sent: 0  Rows_examined: 0  Rows_affected: 0
# Bytes_sent: 11
SET timestamp=1655043675;
commit;

여기서는 느린 쿼리 레코드의 커밋 속도가 느린 트랜잭션에 분명히 포함되지 않는다는 것이 분명합니다.

위 내용은 MySQL 느린 쿼리의 느린 커밋과 binlog의 느린 트랜잭션의 차이점은 무엇입니까?의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!