MySQL数据丢失讨论,开发进阶篇系列

一. innodb_flush_log_at_trx_commit

 这个参数名称有个log,一看就是与日志有关。是指:用来控制缓冲区(log buffer)中的数据写入到日志文件(log file),以及日志文件数据刷新到磁盘(flush)的操作时机。对这个参数的设置值,可以对数据库在性能与数据安全之间,进行折中。 

  参数值解释:

    当参数是0:日志缓冲数据会,每秒一次地写入到日志文件,并且把日志文件刷新到磁盘操作。该模式下在事务提交的时候,不会主动触发写入磁盘的操作。

    当参数是1:每次事务提交时,日志缓冲被写到日志文件,并且对日志文件做磁盘刷新操作,该模式为系统默认。但由于每次事务都需要进行磁盘I/O,所以也最慢。

    当参数是2:每次事务提交时,日志缓冲被写到日志文件,但不对日志文件做磁盘刷新操作。对日志文件每秒执行一次,刷到磁盘操作。

  当设置innodb_flush_log_at_trx_commit=1时, 是默认值,也是最安全的设置,但是在这种模式下性能有一定的损失。 如果设置成0或者2 性能会有所改善,但有数据丢失的风险。
  设置成0则数据库崩溃的时候,那些没有被写入日志文件的事务丢失,最多丢失1秒钟的事务,是最不安全的,但也是效率最高的。
  设置成2则只是没有刷新到磁盘,但已经写入日志文件,所以只要操作系统没有崩溃, 那么并没有数据丢失, 比设置成0更安全。
  在mysql官方中, 为了确保事务的持久性和复制设置的一致性,都是建议将这个参数值设置为1。对于一些数据一致性和完整性要求不高的应用,配置为 2 就足够了;如果为了最高性能,可以设置为 0。有些应用,如支付服务,对一致性和完整性要求很高,所以即使最慢,也最好设置为 1。

参数值

数据安全性

I/O性能

0

安全最差。当数据库崩溃,有丢失1秒钟的事务风险

最优

1

安全最好。无丢失数据

最差

2

安全折中。当操作系统崩溃, 有丢失1秒钟的事务风险

折中

  1.1 查看日志提交方式

  SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

    图片 1

  1.2 修改参数值

           还是一样找到my.cnf, 修改参数值

           [root@xuegod64 ~]# cd /etc

           [root@xuegod64 etc]# vim my.cnf
    图片 2

    [root@xuegod64 ~]# systemctl stop mysqld.service

    [root@xuegod64 ~]# systemctl start  mysqld.service

-- 再次查看
SHOW VARIABLES LIKE 'innodb_flush_log_at_trx_commit';

    图片 3

原文:

原文地址:

二. sync_binlog  

  这个参数是对于MySQL系统来说是至关重要的,他不仅影响到Binlog对MySQL所带来的性能损耗,而且还影响到MySQL中数据的完整性。在MySQL中系统默认的设置是sync_binlog=1。对于“sync_binlog”参数的各种设置的说明如下:

  sync_binlog=0:当事务提交之后,不主动刷新二进制日志文件的数据到磁盘上,而是由操作系统决定。

  sync_binlog=n:每向二进制日志文件写入N条SQL或N个事务后,则把二进制日志文件的数据刷新到磁盘上。

  而当设置为“1”的时候,是最安全但是性能损耗最大的设置。因为当设置为1的时候,即使系统Crash,也最多丢失binlog_cache中未完成的一个事务,对实际数据没有任何实质性影响。|

--  查看binlog写入方式
SHOW VARIABLES LIKE 'sync_binlog';

  图片 4

  总结: 在数据安全与性能以日志文件作为出发点时,我认为功能上与sql server 的数据恢复模式比较相像,但实现的思路是不一样的。
  innodb_flush_log_at_trx_commit和sync_binlog是MySQL innodb引擎的两个重要的参数,其中innodb_flush_log_at_trx_commit是将事务日志从innodb log buffer刷新到磁盘,sync_binlog是将二进制日志文件刷新到磁盘上。
  innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数,当两个参数都设置为1的时候写入性能最差,
