您当前的位置:首页 > 电脑百科 > 数据库 > MYSQL

MySQL:亿级别数据不丢失是如何实现的

时间:2023-12-07 14:39:43  来源:微信公众号  作者:陆队长
LSN(Log Sequence Number,日志逻辑序列号)是单调递增的,用来对应redo log的一个个写入点,每次写入长度为length的redo log,LSN的值就会加上length。

周杰伦的所有歌曲中,我最喜欢的歌就是《听妈妈的话》,其中有这么一句歌词:小朋友,你是否有很多问号,为什么别人在那看漫画,我却在学画画。

应用在现在的场景就是:小伙伴,你是否有很多问号,为什么别人只需要简单用一下MySQL,你却要对MySQL深入浅出。

实际上每天的进步都是为了自己能接受顶尖大佬的技术熏陶,虽然我们不能亲自聆听他们的声音,但是他们已经将自己的思路写在了他们的开源项目里,这就是我们学习开源项目的意义所在。

比如今天,我们的话题是:MySQL可以存储上亿级别的数据,但是却几乎不会丢失数据,这里面到底是因为什么?

先给出结论:MySQL的数据不丢失就需要保证binlog和redo log都持久化到磁盘,因此,为了保证数据不丢失,就需要了解两个日志的写入机制。

1 binlog写入机制

1.1 写入原则

binlog的写入逻辑为:事务执行过程中,先把日志写到binlog cache,事务提交的时候,再把binlog cache写到binlog文件中。

同时,一个事务的Binlog是不能拆开的,因此,无论事务多大,也要确保一次性写入,这就涉及到binlog cache的保存问题。原因在于:binlog写入的前提条件是事务被提交,事务至少进入prepare状态,若此时一个事务的binlog拆分写,意味着备库执行时,可能将还没有提交的事务执行,导致主备数据不一致。

系统给binlog cache分配了一块内存,每个线程一个,参数binlog_cache_size用于控制单个线程内binlog cache所占内存大小。如果超过了这个参数规定大小,就要暂存到磁盘。可以通过语句show status like 'Binlog_cache_disk_use';判断默认大小32KB是否满足大小,如果语句的值远大于0,需要增加binlog_cache_size的值;

MySQL:亿级别数据不丢失是如何实现的图片

事务提交时,执行器把binlog cache里的完整事务写入到binlog,并清空binlog cache。实际上,在第一段提交状态变为prepare状态时,就可以把binlog cache写入binlog,因此,即使之后crash,也能恢复数据。

1.2 写入流程

MySQL:亿级别数据不丢失是如何实现的图片

如图所示,每个线程有自己binlog cache,但是共用同一份binlog文件。执行流程为:

  • 事务执行过程中先把日志写到binlog cache ,事务提交的时候再把binlog cache 写入到binlog文件中,并清空binlog cache;
  • 系统为每个线程分配了一片binlog cache内存,参数binlog_cache_size控制单个线程内binlog cache大小。如果超过这个大小就要暂存到磁盘;
  • 事务提交的时候,执行器把binlog cache里完整的事务写入binlog中。并清空binlog cache。 
  • 每个线程都有自己的binlog cache,共用一份binlog文件 
  • write,是把日志写入到文件系统的page cache内存中,没有持久化到磁盘,所以速度比较快。fsync是将数据持久化到磁盘,因此说,fsync才会占用磁盘的IOPS;

Page Cache是OS关于磁盘IO的缓存,位于内核中,不适用于大文件传输,因为大文件传输page cache的命中率比较低,这个时候page cache不仅没有起到作用还增加了一次数据从磁盘buffer到内核page cache的开销;

高版本的linux系统中已经把Buffer跟虚拟文件系统的page cache合并在一起了,因此也就没有从磁盘buffer拷贝到内核page cache的开销;

write和fsync的时机,由参数sync_binlog控制(与redisAppendfsync相似):

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

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

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

2 redo log机制

2.1 redo log三种状态

MySQL:亿级别数据不丢失是如何实现的图片

如图所示的三种颜色就是redo log的三种状态:

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

