站内搜索

SQLServer性能优化等待SLEEP_BPROOL_FLUSH_MySQL

前言:

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

但是当收缩开始时,即使把每次收缩的范围缩小到500MB,速度也极其慢,经常几个小时都没反应。经过查看等待信息之后发现有一个SPID=18的会话(SPID<=50的均为系统会话)一直显示等待状态为“SLEEP_BPROOL_FLUSH”,并且阻塞了收缩操作。

/

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

说明:环境为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  --直到达到停止范围前一直循环beginset @sql='DBCC SHRINKFILE (N''数据文件名'','+cast(@size as nvarchar(20))+')'--print @sqlexec (@sql)set @size=@size-500end

其中注释掉的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配置、语句及磁盘子系统。

  • 上一篇:详解MySQL的用户密码过期功能_MySQL
  • 下一篇:详解MySQL中的死锁情况以及对死锁的处理方法_MySQL