线上IO问题跟踪-剖析MySQL IO路径

提示:公众号展示代码会自动折行,建议横屏阅读

一、问题描述

某业务CDB实例,每天在特地时间段内( 00:07:00 - 00:08:00左右)机器对应IO监控出现写入尖刺,且主从实例都有类似现象,从机器监控可以看到,问题确实存在。

不仅master,进行同步的slave上有相同的现象,业务方希望找到导致该IO尖刺问题稳定出现的原因。

二、问题分析

首先确定问题来源,上图所示监控为机器级别,机器IO写入负载是否来源于mysqld进程?如果来源于mysqld进程,是来自于mysqld进程的哪一部分写入操作引起?

为了获取IO来源,在slave机上部署mysqld实例监控,以及iotop采集监控,获取对应时间段更详细的相关信息,抓取对应时间段进行IO写入的进程(线程),同时观察对应时间段mysql实例状态。

这段时间内的较大IO写入线程号为:(截取部分记录)

时间

线程号

进程名

读取速度

写入速度

00:07:34

145378

be/4 mysql201

139.10 K/s

263111.57 K/s

00:07:35

145378

be/4 mysql201

124.11 K/s

249703.84 K/s

00:07:36

145378

be/4 mysql201

120.23 K/s

289920.70 K/s

00:07:39

145378

be/4 mysql201

5168.09 K/s

875194.69 K/s

通过slave上iotop采集到的统计信息,可以看出较大写入来源为145378这一线程,确实来自于mysql进程,该时间段内没有抓到其他大量写入的记录,同时该实例slave mysql为单机独占,可以基本确定写入来源为mysql中145378这个线程,那么这个线程是哪一个线程呢?

145378这个线程号正是mysqld slave的线程,而且为单线程回放的sql线程。

Thread 85 (Thread 0x7f68c4c4c700 (LWP 145378)):
#0  0x00007fa2badd3945 in pthread_cond_wait@@GLIBC_2.3.2 () from /lib64/libpthread.so.0
#4  exec_relay_log_event (rli=0x1771c43c8, thd=0x7f68b0000990)  
#5  handle_slave_sql (arg=0x1771c3010)

通过进一步搜索监控记录,可以发现,其实sql线程引入大量写入IO不仅存在于这个时间段,在其他时间段也有较高写入的记录(超过100MB/s),在00:08:00左右持续时间相对较长。这个现象反应出该问题可能贯穿于整个执行过程,只是其他时间段没有这么明显。

通过mysql实例的监控可,可以看到mysql主要进行的操作为insert,slave mysql主要在进行单线程回放,执行这些insert操作,操作数量会有一些增长,每秒操作数不稳定,峰值可能达到4000左右。

从对于slave实例的新增监控可以得到结论,这段时间的主要写入来源确实为mysqld进程,且为mysqld的sql线程,那么问题转换为:

在该业务模型下,进行单线程回放的mysql slave sql thread, 为什么会在一些时段产生较大的写入IO?产生这个现象的时候,mysql在做什么事情

为了更好的分析问题,同时不影响线上业务,在另外一台实验机器上单独搭建mysql 5.7的slave,连到源实例的master作为slave,问题现象也可以复现,为了获取更多信息,打开mysql 5.7的performance_schema,在实验机器的slave机监控上,依然能看到该问题存在。

现在我们需要分析一下,SQL线程回放,可能产生哪些IO写(注意其他线程的IO不会记录到SQL线程头上,例如page cleaner flush),一条SQL语句回放过程中,可能经历的路径上有哪些操作会引发IO操作。

一个slave mysql读取relay log进行日志回放,首先想到回放执行语句,可能由此引发下列写入IO:

  • mysql server binlog日志记录,即回放过程中语句写入的本地binlog。
  • innodb redo undo日志记录。
  • 用户态page flush(free page吃紧,需要自行flush获取可用free page)。
  • relay log purge,删除文件。默认每当slave回放完一个完整relay log,会将对应文件进行删除。

那么,为了找到真正引发写入的来源,需要对于上述来源逐步进行分析、排除,对可能原因进行验证。

首先在5.7 slave上配置log_slave_updates=OFF,使得回放过程不记录binlog日志,问题现象依旧,排除binlog写入。

分钟级监控显示,master实例在对应时间段insert数量有一定幅度增加:

为了保证free page够用,调大了buffer pool,确保free page足够使用,另外关闭relay log purge功能,使得SQL线程不会触发删除relay log文件,问题现象依旧,排除清理relay以及用户态page flush

通过sys schema统计值发现,对于文件写入,count write统计值较大的记录主要来源于redo log(例如ib_logfile0写入7439952次,且总量为57.47GB),但是innodb自身对于log写入的单位时间统计值显示却不大。

/data1/mysql_root/log/20120/ib_logfile0 5       4.00 KiB        819 bytes       7439952   57.47 GiB       8.10 KiB        57.47 GiB       100.00
/data1/mysql_root/log/20120/ib_logfile1 2       64.50 KiB       32.25 KiB       3025254   23.39 GiB       8.11 KiB        23.39 GiB       100.00

