MS SQL Server: 강력한 백업

기다리다! 기다리다! 사실, 이것은 SQL Server 백업 유형에 대한 또 다른 기사가 아닙니다. 복구 모델의 차이점과 너무 자란 로그를 처리하는 방법에 대해서도 이야기하지 않겠습니다.

아마도 (아마도) 이 게시물을 읽은 후 표준 수단을 사용하여 제거된 백업이 내일 밤 1.5배 더 빠르게 제거되는지 확인할 수 있을 것입니다. 그리고 BACKUP DATABASE 매개변수를 조금 더 많이 사용하기 때문입니다.

글의 내용이 너무 뻔했다면 죄송합니다. 나는 "habr SQL Server 백업"이라는 문구에 대해 Google에서 찾은 모든 것을 읽었으며 단일 기사에서 백업 시간이 매개 변수를 사용하여 어떻게든 영향을 받을 수 있다는 사실에 대한 언급을 찾지 못했습니다.

Alexander Gladchenko의 의견에 즉시 주목하겠습니다.@mssqlhelp):

프로덕션에서는 BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE 매개변수를 변경하지 마십시오. 그러한 기사를 쓰기 위해서만 만들어졌습니다. 실제로는 메모리 문제를 즉시 제거할 수 있습니다.

물론 가장 똑똑하고 독점적인 콘텐츠를 게시하는 것은 멋진 일이지만 안타깝게도 그렇지 않습니다. 이 주제에 관한 영어 및 러시아어 기사/게시물이 모두 있습니다(저는 항상 이를 올바르게 불러야 할지 혼란스럽습니다). 내가 만난 것 중 일부는 다음과 같습니다. 시간, два, XNUMX개(sql.ru에서).

그래서 우선 다음에서 약간 단순화된 BACKUP 구문을 첨부하겠습니다. MSDN (그런데 위에서 BACKUP DATABASE에 대해 썼지만 이 모든 내용은 트랜잭션 로그 백업과 차등 백업 모두에 적용되지만 효과는 덜 분명할 수 있습니다.)

BACKUP DATABASE { database_name | @database_name_var }
  TO <backup_device> [ ,...n ]
  <...>
  [ WITH { <...>
           | <general_WITH_options> [ ,...n ] } ]
[;]

<general_WITH_options> [ ,...n ]::=
<...>
--Media Set Options
 <...>
 | BLOCKSIZE = { blocksize | @blocksize_variable }

--Data Transfer Options
   BUFFERCOUNT = { buffercount | @buffercount_variable }
 | MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }
<...>

<…> - 뭔가가 있었다는 뜻인데, 지금은 주제와 관련이 없어 삭제했습니다.

보통 어떻게 백업을 하시나요? 수십억 개의 기사에서 백업을 수행하는 방법을 어떻게 "가르치"나요? 일반적으로 아주 크지 않은 일부 데이터베이스를 일회성 백업해야 하는 경우 자동으로 다음과 같은 내용을 작성합니다.

BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнее

그리고 일반적으로 백업에 대한 기사에서 일반적으로 언급되는 모든 매개변수의 75-90%가 여기에 나열되어 있습니다. 음, INIT, SKIP도 있습니다. MSDN을 방문해 보셨나요? 화면 XNUMX개에 대한 옵션이 있다는 것을 보셨나요? 나도 봤는데...

아마도 첫 번째 코드 블록에 남아 있는 세 가지 매개변수인 BLOCKSIZE, BUFFERCOUNT 및 MAXTRANSFERSIZE에 대해 더 자세히 설명할 것임을 이미 알고 계실 것입니다. MSDN의 설명은 다음과 같습니다.

블록 크기 = { 블록 크기 | @ 블록 크기_변수 } - 물리적 블록 크기를 바이트 단위로 나타냅니다. 지원되는 크기는 512, 1024, 2048, 4096, 8192, 16, 384 및 32바이트(768KB)입니다. 기본값은 테이프 장치의 경우 65이고 기타 장치의 경우 536입니다. 일반적으로 BACKUP 문이 장치에 적합한 블록 크기를 자동으로 선택하므로 이 매개변수는 필요하지 않습니다. 블록 크기를 설정하면 자동 블록 크기 선택이 명시적으로 무시됩니다.