fsync函数同步内存中所有已修改的文件数据到储存设备。一般情况下,对硬盘(或者其他持久存储设备)文件的write操作,更新的只是内存中的页缓存(page cache),而脏页面不会立即更新到硬盘中,而是由操作系统统一调度,如由专门的flusher内核线程在满足一定条件时(如一定时间间隔、内存中的脏页达到一定比例)内将脏页面同步到硬盘上(放入设备的IO请求队列)。 因为write调用不会等到硬盘IO完成之后才返回,因此如果OS在write调用之后、硬盘同步之前崩溃,则数据可能丢失。

如果事务执行过程中MySQL发生异常重启,这部分日志丢了,也不会有损失,因为事务还没有提交, 因此,redo log buffer不需要每次生成都直接持久化磁盘。

2.2 redo log写入策略

由于都是内存操作,因此日志写入redo log buffer,以及write到page cache都很快,但是持久化磁盘的速度比较慢。

为控制写入策略,InnoDB提供了innodb_flush_log_at_trx_commit参数:

  • 0:每次事务提交都只是把redo log留在redo log buffer;
  • 1:每次事务提交都将redo log直接持久化到磁盘;【innodb的默认值】
  • 2:每次事务提交时都只是把redo log写到page cache;

2.3 刷盘时机

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

事务执行过程中的redo log也是直接写入到buffer中,这些redo log也会被后台线程一起持久化到磁盘,因此,一个没有提交的事务的redo log也可能已经持久化到磁盘。

2)空间不足:redo log buffer占用的空间即将到达innodb_log_buffer_size一半时,后台线程会主动写盘。注意,此时由于这个事务还没有提交,所以这个写盘动作只是write,没有调用fsync,即:只是写入到page cache中。

MySQL:亿级别数据不丢失是如何实现的图片

3)其他事务提交:并行事务提交时,顺带将这个事务的redo log buffer持久化到磁盘。假设一个事务 A 执行到一半,已经写了一些 redo log 到 buffer 中,这时候有另外一个线程的事务 B 提交,如果 innodb_flush_log_at_trx_commit 设置的是 1,那么按照这个参数的逻辑,事务 B 要把 redo log buffer 里的日志全部持久化到磁盘。这时候,就会带上事务 A 在 redo log buffer 里的日志一起持久化到磁盘。

2.4 配置说明

两阶段提交,时序上是redo log先prepare,再写binlog,最后再把redo log commit。

  • 在redo log执行prepare阶段MySQL异常重启,redo log没有fsync,内存丢失,直接回滚,不影响数据一致性;
  • 当redo log执行fsync成功,但是binlog持久化异常,此时MySQL异常重启,此时检查redo log在prepare状态,但是Binlog写入失败,则直接回滚即可;
  • 当binlog持久化后,但是redo log commit失败,此时的redo log一定是prepare状态,并且binlog完成,则添加commit标记,进而提交执行持久化,满足数据一致性;
  • binlog完成且提交,redo log也commit成功,此时数据满足一致性。

如果把innodb_flush_log_at_trx_commit设置为1,那么redo log在prepare阶段就要持久化一次,因为crash-safe依赖于prepare状态的redo log + binlog恢复。

每秒一次后台轮询刷盘,再加上crash-safe,InnoDB认为redo log在commit时只需要write到文件系统的page cache就可以了,因为只要binlog写盘成功,就算redo log状态还是prepare状态也会被认为事务已经执行成功,所以只需要write到page cache就OK了,没必要浪费IO主动执行一次fsync。

  • redo log prepare && binlog commit:事务提交;
  • redo log prepare && binlog uncommitted:事务回滚;

MySQL的“双1”配置,指的就是sync_binlog和innodb_flush_log_at_trx_commit设置为1.即:一个事务完整提交前,需要等待两次刷盘,一次是redo log的prepare阶段,一个是Binlog。

这里需要注意的是,在事务中有两次commit,第一次commit是事务语句的commit,这里说的commit主要是第二次commit,即:redo log的commit。

两个commit的不是一个东西,在事务提交时,commit语句可以称为commit1, 这时就会将redolog和 binlog fsync到磁盘, 这里写入的redolog是prepare状态(此时是语句的commit事务),如果这个prepare状态的redolog和binlog都fsync成功的话,这个数据就不会丢失了。 然后后续把redolog的状态从prepare的状态变成commit状态,这里称为commit2,这里的改变状态是后台线程刷的,和数据不丢就没啥关系,只是为了让redolog状态完整。

2.5 组提交(group commit)

2.5.1 LSN