为了进一步排除干扰,修改mysql实现关闭redo log写入,替换mysql 5.7实验版本,统计值显示确实redo文件几乎没有写入增长,问题依旧,排除redo写入造成再看ibdata相关记录增长,也非常有限,也可以排除undo文件写入

到目前为止,通常能想到的用户态写入(例如sql线程回放执行一个事务),都可以排除掉,还有什么原因可以sql线程造成大量写入呢?需要重新整理一下思路。

再回到业务本身,看一下业务的库表结构模型和数据规模,表结构大体如下:

FRFrom int(10) unsigned NOT NULL DEFAULT ‘0’, FRtBody mediumblob NOT NULL, FPSQL varchar(20480) NOT NULL, FBody mediumblob NOT NULL, FResTime bigint(20) NOT NULL DEFAULT ‘0’, FCime bigint(20) NOT NULL DEFAULT ‘0’, FInt1 int(10) unsigned NOT NULL DEFAULT ‘0’, FInt2 int(10) unsigned NOT NULL DEFAULT ‘0’, FInt3 int(10) unsigned NOT NULL DEFAULT ‘0’, FInt4 int(10) unsigned NOT NULL DEFAULT ‘0’, FInt5 int(10) unsigned NOT NULL DEFAULT ‘0’, FChar1 varchar(256) NOT NULL DEFAULT ‘’, FChar2 varchar(256) NOT NULL DEFAULT ‘’, FChar3 varchar(512) NOT NULL DEFAULT ‘’, FChar4 varchar(1024) NOT NULL DEFAULT ‘’, FChar5 varchar(1024) NOT NULL DEFAULT ‘’, FExt blob, FGrp2EvtNo bigint(20) DEFAULT NULL,  PRIMARY KEY (FEventNo)

  • 表数量很多,超过1W张表,且单表数据量不大
  • 表结构没有其他索引,只有主键
  • 包含blob字段
  • 从relay log分析插入语句,blob每次都有较大的数据量进行插入

表数量较多,插入操作,blob字段,并发插入且插入操作分散到各个表,这几点看起来有些关联。在关掉了包括binlog,relay purge,redo log等多个写入之后,再抓一下sql线程回放的堆栈,看一下写入调用的来源:

#0  os_aio_func (type=..., mode=mode@entry=24, 
    name=0x7ee4493f97b8 "./DB_xxx/xxx_36.ibd", file=..., buf=buf@entry=0x7ee453ed4000, offset=offset@entry=122683392, n=n@entry=1048576, read_only=read_only@entry=false, m1=m1@entry=0x0, m2=m2@entry=0x0)
#1  0x0000000001187922 in fil_write_zeros (node=0x7ee4493f5338, node=0x7ee4493f5338, read_only_mode=<optimized out>, 
    len=4194304, start=121634816, page_size=<optimized out>)
#2  fil_space_extend (space=space@entry=0x7ee448d8dbd8, size=<optimized out>)
#3  0x00000000007701d1 in fsp_try_extend_data_file (space=space@entry=0x7ee448d8dbd8, header=header@entry=0x7ef4a4ff0026 "", 
#4  0x000000000118f0df in fsp_reserve_free_extents (n_reserved=n_reserved@entry=0x7f20868f4fa0, space_id=9054, n_ext=3, 
    alloc_type=alloc_type@entry=FSP_NORMAL, mtr=mtr@entry=0x7f20868f5890, n_pages=n_pages@entry=2)
#5  0x00000000010e6394 in btr_cur_pessimistic_insert (flags=flags@entry=0, cursor=cursor@entry=0x7f20868f5150, 
    offsets=offsets@entry=0x7f20868f50b0, heap=heap@entry=0x7f20868f50a0, entry=entry@entry=0x7ee453143488, 
    rec=rec@entry=0x7f20868f5570, big_rec=big_rec@entry=0x7f20868f5090, n_ext=n_ext@entry=0, thr=thr@entry=0x7ee4544623f0, 
    mtr=mtr@entry=0x7f20868f5890)

回顾一下基于主键索引的插入操作,对于b+树,如果插入的record的较大(例如很大的blob),可能会触发分裂操作。类似

对于innodb而言,插入的过程中,首先尝试乐观插入索引,如果空间大小不够,再尝试悲观插入,悲观插入首先保证表空间大小足够(ibd文件 innodb_file_per_table=ON,每个表对应一个文件),这里用户场景,正是每个表一个ibd文件,那么16000张表总共有16000个ibd文件。如果空间不够,尝试对于ibd文件进行扩展,扩展逻辑如下:

每次扩展4个extent(每个extent包含 16kb*64 = 1MB大小供64个data page),即每次扩展形成4MB,业务场景包含16000多个表,且每次插入数据量相对固定,表结构相同,插入目标表随机分散,所以很多表大小,和后续操作,非常均衡可以看作是齐头并进。

