5.7新特性

本文仅翻译了5.7.4 新增和修改的属性,有兴趣的同学可以深入里面感兴趣的东西细看。

增强特性
  1. 安全:
    • 不支持老的password算法
    • 支持密码过期
    • 新装的机器只有root@localhost,匿名账户和test schema都不默认安装了。root密码第一次登录需要修改密码。
  2. sql mode默认值修改: NO_ENGINE_SUBSTITUTION to NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES.

  3. alter table 的rename index从copy table变成inplace

  4. Innodb增强:

    • varchar()字段变长部分支持inplace的online ddl。length bytes 变化和varchar()长度缩短都必须使用copy的方式来做ddl。length bytes:0-255 长度是1个字节;256和256以上长度是2字节。length bytes不变的话,支持inplace修改。length bytes变化的话,必须用copy算法
      ALTER TABLE t1 ALGORITHM=INPLACE, CHANGE COLUMN c1 c1 VARCHAR(256);
      ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Cannot change
      column type INPLACE. Try ALGORITHM=COPY.
    • CREATE TABLE, DROP TABLE, TRUNCATE TABLE, and ALTER TABLE 的DDL 临时表性能增强。啥都没说
    • innodb的临时表元信息不再存在innodb的系统表空间,而是存储再INNODB_TEMP_TABLE_INFO。从这个表可以查到当前活跃的临时表,不管是用户的还是系统帮忙创建的。
    • innodb支持mysql的空间数据。之前用BLOB现在用DATA_GEOMETRY支持。
    • 对非压缩的Innodb临时表有了专门的独立表空间。这个表空间默认在系统启动时自动在DATADIR上创建起来。另外,可以通过innodb_temp_data_file_path来指定文件路径。
    • innodchecksum增强。
    • innodb buffer pool dump和load增强了。通过innodb_buffer_pool_dump_pct参数控制buffer pool里面最近使用的page中有多少百分比读出并dump去。当InnoDB后台任务在做IO时,InnoDB通过 innodb_io_capacity来限制加载的速度。
    • InnoDB增加了对全文索引插件的支持。
    • Innodb对buffer pool instance提供多页清理线程。innodb_page_cleaners可以指定线程数。跟purge不同:
      purge: A type of garbage collection performed by a separate thread, running on a periodic schedule. The purge includes these actions: removing obsolete values from indexes; physically removing rows that were marked for deletion by previous DELETE statements. 清理index里面的过时数据,对delete删除的数据进行物理清除。cleaner是做脏页刷新的。
    • 支持InnoDB的普通和分区表 online ddl(ALGORITHM=INPLACE)OPTIMIZE TABLE ALTER TABLE … FORCE ALTER TABLE … ENGINE=INNODB (when run on an InnoDB table)。这样的话,online ddl能够缩减吃哦年间时间,允许并并发DML,用户的停机时间就减少了。
    • Fusion-io Non-Volatile Memory (NVM)文件系统支持 atomic write ,这样innodb doublewrite buffer比较多余了,对于把ibdata表空间文件存放在支持原子写的Fusion io上的时候,Innodb的doublewrite buffer会自动禁用掉
    • innodb 支持对分区表和单个的innodb表分区 Transportable Tablespace特性。这样,分区表备份,分区表在MySQL各个instance中迁移就方便了很多。
    • innodb_buffer_pool_size 可以在线修改,不用重启服务器了。通过 innodb_buffer_pool_chunk_size 来控制内存中的多少个页挪动。通过Innodb_buffer_pool_resize_status 监控buffer pool迁移状态。
      Active transactions, and operations performed through InnoDB APIs, should be completed before resizing the buffer pool. When initiating a resizing operation, the operation does not start until all active transactions are completed. Once the resizing operation is in progress, new transactions and operations that require access to the buffer pool must wait until the resizing operation finishes. The exception to this rule is that concurrent access to the buffer pool is permitted when defragmenting the buffer pool and withdrawing pages during an operation to decrease buffer pool size. A drawback of allowing concurrent buffer pool access while withdrawing pages is that there could be a temporary shortage of available pages during this phase of the resizing operation.
      活动事务/通过Innodb api进行的动作需要在buffer pool变更大小前做完。也就是说,resize buffer pool会一直等到active transaction完成之后才开始。这个动作开始后,新的事务和操作只要需要用到buffer pool都必须等到resizing完成。例外:当进行buffer pool碎片整理和释放page以便减少buffer pool时,可以访问buffer pool。允许在释放page时同时访问buffer pool的一个缺点是在这个resizing操作时有一个临时缺页的问题。
  5. 增强了MySQL存储过程。MySQL现在有了stacked diagnostics areas。对于执行过的SQL可以获得它返回的各种信息:
    mysql> DROP TABLE test.no_such_table;
    ERROR 1051 (42S02): Unknown table ‘test.no_such_table’
    mysql> GET DIAGNOSTICS CONDITION 1
    -> @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
    mysql> SELECT @p1, @p2;
    +——-+————————————+
    | @p1 | @p2 |
    +——-+————————————+
    | 42S02 | Unknown table ‘test.no_such_table’ |
    +——-+————————————+

  6. explain可以对正在运行的连接显示执行情况。如果你需要了解那些运行时间很长的会话SQL执行为什么这么慢,就可以用这种方法。

  7. trigger。之前在同一个表上的(insert,update,delete)的(before,after)只能加一个trigger。现在可以加多个了。

  8. mysql 客户端支持–syslog,这样mysql客户端提交的语句就会被记录到系统日志中。可以用来做审核和安全审计。

  9. Test suite. 现在使用innodb作为默认存储引擎

  10. mysql client中ctrl+c会中断真正执行的SQL,或者退出MySQL。但是新版本中只会中断SQL或者中断输入,不会退出MySQL了。

  11. mysqlbinlog现在也支持 –rewrite-db 在输出的时候把数据库名修改掉。mysql自身的复制是本来就支持这个特性的。现在给mysqlbinlog工具也增加了这个特性。

  12. handler 在5.7中支持分区表了。handler提供对表存储引擎的直接访问。比如HANDLER … OPEN 可以打开一张表,HANDLER tbl_name READ index_name =可以直接从存储引擎读一行数据。好处在于比select块,没有Server层的解析,快速。缺点在于没有数据快照,读到的数据可能是刚刚被其他session修改过的。

  13. 分区表 现在支持Index condition pushdown。

  14. alter table exchange artition现在可以忽略一行一行的验证数据。(without validation)

  15. 给slave发送binlog信息的master dump thread减少了锁竞争,增强了吞吐量。之前,master dump thread只要读一个event就要给binlog加一个锁,5.7.2之后,它只在读上次成功写入event的位置的时候加锁。这样多个线程同时读binlog文件也快了,就算binlog 文件正被写入,也没有问题。

  16. 扩展了GB18030字符集

  17. change master to部分情况下可以不用stop slave了。

    • SQL thread停掉时,change master可以修改relay_log_file,relay_log_pos,master_delay.
    • io thread 停掉时,你可以修改除relay_log_file,relay_log_pos,master_delay.以外的所有参数值。
    • io,SQL thread都停掉了,你可以修改master_auto_position=1
去掉的特性
  • insert delay 不支持了
  • show engine innodb mutex去掉了
  • innodb tablespace monitor/innodb table monitor也去掉了
  • innodb_monitor、innodb_lock_monitor去掉了.修改为两个系统变量
  • innodb_status_output、innnodb_status_output_locks.
    innodb_user_sys_malloc、innodb_additional_mem_pool_size去掉了。

细看InnoDB数据落盘

1.  概述

前面很多大侠都分享过MySQL的InnoDB存储引擎将数据刷新的各种情况。我们这篇文章从InnoDB往下,看看数据从InnoDB的内存到真正写到存储设备的介质上到底有哪些缓冲在起作用。

我们通过下图看一下相关的缓冲:

all_buffers

图 1 innodb all buffers

 

从上图中,我们可以看到,数据InnoDB到磁盘需要经过

  1. InnoDB buffer pool, Redo log buffer。这个是InnoDB应用系统本身的缓冲。
  2. page cache /Buffer cache(可通过o_direct绕过)。这个是vfs层的缓冲。
  3. Inode cache/directory buffer。这个也是vfs层的缓冲。需要通过O_SYNC或者fsync()来刷新。
  4. Write-Back buffer。(可设置存储控制器参数绕过)
  5. Disk on-borad buffer。(可通过设置磁盘控制器参数绕过)

这里我们使用术语“缓冲”(一般为buffer)来表示对数据写的暂存,使用术语“缓存”(一般为cache)来表示对数据读的暂存。顾名思义,由于底层存储设备和内存之间速率的差异,缓冲是用来暂“缓”对底层存储设备IO的“冲”击。缓存主要是在内存中暂“存”从磁盘读到的数据,以便接下来对这些数据的访问不用再次访问慢速的底层存储设备。

buffer和cache的讨论可以参考彭立勋的:

http://www.penglixun.com/tech/system/buffer_and_cache_diff.html

 

下面我们对这些缓冲自顶向下逐一进行详细的介绍。

 

2.  InnoDB层

该层的缓冲都放在主机内存中,它的目的主要是在应用层管理自己的数据,避免慢速的读写操作影响了InnoDB的响应时间。