LSN(Log Sequence Number,日志逻辑序列号)是单调递增的,用来对应redo log的一个个写入点,每次写入长度为length的redo log,LSN的值就会加上length。

LSN可以看成是事务提交的序号,这个序号是在事务提交写盘的时候生成的,因此可以说LSN反映了事务提交的顺序。

LSN也会写到InnoDB的数据页中,确保数据页中不会被多次执行重复的redo log。

MySQL:亿级别数据不丢失是如何实现的图片

如图所示三个并发事务 (trx1, trx2, trx3) 在 prepare 阶段,都写完 redo log buffer,持久化到磁盘的过程,对应的 LSN 分别是 50、120 和 160。

  • trx1是第一个到达的,会被选择这组leader;
  • 等trx1开始写盘时,组内有三个事务,LSN变成了160;
  • trx1写盘时,携带的LSN为160,因此等trx1返回时,所有LSN小于等于160的redo log都已经被持久化到磁盘;
  • 这时候trx2和trx3可以直接返回;

MySQL当多个线程在提交完prepare,redo log写入到redo log buffer中,此时,redo log buffer存在多个线程的日志,并同步更新了LSN。第一个写完的线程带着LSN去刷盘,写完后,别的线程发现自己的redo log已经写完了(LSN大于线程的LSN),直接就返回。

如上所示,一个组提交的事务越多,节约磁盘的IOPS效果越好。在并发场景,即使innodb_flush_log_at_trx_commit设置为1,事务每次prepare都要执行刷盘,此时可能有其他的线程也在执行事务,也可以将他们组成一个组实现组提交。

2.5.2 两阶段优化

MySQL:亿级别数据不丢失是如何实现的图片

两阶段提交可以简化为如下两步:

  • 先把binlog从binlog cache写到磁盘的binlog文件;
  • 调用fsync持久化;

既然组提交能够优化磁盘的IOPS,那就有了如下的优化:

MySQL:亿级别数据不丢失是如何实现的图片

如图所示,把redo log做fysnc的时间拖到了步骤1之后,采用交叉fsync的方式,就是为了收集更多的“提交”,这样的组提交效果更好一些。

这么一来,binlog也可以组提交了。在执行第4步把binlog fsync到磁盘时,如果有多个事务的 binlog 已经写完了,也是一起持久化的,这样也可以减少 IOPS 的消耗。

不过通常情况下第 3 步执行得会很快(redo log顺序写,相对较快),所以 binlog 的 write 和 fsync 间的间隔时间短,导致能集合到一起持久化的 binlog 比较少,因此 binlog 的组提交的效果通常不如 redo log 的效果那么好。

如果想提升 binlog 组提交的效果,可以通过设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 来实现。

  • binlog_group_commit_sync_delay 参数,表示延迟多少微秒后才调用 fsync;
  • binlog_group_commit_sync_no_delay_count 参数,表示累积多少次以后才调用 fsync。

这两个条件是或的关系,也就是说只要有一个满足条件就会调用 fsync,当 binlog_group_commit_sync_delay 设置为 0 的时候,binlog_group_commit_sync_no_delay_count 也无效了。

之前我们多次提及的WAL能够减少磁盘写,主要是得益于:

  • redo log和binlog都是顺序写,磁盘的顺序写比随机写要快;
  • 组提交机制,大大降低磁盘的IOPS消耗;

3 MySQL的IO瓶颈优化

分析到这里,我们再来回答这个问题:如果你的 MySQL 现在出现了性能瓶颈,而且瓶颈在 IO 上,可以通过哪些方法来提升性能呢?针对这个问题,可以考虑以下三种方法:

  • 设置 binlog_group_commit_sync_delay 和 binlog_group_commit_sync_no_delay_count 参数,减少 binlog 的写盘次数。这个方法是基于“额外的故意等待”来实现的,因此可能会增加语句的响应时间,但没有丢失数据的风险。之所以说没有丢失数据风险,指的是无论fsync延迟多久,只要binlog没有持久化,只是做回滚,不会出现丢数据。但是可能导致业务侧超时。
  • 将 sync_binlog 设置为大于 1 的值(比较常见是 100~1000)。这样做的风险是,主机掉电时会丢 binlog 日志。
  • 将 innodb_flush_log_at_trx_commit 设置为 2。这样做的风险是,主机掉电的时候会丢数据。

