MySQL事务已提交，数据却丢了，赶紧检查下这个配置！！！（收藏）

有个水友提问&＃xff1a;

沈老师&＃xff0c;我们有一次MySQL崩溃&＃xff0c;重启后发现有些已经提交的事务对数据的修改丢失了&＃xff0c;不是说事务能保证ACID特性么&＃xff0c;想问下什么情况下可能导致“事务已经提交&＃xff0c;数据却丢失”呢&＃xff1f;

这个问题有点复杂&＃xff0c;得先从redo log说起。

为什么要有redo log&＃xff1f;

事务提交后&＃xff0c;必须将事务对数据页的修改刷(fsync)到磁盘上&＃xff0c;才能保证事务的ACID特性。

这个刷盘&＃xff0c;是一个随机写&＃xff0c;随机写性能较低&＃xff0c;如果每次事务提交都刷盘&＃xff0c;会极大影响数据库的性能。

随机写性能差&＃xff0c;有什么优化方法呢&＃xff1f;

架构设计中有两个常见的优化方法&＃xff1a;

&＃xff08;1&＃xff09;先写日志(write log first)&＃xff0c;将随机写优化为顺序写&＃xff1b;

&＃xff08;2&＃xff09;将每次写优化为批量写&＃xff1b;

这两个优化&＃xff0c;数据库都用上了。

先说第一个优化&＃xff0c;将对数据的修改先顺序写到日志里&＃xff0c;这个日志就是redo log。

假如某一时刻&＃xff0c;数据库崩溃&＃xff0c;还没来得及将数据页刷盘&＃xff0c;数据库重启时&＃xff0c;会重做redo log里的内容&＃xff0c;以保证已提交事务对数据的影响被刷到磁盘上。

一句话&＃xff0c;redo log是为了保证已提交事务的ACID特性&＃xff0c;同时能够提高数据库性能的技术。

既然redo log能保证事务的ACID特性&＃xff0c;那为什么还会出现&＃xff0c;水友提问中出现的“数据库崩溃&＃xff0c;丢数据”的问题呢&＃xff1f;一起看下redo log的实现细节。

redo log的三层架构&＃xff1f;

画了一个丑图&＃xff0c;简单说明下redo log的三层架构&＃xff1a;

&＃xff08;1&＃xff09;粉色&＃xff0c;是InnoDB的一项很重要的内存结构(In-Memory Structure)&＃xff0c;日志缓冲区(Log Buffer)&＃xff0c;这一层&＃xff0c;是MySQL应用程序用户态&＃xff1b;

&＃xff08;2&＃xff09;屎黄色&＃xff0c;是操作系统的缓冲区(OS cache)&＃xff0c;这一层&＃xff0c;是OS内核态&＃xff1b;

&＃xff08;3&＃xff09;蓝色&＃xff0c;是落盘的日志文件&＃xff1b;

redo log最终落盘的步骤如何&＃xff1f;

首先&＃xff0c;事务提交的时候&＃xff0c;会写入Log Buffer&＃xff0c;这里调用的是MySQL自己的函数WriteRedoLog&＃xff1b;

接着&＃xff0c;只有当MySQL发起系统调用写文件write时&＃xff0c;Log Buffer里的数据&＃xff0c;才会写到OS cache。注意&＃xff0c;MySQL系统调用完write之后&＃xff0c;就认为文件已经写完&＃xff0c;如果不flush&＃xff0c;什么时候落盘&＃xff0c;是操作系统决定的&＃xff1b;

画外音&＃xff1a;有时候打日志&＃xff0c;明明printf了&＃xff0c;tail -f却看不到&＃xff0c;就是这个原因&＃xff0c;操作系统还没有刷盘。

最后&＃xff0c;由操作系统&＃xff08;当然&＃xff0c;MySQL也可以主动flush&＃xff09;将OS cache里的数据&＃xff0c;最终fsync到磁盘上&＃xff1b;

操作系统为什么要缓冲数据到OS cache里&＃xff0c;而不直接刷盘呢&＃xff1f;

这里就是将“每次写”优化为“批量写”&＃xff0c;以提高操作系统性能。

数据库为什么要缓冲数据到Log Buffer里&＃xff0c;而不是直接write呢&＃xff1f;

这也是“每次写”优化为“批量写”思路的体现&＃xff0c;以提高数据库性能。

画外音&＃xff1a;这个优化思路&＃xff0c;非常常见&＃xff0c;高并发的MQ落盘&＃xff0c;高并发的业务数据落盘&＃xff0c;都可以使用。

redo log的三层架构&＃xff0c;MySQL做了一次批量写优化&＃xff0c;OS做了一次批量写优化&＃xff0c;确实能极大提升性能&＃xff0c;但有什么副作用吗&＃xff1f;

画外音&＃xff1a;有优点&＃xff0c;必有缺点。

这个副作用&＃xff0c;就是可能丢失数据&＃xff1a;

&＃xff08;1&＃xff09;事务提交时&＃xff0c;将redo log写入Log Buffer&＃xff0c;就会认为事务提交成功&＃xff1b;

&＃xff08;2&＃xff09;如果写入Log Buffer的数据&＃xff0c;write入OS cache之前&＃xff0c;数据库崩溃&＃xff0c;就会出现数据丢失&＃xff1b;

&＃xff08;3&＃xff09;如果写入OS cache的数据&＃xff0c;fsync入磁盘之前&＃xff0c;操作系统崩溃&＃xff0c;也可能出现数据丢失&＃xff1b;