InnoDB层主要包括两个buffer:redo log buffer和innodb buffer pool。redo log buffer用来暂存对重做日志redo log的日志写,InnoDB buffer pool存储了从磁盘设备读到的InnoDB数据,也缓冲了对InnoDB数据写,即脏页数据。如果主机掉电或者MySQL异常宕机,innodb buffer pool将无法及时刷新到磁盘,那么InnoDB就只能从上一个checkpoint使用redo log来前滚;而redo log buffer如果不能及时刷新到磁盘,那么由于redo log中数据的丢失,就算使用redo 前滚,用户提交的事务由于没有真正的记录到非易失型的磁盘介质中,就丢失掉了。

控制redo log buffer刷新时机的参数是innodb_flush_log_at_trx_commit,而控制redo log buffer和innodb buffer pool刷新方式的参数为innodb_flush_method。针对这两个参数详细介绍的文章有非常多,我们这里主要从缓冲的角度来解析。

2.1. innodb_flush_log_at_trx_commit

控制redo log buffer的innodb_flush_log_at_trx_commit目前支持3种不同的参数值0,1,2

图 2 innodb_flush_log_at_trx_commit示意图

innodb_trx_flush

这里偷个懒,直接引用应元的图。另外,更新一下innodb_flush_log_at_trx_commit=2时在5.6的变化:

< 5.6.6: 每隔一秒将redo log buffer中的数据刷新到磁盘

= 5.6.6:每隔innodb_flush_log_at_timeout秒将数据刷新到磁盘中去。

我们这里不再详细讨论这个问题,具体细节可以参考MySQL数据丢失讨论

2.2. innodb_flush_method

控制innodb buffer pool的innodb_flush_method目前支持4种不同的参数值:

  • fdatasync
  • O_DSYNC
  • O_DIRECT
  • O_DIRECT_NO_FSYNC

这里我们注意到有几个问题:

  1. innodb_flush_method指定的不仅是“数据文件”的刷新方式,也指定了“日志文件”刷新方式。
  2. 这些参数里面没有在windows环境下的参数配置,现在大家都开始不鸟盖茨兄了?其实在注释里面写了,windows就使用async_unbuffered,并且不允许修改,所以没有写到列表里面。
  3. 前三个参数值只允许在6.6和5.6.6之前的版本中用,从5.6.7开始新增了O_DIRECT_NO_FSYNC。也就是说用O_DIRECT打开文件,但是不用fsync()同步数据。这个由于在较新的Linux内核和部分文件系统中,使用O_DIRECT就可以保证数据安全,不用专门再用fsync()来同步,保证元数据也刷新到非易失型的磁盘介质。例如:XFS就不能用这个参数。O_DIRECT绕过了page cache,为什么还要用fsync()再刷新以下,我们在下节专门讨论。
  4. 有人会说referense文档有个小bug,6.6之前的版本default是fdatasync,但是Valid Values可指定的值内竟然没有fdatasync。
System Variable Name innodb_flush_method
Variable Scope Global
Dynamic Variable No
Permitted Values (<= 5.6.6)
Type (Linux) string
Default fdatasync
Valid Values O_DSYNC
O_DIRECT

表格 1 innodb_flush_method可选值

其实这里是他故意的,因为fdatasync()和fsync()是不一样的,就像O_DSYNC和O_SYNC的区别一样。Fdatasync和O_DSYNC仅用于数据同步,fsync()和O_SYNC用于数据和元数据meta-data同步。但是MySQL用fdatasync参数值来指明“数据文件”和“日志文件”是用fsync()打开的(注意:不是fdatasync()),这个是历史原因,所以5.6特意把它从可选值中去掉,避免误解。当然你如果仍然要使用fsync()来同步,那就对innodb_flush_method什么都不要指定就可以了。

  1. 除了O_DIRECT_NO_FSYNC以外,InnoDB都使用fsync()刷新“数据文件”。这里的异常就是O_DIRECT_NO_FSYNC。
  2. 如果指定O_DIRECT,O_DIRECT_NO_FSYNC,数据文件是以O_DIRECT打开(solaris上用directio()方式打开,如果Innodb的数据文件都放在单独的设备时,可以在mount 时使用forcedirectio使得整个文件系统都是以directio打开。这里指明为innodb而不是MySQL的原因是,MyISAM不要用directio())

 

对O_DIRECT_NO_FSYNC模式下日志文件是否可以用O_DIRECT方式打开的,我们特地找到mysql 5.6.14的storage/innobase/os/os0file.cc文件的os_file_create_func函数,摘录代码如下:

#ifdef UNIV_NON_BUFFERED_IO

// TODO: Create a bug, this looks wrong. The flush log

// parameter is dynamic.

if (type == OS_LOG_FILE && srv_flush_log_at_trx_commit == 2) {

 

/* Do not use unbuffered i/o for the log files because

value 2 denotes that we do not flush the log at every

commit, but only once per second */

 

} else if (srv_win_file_flush_method == SRV_WIN_IO_UNBUFFERED) {

 

attributes |= FILE_FLAG_NO_BUFFERING;

}

#endif /* UNIV_NON_BUFFERED_IO */

也就是说,对于日志文件来说,如果设置innodb_flush_log_at_trx_commit为2,O_DIRECT是无效的。

 

闲话少说,下面的一个表和一张图能够更加直观的说明问题:

重新加工了orczhou的刷新关系表:

Open log Flush log flush log Open datafile flush datafile
fdatasync fsync() fsync()
O_DSYNC O_SYNC fsync()
O_DIRECT fsync() O_DIRECT fsync()
O_DIRECT_NO_FSYNC fsync() O_DIRECT
All_O_DIRECT(percona) O_DIRECT fsync() O_DIRECT fsync

表格 2 innodb_flush_method数据文件和日志刷新对应表

 

图 3 innodb_flush_method数据文件和日志刷新示意图

innodb_flush_method

 

3.  VFS层

该层的缓冲都放在主机内存中,它的目的主要是在操作系统层缓冲数据,避免慢速块设备读写操作影响了IO的响应时间。

3.1. 细究O_DIRECT/O_SYNC标签

在前面redo log buffer和innodb buffer pool的讨论中涉及到很多数据刷新和数据安全的问题,我们在本节中,专门讨论O_DIRECT/O_SYNC标签的含义。

我们打开一个文件并写入数据,VFS和文件系统是怎么把数据写到硬件层列,下图展示了关键的数据结构:

图 4 VFS cache图

linux_caches

该图引用自The linux kernel’s VFS Layer

图中,我们看到该层中主要有page_cache/buffer cache/Inode-cache/Directory cache。其中page_cache/buffer cache主要用于缓冲内存结构数据和块设备数据。而inode-cache用于缓冲inode,directory-cache用于缓冲目录结构数据。

根据文件系统和操作系统的不同,一般来说对一个文件的写入操作包括两部分,对数据本身的写入操作,以及对文件属性(metadata元数据)的写入操作(这里的文件属性包括目录,inode等)。

了解了这些以后,我们就能够比较简单的说清楚各个标志的意义了:

page cache buffer cache inode cache dictory cache
O_DIRECT write bypass write bypass write & no flush write & no flush
O_DSYNC/fdatasync() write & flush write & flush write & no flush write & no flush
O_SYNC/fsync() write & flush write & flush write & flush write & flush

表格 3 VFS cache刷新表

 

  • O_DSYNC和fdatasync()的区别在于:是在每一个IO提交的时刻都针对对应的page cache和buffer cache进行刷新;还是在一定数据的写操作以后调用fdatasync()的时刻对整个page cache和buffer cache进行刷新。O_SYNC和fsync()的区别同理。
  • page cache和buffer cache的主要区别在于一个是面向实际文件数据,一个是面向块设备。在VFS上层使用open()方式打开那些使用mkfs做成文件系统的文件,你就会用到page cache和buffer cache,而如果你在Linux操作系统上使用dd这种方式来操作Linux的块设备,你就只会用到buffer cache。
  • O_DSYNC和O_SYNC的区别在于:O_DSYNC告诉内核,当向文件写入数据的时候,只有当数据写到了磁盘时,写入操作才算完成(write才返回成功)。O_SYNC比O_DSYNC更严格,不仅要求数据已经写到了磁盘,而且对应的数据文件的属性(例如文件inode,相关的目录变化等)也需要更新完成才算write操作成功。可见O_SYNC较之O_DSYNC要多做一些操作。
  • Open()的referense中还有一个O_ASYNC,它主要用于terminals, pseudoterminals, sockets, 和pipes/FIFOs,是信号驱动的IO,当设备可读写时发送一个信号(SIGIO),应用进程捕获这个信号来进行IO操作。
  • O_SYNC和O_DIRECT都是同步写,也就是说只有写成功了才会返回。

回过头来,我们再来看innodb_flush_log_at_trx_commit的配置就比较好理解了。O_DIRECT直接IO绕过了page cache/buffer cache以后为什么还需要fsync()了,就是为了把directory cache和inode cache元数据也刷新到存储设备上。

而由于内核和文件系统的更新,有些文件系统能够保证保证在O_DIRECT方式下不用fsync()同步元数据也不会导致数据安全性问题,所以InnoDB又提供了O_DIRECT_NO_FSYNC的方式。

 

当然,O_DIRECT对读和对写都是有效的,特别是对读,它可以保证读到的数据是从存储设备中读到的,而不是缓存中的。避免缓存中的数据和存储设备上的数据是不一致的情况(比如你通过DRBD将底层块设备的数据更新了,对于非分布式文件系统,缓存中的内容和存储设备上的数据就不一致了)。但是我们这里主要讨论缓冲(写buffer),就不深入讨论了。这个问题了。