网上也有说将innodb_flush_log_at_trx_commit=2,sync_binlog=500 或1000。有说对于高并发事务的系统来说,“sync_binlog”设置为0和设置为1的系统写入性能差距可能高达5倍甚至更多。总体上还是要根据业务来判断,在性能和安全上做个选择。

 innodb_flush_log_at_trx_commit和sync_binlog 两个参数是控制MySQL 磁盘写入策略以及数据安全性的关键参数。本文从参数含义,性能,安全角度阐述两个参数为不同的值时对db 性能,数据的影响.

1.  概述

很多企业选择MySQL都会担心它的数据丢失问题,从而选择Oracle,但是其实并不十分清楚什么情况下,各种原因导致MySQL会丢失部分数据。本文不讨论Oracle和MySQL的优劣,仅仅关注MySQL丢失数据的几种情况。希望能够抛砖引玉,让各位MySQL大牛们梳理出MySQL最安全或者性价比合适的适合各种应用场景的方案。

一 参数意义

2.  问题定义

一般我们希望把一系列的数据作为一个原子操作,这样的话,这一系列操作,要么提交,要么全部回滚掉。

当我们提交一个事务,数据库要么告诉我们事务提交成功了,要么告诉我们提交失败。

数据库为了效率等原因,数据只保存在内存中,没有真正的写入到磁盘上去。如果数据库响应为“提交成功”,但是由于数据库挂掉,操作系统,数据库主机等任何问题导致这次“提交成功”的事务对数据库的修改没有生效,那么我们认为这个事务的数据丢失了。这个对银行或者支付宝这种业务场景来说是不能接受的。所以,保证数据不丢失也是数据库选择的一个重要衡量指标

mysql的架构和普通的数据库架构最大的差异在于它使用插件式的存储引擎。数据的存取由存储引擎负责。要了解MySQL数据丢失的问题就需要从MySQL server层和InnoDB目前最流行的支持事务的存储引擎分别来分析了。

innodb_flush_log_at_trx_commit

3.  InnoDB事务数据丢失

首先,我们来看一下InnoDB事务数据丢失的情况。

如果innodb_flush_log_at_trx_commit设置为0,log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行.该模式下,在事务提交的时候,不会主动触发写入磁盘的操作。
如果innodb_flush_log_at_trx_commit设置为1,每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去.
如果innodb_flush_log_at_trx_commit设置为2,每次事务提交时MySQL都会把log buffer的数据写入log file.但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。

3.1. InnoDB事务基本原理

InnoDB的事务提交需要写入undo log,redo log,以及真正的数据页。专业的介绍可以参考丁奇和云华的两篇文章。我们这里通俗一点简单介绍一下。

InnoDB跟Oracle非常类似,使用日志先行的策略,将数据的变更在内存中完成,并且将事务记录成redo,转换为顺序IO高效的提交事务。这里日志先行,也就是说,日志记录到数据库以后,对应的事务就可以返回给用户,表示事务完成。但是实际上,这个数据可能还只在内存中修改完成,并没有刷到磁盘上去,俗称“还没有落地”。内存是易失的,如果在数据“落地”之前,机器挂了,那么这部分数据就丢失了。而数据库怎么保证这些数据还是能够找回来列?否则,用户提交了一个事务,数据库响应请求并回应为事务“提交成功”,数据库重启以后,这部分修改数据的却回到了事务提交之前的状态。

 

3.2. InnoDB事务崩溃恢复基本原理

