Espera! Espera! És cert que aquest no és un altre article sobre els tipus de còpies de seguretat d'SQL Server. Ni tan sols parlaré de les diferències entre els models de recuperació i de com fer front a un tronc massa gran.
Potser (només potser), després de llegir aquesta publicació, podreu assegurar-vos que la còpia de seguretat que s'elimina mitjançant mitjans estàndard s'eliminarà demà a la nit, bé, 1.5 vegades més ràpid. I només pel fet que utilitzeu una mica més de paràmetres de BACKUP DATABASE.
Si el contingut de la publicació era obvi per a tu, ho sento. Vaig llegir tot el que va arribar a Google per a la frase "habr sql server backup", i en cap article no vaig trobar cap menció del fet que el temps de còpia de seguretat es pugui influir d'alguna manera mitjançant paràmetres.
De seguida cridaré la vostra atenció sobre el comentari d'Alexander Gladchenko ():
No canvieu mai els paràmetres BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE en producció. Estan fets només per escriure aquests articles. A la pràctica, desfer-te dels problemes de memòria en molt poc temps.
Per descomptat, seria fantàstic ser el més intel·ligent i publicar contingut exclusiu, però, malauradament, no és així. Hi ha articles/publicacions tant en anglès com en rus (sempre estic confós sobre com anomenar-los correctament) dedicats a aquest tema. Aquests són alguns dels que em vaig trobar: , , .
Per tant, per començar, adjuntaré una sintaxi de BACKUP lleugerament reduïda (per cert, vaig escriure més amunt sobre BACKUP DATABASE, però tot això s'aplica tant a la còpia de seguretat del registre de transaccions com a la còpia de seguretat diferencial, però potser amb un efecte menys evident):
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 }
<...><…> - vol dir que hi havia alguna cosa, però l'he eliminat perquè ara no és rellevant per al tema.
Com sol fer una còpia de seguretat? Com "ensenyen" com fer còpies de seguretat en milers de milions d'articles? En general, si necessito fer una còpia de seguretat única d'alguna base de dades no molt gran, escriuré automàticament alguna cosa com això:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееI, en general, aquí es mostren probablement el 75-90% de tots els paràmetres que s'esmenten habitualment als articles sobre còpies de seguretat. Bé, també hi ha INIT, SKIP. Heu visitat MSDN? Heu vist que hi ha opcions per a una pantalla i mitja? També vaig veure...
Probablement ja us heu adonat que més endavant parlarem dels tres paràmetres que quedaven al primer bloc de codi: BLOCKSIZE, BUFFERCOUNT i MAXTRANSFERSIZE. Aquestes són les seves descripcions de MSDN:
MIDA DE BLOC = { mida de bloc | @ blocksize_variable } - indica la mida del bloc físic en bytes. Les mides admeses són 512, 1024, 2048, 4096, 8192, 16, 384 i 32 bytes (768 KB). El valor predeterminat és 65 per a dispositius de cinta i 536 per a altres dispositius. Normalment, aquest paràmetre no és necessari perquè la instrucció BACKUP selecciona automàticament la mida de bloc adequada per al dispositiu. L'establiment de la mida del bloc anul·la explícitament la selecció automàtica de la mida del bloc.
BUFFERCOUNT = { recompte de buffer | @ buffercount_variable } - Defineix el nombre total de buffers d'E/S que s'utilitzaran per a l'operació de còpia de seguretat. Podeu especificar qualsevol valor enter positiu, però un gran nombre de memòries intermèdies pot provocar un error de falta de memòria a causa de l'excés d'espai d'adreces virtuals al procés Sqlservr.exe.
La quantitat total d'espai utilitzat per les memòries intermèdies es determina mitjançant la fórmula següent:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { mida màxima de la transferència | @ maxtransfersize_variable } especifica la mida més gran del paquet de dades, en bytes, per intercanviar entre SQL Server i el suport del conjunt de còpia de seguretat. S'admeten múltiples de 65 bytes (536 KB) fins a 64 bytes (4 MB).
Ho juro, ho he llegit abans, però mai se m'ha passat pel cap l'impacte que podrien tenir en la productivitat. A més, pel que sembla, he de fer una mena de "coming out" i admetre que encara ara no entenc del tot què estan fent exactament. Probablement necessito llegir més sobre l'E/S en memòria intermèdia i el treball amb un disc dur. Algun dia ho faré, però de moment només puc escriure un script que comprovarà com afecten aquests valors a la velocitat a la qual es fa la còpia de seguretat.
Vaig fer una base de dades petita, d'uns 10 GB de mida, la vaig posar a l'SSD i vaig posar el directori de còpies de seguretat a l'HDD.
Creo una taula temporal per emmagatzemar els resultats (no la tinc temporal, així que puc aprofundir en els resultats amb més detall, però tu decideixes tu mateix):
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 principi de l'script és senzill: bucles imbricats, cadascun dels quals canvia el valor d'un paràmetre, inseriu aquests paràmetres a l'ordre BACKUP, deseu l'últim registre amb l'historial de msdb.dbo.backupset, suprimiu el fitxer de còpia de seguretat i la següent iteració . Com que les dades d'execució de la còpia de seguretat es prenen del conjunt de còpies de seguretat, la precisió es perd una mica (no hi ha fraccions de segons), però sobreviurem a això.
Primer heu d'habilitar xp_cmdshell per eliminar les còpies de seguretat (després no us oblideu de desactivar-lo si no ho necessiteu):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GOBé, de fet:
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;
ENDSi de sobte necessiteu un aclariment sobre el que està passant aquí, escriviu als comentaris o al MP. De moment, només us parlaré dels paràmetres que he posat a BACKUP DATABASE.
Per a BLOCKSIZE tenim una llista de valors "tancada" i no vaig fer una còpia de seguretat amb BLOCKSIZE < 4KB. MAXTRANSFERSIZE qualsevol número que sigui un múltiple de 64 KB, de 64 KB a 4 MB. El valor predeterminat del meu sistema és 1024 KB, vaig agafar 512 - 1024 - 2048 - 4096.
Va ser més difícil amb BUFFERCOUNT: pot ser qualsevol número positiu, però l'enllaç diu . També diu com obtenir informació sobre amb quin BUFFERCOUNT es fa realment la còpia de seguretat; per a mi és 7. No tenia sentit reduir-la i el límit superior es va descobrir experimentalment: amb BUFFERCOUNT = 896 i MAXTRANSFERSIZE = 4194304, la còpia de seguretat va caure amb un error (sobre el qual està escrit a l'enllaç anterior):
Msg 3013, nivell 16, estat 1, línia 7 BACKUP DATABASE està acabant de manera anormal.
Msg 701, nivell 17, estat 123, línia 7 No hi ha prou memòria del sistema a l'agrupació de recursos "per defecte" per executar aquesta consulta.
Com a comparació, primer mostraré els resultats d'executar una còpia de seguretat sense especificar cap paràmetre:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;Bé, còpia de seguretat i còpia de seguretat:
S'han processat 1070072 pàgines per a la base de dades 'bt', fitxer 'bt' al fitxer 1.
S'han processat 2 pàgines per a la base de dades "bt", fitxer "bt_log" al fitxer 1.
BACKUP DATABASE ha processat correctament 1070074 pàgines en 53.171 segons (157.227 MB/s).
El guió en si, provant els paràmetres, va funcionar en un parell d'hores, totes les mesures estaven dins . I aquí teniu una selecció de resultats amb els tres millors temps d'execució (he intentat fer un gràfic maco, però a la publicació m'hauré de conformar amb una taula, i als comentaris va afegir ).
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ó, una nota molt important de d' :
Podem dir amb confiança que la relació entre els paràmetres i la velocitat de còpia de seguretat dins d'aquests rangs de valors és aleatòria, no hi ha cap patró. Però allunyar-se dels paràmetres integrats, òbviament, va tenir un bon efecte en el resultat
Aquells. Només gestionant els paràmetres estàndard de BACKUP es va augmentar el doble de temps d'eliminació de la còpia de seguretat: 2 segons, enfront dels 26 al principi. Això no està malament, oi? Però hem de veure què passa amb la restauració. Què passa si ara triga 53 vegades més a recuperar-se?
Primer, mesurem quant de temps es triga a restaurar una còpia de seguretat amb la configuració predeterminada:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;Bé, tu mateix saps que, les maneres hi són, substituir no és substituir, recuperar no és recuperar. I ho faig així:
S'han processat 1070072 pàgines per a la base de dades 'bt', fitxer 'bt' al fitxer 1.
S'han processat 2 pàgines per a la base de dades "bt", fitxer "bt_log" al fitxer 1.
RESTORE DATABASE ha processat correctament 1070074 pàgines en 40.752 segons (205.141 MB/s).
Ara intentaré restaurar les còpies de seguretat fetes amb BLOCKSIZE, BUFFERCOUNT i MAXTRANSFERSIZE canviats.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304RESTORE DATABASE ha processat correctament 1070074 pàgines en 32.283 segons (258.958 MB/s).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304RESTORE DATABASE ha processat correctament 1070074 pàgines en 32.682 segons (255.796 MB/s).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152RESTORE DATABASE ha processat correctament 1070074 pàgines en 32.091 segons (260.507 MB/s).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304RESTORE DATABASE ha processat correctament 1070074 pàgines en 32.401 segons (258.015 MB/s).
La instrucció RESTORE DATABASE no canvia durant la recuperació; aquests paràmetres no s'especifiquen; el mateix SQL Server els determina a partir de la còpia de seguretat. I és clar que fins i tot amb la recuperació pot haver-hi un guany, gairebé un 20% més ràpid (Per ser sincer, no vaig dedicar gaire temps a la recuperació, vaig revisar diverses de les còpies de seguretat "més ràpides" i em vaig assegurar que no hi hagués deteriorament.).
Per si de cas, permeteu-me aclarir que aquests no són alguns paràmetres òptims per a tothom. Només podeu obtenir els paràmetres òptims per a vosaltres mateixos provant. He obtingut aquests resultats, en obtindreu de diferents. Però veieu que podeu "ajustar" les vostres còpies de seguretat i que realment es poden formar i desplegar més ràpidament.
També us recomano molt que llegiu la documentació sencera, perquè pot haver-hi matisos específics del vostre sistema.
Des que vaig començar a escriure sobre còpies de seguretat, vull escriure immediatament sobre una "optimització" més, que és més habitual que els paràmetres d'"ajustament" (segons tinc entès, l'utilitzen almenys algunes utilitats de còpia de seguretat, potser juntament amb els paràmetres). descrit anteriorment), però encara no s'ha descrit a Habré tampoc.
Si mirem la segona línia de la documentació, just a sota de BACKUP DATABASE, hi veiem:
TO <backup_device> [ ,...n ]Què creieu que passarà si especifiqueu diversos dispositius de seguretat? La sintaxi ho permet. I passarà una cosa molt interessant: la còpia de seguretat simplement s'"estendrà" per diversos dispositius. Aquells. cada "dispositiu" individualment serà inútil, es perdrà un, perdrà tota la còpia de seguretat. Però, com afectarà aquesta difusió a la velocitat de còpia de seguretat?
Intentem fer una còpia de seguretat en dos "dispositius" que es troben un al costat de l'altre a la mateixa carpeta:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Pares del món, per què es fa això?
S'han processat 1070072 pàgines per a la base de dades 'bt', fitxer 'bt' al fitxer 1.
S'han processat 2 pàgines per a la base de dades "bt", el fitxer "bt"registre' al fitxer 1.
BACKUP DATABASE ha processat correctament 1070074 pàgines en 40.092 segons (208.519 MB/s).
La còpia de seguretat es va fer un 25% més ràpida de seguida? Què passa si afegim un parell de dispositius 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 ha processat correctament 1070074 pàgines en 34.234 segons (244.200 MB/s).
En total, el guany és al voltant del 35% del temps de fer una còpia de seguretat només a causa del fet que la còpia de seguretat s'escriu en 4 fitxers en un disc alhora. Vaig comprovar un nombre més gran: no hi ha cap guany al meu ordinador portàtil, de manera òptima: 4 dispositius. Per a tu, no ho sé, has de comprovar. Bé, per cert, si teniu aquests dispositius, són discos realment diferents, felicitats, el guany hauria de ser encara més significatiu.
Ara parlem de com recuperar aquesta felicitat. Per fer-ho, haureu de canviar l'ordre de recuperació i llistar tots els dispositius:
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 ha processat correctament 1070074 pàgines en 38.027 segons (219.842 MB/s).
Una mica més ràpid, però a prop, no significatiu. En general, la còpia de seguretat s'elimina més ràpidament i es restaura de la mateixa manera: èxit? Pel que fa a mi, és tot un èxit. Això és important, així que repeteixo - si vostè si perds almenys un d'aquests fitxers, perdràs tota la còpia de seguretat.
Si mireu al registre la informació de còpia de seguretat que es mostra amb els indicadors de seguiment 3213 i 3605, notareu que quan feu una còpia de seguretat a diversos dispositius, com a mínim augmenta el nombre de BUFFERCOUNT. Probablement, podeu provar de seleccionar paràmetres més òptims per a BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, però no vaig tenir èxit de seguida i em feia massa mandra per tornar a dur a terme aquestes proves, però per a un nombre diferent de fitxers. I és una llàstima les rodes. Si voleu organitzar aquestes proves a casa, no és difícil refer el guió.
Finalment, parlem del preu. Si la còpia de seguretat s'elimina en paral·lel amb el treball dels usuaris, cal tenir un enfocament molt responsable a les proves, perquè si la còpia de seguretat s'elimina més ràpidament, els discs es tensin més, la càrrega del processador augmenta (encara cal comprimir sobre la marxa) i, en conseqüència, la capacitat de resposta global del sistema disminueix.
És broma, però entenc perfectament que no vaig fer cap revelació. El que s'escriu més amunt és simplement una demostració de com podeu seleccionar els paràmetres òptims per fer còpies de seguretat.
Recordeu que tot el que feu es fa sota el vostre risc i risc. Comproveu les vostres còpies de seguretat i no us oblideu de DBCC CHECKDB.
Font: www.habr.com