但是并不建议把 innodb_flush_log_at_trx_commit 设置成 0。因为把这个参数设置成 0,表示 redo log 只保存在内存中,这样的话 MySQL 本身异常重启也会丢数据,风险太大。而 redo log 写到文件系统的 page cache 的速度也是很快的,所以将这个参数设置成 2 跟设置成 0 其实性能差不多,但这样做 MySQL 异常重启时就不会丢数据了,相比之下风险会更小。



Tags:MySQL   点击:()  评论:()
声明:本站部分内容及图片来自互联网,转载是出于传递更多信息之目的,内容观点仅代表作者本人,不构成投资建议。投资者据此操作,风险自担。如有任何标注错误或版权侵犯请与我们联系,我们将及时更正、删除。
▌相关推荐
MySQL 核心模块揭秘
server 层会创建一个 SAVEPOINT 对象,用于存放 savepoint 信息。binlog 会把 binlog offset 写入 server 层为它分配的一块 8 字节的内存里。 InnoDB 会维护自己的 savepoint...【详细内容】
2024-04-03  Search: MySQL  点击:(5)  评论:(0)  加入收藏
MySQL 核心模块揭秘,你看明白了吗?
为了提升分配 undo 段的效率,事务提交过程中,InnoDB 会缓存一些 undo 段。只要同时满足两个条件,insert undo 段或 update undo 段就能被缓存。1. 关于缓存 undo 段为了提升分...【详细内容】
2024-03-27  Search: MySQL  点击:(10)  评论:(0)  加入收藏
MySQL:BUG导致DDL语句无谓的索引重建
对于5.7.23之前的版本在评估类似DDL操作的时候需要谨慎,可能评估为瞬间操作,但是实际上线的时候跑了很久,这个就容易导致超过维护窗口,甚至更大的故障。一、问题模拟使用5.7.22...【详细内容】
2024-03-26  Search: MySQL  点击:(8)  评论:(0)  加入收藏
从 MySQL 到 ByteHouse,抖音精准推荐存储架构重构解读
ByteHouse是一款OLAP引擎,具备查询效率高的特点,在硬件需求上相对较低,且具有良好的水平扩展性,如果数据量进一步增长,可以通过增加服务器数量来提升处理能力。本文将从兴趣圈层...【详细内容】
2024-03-22  Search: MySQL  点击:(23)  评论:(0)  加入收藏
MySQL自增主键一定是连续的吗?
测试环境:MySQL版本:8.0数据库表:T (主键id,唯一索引c,普通字段d)如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不...【详细内容】
2024-03-10  Search: MySQL  点击:(5)  评论:(0)  加入收藏
准线上事故之MySQL优化器索引选错
1 背景最近组里来了许多新的小伙伴,大家在一起聊聊技术,有小兄弟提到了MySQL的优化器的内部策略,想起了之前在公司出现的一个线上问题,今天借着这个机会,在这里分享下过程和结论...【详细内容】
2024-03-07  Search: MySQL  点击:(26)  评论:(0)  加入收藏
MySQL数据恢复,你会吗?
今天分享一下binlog2sql,它是一款比较常用的数据恢复工具,可以通过它从MySQL binlog解析出你要的SQL,并根据不同选项,可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。主要...【详细内容】
2024-02-22  Search: MySQL  点击:(41)  评论:(0)  加入收藏
如何在MySQL中实现数据的版本管理和回滚操作?
实现数据的版本管理和回滚操作在MySQL中可以通过以下几种方式实现,包括使用事务、备份恢复、日志和版本控制工具等。下面将详细介绍这些方法。1.使用事务:MySQL支持事务操作,可...【详细内容】
2024-02-20  Search: MySQL  点击:(50)  评论:(0)  加入收藏
为什么高性能场景选用Postgres SQL 而不是 MySQL
一、 数据库简介 TLDR;1.1 MySQL MySQL声称自己是最流行的开源数据库,它属于最流行的RDBMS (Relational Database Management System,关系数据库管理系统)应用软件之一。LAMP...【详细内容】
2024-02-19  Search: MySQL  点击:(37)  评论:(0)  加入收藏
MySQL数据库如何生成分组排序的序号
经常进行数据分析的小伙伴经常会需要生成序号或进行数据分组排序并生成序号。在MySQL8.0中可以使用窗口函数来实现,可以参考历史文章有了这些函数,统计分析事半功倍进行了解。...【详细内容】
2024-01-30  Search: MySQL  点击:(53)  评论:(0)  加入收藏
▌简易百科推荐
MySQL 核心模块揭秘
server 层会创建一个 SAVEPOINT 对象,用于存放 savepoint 信息。binlog 会把 binlog offset 写入 server 层为它分配的一块 8 字节的内存里。 InnoDB 会维护自己的 savepoint...【详细内容】
2024-04-03  爱可生开源社区    Tags:MySQL   点击:(5)  评论:(0)  加入收藏
MySQL 核心模块揭秘,你看明白了吗?
为了提升分配 undo 段的效率,事务提交过程中,InnoDB 会缓存一些 undo 段。只要同时满足两个条件,insert undo 段或 update undo 段就能被缓存。1. 关于缓存 undo 段为了提升分...【详细内容】
2024-03-27  爱可生开源社区  微信公众号  Tags:MySQL   点击:(10)  评论:(0)  加入收藏
MySQL:BUG导致DDL语句无谓的索引重建
对于5.7.23之前的版本在评估类似DDL操作的时候需要谨慎,可能评估为瞬间操作,但是实际上线的时候跑了很久,这个就容易导致超过维护窗口,甚至更大的故障。一、问题模拟使用5.7.22...【详细内容】
2024-03-26  MySQL学习  微信公众号  Tags:MySQL   点击:(8)  评论:(0)  加入收藏
从 MySQL 到 ByteHouse,抖音精准推荐存储架构重构解读
ByteHouse是一款OLAP引擎,具备查询效率高的特点,在硬件需求上相对较低,且具有良好的水平扩展性,如果数据量进一步增长,可以通过增加服务器数量来提升处理能力。本文将从兴趣圈层...【详细内容】
2024-03-22  字节跳动技术团队    Tags:ByteHouse   点击:(23)  评论:(0)  加入收藏
MySQL自增主键一定是连续的吗?
测试环境:MySQL版本:8.0数据库表:T (主键id,唯一索引c,普通字段d)如果你的业务设计依赖于自增主键的连续性,这个设计假设自增主键是连续的。但实际上,这样的假设是错的,因为自增主键不...【详细内容】
2024-03-10    dbaplus社群  Tags:MySQL   点击:(5)  评论:(0)  加入收藏
准线上事故之MySQL优化器索引选错
1 背景最近组里来了许多新的小伙伴,大家在一起聊聊技术,有小兄弟提到了MySQL的优化器的内部策略,想起了之前在公司出现的一个线上问题,今天借着这个机会,在这里分享下过程和结论...【详细内容】
2024-03-07  转转技术  微信公众号  Tags:MySQL   点击:(26)  评论:(0)  加入收藏
MySQL数据恢复,你会吗?
今天分享一下binlog2sql,它是一款比较常用的数据恢复工具,可以通过它从MySQL binlog解析出你要的SQL,并根据不同选项,可以得到原始SQL、回滚SQL、去除主键的INSERT SQL等。主要...【详细内容】
2024-02-22  数据库干货铺  微信公众号  Tags:MySQL   点击:(41)  评论:(0)  加入收藏
如何在MySQL中实现数据的版本管理和回滚操作?
实现数据的版本管理和回滚操作在MySQL中可以通过以下几种方式实现,包括使用事务、备份恢复、日志和版本控制工具等。下面将详细介绍这些方法。1.使用事务:MySQL支持事务操作,可...【详细内容】
2024-02-20  编程技术汇    Tags:MySQL   点击:(50)  评论:(0)  加入收藏
MySQL数据库如何生成分组排序的序号
经常进行数据分析的小伙伴经常会需要生成序号或进行数据分组排序并生成序号。在MySQL8.0中可以使用窗口函数来实现,可以参考历史文章有了这些函数,统计分析事半功倍进行了解。...【详细内容】
2024-01-30  数据库干货铺  微信公众号  Tags:MySQL   点击:(53)  评论:(0)  加入收藏
mysql索引失效的场景
MySQL中索引失效是指数据库查询时无法有效利用索引,这可能导致查询性能显著下降。以下是一些常见的MySQL索引失效的场景:1.使用非前导列进行查询: 假设有一个复合索引 (A, B)。...【详细内容】
2024-01-15  小王爱编程  今日头条  Tags:mysql索引   点击:(82)  评论:(0)  加入收藏
站内最新
站内热门
站内头条