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.整理磁盘碎片,另外合并&删除日志表的索引减少写入开销也能起到一定作用
 

版权声明:本文为博主原创文章,未经博主允许不得转载。

MySQL性能调优——锁定机制与锁优化分析

——针对多线程的并发访问,任何一个数据库都有其锁定机制,它的优劣直接关系着数据的一致完整性与数据库系统的高并发处理性能。锁定机制也因此成了各种数据库的核心技术之一。不同数据库存储引擎的锁定机制是不同的...
  • zhangliangzi
  • zhangliangzi
  • 2016年05月16日 20:36
  • 2088

队列应用银行排队问题模拟:计算客户的平均停留时间和等待时间以及每个客户的时间信息,两种方法实现

一、数据类型 首先需要两个数据结构:一个是有序事件链表,一个是队列。 1、事件链表 存储客户事件,包括到达事件和离开事件,其中到达事件的事件类型为0,1号窗口的离开事件类型为1,二号窗口的离开事...
  • meiyubaihe
  • meiyubaihe
  • 2014年06月03日 15:47
  • 4338

最优服务次序问题-贪心算法

1、最优服务次序问题 (1)问题描述:   设有n 个顾客同时等待一项服务。顾客i需要的服务时间为ti, 1 (2)编程任务:   对于给定的n个顾客需要的服务时间,编程计算最优服务次序。 ...
  • Heated_Youth
  • Heated_Youth
  • 2016年11月26日 13:47
  • 1948

SQL Server DBA调优日记(一)——大数据量查询记录数优化及原理探讨

SQL Server DBA调优日记第一篇,大数据量查询记录数优化及原理探讨。现象、解决、模拟、原理探讨。...
  • Wentasy
  • Wentasy
  • 2014年04月06日 17:12
  • 9947

SQL调优日记--sleeping进程导致的性能问题

在数据库出现的大量的问题中,阻塞占据在很大的比例。而所有的阻塞中,sleeping进程造成阻塞是非常严重但是,能够完全搞明白的人,并不多。我的很多客户对于sleeping 的由来,和他可能导致的问题都...
  • z10843087
  • z10843087
  • 2017年07月18日 10:41
  • 1161

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

前言 这篇文章花了Inside君整整3个晚上的时间,码了3000多字,可谓满满的诚意之作,献给最有品味的你们。然由于微信公众账号对于技术博文的支持并不是很友好,对于有代码洁癖的小...
  • lb520999
  • lb520999
  • 2015年10月08日 17:11
  • 1300

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

MySQL 5.7并行复制实现原理与调优
  • shaochenshuo
  • shaochenshuo
  • 2015年09月17日 16:44
  • 595

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
  • 1674

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

原文:http://www.innomysql.net/article/16317.html MySQL 5.7并行复制时代 众所周知,MySQL的复制延迟是一直被诟病的问题之一,...
  • qiuyepiaoling
  • qiuyepiaoling
  • 2016年01月05日 16:37
  • 352

SQL调优(SQL TUNING)并行查询提示(Hints)之pq_distribute的使用

本文介绍了Oracle中pq_distribute提示的使用,记录于此,供同行及自己今后参考或学习。...
  • LHDZ_BJ
  • LHDZ_BJ
  • 2017年01月29日 13:12
  • 644
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:SQL调优日记--并行等待的原理和问题排查
举报原因:
原因补充:

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