3.2. O_DIRECT优劣势

在大部分的innodb_flush_method参数值的推荐中都会建议使用O_DIRECT,甚至在percona server分支中还提供了ALL_O_DIRECT,对日志文件也使用了O_DIRECT方式打开。

3.2.1.   优势:

  • 节省操作系统内存:O_DIRECT直接绕过page cache/buffer cache,这样避免InnoDB在读写数据少占用操作系统的内存,把更多的内存留个innodb buffer pool来使用。
  • 节省CPU。另外,内存到存储设备的传输方式主要有poll,中断和DMA方式。使用O_DIRECT方式提示操作系统尽量使用DMA方式来进行存储设备操作,节省CPU。

3.2.2.   劣势

  • 字节对齐。O_DIRECT方式要求写数据时,内存是字节对齐的(对齐的方式根据内核和文件系统的不同而不同)。这就要求数据在写的时候需要有额外的对齐操作。可以通过/sys/block/sda/queue/logical_block_size知道对齐的大小,一般都是512个字节。
  • 无法进行IO合并。O_DIRECT绕过page cache/buffer cache直接写存储设备,这样如果对同一块数据进行重复写就无法在内存中命中,page cache/buffer cache合并写的功能就无法生效了。
  • 降低顺序读写效率。如果使用O_DIRECT打开文件,则读/写操作都会跳过cache,直接在存储设备上读/写。因为没有了cache,所以文件的顺序读写使用O_DIRECT这种小IO请求的方式效率是比较低的。

 

总的来说,使用O_DIRECT来设置innodb_flush_method并不是100%对所有应用和场景都是适用的。

4.  存储控制器层

该层的缓冲都放在存储控制器的对应板载cache中,它的目的主要是在存储控制器层缓冲数据,避免慢速块设备读写操作影响了IO的响应时间。

当数据被fsync()等刷到存储层时,首先会发送到存储控制器层。常见的存储控制器就是Raid卡,而目前大部分的Raid卡都有1G或者更大的存储容量。这个缓冲一般为易失性的存储,通过板载电池/电容来保证该“易失性的存储”的数据在机器断电以后仍然会同步到底层的磁盘存储介质上。

关于存储控制器我们有一些几个方面需要注意的:

  1. write back/write through:

针对是否使用缓冲,一般的存储控制器都提供write back和write through两种方式。write back方式下,操作系统提交的写数据请求直接写入到缓冲中就返回成功;write through方式下,操作系统提交的写数据请求必须要真正写到底层磁盘介质上才返回成功。

  1. 电池/电容区别:

为了保证机器掉电以后在“易失性”缓冲中的数据能够及时刷新到底层磁盘介质上,存储控制器上都有电池/电容来保证。普通的电池有容量衰减的问题,也就是说每隔一段时间,板载的电池都要被控制充放电一次,以保证电池的容量。在电池充放过程中,被设置为write-back的存储控制器会自动变为write through。这个充放电的周期(Learn Cycle周期)一般为90天,LSI卡可以通过MegaCli来查看:

#MegaCli -AdpBbuCmd -GetBbuProperties-aAll

 

BBU Properties for Adapter: 0

 

Auto Learn Period: 90 Days

Next Learn time: Tue Oct 14 05:38:43 2014

Learn Delay Interval:0 Hours

Auto-Learn Mode: Enabled

如果你每隔一段时间发现IO请求响应时间突然慢下来了,就有可能是这个问题哦。通过MegaCli -AdpEventLog -GetEvents -f mr_AdpEventLog.txt -aALL的日志中的Event Description: Battery started charging就可以确定是否发生了发生了充放电的情况。

由于电池有这个问题,新的Raid卡会配置电容来保证“易失性”缓冲中的数据能够及时刷新到底层磁盘介质上,这样就没有充放电的问题了。

  1. read/write ratio:

HP的smart array提供对cache的读和写的区别(Accelerator Ratio),

hpacucli ctrl all show config detail|grep ‘Accelerator Ratio’

Accelerator Ratio: 25% Read / 75% Write

这样你就可以根据应用的实际情况来设置用于缓存读和缓冲写的cache的比例了。

  1. 开启Direct IO

为了能够让上层的设备使用Direct IO方式来绕过raid卡,对Raid需要设置开启DirectIO方式:

/opt/MegaRAID/MegaCli/MegaCli64 -LDSetProp -Direct -Immediate -Lall -aAll

  1. LSI flash raid:

上面我们提到了“易失性”缓冲,如果我们现在有一个非易失性的缓冲,并且容量达到几百G,这样的存储控制器缓冲是不是更能给底层设备提速?作为老牌的Raid卡厂商,LSI目前就有这样的存储控制器,使用write back方式和比较依赖存储控制器缓冲的应用可以考虑使用这种类型的存储控制器。

  1. write barriers

目前raid卡的cache是否有电池或者电容保护对Linux来说是不可见的,所以Linux为了保证日志文件系统的一致性,默认会打开write barriers,也就是说,它会不断的刷新“易失性”缓冲,这样会大大降低IO性能。所以如果你确信底层的电池能够保证“易失性”缓冲会刷到底层磁盘设备的话,你可以在磁盘mount的时候加上-o nobarrier。

5.  磁盘控制器层

该层的缓冲都放在磁盘控制器的对应板载cache中。存储设备固件(firmware)会按规则排序将写操作真正同步到介质中去。这里主要是保证写的顺序性,对机械磁盘来说,这样可以尽量让一次磁头的移动能够完成更多的磁碟写入操作。

一般来说,DMA控制器也是放在磁盘这一层的,通过DMA控制器直接进行内存访问,能够节省CPU的资源。

对于机械硬盘,因为一般的磁盘设备上并没有电池电容等,无法保证在机器掉电时磁盘cache里面的所有数据能够及时同步到介质上,所以我们强烈建议把disk cache关闭掉。

Disk cache可以在存储控制器层关闭。例如,使用MegaCli关闭的命令如下:

MegaCli -LDSetProp -DisDskCache   -Lall -aALL

 

 

6.  总结

从InnoDB到最终的介质,我们经过了各种缓冲,他们的目的其实很明确,就是为了解决:内存和磁盘的速度不匹配的问题,或者说是磁盘的速度过慢的问题。

另外,其实最懂数据是否应该缓冲/缓存的还是应用本身,VFS,存储控制器和磁盘只能通过延迟写入(以便合并重复IO,使随机写变成顺序写)来缓解底层存储设备慢速造成的响应速度慢的问题。所以数据库类型的应用都会来自己管理缓冲,然后尽量避免操作系统和底层设备的缓冲。

但是其实由于目前SSD固态硬盘和PCIe Flash卡的出现,内存和磁盘之间的速度差异被大大缩减了,这些缓冲是否必要,软硬件哪些可改进的,对软硬件工程师的一大挑战。

 

 

参考:

http://www.codeproject.com/Articles/460057/HDD-FS-O_SYNC-Throughput-vs-Integrity

http://rdc.taobao.com/blog/dba/html/296_innodb_flush_method_performance.html

http://www.orczhou.com/index.php/2009/08/innodb_flush_method-file-io/

http://blog.csdn.net/yuyin86/article/details/8113305

http://www.mtop.cc/node/100

https://www.usenix.org/legacy/event/usenix01/full_papers/kroeger/kroeger_html/node8.html

http://www.lsi.com/downloads/Public/Direct%20Assets/LSI/Benchmark_Tips.pdf

http://www.lsi.com/products/flash-accelerators/pages/default.aspx

https://access.redhat.com/documentation/en-US/Red_Hat_Enterprise_Linux/6/html/Storage_Administration_Guide/writebarrieronoff.html

http://en.wikipedia.org/wiki/Direct_memory_access

http://www.hgst.com/tech/techlib.nsf/techdocs/DFE76984029D3BE586256FAB0058B1A8/$file/DMA-white_paper_FINAL.pdf

http://en.wikipedia.org/wiki/Disk_buffer

 

 

 

 

 

 

标签:

innodb_flush_method,O_DIRECT,O_SYNC,fsync,fdatasync,open,mysql5.6,page_cache,cache buffer,disk buffer,inode buffer,write through,write back,write barriers,dma

 

7.  附录

7.1. O_Direct的方式的python code

 

错误的方式:

import os

f = os.open(‘file’, os.O_CREAT | os.O_TRUNC | os.O_DIRECT | os.O_RDWR)

s = ‘ ‘ * 1024

os.write(f, s)

 

Traceback (most recent call last):

File “”, line 1, in

OSError: [Errno 22] Invalid argument

 

正确的方式:

 

import os

import mmap

f = os.open(‘file’, os.O_CREAT | os.O_DIRECT | os.O_TRUNC | os.O_RDWR)

m = mmap.mmap(-1, 1024 * 1024)

s = ‘ ‘ * 1024 * 1024

m.write(s)

os.write(f, m)

os.close(f)

 

 

vm.swappiness=0在最新内核中可能会OOM,MySQL被意外kill

请使用RHEL/CentOS 6.4及更新版本内核的MySQL同志们注意,vm.swappiness = 0的默认行为修改了,如果继续设置vm.swappiness = 0,有可能导致系统内存溢出,从而导致MySQL被意外kill掉。

