ITPUB论坛-中国最专业的IT技术社区

 手机号登录  找回密码
 注册
查看: 6165|回复: 11

[原创] 一文教会你数据库性能调优(附某大型医院真实案例)

[复制链接]
认证徽章
论坛徽章:
9
慢羊羊
日期:2015-03-04 14:55:272015年新春福章
日期:2015-03-06 11:59:47技术图书徽章
日期:2017-02-09 17:05:19秀才
日期:2017-02-22 15:16:26秀才
日期:2017-02-22 15:18:00现任管理团队成员
日期:2017-06-03 02:10:11版主1段
日期:2017-06-05 09:06:08秀才
日期:2017-08-18 11:04:35秀才
日期:2017-09-18 17:02:49
发表于 2018-1-19 10:25 | 显示全部楼层 |阅读模式
本帖最后由 owen_zeng 于 2018-1-24 17:09 编辑

阅读目录


前言
微软工程师的一个工程师曾经对性能调优有一个非常形象的比喻:剥洋葱 。我也非常认可,让我们来一层一层拨开外面它神秘的面纱。


六大因素
下面祭出的是我们在给客户分析数据库性能问题最常用的图。
看完这个图,你是不是对性能调优有了个基本的概念了.通常来讲我们会依照下面的顺序来进行分析:
硬件能力
系统规模
数据库内部因素
软件环境
这4个的顺序可以有所调整或者交换,但是对于系统的性能优化一定要从全局出发切勿一来就深入到某一个SQL语句的优化,因为可能你花费大量的
时间吧一个SQL从20s 优化到1s,但是整个系统的卡慢仍然存在。
最后才是
业务模型及架构
代码设计


实战案例
不废话了,开整开整,直接上干货。
时间:2018年1月某天
事件:某医院客户 下午4点 突然出现大面积的卡慢。整个系统出现严重问题,信息中心电话打爆,医院工程师手足无措。
万幸的是我们给数据库装了‘摄像头’,下面就从监控录像来看看发送了什么。然后加以解决


硬件能力CPU
在问题发生时间段内CPU使用率在20%以下,正常。

Memory
从下面的图像显示,内存使用正常。
页生命周期
可用内存


IO
IO队列平均值很低,15.48 左右有个瞬时的高点,可留意这段时间有没有批量的写入。
总的来看,硬件资源是足够的。

系统规模
问题发生时,每秒的批请求书并不是一个上升趋势,反而有所下降。这是因为系统的拥堵,等待 ,影响了系统的吞吐量。

数据库内部因素等待
慢语句
从会话和慢语句的趋势图可以看到,问题发生的时间和客户描述完全吻合,我们可以断定本身事故的确是慢在数据库。
什么导致的慢
检查者个时间段运行中的语句,可以发现下午15.58左右,数据库中开始出现越来越多的CMEMTHREAD等待。

一直到1900页16.08分的时候,出现了最高达100个并发同时出现CMEMTHREAD等待
什么是CMEMTHREAD等待
微软官方的描述:在任务正在等待线程安全的内存对象时发生。 当多个任务尝试从同一个内存对象分配内存导致争用时,等待时间可能会增加。
这个描述很晦涩,感觉还是完全不知道等待类型是怎么回事,应该怎么处理这类问题。

实际上,从官方描述来看是内存争用的问题,但是实际上这个问题的关键在于多个任务的争用,实际上是并发的执行的问题。
场景
  • 出现在数据库编译或重编译时,将即席执行计划ad hoc plans 插入到计划缓存中的时候
  • NUMA架构下,内存对象是按照节点来分区的
内存对象有三种类型的(Global,Per Numa Node,Per CPU)。 SQL Server将允许对内存对象进行分段,以便只有同一节点或cpu上的线程具有相同的底层CMemObj,从而减少来自其他节点或cpu的线程交互,从而提高性能和可伸缩性。减少内存的并发争用

