图片 45

数据库优化案例——————某知名零售企业ERP系统

写在前面

  记得在自己学习数据库知识的时候特别喜欢看案例,因为优化的手段容易掌握的,但是整体的优化思想很难学会的。这也是为什么自己特别喜欢看案例,今天也分享自己做的优化案例。

  之前分享过OA系统、HIS系统,今天我们来一个最常见的ERP,ERP系统各行各业都在用,不同行业也有不同的特点,博主在做研发的时候还自己写过ERP也算是比较熟悉了。

  不管是本文分享的零售类,还是鞋服门店、家居、汽车、地产等等,也不管是某友、某碟,ERP有一个共同的特点,单据流程长,业务复杂,热点表明显,数据量大,涉及众多系统接口,各种大数据的统计报表….传统行业又缺乏DBA精心管理。

  慢是普遍的!

  最近一直很忙,博客产出也少的可怜,今天整理了一下自己做过优化或各种方案的客户已经超过千家,涉及各行各业,今天分享的案例算是在这些客户中比较典型的了!没有什么高大上都是常见的问题!在之前的博客中都有过提及,那么本篇我们就结合之前的技术点来看看这个案例。学习优化手段的看官们可以参见我的优化系列:

 

  记得在自己学习数据库知识的时候特别喜欢看案例,因为优化的手段容易掌握的,但是整体的优化思想很难学会的。这也是为什么自己特别喜欢看案例,今天也开始分享自己做的优化案例。

SQL SERVER全面优化——-Expert for SQL Server 诊断系列

 

————–博客地址—————————————————————————————

Expert 诊断优化系列 http://www.cnblogs.com/double-K/

 

 

废话不多说,直接开整—————————————————————————————–

 

  最近一直很忙,博客产出也少的可怜,今天整理了一下自己做过优化或各种方案的客户已经超过100家了,今天分享的案例算是在这些客户中比较典型的了!没有什么高大上都是常见的问题!在之前的博客中都有过提及,那么本篇我们就结合之前的技术点来看看这个案例。学习优化手段的看官们可以参见我的优化系列:

用户现象

  系统慢!保存个单据要好几分钟,很多操作都超时,尤其到下午4点左右各种超时,收款什么的都收不了,

  查个报表一个小时,下班了还没查完,经常因为系统慢而加班,

  业务部门已经怨声载道,这个事情已经上报公司高层IT部分压力非常大!

SQL SERVER全面优化——-Expert for SQL Server 诊断系列

————–博客地址—————————————————————————————

Expert 诊断优化系列 http://www.cnblogs.com/double-K/

 

 

废话不多说,直接开整—————————————————————————————–

 

系统环境

  首先我们来看一下这个系统配置及现状,为什么说这个客户经典?往下看就知道了…

  

  先来看看系统配置 :

  

  图片 1

 

   服务器的配置是:8路 24 core 做了超线程
384个逻辑CPU,内存1T,磁盘全闪

   图片 2

     SQL用了2012版本,补丁已经最新,而且服务器配置全部能够识别

    没错。相当牛逼得配置!

  

     图片 3

  

  数据库的大小在1.2个T

 

  咋一看也许数据量太大了,导致性能的问题!可又一想这么强力的服务器也不至于那么慢呀,难道是代码的问题?难道需要分库分表?

系统环境

  首先我们来看一下这个系统配置及现状,为什么说这个客户经典?那就是因为这个客户已经达到可以慢的地方都慢,不该慢的地方也慢!

  首先这是一套医院的HIS系统,慢到什么程度呢?各种功能卡死不管是交款、医嘱、开药一些列几乎所有的功能都慢。但是卡慢的现象只出现在上午的高峰期!

  先来看看系统配置 :

  图片 4

  图片 5

   图片 6

 

  数据库版本是SQL SERVER 2008R2,数据量大概1个多T,服务器64CPU
、128G内存,服务器只运行数据库。

  咋一看服务器确实有点老了,数据量也大了,内存和CPU什么的明显不够用了!