버퍼카운트 = { 버퍼 수 | @ buffercount_변수 } - 백업 작업에 사용될 총 I/O 버퍼 수를 정의합니다. 양의 정수 값을 지정할 수 있지만 버퍼 수가 많으면 Sqlservr.exe 프로세스의 과도한 가상 주소 공간으로 인해 메모리 부족 오류가 발생할 수 있습니다.

버퍼가 사용하는 총 공간량은 다음 공식으로 결정됩니다. BUFFERCOUNT * MAXTRANSFERSIZE.

최대 전송 크기 = { 최대 전송 크기 | @ 최대 전송 크기_변수 }는 SQL Server와 백업 세트 미디어 간에 교환할 최대 데이터 패킷 크기(바이트)를 지정합니다. 65바이트(536KB)부터 최대 64바이트(4MB)의 배수가 지원됩니다.

맹세합니다. 이전에 이 내용을 읽어본 적이 있지만 그것이 생산성에 얼마나 많은 영향을 미칠 수 있는지는 전혀 생각해 본 적이 없습니다. 더욱이, 나는 일종의 "커밍 아웃"을해야하고 지금도 그들이 정확히 무엇을하고 있는지 완전히 이해하지 못한다는 것을 인정해야합니다. 아마도 버퍼링된 I/O 및 하드 드라이브 작업에 대해 더 읽어야 할 것 같습니다. 언젠가는 이 작업을 수행하겠지만 지금은 이러한 값이 백업 수행 속도에 어떤 영향을 미치는지 확인하는 스크립트를 작성하면 됩니다.

10GB 정도의 작은 데이터베이스를 만들어 SSD에 넣고, 백업용 디렉토리는 HDD에 두었습니다.

결과를 저장하기 위해 임시 테이블을 만듭니다(임시 테이블이 없으므로 결과를 더 자세히 살펴볼 수 있지만 스스로 결정합니다).

DROP TABLE IF EXISTS ##bt_results; 

CREATE TABLE ##bt_results (
    id              int IDENTITY (1, 1) PRIMARY KEY,
    start_date      datetime NOT NULL,
    finish_date     datetime NOT NULL,
    backup_size     bigint NOT NULL,
    compressed_size bigint,
    block_size      int,
    buffer_count    int,
    transfer_size   int
);

스크립트의 원리는 간단합니다. 각각 하나의 매개 변수 값을 변경하는 중첩 루프, BACKUP 명령에 이러한 매개 변수 삽입, msdb.dbo.backupset의 기록과 함께 마지막 레코드 저장, 백업 파일 삭제 및 다음 반복입니다. . 백업 실행 데이터는 백업 세트에서 가져오기 때문에 정확도가 다소 떨어지지만(초 단위는 없음) 이 정도는 견딜 수 있습니다.

먼저 백업을 삭제하려면 xp_cmdshell을 활성화해야 합니다(그런 다음 필요하지 않은 경우 비활성화하는 것을 잊지 마십시오).

EXEC sp_configure 'show advanced options', 1;  
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;  
GO

글쎄 사실:

DECLARE @tmplt AS nvarchar(max) = N'
BACKUP DATABASE [bt]
TO DISK = ''D:SQLServerbackupbt.bak''
WITH 
    COMPRESSION,
    BLOCKSIZE = {bs},
    BUFFERCOUNT = {bc},
    MAXTRANSFERSIZE = {ts}';

DECLARE @sql AS nvarchar(max);

/* BLOCKSIZE values */
DECLARE @bs     int = 4096, 
        @max_bs int = 65536;

/* BUFFERCOUNT values */
DECLARE @bc     int = 7,
        @min_bc int = 7,
        @max_bc int = 800;

/* MAXTRANSFERSIZE values */
DECLARE @ts     int = 524288,   --512KB, default = 1024KB
        @min_ts int = 524288,
        @max_ts int = 4194304;  --4MB