InnoDB和Oracle都是利用redo来保证数据一致性的。如果你有从数据库新建一直到数据库挂掉的所有redo,那么你可以将数据完完整整的重新build出来。但是这样的话,速度肯定很慢。所以一般每隔一段时间,数据库会做一个checkpoint的操作,做checkpoint的目的就是为了让在该时刻之前的所有数据都”落地”。这样的话,数据库挂了,内存中的数据丢了,不用从最原始的位置开始恢复,而只需要从最新的checkpoint来恢复。将已经提交的所有事务变更到具体的数据块中,将那些未提交的事务回滚掉。

注意:

3.3. InnoDB redo日志

这样的话,保证事务的redo日志刷到磁盘就成了事务数据是否丢失的关键。而InnoDB为了保证日志的刷写的高效,使用了内存的log buffer,另外,由于InnoDB大部分情况下使用的是文件系统,(linux文件系统本身也是有buffer的)而不是直接使用物理块设备,这样的话就有两种丢失日志的可能性:日志保存在log_buffer中,机器挂了,对应的事务数据就丢失了;日志从log buffer刷到了linux文件系统的buffer,机器挂掉了,对应的事务数据就丢失了。当然,文件系统的缓存刷新到硬件设备,还有可能被raid卡的缓存,甚至是磁盘本身的缓存保留,而不是真正的写到磁盘介质上去了。这个就不在我们这次讨论的范围内了。

InnoDB的日志你还可以参考这篇文章

  由于进程调度策略问题,这个“每秒执行一次 flush(刷到磁盘)操作”并不是保证100%的“每秒”。

3.4. innodb_flush_log_at_trx_commit

所以InnoDB有一个特别的参数用于设置这两个缓存的刷新: innodb_flush_log_at_trx_commit。

默认,innodb_flush_log_at_trx_commit=1,表示在每次事务提交的时候,都把log buffer刷到文件系统中去,并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。这样的话,数据库对IO的要求就非常高了,如果底层的硬件提供的IOPS比较差,那么MySQL数据库的并发很快就会由于硬件IO的问题而无法提升。

为了提高效率,保证并发,牺牲一定的数据一致性。innodb_flush_log_at_trx_commit还可以设置为0和2。

innodb_flush_log_at_trx_commit=0时,每隔一秒把log buffer刷到文件系统中去,并且调用文件系统的“flush”操作将缓存刷新到磁盘上去。这样的话,可能丢失1秒的事务数据。

innodb_flush_log_at_trx_commit=2时,在每次事务提交的时候会把log buffer刷到文件系统中去,但是每隔一秒调用文件系统的“flush”操作将缓存刷新到磁盘上去。如果只是MySQL数据库挂掉了,由于文件系统没有问题,那么对应的事务数据并没有丢失。只有在数据库所在的主机操作系统损坏或者突然掉电的情况下,数据库的事务数据可能丢失1秒之类的事务数据。这样的好处就是,减少了事务数据丢失的概率,而对底层硬件的IO要求也没有那么高(log buffer写到文件系统中,一般只是从log buffer的内存转移的文件系统的内存缓存中,对底层IO没有压力)。MySQL 5.6.6以后,这个“1秒”的刷新还可以用innodb_flush_log_at_timeout 来控制刷新间隔。

在大部分应用环境中,应用对数据的一致性要求并没有那么高,所以很多MySQL DBA会设置innodb_flush_log_at_trx_commit=2,这样的话,数据库就存在丢失最多1秒的事务数据的风险。

引用应元的一个图如下:

  

4.  数据库复制导致数据丢失

MySQL相比其他数据库更适用于互联网的其中一个重要特性就是MySQL的复制。对于互联网这种需要提供7*24小时不间断的服务的要求,MySQL提供异步的数据同步机制。利用这种复制同步机制,当数据库主库无法提供服务时,应用可以快速切换到跟它保持同步的一个备库中去。备库继续为应用提供服务,从而不影响应用的可用性。

这里有一个关键的问题,就是应用切换到备库访问,备库的数据需要跟主库的数据一致才能保证不丢失数据。由于目前MySQL还没有提供全同步的主备复制解决方案所以这里也是可能存在数据丢失的情况。

