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延迟问题和数据刷盘策略流程分析,希望对大家有所帮助!

(0)

相关推荐

  • mysql同步问题之Slave延迟很大优化方法

    一般而言,slave相对master延迟较大,其根本原因就是slave上的复制线程没办法真正做到并发.简单说,在master上是并发模式(以InnoDB引擎为主)完成事务提交的,而在slave上,复制线程只有一个sql thread用于binlog的apply,所以难怪slave在高并发时会远落后master. ORACLE MySQL 5.6版本开始支持多线程复制,配置选项 slave_parallel_workers 即可实现在slave上多线程并发复制.不过,它只能支持一个实例下多个 da

  • MySQL延迟关联性能优化方法

    [背景] 某业务数据库load 报警异常,cpu usr 达到30-40 ,居高不下.使用工具查看数据库正在执行的sql ,排在前面的大部分是: 复制代码 代码如下: SELECT id, cu_id, name, info, biz_type, gmt_create, gmt_modified,start_time, end_time, market_type, back_leaf_category,item_status,picuture_url FROM relation where bi

  • 解决MySQL中的Slave延迟问题的基本教程

    一.原因分析 一般而言,slave相对master延迟较大,其根本原因就是slave上的复制线程没办法真正做到并发.简单说,在master上是并发模式(以InnoDB引擎为主)完成事务提交的,而在slave上,复制线程只有一个sql thread用于binlog的apply,所以难怪slave在高并发时会远落后master. ORACLE MySQL 5.6版本开始支持多线程复制,配置选项 slave_parallel_workers 即可实现在slave上多线程并发复制.不过,它只能支持一个实

  • MySQL主从同步延迟的原因及解决办法

    由于历史原因,MySQL复制基于逻辑的二进制日志,而非重做日志.多次被问到何时MySQL能支持基于物理的复制,其实这就看MySQL各位大佬的想法.上次和赖老师脑暴,倏地说道:MySQL会不会来个基于Paxos的redo复制? 物理复制的真正好处不在于正确性,因为基于ROW格式的日志复制也已能完全保证复制的正确性.由于物理日志的写入是在事务执行过程中就不断写入,而二进制日志的写入仅仅在事务提交时.因此物理日志的优势如下所示: 复制架构下,大事务日志提交速度快: 复制架构下,主从数据延迟小: 假设执

  • MYSQL主从不同步延迟原理分析及解决方案

    1. MySQL数据库主从同步延迟原理.要说延时原理,得从mysql的数据库主从复制原理说起,mysql的主从复制都是单线程的操作,主库对所有DDL和DML产生binlog,binlog是顺序写,所以效率很高,slave的Slave_IO_Running线程到主库取日志,效率很比较高,下一步,问题来了,slave的Slave_SQL_Running线程将主库的DDL和DML操作在slave实施.DML和DDL的IO操作是随即的,不是顺序的,成本高很多,还可能可slave上的其他查询产生lock争

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

    一.MySQL复制流程 官方文档流程如下: MySQL延迟问题和数据刷盘策略 1.绝对的延时,相对的同步 2.纯写操作,线上标准配置下,从库压力大于主库,最起码从库有relaylog的写入. 二.MySQL延迟问题分析 1.主库DML请求频繁 原因:主库并发写入数据,而从库为单线程应用日志,很容易造成relaylog堆积,产生延迟. 解决思路:做sharding,打散写请求.考虑升级到MySQL5.7+,开启基于逻辑时钟的并行复制. 2.主库执行大事务 原因:类似主库花费很长时间更新了一张大表,

  • mongodb 数据块的迁移流程分析

    目录 1. 基本概念 1.1 Chunk(数据块) 1.2 Chunk Size(数据块大小) 1.3 Migration(数据块迁移) 1.4 Migration Thresholds(迁移阈值) 2. 迁移流程 3. 最佳实践 3.1 关于数据块大小的选择 3.2 关于数据块迁移对集群性能的影响 1. 基本概念 1.1 Chunk(数据块) 表示特定服务器上面,连续范围的分片键值所包含的一组数据,是一个逻辑概念. 例如,某数据块记录如下: { "_id" : "chunk

  • MySQL是如何保证数据的完整性

    数据的一致性和完整性对于在线业务的重要性不言而喻,如何保证数据不丢呢?今天我们就探讨下关于数据的完整性和强一致性,MySQL做了哪些改进. 一. MySQL的二阶段提交 在Oracle和MySQL这种关系型数据库中,讲究日志先行策略(Write-Ahead Logging),只要日志持久化到磁盘,就能保证MySQL异常重启后,数据不丢失.在MySQL中,提到日志不得不提的就是redo log和binlog. 1. redo log redo log又称重做日志文件,详细的记录了对每一个数据页里面

  • RocketMQ设计之异步刷盘

    上一篇RocketMQ设计之同步刷盘 异步刷盘方式:在返回写成功状态时,消息可能只是被写入了内存的PAGECACHE,写操作的返回快,吞吐量大:当内存里的消息量积累到一定程度时,统一触发写磁盘操作,快速写入 RocketMQ默认采用异步刷盘,异步刷盘两种策略:开启缓冲池,不开启缓冲池 CommitLog的handleDiskFlush方法: public void handleDiskFlush(AppendMessageResult result, PutMessageResult putMe

  • MySQL数据表分区策略及优缺点分析

    为什么需要分区? 当面对巨大的数据表的时候,至少有一件事情是确定的,表太大了以至于每次查询的时候我们没法做全表扫描.而这个时候也没法使用索引,或者说索引意义不大,更不用说索引的维护代价和空间占用非常高.如果是依赖索引,会导致大量的碎片和低聚集度的数据,这会导致查询的时候有上千次的随机 I/O 访问而导致宕机.这种情况下一般只会使用1-2个索引,而不会更多.这种情况下,有两个可行的选项:查询必须从数据表的指定的部分顺序查找或者是期望的部分数据及其索引与服务器的内存匹配. 需要再次重申:在存储空间过

  • Mysql数据库中的redo log 写入策略和binlog 写入策略

    目录 redo log的写入策略 binlog的写入策略 生产配置 性能瓶颈 redo log的写入策略 InnoDB提供了innodb_flush_log_at_trx_commit参数,它有三种可能取值: 设置为0的时候,表示每次事务提交时都只是把redo log留在redo log buffer中; 设置为1的时候,表示每次事务提交时都将redo log直接持久化到磁盘: 设置为2的时候,表示每次事务提交时都只是把redo log写到page cache. 查看mysql变量:show V

  • MySQL 重写查询语句的三种策略

    在优化存在问题的查询时,我们需要改变方式去获取查询结果--但这并不意味着从 MySQL获取同样的结果集.有些时候我们可以将查询转换为获取相同结果,但更好性能的查询形式.然而,我们也需要考虑重写查询去获取不同的结果,因为这样可以提高开发效率.也可以通过修改应用程序代码来取得相同的效果.本篇文章将介绍如何重写查询的技巧. 复杂查询与分步查询 一个重要的查询设计课题是将复杂查询分解为多个简单查询是否会更好.在传统的数据库设计中强调尽可能地用更少的查询解决大量工作.在过往,这种方式会更好.这是因为以前的

  • 部署MySQL延迟从库的好处小结

    前言 MySQL的主从复制(replication)关系,不太严谨的叫法是"同步"或者"主从同步".实际上在早期,MySQL的主从并不能实现真正的"同步"(sync),而是"异步"的(async). MySQL主从复制它可以有多种模式,最经典的也是最早出现的异步复制(async replication),从5.5版本开始有了半同步复制(semi-sync replication),到了5.7又有了增强半同步.本文要讨论的延迟从

  • MySQL如何快速导入数据

    前言: 日常学习和工作中,经常会遇到导数据的需求.比如数据迁移.数据恢复.新建从库等,这些操作可能都会涉及大量数据的导入.有时候导入进度慢,电脑风扇狂转真的很让人崩溃,其实有些小技巧是可以让导入更快速的,本篇文章笔者会谈一谈如何快速的导入数据. 注:本篇文章只讨论如何快速导入由逻辑备份产生的SQL脚本,其他文件形式暂不讨论. 1.尽量减小导入文件大小 首先给个建议,导出导入数据尽量使用MySQL自带的命令行工具,不要使用Navicat.workbench等图形化工具.特别是大数据量的时候,用My

  • MySQL删除和插入数据很慢的问题解决

    公司开发人员在测试环境中执行一条 insert 语句时,需要花费 10 几秒才可以执行成功.查看测试环境数据库性能.数据量.死锁等信息,均为发现异常.最后通过修改日志写入方式解决此问题. 1. 修改办法 修改/etc/my.cnf文件,将 innodb_flush_log_at_trx_commit = 1改为0, 但这样就要承担数据库Crash后,1秒内未存储到数据库数据丢失可能的风险.MySQL文档中对该参数的描述如下: If the value of innodb_flush_log_at

随机推荐