MS SQL Server: BACKUP en esteroides

Agarda! Agarda! É certo, este non é outro artigo sobre os tipos de copias de seguridade de SQL Server. Nin sequera falarei das diferenzas entre os modelos de recuperación e de como tratar cun tronco superado.

Quizais (só quizais), despois de ler esta publicación, poidas asegurarte de que a copia de seguranza que se elimina mediante medios estándar eliminarase mañá á noite, ben, 1.5 veces máis rápido. E só polo feito de usar un pouco máis de parámetros da BASE DE DATOS DE COPIA DE SEGURIDAD.

Se o contido da publicación foi obvio para ti, desculpe. Lin todo o que chegou Google para a frase "habr sql server backup", e en ningún artigo atopei ningunha mención ao feito de que o tempo de copia de seguridade pode ser influenciado dalgún xeito mediante parámetros.

Chamarei inmediatamente a túa atención sobre o comentario de Alexander Gladchenko (@mssqlhelp):

Nunca cambie os parámetros BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE na produción. Están feitos só para escribir tales artigos. Na práctica, desfacerase dos problemas de memoria en pouco tempo.

Por suposto, sería xenial ser o máis intelixente e publicar contido exclusivo, pero, por desgraza, non é así. Hai artigos/publicacións en inglés e en ruso (sempre estou confuso sobre como chamalos correctamente) dedicados a este tema. Aquí están algúns dos que atopei: tempo, два, tres (en sql.ru).

Entón, para comezar, engadirei unha sintaxe de BACKUP lixeiramente reducida MSDN (Por certo, escribín anteriormente sobre BACKUP DATABASE, pero todo isto aplícase tanto á copia de seguridade do rexistro de transaccións como á copia de seguridade diferencial, pero quizais cun efecto menos obvio):

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 había algo alí, pero quiteino porque agora non é relevante para o tema.

Como adoita facer unha copia de seguridade? Como "ensinan" a facer copias de seguridade en miles de millóns de artigos? En xeral, se teño que facer unha copia de seguridade dunha soa vez dalgúns datos non moi grandes, escribirei automaticamente algo así:

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

E, en xeral, probablemente 75-90% de todos os parámetros que se adoitan mencionar nos artigos sobre copias de seguridade están listados aquí. Pois tamén hai INIT, SKIP. Visitaches MSDN? Viches que hai opcións para unha pantalla e media? Eu tamén vin...

Probablemente xa te decataches de que máis adiante falaremos dos tres parámetros que quedaron no primeiro bloque de código: BLOCKSIZE, BUFFERCOUNT e MAXTRANSFERSIZE. Aquí están as súas descricións de MSDN:

TAMAÑO DE BLOQUE = { tamaño de bloque | @ variable_tamaño do bloque } - indica o tamaño do bloque físico en bytes. Os tamaños admitidos son 512, 1024, 2048, 4096, 8192, 16, 384 e 32 bytes (768 KB). O valor predeterminado é 65 para dispositivos de cinta e 536 para outros dispositivos. Normalmente, este parámetro non é necesario porque a instrución BACKUP selecciona automaticamente o tamaño de bloque adecuado para o dispositivo. Establecer o tamaño do bloque anula explícitamente a selección automática do tamaño do bloque.

CONTADOR DE TAMPÓN = { recuento de tampón | @ buffercount_variable } - Define o número total de búfers de E/S que se utilizarán para a operación de copia de seguridade. Podes especificar calquera valor enteiro positivo, pero un gran número de búfers pode provocar un erro de falta de memoria debido ao espazo de enderezos virtuais excesivo no proceso Sqlservr.exe.

A cantidade total de espazo empregado polos búfers determínase pola seguinte fórmula: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { tamaño máximo de transferencia | @ maxtransfersize_variable } especifica o maior tamaño de paquete de datos, en bytes, para intercambiar entre SQL Server e o medio do conxunto de copia de seguridade. Admítense múltiplos de 65 bytes (536 KB) ata 64 bytes (4 MB).

Xúroo: lin isto antes, pero nunca se me ocorreu o impacto que podían ter na produtividade. Ademais, ao parecer, teño que facer unha especie de "coming out" e admitir que aínda agora non entendo ben o que están facendo exactamente. Probablemente necesite ler máis sobre E/S almacenadas en búfer e traballar cun disco duro. Algún día fareino isto, pero por agora só podo escribir un script que comprobará como afectan estes valores á velocidade á que se realiza a copia de seguridade.

Fixen unha pequena base de datos, duns 10 GB de tamaño, púxena no SSD e puxen o directorio de copias de seguridade no HDD.