目前MySQL提供两种主备同步的方式:异步(asynchronous)和半同步(Semi-sync)

sync_binlog

4.1. MySQL复制原理简介

MySQL复制的原理简介如下:MySQL主库在事务提交时写binlog,并通过sync_binlog参数来控制binlog刷新到磁盘“落地”。而备库通过IO线程从主库拉取binlog,并记录到本地的relay log中;由本地的SQL线程再将relay log中的数据应用到本地数据库中。

异步的方式下,几个线程都是独立的,相互不依赖。

而在半同步的情况下,主库的事务提交需要保证至少有一个备库的IO线程已经拉到了数据,这样保证了至少有一个备库有最新的事务数据,避免了数据丢失。这里称为半同步,是因为主库并不要求SQL线程已经执行完成了这个事务。

半同步在MySQL 5.5才开始提供,并且可能引起并发和效率的一系列问题,比如只有一个备库,备库挂掉了,那么主库在事务提交10秒(rpl_semi_sync_master_timeout控制)后,才会继续,之后变成传统的异步方式。所以目前在生产环境下使用半同步的比较少。

在异步方式下,如何保证数据尽量不丢失就成了主要问题。这个问题其实就是如何保证数据库的binlog不丢失,尽快将binlog落地,这样就算数据库挂掉了,我们还可以通过binlog来将丢失的部分数据手工同步到备库上去(MHA会自动抽取缺失的部分补全备库)。

图示如下:

sync_binlog 的默认值是0,像操作系统刷其他文件的机制一样,MySQL不会同步到磁盘中去而是依赖操作系统来刷新binary log。

4.2. sync_binlog

这个问题就跟上一个innodb_flush_log_at_trx_commit的问题类似了。MySQL提供一个sync_binlog参数来控制数据库的binlog刷到磁盘上去。虽然binlog也有binlog cache,但是MySQL并没有控制binlog cache同步到文件系统缓存的相关考虑。所以我们这里不涉及binlog cache。

默认,sync_binlog=0,表示MySQL不控制binlog的刷新,由文件系统自己控制它的缓存的刷新。

如果sync_binlog>0,表示每sync_binlog次事务提交,MySQL调用文件系统的刷新操作将缓存刷下去。最安全的就是sync_binlog=1了,表示每次事务提交,MySQL都会把binlog刷下去。这样的话,在数据库所在的主机操作系统损坏或者突然掉电的情况下,系统才有可能丢失1个事务的数据。但是binlog虽然是顺序IO,但是设置sync_binlog=1,多个事务同时提交,同样很大的影响MySQL和IO性能。虽然可以通过group commit的补丁缓解,但是刷新的频率过高对IO的影响也非常大。

所以很多MySQL DBA设置的sync_binlog并不是最安全的1,而是100或者是0。这样牺牲一定的一致性,可以获得更高的并发和性能。

当sync_binlog =N (N>0) ,MySQL 在每写 N次 二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。

5.  MySQL和InnoDB协同

注:

5.1. 两段式事务提交

最后我们需要讨论一下上述两个参数对应的redolog和 binlog协同的问题。这两个log都影响数据丢失,但是他们分别在InnoDB和MySQL server层维护。由于一个事务可能使用两种事务引擎,所以MySQL用两段式事务提交来协调事务提交。我们先简单了解一下两段式事务提交的过程

第一阶段:

首先,协调者在自身节点的日志中写入一条的日志记录,然后所有参与者发送消息prepare T,询问这些参与者(包括自身),是否能够提交这个事务;

参与者在接受到这个prepare T 消息以后,会根据自身的情况,进行事务的预处理,如果参与者能够提交该事务,则会将日志写入磁盘,并返回给协调者一个ready T信息,同时自身进入预提交状态状态;如果不能提交该事务,则记录日志,并返回一个not commit T信息给协调者,同时撤销在自身上所做的数据库改;

