注册 登录
  • 欢迎访问"运维那点事",推荐使用Google浏览器访问,可以扫码关注本站的"微信公众号"。
  • 如果您觉得本站对你有帮助,那么可以扫码捐助以帮助本站更好地发展。

MySQL InnoDB磁盘I/O优化

MySQL InnoDB 彭东稳 7362次浏览 已收录 3个评论

一、SQL简易执行流程

  • 查询(如SELECT)

1. 客户端发送一条查询给服务器;

2. 服务器先会检查查询缓存,如果命中了缓存,则立即返回存储在缓存中的结果。否则进入下一阶段;

3. 服务器端进行SQL解析、预处理,再由优化器生成对应的执行计划;

4. MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询;

5. 如果buffer pool中有相关页就不需要从磁盘载入到内存,反之就需要载入,并将结果逐步返回给客户端。

  • 写入(如INSERT)

1. 会话状态转换为update,然后激活事务状态由not_active变为active;

2. 查找定位数据;

3. 进行乐观插入:包括记录insert的undo记录;记录undo的redo log入redo buffer;进行insert元组插入,及实际的插入操作;记录插入的redo log入redo buffer;

4. binlog event写入到binlog cache;

5. 会话状态由update转换为query end;

6. 进入提交准备:binlog准备,及InnoDB层事务准备,状态由active变为prepare;

7. 进入提交阶段:InnoDB进行组提交,确保redo落盘;binlog cache进行flush到binlog文件;fsync binlog文件进行os缓存落盘;Innodb进行提交,事务状态由prepare变为not_active。

至此,日志写入磁盘,内存脏页还在buffer pool中(后台page_cleaner线程周期写入磁盘,释放buffer pool空间)。

二、影响SQL执行性能因素

影响SQL执行性能的关键就在于磁盘,数据库优化大部分工作都是在做磁盘的优化,比如尽可能把随机读写转换为顺序读写、预读、加大buffer pool、优化SQL尽量无磁盘操作等。

Innodb_buffer_pool_read_ahead_rnd:记录进行随机读的时候产生的预读次数。

Innodb_buffer_pool_read_ahead_seq:记录连续读的时候产生的预读次数。

Innodb_buffer_pool_read_requests:读请求次数。

Innodb_buffer_pool_reads:物理读次数。

Innodb_data_read:物理读数据字节量。

Innodb_data_reads:物理读IO请求次数。

Innodb_pages_read:物理读数据页数。

Innodb_rows_read:物理读数据行数。

这些参数都可以作为衡量磁盘繁忙程度,或者是否是buffer pool太小。还有一些write相关的变量,写基本就是脏页刷新及日志更新。

二、磁盘I/O优化

磁盘寻道是一个巨大的性能瓶颈,当数据量剧增到使得缓存命中率减小时,这个问题会更为明显。对于或多或少会进行随机访问的大型的数据库,可以确定对于读至少需要一次磁盘寻道,而对于写需要好几次磁盘寻道。要最小化这个问题,应该用磁盘寻道时间较小的磁盘。增加可用磁盘数,这样就可以通过符号链接将不同文件存储到不同磁盘,或者可以通过磁盘分条,来降低寻道开销;这就是我们常用的RAID技术。

  • 使用符号链接

这意味着,对于MyISAM表,可以将索引文件和数据文件从它们在数据目录中的通常位置符号链接到另一个磁盘(也可能是条带化的)。假设磁盘不用于其他用途,那么这会使得查找和读取时间减小。详情请参见第9.12.3节“使用符号链接”。

  • 条带化

分条意味着有多个磁盘使用的情况下,将第一个数据块放在第一个磁盘上,第二个块在第二个磁盘上,第N个块在(N对磁盘数取模)磁盘上,这意味着如果一般的数据大小小于条带大小(或者完全对齐),那么你将获得更好的性能。分条的性能非常依赖于操作系统和条带大小,因此应该设置不同的条带大小分别进行基准测试。详情请参见第9.13.2节“使用自己的基准”。