数据库指标

  那么我们再看一下数据库的一些表象:

  每秒请求数量:

  图片 7

  用户连接数:

  图片 8

 

 

  语句执行情况:

  图片 9

  图片 10

  

 

 

  等待情况:

  图片 11

 

  图片 12

 

  等待时间:

  图片 13

 

   CPU指标:

  图片 14

 

  内存一些指标:

  图片 15

 

  图片 16

 

 

  磁盘队列:

  图片 17

 

 

 ——————-还很多指标就不一一展示了——————

 

   看到这些基本的指标,除了慢你能看出什么?问题出在哪里?怎么样快速解决?能有一个优化的步骤呈现在眼前么?

 

数据库指标

  那么我们再看一下数据库的一些表象:

  每秒请求数量:

  图片 18

  语句执行情况:

  图片 19

  等待情况:

  图片 20

  等待时间:

  图片 21

   CPU指标:

  图片 22

  内存一些指标:

  图片 23

  磁盘队列:

  图片 24

 

 ——————-还很多指标就不一一展示了——————

 

   看到这些基本的指标,除了慢你能看出什么?问题出在哪里?怎么样快速解决?能有一个优化的步骤呈现在眼前么?

分析

  系统是真的很慢,慢语句数量很多系统阻塞也很严重,确实和客户反映的慢可以吻合。那为什么这么慢?什么原因导致的?

  我总结一般性能慢常和6大因素有关:

  1.   业务压力
  2.   硬件
  3.   环境
  4.   代码
  5.   数据库内部运行因素
  6.   架构

 

 奉上一幅草图

  图片 25

  系统压力:访问压力(也是我们常说的并发)其实并不大,用户连接数也没想像的那么多

  硬件:在内存和磁盘IO确实存在压力

  环境 :服务器和数据库版本什么的没什么问题,具体配置一会儿再看。

  代码 :最不想分析代码,我们留到最后

  数据库内部运行因素:从各种指标来分析,系统语句等待时间太长,导致语句完成慢,而等待主要有两部分:

  1.  硬件资源确实有压力
  2.  语句之前的阻塞太严重了,"LCK_M_",而且等待时间过长,竟然平均达到几百秒

  再分析…这么强的硬件,并不大的访问压力,竟然造成瓶颈?语句写的烂?程序实现的不好?缺索引?环境配置不对?

  下面我们来看看….

 

优化阶段一(常规优化)

  很多时候系统慢要究其原因,难道上线时候就这么慢?那不可能,厂商根本无法交付的!那么问题来了,什么时候开始慢的?对系统做过哪些调整?

  简单的调研开始…给我的只有不到半天的调研时间…得知的基本问题就是系统在最近一月增加了很多功能,有上线了很多其他系统接口!

  那么直接就搞新功能、新程序接口语句?
我认为并不是这样,从一名数据库从业人员来说,看到这样的系统一定要先解决大面积等待问题!个人经验来看很多系统大面积等待解决系统会有个很大的提升和改善!

  配合一些常规的调优手段阶段一开始了,主要给系统大面积创建影响高开销大的索引,调整系统参数,优化tempDB、开启快照读等….具体不细说了,前面系列文章中都有!

 

  预期:

  一般系统上面一轮优化会有明显的改善,我认为这一轮以后系统会明显变快,语句CPU会下降到70%左右,内存压力也会有所减少。

  结果:

  自信满满的我第二天去了各个科室….部分功能依然超时还是各种慢…CPU依然90%以上,内存压力依然明显。但是收集的数据来看,长时间语句数量已经大幅降低,系统等待阻塞情况也明显好转。

  

  优化前

  图片 26

  优化后

  图片 27

  优化前

  图片 28

  优化后

  图片 29

  

