search
HomeDatabaseMysql TutorialSQLServer性能优化等待SLEEP_BPROOL_FLUSH_MySQL

前言:

有一个用于历史归档的数据库(简称历史库),经过一定时间的积累,数据文件已经达到700多GB,后来决定某些数据可以不需要保留,就把这部分数据truncate了,空余出600多GB的空间,也就是说,经过收缩后,理论上数据库只有100多G。为此,我经过重建各个表(表数量不多,但单表数量还是有几千万)的聚集索引后,准备进行收缩。

但是当收缩开始时,即使把每次收缩的范围缩小到500MB,速度也极其慢,经常几个小时都没反应。经过查看等待信息之后发现有一个SPID=18的会话(SPID

\

为此,我觉得即使是小概率事件(因为这个等待类型虽然常见,但是并不总引人注意),既然出现了,就不妨来研究一下。

说明:环境为SQL Server 2008R2

本文出处:http://blog.csdn.net/dba_huangzj/article/details/50455543

简介:

既然这已经成为了问题,那么有必要先了解一下SLEEP_BPOOL_FLUSH这个等待状态是什么。在微软官方说明中:https://technet.microsoft.com/zh-cn/library/ms179984(v=sql.105).aspx ,仅有简单的描述:当检查点为了避免磁盘子系统泛滥而中止新 I/O 的发布时出现。明显这种解释是不足的。因此我翻翻国外大牛的博客和其他书籍,总结如下:

这种等待状态与checkpoint进程有直接关系,checkpoint主要用于在内存的缓冲区(BufferPool)中,自加载到内存之后发生了数据改变(称为脏页),在checkpoint触发后把脏页从内存回写到磁盘的数据文件中。

所以很自然地想到Checkpoint。但是从行为特性来看,又意味着可能你的磁盘子系统有性能问题。

Checkpoint简介:

要了解SLEEP_BPOOL_FLUSH等待类型,有必要先了解一下Checkpoint这个东西。它是SQL Server后台触发的系统进程,也可以手动输入checkpoint来运行。

这个进程负责把缓冲区的被修改过的页写入到数据文件中。常见的地方是在备份中。这个进程的重要作用之一是加快数据库在异常情况下恢复的速度。当数据库发生故障时,SQL Server必须把数据库尽可能地还原到之前的正常状态。SQL Server会使用事务日志进行重做(redo)或回滚(undo),把未写入数据文件的修改重新附加会数据文件中。如果数据页被修改但还未写入数据文件,SQL Server必须把修改重做。如果之前已经有一次Checkpoint发生并把这些脏页写到数据文件,那么这一步就可以跳过,从而加快数据库的恢复速度。如图所示:

\

当一个数据页被事务修改后,这个修改会先被记录在事务日志中(实际上不写入LDF文件而是内存中的一块叫log buffer的区域中,然后再写到磁盘的LDF文件中,这个过程由WRITELOG和LOGBUFFER等待类型表示)。然后在内存的buffer pool中的对应数据页标识为脏页。当Checkpoint进程触发时,所有自上一次Checkpoint发生后至今的脏页都会被物理地写入磁盘的数据文件中,这个过程不会管引发脏页的事务的状态是什么(提交、未提交、回滚)。

通常来说,Checkpoint由SQL Server自动周期性运行(默认情况下为一分钟)。但是不代表真的是只有等待1分钟才触发。用户可以设置这个运行周期不过除非你确定问题的根源在此,否则不要随便修改。因为Checkpoint会自己分析当前IO请求、延时等情况进行触发。从而避免不必要的高IO开销。

在SQL Server中,有以下几种Checkpoint类型(关于Checkpoint的详细描述将在后续文章中专门介绍):

内部Checkpoint类型:不可配置,在特定情况下自动触发,比如备份。自动Checkpoint类型:如果未改动SQLServer相关配置,会在1分钟周期时触发。这种类型可以修改时间,但是这种修改是实例级别的,并且只能修改为小于等于1分钟。手动Checkpoint类型:通过SSMS或其他客户端发起checkpoint命令。这种触发可以输入一个秒数,用于指定checkpoint必须在这个秒数内完成。这种操作是库级别的。比如CHECKPOINT 10,代表SQL Server会在10秒内尝试执行checkpoint。详细内容可见:https://technet.microsoft.com/zh-cn/library/ms188748(v=sql.105).aspx间接Checkpoint类型:这是SQLServer 2012引入的库级别选项。如果这个值大于0则会覆盖特定数据库上的默认自动Checkpoint配置,可以通过下面命令实现:
ALTER DATABASE[数据库名] SET TARGET_RECOVERY_TIME = [秒数或分钟数]

前面提到过,SQL Server会分析当前系统压力,当它认为当前没必要进行Checkpoint时,会扼杀这个进程,从而避免磁盘子系统的雪上加霜。当Checkpoint被扼杀时,就会记录在SLEEP_BPOOL_FLUSH等待类型的信息中。

在正常情况下,这种等待状态应该尽可能接近0。

降低SLEEP_BPOOL_FLUSH等待:

既然有问题,那么就该解决,即使它可能通常没有多大性能问题。遇到这个问题时,建议首先检查配置,还是那句话,如无必要不要修改默认配置。可以通过下面语句查询配置值:

select * from sys.configurations where name ='recovery interval (min)'
其中“value”为0代表默认配置,这个值以分钟为单位,值越小,Checkpoint的频率就越高,越容易引发SLEEP_BPOOL_FLUSH等待。另外在事务中频繁使用CHECKPOINT命令也很容易触发这种等待。

除了这种情况之外,还有一个可能就是数据文件所在的磁盘子系统的性能问题。前面提到过,Checkpoint触发的结果是把缓冲区的脏页写入磁盘,如果当前磁盘负载非常大,那么Checkpoint操作就会被频繁扼杀,从而引起SLEEP_BPOOL_FLUSH等待。

回归主题:

前面介绍了这种等待状态的含义、原因,那么现在来看看我的问题,因为问题还是要解决。经过检查,默认配置没问题,而我在执行的操作是数据文件收缩,所以问题应该是在收缩上面。

收缩数据文件有三个潜在问题:

收缩的逻辑就是把数据移动到数据文件较前的区中,因为收缩是从数据文件的最后的区开始回收,这个操作会消耗大量的时间和系统资源用于移动所有的数据。在这个过程中,SQL Server使用大量的CPU资源去决定数据可以移动到哪里,有多少空间可以用于移动,同时要求大量的IO资源用于从数据文件中读取数据和把数据写入到新的物理地址中。另外,如果表没有聚集索引,那么非聚集索引由于叶子节点记录了RID信息,所以移动会导致非聚集索引的信息更新开销。注意是“每个非聚集索引的每一行”都受影响。不用多说都可以想象到,这是很高开销的操作。日志文件的增长:不管当前使用何种恢复模式,SQL Server都会记录每个数据移动操作,每个数据页和区的分配或回收,还有每个索引的变更。这种记录会加重前面第一个问题的系统资源开销,同时会导致日志文件的快速增大。有一位MVP的博客上介绍了数据文件收缩所需的日志文件数量:http://www.karaszi.com/SQLServer/info_dont_shrink.asp增加表和索引的碎片:需要先说明,碎片不总是坏事,因为存在就有存在的理由。有很多操作并不受碎片影响。这部分可以看微软的白皮书:https://technet.microsoft.com/en-us/library/cc966523.aspx 。里面介绍了碎片的不通类型和需要关注的碎片情景。

通过前面的分析,在查看服务器那个历史库所在的磁盘(普通SAS盘),可以初步确定是磁盘IO性能问题。因为在之前已经对所有表的聚集索引进行了重建(没有堆表),应该是数据紧密度足够高。这就是最头痛的问题,不可能因为收缩慢就说换磁盘,即使能换,财务流程也不是一般的繁琐。那么我们还是来想想怎么使得每次读写操作尽可能地小吧。 本文出处:http://blog.csdn.net/dba_huangzj/article/details/50455543

这个是一个历史库,历史库在月底(写本文的时候)会有比较多的月结类、年度结算类查询,在频繁使用的过程中收缩文件显然不合理,所以把这个操作放在闲时运行(闲时并不一定就是晚上,主要看系统类型和操作时间段)。另外,收缩的规模也要尽可能小,为了避免一大片的语句,可以用下面语句进行自动化收缩:

declare @sql nvarchar(1024)
declare @size int=758000--当前大小,MB为单位
declare @end int =1024  --停止范围
while @size>=@end  --直到达到停止范围前一直循环
begin
set @sql='DBCC SHRINKFILE (N''数据文件名'','+cast(@size as nvarchar(20))+')'
--print @sql
exec (@sql)
set @size=@size-500
end

其中注释掉的print语句用来检查将要执行的命令是否正确。这里只是抛砖引玉,读者可以根据实际情况修改或添加其他功能。另外代码倒数第二行set @size=@size-500意思是每次收缩500MB,读者也可以根据具体情况测试,可能100MB/次反而是最好最快的,那不妨设为set @size=@size-100。

通过调整每次收缩的规模、安排闲时运行,不定期手动运行Checkpoint,虽然等待状态依旧(毕竟磁盘性能是硬伤),但是收缩进度还如意。

最重要的手段还是在服务器闲时进行,在反复测试之后,晚上11点之后,服务器维护作业还未运行,而用户已经下班,此时即使每次收缩100G,也只需要1个多小时。

虽然结果有点不如意,读者可能希望看到如何彻底解决。但是毕竟是正式环境,不能轻易尝试和修改。但是除了前面的方式之外,还是有其他方式可以按需选择:

拆分数据文件,把文件移动到负荷较低或性能较高的磁盘。不过这个操作要考虑数据后期合并。某些库是可以短暂脱机的,可以把数据库移动到性能较好的盘然后附加再进行收缩。其实。。。不收缩未尝不是件好事。

总结:

SLEEP_BPOOL_FLUSH等待跟SQL Server的Checkpoint进程有密切关系,而Checkpoint主要负责的是把脏页写入磁盘。在Checkpoint触发前,SQL Server会分析服务器当前负载,如果磁盘子系统压力过大导致Checkpoint被认为必须扼杀时,SQL Server会把这种状态记录到SLEEP_BPOOL_FLUSH等待状态中。

在一个正常的系统中,这种等待状态的等待时间不应该很长,但是它还是有可能影响系统性能。过于频繁地运行CHECKPOINT命令或把“recovery interval”的值设的过低,都可能引发SLEEP_BPOOL_FLUSH等待。数据文件的磁盘子系统性能过低也同样会引发这种等待信息。

因此,在发现这种等待状态频繁出现或等待时间很长时,需要检查SQL Server配置、语句及磁盘子系统。

Statement
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
如何优化Java开发中的文件压缩解压性能如何优化Java开发中的文件压缩解压性能Jul 01, 2023 am 11:54 AM

如何优化Java开发中的文件压缩解压性能随着互联网技术的不断发展,文件传输和存储成为我们日常开发中经常遇到的需求。为了减小网络传输的带宽消耗和文件存储的空间占用,我们通常需要对文件进行压缩。在Java开发中,常用的文件压缩格式有ZIP和GZIP。本文将介绍如何优化Java开发中的文件压缩解压性能,帮助提高效率。一、合理选择压缩算法在Java开发中,进行文件压

电脑性能看哪些方面电脑性能看哪些方面Dec 23, 2020 pm 01:54 PM

电脑性能看如下几个方面:1、电脑安装的操作系统的版本;2、电脑所配置的处理器类型;3、电脑安装的内存大小;4、操作系统是32位的还是64位的。

Java开发中如何优化字符串查找性能Java开发中如何优化字符串查找性能Jun 29, 2023 am 11:12 AM

在Java开发中,字符串查找是一个常见且关键的操作。无论是在文本处理、数据分析还是系统日志分析等应用场景中,字符串的查找性能都对程序的整体性能有着重要影响。因此,如何优化字符串查找性能成为了Java开发中不可忽视的问题。一、使用indexOf()方法代替contains()方法在字符串查找中,Java提供了两个常用的方法:indexOf()和contains

Vue3中的lazy函数详解:懒加载组件提高应用性能Vue3中的lazy函数详解:懒加载组件提高应用性能Jun 19, 2023 am 08:39 AM

Vue3是一款流行的JavaScript框架,它具有易学易用、高效稳定的特点,尤其擅长构建单页应用程序(SPA)。Vue3中的lazy函数,作为懒加载组件的利器之一,可以很大程度上提高应用程序的性能。本文将详解Vue3中的lazy函数的使用方法与原理,以及它在实际开发中的应用场景和优点。什么是懒加载?在传统的前后端分离的开发中,前端开发人员往往需要处理大量的

Java随机数生成性能优化方法Java随机数生成性能优化方法Jun 30, 2023 pm 12:25 PM

如何优化Java开发中的随机数生成性能随机数在计算机科学中有广泛的应用,特别是在密码学、模拟、游戏等领域。在Java开发中,我们常常需要生成随机数来满足各种需求。然而,随机数生成的性能通常是开发者关注的问题之一。本文将探讨如何优化Java开发中的随机数生成性能。使用ThreadLocalRandom类在Java7中引入了ThreadLocalRandom类

如何通过设置MySQL缓存来提高性能如何通过设置MySQL缓存来提高性能May 11, 2023 am 08:09 AM

MySQL是一种常用的关系型数据库管理系统(RDBMS),在各种应用场景下都得到广泛的应用。然而,在高并发、大数据量的情况下,MySQL数据库的性能受到挑战,特别是在读写操作频繁的场景下,容易出现性能瓶颈。为了提高MySQL数据库的性能,可以通过设置MySQL缓存来减少数据库的IO操作,从而提高MySQL的查询效率。在本文中,我们将介绍如何通过设置MySQL

自动驾驶决策规划技术详解自动驾驶决策规划技术详解Apr 04, 2023 pm 02:35 PM

随着深度强化学习技术的快速发展,越来越多的研究团队开始将其应用于自动驾驶决策规划中,将行为决策与运动规划模块相融合,直接学习得到行驶轨迹。 自动驾驶中的决策规划模块是衡量和评价自动驾驶能力最核心的指标之一,它的主要任务是在接收到传感器的各种感知信息之后,对当前环境作出分析,然后对底层控制模块下达指令。典型的决策规划模块可以分为三个层次:全局路径规划、行为决策、运动规划。01 引言在一套完整的自动驾驶系统中,如果将感知模块比作人的眼睛和耳朵,那么决策规划就是自动驾驶的大脑。大脑在接收到传感器的各种

一篇学会本地知识库对LLM的性能优化一篇学会本地知识库对LLM的性能优化Jun 12, 2023 am 09:23 AM

昨天一个跑了220个小时的微调训练完成了,主要任务是想在CHATGLM-6B上微调出一个能够较为精确的诊断数据库错误信息的对话模型来。不过这个等了将近十天的训练最后的结果令人失望,比起我之前做的一个样本覆盖更小的训练来,差的还是挺大的。这样的结果还是有点令人失望的,这个模型基本上是没有实用价值的。看样子需要重新调整参数与训练集,再做一次训练。大语言模型的训练是一场军备竞赛,没有好的装备是玩不起来的。看样子我们也必须要升级一下实验室的装备了,否则没有几个十天可以浪费。从最近的几次失败的微调训练来看

See all articles

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

Repo: How To Revive Teammates
1 months agoBy尊渡假赌尊渡假赌尊渡假赌
R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks agoBy尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
1 months agoBy尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Safe Exam Browser

Safe Exam Browser

Safe Exam Browser is a secure browser environment for taking online exams securely. This software turns any computer into a secure workstation. It controls access to any utility and prevents students from using unauthorized resources.

PhpStorm Mac version

PhpStorm Mac version

The latest (2018.2.1) professional PHP integrated development tool

MinGW - Minimalist GNU for Windows

MinGW - Minimalist GNU for Windows

This project is in the process of being migrated to osdn.net/projects/mingw, you can continue to follow us there. MinGW: A native Windows port of the GNU Compiler Collection (GCC), freely distributable import libraries and header files for building native Windows applications; includes extensions to the MSVC runtime to support C99 functionality. All MinGW software can run on 64-bit Windows platforms.

WebStorm Mac version

WebStorm Mac version

Useful JavaScript development tools

mPDF

mPDF

mPDF is a PHP library that can generate PDF files from UTF-8 encoded HTML. The original author, Ian Back, wrote mPDF to output PDF files "on the fly" from his website and handle different languages. It is slower than original scripts like HTML2FPDF and produces larger files when using Unicode fonts, but supports CSS styles etc. and has a lot of enhancements. Supports almost all languages, including RTL (Arabic and Hebrew) and CJK (Chinese, Japanese and Korean). Supports nested block-level elements (such as P, DIV),