MS SQL Server: RESPALDO con esteroides

¡Esperar! ¡Esperar! Es cierto que este no es otro artículo sobre los tipos de copias de seguridad de SQL Server. Ni siquiera hablaré sobre las diferencias entre los modelos de recuperación y cómo lidiar con un "registro" demasiado grande.

Quizás (solo posible), después de leer esta publicación, podrá hacer la copia de seguridad, que se le quita por medios estándar, mañana por la noche, bueno, 1.5 veces más rápido. Y solo por el hecho de que usa un poco más de parámetros de RESPALDO DE BASE DE DATOS.

Si el contenido de la publicación te resultó obvio, lo siento. Leí todo lo que Google encontró para la frase "copia de seguridad del servidor habr sql", y no encontré ninguna mención en ningún artículo que indique que el tiempo de copia de seguridad puede verse influenciado de alguna manera mediante el uso de parámetros.

Inmediatamente llamaré su atención sobre el comentario de Alexander Gladchenko (@mssqlhelp):

Nunca cambie los parámetros BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE en el prod. Están hechos solo para escribir dichos artículos. En la práctica, tendrás problemas de memoria para siempre.

Por supuesto, sería genial ser el más inteligente y publicar contenido exclusivo, pero desafortunadamente no es así. Hay artículos/publicaciones tanto en inglés como en ruso (siempre me confundo acerca de cómo llamarlos correctamente) dedicados a este tema. Estos son algunos de los que tengo: tiempo, два, tres (en sql.ru).

Entonces, para empezar, adjuntaré una sintaxis de BACKUP ligeramente truncada de MSDN (por cierto, escribí sobre BACKUP DATABASE anteriormente, pero todo esto se aplica tanto a la copia de seguridad del registro de transacciones como a la copia de seguridad diferencial, pero quizás con un 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 allí, pero lo eliminé, porque ahora no se aplica al tema.

¿Cómo sueles hacer una copia de seguridad? ¿Cómo "enseñan" a llevar una copia de seguridad en miles de millones de artículos? En general, si necesito hacer una copia de seguridad única de una base de datos no muy grande, escribiré automáticamente algo como esto:

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

Y, en general, probablemente el 75-90% de todos los parámetros que normalmente se mencionan en los artículos sobre copias de seguridad se enumeran aquí. Bueno, hay INIT, SKIP más. ¿Has estado en MSDN? ¿Has visto que hay opciones para una pantalla y media? Yo también vi...

Probablemente ya entendió que más adelante hablaremos sobre los tres parámetros que quedaron en el primer bloque de código: BLOCKSIZE, BUFFERCOUNT y MAXTRANSFERSIZE. Aquí están sus descripciones de MSDN:

TAMAÑO DE BLOQUE = { tamaño de bloque | @ variable_tamaño_bloque } - indica el tamaño del bloque físico en bytes. Los tamaños admitidos son 512, 1024, 2048, 4096, 8192, 16, 384 y 32 bytes (768 KB). El valor predeterminado es 65 para dispositivos de cinta y 536 para otros dispositivos. Este parámetro normalmente no es necesario porque la instrucción BACKUP selecciona automáticamente un tamaño de bloque apropiado para el dispositivo. Establecer explícitamente el tamaño del bloque anula la selección automática del tamaño del bloque.

CUENTA DE BÚFER = { recuento de búfer | @ variable_conteodebúfer }: especifica el número total de búferes de E/S que se utilizarán para la operación de copia de seguridad. Puede especificar cualquier valor entero positivo, pero una gran cantidad de búferes puede provocar un error de falta de memoria debido a un espacio de direcciones virtual excesivo en el proceso Sqlservr.exe.

La cantidad total de espacio utilizado por los búferes se determina mediante la siguiente fórmula: BUFFERCOUNT * MAXTRANSFERSIZE.

TAMAÑO MÁXIMO DE TRANSFERENCIA = { tamaño máximo de transferencia | @ maxtransfersize_variable } Especifica el paquete de datos más grande, en bytes, para intercambiar datos entre SQL Server y los medios del conjunto de copia de seguridad. Se admiten múltiplos de 65 536 bytes (64 KB) hasta 4 194 304 bytes (4 MB).

Lo juro, he leído esto antes, pero no se me ocurrió qué impacto podrían tener en el rendimiento. Además, aparentemente, es necesario hacer una especie de "salida del armario" y admitir que incluso ahora no entiendo completamente qué están haciendo exactamente. Probablemente, necesite leer más sobre E / S almacenadas en búfer y cómo trabajar con un disco duro. Algún día lo haré, pero ahora solo puedo escribir un script que verificará cómo estos valores afectan la velocidad con la que se elimina la copia de seguridad.

Creé una base de datos pequeña, de unos 10 GB de tamaño, la puse en el SSD y puse el directorio de respaldo en el HDD.

Creo una tabla temporal para almacenar los resultados (no tengo una tabla temporal para poder recoger los resultados con más detalle, pero tú decides):

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
);

