🥇MS SQL Server: BACKUP ing steroid

Enteni! Enteni! Bener, iki dudu artikel liyane babagan jinis serep SQL Server. Aku malah ora bakal pirembagan bab beda antarane model Recovery lan carane menehi hasil karo log overgrown.

Mbok (mung mbok menawa), sawise maca kirim iki, sampeyan bakal bisa kanggo mesthekake yen serep sing dibusak saka sampeyan nggunakake cara standar bakal dibusak sesuk wengi, uga, 1.5 kaping luwih cepet. Lan mung amarga sampeyan nggunakake paramèter DATABASE BACKUP sing luwih cilik.

Yen isi kiriman kasebut jelas kanggo sampeyan, aku njaluk ngapura. Aku maca kabeh sing Google entuk kanggo tembung "habr sql server backup", lan ora ana artikel siji-sijia sing aku nemokake manawa wektu serep bisa dipengaruhi dening paramèter.

Aku bakal langsung narik perhatian sampeyan menyang komentar Alexander Gladchenko (@mssqlhelp):

Aja ngganti parameter BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE ing produksi. Iki digawe mung kanggo nulis artikel kasebut. Ing laku, sampeyan bakal nyisihaken saka masalah memori ing wektu cendhak.

Mesthi wae, dadi keren dadi konten sing paling cerdas lan ngirim konten eksklusif, nanging, sayangé, ora kaya ngono. Ana artikel / kiriman basa Inggris lan basa Rusia (aku mesthi bingung apa sing kudu diarani kanthi bener) sing ditrapake kanggo topik iki. Ing ngisor iki sawetara sing aku temokake: kaping, два, telu (ing sql.ru).

Dadi, kanggo miwiti, aku bakal masang sintaks BACKUP sing rada diudani saka MSDN (kanthi cara, aku nulis ing ndhuwur babagan BACKUP DATABASE, nanging kabeh iki ditrapake kanggo serep log transaksi lan serep diferensial, nanging bisa uga ana efek sing kurang jelas):

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 }
<...>

<...> - tegese ana sing ana, nanging aku mbusak amarga saiki ora cocog karo topik kasebut.

Carane sampeyan biasane njupuk serep? Kepiye carane "mulang" carane nggawe serep ing milyaran artikel? Umumé, yen aku kudu nggawe serep siji-wektu saka sawetara database ora gedhe banget, aku bakal kanthi otomatis nulis kaya iki:

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

Lan, ing umum, mbokmenawa 75-90% kabeh paramèter sing biasane kasebut ing artikel babagan serep didhaptar ing kene. Nah, ana uga INIT, SKIP. Apa sampeyan wis ngunjungi MSDN? Apa sampeyan ndeleng manawa ana pilihan kanggo siji lan setengah layar? Aku uga weruh ...

Sampeyan mbokmenawa wis temen maujud sing luwih kita bakal pirembagan bab telung paramèter sing tetep ing blok pisanan kode - BLOCKSIZE, BUFFERCOUNT lan MAXTRANSFERSIZE. Mangkene katrangan saka MSDN:

UKURAN BLOCKSIZE = { ukuran blok | @ blocksize_variable } - nuduhake ukuran blok fisik ing bita. Ukuran sing didhukung yaiku 512, 1024, 2048, 4096, 8192, 16, 384, lan 32 bita (768 KB). Nilai standar yaiku 65 kanggo piranti tape lan 536 kanggo piranti liyane. Biasane parameter iki ora perlu amarga statement BACKUP kanthi otomatis milih ukuran blok sing cocog kanggo piranti kasebut. Nyetel ukuran pemblokiran kanthi jelas ngindhari pilihan ukuran blok otomatis.

BUFFERCOUNT = { buffer count | @ buffercount_variable } - Nemtokake jumlah total buffer I / O sing bakal digunakake kanggo operasi serep. Sampeyan bisa nemtokake sembarang nilai integer positif, nanging nomer akeh buffer bisa nimbulaké kesalahan metu saka memori amarga papan alamat virtual gedhe banget ing proses Sqlservr.exe.

