数据库压缩备份提高备份效率

背景

    在数据库的备份过程中有很多参数，前几日发现公司的备份数据库job运行的很慢，就去研究了一下，发现在备份程序中都没有启用压缩，加上压缩以后有发现效率提高了不少，本篇就几个压缩相关的参数来看一下备份数据库的过程中如何提高备份的效率。

代码实现

    为了更好地了解数据库备份，我们首先要知道代码以及参数的含义。

普通代码：

BACKUP DATABASE ‘备份数据库名称’ 
TO DISK = '地址+bak文件名称' ;

加快备份进度的几个关键参数：

1.BUFFERCOUNT--指定用于备份操作的 I/O 缓冲区总数。可以指定任何正整数

2.MAXTRANSFERSIZE--指定要在 SQL Server 和备份介质之间使用的最大传输单位。可能的值是 64 KB的倍数，最大 4 MB。

3.COMPRESSION--压缩备份选项。

4.CHECKSUM--验证数据选项。

5.启用多个备份集。

这四个中CHECKSUM是用来保证数据安全的这里我们不做讨论，由于一般备份也不会用到多个备份集，这里我们主要讨论前三个带来的影响，当然对于数据量级比较大的可以采用多备份集，尤其不同磁盘的备份集将会大大提高效率。

具体对比：

首先是常规没有压缩的情况下的备份如图1：

image

图1

数据页数282295，总共花费了32.147秒。接下来我们看一下当加了压缩等参数后的表现。

增加压缩参数的情况如图2：

image

图2

页数接近，花费的时间是19.524秒，单位备份速度大幅提升到112.957/s。

添加了COMPRESSION、MAXTRANSFERSIZE、BUFFERCOUNT三个参数的：

image

图3

这个就快了不少，花费了17.6秒，写入速度为125.228M/s。

适当缩小MAXTRANSFERSIZE、BUFFERCOUNT，如图4：

image

图4

如图所示，缩小MAXTRANSFERSIZE、BUFFERCOUNT后相应的写入速度略微下降。时间也略微提高。

大幅提升BUFFERCOUNT的数值，能够提升写入速度。

image

图5

缓冲区使用的总计空间由下面公式确定：buffercount*maxtransfersize，这里需要知道该公式结果不能大于分配内存，所以要注意服务器的最大内存以免内存溢出。

除了以上几个条件，我也做了几个相关的其他条件的修改来适应不同的备份数据，例如数据验证CHECKSUM等都会增大备份数据的时间。

注意：

1> 通过压缩前后文件的大小对比可知道到压缩比率。

2> 对已启用压缩的数据库进行压缩备份,CPU消耗会变得更高

压缩主要因素包括：

1.数据类型。字符数据的压缩率要高于其他类型的数据。

2.数据重复的比例越高压缩越好，类似于数据库压缩（页压缩）。

通常，如果某页包含多个行，而其中的某个字段包含相同的值，则该值可获得较大的压缩。相反，对于包含随机数据或者每页只有一个很大的行的数据库，压缩备份的大小几乎与未压缩的备份相同。

总结：

    不难发现，以上主要测试三个数据，在合理外围内越大越能提高效率。同时经过研究还发现，备份压缩后，还原的效率也会提高。

COMPRESSION、MAXTRANSFERSIZE、BUFFERCOUNT配合服务器的性能就能大幅提高备份效率。优点是显而易见的对于备份时间，备份文件大小都有提高。