El principio del script es simple: bucles anidados, cada uno de los cuales cambia el valor de un parámetro, inserte estos parámetros en el comando BACKUP, guarde el último registro con el historial de msdb.dbo.backupset, elimine el archivo de copia de seguridad y el siguiente iteración. Dado que los datos de ejecución de la copia de seguridad se toman del conjunto de copia de seguridad, la precisión se pierde un poco (no hay fracciones de segundo), pero sobreviviremos a esto.

Primero debe habilitar el uso de xp_cmdshell para eliminar las copias de seguridad (entonces no olvide deshabilitarlo si no lo necesita):

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

Bueno en realidad:

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

Si de repente necesita explicaciones sobre lo que está sucediendo aquí, escriba en los comentarios o en un mensaje personal. Por ahora, solo hablaré sobre los parámetros que deslizo en la BASE DE DATOS DE RESPALDO.

Para BLOCKSIZE, tenemos una lista de valores "cerrada" y no tenía una copia de seguridad con BLOCKSIZE < 4 KB. MAXTRANSFERSIZE cualquier múltiplo de 64 KB, de 64 KB a 4 MB. De forma predeterminada en mi sistema de 1024 KB, tomé 512 - 1024 - 2048 - 4096.

Fue más difícil con BUFFERCOUNT: puede ser cualquier número positivo, pero el enlace dice cómo se calcula en BACKUP DATABASE y por qué los valores grandes son peligrosos. También está escrito allí cómo obtener información sobre con qué BUFFERCOUNT se toma realmente la copia de seguridad: lo tengo 7. No tenía sentido reducirlo, y el límite superior se encontró empíricamente: con BUFFERCOUNT = 896 y MAXTRANSFERSIZE = 4194304 la copia de seguridad cayó con un error (sobre el que se publicó en el enlace de arriba):

Msg 3013, nivel 16, estado 1, línea 7 BASE DE DATOS DE RESPALDO está terminando anormalmente.

Mensaje 701, nivel 17, estado 123, línea 7 No hay suficiente memoria del sistema en el grupo de recursos "predeterminado" para ejecutar esta consulta.

A modo de comparación, primero mostraré los resultados de realizar una copia de seguridad sin especificar ningún parámetro:

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

Bueno, copia de seguridad y copia de seguridad:

Se procesaron 1070072 páginas para la base de datos 'bt', archivo 'bt' en el archivo 1.

Se procesaron 2 páginas para la base de datos 'bt', archivo 'bt_log' en el archivo 1.

BACKUP DATABASE procesó con éxito 1070074 páginas en 53.171 segundos (157.227 MB/seg).

El script en sí, probando los parámetros, funcionó en un par de horas, todas las medidas en tabla de google. Y aquí hay una selección de resultados que tienen los tres mejores tiempos de ejecución (traté de hacer un gráfico hermoso, pero, en la publicación, tengo que salir adelante con un cartel, y en los comentarios @mezcla adicional gráficos muy buenos).

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, solo una nota muy importante de @mezcla de comentarios:

podemos decir con confianza que la conexión entre los parámetros y la velocidad de respaldo dentro de estos rangos de valores es aleatoria, no hay un patrón. Pero alejarse de los parámetros integrados obviamente tuvo un buen efecto en el resultado.

Aquellos. Solo mediante el control de los parámetros estándar de BACKUP se ganó el doble en el tiempo de eliminación de la copia de seguridad: 2 segundos, contra 26 al principio. Y no está mal, ¿verdad? Pero hay que ver qué hay con la restauración. ¿Y si ahora tardará 53 veces más en recuperarse?

Primero, midamos cuánto tiempo lleva restaurar una copia de seguridad con la configuración predeterminada:

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

Bueno, usted mismo lo sabe, los caminos están ahí, reemplazar-no reemplazar, recuperar-no recuperar. Y lo hago así:

Se procesaron 1070072 páginas para la base de datos 'bt', archivo 'bt' en el archivo 1.

Se procesaron 2 páginas para la base de datos 'bt', archivo 'bt_log' en el archivo 1.

RESTORE DATABASE procesó con éxito 1070074 páginas en 40.752 segundos (205.141 MB/seg).

Y ahora intentaré restaurar las copias de seguridad realizadas con BLOCKSIZE, BUFFERCOUNT y MAXTRANSFERSIZE modificados.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE procesó con éxito 1070074 páginas en 32.283 segundos (258.958 MB/seg).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE procesó con éxito 1070074 páginas en 32.682 segundos (255.796 MB/seg).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE procesó con éxito 1070074 páginas en 32.091 segundos (260.507 MB/seg).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE procesó con éxito 1070074 páginas en 32.401 segundos (258.015 MB/seg).