SELECTtype, pages_in_bytes,CASEWHEN (0x20 = creation_options & 0x20) THEN 'Global PMO. Cannot be partitioned by CPU/NUMA Node. TF 8048 not applicable.'WHEN (0x40 = creation_options & 0x40) THEN 'Partitioned by CPU.TF 8048 not applicable.'WHEN (0x80 = creation_options & 0x80) THEN 'Partitioned by Node. Use TF 8048 to further partition by CPU'ELSE 'UNKNOWN'ENDfrom sys.dm_os_memory_objectsorder by pages_in_bytes desc

如果你发现,Partitioned by Node 的内存开销是排在前面的,可以使用TRACE FLAG 8048来减少CMEMTHREAD等待.
file:///C:/Users/Administrator.SKY-20171226RJI/AppData/Local/YNote/data/10843087@163.com/72d0f772665e4a1087a614b73e5af634/clipboard.png

从图中可以看到,客户的 Partitioned by Node 是比较靠后的,排在14位。
       3. 补丁
这类场景是最常见的。如果在系统中发现出现大量的CMEMTHREAD等待,优先考虑数据库是不是已经安装最新的补丁


软硬件环境
目前数据库的版本是 11.0.5556.0   而前面提到的补丁,安装后的版本是:11.0.5623.0


代码设计是什么语句产生了等待
都是类似下面的语句,最高时,并发超过100.
SELECT     * INTO #Tmp from TB where 1=2
特点如下:
1.语句简单 开销都小于5不会产生并行
2.都采用了select into #temptable的形式
就像上面分析的一样,CMEMTHREAD等待是一个并发问题,而不是一个内存问题。在其他方案行不通的时候,我们可以通过调整此类语句的写法,减少CMEMTHREAD等待.

业务模型及架构
目前系统是单机运行的状态,这其实是很少见的。存在少量OLAP 和OLTP业务混合的情况。后续我们会给客户规划 读写分离 或者负载均衡的解决方案。在


解决方案安装最新的补丁
至少需要安装前面发的解决等待问题的FIX。建议是直接安装到目前为止最新的2012 SP4补丁。
修改参数
optimize for ad hoc workloads 从0修改为1 。针对将即席执行计划ad hoc plans 插入到计划缓存中的时候 场景,减少ad hoc 查询占用的内存。
增加TEMPDB数据文件的个数
select * into #temptable 会产生大量的闩锁争用,防止在CMEMTHREAD 等待消除后,出现大量的pagelatch 闩锁争用。我经历过很多案例,解决了前面的一个拥堵之后,
后面有产生了新的等待,导致性能更差了。请记住优化是一个长期的,循序渐进的过程。
迁移TEMPDB数据文件的位置
目前部分tempdb文件放在S,一般分放在D盘。建议都迁移到S盘(存储上面),增加tempdb的响应速度。如果可能的话,使用SSD来最大化tempdb的性能,将会是不错的选择。

优化程序的代码
修改代码通常都是放在最后面的,因为要牵涉的情况比较多。前面的手段80%的情况下,都可以解决问题。剩下的20%,我们需要,检查程序中的逻辑,看看这些的语句都是什么业务产生的。什么条件会触发这类业务.对应下面类似的语句都使用存储过程,或者参数化后的方式,减少编译和重编译的次数。另外此类语句都会并发创建临时表,可能通过调整tempdb的设置,加快此类语句的执行速度,减少同一时间此类语句的并发数量。


优化效果
经过前面的几个优化手段,第二天开始,没有再出现过一次CMEMTHREAD的等待。
等待
慢语句


总结
通过这篇文件你应该已经完全学会了数据库性能调优的思想。他告诉了我们出现问题时,怎么动手一步一步的排查问题,就像剥洋葱一样一层一层的剥开。



参考

微软官方博客对这类等待的原理和如何调试How It Works: CMemThread and Debugging Them
SQL Server 2016 对这里问题进行了进一步的优化,详细参考     SQL 2016 – It Just Runs Faster: Dynamic Memory Object (CMemThread) Partitioning

