Espere! Espere! É verdade que este não é outro artigo sobre tipos de backups do SQL Server. Nem vou falar sobre as diferenças entre os modelos de recuperação e como lidar com um tronco cheio de vegetação.
Talvez (apenas talvez), depois de ler esta postagem, você consiga ter certeza de que o backup que foi removido de você usando meios padrão será removido amanhã à noite, bem, 1.5 vezes mais rápido. E só pelo fato de você usar um pouco mais de parâmetros do BACKUP DATABASE.
Se o conteúdo da postagem foi óbvio para você, sinto muito. Eu li tudo o que o Google encontrou sobre a frase “habr sql server backup”, e em nenhum artigo encontrei qualquer menção ao fato de que o tempo de backup pode ser influenciado de alguma forma por meio de parâmetros.
Chamarei imediatamente a sua atenção para o comentário de Alexander Gladchenko ():
Nunca altere os parâmetros BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE na produção. Eles são feitos apenas para escrever tais artigos. Na prática, você se livrará dos problemas de memória rapidamente.
Claro que seria legal ser o mais esperto e postar conteúdos exclusivos, mas, infelizmente, não é o caso. Existem artigos/postagens em inglês e em russo (estou sempre confuso sobre como chamá-los corretamente) dedicados a este tópico. Aqui estão alguns dos que encontrei: , , .
Então, para começar, anexarei uma sintaxe BACKUP ligeiramente simplificada de (a propósito, escrevi acima sobre BACKUP DATABASE, mas tudo isso se aplica tanto ao backup do log de transações quanto ao backup diferencial, mas talvez com um efeito menos óbvio):
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 }
<...><…> - significa que havia algo ali, mas removi porque agora não é relevante para o assunto.
Como você costuma fazer um backup? Como eles “ensinam” como fazer backups de bilhões de artigos? Em geral, se eu precisar fazer um backup único de algum banco de dados não muito grande, escreverei automaticamente algo assim:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееE, em geral, provavelmente 75-90% de todos os parâmetros normalmente mencionados em artigos sobre backups estão listados aqui. Bem, também existe INIT, SKIP. Você visitou o MSDN? Você viu que existem opções de tela e meia? eu também vi...
Você provavelmente já percebeu que falaremos mais sobre os três parâmetros que permaneceram no primeiro bloco de código - BLOCKSIZE, BUFFERCOUNT e MAXTRANSFERSIZE. Aqui estão suas descrições do MSDN:
TAMANHO DO BLOCO = { tamanho do bloco | @ tamanho_de_bloco_variável } - indica o tamanho do bloco físico em bytes. Os tamanhos suportados são 512, 1024, 2048, 4096, 8192, 16, 384 e 32 bytes (768 KB). O valor padrão é 65 para dispositivos de fita e 536 para outros dispositivos. Normalmente, esse parâmetro não é necessário porque a instrução BACKUP seleciona automaticamente o tamanho de bloco apropriado para o dispositivo. A definição explícita do tamanho do bloco substitui a seleção automática do tamanho do bloco.
CONTAGEM DE BUFFER = { contagem de buffers | @ buffercount_variável } - Define o número total de buffers de E/S que serão utilizados para a operação de backup. Você pode especificar qualquer valor inteiro positivo, mas um grande número de buffers pode causar um erro de falta de memória devido ao espaço de endereço virtual excessivo no processo Sqlservr.exe.
A quantidade total de espaço usado pelos buffers é determinada pela seguinte fórmula:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variável } especifica o maior tamanho do pacote de dados, em bytes, para troca entre o SQL Server e a mídia do conjunto de backup. São suportados múltiplos de 65 bytes (536 KB) até 64 bytes (4 MB).
Eu juro – já li isso antes, mas nunca me ocorreu o impacto que eles poderiam ter na produtividade. Além disso, aparentemente, preciso fazer uma espécie de “revelação” e admitir que mesmo agora não entendo completamente o que exatamente eles estão fazendo. Provavelmente preciso ler mais sobre E/S em buffer e como trabalhar com um disco rígido. Algum dia farei isso, mas por enquanto posso apenas escrever um script que irá verificar como esses valores afetam a velocidade com que o backup é feito.
Fiz um pequeno banco de dados, com cerca de 10 GB, coloquei no SSD e coloquei o diretório para backups no HDD.
Eu crio uma tabela temporária para armazenar os resultados (não a tenho temporária, então posso me aprofundar nos resultados com mais detalhes, mas você decide por si mesmo):
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
);O princípio do script é simples - loops aninhados, cada um dos quais altera o valor de um parâmetro, insere esses parâmetros no comando BACKUP, salva o último registro com histórico de msdb.dbo.backupset, exclui o arquivo de backup e a próxima iteração . Como os dados de execução do backup são retirados do conjunto de backup, a precisão é um pouco perdida (não há frações de segundos), mas sobreviveremos a isso.
Primeiro você precisa habilitar o xp_cmdshell para excluir backups (depois não se esqueça de desabilitá-lo se não precisar dele):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOBem, na verdade:
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;
ENDSe de repente você precisar de esclarecimentos sobre o que está acontecendo aqui, escreva nos comentários ou PM. Por enquanto, falarei apenas sobre os parâmetros que coloquei no BACKUP DATABASE.
Para BLOCKSIZE temos uma lista de valores “fechada”, e não realizei backup com BLOCKSIZE < 4KB. MAXTRANSFERSIZE qualquer número que seja múltiplo de 64 KB - de 64 KB a 4 MB. O padrão no meu sistema é 1024 KB, peguei 512 - 1024 - 2048 - 4096.
Foi mais difícil com BUFFERCOUNT - pode ser qualquer número positivo, mas o link diz . Também diz como obter informações sobre com qual BUFFERCOUNT o backup é realmente feito - para mim é 7. Não adiantava reduzi-lo, e o limite superior foi descoberto experimentalmente - com BUFFERCOUNT = 896 e MAXTRANSFERSIZE = 4194304 o backup caiu com um erro (sobre o qual está escrito no link acima):
Msg 3013, Nível 16, Estado 1, Linha 7 BACKUP DATABASE está sendo encerrado de forma anormal.
Msg 701, Nível 17, Estado 123, Linha 7 Não há memória de sistema suficiente no pool de recursos 'padrão' para executar esta consulta.
Para efeito de comparação, primeiro mostrarei os resultados da execução de um backup sem especificar nenhum parâmetro:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Bem, backup e backup:
Processadas 1070072 páginas para banco de dados 'bt', arquivo 'bt' no arquivo 1.
Processadas 2 páginas para o banco de dados 'bt', arquivo 'bt_log' no arquivo 1.
BACKUP DATABASE processou com êxito 1070074 páginas em 53.171 segundos (157.227 MB/s).
O script em si, testando os parâmetros, funcionou em algumas horas, todas as medições estavam em . E aqui está uma seleção de resultados com os três melhores tempos de execução (tentei fazer um gráfico legal, mas no post vou ter que me contentar com uma tabela, e nos comentários adicionado ).
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;
Atenção, uma nota muito importante de de :
Podemos afirmar com segurança que a relação entre os parâmetros e a velocidade de backup dentro dessas faixas de valores é aleatória, não existe um padrão. Mas afastar-se dos parâmetros integrados obviamente teve um bom efeito no resultado
Aqueles. Somente gerenciando os parâmetros padrão do BACKUP houve um ganho de 2 vezes no tempo de remoção do backup: 26 segundos, contra 53 no início. Isso não é ruim, certo? Mas precisamos ver o que acontece com a restauração. E se agora demorar 4 vezes mais para se recuperar?
Primeiro, vamos medir quanto tempo leva para restaurar um backup com as configurações padrão:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Bem, você mesmo sabe disso, os caminhos existem, substituir não é substituir, recuperação não é recuperação. E eu faço assim:
Processadas 1070072 páginas para banco de dados 'bt', arquivo 'bt' no arquivo 1.
Processadas 2 páginas para o banco de dados 'bt', arquivo 'bt_log' no arquivo 1.
RESTORE DATABASE processou com êxito 1070074 páginas em 40.752 segundos (205.141 MB/s).
Agora tentarei restaurar backups feitos com BLOCKSIZE, BUFFERCOUNT e MAXTRANSFERSIZE alterados.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304RESTORE DATABASE processou com êxito 1070074 páginas em 32.283 segundos (258.958 MB/s).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304RESTORE DATABASE processou com êxito 1070074 páginas em 32.682 segundos (255.796 MB/s).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152RESTORE DATABASE processou com êxito 1070074 páginas em 32.091 segundos (260.507 MB/s).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304RESTORE DATABASE processou com êxito 1070074 páginas em 32.401 segundos (258.015 MB/s).
A instrução RESTORE DATABASE não muda durante a recuperação; esses parâmetros não são especificados nela; o próprio SQL Server os determina a partir do backup. E é claro que mesmo com a recuperação pode haver um ganho – quase 20% mais rápido (Para ser sincero, não gastei muito tempo na recuperação, executei vários dos backups “mais rápidos” e certifiquei-me de que não houve deterioração).
Por precaução, deixe-me esclarecer que esses não são alguns parâmetros ideais para todos. Você só pode obter os parâmetros ideais por meio de testes. Eu obtive esses resultados, você obterá outros diferentes. Mas você vê que pode “ajustar” seus backups e eles podem realmente formar e implantar mais rapidamente.
Também recomendo fortemente que você leia a documentação na íntegra, pois pode haver nuances específicas do seu sistema.
Desde que comecei a escrever sobre backups, quero escrever imediatamente sobre mais uma “otimização”, que é mais comum do que “ajustar” parâmetros (pelo que entendi, é usado por pelo menos alguns utilitários de backup, talvez junto com os parâmetros descrito anteriormente), mas também ainda não foi descrito em Habré.
Se olharmos a segunda linha da documentação, logo abaixo de BACKUP DATABASE, vemos:
TO <backup_device> [ ,...n ]O que você acha que acontecerá se você especificar vários backup_devices? A sintaxe permite isso. E uma coisa muito interessante vai acontecer - o backup ficará simplesmente “espalhado” por vários dispositivos. Aqueles. cada “dispositivo” individualmente será inútil, perdeu um, perdeu todo o backup. Mas como essa mancha afetará a velocidade do backup?
Vamos tentar fazer um backup em dois “dispositivos” que estão localizados lado a lado na mesma pasta:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Pais do mundo, por que isso está sendo feito?
Processadas 1070072 páginas para banco de dados 'bt', arquivo 'bt' no arquivo 1.
Processou 2 páginas para banco de dados 'bt', arquivo 'bt'log' no arquivo 1.
BACKUP DATABASE processou com êxito 1070074 páginas em 40.092 segundos (208.519 MB/s).
O backup ficou 25% mais rápido do nada? E se adicionarmos mais alguns dispositivos?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;BACKUP DATABASE processou com êxito 1070074 páginas em 34.234 segundos (244.200 MB/s).
No total, o ganho é de cerca de 35% do tempo de realização de um backup apenas devido ao fato do backup ser gravado em 4 arquivos em um disco ao mesmo tempo. Verifiquei um número maior - não há ganho no meu laptop, idealmente - 4 dispositivos. Para você - não sei, você precisa verificar. Bom, aliás, se você tem esses aparelhos - são discos realmente diferentes, parabéns, o ganho deve ser ainda mais significativo.
Agora vamos falar sobre como restaurar essa felicidade. Para fazer isso, você terá que alterar o comando de recuperação e listar todos os dispositivos:
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 processou com êxito 1070074 páginas em 38.027 segundos (219.842 MB/s).
Um pouco mais rápido, mas em algum lugar próximo, não significativo. Em geral, o backup é removido mais rapidamente e restaurado da mesma forma - sucesso? Quanto a mim, é um grande sucesso. Esse é importante, então repito - se você se você perder pelo menos um desses arquivos, perderá todo o backup.
Se você observar no log as informações de backup exibidas usando os Trace Flags 3213 e 3605, notará que, ao fazer backup em vários dispositivos, pelo menos o número de BUFFERCOUNT aumenta. Provavelmente, você pode tentar selecionar parâmetros mais ideais para BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, mas não consegui imediatamente e tive preguiça de realizar esses testes novamente, mas para um número diferente de arquivos. E é uma pena as rodas. Se você quiser organizar esses testes em casa, não será difícil refazer o roteiro.
Finalmente, vamos falar sobre preço. Se o backup for removido paralelamente ao trabalho dos usuários, você precisa ter uma abordagem muito responsável nos testes, porque se o backup for removido mais rápido, os discos ficam mais sobrecarregados, a carga no processador aumenta (você ainda precisa compactar em tempo real) e, conseqüentemente, a capacidade de resposta geral do sistema diminui.
Brincadeira, mas entendo perfeitamente que não fiz nenhuma revelação. O que está escrito acima é simplesmente uma demonstração de como você pode selecionar os parâmetros ideais para fazer backups.
Lembre-se de que tudo o que você faz é por sua própria conta e risco. Verifique seus backups e não se esqueça do DBCC CHECKDB.
Fonte: habr.com