MySQL性能与可用性分析

日志

  • 重做日志（redo log）：确保事务的持久性。防止在发生故障的时间点，尚有脏页未写入磁盘，在重启mysql服务的时候，根据redo log进行重做，从而达到事务的持久性这一特性。
  • 回滚日志（undo log）：保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），即非锁定读
  • 二进制日志，归档日志（binlog）：用于复制和备份，在主从复制中，从库利用主库上的binlog进行重播，实现主从同步。 也可基于时间点做数据库的的还原。
  • 其他还有常见的慢日志、查询日志、错误日志

我们主要看一下前面3个日志，来理解MySQL是如何利用这些日志和一些特殊的机制保证可用性的。

redo log 重做日志

在MySQL里，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，整个过程IO成本、查找成本都很高。为了解决这个问题，MySQL的设计者就用了WAL技术，WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

WAL(Write-Ahead Loggin)机制

在MySQL里，如果每一次的更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新。整个过程IO成本、查找成本都很高。为了解决这个问题，MySQL的设计者就用了WAL技术，WAL的全称是Write-Ahead Logging，它的关键点就是先写日志，再写磁盘。

具体来说，当有一条记录需要更新的时候，InnoDB引擎就会先把记录写到redo log里面，并更新内存，这个时候更新就算完成了。同时，InnoDB引擎会在适当的时候，将这个操作记录更新到磁盘里面，而这个更新往往是在系统比较空闲的时候做。

另外，InnoDB的redo log是固定大小的，比如可以配置为一组4个文件，每个文件的大小是1GB，那么这块“粉板”总共就可以记录4GB的操作。从头开始写，写到末尾就又回到开头循环写，如下面这个图所示。

redolog

write pos是当前记录的位置，一边写一边后移，写到第3号文件末尾后就回到0号文件开头。checkpoint是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。

write pos和checkpoint之间的是空着的部分，可以用来记录新的操作。如果write pos追上checkpoint，表示满了，这时候不能再执行新的更新，得停下来先擦掉一些记录，把checkpoint推进一下。

有了redo log，InnoDB就可以保证即使数据库发生异常重启，之前提交的记录都不会丢失，这个能力称为crash-safe。

redo log的三种状态：

  • 红色：存在redo log buffer中，物理上是在MySQL进程内存中
  • 黄色：写到磁盘(write)，但是没有持久化（fsync)，物理上是在文件系统的page cache里面
  • 绿色：持久化到磁盘，对应的是hard disk

redo log

为了控制 redo log 的写入策略，InnoDB 提供了 innodb_flush_log_at_trx_commit 参 数，它有三种可能取值:

  • 0，表示每次事务提交时都只是把 redo log 留在 redo log buffer 中 ;
  • 1，表示每次事务提交时都将 redo log 直接持久化到磁盘;
  • 2 ，表示每次事务提交时都只是把 redo log 写到 page cache。

InnoDB 有一个后台线程，每隔 1 秒，就会把 redo log buffer 中的日志，调用 write 写 到文件系统的 page cache，然后调用 fsync 持久化到磁盘。

实际上，除了后台线程每秒一次的轮询操作外，还有两种场景会让一个没有提交的事务的 redo log 写入到磁盘中。

1. redo log buffer 占用的空间即将达到 innodb_log_buffer_size 一半的时候，后台线程会主动写盘。注意，由于这个事务并没有提交，所以这个写盘动作只是 write，而没有调用 fsync，也就是只留在了文件系统的 page cache。
2. 并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。假设一个事务 A 执行到一半，已经写了一些 redo log 到 buffer 中，这时候有另外一个线程的事务 B 提交，如果 innodb_flush_log_at_trx_commit 设置的是1，那么按照这个参数的逻辑，事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候，就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。

binlog

MySQL整体来看，其实就有两块：一块是Server层，它主要做的是MySQL功能层面的事情；还有一块是引擎层，负责存储相关的具体事宜。上面我们聊到的redo log是InnoDB引擎特有的日志，而Server层也有自己的日志，称为binlog。

