MySQL 복제에서 병렬 복제를 구현하는 방법

전통적인 단일 스레드 복제 지침

우리 모두 알고 있듯이, MySQL 버전 5.6 이전에는 마스터-슬레이브 복제의 슬레이브 노드에 두 개의 스레드, 즉 I/O 스레드와 SQL 스레드가 있었습니다.

• I/O 스레드는 바이너리 로그에서 이벤트를 수신하여 릴레이 로그에 기록하는 역할을 합니다.

• SQL 스레드는 릴레이 로그를 읽고 데이터베이스에서 다시 재생합니다.

위 방법을 사용하면 가끔 지연이 발생하는데, 마스터 노드와 슬레이브 노드 사이에 지연이 발생할 수 있는 상황은 무엇인가요?

• 1. 메인 데이터베이스에서 대규모 트랜잭션(예: 대규모 테이블 구조 변경)을 실행합니다. .

• 2. 기본 데이터베이스에 대한 대규모 변경(예: 대규모 삽입, 업데이트 및 삭제 작업)

• 3. ROW 동기화 모드에서는 기본 데이터베이스 테이블에 자주 업데이트되는 기본 키가 없습니다.

• 4. 데이터베이스 매개변수 구성이 비합리적이며 슬레이브 노드 성능에 병목 현상이 있습니다(예: 슬레이브 노드 트랜잭션 로그 설정이 너무 작아서 디스크 플러시가 자주 발생함).

• 5. 네트워크 환경이 불안정하여 노드 IO 스레드에서 binlog를 읽는 데 지연 및 재연결이 발생합니다.

• 6. 마스터-슬레이브 하드웨어 구성이 다르며 슬레이브 노드의 하드웨어 리소스 사용량이 상한에 도달합니다. (예: 마스터 노드 SSD 디스크, 슬레이브 노드 SAS 디스크)

위의 지연 이유를 대략적으로 분류할 수 있습니다.

• 1. 하드웨어 문제(디스크 IO, 네트워크 IO 등 포함)

• 2.

• 3. 데이터베이스 설계 문제.

• 4. 기본 데이터베이스가 대량으로 변경되고 슬레이브 노드의 단일 스레드 SQL 처리가 시기적절하지 않습니다.

요약

위의 이유를 분석한 결과 마스터-슬레이브 지연을 줄이기 위해서는 하드웨어 조건 개선 외에도 DBA가 데이터베이스 설계 및 구성에도 주의를 기울여야 함을 알 수 있습니다. 마지막으로 슬레이브 노드의 동시 처리 능력을 향상시키는 것이 필요하며, 핵심은 데이터 충돌 및 확인 문제를 해결하는 방법입니다. 다중 스레드 복구를 전제로 오류 복구 위치 지점을 지정합니다.

MySQL5.6은 라이브러리 수준 병렬 복제를 기반으로 합니다

인스턴스에 여러 데이터베이스가 있는 경우 여러 스레드를 시작할 수 있으며 각 스레드는 하나의 데이터베이스에 해당합니다. 이 모드에서는 슬레이브 노드가 여러 스레드를 시작합니다. 스레드는 Coordinator와 WorkThread라는 두 가지 범주로 나뉩니다. Coordinator 和 WorkThread。

• 线程分工执行逻辑

Coordinator线程负责判断事务是否可以并行执行，如果可以并行就把事务分发给WorkThread线程执行，如果判断不能执行，如DDL，跨库操作等，就等待所有的worker线程执行完成之后，再由Coordinator执行。

• 关键配置信息

slave-parallel-type=DATABASE

• 方案不足点

这种并行复制的模式,只有在实例中有多个DB且DB的事务都相对繁忙的情况下才会有较高的并行度，但是日常维护中其实单个实例的的事务处理相对集中在一个DB上。大多数延迟情况都基于热点表的出现而观察得出。如果能够提供基于表的并行度是一个很好方法。

MySQL5.7基于组提交的并行复制

组提交说明