条带化的速度差异十分依赖于参数配置。根据如何设置条带化参数和硬盘数量,你可能会得到数量级的差异。对于优化随机访问还是顺序访问,你必须做出选择。

为了可靠性,你可能需要使用RAID 0+1(条带加镜像),但在这种情况下,需要2×N个磁盘来N个磁盘的数据。如果你有足够的钱,这可能是最好的选择,而且,你可能还需要额外投资一些卷管理软件来有效地管理它们。

一个好的选择是根据数据类型的关键程度来改变RAID级别。 例如,可以再生的一般重要数据可以放在RAID 0上,但将真正重要的数据(如主机信息和日志)存储在RAID 0+1或 RAID N磁盘上。 如果写入较多,则由于更新奇偶校验位需要时间,RAID N可能不太合适。

你也可以设置数据库所涉及的文件系统的参数:如果您不需要知道上次访问文件的时间(这在数据库服务器上通常用途不大),则可以使用-o noatime选项来挂载文件系统。 这会跳过对文件系统上inode的最后访问时间的更新,从而避免某些磁盘寻道。在许多操作系统上,您可以通过使用-o async选项挂载文件系统以将其设为异步更新。 如果您的计算机相当稳定,这应该给你更好的性能,而不会牺牲太多的可靠性。(在Linux上,此标志默认是打开的)

三、InnoDB磁盘IO优化

如果你遵循数据库设计和调优技术的最佳做法,但由于磁盘I/O活动较大,你的数据库仍然很慢,请考虑这些磁盘I/O优化。如果top工具显示你的CPU使用率百分比低于70%,则你的工作负载可能是磁盘导致的。几种优化方式如下:

  • 增加缓冲池大小

当表数据缓存在InnoDB缓冲池中时,可以通过查询重复访问,而不需要任何磁盘I/O。使用innodb_buffer_pool_size选项指定缓冲池的大小。该内存区域足够重要,通常建议将innodb_buffer_pool_size其配置为系统内存的50%到75%。有关更多信息,请参见第8.12.4.1节“MySQL如何使用内存”

  • 调整数据刷新方法

在某些版本的GNU/Linux和Unix中,使用Unix fsync()系统调用( InnoDB默认情况下使用)将数据刷新到磁盘是非常缓慢的。如果数据库写入性能存在问题,请将innodb_flush_method参数设置为O_DSYNC进行基准测试。

这个innodb_flush_method参数定义用于将数据刷新到InnoDB数据文件和日志文件的方法,这可能会影响磁盘I/O吞吐量。

如果在类Unix系统上将innodb_flush_method设置为NULL,则默认使用fsync选项。如果在Windows系统上将innodb_flush_method设置为NULL,则默认使用async_unbuffered选项。对于类Unix系统的innodb_flush_method选项包括:

fsync:InnoDB使用fsync()系统调用来刷新数据和日志文件,fsync也是默认设置。fsync(int filedes)函数只对由文件描述符filedes指定的单一文件起作用,并且等待写磁盘操作结束,然后返回。fdatasync(int filedes)函数类似于fsync,但它只影响文件的数据部分。而除数据外,fsync还会同步更新文件的元信息到磁盘。

O_DSYNC:InnoDB使用O_SYNC打开并刷新日志文件,使用fsync()刷新数据文件。InnoDB没有直接使用O_DSYNC,因为在很多种Unix上都存在问题。O_SYNC方式表示以同步io的方式打开文件,任何写操作都将阻塞到数据写入物理磁盘后才返回。

O_DIRECT:InnoDB使用O_DIRECT(或Solaris上的directio())打开数据文件,并使用fsync()来刷新数据和日志文件。某些GNU/Linux版本,FreeBSD和Solaris上提供了此选项。在类Unix操作系统中,文件的打开方式为O_DIRECT时会尝试最小化I/O对此文件的缓存效果,该文件的I/O是直接在用户空间的Buffer上操作的,并且I/O操作是同步的,因此不管是read()系统调用还是write()系统调用,数据都保证是从磁盘上读取的,这通常这会降低性能。但它在特殊情况下很有用,例如当应用程序有自己的缓存时。

