¡Espera! ¡Espera! En realidad, esto no es otro artículo sobre los tipos de copias de seguridad de SQL Server. Ni siquiera voy a hablar sobre las diferencias en los modelos de recuperación ni sobre cómo lidiar con un 'log' hinchado.
Es posible (solo posible) que, tras leer esta publicación, logres que la copia de seguridad que realizas con las herramientas estándar se complete mañana por la noche, digamos, 1.5 veces más rápido. Y solo debido a que utilizas un poco más de parámetros BACKUP DATABASE.
Si el contenido de la publicación te parecía evidente, lo siento. He leído todo lo que encontré en Google con la frase 'habr sql server backup', y en ningún artículo encontré mención de que se pueda influir en el tiempo de copia de seguridad de alguna manera utilizando parámetros.
Llamo tu atención sobre el comentario de Alexander Gladchenko ():
Nunca cambies los parámetros BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE en producción. Están diseñados solo para escribir este tipo de artículos. En la práctica, solo atraerás problemas de memoria.
Sería genial ser el más inteligente y publicar contenido exclusivo, pero, desgraciadamente, no es así. Hay artículos/publicaciones tanto en inglés como en ruso (siempre confundo cómo llamarlos correctamente) dedicados a este tema. Aquí hay una parte de los que encontré: , , .
Así que, para empezar, adjunto una sintaxis de BACKUP ligeramente simplificada de (por cierto, mencioné BACKUP DATABASE arriba, pero todo esto se aplica también a la copia de seguridad del registro de transacciones y a las copias de seguridad diferenciales, aunque, posiblemente, con un efecto menos evidente):
BACKUP DATABASE { database_name | @database_name_var }
TO [ ,...n ]
[ WITH {
| [ ,...n ] } ]
[;]
[ ,...n ]::=
--Opciones del conjunto de medios
| BLOCKSIZE = { blocksize | @blocksize_variable }
--Opciones de transferencia de datos
BUFFERCOUNT = { buffercount | @buffercount_variable }
| MAXTRANSFERSIZE = { maxtransfersize | @maxtransfersize_variable }significa que había algo allí, pero lo eliminé porque ahora no es relevante para el tema.
¿Cómo sueles realizar la copia de seguridad? ¿Cómo 'se enseña' a hacer copias de seguridad en miles de artículos? En general, si se necesita realizar una copia de seguridad de una base de datos no muy grande una vez, automáticamente escribiré algo como esto:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--bueno, CHECKSUM lo escribí solo para parecer más inteligenteY, en general, aquí se enumeran, probablemente, el 75-90% de todos los parámetros que comúnmente se mencionan en los artículos sobre copias de seguridad. Bueno, ahí están INIT, SKIP, etc. ¿Han ido a MSDN? ¿Han visto que hay opciones para llenar una pantalla y media? Yo también lo he visto...
Probablemente ya han comprendido que a continuación se hablará de los tres parámetros que quedaron en el primer bloque de código: BLOCKSIZE, BUFFERCOUNT y MAXTRANSFERSIZE. Aquí están sus descripciones de MSDN:
BLOCKSIZE = { blocksize | @ blocksize_variable } indica el tamaño del bloque físico en bytes. Se admiten tamaños de 512, 1024, 2048, 4096, 8192, 16 384, 32 768 y 65 536 bytes (64 KB). El valor por defecto es 65 536 para dispositivos de cinta y 512 para otros dispositivos. Normalmente, no es necesario especificar este parámetro, ya que la instrucción BACKUP elige automáticamente el tamaño de bloque que corresponde al dispositivo. La configuración explícita del tamaño de bloque anula la selección automática del tamaño de bloque.
BUFFERCOUNT = { buffercount | @ buffercount_variable } determina el número total de buffers de entrada/salida que se utilizarán para la operación de copia de seguridad. Se puede especificar cualquier valor entero positivo, sin embargo, un número grande de buffers puede provocar un error de falta de memoria debido a un espacio de direcciones virtual excesivo en el proceso Sqlservr.exe.
El volumen total del espacio utilizado por los buffers se determina mediante la siguiente fórmula:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } indica el mayor volumen de paquete de datos en bytes para el intercambio de datos entre SQL Server y el medio de respaldo. Se admiten valores que son múltiplos de 65 536 bytes (64 KB), hasta 4 194 304 bytes (4 MB).
Juro que leí esto antes, pero ni me imaginaba el impacto en el rendimiento que pueden tener. Además, parece que debo hacer una especie de 'salida del armario' y admitir que incluso ahora no entiendo del todo qué es lo que hacen exactamente. Tal vez debería leer más sobre la entrada/salida buffered y el funcionamiento con discos duros. Alguna vez lo haré, pero por ahora solo puedo escribir un script que verifique cómo estos valores afectan la velocidad a la que se realiza una copia de seguridad.
He creado una pequeña base de datos, con un tamaño de alrededor de 10 GB, la he colocado en un SSD, y el directorio para las copias de seguridad lo he colocado en un HDD.
Estoy creando una tabla temporal para almacenar resultados (en mi caso, no es temporal, para que se pueda investigar los resultados más a fondo, pero ustedes decidan):
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 de funcionamiento del script es simple: bucles anidados, cada uno de los cuales cambia el valor de un parámetro, paso esos parámetros al comando BACKUP, guardo la última entrada de la historia de msdb.dbo.backupset, elimino el archivo de respaldo y paso a la siguiente iteración. Dado que los datos de la ejecución del respaldo se toman de backupset, la precisión se pierde un poco (no hay fracciones de segundo), pero eso lo superaremos.
Primero, es necesario habilitar el uso de xp_cmdshell para eliminar los respaldos (no olviden desactivarlo después si no lo necesitan):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOY, 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);
/* VALORES DE BLOCKSIZE */
DECLARE @bs int = 4096,
@max_bs int = 65536;
/* VALORES DE BUFFERCOUNT */
DECLARE @bc int = 7,
@min_bc int = 7,
@max_bc int = 800;
/* VALORES DE MAXTRANSFERSIZE */
DECLARE @ts int = 524288, --512KB, por defecto = 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;
ENDSi necesitan aclaraciones sobre lo que está sucediendo aquí, escriban en los comentarios o en un mensaje privado. Por ahora, solo hablaré sobre los parámetros que paso a BACKUP DATABASE.
Para BLOCKSIZE tenemos una lista de valores «cerrada», y no pude realizar una copia de seguridad con BLOCKSIZE < 4KB. MAXTRANSFERSIZE es cualquier número múltiplo de 64KB, desde 64KB hasta 4MB. Por defecto, en mi sistema es 1024KB, opté por 512 — 1024 — 2048 — 4096.
Fue más complicado con BUFFERCOUNT — puede ser cualquier número positivo, pero en el enlace dice . También se menciona cómo obtener información sobre con qué BUFFERCOUNT se realiza realmente la copia de seguridad — en mi caso es 7. No tenía sentido reducirlo, y se descubrió un límite superior de manera empírica — con BUFFERCOUNT = 896 y MAXTRANSFERSIZE = 4194304 la copia de seguridad falló con un error (del que se habla en el enlace anterior):
Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE está terminando anormalmente.
Msg 701, Level 17, State 123, Line 7 No hay suficiente memoria del sistema en el grupo de recursos 'default' para ejecutar esta consulta.
Para comparar, primero mostraré los resultados de la ejecución de la copia de seguridad sin especificar ningún parámetro en absoluto:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Bueno, es una copia de seguridad y listo:
Se procesaron 1070072 páginas para la base de datos ‘bt’, archivo ‘bt’ en archivo 1.
Se procesaron 2 páginas para la base de datos ‘bt’, archivo ‘bt_log’ en archivo 1.
BACKUP DATABASE procesó exitosamente 1070074 páginas en 53.171 segundos (157.227 MB/sec).
El propio script que prueba los parámetros funcionó en un par de horas, todas las medidas en . Aquí están los resultados que tienen los tres mejores tiempos de ejecución (traté de hacer un gráfico bonito, pero, en la publicación, deberé conformarme con una tabla, y en los comentarios añadido ).
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, aquí hay un comentario muy importante de de :
se puede afirmar con confianza que la relación entre los parámetros y la velocidad de la copia de seguridad en estos rangos de valores es aleatoria, no hay ninguna regularidad. Pero alejarse de los parámetros integrados, evidentemente, ha influido positivamente en el resultado.
Es decir, solo mediante la gestión de los parámetros estándar de BACKUP se logró una mejora en el tiempo de realización de la copia de seguridad de 2 veces: 26 segundos, frente a 53 al principio. No está mal, ¿verdad? Pero necesitamos ver qué sucede con la recuperación. ¿Y si ahora la recuperación toma 4 veces más?
Para comenzar, midamos cuánto dura la recuperación de la copia de seguridad con la configuración por defecto:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Bueno, esto ya lo saben, los caminos, replace o no replace, recovery o no recovery. Y yo lo ejecuto así:
Se procesaron 1070072 páginas para la base de datos ‘bt’, archivo ‘bt’ en archivo 1.
Se procesaron 2 páginas para la base de datos ‘bt’, archivo ‘bt_log’ en archivo 1.
RESTORE DATABASE se procesó exitosamente 1070074 páginas en 40.752 segundos (205.141 MB/segundo).
Ahora intentaré restaurar las copias de seguridad realizadas con BLOCKSIZE, BUFFERCOUNT y MAXTRANSFERSIZE modificados.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304RESTORE DATABASE se procesó exitosamente 1070074 páginas en 32.283 segundos (258.958 MB/segundo).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304RESTORE DATABASE se procesó exitosamente 1070074 páginas en 32.682 segundos (255.796 MB/segundo).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152RESTORE DATABASE se procesó exitosamente 1070074 páginas en 32.091 segundos (260.507 MB/segundo).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304RESTORE DATABASE se procesó exitosamente 1070074 páginas en 32.401 segundos (258.015 MB/segundo).
La instrucción RESTORE DATABASE no cambia al restaurar, estos parámetros no se especifican, SQL Server los determina automáticamente a partir de la copia de seguridad. Y se puede ver que incluso al restaurar puede haber una mejora, siendo prácticamente un 20% más rápido (honestamente, no dediqué mucho tiempo a la restauración, probé algunas de las copias de seguridad más 'rápidas' y comprobé que no hay empeoramiento).
Por si acaso aclaro, aquí no se describen parámetros óptimos para todos. Los parámetros óptimos para ti solo se pueden obtener mediante pruebas. Yo obtuve estos resultados, tú obtendrás otros. Pero verás que tus copias de seguridad pueden 'ajustarse' y realmente pueden formarse y desplegarse más rápido.
Además, recomiendo encarecidamente leer la documentación completa, porque puede haber matices específicos para tu sistema.
Ya que comencé a escribir sobre copias de seguridad, quiero escribir también sobre otra 'optimización' que se encuentra más a menudo que el 'tuning' de parámetros (que, según entiendo, al menos algunas herramientas de copia de seguridad la utilizan, posiblemente junto con los parámetros descritos anteriormente), pero que aún no se ha descrito en Habr.
Si miras la segunda línea de la documentación, justo debajo de BACKUP DATABASE, allí vemos:
TO [ ,...n ]¿Qué crees que sucederá si se especifican varios backup_device? El sintaxis lo permite. Y habrá una cosa muy interesante: la copia de seguridad simplemente se 'dispersará' entre varios dispositivos. Es decir, cada 'dispositivo' por separado será inútil, se pierde uno, se pierde toda la copia. Pero, ¿cómo afectará esta dispersión 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;¡Dios santo, ¿qué está pasando aquí?
Se procesaron 1070072 páginas para la base de datos ‘bt’, archivo ‘bt’ en archivo 1.
Procesadas 2 páginas para la base de datos ‘bt’, archivo ‘btlog’ en el archivo 1.
COPIA DE SEGURIDAD DE LA BASE DE DATOS procesada correctamente, 1070074 páginas en 40.092 segundos (208.519 MB/seg).
¿La copia de seguridad se realizó un 25% más rápido por sí sola? ¿Y si se agregan un par de dispositivos más?
COPIA DE SEGURIDAD DE LA BASE DE DATOS [bt]
A
DISCO = 'D:SQLServerbackupbt1.bak',
DISCO = 'D:SQLServerbackupbt2.bak',
DISCO = 'D:SQLServerbackupbt3.bak',
DISCO = 'D:SQLServerbackupbt4.bak'
CON COMPRESIÓN;COPIA DE SEGURIDAD DE LA BASE DE DATOS procesada correctamente, 1070074 páginas en 34.234 segundos (244.200 MB/seg).
En total, hay un ahorro de aproximadamente un 35% en el tiempo de realización de la copia de seguridad solo porque se escribe en 4 archivos a la vez en un mismo disco. He probado con más dispositivos, pero en mi portátil no hubo ganancia, lo óptimo son 4 dispositivos. Para ustedes, no sé, hay que verificar. Y, por cierto, si ustedes tienen estos dispositivos — son discos realmente diferentes, felicitaciones, el ahorro debería ser aún mayor.
Ahora hablemos de cómo restaurar esta maravilla. Para esto, hay que modificar el comando de restauración y enumerar todos los dispositivos:
RESTABLECER BASE DE DATOS [bt]
DE
DISCO = 'D:SQLServerbackupbt1.bak',
DISCO = 'D:SQLServerbackupbt2.bak',
DISCO = 'D:SQLServerbackupbt3.bak',
DISCO = 'D:SQLServerbackupbt4.bak'
CON REEMPLAZAR, RECUPERACIÓN;RESTABLECER BASE DE DATOS procesada correctamente, 1070074 páginas en 38.027 segundos (219.842 MB/seg).
Un poco más rápido, pero más o menos en el mismo rango, no es significativo. En general, la copia de seguridad se realiza más rápido, y la restauración es igual — ¿éxito? Para mí, es un éxito bastante decente. Es importante, así que lo repetiré — si usted pierde siquiera uno de estos archivos — pierde toda la copia de seguridad..
Si se examina la información en el registro sobre la copia de seguridad generada mediante los Trace Flags 3213 y 3605, se puede notar que, al hacer copias de seguridad en varios dispositivos, al menos se incrementa la cantidad de BUFFERCOUNT. Probablemente, se pueden intentar ajustar los parámetros más óptimos para BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, pero no me salió a la primera, y no tenía ganas de realizar nuevamente ese tipo de pruebas, especialmente con diferentes cantidades de archivos. Además, los discos son preciados. Si desean organizar tal prueba en su lugar, no es difícil modificar el script.
Al final, hablemos del precio. Si la copia de seguridad se realiza en paralelo con el trabajo de los usuarios, es necesario abordar la prueba con mucha responsabilidad, ya que si la copia de seguridad se hace más rápido, los discos se cargan más, la carga en el procesador aumenta (se debe comprimir todo esto sobre la marcha), y, como resultado, se reduce la capacidad de respuesta general del sistema.
Bromas aparte, entiendo perfectamente que no he hecho ninguna revelación. Lo que está escrito arriba es simplemente una demostración de cómo se pueden optimizar los parámetros para realizar copias de seguridad.
Recuerde que todo lo que haga, lo hace bajo su propio riesgo. Verifique sus copias de seguridad y no olvide el DBCC CHECKDB.
Fuente: habr.com
