SQL调优日记--并行等待的原理和问题排查

原创 2017年08月27日 10:03:00

前言

  
今天处理项目,客户反应数据库在某个时间段,反应特别慢。需要我们提供一些优化建议。
 

现象

     由于是特定的时间段慢,排查起来就比较方便。直接查看这个时间段数据库的等待情况。查看等待类型发现了大量的CXPAKET等待类型且等待时间长.


 
有的看官可能知道,出现这个等待类似时,可以适当降低最大并行度来解决。但是为什么这么做呢?降低并行度就一定可以解决问题吗?

CXPAKET原理

 
  那什么是CXPAKET 等待呢。 当数据库引擎分析查询的开销超过设定的阀值时,SQL SERVER会选择并行执行。数据库引擎会为这个请求创建多个任务。每个任务处理数据的一个子集。每个任务可以在一个分开的CPU/核上执行。请求主要使用生产-消费 队列跟这些任务交互。如果这个队列是空的,(即生产者没有推入任何数据到这个队列)。这个消费者必须暂停并且等待。相应等待类型就是CXPACKET 等待类型。显示这个等待类型的请求 说明这个任务应该提供,但是没有提供任何(或足够)数据来消费。这些生产商任务反过来可能会暂停,等待一些其他类型的等待.
如下图:索引扫描就是一个并行执行的动作。

 

打个比方
  客户端程序就是老板,数据库引擎是部门领导,老板发出一个要求(request),查看最近一年的销售数据。领导一看这任务工作量大,一个人查太慢,要查到猴年马月。果断决定多派几个人。一次最多可以派多少个攻城狮呢?(就取决于最大并行度)这里假设是4个。这就分配4个人 小李、小王、小张、小陈去完成。 那这一年的任务怎么分配呢? 以后再细说。 因为各种原因,其他人都做得了,小王还没有完成。领导不可能拿着半成品的数据就去找老板,只能等着小王。这就是CXPACKET.
 

问题排查


 弄懂了CXPACKET的原理,那我们怎么来排查这类问题呢?首先,小王并不是偷懒,他的工作能力和其他人是相同的。所以,我们需要找出小王慢的原因,
 使用下面的脚本:
select r.session_id,
status,
command,
r.blocking_session_id,
r.wait_type as[request_wait_type],
r.wait_time as[request_wait_time],
t.wait_type as[task_wait_type],
t.wait_duration_ms as[task_wait_time],
t.blocking_session_id,
t.resource_description
from sys.dm_exec_requests r
LEFT join sys.dm_os_waiting_tasks t
on r.session_id = t.session_id
where r.session_id >=50
and r.session_id <> @@spid;
 


通过上面的语句我们找到,并行等待正在等待LCK_M_S.说明查询是被其他的操作阻塞了。上面的问题是由于一个写入语句引起的。这个语句是一个很简单的插入动作,为什么写入会这么慢呢。可以查看磁盘响应时间,,磁盘队列发现都出奇的高。


建议


看来问题是由于磁盘本身引起的。给出如下的解决建议:
1.更换读写速度更快的磁盘
2.目前数据文件和日志文件在同一物理磁盘,分割开来
3.从业务出发。经过和客户沟通后发现,这个表是操作日志表。每次做业务操作都会记录日志。所以特别的大。
对应这样的表,可以单独建立文件夹组,文件,并把表放在单独的磁盘,缓解IO压力
4. 比如传统机械磁盘IOPS往往是瓶颈,而吞吐量并不是,所以磁盘格式化的簇大小就比较重要,较大的簇可以减少IOPS瓶颈。
5.对于日志表,如果能改程序,在前端程序合并写入,或者在情况允许的情况下开trace flag 610最小化日志写入
6.整理磁盘碎片,另外合并&删除日志表的索引减少写入开销也能起到一定作用
 

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/z10843087/article/details/77618652

Java系统排查之四大名捕与7大武器

  • 2017年12月12日 20:50
  • 34.87MB
  • 下载

数据库偶然出现死锁(等待锁超时)的情况处理:

前言:朋友咨询我说执行简单的update语句失效,症状如下: mysql> update order_info  set province_id=15  ,city_id= 1667  where ...
  • qq_35779879
  • qq_35779879
  • 2017-11-20 17:17:06
  • 118

MYSQL 5.7 并行复制实现原理与调优

Contents [hide] 1 MySQL 5.7并行复制时代 2 MySQL 5.6并行复制架构 3 MySQL 5.7并行复制原理 3.1 MySQL 5.7基于组提交的并行复制 3....
  • YABIGNSHI
  • YABIGNSHI
  • 2016-04-20 16:46:04
  • 1995

性能测试及分析调优准则

7.1附录1:执行性能测试基本原则   原则一:测试前,要确认系统级的关键参数已经基本配置正确(例如:数据库、WEB容器、线程池、JDBC连接池、对象池、JVM、操作系统、应用系统等配置);   ...
  • wma1314
  • wma1314
  • 2016-03-18 14:40:17
  • 769

SparkSQL性能调优

最近在学习spark时,觉得Spark SQL性能调优比较重要,所以自己写下来便于更过的博友查看,同时也希望大家给我指出我的问题和不足 在spark中,Spark SQL性能调优只要是通过下面的一些选...
  • YQlakers
  • YQlakers
  • 2017-03-31 14:54:48
  • 4029

oracle数据库调优

  • 2008年11月21日 14:33
  • 14.76MB
  • 下载

oracle 并行parallel操作,会大大提高sql执行效率

如果服务器存在多个cpu的话,我们就可以使用parallel进行并行执行某个查询,插入操作的sql,这样可以大大提高sql的执行效率,具体使用几个并行的进程,可以设置process count = c...
  • fycghy0803
  • fycghy0803
  • 2012-10-17 16:51:41
  • 4339

性能调优(处理 sql server 死锁)

        最近在做性能测试的时候发现程序在SQL Server下有很多死锁,于是进行了一些优化工作。尽管并无法解决所有问题,但是可喜的是性能得到了量级的提升。        测试工具:winRu...
  • olony
  • olony
  • 2007-08-05 19:48:00
  • 2462

sql调优 sql调优

  • 2009年11月26日 01:29
  • 43KB
  • 下载

SQL调优简介及调优方式

在日常工作或交流中,经常会讨论一些关于sql调优的问题,然后总结了下,下面我们主要是从软件方面进行分析,希望对你有帮助:         引导语:我曾有一种感觉,不管何种调优方式,索引是最根本的方法,...
  • u011463470
  • u011463470
  • 2016-03-30 17:02:16
  • 6602
收藏助手
不良信息举报
您举报文章:SQL调优日记--并行等待的原理和问题排查
举报原因:
原因补充:

(最多只允许输入30个字)