从slave实例监控来看,insert在对应时间段超过1000个每秒,最多可以达到4000个每秒。假设相同数据量模型,其中500个表同时扩展大小,这一秒内,可能同时产生500*4MB = 2GB左右的写入。为了印证这个写入来源,继续在innodb层添加日志跟踪,对于idb文件扩展加入以下逻辑进行日志跟踪:

每100MB扩展数据量,查看累计时间,如果累计时间在1秒以内,打印一条warning日志,且sleep 0.5s。

  查看对应时间段新增日志(+8为北京时间),

对应时间扩展很频繁,除去sleep时间,大约0.1-0.2秒能够产生100MB的扩展写入,IO尖刺数据量基本吻合,与iotop抓取到的大io写入也基本吻合。至此我们基本可以得出问题结论。

三、问题结论

业务模型比较特殊,多个包含BLOB字段的小表(超过16000),业务模型为insert,写入目标表分散,在某些时间段会并发insert(平均2000个左右每秒,峰值4000个每秒),由于BLOB字段占用空间较大,各个表使用分散,导致频繁同时触发分裂,进而导致底层data page扩展,使得底层多个ibd文件同时并发extend,初始化data page产生某些时间段较大IO,反应到机器监控上为某些时间段IO尖刺

解决方法:

  1. 业务层面,对于类似场景,考虑合并表数量,减少并发扩展带来的写入压力,可以一定程度缓解IO尖刺。
  2. MySQL层面,考虑更加智能的数据文件扩展算法,适配上述场景。减少扩展的并发性。
  3. MySQL层面,考虑用户指定初始化表空间大小,提前预分配和初始化,避免动态扩展。

腾讯数据库技术团队对内支持微信红包,彩票、数据银行等集团内部业务,对外为腾讯云提供各种数据库产品,如CDB、CTSDB、CKV、CMongo, 腾讯数据库技术团队专注于增强数据库内核功能,提升数据库性能,保证系统稳定性并解决用户在生产过程中遇到的问题,并对生产环境中遇到的问题及知识进行分享。

原文发布于微信公众号 - 腾讯数据库技术(gh_83eebc796d5d)

原文发表时间:2018-12-17

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏北京马哥教育

为 Zabbix 优化 MySQL

Zabbix 和 MySQL 在大型的 Zabbix 环境中,遇到的挑战大部分是 MySQL 以及更具体的说是 MySQL 磁盘 IO。 考虑到这一点,我将提...

34830
来自专栏DeveWork

免插件仅代码实现WordPress评论回复邮件

许多wordpress博主为增加与读者的互动,从而获得更加多的“回头客”,常常在评论上启用一个“评论回复邮件”的功能。这个功能可以使用插件来实现,但我们一贯遵循...

38580
来自专栏王亮的专栏

存储总量达 20T 的 MySQL 实例,如何完成迁移?

为保证业务迁移顺利进行,对迁移流程,工具进行了前期的调查研究,并对过程中发现的 4 大问题进行及时解决,本文为实际迁移经验分享。

32.6K110
来自专栏企鹅号快讯

Domino 9.0.1中全文索引任务的潜在风险

Domino中的全文索引任务是用来更新数据库中的全文索引,这样用户可以更好地利用数据库中的索引来精确地快速查找文档。全文索引必须定期更新,不然新的或更改的文档,...

19670
来自专栏数据和云

实战课堂:系统CPU高消耗的SQL筛选和最佳索引优化

在一次客户系统性能优化项目中,经过第一阶段的优化之后,数据库的DB Time和物理读都明显降低,但是我们发现CPU并没有明显降低。

13240
来自专栏性能与架构

Kafka 流数据 SQL 引擎 -- KSQL

KSQL 是什么? KSQL 是一个 Kafka 的 SQL 引擎,可以让我们在流数据上持续执行 SQL 查询 例如,有一个用户点击流的topic,和一个可持续...

46660
来自专栏数据和云

SQL之美 - Oracle 子查询优化系列精讲

题记:SQL优化及SQL审核,是从源头解决性能问题的根本手段,无论是开发人员还是DBA,都应当持续深入的学习SQL开发技能,从而为解决性能问题打下根基。 本系列...

37730
来自专栏友弟技术工作室

mysql优化

上篇文章是关于mysql优化的,那个内容是我大学的时候学习的笔记,最近学习发现一些比较好的内容,在这里分享给大家。 版权源于网上。 工作中使用最多的就是MySQ...

54170
来自专栏Java进阶架构师

「mysql优化专题」这大概是一篇最好的mysql优化入门文章(1)

优化,一直是面试最常问的一个问题。因为从优化的角度,优化的思路,完全可以看出一个人的技术积累。那么,关于系统优化,假设这么个场景,用户反映系统太卡(其实就是高并...

10840
来自专栏IT派

如何用Python 编写知乎爬虫?So easy!

在爬虫系统中,待抓取 URL 队列是很重要的一部分。待抓取 URL 队列中的 URL 以什么样的顺序排列也是一个很重要的问题,因为这涉及到先抓取那个页面,后抓取...

16400

扫码关注云+社区

领取腾讯云代金券