画外音&＃xff1a;如上文所说&＃xff0c;应用程序系统调用完write之后&＃xff08;不可能每次write后都立刻flush&＃xff0c;这样写日志很蠢&＃xff09;&＃xff0c;就认为写成功了&＃xff0c;操作系统何时fsync&＃xff0c;应用程序并不知道&＃xff0c;如果操作系统崩溃&＃xff0c;数据可能丢失。

任何脱离业务的技术方案都是耍流氓&＃xff1a;

&＃xff08;1&＃xff09;有些业务允许低效&＃xff0c;但不允许一丁点数据丢失&＃xff1b;

&＃xff08;2&＃xff09;有些业务必须高性能高吞吐&＃xff0c;能够容忍少量数据丢失&＃xff1b;

MySQL是如何折衷的呢&＃xff1f;

MySQL有一个参数&＃xff1a;

innodb_flush_log_at_trx_commit

能够控制事务提交时&＃xff0c;刷redo log的策略。

目前有三种策略&＃xff1a;

策略一&＃xff1a;最佳性能(innodb_flush_log_at_trx_commit&＃61;0)

每隔一秒&＃xff0c;才将Log Buffer中的数据批量write入OS cache&＃xff0c;同时MySQL主动fsync。

这种策略&＃xff0c;如果数据库崩溃&＃xff0c;有一秒的数据丢失。

策略二&＃xff1a;强一致(innodb_flush_log_at_trx_commit&＃61;1)

每次事务提交&＃xff0c;都将Log Buffer中的数据write入OS cache&＃xff0c;同时MySQL主动fsync。

这种策略&＃xff0c;是InnoDB的默认配置&＃xff0c;为的是保证事务ACID特性。

策略三&＃xff1a;折衷(innodb_flush_log_at_trx_commit&＃61;2)

每次事务提交&＃xff0c;都将Log Buffer中的数据write入OS cache&＃xff1b;

每隔一秒&＃xff0c;MySQL主动将OS cache中的数据批量fsync。

画外音&＃xff1a;磁盘IO次数不确定&＃xff0c;因为操作系统的fsync频率并不是MySQL能控制的。

这种策略&＃xff0c;如果操作系统崩溃&＃xff0c;最多有一秒的数据丢失。

画外音&＃xff1a;因为OS也会fsync&＃xff0c;MySQL主动fsync的周期是一秒&＃xff0c;所以最多丢一秒数据。

讲了这么多&＃xff0c;回到水友的提问上来&＃xff0c;数据库崩溃&＃xff0c;重启后丢失了数据&＃xff0c;有很大的可能&＃xff0c;是将innodb_flush_log_at_trx_commit参数设置为0了&＃xff0c;这位水友最好和DBA一起检查一下InnoDB的配置。

可能有水友要问&＃xff0c;高并发的业务&＃xff0c;InnoDB运用哪种刷盘策略最合适&＃xff1f;

高并发业务&＃xff0c;行业最佳实践&＃xff0c;是使用第三种折衷配置&＃xff08;&＃61;2&＃xff09;&＃xff0c;这是因为&＃xff1a;

&＃xff08;1&＃xff09;配置为2和配置为0&＃xff0c;性能差异并不大&＃xff0c;因为将数据从Log Buffer拷贝到OS cache&＃xff0c;虽然跨越用户态与内核态&＃xff0c;但毕竟只是内存的数据拷贝&＃xff0c;速度很快&＃xff1b;

&＃xff08;2&＃xff09;配置为2和配置为0&＃xff0c;安全性差异巨大&＃xff0c;操作系统崩溃的概率相比MySQL应用程序崩溃的概率&＃xff0c;小很多&＃xff0c;设置为2&＃xff0c;只要操作系统不崩溃&＃xff0c;也绝对不会丢数据。

总结
一、为了保证事务的ACID特性&＃xff0c;理论上每次事务提交都应该刷盘&＃xff0c;但此时效率很低&＃xff0c;有两种优化方向&＃xff1a;
&＃xff08;1&＃xff09;随机写优化为顺序写&＃xff1b;
&＃xff08;2&＃xff09;每次写优化为批量写&＃xff1b;

二、redo log是一种顺序写&＃xff0c;它有三层架构&＃xff1a;
&＃xff08;1&＃xff09;MySQL应用层&＃xff1a;Log Buffer
&＃xff08;2&＃xff09;OS内核层&＃xff1a;OS cache
&＃xff08;3&＃xff09;OS文件&＃xff1a;log file

三、为了满足不同业务对于吞吐量与一致性的需求&＃xff0c;MySQL事务提交时刷redo log有三种策略&＃xff1a;
&＃xff08;1&＃xff09;0&＃xff1a;每秒write一次OS cache&＃xff0c;同时fsync刷磁盘&＃xff0c;性能好&＃xff1b;
&＃xff08;2&＃xff09;1&＃xff1a;每次都write入OS cache&＃xff0c;同时fsync刷磁盘&＃xff0c;一致性好&＃xff1b;
&＃xff08;3&＃xff09;2&＃xff1a;每次都write入OS cache&＃xff0c;每秒fsync刷磁盘&＃xff0c;折衷&＃xff1b;

四、高并发业务&＃xff0c;行业内的最佳实践&＃xff0c;是&＃xff1a;
innodb_flush_log_at_trx_commit&＃61;2

知其然&＃xff0c;知其所以然&＃xff0c;希望大家有收获。

架构师之路-分享技术思路

相关推荐&＃xff1a;

《架构师之路&＃xff0c;21年干货精选》

贵司线上的配置是多少&＃xff1f;丢过数据么&＃xff1f;