背景
客户收到了SQL专家云告警邮件,在凌晨2点到3点之间带有资源等待的会话数暴增,请我们协助分析。
现象
登录SQL专家云,进入活动会话的趋势分析页面,下钻到2点钟一个小时内的数据,看到每分钟的等待数都在100左右,2点15分时达到200。
![](https://i-blog.csdnimg.cn/blog_migrate/787688e6544c1eed22f73d9c73ecd4d1.png)
转到活动会话原始数据页面,看到大量会话都在等待,等待类型是LATCH_EX,等待资源是LOG_MANAGER,数据库都是MIIS****。SQL语句是INSERT、UPDATE、DELETE等写入的语句。
![](https://i-blog.csdnimg.cn/blog_migrate/33edeed8bfb547ad4465d49f6e480252.png)
等待资源是LOG_MANAGER,说明数据库MIIS****的日志文件在发生变化。转到数据库空间页面,发现日志文件从2点钟开始增长,2点20时增长到90GB,3点时降到初始值(因为3点有自动收缩日志文件的计划任务)。
![](https://i-blog.csdnimg.cn/blog_migrate/5a6e18a539aeaa3bfc1ac0813106ccb7.png)
分析
首先要分析的是什么语句导致数据库日志文件的暴增。进入慢语句汇总页面,汇总2点钟一个小时内的慢语句, 根据执行时间、CPU消耗、读次数、写次数等指标排序, 找到一个非常大的SQL语句,2点开始执行,2点18分结束。这是迁移历史数据的作业,把当前时间一年前数据迁移到历史表(插入到历史表,然后从当前表中删除),作业很久以前被停止了,昨天才开启,因为要迁移的数据很多,导致了日志文件的暴增。
![](https://i-blog.csdnimg.cn/blog_migrate/d1e2aa914b82f8fcb4be0653675e9287.png)
接下来分析LOG_MANAGER的等待,日志文件空间不够时就会触发自动增长,文件增长时,写入数据的会话必须等待,此时会看到Latch等待类型,增长花费的时间越长,等待的时间越长,造成的性能抖动越严重。
从2点钟开始日志文件频繁自动增长,日志文件的自动增长增量设置为10%,随着日志文件的空间越来越大,每次增加会达到几GB甚至更多,基于磁盘的性能,最少造成十几秒的性能抖动。
![](https://i-blog.csdnimg.cn/blog_migrate/b798f7ac4298e738276076afc1d4900b.png)
解决
修改数据文件和日志文件的自动增长为200MB。 每次自动增长很快就能完成,基本不会有性能抖动。
调整自动收缩日志文件的维护计划,每次收缩的时候预留10GB的空间,避免频繁的自动增长。
定期检查数据文件的空间,一次性增长一定的空间,避免频繁的自动增长。
其它
除非磁盘空间严重不足,否则不要收缩数据文件,详细请参考:数据库自动收缩造成的阻塞。
北京格瑞趋势科技有限公司是聚焦于数据服务的高新技术企业,成立于2008年,创始团队及核心技术人员来自微软和雅虎。微软数据平台金牌合作伙伴,卫宁健康数据平台战略合作伙伴。通过产品+服务双轮驱动的业务模式,14年间累计服务4000+客户,覆盖互联网、市政、交通、电信、医疗、教育、电力、制造业等各个领域。
![](https://i-blog.csdnimg.cn/blog_migrate/6b446d9fe5ce7b8e7fcd6fb9968d98fb.png)