ProHoster > Blog > Administrasi > MS SQL Server: Nyadangkeun on stéroid

MS SQL Server: Nyadangkeun on stéroid

Antosan! Antosan! Leres, ieu sanés tulisan sanés ngeunaan jinis cadangan SQL Server. Kuring malah moal ngobrol ngeunaan béda antara model recovery na kumaha carana nungkulan hiji log overgrown.

Panginten (ngan panginten), saatos maca tulisan ieu, anjeun bakal tiasa mastikeun yén cadangan anu dipiceun tina anjeun nganggo cara standar bakal dipupus isukan wengi, saé, 1.5 kali langkung gancang. Sarta ngan alatan kanyataan yén anjeun ngagunakeun saeutik leuwih parameter BACKUP DATABASE.

Upami eusi tulisan éta écés pikeun anjeun, hapunten. Kuring maca sagalana yén Google ngagaduhan kana frase "habr sql server cadangan", sarta dina henteu artikel tunggal kuring manggihan sagala nyebut kanyataan yén waktu cadangan bisa kumaha bae dipangaruhan maké parameter.

Kuring bakal langsung narik perhatian anjeun kana koméntar Alexander Gladchenko (@mssqlhelp):

Pernah ngarobah parameter BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE dina produksi. Éta dijieun ngan pikeun nulis artikel misalna. Dina prakna, anjeun bakal meunang leupas tina masalah memori dina waktu anu singget.

Éta, tangtosna, janten keren janten eusi anu paling pinter sareng ngeposkeun ekslusif, tapi, hanjakalna, ieu sanés masalahna. Aya artikel / tulisan basa Inggris sareng basa Rusia (Abdi sok bingung naon anu kedah disebat leres) anu dikhususkeun pikeun topik ieu. Ieu sababaraha anu kuring mendakan: waktu, два, tilu (dina sql.ru).

Janten, pikeun ngamimitian, kuring bakal ngagantelkeun sintaksis BACKUP anu rada dilucuti tina MSDN (Ngomong-ngomong, kuring nyerat di luhur ngeunaan BACKUP DATABASE, tapi sadayana ieu manglaku ka cadangan log transaksi sareng cadangan diferensial, tapi sigana gaduh pangaruh anu kirang atra):

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

<...> - hartina aya nu aya didinya, tapi ku kuring dipupus sabab ayeuna teu relevan jeung topikna.

Kumaha anjeun biasana nyandak cadangan? Kumaha aranjeunna "ngajarkeun" kumaha nyandak cadangan dina milyaran artikel? Sacara umum, lamun kuring kudu nyieun cadangan hiji-waktos sababaraha database teu kacida gedéna, kuring bakal otomatis nulis hal kawas kieu:

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

Sareng, sacara umum, sigana 75-90% sadaya parameter anu biasana disebatkeun dina tulisan ngeunaan cadangan didaptarkeun di dieu. Tah, aya oge INIT, SKIP. Dupi anjeun nganjang ka MSDN? Naha anjeun ningali yén aya pilihan pikeun hiji satengah layar? Kuring ogé ningali ...

Anjeun meureun geus sadar yen salajengna urang bakal ngobrol ngeunaan tilu parameter nu tetep dina blok kode kahiji - BLOCKSIZE, BUFFERCOUNT na MAXTRANSFERSIZE. Ieu pedaran maranéhanana ti MSDN:

UKURAN BLOCKSIZE = { ukuran blok | @ blocksize_variable } - nunjukkeun ukuran blok fisik dina bait. Ukuran anu dirojong nyaéta 512, 1024, 2048, 4096, 8192, 16, 384, sareng 32 bait (768 KB). Nilai standar nyaéta 65 pikeun alat kasét sareng 536 pikeun alat anu sanés. Ilaharna parameter ieu teu perlu sabab pernyataan BACKUP otomatis milih ukuran blok luyu pikeun alat. Nyetél ukuran blok sacara eksplisit overrides pilihan ukuran blok otomatis.

BUFFERCOUNT = { panyangga | @ buffercount_variable } - Nangtukeun jumlah total I / O buffers anu bakal dipaké pikeun operasi cadangan. Anjeun tiasa nangtukeun sagala nilai integer positif, tapi sajumlah badag panyangga bisa ngabalukarkeun hiji kaluar-of-memori kasalahan alatan spasi alamat maya kaleuleuwihan dina prosés Sqlservr.exe.