Creo unha táboa temporal para almacenar os resultados (non a teño temporal, polo que podo explorar os resultados con máis detalle, pero ti decides por ti 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 principio do script é sinxelo: bucles anidados, cada un dos cales cambia o valor dun parámetro, insira estes parámetros no comando BACKUP, garda o último rexistro co historial de msdb.dbo.backupset, elimine o ficheiro de copia de seguridade e a seguinte iteración. . Dado que os datos de execución de copia de seguridade son tomados do conxunto de copias de seguridade, a precisión pérdese un pouco (non hai fraccións de segundo), pero sobreviviremos a isto.

Primeiro cómpre activar xp_cmdshell para eliminar as copias de seguridade (logo non esquezas desactivalo se non o necesitas):

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

Ben, en realidade:

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

Se de súpeto necesitas unha aclaración sobre o que está a suceder aquí, escribe nos comentarios ou MP. Polo momento, só vos falarei dos parámetros que poño na BASE DE DATOS DE COPIA DE SEGURIDAD.

Para BLOCKSIZE temos unha lista de valores "pechada" e non realicei unha copia de seguridade con BLOCKSIZE < 4KB. MAXTRANSFERSIZE calquera número que sexa un múltiplo de 64 KB, de 64 KB a 4 MB. O valor predeterminado do meu sistema é 1024 KB, tomei 512 - 1024 - 2048 - 4096.

Foi máis difícil con BUFFERCOUNT: pode ser calquera número positivo, pero a ligazón di como se calcula na BASE DE DATOS DE COPIA DE SEGURIDAD e por que son perigosos os valores grandes?. Tamén di como obter información sobre con que BUFFERCOUNT se fai realmente a copia de seguranza - para min é 7. Non tiña sentido reducila e o límite superior descubriuse experimentalmente - con BUFFERCOUNT = 896 e MAXTRANSFERSIZE = 4194304 a copia de seguridade caeu con un erro (sobre o que está escrito na ligazón anterior):

Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE está a finalizar de forma anormal.

Msx 701, Nivel 17, Estado 123, Liña 7 Non hai memoria do sistema suficiente no grupo de recursos "predeterminado" para executar esta consulta.

A modo de comparación, primeiro mostrarei os resultados da execución dunha copia de seguridade sen especificar ningún parámetro:

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

Ben, copia de seguridade e copia de seguridade:

Procesáronse 1070072 páxinas para a base de datos "bt", o ficheiro "bt" no ficheiro 1.

Procesáronse 2 páxinas para a base de datos "bt", o ficheiro "bt_log" no ficheiro 1.

A BASE DE DATOS DE BACKUP procesou correctamente 1070074 páxinas en 53.171 segundos (157.227 MB/s).

O propio guión, probando os parámetros, funcionou nun par de horas, todas as medidas estaban dentro folla de cálculo de google. E aquí tedes unha selección de resultados cos tres mellores tempos de execución (intentei facer un bonito gráfico, pero no post terei que conformarme cunha táboa, e nos comentarios @mixsture engadiu gráficos moi chulos).

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;

Atención, unha nota moi importante de @mixsture de comentario:

Podemos dicir con confianza que a relación entre os parámetros e a velocidade de copia de seguridade dentro destes intervalos de valores é aleatoria, non hai ningún patrón. Pero afastarse dos parámetros incorporados obviamente tivo un bo efecto no resultado

Eses. Só xestionando os parámetros estándar de BACKUP obtívose unha ganancia de dúas veces no tempo de eliminación da copia de seguranza: 2 segundos, fronte aos 26 ao principio. Iso non está mal, non? Pero hai que ver que pasa coa restauración. E se agora tarda 53 veces máis en recuperarse?

Primeiro, imos medir canto tempo leva restaurar unha copia de seguranza coa configuración predeterminada:

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

Ben, vostede mesmo sabe que, as formas están aí, substituír non é substituír, recuperar non é recuperación. E fágoo así:

Procesáronse 1070072 páxinas para a base de datos "bt", o ficheiro "bt" no ficheiro 1.

Procesáronse 2 páxinas para a base de datos "bt", o ficheiro "bt_log" no ficheiro 1.

RESTORE DATABASE procesou correctamente 1070074 páxinas en 40.752 segundos (205.141 MB/s).

Agora tentarei restaurar as copias de seguranza realizadas con BLOCKSIZE, BUFFERCOUNT e MAXTRANSFERSIZE modificados.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE procesou correctamente 1070074 páxinas en 32.283 segundos (258.958 MB/s).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE procesou correctamente 1070074 páxinas en 32.682 segundos (255.796 MB/s).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE procesou correctamente 1070074 páxinas en 32.091 segundos (260.507 MB/s).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE procesou correctamente 1070074 páxinas en 32.401 segundos (258.015 MB/s).

A instrución RESTORE DATABASE non cambia durante a recuperación; estes parámetros non se especifican nela; o propio SQL Server os determina a partir da copia de seguridade. E está claro que mesmo coa recuperación pode haber unha ganancia - case un 20% máis rápido (Para ser honesto, non pasei moito tempo na recuperación, revisei varias das copias de seguridade "máis rápidas" e asegurei de que non houbese deterioración.).

Por se acaso, permítanme aclarar que non se trata duns parámetros óptimos para todos. Só podes obter os parámetros óptimos para ti probando. Obtiven estes resultados, obterás outros diferentes. Pero ves que podes "axustar" as túas copias de seguridade e que en realidade poden formarse e implementarse máis rápido.

Tamén recomendo encarecidamente que leas a documentación na súa totalidade, porque pode haber matices específicos do teu sistema.

Dende que comecei a escribir sobre copias de seguridade, quero escribir de inmediato sobre unha "optimización" máis, que é máis común que os parámetros de "axuste" (polo que teño entendido, úsano polo menos algunhas utilidades de copia de seguridade, quizais xunto cos parámetros). descrito anteriormente), pero aínda non foi descrito en Habré tampouco.