Jumlah total spasi sing digunakake dening buffer ditemtokake kanthi rumus ing ngisor iki: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } nemtokake ukuran paket data paling gedhe, ing bita, kanggo ijol-ijolan antarane SQL Server lan media set serep. Multiple saka 65 bita (536 KB) nganti 64 bita (4 MB) didhukung.

Sumpah - Aku wis maca iki sadurunge, nanging aku ora tau kelingan carane akeh saka impact padha bisa kanggo produktivitas. Menapa malih, ketoke, aku kudu nggawe jenis "metu" lan ngakoni yen saiki aku ora ngerti apa sing ditindakake. Aku mbokmenawa kudu maca liyane babagan buffered I / O lan nggarap hard drive. Ing sawijining dina aku bakal nindakake iki, nanging saiki aku mung bisa nulis skrip sing bakal mriksa kepiye nilai-nilai kasebut mengaruhi kacepetan serep kasebut.

Aku digawe database cilik, bab 10 GB ing ukuran, sijine ing SSD, lan sijine direktori kanggo serep ing HDD.

Aku nggawe tabel sauntara kanggo nyimpen asil (Aku ora duwe sak wentoro, supaya aku bisa dig menyang asil ing liyane rinci, nanging sampeyan arep dhewe):

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

Ing asas saka script punika prasaja - puteran nested, saben kang ngganti Nilai siji parameter, masang paramèter iki menyang printah BACKUP, nyimpen rekaman pungkasan karo sajarah saka msdb.dbo.backupset, mbusak file serep lan pengulangan sabanjuré . Wiwit data eksekusi serep dijupuk saka backupset, akurasi rada ilang (ora ana pecahan detik), nanging kita bakal slamet.

Pisanan sampeyan kudu ngaktifake xp_cmdshell kanggo mbusak serep (banjur aja lali mateni yen sampeyan ora butuh):

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

Inggih, bener:

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

Yen dumadakan sampeyan butuh klarifikasi babagan kedadeyan ing kene, tulis ing komentar utawa PM. Saiki, aku mung bakal ngandhani babagan paramèter sing daklebokake ing BACKUP DATABASE.

Kanggo BLOCKSIZE kita duwe "ditutup" dhaftar nilai, lan aku ora nindakake serep karo BLOCKSIZE <4KB. MAXTRANSFERSIZE sembarang nomer sing kelipatan saka 64KB - saka 64KB kanggo 4MB. Standar ing sistemku yaiku 1024KB, aku njupuk 512 - 1024 - 2048 - 4096.

Iku luwih angel karo BUFFERCOUNT - bisa dadi nomer positif, nanging link ngandika Kepiye cara ngitung ing BACKUP DATABASE lan kenapa nilai gedhe mbebayani?. Uga ngandika carane njaluk informasi bab kang BUFFERCOUNT serep bener digawe karo - kanggo kula iku 7. Ora ana titik kanggo ngurangi, lan watesan ndhuwur ditemokaké eksperimen - karo BUFFERCOUNT = 896 lan MAXTRANSFERSIZE = 4194304 serep ambruk karo kesalahan (bab sing ditulis ing link ndhuwur):

Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE mungkasi kanthi ora normal.

Msg 701, Level 17, State 123, Line 7 Ora cukup memori sistem ing sumber daya 'standar' kanggo mbukak pitakonan iki.

Kanggo mbandhingake, pisanan aku bakal nuduhake asil nggawe serep tanpa nemtokake parameter apa wae:

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

Inggih, serep lan serep:

Diproses 1070072 kaca kanggo database 'bt', file 'bt' ing file 1.

Diproses 2 kaca kanggo database 'bt', file 'bt_log' ing file 1.

BACKUP DATABASE kasil ngolah 1070074 kaca sajrone 53.171 detik (157.227 MB/detik).