优化阶段一(常规优化)

  很多时候系统慢要究其原因,难道上线时候就这么慢?那不可能,厂商根本无法交付的!那么问题来了,什么时候开始慢的?对系统做过哪些调整?

  简单的调研开始…

  我靠!!!厂商完全不配合,工程师对系统及其不熟悉,一问三不知,最近做什么改动也说不清,用户也不知道。厂商给的结论:继续加硬件….更强的IO….数据分离减小数据量!

  协调厂商完全协调不动,基本没戏了!

  既然是数据库问题,那我们就数据库下手吧!从一名数据库从业人员来说,看到这样的系统一定要先解决大面积等待问题!个人经验来看很多系统大面积等待解决系统会有个很大的提升和改善!

  配合一些常规的调优手段阶段一开始了,主要给系统大面积创建影响高开销大的索引,调整系统参数,优化tempDB等….具体不细说了,前面系列文章中都有!

 

  预期:

  一般系统上面一轮优化会有明显的改善,我认为这一轮以后系统会明显变快,语句运行环境合适,索引什么的合理资源消耗自然就少,内存和IO压力也会有所减少。

  结果:

  系统内存,IO压力趋于平稳,慢语句数量有所减少,但依然很多,阻塞依然存在,超过2分钟的语句依然很多。

  

  优化前

  图片 30

 

  优化后

  图片 31

 

 

  优化前

  图片 12

  优化后

  图片 33

 

  

优化阶段二(针对语句)

   再次分析解决大面积语句阻塞的系统,发现现在的情况,主要有如下几个:

  1. 由于内存不足导致的IO压力。
  2. 系统CPU依然彪高。
  3. 部分功能语句依然慢,消耗的资源很高。

  再次对系统调研:

  1. 哪些功能慢,执行的语句是什么。
  2. 系统的接口语句问题。
  3. 系统中还有哪些消耗资源高的语句,是否能优化。  

  

  调研后,我遇到了最常见也是最大的问题:
语句慢由于程序!很多人看到这会说程序慢就改呗,那有啥问题?
问题就在于你来做优化直接了当的和人家开发人员说你程序太烂必须改!如果你是程序开发人员你会有什么样的反应?

  他会说:对不起,影响太大改不了!

  那么这个优化项目黄了,或者你要付出更大的代价绕过这样的问题。

 

 

   分析中发现程序使用了大量各种自定义函数,有一定经验的人都应该知道,语句在筛选的列上使用函数是没有办法使用索引查找的,这样相对于这种单表数据就几百甚至几千万的表,是何等的灾难!但是不能冒然突出修改程序,那还能怎么优化呢?大概分析后得出结论,程序主要消耗在几部分:

  1. 部分业务功能语句慢。
  2. 接口语句慢(主要是视图,供其他程序调用)。
  3. 还有报表程序。

 

  针对第一部分在不能改程序的情况下,尝试添加计划向导改变语句执行情况;

  针对第二部分修改接口视图,包括替换掉函数、添加索引等;

  针对第三部分报表这东西不是短期就可以优化的,所以再原有镜像的方案上添加快照,实现了简单的读写分离,直接分走;

  

  语句优化的效果:

  优化前

  图片 34

  优化后

  图片 35

  优化前

  图片 36

  优化后

  图片 37

  

 

   预期:

  90%消耗高的语句都得到了优化,系统应该可以快起来了,CPU、内存指标也应该正常了!

   结果:

  语句的消耗和时间都降下来了,系统卡慢现象有明显好转,但是CPU依然90%以上、内存压力依然明显,磁盘队列还是很高!系统性能问题依然存在。

优化阶段二(针对语句)

   再次分析解决大面积语句阻塞的系统,发现现在的情况,主要有如下几个:

  1. 内存某些时候还是存在波动,但整体IO 内存已经不是瓶颈。
  2. 系统中有SLEEPING的程序阻塞时间长
  3. 部分功能语句依然慢,消耗的资源很高。

  再次对系统调研:

  1. 执行的慢语句是什么业务,是业务功能?还是报表?还是接口?
  2. 系统中频繁且较慢的语句。
  3. 系统中阻塞的操作是什么。  

  

  调研后,我遇到了最常见也是最大的问题:
语句慢由于程序!在HIS的优化案例中就是因为程序大量使用自定义函数,我们没法改,我们巧妙的绕过。那么这次我们如何绕过?

   

  一:报表

  分析中发现程序系统中消耗最多资源的主要是报表。

  报表通过一系列复杂的查询插入到物理临时表,啥叫物理临时表?