O_DIRECT_NO_FSYNC:从MySQL 5.6.7的版本加入,InnoDB存储引擎将依然使用O_DIRECT选项打开数据表空间文件,但是在刷新时,不再需要额外的fsync操作。此设置适用于某些类型的文件系统,但不适用于其他类型。例如,它不适合XFS。如果你不确定你使用的文件系统是否需要fsync(),例如保留所有文件元数据,那么请改用O_DIRECT。(官方仅说明使用xfs文件系统不能设置为O_DIRECT_NO_FSYNC,但却只字未提哪个文件系统可以设置?请问MySQL官方这样的说明又有何意义呢?reiserfs和btrfs貌似(不十分确定)不使用inode来存放元数据信息,或许这两个文件系统下可以进行O_DIRECT_NO_FSYNC的设置?这个问题留给文件系统专家来回答了。不过目前看来,最为安全和稳妥的做法还是将参数innodb_flush_method设置为O_DIRECT。)

Warning

目前不建议在Linux系统上使用O_DIRECT_NO_FSYNC设置。当数据文件大小更改时,它可能会导致操作系统夯住。

在InnoDB存储引擎的配置中参数innodb_flush_method通常设置为O_DIRECT,这也是官方文档所推荐的设置值。DBA或开发人员知道该参数是文件打开的一个标识,启用后文件的写入将绕过操作系统缓存,直接写文件。其在InnoDB存储引擎中的表现为对于写入到数据表空间将绕过操作系统缓存。这样设置通常不会有更好的性能,但是数据库已经有自己的缓存系统,这样的设置可以确定数据库系统对于内存的使用。

细心的读者可能会产生一些困惑,因为不管参数innodb_flush_method的设置为何值,在刷新脏页时都会调用fsync操作。那么,当用户已经打开文件操作的O_DIRECT标识,为什么还需要进行一次fsync操作确保文件的写入呢?

最早发现这个问题的是Facebook的MySQL团队负责人Mark Calleghan。其在2009年时在MySQL数据库的官方论坛中提交Bug #45892,当年看到此Bug的回复时还是有些疑惑,因为其答复的诸如xfs这类文件系统,有些元数据还需要通过fsync进行刷新。

最近和文件系统内核开发人员做交流,其对ext4的文件系统做了简单的介绍,自己对文件系统有了重新认识,对之前Bug的回复也有了更为清楚的理解。在有些文件系统中,例如ext4、xfs,文件(包括目录,在Linux中所有对象都是文件)都有一个inode与之对应,其保存有两部分的内容,元数据和文件的存储数据。根据wiki的介绍,元数据包含的内容有:文件的字节数、文件的权限、文件的时间戳、链接的数量,即有多少文件指向该inode、指向数据块的链接、etc。

可以发现元数据及其重要的,不仅仅是文件最后修改时间、权限等信息,它还包含有指向存储块的信息。若在数据增长时,元数据没有及时更新,那么同样可能会导致 数据丢失的情况。虽然此时,数据可能在磁盘上,但文件不知道那些块也是其组成部门。可以看到,这也是MySQL官网对上述Bug中的回复:For example,if a file grows while O_DIRECT is enabled it will still write to the new part of the file, however since the metadata doesn‘t reflect the new size of the file the tail portion can be lost in the event of a crash.

inode中的元数据是保存在inode cache中,inode的保存文件的数据是保存在操作系统缓存中(若未开启O_DIRECT标识)。读者可以观察下图Linux文件系统的实现方式:

MySQL InnoDB磁盘I/O优化

fsync操作会同步上图中的Inode cache,Buffer cache(也就是操作系统缓存),Directory cache。 因此这就是为什么InnoDB存储引擎即使在文件打开时加上O_DIRECT标识,刷新脏页依然需要fsync操作。这是因为O_DIRECT标识只是忽略了图中Buffe cache。刷新文件的另一个函数fdatasync(),其仅刷新buffer cache的内容到磁盘,因此比fsync有更好的性能,但是存在数据丢失的风险。