Skrip kasebut dhewe, nguji paramèter, kerjane sajrone sawetara jam, kabeh pangukuran ana google spreadsheet. Lan ing kene ana pilihan asil kanthi telung eksekusi paling apik (Aku nyoba nggawe grafik sing apik, nanging ing kiriman aku kudu nggawe tabel, lan ing komentar. @campuran ditambahake grafis banget kelangan).

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;

Manungsa waé, cathetan penting banget saka @campuran saka komentar:

Kita kanthi yakin bisa ujar manawa hubungan antarane paramèter lan kacepetan serep ing kisaran nilai kasebut acak, ora ana pola. Nanging obah saka paramèter dibangun ing temenan wis efek apik ing asil

Sing. Mung kanthi ngatur paramèter BACKUP standar ana gain 2-melu ing wektu aman serep: 26 detik, mungsuh 53 ing wiwitan. Iku ora ala, ta? Nanging kita kudu ndeleng apa sing kedadeyan karo pemugaran. Apa yen saiki butuh 4 kaping maneh kanggo pulih?

Pisanan, ayo ngukur suwene wektu kanggo mulihake serep kanthi setelan gawan:

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

Lha kowe dhewe ngerti, cara-carane ana, ganti ora ganti, pulih dudu pulih. Lan aku nindakake kaya iki:

Diproses 1070072 kaca kanggo database 'bt', file 'bt' ing file 1.

Diproses 2 kaca kanggo database 'bt', file 'bt_log' ing file 1.

MULIHKAN DATABASE kasil ngolah 1070074 kaca sajrone 40.752 detik (205.141 MB/detik).

Saiki aku bakal nyoba kanggo mulihake serep dijupuk karo diganti BLOCKSIZE, BUFFERCOUNT lan MAXTRANSFERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

MULIHKAN DATABASE kasil ngolah 1070074 kaca sajrone 32.283 detik (258.958 MB/detik).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

MULIHKAN DATABASE kasil ngolah 1070074 kaca sajrone 32.682 detik (255.796 MB/detik).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

MULIHKAN DATABASE kasil ngolah 1070074 kaca sajrone 32.091 detik (260.507 MB/detik).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

MULIHKAN DATABASE kasil ngolah 1070074 kaca sajrone 32.401 detik (258.015 MB/detik).

Pernyataan RESTORE DATABASE ora owah sajrone pemulihan; paramèter kasebut ora ditemtokake; SQL Server dhewe nemtokake saka serep. Lan jelas manawa kanthi pulih bisa entuk bathi - meh 20% luwih cepet (Jujur, aku ora ngentekake akeh wektu kanggo pulih, aku mbukak sawetara serep "paling cepet" lan priksa manawa ora ana kerusakan.).

Mung ing kasus, mugi kula njlentrehake manawa iki dudu sawetara paramèter sing optimal kanggo kabeh wong. Sampeyan mung bisa entuk paramèter optimal kanggo dhewe kanthi nyoba. Aku entuk asil iki, sampeyan bakal entuk beda. Nanging sampeyan bisa ndeleng manawa sampeyan bisa "nyetel" serep lan bisa nggawe lan nyebarake luwih cepet.

Aku uga menehi saran supaya sampeyan maca dokumentasi kanthi lengkap, amarga bisa uga ana nuansa khusus kanggo sistem sampeyan.

Wiwit aku miwiti nulis babagan serep, aku pengin langsung nulis babagan siji liyane "optimasi", sing luwih umum tinimbang paramèter "tuning" (saben aku ngerti, iki digunakake paling ora sawetara keperluan serep, mbok menawa bebarengan karo paramèter. diterangake sadurunge), nanging uga durung diterangake ing Habré.

Yen kita ndeleng baris kapindho ing dokumentasi, ing sangisore BACKUP DATABASE, kita bakal weruh:

TO <backup_device> [ ,...n ]

Apa sampeyan mikir yen sampeyan nemtokake sawetara backup_devices? Sintaks ngidini. Lan bab sing menarik banget bakal kelakon - serep mung bakal "nyebar" ing sawetara piranti. Sing. saben "piranti" individu bakal ora ana guna, ilang siji, ilang kabeh serep. Nanging carane smearing kuwi mengaruhi kacepetan serep?