Jumlah total spasi dipaké ku buffers ditangtukeun ku rumus ieu: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } nangtukeun ukuran pakét data panggedéna, dina bait, pikeun tukeur antara SQL Server jeung média set cadangan. Sababaraha 65 bait (536 KB) nepi ka 64 bait (4 MB) dirojong.

Sumpah - Kuring kantos maca ieu sateuacanna, tapi kuring henteu pernah kapikir sabaraha dampak anu aranjeunna tiasa gaduh kana produktivitas. Sumawona, katingalina, kuring kedah ngadamel jinis "kaluar" sareng ngaku yén ayeuna kuring henteu ngartos pisan naon anu aranjeunna lakukeun. Kuring meureun kudu maca leuwih lengkep tentang buffered I / O jeung gawé bareng hard drive. Someday kuring bakal ngalakukeun ieu, tapi pikeun ayeuna kuring ngan ukur tiasa nyerat naskah anu bakal mariksa kumaha nilai-nilai ieu mangaruhan kagancangan cadanganna.

Kuring nyieun database leutik, kira-kira 10 GB ukuranana, nempatkeun eta dina SSD, sarta nempatkeun diréktori pikeun cadangan dina HDD.

Kuring nyieun tabel samentara pikeun nyimpen hasil (Kuring teu boga eta samentara, jadi kuring bisa ngagali kana hasil dina leuwih jéntré, tapi anjeun mutuskeun pikeun diri):

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

Prinsip naskah téh basajan - puteran nested, nu masing-masing ngarobah nilai hiji parameter, selapkeun parameter ieu kana paréntah BACKUP, simpen catetan panungtungan kalawan sajarah ti msdb.dbo.backupset, ngahapus payel cadangan tur Iteration salajengna . Kusabab data palaksanaan cadangan dicokot tina backupset, akurasi rada leungit (teu aya fraksi detik), tapi urang bakal salamet ieu.

Mimiti anjeun kedah ngaktipkeun xp_cmdshell pikeun mupus cadangan (teras tong hilap mareuman upami anjeun henteu peryogi): 

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

Nya, sabenerna:

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

Upami anjeun ujug-ujug peryogi klarifikasi ngeunaan naon anu kajantenan di dieu, tulis dina koméntar atanapi PM. Kanggo ayeuna, kuring ngan ukur nyarioskeun ka anjeun ngeunaan parameter anu kuring lebetkeun dina BACKUP DATABASE.

Pikeun BLOCKSIZE kami boga "tutup" daptar nilai, sarta kuring teu ngalakukeun cadangan kalawan BLOCKSIZE <4KB. MAXTRANSFERSIZE sagala angka nu mangrupa kelipatan 64KB - ti 64KB nepi ka 4MB. Standar dina sistem kuring nyaéta 1024KB, kuring nyandak 512 - 1024 - 2048 - 4096.

Éta langkung hese sareng BUFFERCOUNT - éta tiasa janten angka anu positif, tapi tautanna nyarios kumaha diitung dina BACKUP DATABASE sareng naha nilai ageung bahaya?. Ogé nyebutkeun kumaha carana meunangkeun informasi ngeunaan nu BUFFERCOUNT cadangan sabenerna dijieun kalawan - keur kuring éta 7. Teu aya titik dina ngurangan éta, sarta wates luhur kapanggih sacara ékspériméntal - kalawan BUFFERCOUNT = 896 sarta MAXTRANSFERSIZE = 4194304 cadangan murag kalawan. kasalahan (ngeunaan nu ditulis dina link di luhur):

Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE terminating abnormally.

Msg 701, Level 17, State 123, Line 7 Aya teu cukup memori sistem dina sumberdaya kolam renang 'standar' pikeun ngajalankeun query ieu.

Pikeun babandingan, mimitina kuring bakal nunjukkeun hasil ngajalankeun cadangan tanpa nangtukeun parameter naon waé:

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

Nya, cadangan sareng cadangan:

Diolah 1070072 kaca pikeun database 'bt', file 'bt' dina file 1.

Diolah 2 kaca pikeun database 'bt', file 'bt_log' dina file 1.