简单来说就是在双1的设置下，事务提交后即刷盘的操作改为多个事务合并成一组事务再进行统一刷盘，这样处理就降低了磁盘IO的压力。详细资料参考老叶茶馆关于组提交的说明推文https://mp.weixin.qq.com/s/rcPkrutiLc93aTblEZ7sFg

一组事务同时提交也就意味着组内事务不存在冲突，故组内的事务在从节点上就可以并发执行，问题在于如何区分事务是否在同一组中的，于是在binlog中出现了两个新的参数信息last_committed 和 sequence_number

• 如何判断事务在一个组内呢?

解析binlog可以发现里面多了last_committed和sequence_number两个参数信息，其中last_committed存在重复的情况。

• sequence_number # 这个值指的是事务提交的序号，单调递增。

• last_committed

스레드 분업 실행 로직
코디네이터 스레드는 트랜잭션이 병렬로 실행될 수 있는지 여부를 결정하는 역할을 담당합니다. 병렬로 실행될 수 있으면 트랜잭션이 분산됩니다. WorkThread 스레드에 DDL, 크로스 라이브러리 작업 등 실행이 불가능하다고 판단되는 경우 , 모든 작업자 스레드가 실행을 완료할 때까지 기다린 다음 코디네이터에서 실행합니다.

• 주요 구성 정보

  🎜🎜

  [root@mgr2 GreatSQL]# mysqlbinlog mysql-bin.0000002 | grep last_committed
  GTID last_committed=0 sequence_number=1
  GTID last_committed=0 sequence_number=2
  GTID last_committed=2 sequence_number=3
  GTID last_committed=2 sequence_number=4
  GTID last_committed=2 sequence_number=5
  GTID last_committed=2 sequence_number=6
  GTID last_committed=6 sequence_number=7
  GTID last_committed=6 sequence_number=8

  🎜🎜🎜Scheme 단점🎜🎜🎜🎜🎜이 병렬 복제 모드는 인스턴스에 여러 개의 DB가 있고 DB의 트랜잭션이 상대적으로 높은 수준의 경우에만 사용할 수 있습니다. 그러나 일상적인 유지 관리에서는 단일 인스턴스의 트랜잭션 처리가 상대적으로 하나의 DB에 집중됩니다. 대부분의 지연은 핫스팟 테이블의 존재 여부에 따라 관찰됩니다. 테이블 기반 병렬성을 제공하는 것이 좋습니다. 🎜🎜

  그룹 제출 기반 MySQL5.7 병렬 복제

  그룹 제출 지침

  🎜🎜간단히 말하면 double 1 설정에서 트랜잭션 후 디스크를 플러시하는 작업은 다음과 같습니다. 여러 트랜잭션을 트랜잭션 그룹으로 병합하고 통합 디스크 플러시를 수행하여 디스크 IO에 대한 부담을 줄입니다. 자세한 내용은 라오예 찻집의 그룹 제출 설명 트윗 https://mp.weixin.qq.com/s/rcPkrutiLc93aTblEZ7sFg🎜🎜🎜A 그룹을 참고하세요. 즉, 그룹 내의 트랜잭션에는 충돌이 없으므로 슬레이브 노드에서 그룹 내 트랜잭션을 동시에 실행할 수 있다는 것이 문제이다. 그룹이므로 binlog last_committed 및 sequence_number에 두 개의 새로운 매개변수 정보가 나타납니다.🎜🎜🎜🎜트랜잭션이 그룹에 있는지 어떻게 판단하나요?🎜🎜🎜🎜🎜 binlog를 분석해 보면 last_committed와 sequence_number 두 개의 매개변수 정보가 더 있고 그 중 last_committed가 중복되어 있는 것을 확인할 수 있습니다. 🎜🎜🎜🎜🎜sequence_number # 이 값은 트랜잭션 제출의 시퀀스 번호를 나타내며 단조롭게 증가합니다. 🎜🎜🎜🎜last_committed # 이 값에는 두 가지 의미가 있습니다. 1. 동일한 값은 해당 트랜잭션이 동일한 그룹에 있음을 의미합니다. 2. 이 값은 이전 트랜잭션 그룹의 최대 수를 나타냅니다. 🎜🎜🎜

  slave-parallel-type=LOGICAL_CLOCK

  🎜🎜🎜데이터베이스 구성🎜🎜🎜

  slave_parallel_type = LOGICAL_CLOCK
  slave_parallel_workers = 8
  binlog_transaction_dependency_tracking = WRITESET
  slave_preserve_commit_order = 1

  🎜🎜🎜계획이 부적절함🎜