SELECT TOP 1 
    @bs = COALESCE (block_size, 4096), 
    @bc = COALESCE (buffer_count, 7), 
    @ts = COALESCE (transfer_size, 524288)
FROM ##bt_results
ORDER BY id DESC;

WHILE (@bs <= @max_bs)
BEGIN
    WHILE (@bc <= @max_bc)
    BEGIN       
        WHILE (@ts <= @max_ts)
        BEGIN
            SET @sql = REPLACE (REPLACE (REPLACE(@tmplt, N'{bs}', CAST(@bs AS nvarchar(50))), N'{bc}', CAST (@bc AS nvarchar(50))), N'{ts}', CAST (@ts AS nvarchar(50)));

            EXEC (@sql);

            INSERT INTO ##bt_results (start_date, finish_date, backup_size, compressed_size, block_size, buffer_count, transfer_size)
            SELECT TOP 1 backup_start_date, backup_finish_date, backup_size, compressed_backup_size,  @bs, @bc, @ts 
            FROM msdb.dbo.backupset
            ORDER BY backup_set_id DESC;

            EXEC xp_cmdshell 'del "D:SQLServerbackupbt.bak"', no_output;

            SET @ts += @ts;
        END
        
        SET @bc += @bc;
        SET @ts = @min_ts;

        WAITFOR DELAY '00:00:05';
    END

    SET @bs += @bs;
    SET @bc = @min_bc;
    SET @ts = @min_ts;
END

여기서 무슨 일이 일어나고 있는지 갑자기 설명이 필요하면 댓글이나 PM을 작성하세요. 지금은 BACKUP DATABASE에 넣은 매개변수에 대해서만 말씀드리겠습니다.

BLOCKSIZE의 경우 "닫힌" 값 목록이 있으며 BLOCKSIZE < 4KB로 백업을 수행하지 않았습니다. MAXTRANSFERSIZE는 64KB의 배수(64KB에서 4MB까지)인 모든 숫자입니다. 내 시스템의 기본값은 1024KB이며 512 - 1024 - 2048 - 4096을 사용했습니다.

BUFFERCOUNT를 사용하면 더 어려웠습니다. 양수일 수 있지만 링크에는 다음과 같이 나와 있습니다. BACKUP DATABASE에서 계산하는 방법과 큰 값이 위험한 이유. 또한 백업이 실제로 어떤 BUFFERCOUNT로 만들어졌는지에 대한 정보를 얻는 방법도 나와 있습니다. 저에게는 7입니다. 이를 줄일 필요가 없었고 상한선은 실험적으로 발견되었습니다. BUFFERCOUNT = 896 및 MAXTRANSFERSIZE = 4194304로 백업이 떨어졌습니다. 오류(위 링크에 기록되어 있음):

메시지 3013, 수준 16, 상태 1, 줄 7 BACKUP DATABASE가 비정상적으로 종료됩니다.

메시지 701, 수준 17, 상태 123, 줄 7 이 쿼리를 실행하기에는 '기본' 리소스 풀의 시스템 메모리가 부족합니다.

비교를 위해 먼저 매개변수를 전혀 지정하지 않고 백업을 실행한 결과를 보여드리겠습니다.

BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;

음, 백업 및 백업:

파일 1070072의 데이터베이스 'bt', 파일 'bt'에 대해 1 페이지를 처리했습니다.

파일 2의 데이터베이스 'bt', 파일 'bt_log'에 대해 1페이지를 처리했습니다.

BACKUP DATABASE는 1070074초(53.171MB/초) 만에 157.227페이지를 성공적으로 처리했습니다.

매개 변수를 테스트하는 스크립트 자체는 몇 시간 만에 작동했으며 모든 측정은 구글 스프레드시트. 그리고 여기에 세 가지 최고의 실행 시간이 포함된 결과가 있습니다. (좋은 그래프를 만들려고 노력했지만 게시물에서는 표로 처리해야 하고 댓글에서는 @mixsture 추가 된 아주 멋진 그래픽).

SELECT TOP 7 WITH TIES 
    compressed_size, 
    block_size, 
    buffer_count, 
    transfer_size,
    DATEDIFF(SECOND, start_date, finish_date) AS backup_time_sec