BACKUP DATABASE hasil ngolah 1070074 kaca dina 53.171 detik (157.227 MB/detik).

Skrip sorangan, nguji parameter, digawé dina sababaraha jam, sadaya pangukuran aya google spreadsheet. Sareng ieu mangrupikeun pilihan hasil kalayan tilu waktos palaksanaan pangsaéna (Kuring nyobian ngadamel grafik anu saé, tapi dina tulisan kuring kedah ngalakukeun méja, sareng dina koméntar. @campuran tambah grafik pisan cool).

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;

MS SQL Server: Nyadangkeun on stéroid

Perhatosan, catetan anu penting pisan ti @campuran ti komentar:

Urang yakin bisa disebutkeun yen hubungan antara parameter jeung speed cadangan dina rentang nilai ieu acak, teu aya pola. Tapi ngajauhan tina parameter anu diwangun écés ngagaduhan pangaruh anu hadé dina hasilna

Jelema. Ngan ku ngatur parameter BACKUP baku éta gain 2-melu dina waktos panyabutan cadangan: 26 detik, versus 53 di awal. Éta henteu goréng, leres? Tapi urang kedah ningali naon anu lumangsung dina restorasi. Kumaha upami ayeuna peryogi 4 kali langkung lami kanggo pulih?

Kahiji, hayu urang ngukur sabaraha lila waktu nu diperlukeun mulangkeun cadangan kalawan setélan standar:

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

Nya, anjeun sorangan terang yén, cara-carana aya, ngagentos sanés ngagentos, pamulihan sanés pamulihan. Sareng kuring ngalakukeun sapertos kieu:

Diolah 1070072 kaca pikeun database 'bt', file 'bt' dina file 1.

Diolah 2 kaca pikeun database 'bt', file 'bt_log' dina file 1.

MULIHKAN DATABASE hasil ngolah 1070074 kaca dina 40.752 detik (205.141 MB/detik).

Ayeuna kuring bakal nyobian mulangkeun cadangan anu dicandak ku BLOCKSIZE anu dirobih, BUFFERCOUNT sareng MAXTRANSFERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

MULIHKAN DATABASE hasil ngolah 1070074 kaca dina 32.283 detik (258.958 MB/detik).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

MULIHKAN DATABASE hasil ngolah 1070074 kaca dina 32.682 detik (255.796 MB/detik).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

MULIHKAN DATABASE hasil ngolah 1070074 kaca dina 32.091 detik (260.507 MB/detik).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

MULIHKAN DATABASE hasil ngolah 1070074 kaca dina 32.401 detik (258.015 MB/detik).

Pernyataan RESTORE DATABASE henteu robih salami pamulihan; parameter ieu henteu dieusian di jerona; SQL Server sorangan nangtukeun aranjeunna tina cadangan. Sareng écés yén sanaos pamulihan tiasa aya kauntungan - ampir 20% langkung gancang (Jujur, kuring henteu nyéépkeun waktos kanggo pamulihan, kuring ngajalankeun sababaraha cadangan "panggancangna" sareng mastikeun yén teu aya karusakan.).

Ngan bisi, hayu atuh netelakeun yen ieu teu sababaraha parameter anu optimal pikeun dulur. Anjeun ngan bisa meunangkeun parameter optimal pikeun diri ku nguji. Kuring ngagaduhan hasil ieu, anjeun bakal nampi anu béda. Tapi anjeun ningali yén anjeun tiasa "ngatur" cadangan anjeun sareng aranjeunna tiasa ngabentuk sareng nyebarkeun langkung gancang.

Kuring ogé nyarankeun pisan yén anjeun maca dokuméntasi sacara lengkep, sabab meureun aya nuansa khusus pikeun sistem anjeun.

Kusabab kuring mimiti nulis ngeunaan cadangan, Abdi hoyong geura-giru nulis ngeunaan hiji deui "optimasi", nu leuwih umum ti "tuning" parameter (sajauh kuring ngartos, ieu dipaké ku sahenteuna sababaraha Utiliti cadangan, sugan bareng jeung parameter. dijelaskeun sateuacana), tapi éta ogé henteu acan dijelaskeun dina Habré.

