SQL Server 이야기

SQL Server 2008 데이터베이스 백업 압축(Backup Compression)

늘푸르른나 2010. 7. 5. 19:11

점점 데이터베이스의 크기가 커져서 수백GB의 크기는 일반화된지 오래이고 수TB에 이르는 데이터베이스가 사용되고 있는 것이 현실입니다. 데이터베이스가 이렇게 커졌음에도 불구하고 물리적인 메모리의 증가와 적절한 인덱스 튜닝 등의 작업으로 인해 이를 사용하는 쿼리문의 성능은 과거에 비해 나빠지지는 않았으며 대용량 데이터베이스를 사용하는 데 큰 무리가 없는 것이 현실입니다.

 

하지만, 관리 측면에서 보면 데이터베이스의 크기가 커진다는 것은 점점 부담이 커지게 됨을 의미합니다. 특히 데이터베이스를 백업(Backup)하거나 복원(Restore)할 때 몇 시간 이상이 소요되는 것은 자주 백업을 받아 두기 힘들게 만들뿐만 아니라 문제가 발생하여 복원이 필요할 때 서비스의 정상화를 지연시키는 요인이 되기도 합니다.

 

SQL Server 2008에서는 이런 문제점을 완화하기 위해 백업 압축(Backup Compression) 기능이 추가되었습니다. 데이터베이스를 백업할 때 압축하게 되면 백업 파일의 크기가 줄어드는 것뿐만 아니라 디스크 I/O를 감소시켜 백업 시간을 단축시켜 주는 효과를 가져올 수 있습니다. 압축된 백업 파일은 복원(Restore) 시에도 디스크 I/O를 줄여 주기 때문에 그만큼 복원 시간을 단축시켜 줍니다. 실제로 백업이나 복원 시에 대부분의 시간을 차지하는 것은 디스크 I/O이기 때문에 백업 압축으로 인해서 가져오는 디스크 I/O의 감소는 큰 효과를 가져오게 되는 것입니다.

 

하지만, 백업 압축 기능으로 인해 얻는 이익만 있는 것은 아닙니다. 반대급부로 백업 압축을 사용하게 되면 백업이나 복원 시 CPU 사용량이 증가하게 되는데 점점 CPU의 처리 능력이 좋아지는 추세이고 백업이나 복원을 수행하는 시간이 사용자가 많은 시간대는 피하는 것이 일반적이므로 큰 단점이 되지는 않는다 할 수 있습니다. 만약, 백업 압축으로 인해 증가하는 CPU 사용량이 SQL Server의 성능에 악영향을 미친다면 리소스 관리자(Resource Governor) 설정을 통해 백업을 수행하는 세션에서 사용하는 CPU를 제한하는 방법을 고려해 볼 수도 있습니다.

 

백업 압축 기능은 SQL Server 2008 Enterprise Edition에서만 지원됩니다(SQL Server 2008 R2에서는 Standard Edition에서도 지원됨). 하지만, Enterprise Edition에서 압축된 백업 파일은 SQL Server 2008의 모든 Edition에서 복원(Restore)이 가능합니다.

 

SQL Server 2008에서 백업 압축 작업은 여러 개의 작업 스레드(Worker Thread)에서 병렬로 처리되며 SOS Scheduler의 갯수(일반적으로 CPU의 갯수와 일치함) 만큼의 작업 스레드가 사용될 수 있습니다. 압축 작업은 데이터베이스로부터 읽어 들여 채워진 백업 버퍼(Backup Buffer)에 대해서 수행되고 압축된 내용은 다시 백업 버퍼로 저장되었다가 최종적으로 디스크(또는, 테이프)로 저장됩니다. SQL Server 2008에서 백업 압축을 위해 사용하는 알고리즘은 LZ77으로 WINZIP이나 WINRAR 등에서도 널리 사용되는 알고리즘입니다.

 

SQL Server 2008에서 백업 압축 기능을 사용하는 방법은 서버 수준에서 서버 구성 옵션 설정을 통해 모든 백업이 압축되도록 설정하는 방법과 데이터베이스 수준에서 BACKUP 문 실행시 'WITH COMPRESSION' 옵션을 지정하는 방법이 있습니다. SQL Server 2008 설치시 서버 수준에서는 기본적으로 백업 압축 기능이 비활성화되어 있습니다.

 

다음의 명령을 실행함으로써 서버 수준에서 백업 압축 기능을 활성화할 수 있습니다. 이렇게 서버 수준에서 백업 압축 기능을 활성화하게 되면 이후 수행되는 모든 백업(트랜잭션 로그 백업 포함)은 자동적으로 압축됩니다. 

use master
go
sp_configure 'backup compression default', 1
reconfigure with override
go

 

만약 서버 수준에서 설정된 백업 압축 기능을 무시하고 특정 데이터베이스에 대해서는 압축 없이 백업을 수행하려면 다음과 같이 옵션을 지정하여 백업 명령을 실행해 주면 됩니다. 

backup database test to disk='c:\backup\test.bak' with no_compression

go

 

명령 대신에 SQL Server Management Studio를 통해 백업을 수행할 경우에는 다음의 화면과 같이 '옵션' 페이지에서 백업 압축 설정 옵션을 '백업 압축 안 함'으로 설정해 주면 서버 수준에서 설정한 백업 압축 기능을 무시하고 압축 없이 백업을 수행할 수 있습니다. 

 