在之前的《LINUX上MYSQL优化三板斧》中,我们建议大家把 vm.swappiness = 0 设置好。来尽量避免MySQL的服务器内存被交换出去。这样Linux在把内存交换出去时更偏向于将cache页交换出去,而不是将inactive页交换出去。详细描述请参考:http://hatemysql.com/?p=463。

经常有人会问, vm.swappiness = 0会不会导致Linux在有swap空间的时候也不交换出去,从而导致内存溢出(OOM)。参照《LINUX上MYSQL优化三板斧》介绍,我们知道,这个值只是一个Linux在判断是否交换内存(swap)的一个“倾向”参考值,而并不是说,设置为0以后,Linux就完全不会使用内存交换空间。

但是,在较新的内核中(2.6.32-303.el6及以后),vm.swappiness = 0 的默认行为修改掉了,这个说法不再成立。设置该参数vm.swappiess=0,有可能导致MySQL数据库所在的系统出现内存溢出。一般来说,MySQL数据库占用的内存是整个服务器中最大的,根据Linux的策略,它会会首先把MySQL给Kill掉(调整 /proc/(pidof -s mysqld)/oom_adj可以改变OOM时kill的优先级),从而导致应用故障等。

这个修改是在内核3.5-rc1中提交的,并且合并到了2.6.32-303.el6及之后的各个版本。先让我们来看看这个patch:

http://gitorious.ti.com/ti-linux-kernel/ti-linux-kernel/commit/fe35004fbf9eaf67482b074a2e032abb9c89b1dd?format=patch

就像Satoru Moriya所说的那样,在之前的版本中,就算我们设置了swappiness=0并且RAM中还有pagecache,内核也可能会交换出部分匿名内存页。而为了“满足用户的需求”,这个patch修改了swappiness=0的行为,如果你设置swappiness=0,那么只有在(nr_free + nr_filebacked < high watermark)才会交换内存,也就是说空闲内存和文件缓存基本没有了才会触发内存swap。这样的话,副作用在于:内存如果不够了,Linux有可能触发OOM,从而kill掉耗费内存最多的MySQL进程。

在2.6.32-303.el6 RHEL/CentOS及更新版本的内核中,该patch就已经被合并进来:

其他分发版本的Linux(比如Debian,Ubuntu)的版本中,请各位自己查阅一下,看看时候已经合并该patch。

RHEL/CentOS 6.3的内核版本是2.6.32-279,而RHEL/CentOS 6.4的内核为2.6.32-358,从这个版本开始,swappiness的行为就已经修改了,使用这个版本及之后版本的同志们需要特别注意一下。

解决的办法其实也很简单,

1、尽量保证Linux操作系统还有足够的内存

2、最新的内核,建议把vm.swappiness设置1

3、考虑设置 /proc/(pidof -s mysqld)/oom_adj为较小的值来尽量避免MySQL由于内存不足而被关闭。

参考:

https://github.com/torvalds/linux/blob/master/mm/vmscan.c

http://gitorious.ti.com/ti-linux-kernel/ti-linux-kernel/commit/fe35004fbf9eaf67482b074a2e032abb9c89b1dd?format=patch

http://www.mysqlperformanceblog.com/2014/04/28/oom-relation-vm-swappiness0-new-kernel/

利用索引覆盖95%的数据

大家都知道索引可以极大的加快查询的速度,但是索引也是有代价的,它需要消耗额外的存储空间,对数据的更新操作也涉及到对应索引的一致性更新。

而对某些特殊的字符串索引来说,我们并不一定要整个字段长度都索引起来,只需要索引一定的前缀大小就可以达到快速查找指定数据的目的。

比如name字段记录的是人的名字,定义为:

之所以定义为这么长,是为了适应国外超长的名字。有兴趣的同学可以google一下“世界最长人名”。

但是正常的人名其实并不长,所以我们只需要索引前几个就行了。

所以如何确定索引的长度以保证更好的选择性,前缀性也是很多人研究的课题和论文题目。如果你把整个列都索引起来,对应的选择性当然非常高,但是浪费的空间却非常多;如果只索引列的部分前缀,那么有可能选择性比较差,达不到通过索引提高查询速度的目的。

我们这里不分析也不介绍现有的确定索引前缀长度的方法:) 仅仅介绍一种获得索引前缀长度的方法,它能保证95%的列数据都能索引起来。

按照@Dixon的理论,一般来说,你如果索引了95%的字符串前缀,它就可以满足大部分情况过滤需求。

对应的我们可以采用这样的sql来获得我们需要的95%字符前缀长度:

例如:
现在有一个filename表

该表有10条数据如下:

我们按照dixon的要求,需要给filename列找到一个最佳的前缀长度,就可以这样来查询:

子查询查询结果如下:

按length(filename)排序,其中l表示字符串列filename的长度,c表示filename列中长度为l的行数,t表示filename列中长度小于等于l的行数。@count表示filenames表的总行数。

其中,id和filename列是我特别加进去的,为了更直观的展示该子查询要表达的内容,这样的话,在外面的循环中,我们根据t来排序,找到大于95% * @count的第一个filename列的长度,也就是Dixon所说的95分为数(和中位数对应)。

参考:

http://www.jonathanlevin.co.uk/2011/11/get-95-for-your-index-prefix.html

http://tech.it168.com/a2011/0711/1216/000001216087_11.shtml

http://en.wikipedia.org/wiki/Dixon’s_Q_test

Linux上MySQL优化三板斧

现在MySQL运行的大部分环境都是在Linux上的,如何在Linux操作系统上根据MySQL进行优化,我们这里给出一些通用简单的策略。这些方法都有助于改进MySQL的性能。
闲话少说,进入正题。

一、CPU

首先从CPU说起。
你仔细检查的话,有些服务器上会有的一个有趣的现象:你cat /proc/cpuinfo时,会发现CPU的频率竟然跟它标称的频率不一样:

这个是Intel E5-2620的CPU,他是2.00G * 24的CPU,但是,我们发现第5颗CPU的频率为1.2G。
这是什么原因列?
这些其实都源于CPU最新的技术:节能模式。操作系统和CPU硬件配合,系统不繁忙的时候,为了节约电能和降低温度,它会将CPU降频。这对环保人士和抵制地球变暖来说是一个福音,但是对MySQL来说,可能是一个灾难。
为了保证MySQL能够充分利用CPU的资源,建议设置CPU为最大性能模式。这个设置可以在BIOS和操作系统中设置,当然,在BIOS中设置该选项更好,更彻底。由于各种BIOS类型的区别,设置为CPU为最大性能模式千差万别,我们这里就不具体展示怎么设置了。

二、内存

然后我们看看内存方面,我们有哪些可以优化的。

i)我们先看看numa

非一致存储访问结构 (NUMA : Non-Uniform Memory Access) 也是最新的内存管理技术。它和对称多处理器结构 (SMP : Symmetric Multi-Processor) 是对应的。简单的队别如下:

Smp numa

如图所示,详细的NUMA信息我们这里不介绍了。但是我们可以直观的看到:SMP访问内存的都是代价都是一样的;但是在NUMA架构下,本地内存的访问和非本地内存的访问代价是不一样的。对应的根据这个特性,操作系统上,我们可以设置进程的内存分配方式。目前支持的方式包括:

简而言之,就是说,你可以指定内存在本地分配,在某几个CPU节点分配或者轮询分配。除非是设置为–interleave=nodes轮询分配方式,即内存可以在任意NUMA节点上分配这种方式以外。其他的方式就算其他NUMA节点上还有内存剩余,Linux也不会把剩余的内存分配给这个进程,而是采用SWAP的方式来获得内存。有经验的系统管理员或者DBA都知道SWAP导致的数据库性能下降有多么坑爹。
所以最简单的方法,还是关闭掉这个特性。
关闭特性的方法,分别有:可以从BIOS,操作系统,启动进程时临时关闭这个特性。
a)由于各种BIOS类型的区别,如何关闭NUMA千差万别,我们这里就不具体展示怎么设置了。
b)在操作系统中关闭,可以直接在/etc/grub.conf的kernel行最后添加numa=off,如下所示:

另外可以设置 vm.zone_reclaim_mode=0尽量回收内存。
c)启动MySQL的时候,关闭NUMA特性:

当然,最好的方式是在BIOS中关闭。

ii)我们再看看vm.swappiness。

vm.swappiness是操作系统控制物理内存交换出去的策略。它允许的值是一个百分比的值,最小为0,最大运行100,该值默认为60。vm.swappiness设置为0表示尽量少swap,100表示尽量将inactive的内存页交换出去。
具体的说:当内存基本用满的时候,系统会根据这个参数来判断是把内存中很少用到的inactive 内存交换出去,还是释放数据的cache。cache中缓存着从磁盘读出来的数据,根据程序的局部性原理,这些数据有可能在接下来又要被读取;inactive 内存顾名思义,就是那些被应用程序映射着,但是“长时间”不用的内存。
我们可以利用vmstat看到inactive的内存的数量:

通过/proc/meminfo 你可以看到更详细的信息:

