binlog

写入机制

binlog cache是线程私有的

  1. 在事务执行过程中，先把日志写到binlog cache中
  2. binlog cache每个线程单独持有一份，通过参数binlog_cache_size控制单个线程内binlog cache所占内存的大小。如果超过了这个参数规定的大小，就要暂存到磁盘。
  3. 事务提交的时候并没有把数据持久化到磁盘，而是写入到了文件系统的page cache，因此速度比较快，这个过程是write过程。
  4. 当调用fsync的时候，才会将page cache中的数据持久化到磁盘

write和fsync的时机

由参数sync_binlog控制：

  1. sync_binlog=0时，表示每次提交事务都只write，不fsync。会丢数据，没有保障
  2. sync_binlog=1时，表示每次提交事务都会执行fsync。不会丢失binlog

  3. sync_binlog=N(N>1)时，表示每次提交事务都write，但累计N个事务后才fsync。会丢失最近N个事务的binlog日志，相比于第一种方式较好。

    redo log

    写入机制

    redo log buffer是线程共享的

  4. 事务在执行过程中，生成的redo log是要先写到redo log buffer的

  5. 不需要每次写入到redo log buffer的内容都要直接持久化到磁盘，如果在事务执行期间MySQL异常重启，这部分日志丢了也没问题，因为事务还没有提交
  6. 事务提交之前redo log buffer的内容有可能被持久化到磁盘

redo log和binlog类似，有三种状态：

  • 在redo log buffer中，在内存中（内存操作）
  • write到文件系统的page cache中，但是没有持久化（内存操作）
  • fsync持久化到磁盘（磁盘IO）

    write和fsync的时机

    InnoDB提供了innodb_flush_log_at_trx_commit参数：
  1. 0，表示每次事务提交的时候都只是把redo log留在redo log buffer中
  2. 1，表示每次提交事务的时候都将redo log直接持久化到磁盘
  3. 2，表示每次提交事务都只是把redo log写到page cache中

“双1”配置就是指的是sync_binlog和innodb_flush_log_at_trx_commit都设置成1。也就是一个事务完整提交前，需要等待两次刷盘（redo log和binlog）

InnoDB后台有一个线程，每隔一秒就会把redo log buffer中日志，调用write写到page cache中，然后调用fsync持久化到磁盘，因此未提交的事务产生的redo log，可能会被后台线程持久化到磁盘。

  • 当redo log buffer占用的空间即将达到innodb_log_buffer_size一般时，后台线程会主动写盘，由于事务没有提交，因此这个写盘动作只是write到page cache，并没有调用fsync
  • 并行的事务提交的时候，会顺带将这个事务的redo log buffer持久化到磁盘。
    • 假设事务A执行到了一半，已经写了一部分redo log到buffer中，这时候有事务B提交，如果innodb_flush_log_at_trx_commit设置的是1，那么事务B要把redo log buffer里的日志全部持久化到磁盘，就会带着事务A在redo log buffer里的内容一起持久化

      prepare进行持久化还是commit时持久化

      在MySQL日志系统这篇文章中我们讲过redo log存在prepare和commit状态，由于InnDB利用了prepare状态的redo log+binlog来实现崩溃恢复，如果把innodb_flush_log_at_trx_commit设置为1，那么在prepare阶段就要持久化一次。

而在commit的时候，InnoDB认为redo log在commit的时候就不需要fsync了，只会write到文件系统的page cache中就可以了。

组提交

LSN日志逻辑序列号

LSN是单调递增的，用来对应redo log的一个个写入点，每次写入长度为length的redo log，LSN的值就会加上length。LSN也会写到数据页中，来确保数据页不会被多次执行重复的redo log。

举例

三个并发的事务trx1、trx2、trx3在prepare阶段，都写完了redo log buffer，对应的LSN分别是60,100,150

  • trx1是第一个到达的，被选为这组的leader
  • 当trx1要写盘时，这个组里面已经有三个事务了，这时候LSN变成了150
  • trx1去写盘的时候，带的就是LSN=150，因此等trx1返回时，所有LSN小于等于150的redo log，都已经被持久化到磁盘了
  • trx2和trx3就可以直接返回了

因此，一次组提交里面，组员越多，节约磁盘IOPS的效果越好。

通过设置参数binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count来实现提交binlog组提交的效果。

  1. binlog_group_commit_sync_delay，表示延迟多少微秒后才调用fsync
  2. binlog_group_commit_sync_no_delay_count，表示累计多少次以后才调用fsync

    上边这两个参数是或的关系，只要满足其中一个就会调用fsync

为什么说WAL机制是为了减少磁盘写？

  • WAL机制主要得益于binlog和redo log是都是顺序写，磁盘的顺序写比随机写速度要快

  • 组提机制，可以大幅度降低磁盘的IOPS

    如果MySQL出现了性能瓶颈，而且性能瓶颈在IO上，可以通过哪些办法来提升性能？

  • 设置binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count参数，来减少binlog的写盘次数，这个方法是基于“额外的故意等待”来实现的，因此可能会增加语句的响应时间，但没有丢数据的风向

  • 将sync_binlog设置为大于1的值，风险是主机掉电会丢binlog
  • 将innodb_flush_log_at_trx_commit设置为2，这样做风险是，主机掉电的时候会丢数据