若用户想通过O_DIRECT写入文件,但避免可能存在的潜在风险时,可以再加上O_SYNC标识,此时写入实际变为了一个同步写(synchronous I/O)操作,因此不再需要额外的fsync操作。

上面已经解释了InnoDB存储引擎为什么即使在开启O_DIRECT选项后依然需要调用fsync操作。下面将说明MySQL 5.6中InnoDB存储引擎的变化以及O_DIRECT对重做日志文件的影响。

继续深入思考一个问题,为什么InnoDB的重做日志不使用O_DIRECT标识进行打开,而依然使用buffered I/O呢?我的理解是为了更为有效的Group Commit。若深入源码内部来看事务提交时的操作,InnoDB存储引擎的处理方式:

可以看到fsync操作是在释放log->mutex之后。这样做的目的是在fsync时,由于已经释放log->mutex,那么其他事务可以继续将重做日志条目写入到redo log buffer中,同时这也是为什么在事务提交时,InnoDB会拷贝最后一个redo log block。若重做日志使用O_DIRECT,写入重做日志文件的过程会变慢(因为不是仅写入到操作系统缓存),Group Commit的效率就会变差。

MySQL 5.6开始InnoDB可以将重做日志文件组设置为最大512G,之前的限制为4G。这对SSD和写入密集型应用会带来明显的帮助。但是重做日志buffered I/O的问题是会导致使用过多的操作系统缓存,这也是为什么Mark Callaghan会尝试使用O_DIRECT的方式来打开重做日志。

既然不能在InnoDB内部处理该问题(至少目前),我想可以通过操作系统提供的接口来刷新操作系统中缓存的数据(vm.drop_caches = 3),从而减少内存过度的使用问题。

关于O_DIRECT的话题暂时结束了,希望用户能更好的理解其对InnoDB内部的影响。后续将继续关注O_DIRECT_NO_FSYNC对于其他文件系统的影响。摘自姜总的“InnoDB O_DIRECT选项漫谈”。

  • 在Linux上noop和deadline两种I/O调度程序的选择

InnoDB使用Linux上的异步I/O子系统(Naive AIO)对数据文件页执行预读和写请求。此行为由innodb_use_native_aio配置选项控制,配置选项默认启用。使用Naive AIO来说,I/O调度器(I/O Scheduler)的类型对I/O性能有更大的影响。通常,建议使用noop或deadline这两种I/O调度进行基准测试,以确定哪个I/O调度程序为你的工作负载和环境提供最佳结果。

  • 考虑非旋转存储

非旋转存储通常为随机I/O操作提供更好的性能,以及用于顺序I/O操作的旋转存储。在旋转和非旋转存储设备上分发数据和日志文件时,请主要考虑对每个文件执行的I/O操作的类型。面向随机的I/O的文件通常包括每表文件和一般表空间数据文件,撤销表空间文件和临时表空间文件。面向顺序的I/O的文件包括InnoDB系统表空间文件(双重缓冲和插入缓冲)以及日志文件,如二进制日志文件和重做日志文件。

使用非旋转存储时,请查看以下配置选项的设置:

innodb_checksum_algorithm

该crc32选项使用更快的校验和算法,建议用于快速存储系统。

innodb_flush_neighbors

该选项可优化旋转存储设备的I/O,禁用它用于非旋转存储或旋转和非旋转存储的混合。

innodb_io_capacity

对于低端非旋转存储设备,默认设置200通常就足够了。对于高端,总线连接的设备,请考虑更高的设置,如1000。

innodb_io_capacity_max

默认值为2000,适用于使用非旋转存储的工作负载。对于高端,总线附加的非旋转存储设备,考虑更高的设置,如2500。

innodb_log_compressed_pages

如果重做日志在非旋转存储器上,请考虑禁用此选项以减少日志记录。请参阅禁用压缩页面的日志记录。

innodb_log_file_size

如果重做日志在非旋转存储上,请配置此选项以最大化缓存和写入组合。

