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

MySQL InnoDB磁盘I/O优化

MySQL InnoDB 彭东稳 5697次浏览 已收录 0个评论

一、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数据文件及redo log的打开、刷写模式,对于这个参数,文档上是这样描述的:有三个值:fdatasync(默认),O_DSYNC,O_DIRECT。

fdatasync模式:写数据时,write这一步并不需要真正写到磁盘才算完成(可能写入到操作系统buffer中就会返回完成),真正完成是flush操作,buffer交给操作系统去flush,并且文件的元数据信息也都需要更新到磁盘。

O_DSYNC模式:写日志操作是在write这步完成,而数据文件的写入是在flush这步通过fsync完成。

O_DIRECT模式:数据文件的写入操作是直接从mysql innodb buffer到磁盘的,并不用通过操作系统的缓冲,而真正的完成也是在flush这步,日志还是要经过OS缓冲。

MySQL InnoDB磁盘I/O优化

注:在类unix操作系统中,文件的打开方式为O_DIRECT会最小化缓冲对io的影响,该文件的io是直接在用户空间的Buffer上操作的,并且io操作是同步的,因此不管是read()系统调用还是write()系统调用,数据都保证是从磁盘上读取的;O_SYNC方式表示以同步io的方式打开文件,任何写操作都将阻塞到数据写入物理磁盘后才返回。fsync(int filedes)函数只对由文件描述符filedes指定的单一文件起作用,并且等待写磁盘操作结束,然后返回。fdatasync(int filedes)函数类似于fsync,但它只影响文件的数据部分。而除数据外,fsync还会同步更新文件的元信息到磁盘。

  • 在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


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

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