这里我们对不活跃inactive内存进一步深入讨论。Linux中,内存可能处于三种状态:free,active和inactive。众所周知,Linux Kernel在内部维护了很多LRU列表用来管理内存,比如LRU_INACTIVE_ANON, LRU_ACTIVE_ANON, LRU_INACTIVE_FILE , LRU_ACTIVE_FILE, LRU_UNEVICTABLE。其中LRU_INACTIVE_ANON, LRU_ACTIVE_ANON用来管理匿名页,LRU_INACTIVE_FILE , LRU_ACTIVE_FILE用来管理page caches页缓存。系统内核会根据内存页的访问情况,不定时的将活跃active内存被移到inactive列表中,这些inactive的内存可以被交换到swap中去。
一般来说,MySQL,特别是InnoDB管理内存缓存,它占用的内存比较多,不经常访问的内存也会不少,这些内存如果被Linux错误的交换出去了,将浪费很多CPU和IO资源。 InnoDB自己管理缓存,cache的文件数据来说占用了内存,对InnoDB几乎没有任何好处。
所以,我们在MySQL的服务器上最好设置vm.swappiness=0。

我们可以通过在sysctl.conf中添加一行:

并使用sysctl -p来使得该参数生效。

三、文件系统

最后,我们看一下文件系统的优化

i)我们建议在文件系统的mount参数上加上noatime,nobarrier两个选项。

用noatime mount的话,文件系统在程序访问对应的文件或者文件夹时,不会更新对应的access time。一般来说,Linux会给文件记录了三个时间,change time, modify time和access time。
我们可以通过stat来查看文件的三个时间:

其中access time指文件最后一次被读取的时间,modify time指的是文件的文本内容最后发生变化的时间,change time指的是文件的inode最后发生变化(比如位置、用户属性、组属性等)的时间。一般来说,文件都是读多写少,而且我们也很少关心某一个文件最近什么时间被访问了。
所以,我们建议采用noatime选项,这样文件系统不记录access time,避免浪费资源。
现在的很多文件系统会在数据提交时强制底层设备刷新cache,避免数据丢失,称之为write barriers。但是,其实我们数据库服务器底层存储设备要么采用RAID卡,RAID卡本身的电池可以掉电保护;要么采用Flash卡,它也有自我保护机制,保证数据不会丢失。所以我们可以安全的使用nobarrier挂载文件系统。设置方法如下:
对于ext3, ext4和 reiserfs文件系统可以在mount时指定barrier=0;对于xfs可以指定nobarrier选项。

ii)文件系统上还有一个提高IO的优化万能钥匙,那就是deadline。

在Flash技术之前,我们都是使用机械磁盘存储数据的,机械磁盘的寻道时间是影响它速度的最重要因素,直接导致它的每秒可做的IO(IOPS)非常有限,为了尽量排序和合并多个请求,以达到一次寻道能够满足多次IO请求的目的,Linux文件系统设计了多种IO调度策略,已适用各种场景和存储设备。
Linux的IO调度策略包括:Deadline scheduler,Anticipatory scheduler,Completely Fair Queuing(CFQ),NOOP。每种调度策略的详细调度方式我们这里不详细描述,这里我们主要介绍CFQ和Deadline,CFQ是Linux内核2.6.18之后的默认调度策略,它声称对每一个 IO 请求都是公平的,这种调度策略对大部分应用都是适用的。但是如果数据库有两个请求,一个请求3次IO,一个请求10000次IO,由于绝对公平,3次IO的这个请求都需要跟其他10000个IO请求竞争,可能要等待上千个IO完成才能返回,导致它的响应时间非常慢。并且如果在处理的过程中,又有很多IO请求陆续发送过来,部分IO请求甚至可能一直无法得到调度被“饿死”。而deadline兼顾到一个请求不会在队列中等待太久导致饿死,对数据库这种应用来说更加适用。
实时设置,我们可以通过
[pre]
echo deadline >/sys/block/sda/queue/scheduler[/pre]

来将sda的调度策略设置为deadline。

我们也可以直接在/etc/grub.conf的kernel行最后添加elevator=deadline来永久生效。

总结

  1. CPU方面
    • 关闭电源保护模式
  2. 内存:
    • vm.swappiness = 0
    • 关闭numa
  3. 文件系统:
    • 用noatime,nobarrier挂载系统
    • IO调度策略修改为deadline。

 

参考文档:
http://www.gentoo-wiki.info/FAQ_Linux_Memory_Management
http://bbs.gfan.com/android-4165836-1-1.html
https://wiki.archlinux.org/index.php/CPU_Frequency_Scaling_(%E7%AE%80%E4%BD%93%E4%B8%AD%E6%96%87)
http://www.mysqlperformanceblog.com/2013/12/07/linux-performance-tuning-tips-mysql/

请不要用Seconds_Behind_Master来衡量MySQL主备的延迟时间

MySQL 本身通过 show slave status 提供了 Seconds_Behind_Master ,用于衡量主备之间的复制延迟,但是 今天碰到了一个场景,发现 Seconds_Behind_Master 为 0 , 备库的 show slave status 显示 IO/SQL 线程都是正常的 , MySQL 的主库上的变更却长时间无法同步到备库上。如果没有人为干预,直到一个小时以后, MySQL 才会自动重连主库,继续复制主库的变更。

影响范围: MySQL , Percona , MariaDB 的所有版本。

虽然这种场景非常特殊,遇到的概率并不高,但是个人觉得有必要提醒一下使用 MySQL 的 DBA 们。通过对这个场景的分析,也有助于我们更加深入的理解 MySQL replication 重试机制。

一、重现步骤

搭建主备的复制,临时断开主库的网络,并 kill 掉主库 MySQL 的 binlog dump 线程。

此时观察备库的复制情况, show slave status 中:

但是此时你把网络恢复以后,在主库做任何变更,备库都无法获得数据更新了。而且备库上的 show slave status 显示: IO 线程 SQL 线程一切正常,复制延迟一直是 0 。

一切正常,普通的监控软件都不会发现备库有数据延迟。

二、原理分析

MySQL 的 Replication 是区别于其他数据库很关键的地方。也是可扩展性和高可用的基础。它本身已经非常智能化,只需要我们调用 Change Master 指定 Binlog 文件名和偏移位置就可以搭建从主库到备库的复制关系。

MySQL 复制 线程 会自动将目前复制位置记录下来,在主备复制中断的时候自动连上主库,并从上次中断的位置重新开始复制。这些操作都是全自动化的,不需要人为的干预。这给了 MySQL DBA 带来了很多便利,同时却也隐藏了很多细节。
要真正的理解前面问题的真相以及怎么解决这个问题,我们还是需要真正的理解 MySQL 复制的原理。

2.1“推”还是“拉”

首先, MySQL 的复制是“推”的,而不是“拉”的。“拉”是指 MySQL 的备库不断的循环询问主库是否有数据更新,这种方式资源消耗多,并且效率低。“推”是指 MySQL 的主库在自己有数据更新的时候推送这个变更给备库,这种方式只有在数据有变更的时候才会发生交互,资源消耗少。如果你是程序员出身,你一定会选择“推”的方式。

那么 MySQL 具体是怎么“推”的列,实际上备库在向主库申请数据变更记录的时候,需要指定从主库 Binlog 的哪个文件 ( MASTER_LOG_FILE ) 的具体多少个字节偏移位置 ( MASTER_LOG_POS ) 。对应的,主库会启动一个 Binlog dump 的线程,将变更的记录从这个位置开始一条一条的发给备库。备库一直监听主库过来的变更,接收到一条,才会在本地应用这个数据变更。

2.2 原因解析

从上面的分析,我们可以大致猜到为什么 show slave status 显示一切正常,但是实际上主库的变更都无法同步到备库上来:

出现问题的时候, Binlog dump 程序被我们 kill 掉了。作为监听的一方,备库一直没有收到任何变更,它会认为主库上长时间没有任何变更,导致没有变更数据推送过来。备库是无法判断主库上对应的 Binlog dump 线程 到底是意外终止了,还是长时间没有任何数据变更的。所以,对这两种情况来说,备库都显示为正常。

当然, MySQL 会尽量避免这种情况。比如:

  • 在 Binlog dump 被 kill 掉时通知备库 线程 被 kill 掉了。所以我们重现时需要保证这个通知发送不到备库,也就是说该问题重现的关键在于 Binlog dump 被 kill 的消息由于网络堵塞或者其他原因无法发送到备库。
  • 备库如果长时间没有收到从主库过来的变更,它会每隔一段时间重连主库。

2.3 问题避免

基于上面的分析,我们知道 MySQL 在这种情况下确实无法避免,那么我们可以有哪些办法可以避开列:

  1. 被动处理:修改延迟的监控方法,发现问题及时处理。
  2. 主动预防:正确设置 –master-retry-count , –master-connect-retry , –slave-net-timeout 复制重试参数。
被动处理

MySQL 的延迟监控大部分直接采集 show slave status 中的 Seconds_Behind_Master 。这种情况下, Seconds_Behind_Master 就无法用来真实的衡量主备之间的复制延迟了。我们建议通过在主库轮询插入时间信息,并通过复制到备库的时间差来获得主备延迟的方案。 Percona 提供了一种类似的方案 pt-heartbeat 。

发现这个问题以后,我们只需要 stop slave; start slave; 重启复制就能解决这个问题。

主动预防

MySQL 可以指定三个参数,用于复制线程重连主库:

  • master-retry-count
  • master-connect-retry
  • slave-net-timeout

其中 master-connect-retry 和 master-retry-count 需要在 Change Master 搭建主备复制时指定,而 slave-net-timeout 是一个全局变量,可以在 MySQL 运行时在线设置。