Upami urang ningali garis kadua dina dokuméntasi, langsung dina BACKUP DATABASE, di dinya urang tingali:

TO <backup_device> [ ,...n ]

Kumaha saur anjeun bakal kajadian lamun anjeun nangtukeun sababaraha backup_devices? Sintaksis ngamungkinkeun. Sareng hiji hal anu pikaresepeun pisan bakal kajantenan - cadanganna bakal "nyebarkeun" ka sababaraha alat. Jelema. unggal "alat" individual bakal aya gunana, leungit hiji, leungit sakabéh cadangan. Tapi kumaha smearing sapertos bakal mangaruhan speed cadangan?

Hayu urang cobaan nyieun cadangan dina dua "alat" nu lokasina sisi-demi-sisi dina folder sarua:

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

Bapa saalam dunya, naha ieu dilakukeun?

Diolah 1070072 kaca pikeun database 'bt', file 'bt' dina file 1.

Diolah 2 kaca pikeun database 'bt', file 'bt'log' dina file 1.

BACKUP DATABASE hasil ngolah 1070074 kaca dina 40.092 detik (208.519 MB/detik).

Naha cadanganna janten 25% langkung gancang ngan teu jelas? Kumaha upami urang nambihan sababaraha alat deui?

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

BACKUP DATABASE hasil ngolah 1070074 kaca dina 34.234 detik (244.200 MB/detik).

Dina total, gain sakitar 35% tina waktos nyandak cadangan ngan kusabab kanyataan yén cadangan ditulis kana 4 file dina hiji disk sakaligus. Kuring pariksa jumlah anu langkung ageung - henteu aya kauntungan dina laptop kuring, optimal - 4 alat. Pikeun anjeun - kuring henteu terang, anjeun kedah parios. Nya, ku jalan kitu, upami anjeun gaduh alat ieu - ieu mangrupikeun disk anu béda-béda, ucapan salamet, kauntungan kedah langkung signifikan.

Ayeuna hayu urang ngobrol ngeunaan kumaha carana mulangkeun kabagjaan ieu. Jang ngalampahkeun ieu, anjeun kedah ngarobih paréntah pamulihan sareng daptar sadaya alat:

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 hasil ngolah 1070074 kaca dina 38.027 detik (219.842 MB/detik).

A saeutik gancang, tapi tempat deukeut, teu signifikan. Sacara umum, cadangan dicabut langkung gancang sareng disimpen dina cara anu sami - suksés? Sedengkeun pikeun kuring, éta cukup suksés. Ieu penting, jadi kuring ngulang - lamun anjeun lamun leungit sahanteuna hiji file ieu, Anjeun leungit sakabéh cadangan.

Upami anjeun ningali dina log inpormasi cadangan anu ditampilkeun nganggo Trace Flags 3213 sareng 3605, anjeun bakal aya bewara yén nalika nyadangkeun ka sababaraha alat, sahenteuna jumlah BUFFERCOUNT naék. Panginten, anjeun tiasa nyobian milih parameter anu langkung optimal pikeun BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, tapi kuring henteu hasil langsung, sareng kuring teuing puguh pikeun nguji deui, tapi pikeun sababaraha file anu béda. Tur éta éra ngeunaan roda. Upami anjeun badé ngatur tés sapertos kitu di bumi, henteu sesah ngadamel deui naskah.

Tungtungna, hayu urang ngobrol ngeunaan harga. Upami cadanganna dicabut paralel sareng padamelan pangguna, anjeun kedah nyandak pendekatan anu tanggung jawab pisan pikeun nguji, sabab upami cadangan dicabut langkung gancang, disk langkung tapis, beban dina prosésor naék (anjeun masih kedah komprési. eta on laleur), sarta sasuai, respon sakabéh sistem nurun.

Ngan bercanda, tapi kuring ngarti pisan yén kuring henteu ngadamel wahyu. Naon anu diserat di luhur mangrupikeun demonstrasi kumaha anjeun tiasa milih parameter optimal pikeun nyandak cadangan.

Émut yén sadaya anu anjeun laksanakeun dilakukeun dina peril sareng résiko anjeun nyalira. Pariksa cadangan anjeun sarta ulah poho ngeunaan DBCC CHECKDB.

sumber: www.habr.com

Tambahkeun komentar