就是非#temp 而是真真正正的插入到表中,用完在delete!

  插入在删除,中间还有跟业务表关联操作,导致报表也会阻塞业务!

  插入删除的数据量是多少? 你们猜一下??

  千万级别….

  

  二:接口

  接口程序中频繁调用业务数据并发更新频繁….导致业务受阻…

 

  三:问题代码

  代码的问题主要有两个:

  1.代码较复杂,需要细致优化。

  2.程序中存在连接泄露,简单理解成程序报错后事务不能有效处理,导致事务未提交阻塞系统

  图片 38

 

  针对第一部分报表,语句更是复杂至极…这东西不是短期就可以优化的,考虑分出去

  针对第二部分接口,修改接口视图,包括写法优化、添加索引、调用频率等;

  针对第三部分业务语句进行细致优化,查询提示,计划向导、重编译等等手段…

  

  

优化阶段三(深入指标分析)

  经过前两个阶段的优化一般系都会明显好转,并且指标正常,这也是前面提到的可以慢的地方慢已经解决,那么为什么CPU、内存压力没有缓解?难道真的是64CPU、128G内存不能支持了?需要加内存换CPU?难道要做负载均衡?各种拆分?

优化阶段三(报表分离)

  经过前两个阶段的优化一般系都会明显好转,只剩报表没有处理,和一部分高消耗的频繁接口查询,这部分我们采用报表分离的方式去解决。

  这里面我们遇到一个问题,报表要写物理表!用2012
自带的AlwaysOn是没有办法实现的(辅助节点只能读)

  

  使用发布订阅,又不能同时满足数据安全和业务连续的要求,客户又不满意。

  

  我们想到是否可以把写入物理表变成写入#temp 临时表?
软件厂商给出的结论是:不可能….

  

     那这里面我们使用了第三方的产品Moebius集群(这里真的不是广告….)

 

  如何实现:  

  多活集群,几个节点数据实时一致,这样的基本知识就不普及了…集群介绍也免了

  首先程序只有一个连接字符串没法把报表指向到辅助服务器,我们只能通过Moebius集群的前端调度引擎,定制规则把报表所使用的存储过程定点指向到第二台服务器,解决了程序不能分离的问题。

  其次Moebius集群可以实现两个节点都可写,以满足辅助节点报表查询写入物理表的需要。

  再次临时表的写入量太大,千万级别数据同步也是问题,这里好就好在程序中写入的物理临时表都是以“Temp_”
开头并以GUID类型结尾。我们在这里设置了只要这样的表写入不会反向同步给主节点,这样根据规则控制双向同步满足了报表的要求,最终实现了报表的分离。

  报表快了? 当然没有,只是分离不可能快,但是好处有两个:

  1.   OLAP和OLTP分离事务阻塞得到解决
  2.   报表服务器和业务服务器可以根据自身的业务特别进行单独的个性化设置
  3.   根据报表的要求我们配置高速IO的硬件

 

  预期:

  语句已经优化,阻塞情况也被解决,CPU、内存、磁盘压力也没有了,系统肯定快起来了!

  结果:

  系统快起来了!

  

  最终业务系统节点全天24小时的慢语句数量:(虽然还有慢语句存在,毕竟是TB级别的数据量,不影响业务运行客户完全可以接受!)

  图片 39

 

————–博客地址—————————————————————————————

Expert 诊断优化系列 http://www.cnblogs.com/double-K/

 

 


 

  总结 : 系统慢往往我们要全面分析,本文提供的维度:

  1.   业务压力
  2.   硬件
  3.   环境
  4.   代码
  5.   数据库内部运行因素
  6.   架构

 

    往往优化真的不是简单的调一调语句,加一加硬件,全面地分析是根本解决性能问题的首要任务。

  当然不是所有的优化都可以彻底解决,如本文中报表的改善是通过读写分离的方式实现,很多时候在ERP系统中报表的处理方式都是如此,报表如果细致优化,那需要多长时间呀!也许都是重写了。

 

  本文的优化过程主要是:全面分析系统问题——〉宏观层面解决(环境、数据库内部运行因素、硬件压力)——〉低效代码调整——〉架构方案实现(稳定、安全、高效)——〉最终系统顺畅