论坛徽章:
181
秀才
日期:2016-02-18 10:06:21白羊座
日期:2016-01-22 12:49:11秀才
日期:2016-01-21 13:37:04摩羯座
日期:2016-01-20 16:48:10火眼金睛
日期:2016-01-31 22:00:00ITPUB15周年纪念
日期:2016-10-13 13:15:34巨蟹座
日期:2016-01-30 22:10:33目光如炬
日期:2016-01-03 22:00:00秀才
日期:2015-12-21 09:53:46目光如炬
日期:2015-12-20 22:00:00
发表于 2018-1-19 13:41 | 显示全部楼层
这机器cpu384核?够强。
内存多少?

使用道具 举报

回复
认证徽章
论坛徽章:
9
慢羊羊
日期:2015-03-04 14:55:272015年新春福章
日期:2015-03-06 11:59:47技术图书徽章
日期:2017-02-09 17:05:19秀才
日期:2017-02-22 15:16:26秀才
日期:2017-02-22 15:18:00现任管理团队成员
日期:2017-06-03 02:10:11版主1段
日期:2017-06-05 09:06:08秀才
日期:2017-08-18 11:04:35秀才
日期:2017-09-18 17:02:49
发表于 2018-1-19 14:06 | 显示全部楼层
jieyancai 发表于 2018-1-19 13:41
这机器cpu384核?够强。
内存多少?

1T

使用道具 举报

回复
论坛徽章:
63
林肯
日期:2013-09-12 15:57:33马自达
日期:2013-10-11 13:52:31路虎
日期:2014-01-26 14:35:49三菱
日期:2013-11-25 11:21:19现代
日期:2013-08-29 14:39:50雪佛兰
日期:2013-09-12 15:55:00一汽
日期:2013-11-28 14:15:05技术图书徽章
日期:2013-12-11 10:11:35技术图书徽章
日期:2014-01-14 10:54:13技术图书徽章
日期:2013-12-11 10:10:51
发表于 2018-1-22 09:40 | 显示全部楼层
收藏,慢慢看! 

BTW, 楼上这些snapshot 是用什么监控工具捕获的, 能否提供名字和方法?

使用道具 举报

回复
认证徽章
论坛徽章:
9
慢羊羊
日期:2015-03-04 14:55:272015年新春福章
日期:2015-03-06 11:59:47技术图书徽章
日期:2017-02-09 17:05:19秀才
日期:2017-02-22 15:16:26秀才
日期:2017-02-22 15:18:00现任管理团队成员
日期:2017-06-03 02:10:11版主1段
日期:2017-06-05 09:06:08秀才
日期:2017-08-18 11:04:35秀才
日期:2017-09-18 17:02:49
发表于 2018-1-22 09:53 | 显示全部楼层
pastime_Wang 发表于 2018-1-22 09:40
收藏,慢慢看! 

BTW, 楼上这些snapshot 是用什么监控工具捕获的, 能否提供名字和方法?

这个工具是我们公司研发的产品,,名字我需要的话可以站内短信我。

使用道具 举报

回复
论坛徽章:
63
林肯
日期:2013-09-12 15:57:33马自达
日期:2013-10-11 13:52:31路虎
日期:2014-01-26 14:35:49三菱
日期:2013-11-25 11:21:19现代
日期:2013-08-29 14:39:50雪佛兰
日期:2013-09-12 15:55:00一汽
日期:2013-11-28 14:15:05技术图书徽章
日期:2013-12-11 10:11:35技术图书徽章
日期:2014-01-14 10:54:13技术图书徽章
日期:2013-12-11 10:10:51
发表于 2018-1-22 09:54 | 显示全部楼层
欧, 不是免费的就算了, 谢谢!

使用道具 举报

回复
论坛徽章:
1
秀才
日期:2017-02-22 15:18:00
发表于 2018-1-24 16:02 | 显示全部楼层
本帖最后由 mssql_wangwang 于 2018-1-24 16:04 编辑

SQLSERVER 的DMV已经可以收集很多相关的服务器状态。
查看sys.dm_os_wait_stats  看按等待时间DESC查看前十
其中最重要的一点应该首先看CXPACKET并发度,特别是CPU核心特别多的服务器,
一定得把握好'max degree of parallelism‘ 的设置,
当解决了并发度后,CMEMTHREAD这个等待自然也就解决了。