La declaración RESTORE DATABASE no cambia durante la recuperación, estos parámetros no se especifican en ella, el propio SQL Server los determina a partir de la copia de seguridad. Y está claro que incluso con la restauración puede haber una ganancia, casi un 20% más rápido (para ser honesto, no dediqué mucho tiempo a la recuperación, perforé varias de las copias de seguridad "más rápidas" y me aseguré de que no hubiera deterioro).

Por si acaso, lo aclararé: aquí no se describen algunos parámetros que son óptimos para todos. Puede obtener los mejores parámetros para usted solo mediante pruebas. Obtuve estos resultados, obtendrás otros diferentes. Pero verá que sus copias de seguridad se pueden "ajustar" y realmente pueden formarse e implementarse más rápido.

También recomiendo encarecidamente que lea la documentación en su totalidad, ya que puede haber matices para su sistema.

Desde que comencé a escribir sobre copias de seguridad, quiero escribir de inmediato sobre una "optimización" más, que es más común que los parámetros de "ajuste" (según tengo entendido, es utilizado por al menos algunas de las utilidades de copia de seguridad, posiblemente junto con los parámetros descritos anteriormente), pero aún no ha sido descrito en Habré.

Si nos fijamos en la segunda línea de la documentación, justo debajo de BACKUP DATABASE, vemos:

TO <backup_device> [ ,...n ]

¿Qué cree que sucederá si especifica varios dispositivos de copia de seguridad? La sintaxis sí. Y habrá algo muy interesante: la copia de seguridad simplemente se "manchará" en varios dispositivos. Aquellos. cada "dispositivo" por separado será inútil, perdió uno, perdió toda la copia de seguridad. Pero, ¿cómo afectará esa mancha a la velocidad de la copia de seguridad?

Intentemos hacer una copia de seguridad en dos "dispositivos" que están uno al lado del otro en la misma carpeta:

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

Santos Padres, ¿por qué se hace esto?

Se procesaron 1070072 páginas para la base de datos 'bt', archivo 'bt' en el archivo 1.

2 páginas procesadas para la base de datos 'bt', archivo 'bt'log' en el archivo 1.

BACKUP DATABASE procesó con éxito 1070074 páginas en 40.092 segundos (208.519 MB/seg).

¿La copia de seguridad se volvió un 25% más rápida de la nada? ¿Y si añades un par de dispositivos más?

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

BACKUP DATABASE procesó con éxito 1070074 páginas en 34.234 segundos (244.200 MB/seg).

En total, la ganancia es aproximadamente el 35 % del tiempo de la copia de seguridad solo debido al hecho de que la copia de seguridad se escribe inmediatamente en 4 archivos en un disco. Verifiqué un número mayor: no hay ganancia en mi computadora portátil, de manera óptima: 4 dispositivos. Para ti, no lo sé, debes verificarlo. Y, por cierto, si tiene estos dispositivos, estos son discos realmente diferentes, felicitaciones, la ganancia debería ser aún más significativa.

Ahora hablemos de cómo restaurar esta felicidad. Para hacer esto, deberá cambiar el comando de recuperación y enumerar todos los 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 procesó con éxito 1070074 páginas en 38.027 segundos (219.842 MB/seg).

Un poco más rápido, pero en algún lugar cercano, no es esencial. En general, la copia de seguridad se elimina más rápido y se restaura de la misma manera. ¿Éxito? En lo que a mí respecta, es todo un éxito. Este es importante, así que repito - si Ud. si pierde al menos uno de estos archivos, perderá toda la copia de seguridad.

Si observa la información de registro sobre la copia de seguridad que se muestra con Trace Flag 3213 y 3605, puede notar que al realizar la copia de seguridad en varios dispositivos, al menos aumenta la cantidad de BUFFERCOUNT. Probablemente, puede intentar elegir parámetros más óptimos para BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, pero no tuve éxito de inmediato, y volví a realizar tales pruebas, pero era demasiado perezoso para una cantidad diferente de archivos. Sí, y los discos son una pena. Si desea organizar dichas pruebas en su lugar, no es difícil rehacer el guión.

Por último, hablemos del precio. Si se realiza una copia de seguridad en paralelo con el trabajo de los usuarios, es necesario abordar las pruebas de manera muy responsable, porque si la copia de seguridad se realiza más rápido, los discos se sobrecargan más, la carga en el procesador aumenta (todavía necesita comprimirlo en la mosca), respectivamente, la capacidad de respuesta general del sistema disminuye.

Bromas, bromas, pero entiendo perfectamente que no hice ninguna revelación. Lo que está escrito arriba es solo una demostración de cómo puede elegir los parámetros óptimos para realizar copias de seguridad.

Recuerde que todo lo que hace, lo hace bajo su propio riesgo y riesgo. Verifique sus copias de seguridad y no se olvide de DBCC CHECKDB.

Fuente: habr.com