具体的重试策略为:备库过了 slave-net-timeout 秒还没有收到主库来的数据,它就会开始第一次重试。然后每过 master-connect-retry 秒,备库会再次尝试重连主库。直到重试了 master-retry-count 次,它才会放弃重试。如果重试的过程中,连上了主库,那么它认为当前主库是好的,又会开始 slave-net-timeout 秒的等待。

slave-net-timeout 的默认值是 3600 秒, master-connect-retry 默认为 60 秒, master-retry-count 默认为 86400 次。也就是说,如果主库一个小时都没有任何数据变更发送过来,备库才会尝试重连主库。这就是为什么在我们模拟的场景下,一个小时后,备库才会重连主库,继续同步数据变更的原因。
这样的话,如果你的主库上变更比较频繁,可以考虑将 slave-net-timeout 设置的小一点,避免主库 Binlog dump 线程 终止了,无法将最新的更新推送过来。

当然 slave-net-timeout 设置的过小也有问题,这样会导致如果主库的变更确实比较少的时候,备库频繁的重新连接主库,造成资源浪费。
沃趣科技的 Q Monitor 监控中对主备复制的延迟监控,并不是通过 Seconds_Behind_Master 来监控主备的。它采用了类似于 pt-heartbeat 的方式对主备进行复制延迟监控。

MySQL 5.6 GTID模式下手工删除日志导致备库数据丢失

我们在测试 5.6 GTID 的时候,发现了一个导致主备数据丢失的场景。特别提醒一下使用 MySQL 5.6 并启用了 GTID 的各位,以避免这种情况发生。同时也简单介绍了 GTID 的实现原理。

场景描述

有一台 MySQL 实例 A ,启用了 GTID 。由于 MySQL 服务器空间吃紧,我们手工将主库的所有的 binlog 以及 binlog 的 index 文件都删除掉,并启动了这个 MySQL 实例。此后,本来连在该 MySQL 实例上的备库虽然 show slave status 状态都是正常的,但是却无法获得主库上的任何更新了。备库上的 show slave status 如下:

GTID 是什么?

首先,简单描述一下 GTID 。

GTID 是 MySQL 5.6 新引入了的概念,它是由 UUID 和事务 ID 组成。这样全球所有的服务器的事务都可以用 GTID 来表示。下面就是一个 GTID :

9300dd57-51da-11e3-989d-3cd92bee36a8 表示的是一台服务器 A 的 UUID ,这个是全球唯一的。 1 表示这个服务器 A 的第一个事务。
引入 GTID 以后, MySQL 就更灵活了:

  • Slave 在搭建复制的时候,简单的设置 auto_position=1 ,不用去找 MASTER_LOG_FILE 和 MASTER_LOG_POS 这种坑爹的“二进制文件的字节偏移位置”了。
  • 在环形复制或者一主多从的架构下,节点一旦损坏,其他节点之间复制的架构重新搭建起来能够非常简单的完成。

GTID 实现原理

我们这里也简单描述一下 GTID 的实现原理。
MySQL 在主库上会把它自己执行过的所有事务的 GTID 都记录下来: gtid_executed 。 gtid_executed 是一个 GTID 的集合,不管是客户自己提交的还是 SQL 线程执行过的数据变更事务,都会被记录在这里。你可以通过 show global variables 查看:

然后,当备库需要搭建复制的时候,有两种方式:

  • auto_position=0 ,需要指定 MASTER_LOG_FILE 和 MASTER_LOG_POS 。
  • auto_position=1 。

auto_position=0 时,很简单根据指定的 binlog 文件和偏移量, Master 将 binlog 中的各个数据变更事务发送给备库就好了。备库接收到对应的事务,判断是否本机发起的 ( 通过数据变更事务记录的 server_id 来判断 ) ,如果不是本机发起的,就直接执行。

在 auto_position=1 时,备库将自己的 gtid_executed 的事务集合传给 Master , Master 找到第一个包含有非备库 gtid_executed 事务集中数据变更的 binlog ,将 binlog 中的各个 event 发送给备库。这样备库首先判断是否本机发起的,然后判断发送过来的各个数据变更事务是否在本机执行过。没有执行过的事务都需要在本地执行一遍。

问题分析

我们还是看备库上的 slave 状态:

我们注意到 Retrieved_Gtid_Set 比 Executed_Gtid_Set 还要少。 Retrieved_Gtid_Set 记录的是 IO thread 从主库拿到的事务集合, Executed_Gtid_Set 记录的是本机已经执行的事务集合。这里有一个很奇怪的想象,本机已经执行的事务集合比 IO thread 从主库拿到的事务集合还要大,而且它只是备库。
回想一下,我们之前做的操作:手工删除了 binlog 日志和 binlog index 文件,然后启动了 MySQL 。这个操作在没有启动 GTID 的 MySQL 上,我们也做过类似的事情,备库并没有复制丢失的问题。

这里之所以数据丢失的原因就很清晰了:

我们手工删除了 binlog 以后,数据库会自动生成 binlog 。但是在 Master 中,它重新从 9300dd57-51da-11e3-989d-3cd92bee36a8:1 第一个事务开始计数。从另外一个侧面来说,已经执行完的 GTID 集合, MySQL 并没有单独保存,而是通过 Binlog 来获得的。

备库已经执行完了 9300dd57-51da-11e3-989d-3cd92bee36a8 :1 - 28123 这些事务。所以当 SQL thread 拿到从主库复制过来的变更事务时,发现这些事务都是它已经执行过的事务,那么它就不会再重复执行,从而导致对应的这些事务都会被丢失掉。

根据前面的描述,我们尽量不要在数据库之外去做一些事情。比如:手工删除 binlog ,我们其实可以用更好的办法:“ purge master logs ”。就算我们确实需要在手工删除,并启动数据库,需要同时将 auto.cnf 清理掉。这样主库会生成新的 UUID ,备库发现是新的事务就会执行这个变更了。

EXT文件系统误删除数据恢复指南

 

 

我们在管理数据库和系统的时候,经常需要做rm 删除文件的操作。由于Linux是没有回收站的,rm删除了文件或者目录以后,数据是无法从Windows所谓的回收站中找到并恢复的。这样的话,数据被误删除了以后,想要恢复我们一般需要从备份中,或者找数据恢复公司来恢复数据。但是,在某些比较特殊的情况下,使用了以下方法,我们还是可以找回部分数据的。

这里我们主要介绍两种数据恢复的方法。第一种是针对文件在文件系统中已经被删除了,但是,打开这个文件的进程还存在。第二种针对文件在文件系统中已经被删除了,目前也没有任何进程打开着这个文件,但是文件在删除以后没有其他对文件系统的变更操作。

 

  1. 从/proc文件系统恢复数据

在Linux系统中,文件被删除了,只要打开文件的进程没有被关闭,那么恭喜你,这个文件重新恢复出来的可能性非常大。因为Linux操作系统在删除文件时,会判断打开这个文件的所有进程是否都已经关闭,如果还有一个进程没有关闭,那么这个文件的空间将不会释放。只有所有打开这个文件的进程都关闭以后,这个文件的空间才会释放。这也是为什么在Linux下有时候我们删除文件,文件的空间无法释放掉的原因。

这种情况下,我们可以尝试从/proc文件系统中将文件恢复出来。

/proc 文件系统是一种内核和内核模块用来向进程 (process) 发送信息的机制 (所以叫做 /proc)。通过这个伪文件系统让你可以和内核内部数据结构进行交互。你可以获取对应进程的有用信息,在运行中 (on the fly) 通过改变内核参数修改部分设置。它与其他文件系统不同,/proc 是存在于内存之中而不是硬盘上。

接下来我们模拟一下数据误删除的过程,来看看在进程没有关闭的情况下,怎么从/proc中恢复数据。

首先,我们有一个echo_red.sh的文件,我们在会话session 1查看一下这个文件的内容。

此时,在另外一个会话session 2中有一个进程在修改这个文件:

然后这个文件在会话session 1中被我们“误删除”掉了:

 

Session 1

Session 2

[root@test1 /home/woqu]

#ll

总用量 4

-rw-r–r– 1 root 93 10月 16 17:49 echo_red.sh

 

[root@test1 /home/woqu]

#cat echo_red.sh

echo_red()