Coba gawe serep ing rong "piranti" sing ana ing folder sing padha:

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

Para leluhure jagad, yagene iki ditindakake?

Diproses 1070072 kaca kanggo database 'bt', file 'bt' ing file 1.

Diproses 2 kaca kanggo database 'bt', file 'bt'log 'ing file 1.

BACKUP DATABASE kasil ngolah 1070074 kaca sajrone 40.092 detik (208.519 MB/detik).

Apa serep dadi 25% luwih cepet mung metu saka biru? Apa yen kita nambah sawetara piranti liyane?

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

BACKUP DATABASE kasil ngolah 1070074 kaca sajrone 34.234 detik (244.200 MB/detik).

In total, gain kira-kira 35% saka wektu njupuk serep mung amarga kasunyatan sing serep ditulis kanggo 4 file ing siji disk bebarengan. Aku mriksa nomer luwih gedhe - ora ana gain ing laptop, kanthi optimal - 4 piranti. Kanggo sampeyan - Aku ora ngerti, sampeyan kudu mriksa. Inggih, kanthi cara, yen sampeyan duwe piranti kasebut - iki disk sing beda-beda, Sugeng, gain kudu luwih penting.

Saiki ayo ngomong babagan carane mulihake rasa seneng iki. Kanggo nindakake iki, sampeyan kudu ngganti printah Recovery lan dhaptar kabeh piranti:

RESTORE DATABASE [bt]
FROM 
    DISK = 'D:SQLServerbackupbt1.bak',
    DISK = 'D:SQLServerbackupbt2.bak',
    DISK = 'D:SQLServerbackupbt3.bak',
    DISK = 'D:SQLServerbackupbt4.bak'
WITH REPLACE, RECOVERY;

MULIHKAN DATABASE kasil ngolah 1070074 kaca sajrone 38.027 detik (219.842 MB/detik).

A sethitik luwih cepet, nanging nang endi wae cedhak, ora wujud. Umumé, serep dibusak luwih cepet lan dibalèkaké ing cara sing padha - sukses? Kanggo kula, iku cukup sukses. Iki penting, dadi aku mbaleni - yen sampeyan yen sampeyan kelangan paling ora siji saka file iki, sampeyan bakal kelangan kabeh serep.

Yen katon ing log ing informasi serep katampil nggunakake Trace Flags 3213 lan 3605, sampeyan bakal sok dong mirsani sing nalika gawe serep kanggo sawetara piranti, paling jumlah BUFFERCOUNT mundhak. Mbokmenawa, sampeyan bisa nyoba kanggo milih paramèter luwih optimal kanggo BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, nanging aku ora langsung kasil, lan aku kesed kanggo nindakake testing kuwi maneh, nanging kanggo nomer beda file. Lan isin bab gembong. Yen sampeyan pengin ngatur tes kasebut ing omah, ora angel nggawe maneh naskah kasebut.

Pungkasan, ayo ngomong babagan rega. Yen serep dibusak ing podo karo karo karya kedhaftar, sampeyan kudu njupuk pendekatan banget tanggung jawab kanggo testing, amarga yen serep dibusak luwih cepet, disk luwih tegang, beban ing prosesor mundhak (sampeyan isih kudu compress kanthi cepet), lan kanthi mangkono, respon sakabèhé sistem mudhun.

Mung guyon, nanging aku ngerti banget yen aku ora nggawe wahyu. Apa sing ditulis ing ndhuwur mung minangka demonstrasi babagan carane sampeyan bisa milih paramèter optimal kanggo nggawe serep.

Elinga yen kabeh sing ditindakake ditindakake kanthi resiko lan resiko dhewe. Priksa serep lan aja lali babagan DBCC CHECKDB.

Source: www.habr.com

MS SQL Server: BACKUP ing steroid

Add a comment Отменить ответ