FROM ##bt_results
ORDER BY backup_time_sec ASC;

주의, 매우 중요한 메모입니다. @mixsture 으로 의견:

이러한 값 범위 내에서 매개변수와 백업 속도 간의 관계는 무작위이며 패턴이 없다고 자신있게 말할 수 있습니다. 하지만 내장된 매개변수에서 벗어나는 것은 분명히 결과에 좋은 ​​영향을 미쳤습니다.

저것들. 표준 BACKUP 매개변수를 관리함으로써만 백업 제거 시간이 2배 증가했습니다. 초기에는 26초였지만 53초였습니다. 나쁘지 않죠? 하지만 우리는 복원 과정에서 어떤 일이 일어나는지 지켜봐야 합니다. 이제 복구하는 데 4배 더 오래 걸리면 어떻게 되나요?

먼저 기본 설정으로 백업을 복원하는 데 걸리는 시간을 측정해 보겠습니다.

RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;

글쎄, 당신은 방법이 있고 교체가 교체가 아니며 복구가 복구가 아니라는 것을 알고 있습니다. 그리고 저는 이렇게 합니다:

파일 1070072의 데이터베이스 'bt', 파일 'bt'에 대해 1 페이지를 처리했습니다.

파일 2의 데이터베이스 'bt', 파일 'bt_log'에 대해 1페이지를 처리했습니다.

RESTORE DATABASE는 1070074초(40.752MB/초) 만에 205.141페이지를 성공적으로 처리했습니다.

이제 BLOCKSIZE, BUFFERCOUNT 및 MAXTRANSFERSIZE가 변경된 백업을 복원해 보겠습니다.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE는 1070074초(32.283MB/초) 만에 258.958페이지를 성공적으로 처리했습니다.

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE는 1070074초(32.682MB/초) 만에 255.796페이지를 성공적으로 처리했습니다.

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE는 1070074초(32.091MB/초) 만에 260.507페이지를 성공적으로 처리했습니다.

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE는 1070074초(32.401MB/초) 만에 258.015페이지를 성공적으로 처리했습니다.

RESTORE DATABASE 문은 복구 중에 변경되지 않으며 이러한 매개 변수는 여기에 지정되지 않으며 SQL Server 자체가 백업에서 결정합니다. 그리고 회복 후에도 거의 20% 더 빠른 이득이 있을 수 있다는 것이 분명합니다(솔직히 말해서 복구에 많은 시간을 소비하지 않았고 "가장 빠른" 백업을 여러 개 실행하여 성능 저하가 없는지 확인했습니다.).

혹시라도 이것이 모든 사람에게 최적인 일부 매개변수가 아니라는 점을 분명히 하겠습니다. 테스트를 통해서만 최적의 매개 변수를 얻을 수 있습니다. 나는 이러한 결과를 얻었지만 여러분은 다른 결과를 얻게 될 것입니다. 그러나 백업을 "조정"할 수 있고 실제로 더 빠르게 구성하고 배포할 수 있다는 것을 알 수 있습니다.

또한 시스템에 따라 미묘한 차이가 있을 수 있으므로 설명서 전체를 읽어 보시기 바랍니다.

백업에 대해 쓰기 시작했기 때문에 "조정" 매개변수보다 더 일반적인 하나 이상의 "최적화"에 대해 즉시 작성하고 싶습니다. 앞에서 설명), Habré에서도 아직 설명되지 않았습니다.

BACKUP DATABASE 바로 아래에 있는 문서의 두 번째 줄을 보면 다음과 같습니다.

TO <backup_device> [ ,...n ]

여러 개의 backup_device를 지정하면 어떻게 될 것이라고 생각하시나요? 구문이 이를 허용합니다. 그리고 매우 흥미로운 일이 일어날 것입니다. 백업은 단순히 여러 장치에 "확산"됩니다. 저것들. 각 "장치"는 개별적으로 쓸모가 없으며 하나를 잃어버리거나 전체 백업을 잃어버리게 됩니다. 하지만 이러한 번짐이 백업 속도에 어떤 영향을 미칠까요?