{

    # echo a message with red color

    echo -e “\e[1;31m$@\e[m”

    return 0

}

 

 

 

[root@test1 /home/woqu]

#cat >echo_red.sh

echo_red()

{

    # echo a message with red color

    echo -e “\e[1;31m$@\e[m”

    return 0

}

 

[root@test1 /home/woqu]

#rm -f echo_red.sh

 

[root@test1 /home/woqu]

#ll

总用量 0

 

 

 

此时,我们发现文件被“误删除”了,需要恢复数据,那么我们需要怎么做列?

l   磁盘备份

发现误删除以后,我们需要立刻停止对该分区的写操作。

在恢复之前,如果可能的话,建议通过dd命令将磁盘整个备份起来,以避免操作的时候损坏了磁盘上相关数据。

 

l   确定进程号和文件句柄号

首先,我们需要确定打开这个文件的进程号,以及进程打开这个文件的文件号。最直接的办法就是lsof |grep -i delete:

[root@test1 /home/woqu]

#lsof |grep -i delete

cat       11791  root    1w      REG              253,0       94    1048589 /home/woqu/echo_red.sh (deleted)

这里一共有9列,各列列名如下:

COMMAND     PID  USER   FD      TYPE             DEVICE SIZE/OFF       NODE NAME

也就是说,打开这个文件的进程是11791,而/home/woqu/echo_red.sh对应该进程的文件句柄是1w。也就是说文件句柄号是1。

l   恢复误删除文件

然后,我们就可以直接将这个文件的内容拷贝出来:

 [root@test1 /root]

#cp /proc/11791/fd/1 echo_red.sh

 

[root@test1 /root]

#cat echo_red.sh

echo_red()

{

    # echo a message with red color

    echo -e “\e[1;31m$@\e[m”

    return 0

}

如上所示,数据文件恢复出来了,内容也是一模一样的。

 

  1. Extundelete工具恢复

对于使用ext3,ext4文件系统的Linux系统有一个比较好的工具可以用于数据恢复,那就是extundelete。当然其他的文件系统当然也有类似的恢复工具。

由于大部分Linux发行版都是以ext3,ext4作为默认文件系统的,我们这里以extundelete为例演示数据删除以后恢复的相关步骤。

老规矩,首先我们需要制造一个“误删除”的现场。

现在我们的/home/mysql下有多个目录,其中一个目录为script:

[root@test1 /home/mysql]

#ll

total 28

drwxr-xr-x 2 mysql 4096 Jul 21 14:42 bin

drwxr-xr-x 2 mysql 4096 Oct 12 17:52 conf

drwxr-xr-x 3 mysql 4096 Sep 26 14:57 data

drwxr-xr-x 4 mysql 4096 Oct 16 15:24 program

drwxr-xr-x 2 root  4096 Oct 16 18:16 script

drwxr-xr-x 4 mysql 4096 Oct 16 15:25 source

drwxr-xr-x 7 mysql 4096 May 31 11:27 thirdparty

这个script目录下有一些文件,如下:

[root@test1 /home/mysql]

#tree script/

script/

├── get_mysql_fdflag.sh

├── mysqlreport.sh

└── test_o_direct.c

由于某种原因,/home/mysql/script被误删除了。

[root@test1 /home/mysql]

#rm -fr script/

 

l   磁盘备份

发现误删除以后,我们需要立刻停止对该分区的写操作,避免inode被重用。

接下来就需要用extundelete工具对它进行恢复。在恢复之前如果可能的话,建议通过dd命令将磁盘整个备份起来,以避免操作的时候损坏了磁盘上相关数据。万一extundelete或者类似的工具无法恢复数据,这些数据交给专业的硬盘恢复公司也更容易找回数据一些。

 

l   umount分区

做完了备份,我们首先做的第一步,需要将误删除数据的磁盘分区首先umount掉,这也是避免该分区的数据被损坏的一个步骤。在我们的模拟环境,我们需要:

[root@test1 /root]

#umount /home/

 

l   安装extundelete

如果你机器上并没有安装extundelete的话,首先,你需要把这个工具安装好。目前最新的extundelete版本是0.2.4,安装方法如下:

yum -y install e2fsprogs*

wget  http://nchc.dl.sourceforge.net/project/extundelete/extundelete/0.2.4/extundelete-0.2.4.tar.bz2

tar xjf extundelete-0.2.4.tar.bz2

cd extundelete-0.2.4/

./configure

make

make install

 

l   查找误删除文件

通过extundelete可以查看哪些文件被删除了。在我们的模拟场景下,可以这样使用extundelete –inode 2 /dev/VolGroup/home查看/home分区下各个文件和目录的详细信息。这里/dev/VolGroup/home指的是/home对应的分区。对于ext系列的文件系统,编号为2的inode中包含了该分区下的各个文件和目录信息。输出信息如下:

[root@test1 /root]

#extundelete –inode 2 /dev/VolGroup/home

NOTICE: Extended attributes are not restored.

Loading filesystem metadata … 400 groups loaded.

Group: 0

Contents of inode 2:

0000 | ed 41 00 00 00 10 00 00 87 99 5e 52 87 99 5e 52 | .A……..^R..^R

0010 | 87 99 5e 52 00 00 00 00 00 00 05 00 08 00 00 00 | ..^R…………

0020 | 00 00 00 00 05 00 00 00 21 24 00 00 00 00 00 00 | ……..!$……

0030 | 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 | …………….

0040 | 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 | …………….

0050 | 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 | …………….

0060 | 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 | …………….

0070 | 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 | …………….

0080 | 1c 00 00 00 74 63 29 04 74 63 29 04 b8 23 27 8a | ….tc).tc)..#’.

0090 | e0 3e 2d 52 00 00 00 00 00 00 00 00 00 00 02 ea | .>-R…………

00a0 | 07 06 3c 00 00 00 00 00 21 00 00 00 00 00 00 00 | ..<…..!…….

00b0 | 73 65 6c 69 6e 75 78 00 00 00 00 00 00 00 00 00 | selinux………

00c0 | 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 | …………….

00d0 | 00 00 00 00 00 00 00 00 00 00 00 00 73 79 73 74 | …………syst

00e0 | 65 6d 5f 75 3a 6f 62 6a 65 63 74 5f 72 3a 68 6f | em_u:object_r:ho

00f0 | 6d 65 5f 72 6f 6f 74 5f 74 3a 73 30 00 00 00 00 | me_root_t:s0….

 

Inode is Allocated

File mode: 16877

Low 16 bits of Owner Uid: 0

Size in bytes: 4096

Access time: 1381931399

Creation time: 1381931399

Modification time: 1381931399

Deletion Time: 0

Low 16 bits of Group Id: 0

Links count: 5

Blocks count: 8

File flags: 0

File version (for NFS): 0

File ACL: 0

Directory ACL: 0

Fragment address: 0

Direct blocks: 9249, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0

Indirect block: 0

Double indirect block: 0

Triple indirect block: 0

 

File name                                       | Inode number | Deleted status

.                                                 2

..                                                2

lost+found                                        11

mysql                                             262145

cdrom.repo                                        12

woqu                                              2883585

我们这里最关心的还是mysql目录的信息。这里我们知道mysql的Inode为262145。于是我们可以再次用extundelete –inode 来查看mysql目录的详细信息:

[root@test1 /root]

#extundelete –inode 262145 /dev/VolGroup/home

NOTICE: Extended attributes are not restored.

Loading filesystem metadata … 400 groups loaded.

Group: 32

Contents of inode 262145:

0000 | c0 41 59 02 00 10 00 00 71 9a 5e 52 a8 99 5e 52 | .AY…..q.^R..^R

0010 | a8 99 5e 52 00 00 00 00 59 02 0c 00 08 00 00 00 | ..^R….Y…….

0020 | 00 00 08 00 54 00 00 00 0a f3 01 00 04 00 00 00 | ….T………..

0030 | 00 00 00 00 00 00 00 00 01 00 00 00 20 20 10 00 | …………  ..

0040 | 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 | …………….

0050 | 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 | …………….

0060 | 00 00 00 00 fc 9e be d7 00 00 00 00 00 00 00 00 | …………….

0070 | 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 | …………….

0080 | 1c 00 00 00 98 8a f7 bb 98 8a f7 bb 84 eb 44 c0 | …………..D.

0090 | ae be 3e 52 b4 1d 94 e3 00 00 00 00 00 00 00 00 | ..>R…………

00a0 | 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 | …………….

00b0 | 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 | …………….

00c0 | 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 | …………….

00d0 | 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 | …………….

00e0 | 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 | …………….

00f0 | 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 | …………….

 

Inode is Allocated

File mode: 16832

Low 16 bits of Owner Uid: 601

Size in bytes: 4096

Access time: 1381931633

Creation time: 1381931432

Modification time: 1381931432

Deletion Time: 0

Low 16 bits of Group Id: 601

Links count: 12

Blocks count: 8

File flags: 524288

File version (for NFS): 3619593980

File ACL: 0

Directory ACL: 0

Fragment address: 0

Direct blocks: 127754, 4, 0, 0, 1, 1056800, 0, 0, 0, 0, 0, 0

Indirect block: 0

Double indirect block: 0

Triple indirect block: 0

 

File name                                       | Inode number | Deleted status

.                                                 262145

..                                                2

.mozilla                                          262146

.bash_profile                                     262152

.gnome2                                           262150

.emacs                                            262195

.bash_logout                                      262194

.bashrc                                           262149

bin                                               262154

conf                                              262155

data                                              262156

script                                            404044     Deleted

thirdparty                                        262158

program                                           264107

.viminfo                                          262765

.bash_history                                     262193

.bzr.log                                          262153

.mysql_history                                    273588

source                                            402793

.ssh                                              414601

这里我们误删除的script目录在这里被标记为Deleted状态了。

 

l   恢复误删除数据

extundelete可以通过–restore-inode将指定inode对应的文件恢复出来,也可以使用–restore-all将所有状态为已经Deleted的文件和目录恢复回来。restore-inode主要用于恢复单个文件;restore-all用于恢复所有的文件目录。另外,还有–restore-file,–restore-files,–restore-directory来恢复指定目录或者文件。

另外,如果你知道删除的时间,那么可以指定–after或者–before来指定误删除的时间。

恢复数据的时候,extundelete将在当前目录下新建RECOVERED_FILES文件夹,并把恢复出来的数据文件或者目录存放在该目录中。

比如,我们使用–restore-inode恢复数据,恢复264111号inode文件如下:

[root@test1 /root/RECOVERED_FILES]

#extundelete –restore-inode 264111 /dev/VolGroup/home

NOTICE: Extended attributes are not restored.

Loading filesystem metadata … 400 groups loaded.

Loading journal descriptors … 31810 descriptors loaded.

 

[root@test1 /root/RECOVERED_FILES]

#ll file. 264111

-rw-r–r– 1 root 43816 10月 16 15:42 file.264111

如上,它恢复出来的文件会被重命名为file.$Inode_no(这里是file.264111)放在RECOVERED_FILES目录中。需要完全恢复数据的话,只需要将文件拷贝回原目录,并重命名。

 

使用restore-all恢复的话,目录名和文件名都会恢复回来,你可以在当前目录的RECOVERED_FILES目录下找到对应的文件和目录如下:

[root@test1 /root/RECOVERED_FILES]

#ll mysql/

total 16

drwxr-xr-x 4 root 4096 Oct 16 15:42 script

你只需要将script拷贝到原目录就好了。

 

  1. 终极解决方案

当然,以上的两个方法都是万不得已才使用的。最好的DBA和SA永远不是四处奔忙的救火队员。最好的办法是先做好预防工作,在发生之前尽量保证不出问题,而rm误删除文件的预防就是对重要数据进行备份以及rm -i。

alias rm=’rm -i –’

做了别名以后,删除数据的时候,rm命令就会提示你,文件是否确定要删除:

[root@test1 /root/RECOVERED_FILES/mysql/script]

#rm sock

rm:是否删除普通文件 “sock”?

其他避免误删除等故障的方法可以参考《远离故障的十大原则》。当然,最重要的还是日常对这种不可逆操作的谨慎和小心,并及时做好备份。

 

MVCC read view的问题

之前写了一篇文章以为对MVCC的大致原理有些了解了。今天看了《高性能MySQL》的时候,深究了一下read view的问题,发现还是蛮有意思的。

 

特别画了一张图来确认一下。

本文是上一篇MySQL事务和MVCC简介的后续,建议先了解上一篇文章以后再阅读本文。

上一篇文章简单描述了MVCC的相关情况,但是没有详细说,read view是什么结构,并且它到底是怎么工作的。

比如,我们在show engine innodb status可以看到如下内容:

  1.   —TRANSACTION 0 600, ACTIVE 4 sec, process no 3396, OS thread id 1148250464, thread declared inside InnoDB 442
  2.   mysql tables in use 1, locked 0
  3.   MySQL thread id 8079, query id 728899 localhost baron Sending data
  4.   select sql_calc_found_rows * from b limit 5
  5.   Trx read view will not see trx with id>= 0 601, sees <0 596

要理解这个,我们首先要知道:

read view其实就是一个保存事务ID的list列表。记录的是本事务执行时,MySQL还有哪些事务在执行。

Read Repeatable(下文和图中用RR表示)对应的是在每个事务启动的时候创建 一个Read View。

Read Commit(下文和图中用RC表示)对应的是每次执行SQL statement时候创建 一个Read View。

 

根据show engine innodb status的输出是说看到这个事务的id是600。

对这个事务来说,trx id为596以下的所有事务修改的行数据,这个事务都可以看到,

trx id在601以上的事务修改的数据,这个事务都不应该读取到。

596到601号事务,一共5个事务修改的数据无法确定是否能够读取。read view应该为这5个事务id集合的子集。

 

如果线程的隔离级别是RR:

按照show engine innodb status的输出,600号事务在事务启动的时候,MySQL告诉它:

596之前的所有事务都已经提交了(Trx read view will not see trx with id>= 0 601, sees <0 596),

由于事务本身是600号,那么对应的601号事务因为是在它后面启动的,600号事务肯定无法提供读取到数据(Trx read view will not see trx with id>= 0 601, sees <0 596)。

read view表示的是事务开始时MySQL还有哪些事务在执行,就应该为{596,597,598,599}集合的子集,假设为{596,598},

根据read view,Innodb在读取数据的时候需要判断该行数据的修改事务号,判断的方法为:

a) 如果行数据的修改事务号小于596,由于在事务启动的时候596之前的所有线程都已经提交了,那么该行数据可读。

b) 如果行数据的修改事务号大于601,那么该行数据肯定不可读。如果事务号为600(即自己),本事务未提交,当然也是不可读的。

为了保证在事务内任何时间读取的数据都是一致的,需要根据行数据的undo信息回溯,每次回溯都需要进行a),b),c),d)的判断,直到找到一个可读的数据。

