问答文章1 问答文章501 问答文章1001 问答文章1501 问答文章2001 问答文章2501 问答文章3001 问答文章3501 问答文章4001 问答文章4501 问答文章5001 问答文章5501 问答文章6001 问答文章6501 问答文章7001 问答文章7501 问答文章8001 问答文章8501 问答文章9001 问答文章9501

如果sql语句已经达到优化,瓶颈在于数据文件的磁盘io,此时可以怎么调整

发布网友 发布时间:2022-04-08 01:01

我来回答

2个回答

热心网友 时间:2022-04-08 02:30

      具体问题具体分析,举例来说明为什么磁盘IO成瓶颈数据库的性能急速下降了。

     为什么当磁盘IO成瓶颈之后, 数据库的性能不是达到饱和的平衡状态,而是急剧下降。为什么数据库的性能有非常明显的分界点,原因是什么?

      相信大部分做数据库运维的朋友,都遇到这种情况。 数据库在前一天性能表现的相当稳定,数据库的响应时间也很正常,但就在今天,在业务人员反馈业务流量没有任何上升的情况下,数据库的变得不稳定了,有时候一个最简单的insert操作, 需要几十秒,但99%的insert却又可以在几毫秒完成,这又是为什么了?

        dba此时心中有无限的疑惑,到底是什么原因呢? 磁盘IO性能变差了?还是业务运维人员反馈的流量压根就不对? 还是数据库内部出问题?昨天不是还好好的吗?

        当数据库出现响应时间不稳定的时候,我们在操作系统上会看到磁盘的利用率会比较高,如果观察仔细一点,还可以看到,存在一些读的IO. 数据库服务器如果存在大量的写IO,性能一般都是正常跟稳定的,但只要存在少量的读IO,则性能开始出现抖动,存在大量的读IO时(排除配备非常高速磁盘的机器),对于在线交易的数据库系统来说,大概性能就雪崩了。为什么操作系统上看到的磁盘读IO跟写IO所带来的性能差距这么大呢? 

        如果亲之前没有注意到上述的现象,亲对上述的结论也是怀疑。但请看下面的分解。

        在写这个文章之前,作者阅读了大量跟的IO相关的代码,如异步IO线程的相关的,innodb_buffer池相关的,以及跟读数据块最相关的核心函数buf_page_get_gen函数以及其调用的相关子函数。为了将文章写得通俗点,看起来不那么累,因此不再一行一行的将代码解析写出来。

     咱们先来提问题。 buf_page_get_gen函数的作用是从Buffer bool里面读数据页,可能存在以下几种情况。

      提问. 数据页不在buffer bool 里面该怎么办? 

     回答:去读文件,将文件中的数据页加载到buffer pool里面。下面是函数buffer_read_page的函数,作用是将物理数据页加载到buffer pool, 图片中显示

buffer_read_page函数栈的顶层是pread64(),调用了操作系统的读函数。

buf_read_page的代码

   如果去读文件,则需要等待物理读IO的完成,如果此时IO没有及时响应,则存在堵塞。这是一个同步读的操作,如果不完成该线程无法继续后续的步骤。因为需要的数据页不再buffer 中,无法直接使用该数据页,必须等待操作系统完成IO .

      再接着上面的回答提问:

       当第二会话线程执行sql的时候,也需要去访问相同的数据页,它是等待上面的线程将这个数据页读入到缓存中,还是自己再发起一个读磁盘的然后加载到buffer的请求呢?   代码告诉我们,是前者,等待第一个请求该数据页的线程读入buffer pool。

      试想一下,如果第一个请求该数据页的线程因为磁盘IO瓶颈,迟迟没有将物理数据页读入buffer pool, 这个时间区间拖得越长,则造成等待该数据块的用户线程就越多。对高并发的系统来说,将造成大量的等待。 等待数据页读入的函数是buf_wait_for_read,下面是该函数相关的栈。


    通过解析buf_wait_for_read函数的下层函数,我们知道其实通过首先自旋加锁pin的方式,超过设定的自旋次数之后,进入等待,等待IO完成被唤醒。这样节省不停自旋pin时消耗的cpu,但需要付出被唤起时的开销。

    再继续扩展问题: 如果会话线程A 经过物理IO将数据页1001读入buffer之后,他需要修改这个页,而在会话线程A之后的其他的同样需要访问数据页1001的会话线程,即使在数据页1001被入读buffer pool之后,将仍然处于等待中。因为在数据页上读取或者更新的时候,同样需要上锁,这样才能保证数据页并发读取/更新的一致性。

     由此可见,当一个高并发的系统,出现了热点数据页需要从磁盘上加载到buffer pool中时,造成的延迟,是难以想象的。因此排在等待热点页队列最后的会话线程最后才得到需要的页,响应时间也就越长,这就是造成了一个简单的sql需要执行几十秒的原因。

