解决简单恢复模式下产生的日志增长

简介

  最近测试服务器进行数据归档,其间程序员发现一个问题,空间不足,我查看原因发现日志文件暴涨。然后将数据库改为简单恢复模式,但是依然存在这个问题。经过查询资料发现了日志文件在简单模式下依然增加的原因。

Simple概念

  Simple恢复模式也叫做”Checkpoint with truncate log“,其实这个名字更形象,在Simple模式下,SQL Server会在每次checkpoint或backup之后自动截断log,也就是丢弃所有的闲置日志记录,仅保留用于实例启动时自动发生的instance recovery所需的少量log,这样做的好处是log文件非常小,不需要DBA去维护、备份log,但坏处也是显而易见的,就是一旦数据库出现异常,需要恢复时,最多只能恢复到上一次的备份,无法恢复到最近可用状态,因为log丢失了。

Checkpoint

CheckPoint和lazyWriter一样,都会将缓冲区内脏数据写入到磁盘,同时在简单恢复模式下截断日志;lazyWriter缓存不足的时候会触发执行,这里我们暂且不做讨论。

针对CheckPoint我请教了Careyson以后总结出以下几个触发其执行的原因:

  1. 一些Internal CheckPoint时,比如说关闭数据库实例等。
  2. 数据库完整备份或差异备份(日志备份不会触发checkpoint)。
  3. 数据库恢复模式为简单恢复模式下当日志文件使用超过70%时。
  4. CheckPoint执行的时间间隔阈值被足够多的日志记录超过。
  5. 手动执行CheckPoint。

场景描述:

  Simple模式主要用于非critical的业务,比如开发库和测试库,那么这次由于测试环境的磁盘紧张我们也都采用了简单模式。但是数据归档发生时依然产生了大量的日志,并且增加了磁盘占用,这又是什么原因那?因为我们在归档处理中使用了大量的insert和delete以及update操作,这样话,短时间内产生了大量的日志,这个时候日志迅速增加;又因为在SQL Server中,CheckPoint是一个完整的过程,这个过程的耗时取决于脏数据的大小。一旦在很短时间内,日志的CheckPoint没完成的时候日志增加超过了日志的规定上限。则将产生更多的日志。

  如上所述,产生这个问题的原因就是:CheckPoint时间间隔阈值被足够多的日志记录超过,触发CheckPoint才写入磁盘。

  下面这个实例来自于:

http://sqlblog.com/blogs/linchi_shea/archive/2009/04/12/a-race-condition-in-the-simple-recovery-mode-why-is-my-log-autogrowing.aspx

    让我们用一个脚本来实际的阐明这种行为。首先在一个测试数据库中运行一下脚本创建一个测试表并填充一些数据。

  测试数据库设置:

  1.设置为简单的恢复模式。

  2.日志的大小为100M。

  3.日志文件的自动增长被禁用(因为观察日志空间被用完的错误比检查自动增长要容易)。

--创建表并初始化数据
create table test(i int, c char(1000)) 
go
declare @i int 
set @i = 1 
while @i < 10000     --插入9999条测试数据
begin 
  insert test values(@i, 'abc')          
  set @i = @i + 1 
end

   运行以下脚本,观察资源竞争:

 set nocount on go
declare @change_size int 
set @change_size = 100 -- 根据需要来调整这个值
declare @i int 
set @i = 1 
while @i < 100 
begin 
if @i % 2 = 0 
 update test set c = replicate('a', @change_size)            
else 
  update test set c = replicate('b', @change_size)                       
select @i = @i + 1 
end 

 反复根据修改@change_size来看结果,当我将@change_size改为120甚至更大时,得到了9002的错误信息,非常准确的告诉我数据库的事务日志已满。

通过上面这个引用的例子,很好地再现了问题的产生机制,那么我们怎么处理这个情况那?

解决

  方案1:

  强制执行CheckPoint。但是执行后有个很不好的影响,严重影响了存储过程的执行时间。由此可知这样做很消耗性能啊。

  方案2:

  缩短CheckPoint时间间隔阈值。

 默认值是0,意味着由SQL Server来管理这个回复间隔。   

 也可以SQL语句实现这个功能:

  方案3:

  增大日志文件大小。

总结:

  日志文件是一个双刃剑,WAL机制很好的保证了数据的一致性和维护性。但是也产生了额外的性能和维护的成本的上升。需要我们根据实际情况去处理这些不同的情景。需要注意的是在TempDB中是不会产生日志的,除非手动执行。除此之外,并非所有的时间间隔后都会产生日志,因为当数据很少的时候有可能不触发Checkpoint执行。

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏WeTest质量开放平台团队的专栏

Linker加载so失败问题分析

原文链接:https://wetest.qq.com/lab/view/421.html

14710
来自专栏deepcc

ajax 请求中多出现一次OPTIONS请求及其解决办法

W3C规范!在跨域请求中,分为简单请求(get和部分post,post时content-type属于application/x-www-form-urlenco...

54240
来自专栏Java面试通关手册

可能是全网把 ZooKeeper 概念讲的最清楚的一篇文章

相信大家对 ZooKeeper 应该不算陌生。但是你真的了解 ZooKeeper 是个什么东西吗?如果别人/面试官让你给他讲讲 ZooKeeper 是个什么东...

23550
来自专栏数据和云

【新书连载】一波三折:释放内存导致数据库崩溃

编辑说明:《Oracle性能优化与诊断案例精选》出版以来,收到很多读者的来信和评论,我们会通过连载的形式将书中内容公布出来,希望书中内容能够帮助到更多的读者朋友...

39880
来自专栏QQ音乐前端团队专栏

【译】开始在web中使用JS Modules

15920
来自专栏个人分享

分布式系统中的线程与进程

  虽然进程构成了分布式系统中的基本组成单元,但是操作系统提供的用于构建分布式系统的进程在粒度上还是太大了,而就粒度而言,将每个进程细分为若干控制线程的形式则更...

7210
来自专栏张戈的专栏

Linux系统内存监控、性能诊断工具vmstat命令详解

最近,烦心事有点多,博客也像是进入了便秘期。虽然还远远不到说放弃的地步,但总有一种挤不出牙膏的郁闷感。很怀念前几个月的冲劲和激情,一天都能存好几篇优质草稿。 看...

38650
来自专栏祝威廉

猛犸系统

10.猛犸也支持通过分布式Shell引擎支持传统的服务器模式。并且资源模型和传统的服务器模式同时并存,解决各自擅长的问题

7920
来自专栏云计算

重新审视分布式(微服务)体系结构中的全局数据一致性

早在2015年的时候,我写了几篇文章,介绍如何通过搭载标准Java EE事务管理器以获得跨分布式服务的数据一致性(查看原文请点击这里,基于Spri...

17020
来自专栏java学习

针对java初学者以及自学者的一篇入门教程

Java基础 | 数据库 | Android | 学习视频 | 学习资料下载 最新通知 按照我去培训机构的学习经历,给初学还有自学Java 的同学一个基本的学习...

44390

扫码关注云+社区

领取腾讯云代金券