为什么会有两份日志呢？

因为最开始MySQL里并没有InnoDB引擎。MySQL自带的引擎是MyISAM，但是MyISAM没有crash-safe的能力，binlog日志只能用于归档。而InnoDB是另一个公司以插件形式引入MySQL的，既然只依靠binlog是没有crash-safe能力的，所以InnoDB使用另外一套日志系统——也就是redo log来实现crash-safe能力。

这两种日志有以下三点不同。

  • redo log是InnoDB引擎特有的；binlog是MySQL的Server层实现的，所有引擎都可以使用。
  • redo log是物理日志，记录的是“在某个数据页上做了什么修改”；binlog是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”。
  • redo log是循环写的，空间固定会用完；binlog是可以追加写入的。“追加写”是指binlog文件写到一定大小后会切换到下一个，并不会覆盖以前的日志。

有了对这两个日志的概念性理解，我们再来看执行器和InnoDB引擎在执行这个简单的update语句时的内部流程。

执行器先找引擎取ID=2这一行。ID是主键，引擎直接用树搜索找到这一行。如果ID=2这一行所在的数据页本来就在内存中，就直接返回给执行器；否则，需要先从磁盘读入内存，然后再返回。

执行器拿到引擎给的行数据，把这个值加上1，比如原来是N，现在就是N+1，得到新的一行数据，再调用引擎接口写入这行新数据。

引擎将这行新数据更新到内存中，同时将这个更新操作记录到redo log里面，此时redo log处于prepare状态。然后告知执行器执行完成了，随时可以提交事务。

执行器生成这个操作的binlog，并把binlog写入磁盘。

执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交（commit）状态，更新完成。

这里我给出这个update语句的执行流程图，图中浅色框表示是在InnoDB内部执行的，深色框表示是在执行器中执行的。

image

事务执行过程中，先把日志写到binlog cache，事务提交的时候，再把binlog cache写到binlog文件中。

一个事务的binlog是不能被拆开的，因此不论这个事务多大，也要确保一次性写入。这就涉及到了binlog cache的保存问题。

系统给binlog cache分配了一片内存，每个线程一个，参数 binlog_cache_size用于控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。

事务提交的时候，执行器把binlog cache里的完整事务写入到binlog中，并清空binlog cache。

binlog

可以看到，每个线程有自己的binlog cache，但是共用同一份binlog文件。

图中的write，指的就是指把日志写入到文件系统的page cache，并没有把数据持久化到磁盘，所以速度比较快。

图中的fsync，才是将数据持久化到磁盘的操作。

write 和fsync的时机，是由参数sync_binlog控制的：

  • sync_binlog=0的时候，表示每次提交事务都只write，不fsync；
  • sync_binlog=1的时候，表示每次提交事务都会执行fsync；
  • sync_binlog=N(N>1)的时候，表示每次提交事务都write，但累积N个事务后才fsync。

因此，在出现IO瓶颈的场景里，将sync_binlog设置成一个比较大的值，可以提升性能。在实际的业务场景中，考虑到丢失日志量的可控性，一般不建议将这个参数设成0，比较常见的是将其设置为100~1000中的某个数值。

但是，将sync_binlog设置为N，对应的风险是：如果主机发生异常重启，会丢失最近N个事务的binlog日志。

binlog 存储格式

  • row：记录的一行行sql（建议）

  • statement：记录的是event（占用空间更小，但尽量不设置成这个）

  • mixed：mysql自行判断，有可能引起主备不一致的用row，否则用statement

既然MySQL能保证redo log和binlog能可靠性写入磁盘，那么在他们两者之间如何保证可靠转换的呢？

两阶段提交

redo log 先 prepare，再写 binlog，最后再把 redo log commit。利用这个两阶段提交机制，MySQL保证了redo log和binlog的可靠传输。