参与者能够推迟发送响应的时间,但最终还是需要发送的。

第二阶段:

协调者会收集所有参与者的意见,如果收到参与者发来的not commit T信息,则标识着该事务不能提交,协调者会将Abort T 记录到日志中,并向所有参与者发送一个Abort T 信息,让所有参与者撤销在自身上所有的预操作;

如果协调者收到所有参与者发来prepare T信息,那么协调者会将Commit T日志写入磁盘,并向所有参与者发送一个Commit T信息,提交该事务。若协调者迟迟未收到某个参与者发来的信息,则认为该参与者发送了一个VOTE_ABORT信息,从而取消该事务的执行。

参与者接收到协调者发来的Abort T信息以后,参与者会终止提交,并将Abort T 记录到日志中;如果参与者收到的是Commit T信息,则会将事务进行提交,并写入记录

一般情况下,两阶段提交机制都能较好的运行,当在事务进行过程中,有参与者宕机时,他重启以后,可以通过询问其他参与者或者协调者,从而知道这个事务到底提交了没有。当然,这一切的前提都是各个参与者在进行每一步操作时,都会事先写入日志。

具体的介绍可以参考《事务和两阶段提交》以及《分布式事务设计-两阶段提交》

   如果启用了autocommit,那么每一个语句statement就会有一次写操作;否则每个事务对应一个写操作。

5.2. innodb_support_xa

innodb_support_xa可以开关InnoDB的xa两段式事务提交。默认情况下,innodb_support_xa=true,支持xa两段式事务提交。此时MySQL首先要求innodb prepare,对应的redolog 将写入log buffer;如果有其他的引擎,其他引擎也需要做事务提交的prepare,然后MySQL server将binlog将写入;并通知各事务引擎真正commit;InnoDB将commit标志写入,完成真正的提交,响应应用程序为提交成功。这个过程中任何出错将导致事务回滚,响应应用程序为提交失败。也就是说,在这种情况下,基本不会出错。

但是由于xa两段式事务提交导致多余flush等操作,性能影响会达到10%,所有为了提高性能,有些DBA会设置innodb_support_xa=false。这样的话,redolog和binlog将无法同步,可能存在事务在主库提交,但是没有记录到binlog的情况。这样也有可能造成事务数据的丢失。

 

综上,我们列举了影响InnoDB数据丢失的参数innodb_flush_log_at_trx_commit,影响MySQL复制数据丢失的sync_binlog,以及由于MySQL和InnoDB需要协调而可能导致数据丢失的参数innodb_support_xa。

标签:innodb_flush_log_at_trx_commit, innodb_support_xa, mysql, sync_binlog, 丢失, 数据
Category: mysql  |  Comment

   根据上述描述,我做了一张图,可以方便大家查看。
  图片 5

mysql innodb_flush_log_at_trx_commit翻译

2010年05月12日, 3:36 下午

知道innodb_flush_log_at_trx_commit的意思,但是对它取值0,1,2一直有点模糊不清。特地找了MySQL 5.1的refrence,自己翻译一下。虽然,也有官方的中文版翻译,但是不好意思,有点不相信它。