无压力

 

  当然此案例中客户的数据量已经到了可以做数据分离,分区分表的阶段,但分享本案例的原因也在于,不要认为上TB的数据一定就要分库分表的各种拆分,在性能调优的简单付出中依然可以收获更大的收益,真心希望看官们在选择分库分表付出的极大代价之前可以找专业的人全面分析一下,仔细评估你的系统到底是什么瓶颈!

 

 

 —————————————————————————————————-

注:此文章为原创,欢迎转载,请在文章页面明显位置给出此文链接!
若您觉得这篇文章还不错请点击下右下角的推荐,非常感谢!

如果您也遇到类似问题欢迎添加微信技术交流

 图片 40

 

CPU分析

  首先我对CPU压力进行了分析,综合语句的CPU消耗和CPU的表象来看,很大一部分应该不是语句执行消耗的!那么服务器上确实也没有跑其他程序,CPU资源哪里去了?

  看看这个计数器:

  图片 41

 

  SQL的编译次数高峰时间段达到每秒2000多次!很多书上写过,相信很多看官也知道,语句不参数化会给CPU造成压力,这就是个鲜活的例子!那么解决办法也是比较粗暴,程序无法修改那么就在数据库上开启强制参数化。

  看下效果:

  图片 42

  图片 43

 

   我想不用多说什么了!

  

内存分析

  看到了CPU的现象那么内存的问题也有眉目了,这么多编译即席查询,首先看一下内存中缓存了那些数据:

  图片 44

 

  SQLOPTIMIZER
Singlepage占到了80多个G,而在查询数据页的缓存只有20个G,而且仍然在被不断压缩,那么内存没压力就怪了!这个SQLOPTIMIZER
Singlepage尝试了一下是无法通过DBCC
FREExxxxx的操作释放的,所以在半夜直接重启了SQL
服务!将近2年没有重启的SQL服务就这么折在我的手里了!

   重启后页生命周期:

  图片 45

  

  内存这个问题,不知道是不是微软的一个小BUG,查询计划的缓存个人理解不会一直压榨数据缓存的,客户的数据库没有补丁,但是查阅08的各个补丁也没有找到相关问题的修复。

  也请遇到过或了解的朋友给点提示!

 

  预期:

  语句已经优化,阻塞情况也被解决,CPU、内存、磁盘压力也没有了,系统肯定快起来了!

  结果:

  系统快起来了!

————–博客地址—————————————————————————————

Expert 诊断优化系列 http://www.cnblogs.com/double-K/

 

 


 

  总结 :
文章只是简单的描述了一下某医院HIS系统的优化过程,当然一周的工作仅仅通过一篇文章写出全过程细节必然不那么详尽,还望看官们见谅!

      整个的优化过程是程序只修改了2条语句,其他都是通过数据库优化手段完成。而且没有添加任何硬件资源!

优化过程主要分为:

  1. 系统整体调研
    :和科室用户沟通慢的情况,系统最近变更情况,并收集数据。
  2. 常规优化 : 调整数据库参数配置,添加索引,解决阻塞。
  3. 再次调研:系统慢功能,慢语句。
  4. 针对语句优化:写法不足,是否缺失索引,是否能加提示、计划向导等
  5. 整体压力是否缓解:如果仍然压力很大找到瓶颈,是否可以解决?如果不能解决才考虑添加硬件或选用分离、分离等方案。

 

 文章用用到的 Expert FOR SQLSERVER
工具下载链接:
http://zhuancloud.com/ReceptionViews/Install.html**

 —————————————————————————————————-

注:此文章为原创,欢迎转载,请在文章页面明显位置给出此文链接!
若您觉得这篇文章还不错请点击下右下角的推荐,非常感谢!