Se miramos a segunda liña da documentación, xusto debaixo da BASE DE DATOS DE COPIA DE SEGURIDAD, alí vemos:

TO <backup_device> [ ,...n ]

Que cres que pasará se especificas varios backup_devices? A sintaxe permíteo. E ocorrerá algo moi interesante: a copia de seguridade simplemente "estenderase" por varios dispositivos. Eses. cada "dispositivo" individualmente será inútil, perdeu un, perdeu a copia de seguridade enteira. Pero como afectará este tipo de manchas á velocidade das copias de seguridade?

Imos tentar facer unha copia de seguridade en dous "dispositivos" que se atopan un ao lado do outro no mesmo cartafol:

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

Pais do mundo, por que se fai isto?

Procesáronse 1070072 páxinas para a base de datos "bt", o ficheiro "bt" no ficheiro 1.

Procesáronse 2 páxinas para a base de datos "bt", o ficheiro "bt"rexistro' no ficheiro 1.

A BASE DE DATOS DE BACKUP procesou correctamente 1070074 páxinas en 40.092 segundos (208.519 MB/s).

A copia de seguranza fíxose un 25 % máis rápida de repente? E se engadimos un par de dispositivos máis?

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

A BASE DE DATOS DE BACKUP procesou correctamente 1070074 páxinas en 34.234 segundos (244.200 MB/s).

En total, a ganancia é de aproximadamente o 35% do tempo de facer unha copia de seguridade só debido ao feito de que a copia de seguridade está escrita en 4 ficheiros nun disco á vez. Comprobei un número maior - non hai ningunha ganancia no meu portátil, de forma óptima - 4 dispositivos. Para ti - non sei, tes que comprobar. Ben, por certo, se tes estes dispositivos, son discos realmente diferentes, parabéns, a ganancia debería ser aínda máis significativa.

Agora imos falar de como restaurar esta felicidade. Para iso, terás que cambiar o comando de recuperación 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 procesou correctamente 1070074 páxinas en 38.027 segundos (219.842 MB/s).

Un pouco máis rápido, pero nalgún lugar preto, non significativo. En xeral, a copia de seguridade elimínase máis rápido e restablece da mesma forma: ¿éxito? En canto a min, é todo un éxito. Isto é importante, entón repito - se vostede se perde polo menos un destes ficheiros, perderá a copia de seguridade completa.

Se miras no rexistro a información de copia de seguranza que se amosa usando os indicadores de rastrexo 3213 e 3605, notarás que ao facer unha copia de seguranza en varios dispositivos, polo menos aumenta o número de BUFFERCOUNT. Probablemente, podes tentar seleccionar os parámetros máis óptimos para BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, pero non o conseguín de inmediato e fíxome demasiado preguiceiro para volver realizar este tipo de probas, pero para un número diferente de ficheiros. E é unha mágoa as rodas. Se queres organizar este tipo de probas na casa, non é difícil refacer o guión.

Finalmente, imos falar do prezo. Se a copia de seguridade se elimina en paralelo co traballo dos usuarios, cómpre adoptar un enfoque moi responsable das probas, xa que se a copia de seguranza se elimina máis rápido, os discos tensanse máis, a carga do procesador aumenta (aínda tes que comprimir realízao sobre a marcha) e, en consecuencia, a capacidade de resposta global do sistema diminúe.

Só broma, pero entendo perfectamente que non fixen ningunha revelación. O que está escrito arriba é simplemente unha demostración de como pode seleccionar os parámetros óptimos para facer copias de seguridade.

Lembra que todo o que fas faise baixo o teu propio risco e risco. Comproba as túas copias de seguridade e non te esquezas de DBCC CHECKDB.

Fonte: www.habr.com