英文原文如下:
innodb_flush_log_at_trx_commit
Command-Line Format     –innodb_flush_log_at_trx_commit[=#]
Config-File Format     innodb_flush_log_at_trx_commit
Option Sets Variable     Yes, innodb_flush_log_at_trx_commit
Variable Name     innodb_flush_log_at_trx_commit
Variable Scope     Global
Dynamic Variable     Yes
Permitted Values
Type     numeric
Default     1
Valid Values     0, 1, 2

If the value of innodb_flush_log_at_trx_commit is 0, the log buffer is written out to the log file once per second and the flush to disk operation is performed on the log file, but nothing is done at a transaction commit. When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. When the value is 2, the log buffer is written out to the file at each commit, but the flush to disk operation is not performed on it. However, the flushing on the log file takes place once per second also when the value is 2. Note that the once-per-second flushing is not 100% guaranteed to happen every second, due to process scheduling issues.

如果innodb_flush_log_at_trx_commit设置为0,log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行;但是,这种模式下,在事务提交的时候,不会有任何动作。如果innodb_flush_log_at_trx_commit设置为1(默认值),log buffer每次事务提交都会写入log file,并且,flush刷到磁盘中去。如果innodb_flush_log_at_trx_commit设置为2,log buffer在每次事务提交的时候都会写入log file,但是,flush(刷到磁盘)操作并不会同时进行。这种模式下,MySQL会每秒一次地去做flush(刷到磁盘)操作。注意:由于进程调度策略问题,这个“每秒一次的flush(刷到磁盘)操作”并不是保证100%的“每秒”。

The default value of 1 is the value required for ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose at most one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, then only an operating system crash or a power outage can erase the last second of transactions. However, InnoDB’s crash recovery is not affected and thus crash recovery does work regardless of the value.

默认值1是为了ACID (atomicity, consistency, isolation, durability)原子性,一致性,隔离性和持久化的考虑。如果你不把innodb_flush_log_at_trx_commit设置为1,你将获得更好的性能,但是,你在系统崩溃的情况,可能会丢失最多一秒钟的事务数据。当你把innodb_flush_log_at_trx_commit设置为0,mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。如果你把innodb_flush_log_at_trx_commit设置为2,只有在操作系统崩溃或者系统掉电的情况下,上一秒钟所有事务数据才可能丢失。(下面的这句话到底是针对innodb_flush_log_at_trx_commit为2说的,还是针对前面这一整段说的,我就搞不清楚了,下次问问编写这一段文档的MySQL的人去。感觉是针对整段的:就是说InnoDB的crash recovery会利用log file来恢复数据文件,跟innodb_flush_log_at_trx_commit的值没有关系,管你这个值怎么设置的,我从log file拿到多少数据,就恢复多少数据。)InnoDB的crash recovery崩溃恢复机制并不受这个值的影响,不管这个值设置为多少,crash recovery崩溃恢复机制都会工作。

For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, use innodb_flush_log_at_trx_commit = 1 and sync_binlog = 1 in your master server my.cnf file.

为了在使用InnoDB事务的搭建复制环境中,达到最大的持久化和一致性,你需要在你的master主机的my.cnf中设置innodb_flush_log_at_trx_commit = 1并且设置sync_binlog = 1。

Caution

Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt the InnoDB database. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try using the Unix command hdparm to disable the caching of disk writes in hardware caches, or use some other command specific to the hardware vendor.

注意:
很多操作系统和一些磁盘硬件系统并不会真正的做flush-to-disk刷新到磁盘的这个操作。他们即使并没有真正刷到磁盘也会告诉mysqld说flush刷新到磁盘的操作已经完成了。这样的话,即使innodb_flush_log_at_trx_commit设置为1,也不能保证事务的持久化,最糟的情况下,一个主机掉电,就有可能导致InnoDB数据库崩溃。你可以考虑在SCSI磁盘控制器里面或者磁盘本身中,使用带蓄电池后备电源的磁盘缓存disk cache,来提高文件刷新操作的速度,使得这个操作更加安全。你同样可以尝试使用Unix的hdparm命令来阻止硬件缓存hardware cache的写磁盘缓存操作,或者使用其他硬件提供商hardware vendor提供的命令来避免写磁盘缓存。

这里既然提到了sync_binlog就顺便把它也翻译一下。
sync_binlog
Command-Line Format     –sync-binlog=#
Config-File Format     sync_binlog
Option Sets Variable     Yes, sync_binlog
Variable Name     sync_binlog
Variable Scope     Global
Dynamic Variable     Yes
Permitted Values
Platform Bit Size     32
Type     numeric
Default     0
Range     0-4294967295
Permitted Values
Platform Bit Size     64
Type     numeric
Default     0
Range     0-18446744073709547520

If the value of this variable is greater than 0, the MySQL server synchronizes its binary log to disk (using fdatasync()) after every sync_binlog writes to the binary log. There is one write to the binary log per statement if autocommit is enabled, and one write per transaction otherwise. The default value of sync_binlog is 0, which does no synchronizing to disk — in this case, the server relies on the operating system to flush the binary log’s contents from to time as for any other file. A value of 1 is the safest choice because in the event of a crash you lose at most one statement or transaction from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).

