Wachtsje! Wachtsje! Wier, dit is gjin oar artikel oer soarten SQL Server-backups. Ik sil net iens prate oer de ferskillen tusken herstelmodellen en hoe't jo omgean mei in oergroeide log.
Miskien (krekt miskien), nei it lêzen fan dit berjocht, kinne jo der wis fan wêze dat de reservekopy dy't fan jo is ferwidere mei standert middels moarn nacht, no, 1.5 kear flugger wurdt fuortsmiten. En allinich troch it feit dat jo in bytsje mear BACKUP DATABASE-parameters brûke.
As de ynhâld fan 'e post wie dúdlik foar jo, it spyt my. Ik lês alles dat Google krige foar de útdrukking "habr sql server backup", en yn gjin inkeld artikel fûn ik gjin melding fan it feit dat de reservekopytiid op ien of oare manier kin wurde beynfloede troch parameters.
Ik sil jo oandacht fuortendaliks lûke op it kommentaar fan Alexander Gladchenko ():
Feroarje nea de parameters BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE yn produksje. Se wurde makke allinnich foar it skriuwen fan sokke artikels. Yn 'e praktyk sille jo ûnthâldproblemen yn gjin tiid kwytreitsje.
It soe fansels cool wêze om de tûkste te wêzen en eksklusive ynhâld te pleatsen, mar spitigernôch is dit net it gefal. D'r binne sawol Ingelsktalige as Russysktalige artikels / berjochten (ik bin altyd yn 'e war oer wat ik se korrekt neame moat) wijd oan dit ûnderwerp. Hjir binne guon fan 'e dy't ik tsjinkaam: , , .
Dat, om te begjinnen, sil ik in wat stripte BACKUP-syntaksis taheakje fan (troch de manier, ik skreau hjirboppe oer BACKUP DATABASE, mar dit alles jildt foar sawol transaksjelogboek-backup as differinsjaal backup, mar miskien mei in minder fanselssprekkend effekt):
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 }
<...><...> - it betsjut dat d'r wat wie, mar ik haw it fuorthelle, om't it no net relevant is foar it ûnderwerp.
Hoe nimme jo normaal in reservekopy? Hoe "leare" se hoe't jo backups kinne nimme yn miljarden artikels? Yn 't algemien, as ik in ienmalige reservekopy moat meitsje fan wat net heul grutte database, sil ik automatysk sa'n ding skriuwe:
BACKUP DATABASE smth
TO DISK = 'D:Backupsmth.bak'
WITH STATS = 10, CHECKSUM, COMPRESSION, COPY_ONLY;
--ладно, CHECKSUM я написал только чтобы казаться умнееEn, yn 't algemien, binne wierskynlik 75-90% fan alle parameters dy't normaal wurde neamd yn artikels oer backups hjir neamd. No, der is ek INIT, SKIP. Hawwe jo MSDN besocht? Hawwe jo sjoen dat d'r opsjes binne foar ien en in heal skerm? Ik seach ek...
Jo hawwe wierskynlik al realisearre dat wy fierder sille prate oer de trije parameters dy't bleaunen yn it earste blok fan koade - BLOCKSIZE, BUFFERCOUNT en MAXTRANSFERSIZE. Hjir binne har beskriuwingen fan MSDN:
BLOCKSIZE = { blokgrutte | @ blocksize_fariabele } - jout de fysike blokgrutte yn bytes oan. Stipe grutte binne 512, 1024, 2048, 4096, 8192, 16, 384 en 32 bytes (768 KB). De standertwearde is 65 foar tape-apparaten en 536 foar oare apparaten. Typysk is dizze parameter net nedich, om't de BACKUP-statement automatysk de passende blokgrutte foar it apparaat selektearret. It ynstellen fan de blokgrutte oerskriuwt de automatyske seleksje fan blokgrutte eksplisyt.
BUFFERCOUNT = { buffercount | @ buffercount_fariabele } - Beskiedt it totale oantal I/O-buffers dy't brûkt wurde foar de reservekopyoperaasje. Jo kinne elke positive heule getalwearde oantsjutte, mar in grut oantal buffers kin in flater sûnder ûnthâld feroarsaakje troch tefolle firtuele adresromte yn it Sqlservr.exe-proses.
De totale hoemannichte romte brûkt troch buffers wurdt bepaald troch de folgjende formule:
BUFFERCOUNT * MAXTRANSFERSIZE.
MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } spesifisearret de grutste datapakketgrutte, yn bytes, om te wikseljen tusken SQL Server en de backupset media. Mearfâldichheden fan 65 bytes (536 KB) oant 64 bytes (4 MB) wurde stipe.
Ik swar - ik haw dit earder lêzen, mar it is my noait opfallen hoefolle ynfloed se kinne hawwe op produktiviteit. Boppedat moat ik blykber in soarte fan "útkomme" meitsje en tajaan dat ik no sels net folslein begryp wat se krekt dogge. Ik moat wierskynlik mear lêze oer buffered I / O en wurkje mei in hurde skiif. Ienris sil ik dit dwaan, mar foar no kin ik gewoan in skript skriuwe dat sil kontrolearje hoe't dizze wearden de snelheid beynfloedzje wêrop de reservekopy wurdt nommen.
Ik makke in lytse databank, sawat 10 GB yn grutte, sette it op 'e SSD, en sette de map foar backups op' e HDD.
Ik meitsje in tydlike tabel om de resultaten op te slaan (ik haw it net tydlik, dus ik kin mear yn 'e resultaten grave, mar jo beslute sels):
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
);It prinsipe fan it skript is ienfâldich - nêste loops, wêrfan elk de wearde fan ien parameter feroaret, ynfoegje dizze parameters yn it kommando BACKUP, bewarje de lêste record mei skiednis fan msdb.dbo.backupset, wiskje de reservekopybestân en de folgjende iteraasje . Sûnt de reservekopy útfiering gegevens wurdt nommen út de backupset, de krektens is wat ferlern (der binne gjin fraksjes fan sekonden), mar wy sille oerlibje dit.
Earst moatte jo xp_cmdshell ynskeakelje om backups te wiskjen (ferjit it dan net út te skeakeljen as jo it net nedich binne):
EXEC sp_configure 'show advanced options', 1;
EXEC sp_configure 'xp_cmdshell', 1;
RECONFIGURE;
EXEC sp_configure 'show advanced options', 0;
GONo, eins:
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;
ENDAs jo ynienen ferdúdliking nedich hawwe oer wat hjir bart, skriuw dan yn 'e opmerkings of PM. Foar no sil ik jo allinich fertelle oer de parameters dy't ik yn BACKUP DATABASE set.
Foar BLOCKSIZE hawwe wy in "sletten" list mei wearden, en ik haw gjin reservekopy útfierd mei BLOCKSIZE <4KB. MAXTRANSFERSIZE elk getal dat is in mearfâldichheid fan 64KB - fan 64KB to 4MB. De standert op myn systeem is 1024KB, ik naam 512 - 1024 - 2048 - 4096.
It wie dreger mei BUFFERCOUNT - it kin elk posityf nûmer wêze, mar de keppeling seit . It seit ek hoe't jo ynformaasje krije oer hokker BUFFERCOUNT de reservekopy is eins makke mei - foar my is it 7. Der wie gjin punt om it te ferminderjen, en de boppegrins waard eksperiminteel ûntdutsen - mei BUFFERCOUNT = 896 en MAXTRANSFERSIZE = 4194304 foel de reservekopy mei in flater (oer hokker skreaun yn 'e boppesteande link):
Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE wurdt abnormaal beëinige.
Msg 701, Level 17, State 123, Line 7 Der is net genôch systeemûnthâld yn boarne pool 'standert' om dizze query út te fieren.
Foar fergeliking sil ik earst de resultaten sjen litte fan it útfieren fan in reservekopy sûnder parameters op te jaan:
BACKUP DATABASE [bt]
TO DISK = 'D:SQLServerbackupbt.bak'
WITH COMPRESSION;No, backup en backup:
Ferwurke 1070072 siden foar database 'bt', triem 'bt' op triem 1.
Ferwurke 2 siden foar database 'bt', triem 'bt_log' op triem 1.
BACKUP DATABASE mei súkses ferwurke 1070074 siden yn 53.171 sekonden (157.227 MB / sek).
It skript sels, testen fan de parameters, wurke yn in pear oeren, alle mjittingen wiene yn . En hjir is in seleksje fan resultaten mei de trije bêste útfieringstiden (ik besocht in moaie grafyk te meitsjen, mar yn 'e post sil ik it dwaan moatte mei in tabel, en yn' e kommentaren tafoege ).
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;
Oandacht, in tige wichtige notysje fan fan :
Wy kinne mei fertrouwen sizze dat de relaasje tusken de parameters en reservekopy snelheid binnen dizze berik fan wearden is willekeurich, der is gjin patroan. Mar fuortgean fan de ynboude parameters hie fansels in goed effekt op it resultaat
Dy. Allinne troch it behearen fan de standert BACKUP parameters wie in 2-fold winst yn reservekopy removal tiid: 26 sekonden, tsjin 53 oan it begjin. Dat is net slim, toch? Mar wy moatte sjen wat der bart mei de restauraasje. Wat as it no 4 kear langer duorret om te herstellen?
Litte wy earst mjitte hoe lang it duorret om in reservekopy te herstellen mei standertynstellingen:
RESTORE DATABASE [bt]
FROM DISK = 'D:SQLServerbackupbt.bak'
WITH REPLACE, RECOVERY;No, jo sels witte dat, de manieren binne der, ferfange is net ferfange, herstel is gjin herstel. En ik doch it sa:
Ferwurke 1070072 siden foar database 'bt', triem 'bt' op triem 1.
Ferwurke 2 siden foar database 'bt', triem 'bt_log' op triem 1.
RESTORE DATABASE hat 1070074 siden mei súkses ferwurke yn 40.752 sekonden (205.141 MB / sek).
No sil ik besykje backups te herstellen nommen mei feroare BLOCKSIZE, BUFFERCOUNT en MAXTRANSFERSIZE.
BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304RESTORE DATABASE hat 1070074 siden mei súkses ferwurke yn 32.283 sekonden (258.958 MB / sek).
BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304RESTORE DATABASE hat 1070074 siden mei súkses ferwurke yn 32.682 sekonden (255.796 MB / sek).
BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152RESTORE DATABASE hat 1070074 siden mei súkses ferwurke yn 32.091 sekonden (260.507 MB / sek).
BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304RESTORE DATABASE hat 1070074 siden mei súkses ferwurke yn 32.401 sekonden (258.015 MB / sek).
De RESTORE DATABASE statement feroaret net tidens herstel; dizze parameters wurde der net yn oantsjutte; SQL Server sels bepaalt se fanút de reservekopy. En it is dúdlik dat sels mei herstel in winst kin wêze - hast 20% rapper (Om earlik te wêzen, haw ik net folle tiid bestege oan herstel, ik rûn troch ferskate fan 'e "rapste" backups en soarge derfoar dat d'r gjin efterútgong wie).
Krekt yn gefal, lit my dúdlik meitsje dat dit net guon parameters binne dy't foar elkenien optimaal binne. Jo kinne allinich de optimale parameters foar josels krije troch te testen. Ik haw dizze resultaten krigen, jo sille ferskate krije. Mar jo sjogge dat jo jo backups kinne "ynstelle" en se kinne feitlik rapper foarmje en ynsette.
Ik riede ek sterk oan dat jo de dokumintaasje yn syn gehiel lêze, om't d'r nuânses kinne wêze spesifyk foar jo systeem.
Sûnt ik begon te skriuwen oer backups, wol ik fuortdaliks skriuwe oer noch ien "optimalisaasje", dy't faker is dan "tuning" parameters (foar safier't ik begryp, wurdt it brûkt troch op syn minst guon reservekopy-hulpprogramma's, miskien tegearre mei de parameters earder beskreaun), mar it is ek noch net op Habré beskreaun.
As wy nei de twadde rigel yn 'e dokumintaasje sjogge, rjochts ûnder BACKUP DATABASE, sjogge wy:
TO <backup_device> [ ,...n ]Wat tinke jo dat sil barre as jo ferskate backup_devices oantsjutte? De syntaksis lit it ta. En in heul ynteressant ding sil barre - de reservekopy sil gewoan "ferspraat" wurde oer ferskate apparaten. Dy. elk "apparaat" yndividueel sil wêze nutteloos, ferlern ien, ferlear de hiele reservekopy. Mar hoe sil sa'n smeer de backupsnelheid beynfloedzje?
Litte wy besykje in reservekopy te meitsjen op twa "apparaten" dy't njonken inoar lizze yn deselde map:
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak'
WITH COMPRESSION;Heiten fan 'e wrâld, wêrom wurdt dit dien?
Ferwurke 1070072 siden foar database 'bt', triem 'bt' op triem 1.
Ferwurke 2 siden foar database 'bt', triem 'bt'log' op triem 1.
BACKUP DATABASE mei súkses ferwurke 1070074 siden yn 40.092 sekonden (208.519 MB / sek).
Is de reservekopy krekt út it blau 25% flugger wurden? Wat as wy in pear mear apparaten tafoegje?
BACKUP DATABASE [bt]
TO
DISK = 'D:SQLServerbackupbt1.bak',
DISK = 'D:SQLServerbackupbt2.bak',
DISK = 'D:SQLServerbackupbt3.bak',
DISK = 'D:SQLServerbackupbt4.bak'
WITH COMPRESSION;BACKUP DATABASE mei súkses ferwurke 1070074 siden yn 34.234 sekonden (244.200 MB / sek).
Yn totaal is de winst sawat 35% fan 'e tiid fan it nimmen fan in reservekopy allinich troch it feit dat de reservekopy wurdt skreaun nei 4 bestannen op ien skiif tagelyk. Ik kontrolearre in grutter oantal - der is gjin winst op myn laptop, optimaal - 4 apparaten. Foar jo - ik wit it net, jo moatte kontrolearje. No, trouwens, as jo dizze apparaten hawwe - dit binne echt ferskillende skiven, lokwinsken, de winst moat noch wichtiger wêze.
No litte wy prate oer hoe te herstellen dit lok. Om dit te dwaan, moatte jo it herstelkommando feroarje en alle apparaten listje:
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 hat 1070074 siden mei súkses ferwurke yn 38.027 sekonden (219.842 MB / sek).
In bytsje flugger, mar earne tichtby, net signifikant. Yn 't algemien wurdt de reservekopy flugger fuortsmiten, mar op deselde manier restaurearre - sukses? Wat my oanbelanget, it is nochal in súkses. Dit is wichtich, dus ik werhelje - as jo as jo op syn minst ien fan dizze triemmen ferlieze, ferlieze jo de folsleine reservekopy.
As jo sjogge yn it log op de reservekopy ynformaasje werjûn mei help fan Trace Flags 3213 en 3605, do silst merken dat by reservekopy nei ferskate apparaten, op syn minst it oantal BUFFERCOUNT tanimt. Wierskynlik kinne jo besykje te selektearjen mear optimale parameters foar BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, mar ik slagge net daliks, en ik wie te lui om te fieren sokke testen wer, mar foar in oar oantal triemmen. En it is spitich oer de tsjillen. As jo soksoarte testen thús wolle organisearje, is it net dreech om it skript opnij te meitsjen.
As lêste, litte wy prate oer priis. As de reservekopy wurdt fuorthelle parallel mei it wurk fan de brûkers, dan moatte nimme in hiel ferantwurde oanpak fan testen, want as de reservekopy wurdt fuortsmiten flugger, de skiven wurde mear spand, de lading op de prosessor nimt ta (jo moatte noch komprimearje it on the fly), en dêrtroch nimt de totale responsiviteit fan it systeem ôf.
In grapke, mar ik begryp goed dat ik gjin iepenbieringen dien haw. Wat hjirboppe skreaun is is gewoan in demonstraasje fan hoe't jo de optimale parameters kinne selektearje foar it nimmen fan backups.
Unthâld dat alles wat jo dogge wurdt dien op jo eigen risiko en risiko. Kontrolearje jo backups en ferjit net oer DBCC CHECKDB.
Boarne: www.habr.com