一次性能优化实战经历

每次经历数据库性能调优,都是对性能优化的再次认识、对自己知识不足的有力验证,只有不断总结、学习才能少走弯路。

一、性能问题描述

应用端反应系统查询缓慢,长时间出不来结果。SQLServer数据库服务器吞吐量不足,CPU资源不足,经常飙到100%…….

二、监测分析

收集性能数据采用二种方式:连续一段时间收集和高峰期实时收集

连续一天收集性能指标(以下简称“连续监测”)

目的: 通过此方式得到CPU/内存/磁盘/SQLServer总体情况,宏观上分析当前服务器的主要的性能瓶颈。

工具: 性能计数器 Perfmon+PAL日志分析器(工具使用方法请参考另外一篇博文

配置:

  1. Perfmon配置主要性能计数器内容具体如下表
  2. Perfmon收集的时间间隔:15秒 (不宜过短,否则会对服务器性能造成额外压力)
  3. 收集时间:  8:00~20:00业务时间,收集一天

分析监测结果

收集完成后,通过PAL(一款日志分析工具,可见我的另外一篇博文介绍)工具自动分析出结果显示主要性能问题:

业务高峰期CPU接近100%,并伴随较多的Latch(闩锁)等待,查询时有大量的扫表操作。这些只是宏观上得到的“现象级“的性能问题表现,并不能一定说明是CPU资源不够导致的,需要进一步找证据分析。

PAL分析得出几个突出性能问题

1. 业务高峰期CPU接近瓶颈:CPU平均在60%左右,高峰在80%以上,极端达到100%

2. Latch等待一直持续存在,平均在>500。Non-Page Latch等待严重

3. 业务高峰期有大量的表扫描

4. SQL编译和反编译参数高于正常

5.PLE即页在内存中的生命周期,其数量从某个时间点出现断崖式下降

其数量从早上某个时间点下降后直持续到下午4点,说明这段时间内存中页面切换比较频繁,出现从磁盘读取大量页数据到内存,很可能是大面积扫表导致。

实时监测性能指标

目的: 根据“连续监测“已知的业务高峰期PeakTime主要发生时段,接下来通过实时监测重点关注这段时间各项指标,进一步确认问题。

工具: SQLCheck(工具使用介绍文章后面会发出)

配置: 客户端连接到SQLCheck配置

小贴士:建议不要在当前服务器运行,可选择另外一台机器运行SQLCheck

分析监测结果

实时监测显示Non-Page Latch等待严重,这点与上面“连续监测”得到结果一直

Session之间阻塞现象时常发生,经分析是大的结果集查询阻塞了别的查询、更新、删除操作导致

详细分析

数据库存存在大量表扫描操作,导致缓存中数据不能满足查询,需要从磁盘中读取数据,产生IO等待导致阻塞。

 1. Non-Page Latch等待时间长

2. Non-Page Latch等待发生时候,实时监测显示正在执行大的查询操作

3. 伴有session之间阻塞现象,在大的查询时发生阻塞现象,CPU也随之飙到95%以上

解决方案

找到问题语句,创建基于条件的索引来减少扫描,并更新统计信息。

上面方法还无法解决,考虑将受影响的数据转移到更快的IO子系统,考虑增加内存。

三、等待类型分析

通过等待类型,换个角度进一步分析到底时哪些资源出现瓶颈

工具:  DMV/DMO

操作:

1. 先清除历史等待数据

选择早上8点左右执行下面语句

2. 晚上8点左右执行,执行下面语句收集Top 10的等待类型信息统计。

3.提取信息

查询结果得出排名:

1:CXPACKET

2:LATCH_X

3:IO_COMPITION

4:SOS_SCHEDULER_YIELD

5:   ASYNC_NETWORK_IO

6.   PAGELATCH_XX

7/8.PAGEIOLATCH_XX

跟主要资源相关的等待方阵如下:

CPU相关:CXPACKET 和SOS_SCHEDULER_YIELD

IO相关: PAGEIOLATCH_XXIO_COMPLETION

Memory相关 PAGELATCH_XX、LATCH_X

进一步分析前几名等待类型

当前排前三位:CXPACKET、LATCH_EX、IO_COMPLETION等待,开始一个个分析其产生等待背后原因

CXPACKET等待分析

CXPACKET等待排第1位, SOS_SCHEDULER_YIELD排在4位,伴有第7、8位的PAGEIOLATCH_XX等待。发生了并行操作worker被阻塞

说明:

1.    存在大范围的表Scan

2.    某些并行线程执行时间过长,这个要将PAGEIOLATCH_XX和非页闩锁Latch_XX的ACCESS_METHODS_DATASET_PARENT Latch结合起来看,后面会给到相关信息

3.    执行计划不合理的可能

分析:

1.     首先看一下花在执行等待和资源等待的时间

2.     PAGEIOLATCH_XX是否存在,PAGEIOLATCH_SH等待,这意味着大范围SCAN

3.     是否同时有ACCESS_METHODS_DATASET_PARENT Latch或ACCESS_METHODS_SCAN_RANGE_GENERATOR LATCH等待

4.     执行计划是否合理

信提取息:

获取CPU的执行等待和资源等待的时间所占比重

执行下面语句:

结论:从下表收集到信息CPU主要花在资源等待上,而执行时候等待占比率小,所以不能武断认为CPU资源不够。

造成原因

缺少聚集索引、不准确的执行计划、并行线程执行时间过长、是否存在隐式转换、TempDB资源争用

解决方案:

主要从如何减少CPU花在资源等待的时间

1.    设置查询的MAXDOP,根据CPU核数设置合适的值(解决多CPU并行处理出现水桶短板现象)

2.    检查”cost threshold parallelism”的值,设置为更合理的值

3.    减少全表扫描:建立合适的聚集索引、非聚集索引,减少全表扫描

4.    不精确的执行计划:选用更优化执行计划

5.    统计信息:确保统计信息是最新的

6.    建议添加多个Temp DB 数据文件,减少Latch争用,最佳实践:>8核数,建议添加4个或8个等大小的数据文件

LATCH_EX等待分析

LATCH_EX等待排第2位。

说明

有大量的非页闩锁等待,首先确认是哪一个闩锁等待时间过长,是否同时发生CXPACKET等待类型。

分析

查询所有闩锁等待信息,发现ACCESS_METHODS_DATASET_PARENT等待最长,查询相关资料显示因从磁盘->IO读取大量的数据到缓存,结合与之前Perfmon结果做综合分析判断,判断存在大量扫描。

运行脚本

信提取息:

造成原因

有大量的并行处理等待、IO页面处理等待,这进一步推定存在大范围的扫描表操作。

与开发人员确认存储过程中使用大量的临时表,并监测到业务中处理用频繁使用临时表、标量值函数,不断创建用户对象等,TEMPDB 处理内存相关PFSGAMSGAM时,有很多内部资源申请征用的Latch等待现象。

解决方案:

1.    优化TempDB

2.    创建非聚集索引来减少扫描

3.    更新统计信息

4.    在上面方法仍然无法解决,可将受影响的数据转移到更快的IO子系统,考虑增加内存

IO_COMPLETION等待分析

现象

IO_COMPLETION等待排第3位

说明:

IO延迟问题,数据从磁盘到内存等待时间长

分析

从数据库的文件读写效率分析哪个比较慢,再与“CXPACKET等待分析”的结果合起来分析。

Temp IO读/写资源效率

1.    TempDB的数据文件的平均IO在80左右,这个超出一般值,TempDB存在严重的延迟。

2.    TempDB所在磁盘的Read latency为65,也比一般值偏高。

运行脚本:

信提取息:

各数据文件IO/CPU/Buffer访问情况,Temp DB的IO Rank达到53%以上

 

解决方案:

添加多个Temp DB 数据文件,减少Latch争用。最佳实践:>8核数,建议添加4个或8个等大小的数据文件。

其他等待

分析:

通过等待类型发现与IO相关 的PAGEIOLATCH_XX 值非常高,数据库存存在大量表扫描操作,导致缓存中数据不能满足查询,需要从磁盘中读取数据,产生IO等待。

解决方案:

创建合理非聚集索引来减少扫描,更新统计信息

上面方法还无法解决,考虑将受影响的数据转移到更快的IO子系统,考虑增加内存。

四、优化方案

依据以上监测和分析结果,从“优化顺序”和“实施原则”开始实质性的优化。

优化顺序

1.    从数据库配置优化

理由:代价最小,根据监测分析结果,通过修改配置可提升空间不小。

2.    索引优化

理由:索引不会动数据库表等与业务紧密的结构,业务层面不会有风险。

步骤:考虑到库中打表(超过100G),在索引优化也要分步进行。 优化索引步骤:无用索引->重复索引->丢失索引添加->聚集索引->索引碎片整理。

3.    查询优化

理由:语句优化需要结合业务,需要和开发人员紧密沟通,最终选择优化语句的方案

步骤:DBA抓取执行时间、使用CPU、IO、内存最多的TOP SQL语句/存储过程,交由开发人员并协助找出可优化的方法,如加索引、语句写法等。

实施原则

整个诊断和优化方案首先在测试环境中进行测试,将在测试环境中测试通过并确认的逐步实施到正式环境。

数据库配置优化

1. 当前数据库服务器有超过24个核数, 当前MAXDOP为0,配置不合理,导致调度并发处理时出现较大并行等待现象(水桶短板原理)

优化建议:建议修改MAXDOP 值,最佳实践>8核的,先设置为4

2. 当前COST THRESHOLD FOR PARALLELISM值默认5秒

优化建议:建议修改 COST THRESHOLD FOR PARALLELISM值,超过15秒允许并行处理

3. 监测到业务中处理用频繁使用临时表、标量值函数,不断创建用户对象等,TEMPDB 处理内存相关PFSGAMSGAM时,有很多的Latch等待现象,给性能造成影响

优化建议:建议添加多个Temp DB 数据文件,减少Latch争用。最佳实践:>8核数,建议添加4个或8个等大小的数据文件。

4. 启用optimize for ad hoc workloads

5. Ad Hoc Distributed Queries开启即席查询优化

索引优化

1. 无用索引优化

目前库中存在大量无用索引,可通过脚本找出无用的索引并删除,减少系统对索引维护成本,提高更新性能。另外,根据读比率低于1%的表的索引,可结合业务最终确认是否删除索引。

详细列表请参考:性能调优数据收集_索引.xlsx-无用索引

无用索引,参考执行语句:

表的读写比,参考执行语句

2. 移除、合并重复索引

目前系统中很多索引重复,对该类索引进行合并,减少索引的维护成本,从而提升更新性能。

重复索引,参考执行语句:

3. 添加丢失索引

根据对语句的频次,表中读写比,结合业务对缺失的索引进行建立。

丢失索引,参考执行语句:

4. 索引碎片整理

需要通过DBCC check完成索引碎片清理,提高查询时效率。

备注:当前据库很多表比较大(>50G),做表上索引可能花费很长时间,一般1个T的库要8小时以上,建议制定一个详细计划,以表为单位逐步碎片清理。

索引碎片参考执行语句:

5. 审查没有聚集、主键索引的表

当前库很多表没有聚集索引,需要细查原因是不是业务要求,如果没有特殊原因可以加上。

查询语句优化

1.  从数据库历史保存信息中,通过DMV获取 

  • 获取Top100花费时间最多查询SQL
  • 获取Top100花费时间最多存储过程
  • 获取Top100花费I/O时间最多

参考获取Top100执行语句

2.  通过工具实时抓取业务高峰期这段时间执行语句

收集工具:

推荐使用SQLTrace或Extend Event,不推荐使用Profiler

收集内容:

  • SQL语句
  • 存储过程
  • Statment语句

分析工具:

推荐ClearTrace,免费。具体使用方法请见我的另外一篇博文介绍。

3.  需要逐条分析以上二点收集到语句,通过类似执行计划分析找出更优化的方案语句 

单条语句的执行计划分析工具Plan Explorer,请见我的另外一篇博文介绍

4.  此次优化针对当前库,特别关注下面几个性能杀手问题

五、优化效果

1.  平均CPU使用时间在30000毫秒以上语句由20个减少到3个

2.  执行语句在CPU使用超过10000毫秒的,从1500减少到500个

3.  CPU保持在 20%左右,高峰期在40%~60%,极端超过60%以上,极少80%

4.  Batch Request从原来的1500提高到4000

最后方一张优化前后的效果对比,有较明显的性能提升,只是解决眼前的瓶颈问题。

 小结

数据库的优化只是一个层面,或许解决眼前的资源瓶颈问题,很多发现数据库架构设计问题,受业务的限制,无法动手去做任何优化,只能到此文为止,这好像也是一种常态。从本次经历中,到想到另外一个问题,当只有发生性能瓶颈时候,企业的做法是赶快找人来救火,救完火后,然后就….好像就没有然后…结束。换一种思维,如果能从日常维护中做好监控、提前预警,做好规范,或许这种救火的行为会少些。

感谢2016!

1 3 收藏 评论

相关文章

可能感兴趣的话题



直接登录
跳到底部
返回顶部