MySQL延迟问题和数据刷盘策略流程分析

一、mysql复制流程

官方文档流程如下：

mysql延迟问题和数据刷盘策略

1、绝对的延时，相对的同步

2、纯写操作，线上标准配置下，从库压力大于主库，最起码从库有relaylog的写入。

二、mysql延迟问题分析

1、主库dml请求频繁

原因：主库并发写入数据，而从库为单线程应用日志，很容易造成relaylog堆积，产生延迟。

解决思路：做sharding，打散写请求。考虑升级到mysql5.7+，开启基于逻辑时钟的并行复制。

2、主库执行大事务

原因：类似主库花费很长时间更新了一张大表，在主从库配置相近的情况下，从库也需要花几乎同样的时间更新这张大表，此时从库延迟开始堆积，后续的events无法更新。

解决思路：拆分大事务，及时提交。

3、主库对大表执行ddl语句

原因：ddl未开始执行，被阻塞，检查到位点不变；ddl正在执行，单线程应用导致延迟增加，位点不变。

解决思路：找到被阻塞ddl或是写操作的查询，干掉该查询，让ddl正常在从库上执行；业务低峰期执行，尽量使用支持onlineddl的高版本mysql。

4、主从实例配置不一致

原因：硬件上：主库实例服务器使用ssd，而从库实例服务器使用普通sas盘、cpu主频不一致等；配置上：如raid卡写策略不一致，os内核参数设置不一致，mysql落盘策略(innodb_flush_log_at_trx_commit和sync_binlog等)不一致等

解决思路：尽量统一db机器的配置（包括硬件及选项参数）；甚至对于某些olap业务，从库实例硬件配置高于主库等。

5、从库自身压力过大

原因：从库执行大量select请求，或业务大部分select请求被路由到从库实例上，甚至大量olap业务，或者从库正在备份等，此时可能造成cpu负载过高，io利用率过高等，导致sqlthread应用过慢。

解决思路：建立更多西安数据库培训从库，打散读请求，降低现有从库实例的压力。

也可以调整innodb_flush_log_at_trx_commit=0和sync_binlog=0刷盘参数来缓解io压力来降低主从延迟。

三、大促期间cpu过高问题

现象：

高并发导致cpu负载过高，处理请求时间拉长，逐步积压，最终导致服务不可用；大量的慢sql导致cpu负载过高。

解决思路：

基本上是禁止或是慎重考虑数据库主从切换，这个解决不了根本问题，需要研发配合根治sql问题，也可以服务降级，容器的话可以动态扩容cpu；和业务协商启动pt-kill查杀只读慢sql；查看是否可以通过增加一般索引或是联合索引来解决慢sql问题，但此时要考虑ddl对数据库影响。

四、innodb刷盘策略

mysql的innodb_flush_method这个参数控制着innodb数据文件及redolog的打开、刷写模式，对于这个参数，文档上是这样描述的：

有三个值：fdatasync(默认)，o_dsync，o_direct

默认是fdatasync，调用fsync()去刷数据文件与redolog的buffer

为o_dsync时，innodb会使用o_sync方式打开和刷写redolog,使用fsync()刷写数据文件

为o_direct时，innodb使用o_direct打开数据文件，使用fsync()刷写数据文件跟redolog

首先文件的写操作包括三步：open,write,flush

上面最常提到的fsync(intfd)函数，该函数作用是flush时将与fd文件描述符所指文件有关的buffer刷写到磁盘，并且flush完元数据信息(比如修改日期、创建日期等)才算flush成功。

使用o_dsync方式打开redo文件表示当write日志时，数据都write到磁盘，并且元数据也需要更新，才返回成功。

o_direct则表示我们的write操作是从mysqlinnodbbuffer里直接向磁盘上写。

这三种模式写数据方式具体如下：

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

o_dsync模式：写日志操作是在write这步完成，而数据文件的写入是在flush这步通过fsync完成

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

mysql延迟问题和数据刷盘策略

1、在类unix操作系统中，文件的打开方式为o_direct会最小化缓冲对io的影响，该文件的io是直接在用户空间的buffer上操作的，并且io操作是同步的，因此不管是read()系统调用还是write()系统调用，数据都保证是从磁盘上读取的；所以io方面压力最小，对于cpu处理压力上也最小，对物理内存的占用也最小；但是由于没有操作系统缓冲的作用，对于数据写入磁盘的速度会降低明显（表现为写入响应时间的拉长），但不会明显造成整体sql请求量的降低（这有赖于足够大的innodb_buffer_pool_size）。

2、o_dsync方式表示以同步io的方式打开文件，任何写操作都将阻塞到数据写入物理磁盘后才返回。这就造成cpu等待加长，sql请求吞吐能力降低，insert时间拉长。

3、fsync(intfiledes)函数只对由文件描述符filedes指定的单一文件起作用，并且等待写磁盘操作结束，然后返回。fdatasync(intfiledes)函数类似于fsync，但它只影响文件的数据部分。而除数据外，fsync还会同步更新文件的元信息到磁盘。

o_dsync对cpu的压力最大，datasync次之，o_direct最小；整体sql语句处理性能和响应时间看，o_dsync较差；o_direct在sql吞吐能力上较好（仅次于datasync模式），但响应时间却是最长的。

默认datasync模式，整体表现较好，因为充分利用了操作系统buffer和innodb_buffer_pool的处理性能，但带来的负面效果是free内存降低过快，最后导致页交换频繁，磁盘io压力大，这会严重影响大并发量数据写入的稳定性。

总结

以上所述是小编给大家介绍的mysql延迟问题和数据刷盘策略流程分析，希望对大家有所帮助！