innodb_page_size

考虑使用与磁盘的内部扇区大小相匹配的页面大小。早期的SSD设备通常具有4k扇区大小,一些较新的设备具有16k扇区大小。默认InnoDB页面大小为16k。保持页面大小接近存储设备块大小可将未更改的数据量重写到磁盘中。

binlog_row_image

如果二进制日志在非旋转存储上,并且所有表都具有主键,请考虑将此选项设置minimal为减少日志记录。确保为你的操作系统启用了TRIM支持。通常默认情况下启用。

  • 增加I/O capacity以避免积压

如果InnoDB检查点操作导致吞吐量周期性下降,请考虑增加innodb_io_capacity配置选项的值。这个值越高,flushing操作会越频繁,以减小数据积压,避免降低吞吐量。

当log cap(指未刷新到磁盘脏页的日志大小)大于整个日志空间的75%时,系统会异步的将log cap部分的日志涉及的脏页刷到磁盘上,但是此时事务提交不会终止,也就是说还允许有redo log的继续写入。但是如果log cap继续增加,当超过整个日志空间的90%时,MySQL会停止事务的更新,此时redo log也会停止写入,必须等到刷足够的脏页时,才能允许事务再次提交。本质上说,如果事务提交的速度大于脏页刷盘的速度,最终都会触发上述日志保护的功能,即最终系统停止事务的更新,来保证日志记录的脏页能够刷新到磁盘上。也就是说更高的值会导致更频繁的flushing,避免积压的工作可能导致吞吐量下降。

  • 在Fusion-io设备上存储系统表空间文件

你可以通过在支持原子写入的Fusion-io设备上存储系统表空间文件(“ ibdata文件 ”),以获得doublewrite缓冲区相关的I/O优化。在这种情况下,doublewrite缓冲(innodb_doublewrite)被自动禁用,并且Fusion-io原子写入用于所有数据文件。此功能仅在Fusion-io硬件上受支持,并且仅在Linux上启用Fusion-io NVMFS。要充分利用此功能,建议使用innodb_flush_method设置O_DIRECT。

注意:由于doublewrite buffer的设置时全局的,所以对于那些留在非Fusion-io硬件中的数据文件,doublewrite buffer依旧是无法使用的。

  • 禁止记录压缩页面

当使用InnoDB表压缩功能时,对压缩数据进行更改时,会将重新压缩页面的图像写入重做日志。此行为由innodb_log_compressed_pages控制,默认情况下启用此功能,以防止zlib在恢复期间使用不同版本的压缩算法时可能会发生损坏。如果你确定zlib版本不会更改,请禁用innodb_log_compressed_pages以减少修改压缩数据的重做日志生成的工作负载。

<参考>

http://blog.jobbole.com/90267

https://www.jianshu.com/p/5248ca67eac2

https://dev.mysql.com/doc/refman/5.7/en/disk-issues.html

https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-diskio.html


如果您觉得本站对你有帮助,那么可以支付宝扫码捐助以帮助本站更好地发展,在此谢过。
喜欢 (3)or分享 (0)
关于作者:

您必须 登录 才能发表评论!

(3)个小伙伴在吐槽
  1. 关于innodb_flush_method和innodb_flush_log_at_trx_commit两个参数,都可以将日志文件刷盘。如果innodb_flush_method设置为o_direct,那么无论innodb_flush_log_at_trx_commit设置为多少都是直接刷到磁盘,不经过os 缓存呢?不太理解这两个参数的关系。
    zhazha2018-09-27 09:08 Windows 10 | Chrome 67.0.3396.62
    • redo log打开文件使用的是buffer i/o,不使用o_direct是为了更有效的使用group commit。更详细可以看看姜总之前写的InnoDB O_DIRECT选项漫谈。
      彭东稳2018-09-27 13:36 未知操作系统 | Chrome 68.0.3440.106
      • 好的,十分感谢,我再研究一下~
        zhazha2018-09-27 14:22 Windows 10 | Chrome 67.0.3396.62