서버 수준에서 백업 압축 기능을 활성화하여 사용하는 대신에 다음과 같이 옵션을 지정하여 백업 명령을 실행함으로써 데이터베이스 수준에서 백업 압축 여부를 지정할 수도 있습니다. 

backup database test to disk='c:\backup\test.bak' with compression

go

 

명령 대신에 SQL Server Management Studio를 통해 백업을 수행할 경우에는 다음의 화면과 같이 '옵션' 페이지에서 백업 압축 설정 옵션을 '백업 압축'으로 설정해 주면 백업 압축을 수행할 수 있습니다. 

 

테스트 목적으로 약 225MB 크기의 데이터베이스에 대해서 압축하여 백업을 받았을 때와 압축 없이 백업을 받았을 때의 처리 속도를 비교해 보면 다음과 같습니다. 이로부터 압축하여 백업을 받았을 때 많은 시간 단축이 있음을 알 수 있으며 데이터베이스의 크기가 클 경우 그 효과는 더욱 클 것임을 어렵지 않게 짐작할 수 있습니다.

[압축시] 

파일 1에서 데이터베이스 'test', 파일 'test'에 대해 28056개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'test', 파일 'test_log'에 대해 1개의 페이지를 처리했습니다 .
BACKUP DATABASE이(가) 28057개의 페이지를 9.360초 동안 처리했습니다(23.418MB/초).

[비압축시] 

파일 1에서 데이터베이스 'test', 파일 'test'에 대해 28056개의 페이지를 처리했습니다 .
파일 1에서 데이터베이스 'test', 파일 'test_log'에 대해 1개의 페이지를 처리했습니다 .
BACKUP DATABASE이(가) 28057개의 페이지를 22.219초 동안 처리했습니다(9.865MB/초).

 

백업 압축시 압축 비율은 데이터베이스에 저장되어 있는 실제 데이터에 따라 크게 달라질 수 있습니다. 압축 비율에 영향을 미치는 요소는 다양한데 그중 다음의 요소들이 큰 영향을 미칩니다.

  • 데이터의 형식
    문자 데이터는 다른 형식의 데이터보다 압축률이 높습니다.

  • 페이지의 행에 포함된 데이터의 일관성
    일반적으로 한 페이지에 필드의 값이 같은 행이 여러 개 있는 경우 이 값에 상당한 압축이 발생할 수 있습니다. 반면 임의의 데이터가 들어 있거나 페이지당 하나의 큰 행만 들어 있는 데이터베이스의 경우 압축된 백업의 크기가 압축되지 않은 백업의 크기와 거의 같습니다.

  • 데이터의 암호화 여부
    암호화된 데이터는 암호화되지 않은 데이터보다 압축률이 크게 낮습니다. TDE(Tranparent Data Encryption, 투명한 데이터 암호화)를 사용하여 전체 데이터베이스를 암호화할 경우 백업을 압축해도 크기가 별로 줄어들지 않거나 그대로일 수 있습니다.

  • 데이터베이스의 압축 여부
    행 압축(Row Compression), 페이지 압축(Page Compression)을 통해 데이터베이스가 압축된 경우 백업을 압축하면 크기가 줄어들더라도 많이 줄어들지 않을 수 있습니다.
  •  

    백업 압축 비율은 msdb의 backupset 시스템 테이블로부터 확인해 볼 수 있는데 backup_size 칼럼은 데이터베이스의 크기(전체 크기가 아니라 백업의 대상이 되는 크기)를, compressed_backup_size 칼럼은 압축하여 디스크에 저장된 백업의 크기를 각각 나타내는 바이트 단위의 값입니다. 다음과 같은 쿼리문을 실행함으로써 압축 비율을 확인해 볼 수 있습니다. 예를 들어, 다음의 쿼리 결과로 압축 비율이 10%로 확인되었다면 백업 압축을 통해 백업 파일의 크기가 10% 수준으로(90%가 감소함) 줄어들었음을 알 수 있습니다. 

    select compressed_backup_size/backup_size*100 as [Compression Ratio(%)] from msdb..backupset

    go 

     

    msdb의 시스템 테이블에 의존하지 않고 백업 파일로부터 직접 백업 압축 비율을 확인해 볼 수도 있습니다. 다음과 같이 명령을 실행하면 그 결과로부터 'BackupSize'와 'CompressedBackupSize'를 확인할 수 있습니다. 

    restore headeronly from disk='c:\backup\test.bak'

    go

     

    끝으로, 백업 파일이 압축된 백업인지 여부는 msdb의 시스템 테이블로부터 확인하는 방법과 백업 파일로부터 직접 확인하는 방법이 있습니다. 다음과 같은 쿼리문을 실행함으로써 압축된 백업인지 여부를 msdb의 시스템 테이블을 통해 확인해 볼 수 있는데 쿼리 결과에서 'is_compressed' 칼럼 값이 1이면 압축된 백업임을 의미합니다. 

    select * from msdb..backupmediaset

    go

     

    백업 파일에 대해서는 다음과 같은 명령을 실행함으로써 압축 여부를 확인해 볼 수 있는데 'IsCompressed' 값이 1일 경우 압축된 백업임을 의미합니다. 

    restore labelonly from disk='c:\backup\test.bak'

    go