基于组提交的同步有个不足点，就是当主节点的事务繁忙度较低的时候，导致时间段内组提交fsync刷盘的事务量较少，于是导致从库回放的并行度并不高，甚至可能一组里面只有一个事务，这样从节点的多线程就基本用不到，可以通过设置下面两个参数，让主节点延迟提交。

• binlog_group_commit_sync_delay # 等待延迟提交的时间，binlog提交后等待一段时间再 fsync。让每个 group 的事务更多，人为提高并行度。

• binlog_group_commit_sync_no_delay_count # 待提交的最大事务数，如果等待时间没到，而事务数达到了，就立即 fsync。达到期望的并行度后立即提交，尽量缩小等待延迟。

MySQL8.0基于writeset的并行复制

writeset 基于事务结果冲突进行判断事务是否可以进行并行回放的方法，他由binlog-transaction-dependency-tracking参数进行控制，默认采用WRITESET方法。

关键参数查看

Command-Line Format	--binlog-transaction-dependency-tracking=value
System Variable	binlog_transaction_dependency_tracking
Scope	Global
Dynamic	Yes
SET_VAR Hint Applies	No
Type	Enumeration
Default Value	COMMIT_ORDER
Valid Values	COMMIT_ORDER WRITESET WRITESET_SESSION

参数配置项说明

• COMMIT_ORDER # 使用 5.7 Group commit 的方式决定事务依赖。

• WRITESET     # 使用写集合的方式决定事务依赖。

• WRITESET_SESSION # 使用写集合，但是同一个session中的事务不会有相同的last_committed。

writeset 是一个HASH类型的数组，里面记录着事务的更新信息，通过transaction_write_set_extraction判断当前事务更新的记录与历史事务更新的记录是否存在冲突，判断过后再采取对应处理方法。writeset储存的最大存储值由binlog-transaction-dependency-history-size控制。

需要注意的是，当设置成WRITESET或WRITESET_SESSION的时候，事务提交是无序状态的，可以通过设置 slave_preserve_commit_order=1 强制按顺序提交。

• binlog_transaction_dependency_history_size

设定一个上限，限制在内存中缓存之前事务修改的行信息时所使用的行哈希数。一旦达到这个哈希数，就会清除历史记录。

Command-Line Format	--binlog-transaction-dependency-history-size=#
System Variable	binlog_transaction_dependency_history_size
Scope	Global
Dynamic	Yes
SET_VAR Hint Applies	No
Type	Integer
Default Value	25000
Minimum Value	1
Minimum Value	1000000

• transaction_write_set_extraction

该模式支持三种算法，默认采用XXHASH64，当从节点配置writeset复制的时候，该配置不能配置为OFF。在MySQL 8.0.26中，该参数已被标记为已弃用，将来将被删除。

Command-Line Format	--transaction-write-set-extraction[=value]
Deprecated	8.0.26
System Variable	binlog_transaction_dependency_history_size
Scope	Global, Session
Dynamic	Yes
SET_VAR Hint Applies	No
Type	Enumeration
Default Value	XXHASH64
Valid Values	OFF MURMUR32 XXHASH64

数据库配置

slave_parallel_type = LOGICAL_CLOCK
slave_parallel_workers = 8
binlog_transaction_dependency_tracking = WRITESET
slave_preserve_commit_order = 1

위 내용은 MySQL 복제에서 병렬 복제를 구현하는 방법의 상세 내용입니다. 자세한 내용은 PHP 중국어 웹사이트의 기타 관련 기사를 참조하세요!