再回头来看上面的问题,mysql数据库出现性能下降时,可以看到操作系统有读IO。 原因是,在数据库对数据页的更改,是在内存中的,然后通过检查点线程进行异步写盘,这个异步的写操作是不堵塞执行sql的会话线程的。所以,即使看到操作系统上有大量的写IO,数据库的性能也是很平稳的。但当用户线程需要查找的数据页不在buffer pool中时,则会从磁盘上读取,在一个热点数据页不是非常多的情况下,我们设置足够大的innodb_buffer_pool的size, 基本可以缓存所有的数据页,因此一般都不会出现缺页的情况,也就是在操作系统上基本看不到读的IO。  当出现读的IO时,原因时在执行buf_read_page_low函数,从磁盘上读取数据页到buffer pool, 则数据库的性能则开始下降,当出现大量的读IO,数据库的性能会非常差。

热心网友 时间:2022-04-08 03:48

sql 数据库占用磁盘IO读写过高,
原因:可能是插入数据频繁,并且存在的索引太多
所以建议清除不用的索引
或是对数据库进行重建索引
也可以叫DBCC 操作
声明声明:本网页内容为用户发布,旨在传播知识,不代表本网认同其观点,若有侵权等问题请及时与本网联系,我们将在第一时间删除处理。E-MAIL:11247931@qq.com
2198香辣虾是什么意思 虾什么梗 肖战 刻纸制作需要哪些步骤 好玩的生存游戏手游(好玩的生存游戏手游推荐) 北京电脑职业培训学校都有哪些北京电脑培训班都有哪些 欧盟商标注册需要提供什么资料? 怎么注册欧盟商标?需要准备哪些材料? 5g手机有什么好处 5g手机大概多少钱 口水臭是什么原因呢? 口水臭是什么原因 口水臭怎么改善 不去角质的危害 去角质对皮肤有害处吗? 经常去角质对皮肤好吗? 经常去角质有什么害处 什么情况下需要去角质?频繁去角质会对皮肤造成哪些危害? 去角质有什么危害吗?皮肤什么情况需要去角质? 经常去角质好吗? 去角质会对皮肤有损害吗? 去角质的产品对皮肤又危害吗 武通908路今天发车吗武通908跑? 去角质会伤害皮肤吗? 从天津静海区去天津的阳光男科医院怎么走? 去角质的危害 请问一下谁知道静海发天津的190,191,192能从市里带货到静海吗 静海到天津191现在通车了吗? 天津公交191路线从哪下车离大悦城近 去角质对皮肤有什么伤害和影响? 过度去角质有什么危害? 怎么从静海去天津滨海机场 天津191路公交车路过静海区禹州吗? 亲情卡支付宝是怎么扣费的 黛珂植物欣韵水乳的条形码 网购化妆品怎么辨别真伪? 兰州国芳百货有黛珂专柜嘛 猫可以吃草莓冻干嘛? 创意美术,城堡怎么画? Windows 下怎么关闭mysql的异步io? 梦到蛇在床 主死事是什么意思 清朝康熙年间的热河在今现在的哪个地方? 清代皇帝的“热河行宫”在哪里? 热河省的介绍 热河在康熙时就已经改名为承德避暑山庄,为何咸丰、慈禧在英法联军攻打北京时逃往热河?不说逃往承德。 梦见蛇上床,主死事,什么意思。?? 在收楼时物业要求业主都签一份“消防安全责任书” 限高属于失信吗 同一建筑的不同单位共用的消防安全通道及公用消防设施的情况如何签订协议?? ps怎样拉伸图片 物业公司为什么让业主签署消防安全责任书? 限高严重还是失信严重 康熙皇帝为什么修建承德避暑山庄呢?