最后我们来看下整体简图：

持久化

undo log

在数据修改的时候，不仅记录了redo log，还记录了相对应的undo log，如果因为某些原因导致事务失败或回滚了，可以借助undo log进行回滚。

undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。

当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚。有时候应用到行版本控制的时候，也是通过undo log来实现的：当读取的某一行被其他事务锁定时，它可以从undo log中分析出该行记录以前的数据是什么，从而提供该行版本信息，让用户实现非锁定一致性读取。

undo log是采用段(segment)的方式来记录的，每个undo操作在记录的时候占用一个undo log segment。

另外，undo log也会产生redo log，因为undo log也要实现持久性保护。

说到undo log提一下MVCC(Multi Version Concurrency Control的简称)，多版本并发控制。

MVCC

MVCC的实现，通过保存数据在某个时间点的快照来实现的。这意味着一个事务无论运行多长时间，在同一个事务里能够看到数据一致的视图。根据事务开始的时间不同，同时也意味着在同一个时刻不同事务看到的相同表里的数据可能是不同的。

详细分析可参考数据库基础（四）Innodb MVCC实现原理

主从复制原理

  1. master服务器将数据的改变记录二进制binlog日志，当master上的数据发生改变时，则将其改变写入二进制日志中；
  2. slave服务器会在一定时间间隔内对master二进制日志进行探测其是否发生改变，如果发生改变，则开始一个I/O Thread请求master二进制事件
  3. 同时主节点为每个I/O线程启动一个dump线程，用于向其发送二进制事件，并保存至从节点本地的中继日志中，从节点将启动SQL线程从中继日志中读取二进制日志，在本地重放，使得其数据和主节点的保持一致，最后I/OThread和SQLThread将进入睡眠状态，等待下一次被唤醒。

也就是说：

  • 从库会生成两个线程,一个I/O线程,一个SQL线程;
  • I/O线程会去请求主库的binlog,并将得到的binlog写到本地的relay-log(中继日志)文件中;
  • 主库会生成一个log dump线程,用来给从库I/O线程传binlog;
  • SQL线程,会读取relay log文件中的日志,并解析成sql语句逐一执行

主从复制是构建高可用MySQL的基础，复制就是让一台服务器的数据和其它服务器保持同步，一台主库可以同步到多台备库上面，备库也可以作为另一台服务器的主库。主库和备库之间可以有多种不同的组合方式。

主从复制延迟

产生延迟原因：

  • 主节点如果执行一个很大的事务(更新千万条语句，执行很长时间的事务)，那么就会对主从延迟产生较大的影响
  • 网络延迟，日志较大，slave数量过多。
  • 主节点多线程写入，从节点只有单线程恢复

处理办法：

  • 大事务：将大事务分为小事务，分批处理数据。
  • 减少Slave的数量，不要超过5个，减少单次事务的大小。
  • MySQL 5.7之后，可以使用多线程复制，使用MGR复制架构

复制模式

  • 异步模式（默认）：主库在执行完客户端提交的事务后会立即将结果返给客户端，并不关心从库是否已经接收并处理，这样就会有一个问题，主如果crash掉了，此时主上已经提交的事务可能并没有传到从库上，如果此时，强行将从提升为主，可能导致新主上的数据不完整。
  • 全同步模式：指当主库执行完一个事务，所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回，所以全同步复制的性能必然会收到严重的影响。
  • 半同步模式（需安装插件）：是介于全同步复制与全异步复制之间的一种，主库只需要等待至少一个从库节点收到并且 Flush Binlog 到 Relay Log 文件即可，主库不需要等待所有从库给主库反馈。同时，这里只是一个收到的反馈，而不是已经完全完成并且提交的反馈，如此，节省了很多时间。

高可用方案

  • MMM
  • MHA
  • PXC
  • MGR

参考：

关注和赞赏都是对小欧莫大的支持！ 🤝 🤝 🤝
公众号

← 优化
→