《MySQL实战45讲》第26讲 备库为什么会延迟好几个小时?
备库并行复制模型
![](https://i-blog.csdnimg.cn/blog_migrate/a4849559710fdf04696c02e9e31b343e.png)
其实,所有的多线程复制机制,都是要把只有一个线程的 sql_thread,拆成多个线程
![](https://i-blog.csdnimg.cn/blog_migrate/a5f9300270a83697b249c9ae56035af3.png)
coordinator 就是原来的 sql_thread,不过现在它不再直接更新数据了,只负责读取中转日志和分发事务。真正更新日志的,变成了 worker 线程。
coordinator分发时遵守的规则
- 不能造成更新覆盖。这就要求更新同一行的两个事务,必须被分发到同一个 worker 中。
- 同一个事务不能被拆开,必须放到同一个 worker 中。
按表分发策略
按表分发事务的基本思路是,如果两个事务更新不同的表,它们就可以并行。因为数据是存储在表里的,所以按表分发,可以保证两个 worker 不会更新同一行。
每个事务在分发的时候,跟所有 worker 的冲突关系
- 如果跟所有 worker 都不冲突,coordinator 线程就会把这个事务分配给最空闲的 woker;
- 如果跟多于一个 worker 冲突,coordinator 线程就进入等待状态,直到和这个事务存在冲突关系的 worker 只剩下 1个;
- 如果只跟一个 worker 冲突,coordinator 线程就会把这个事务分配给这个存在冲突关系的 worker。
这个按表分发的方案,在多个表负载均匀的场景里应用效果很好。但是,如果碰到热点表,比如所有的更新事务都会涉及到某一个表的时候,所有事务都会被分配到同一个 worker 中,就变成单线程复制了。
按行分发策略
按行复制的核心思路是:如果两个事务没有更新相同的行,它们在备库上可以并行执行。
相比于按表并行分发策略,按行并行策略在决定线程分发的时候,需要消耗更多的计算资源。
按行分发策略问题
- 耗费内存。比如一个语句要删除 100 万行数据,这时候 hash 表就要记录 100 万个项。
- 耗费 CPU。解析 binlog,然后计算 hash 值,对于大事务,这个成本还是很高的。
《MySQL实战45讲》第27讲 主库出问题了,从库怎么办?
一主多从基本结构
![](https://i-blog.csdnimg.cn/blog_migrate/536b4cc54d2a90411afdd23e8234b10b.png)
虚线箭头表示的是主备关系,也就是 A 和 A’互为主备, 从库 B、C、D 指向的是主库 A。一主多从的设置,一般用于读写分离,主库负责所有的写入和一部分读,其他的读请求则由从库分担。
GTID
GTID 的全称是 Global Transaction Identifier,也就是全局事务 ID,是一个事务在提交的时候生成的,是这个事务的唯一标识。它由两部分组成,格式是:
GTID=server_uuid:gno
- server_uuid 是一个实例第一次启动时自动生成的,是一个全局唯一的值;
- gno 是一个整数,初始值是 1,每次提交事务的时候分配给这个事务,并加 1。
基于 GTID 的主备切换
在 GTID 模式下,备库 B 要设置为新主库 A’的从库。
我们把现在这个时刻,实例 A’的 GTID 集合记为 set_a,实例 B 的 GTID 集合记为 set_b。接下来,我们就看看现在的主备切换逻辑。
我们在实例 B 上执行 start slave 命令,取 binlog 的逻辑是这样的:
- 实例 B 指定主库 A’,基于主备协议建立连接。
- 实例 B 把 set_b 发给主库 A’。
- 实例 A’算出 set_a 与 set_b 的差集,也就是所有存在于 set_a,但是不存在于 set_b 的 GTID 的集合,判断A’本地是否包含了这个差集需要的所有 binlog 事务。(a. 如果不包含,表示 A’已经把实例 B 需要的 binlog给删掉了,直接返回错误; b. 如果确认全部包含,A’从自己的 binlog 文件里面,找出第一个不在 set_b 的事务,发给 B;)
- 之后就从这个事务开始,往后读文件,按顺序取 binlog 发给 B 去执行。
《MySQL实战45讲》第28讲 读写分离有哪些坑?
处理过期读的方案
过期读,即在从库上会读到系统的一个过期状态。
处理方案大致如下:
- 强制走主库方案;
- sleep 方案;
- 判断主备无延迟方案;
- 配合 semi-sync 方案;
- 等主库位点方案;
- 等 GTID 方案。
强制走主库方案
强制走主库方案其实就是,将查询请求做分类。通常情况下,我们可以将查询请求分为这么两类:
- 对于必须要拿到最新结果的请求,强制将其发到主库上。
- 对于可以读到旧数据的请求,才将其发到从库上。
sleep 方案
主库更新后,读从库之前先 sleep 一下。具体的方案就是,类似于执行一条 select sleep(1) 命令。
判断主备无延迟方案
要确保备库无延迟,通常有三种做法。
第一种确保主备无延迟的方法是,每次从库执行查询请求前,先判断 seconds_behind_master 是否已经等于 0。如果还不等于 0 ,那就必须等到这个参数变为 0 才能执行查询请求。第二种方法,对比位点确保主备无延迟。第三种方法,对比 GTID 集合确保主备无延迟。
配合 semi-sync 方案
semi-sync 做了这样的设计:
- 事务提交的时候,主库把 binlog 发给从库;
- 从库收到 binlog 以后,发回给主库一个 ack,表示收到了;
- 主库收到这个 ack 以后,才能给客户端返回“事务完成”的确认。
semi-sync+ 位点判断的方案,只对一主一备的场景是成立的。在一主多从场景中,主库只要等到一个从库的 ack,就开始给客户端返回确认。这时,在从库上执行查询请求,就有两种情况:
- 如果查询是落在这个响应了 ack 的从库上,是能够确保读到最新数据;
- 但如果是查询落到其他从库上,它们可能还没有收到最新的日志,就会产生过期读的问题。
其实,semi-sync 配合判断主备无延迟的方案,存在两个问题:
- 一主多从的时候,在某些从库执行查询请求会存在过期读的现象;
- 在持续延迟的情况下,可能出现过度等待的问题。
等主库位点方案
要理解等主库位点方案,我需要先和你介绍一条命令:
select master_pos_wait(file, pos[, timeout]);
这条命令的逻辑如下:
- 它是在从库执行的;
- 参数 file 和 pos 指的是主库上的文件名和位置;
- timeout 可选,设置为正整数 N 表示这个函数最多等待 N 秒。
这个命令正常返回的结果是一个正整数 M,表示从命令开始执行,到应用完 file 和 pos 表示的 binlog 位置,执行了多少事务。
对于先执行 trx1,再执行一个查询请求的逻辑,要保证能够查到正确的数据,我们可以使用这个逻辑:
- trx1 事务更新完成后,马上执行 show master status 得到当前主库执行到的 File 和 Position;
- 选定一个从库执行查询语句;
- 在从库上执行 select master_pos_wait(File, Position, 1);
- 如果返回值是 >=0 的正整数,则在这个从库执行查询语句;
- 否则,到主库执行查询语句。
GTID 方案
执行流程:
- trx1 事务更新完成后,从返回包直接获取这个事务的 GTID,记为 gtid1;
- 选定一个从库执行查询语句;
- 在从库上执行 select wait_for_executed_gtid_set(gtid1, 1);
- 如果返回值是 0,则在这个从库执行查询语句;
- 否则,到主库执行查询语句。
《MySQL实战45讲》第29讲 如何判断一个数据库是不是出问题了?
select 1 判断
select 1 成功返回,只能说明这个库的进程还在,并不能说明主库没问题。
innodb_thread_concurrency
innodb_thread_concurrency控制 InnoDB 的并发线程上限。也就是说,一旦并发线程数达到这个值,InnoDB 在接收到新请求的时候,就会进入等待状态,直到有线程退出。
并发连接和并发查询
并发连接和并发查询,并不是同一个概念。你在 show processlist 的结果里,看到的几千个连接,指的就是并发连接。而“当前正在执行”的语句,才是我们所说的并发查询。
在线程进入锁等待以后,并发线程的计数会减一,也就是说等行锁(也包括间隙锁)的线程是不算的。
查表判断
为了能够检测 InnoDB 并发线程数过多导致的系统不可用情况,我们需要找一个访问 InnoDB 的场景。一般的做法是,在系统库(mysql 库)里创建一个表,比如命名为 health_check,里面只放一行数据,然后定期执行:
mysql> select * from mysql.health_check;
使用这个方法,我们可以检测出由于并发线程过多导致的数据库不可用的情况。
缺陷
空间满了以后,这种方法又会变得不好使。更新事务要写 binlog,而一旦 binlog 所在磁盘的空间占用率达到 100%,那么所有的更新语句和事务提交的 commit 语句就都会被堵住。但是,系统这时候还是可以正常读数据的。
更新判断
既然要更新,就要放个有意义的字段,常见做法是放一个 timestamp 字段,用来表示最后一次执行检测的时间。
mysql> update mysql.health_check set t_modified=now();
如果主库 A 和备库 B 都用相同的更新命令,就可能出现行冲突,也就是可能会导致主备同步停止。所以,现在看来 mysql.health_check 这个表就不能只有一行数据了。
为了让主备之间的更新不产生冲突,我们可以在 mysql.health_check 表上存入多行数据,并用 A、B 的 server_id 做主键。
内部统计
针对磁盘利用率这个问题,如果 MySQL 可以告诉我们,内部每一次 IO 请求的时间,那我们判断数据库是否出问题的方法就可靠得多了。
其实,MySQL 5.6 版本以后提供的 performance_schema 库,就在 file_summary_by_event_name表里统计了每次 IO 请求的时间。(显示的是 redo log 操作的时间统计)
在 performance_schema 库的 file_summary_by_event_name 表里,binlog 对应的是 event_name = "wait/io/file/sql/binlog"这一行。各个字段的统计逻辑,与 redo log 的各个字段完全相同。
因为我们每一次操作数据库,performance_schema 都需要额外地统计这些信息,所以我们打开这个统计功能是有性能损耗的。
《MySQL实战45讲》第30讲 用动态的观点看加锁
怎么看死锁?
- 由于锁是一个个加的,要避免死锁,对同一组资源,要按照尽量相同的顺序访问;
- 在发生死锁的时刻,InnoDB会选择回滚成本更小的语句来回滚。
学习地址:MySQL实战45讲