当sync_binlog变量设置为大于0的值时,MySQL在每次“sync_binlog”这么多次写二进制日志binary log时,会使用fdatasync()函数将它的写二进制日志binary log同步到磁盘中去。如果启用了autocommit,那么每一个语句statement就会有一次写操作;否则每个事务对应一个写操作。sync_binlog的默认值是0,这种模式下,MySQL不会同步到磁盘中去。这样的话,MySQL依赖操作系统来刷新二进制日志binary log,就像操作系统刷其他文件的机制一样。当sync_binlog变量设置为1是最安全的,因为在crash崩溃的情况下,你的二进制日志binary log只有可能丢失最多一个语句或者一个事务。但是,这也是最慢的一种方式(除非磁盘有使用带蓄电池后备电源的缓存cache,使得同步到磁盘的操作非常快)。

找了一下man fdatasync:
fdatasync() flushes all data buffers of a file to disk (before the system call returns).  It resembles fsync() but is not required to update the metadata such as access time.
fdatasync() (在系统调用system call返回前)将文件中所有的数据缓存区data buffers都flush刷到磁盘中去。它类似于fsync()函数,但是它不会更新元数据metadata:比如最后访问时间等。

二 性能

    两个参数在不同值时对db的纯写入的影响表现如下:

    图片 6

 测试场景1 

  innodb_flush_log_at_trx_commit=2 

  sync_binlog=1000

 测试场景2 

  innodb_flush_log_at_trx_commit=1 

  sync_binlog=1000

 测试场景3 

  innodb_flush_log_at_trx_commit=1 

  sync_binlog=1

 测试场景4

  innodb_flush_log_at_trx_commit=1

  sync_binlog=1000

 测试场景5 

  innodb_flush_log_at_trx_commit=2 

  sync_binlog=1000 

 

场景 TPS
场景1 41000
场景2 33000
场景3 26000
场景4 33000

由此可见,当两个参数设置为双1的时候,写入性能最差,sync_binlog=N (N>1 ) innodb_flush_log_at_trx_commit=2 时,(在当前模式下)MySQL的写操作才能达到最高性能。

三 安全

当innodb_flush_log_at_trx_commit和sync_binlog  都为 1 时是最安全的,在mysqld 服务崩溃或者服务器主机crash的情况下,binary log 只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得,双11 会导致频繁的io操作,因此该模式也是最慢的一种方式。

当innodb_flush_log_at_trx_commit设置为0,mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
当innodb_flush_log_at_trx_commit设置为2,只有在操作系统崩溃或者系统掉电的情况下,上一秒钟所有事务数据才可能丢失。

 

双1适合数据安全性要求非常高,而且磁盘IO写能力足够支持业务,比如订单,交易,充值,支付消费系统。双1模式下,当磁盘IO无法满足业务需求时 比如11.11 活动的压力。推荐的做法是 innodb_flush_log_at_trx_commit=2 ,sync_binlog=N (N为500 或1000) 且使用带蓄电池后备电源的缓存cache,防止系统断电异常。

 

 

四 小结

    系统性能和数据安全是业务系统高可用稳定的必要因素。我们对系统的优化需要寻找一个平衡点,合适的才是最好的,根据不同的业务场景需求,可以将两个参数做组合调整,以便是db系统的性能达到最优化。

 

参考文章

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:MySQL数据丢失讨论,开发进阶篇系列

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。