c) 如果行数据的修改事务号在read view里面{596,599},说明是该事务(600号)开始时没有提交的数据修改,

为了保证在事务内任何时间读取的数据都是一致的,需要根据行数据的undo信息回溯,每次回溯都需要进行a),b),c),d)的判断,直到找到一个可读的数据。

d)如果不在read view里面,即事务id号在{597,598}中,说明修改行数据是该事务(600号)开始时已经提交的数据修改,那么该行数据可读。

mvcc_readview

图1 MySQL read view 示意图

如图1。这个事务的行修改数据在{[0~595],597,598}是可读区间,{596,599,600,[601~ +infinity]}是不可读区间。

 

 

如果线程的隔离级别是RC,线程开始的时候,RC事务并不会做read view,此时开始的SQL跟上面RR的情况可能是一样的。

但是过了一段时间如果601事务提交了,同样的查询,在RC下面提交,对应的show engine innodb status的信息可能稍微有点不同:

  1.   —TRANSACTION 0 600, ACTIVE 4 sec, process no 3396, OS thread id 1148250464, thread declared inside InnoDB 442
  2.   mysql tables in use 1, locked 0
  3.   MySQL thread id 8079, query id 728899 localhost baron Sending data
  4.   select sql_calc_found_rows * from b limit 5
  5.   Trx read view will not see trx with id>= 0 602, sees <0 596

按照输出,600号事务在语句“select sql_calc_found_rows * from b limit 5”发起的时候,MySQL告诉它:

596之前的所有事务都已经提交了(Trx read view will not see trx with id>= 0 601, sees <0 596),

对应的,602号线程以及它之后的所有线程都还未提交(Trx read view will not see trx with id>= 0 602, sees <0 596)。

read view表示的是语句开始时MySQL还有哪些事务在执行(注意,这里跟RR为事务开始的时候的read view不同了),

在一个事务里面,每个SQL执行的时候,它的read view都可能是不同的。有可能事务启动的时候的sql的read view为{596,598},

这个语句执行的时候,601事务提交了,read view为{596,598}。

注意,601号事务虽然在600事务后启动,此时已经提交了行数据修改,它修改的数据,600号线程也可以读到。

根据read view,InnoDB在读取数据的时候需要判断该行数据的修改事务号,判断的方法为:

a) 如果行数据的修改事务号小于596,由于在语句启动的时候596之前的所有线程都已经提交了,那么该行数据可读。

b) 如果行数据的修改事务号大于等于602,那么该行数据肯定不可读。如果事务号为600(即自己),本事务未提交,当然也是不可读的。

为了保证读到的是Commited的数据,需要根据行数据的undo信息回溯,每次回溯都需要进行a),b),c),d)的判断,直到找到一个可读的数据。

c) 如果行数据的修改事务号在read view里面{596,599},说明是该语句开始时没有提交的数据修改,

为了保证读到的是Commited的数据,需要根据行数据的undo信息回溯,每次回溯都需要进行a),b),c),d)的判断,直到找到一个可读的数据。

d)如果不在read view里面,即事务id号在{597,598}中,说明修改行数据是该语句开始时已经提交的数据修改,那么该行数据可读。

 

如图1。这个语句的修改行数据的事务id在{[0~595],597,598,601}是可读区间,{596,599,600,[602~ +infinity]}是不可读区间。

 

整体来说,这篇文章描述了在Read Readrepeatable和Read Commit环境下,MySQL根据Read View读取数据的方法,来保证可重复读和只读到已经提交的数据。

MYSQL数据丢失讨论

 

目录

 

MySQL数据丢失讨论……………………………………………………………………………………… 1

目录……………………………………………………………………………………………………………….. 3

1.    概述…………………………………………………………………………………………………………. 4

2.    问题定义………………………………………………………………………………………………….. 4

3.    InnoDB事务数据丢失………………………………………………………………………………. 4

3.1.   InnoDB事务基本原理…………………………………………………………………………….. 4

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

3.3.   InnoDB redo日志…………………………………………………………………………………… 5

3.4.   innodb_flush_log_at_trx_commit……………………………………………………………… 5

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

4.1.   MySQL复制原理简介……………………………………………………………………………… 6

4.2.   sync_binlog…………………………………………………………………………………………… 6

5.    MySQL和InnoDB协同……………………………………………………………………………… 7

5.1.   两段式事务提交……………………………………………………………………………………. 7

5.2.   innodb_support_xa…………………………………………………………………………………. 7

 

 

1.  概述

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

2.  问题定义

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

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

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

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

 

3.  InnoDB事务数据丢失

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

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的日志你还可以参考这篇文章

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秒的事务数据的风险。

引用应元的一个图如下:

innodb_flush_log_at_commit

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

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

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

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

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会自动抽取缺失的部分补全备库)。

图示如下:

mysql_replication

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。这样牺牲一定的一致性,可以获得更高的并发和性能。

5.  MySQL和InnoDB协同

5.1. 两段式事务提交

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

transaction_xa

第一阶段:

首先,协调者在自身节点的日志中写入一条的日志记录,然后所有参与者发送消息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信息,则会将事务进行提交,并写入记录

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

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

 

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。