동일한 폴더에 나란히 위치한 두 개의 "장치"에 백업을 만들어 보겠습니다.

BACKUP DATABASE [bt]
TO 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak'   
WITH COMPRESSION;

세계의 아버지들이여, 왜 이런 일이 벌어지고 있습니까?

파일 1070072의 데이터베이스 'bt', 파일 'bt'에 대해 1 페이지를 처리했습니다.

데이터베이스 'bt', 파일 'bt'에 대해 2페이지를 처리했습니다.파일 1에 '로그'를 입력합니다.

BACKUP DATABASE는 1070074초(40.092MB/초) 만에 208.519페이지를 성공적으로 처리했습니다.

갑자기 백업 속도가 25% 빨라졌습니까? 장치를 몇 개 더 추가하면 어떻게 될까요?

BACKUP DATABASE [bt]
TO 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak',
    DISK = 'D:SQLServerbackupbt3.bak',
    DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;

BACKUP DATABASE는 1070074초(34.234MB/초) 만에 244.200페이지를 성공적으로 처리했습니다.

백업이 한 디스크에 있는 35개 파일에 동시에 기록되므로 전체적으로 백업 수행 시간의 약 4%가 향상됩니다. 더 큰 숫자를 확인했습니다. 내 노트북에는 이득이 없습니다. 최적으로는 4개의 장치입니다. 당신을 위해-모르겠어요. 확인해야합니다. 그런데, 만약 당신이 이러한 장치를 가지고 있다면, 이것은 정말 다른 디스크입니다. 축하합니다. 이득은 훨씬 더 중요할 것입니다.

이제 이 행복을 회복하는 방법에 대해 이야기합시다. 이렇게 하려면 복구 명령을 변경하고 모든 장치를 나열해야 합니다.

RESTORE DATABASE [bt]
FROM 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak',
    DISK = 'D:SQLServerbackupbt3.bak',
    DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;

RESTORE DATABASE는 1070074초(38.027MB/초) 만에 219.842페이지를 성공적으로 처리했습니다.

조금 더 빠르지만 가까운 곳에서는 중요하지 않습니다. 일반적으로 백업은 더 빨리 제거되고 동일한 방식으로 복원됩니다. 성공합니까? 나로서는 꽤 성공적인 일이다. 이것 важно, 그래서 반복합니다 - 만약 당신이 이러한 파일 중 하나라도 손실되면 전체 백업도 손실됩니다.

Trace Flags 3213, 3605를 사용하여 표시된 백업 정보를 로그에서 살펴보면 여러 장치에 백업할 때 적어도 BUFFERCOUNT 개수가 증가하는 것을 알 수 있습니다. 아마도 BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE에 대해 더 최적의 매개 변수를 선택하려고 시도할 수 있지만 즉시 성공하지 못했고 이러한 테스트를 다시 수행하기에는 너무 게으른데 파일 수가 다릅니다. 그리고 바퀴가 아쉽네요. 집에서 이러한 테스트를 구성하려면 스크립트를 다시 만드는 것이 어렵지 않습니다.

마지막으로 가격에 대해 말씀드리겠습니다. 백업이 사용자 작업과 동시에 제거되는 경우 테스트에 대해 매우 책임감 있는 접근 방식을 취해야 합니다. 백업이 더 빨리 제거되면 디스크에 더 많은 부담이 가해지고 프로세서의 로드가 증가하기 때문입니다(여전히 압축해야 함). 즉석에서) 따라서 시스템의 전반적인 응답성이 감소합니다.

농담입니다만, 제가 어떤 폭로도 하지 않았다는 점은 충분히 이해합니다. 위에 쓰여진 내용은 백업을 수행하기 위한 최적의 매개변수를 선택하는 방법을 보여주는 것입니다.

귀하가 하는 모든 일은 귀하 자신의 위험과 위험에 따라 수행된다는 점을 기억하십시오. 백업을 확인하고 DBCC CHECKDB를 잊지 마세요.

출처 : habr.com