1. Partitioned by Node  与group by 类似的计算类聚合函数本身就是非常耗CPU资源的,
如果此时不控制并发度,只要有一个节点能力计算弱或是本身分配的计算任务大,必然会出现wait,
导致几百核的CPU都陪着干等,结果出现慢是必然的。
2.page life expectancy 能维持在40w+的时间上,说明内存是富余的,不需频繁清空。可以直接从缓存中读取数据。
3.IO  avg disk queue length 取决于Raid 的磁盘个数,通数不大于磁盘总个数的2倍。
4.Tempdb 可考虑分多个文件,分布于多个ssd磁盘。

sql2008升级到sql2014如果未打补丁的话,可能会遇到 
select count(distinct filed1) t1, count(distinct filed2) t2  from t 的效率问题。
注意是 count(distinct )

剩下的就是TOP N 的问题了。REBUILD  INDEX,

使用道具 举报

回复
认证徽章
论坛徽章:
9
慢羊羊
日期:2015-03-04 14:55:272015年新春福章
日期:2015-03-06 11:59:47技术图书徽章
日期:2017-02-09 17:05:19秀才
日期:2017-02-22 15:16:26秀才
日期:2017-02-22 15:18:00现任管理团队成员
日期:2017-06-03 02:10:11版主1段
日期:2017-06-05 09:06:08秀才
日期:2017-08-18 11:04:35秀才
日期:2017-09-18 17:02:49
发表于 2018-1-24 17:13 | 显示全部楼层
mssql_wangwang 发表于 2018-1-24 16:02
SQLSERVER 的DMV已经可以收集很多相关的服务器状态。
查看sys.dm_os_wait_stats  看按等待时间DESC查看 ...

兄弟,分析的非常好啊,是哪个公司的,就通过文章提供的信息就能找到一些问题关键。
不过,在出问题之前,最大并行度在最开始就设置为12,tempdb 有24个,都已经在SSD上。这些基本配置都已经配置上了。

使用道具 举报

回复
论坛徽章:
1
秀才
日期:2017-02-22 15:18:00
发表于 2018-1-25 09:49 | 显示全部楼层
本帖最后由 mssql_wangwang 于 2018-1-25 09:55 编辑
owen_zeng 发表于 2018-1-24 17:13
兄弟,分析的非常好啊,是哪个公司的,就通过文章提供的信息就能找到一些问题关键。
不过,在出问题之前 ...

谢,过奖了。国内对SQL SERVER研究深透彻的太少了。主要是几大主流财团未大规模使用MS。
个人感觉现在的SQL SERVER功能,效率,安全等方面已经很不错了。

使用道具 举报

回复
招聘 : 数据库管理员
认证徽章
论坛徽章:
99
技术图书徽章
日期:2014-03-27 09:30:56秀才
日期:2018-04-08 14:48:31
发表于 2018-2-7 14:13 | 显示全部楼层
mssql_wangwang 发表于 2018-1-24 16:02
SQLSERVER 的DMV已经可以收集很多相关的服务器状态。
查看sys.dm_os_wait_stats  看按等待时间DESC查看 ...

这个 分析,高手啊

使用道具 举报

回复

您需要登录后才可以回帖 登录 | 注册

本版积分规则

TOP技术积分榜 社区积分榜 徽章 电子杂志 团队 统计 虎吧 老博客 知识索引树 读书频道 积分竞拍 文本模式 帮助
  ITPUB首页 | ITPUB论坛 | 数据库技术 | 企业信息化 | 开发技术 | 微软技术 | 软件工程与项目管理 | IBM技术园地 | 行业纵向讨论 | IT招聘 | IT文档 | IT博客
  ChinaUnix | ChinaUnix博客 | ChinaUnix论坛 | SAP ERP系统
CopyRight 1999-2011 itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有 联系我们 网站律师 隐私政策 知识产权声明
京ICP备16024965号 北京市公安局海淀分局网监中心备案编号:11010802021510 广播电视节目制作经营许可证:编号(京)字第1149号
  
快速回复 返回顶部 返回列表