MS SQL Server: Steroidlerde YEDEKLEME

Beklemek! Beklemek! Doğru, bu, SQL Server yedekleme türleri hakkında başka bir makale değil. Kurtarma modelleri arasındaki farklardan ve aşırı büyümüş bir kütükle nasıl başa çıkılacağından bahsetmeyeceğim bile.

Belki (sadece belki), bu yazıyı okuduktan sonra, standart yöntemlerle sizden kaldırılan yedeğin yarın gece, yani 1.5 kat daha hızlı kaldırılacağından emin olabilirsiniz. Ve sadece biraz daha fazla YEDEKLEME VERİTABANI parametresi kullanmanız nedeniyle.

Gönderinin içeriği sizin için açıksa özür dilerim. Google'ın "habr sql sunucu yedeklemesi" ifadesi için bulduğu her şeyi okudum ve tek bir makalede yedekleme süresinin parametreler kullanılarak bir şekilde etkilenebileceği gerçeğinden bahsetmedim.

Hemen dikkatinizi Alexander Gladchenko'nun yorumuna çekeceğim (@mssqlhelp):

Üretimde BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE parametrelerini asla değiştirmeyin. Sadece bu tür makaleler yazmak için yapılmıştır. Uygulamada kısa sürede hafıza sorunlarınızdan kurtulacaksınız.

Elbette en akıllı olmak ve özel içerik yayınlamak harika olurdu, ancak ne yazık ki durum böyle değil. Bu konuya ayrılmış hem İngilizce hem de Rusça makaleler/yazılar var (bunları doğru şekilde nasıl adlandıracağım konusunda her zaman kafam karışıyor). İşte karşılaştığımlardan bazıları: zaman, iki, üç (sql.ru'da).

Başlangıç ​​olarak, biraz sadeleştirilmiş bir BACKUP söz dizimi ekleyeceğim. MSDN (Bu arada, YEDEKLEME VERİTABANI hakkında yukarıda yazdım, ancak tüm bunlar hem işlem günlüğü yedeklemesi hem de diferansiyel yedekleme için geçerlidir, ancak belki daha az belirgin bir etkiye sahiptir):

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

<…> - orada bir şey olduğu anlamına geliyor, ancak artık konuyla alakalı olmadığı için kaldırdım.

Genellikle nasıl yedek alırsınız? Milyarlarca makalede yedek almayı nasıl “öğretiyorlar”? Genel olarak, çok büyük olmayan bir veri tabanının tek seferlik yedeğini almam gerekirse, otomatik olarak şöyle bir şey yazacağım:

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

Ve genel olarak, yedeklemelerle ilgili makalelerde genellikle bahsedilen tüm parametrelerin muhtemelen% 75-90'ı burada listelenmiştir. Bir de INIT, SKIP var. MSDN'yi ziyaret ettiniz mi? Bir buçuk ekran için seçeneklerin olduğunu gördünüz mü? Ben de gördüm...

Muhtemelen, ilk kod bloğunda kalan üç parametre - BLOCKSIZE, BUFFERCOUNT ve MAXTRANSFERSIZE - hakkında daha fazla konuşacağımızı zaten fark etmişsinizdir. İşte MSDN'deki açıklamaları:

BLOK BOYUTU = { blok boyutu | @ blokboyutu_değişken } - bayt cinsinden fiziksel blok boyutunu gösterir. Desteklenen boyutlar 512, 1024, 2048, 4096, 8192, 16, 384 ve 32 bayttır (768 KB). Varsayılan değer teyp aygıtları için 65, diğer aygıtlar için 536'dir. Genellikle bu parametre gerekli değildir çünkü BACKUP ifadesi cihaz için uygun blok boyutunu otomatik olarak seçer. Blok boyutunun ayarlanması, otomatik blok boyutu seçimini açıkça geçersiz kılar.

BUFFERCOUNT = { arabellek sayısı | @ buffercount_variable } - Yedekleme işlemi için kullanılacak toplam G/Ç arabellek sayısını tanımlar. Herhangi bir pozitif tamsayı değeri belirtebilirsiniz ancak çok sayıda arabellek, Sqlservr.exe işlemindeki aşırı sanal adres alanı nedeniyle yetersiz bellek hatasına neden olabilir.

Tamponların kullandığı toplam alan miktarı aşağıdaki formülle belirlenir: BUFFERCOUNT * MAXTRANSFERSIZE.

MAKSTRANSFER BOYUTU = { maksimum transfer boyutu | @ maxtransfersize_variable } SQL Server ile yedekleme kümesi ortamı arasında değiştirilecek en büyük veri paketi boyutunu bayt cinsinden belirtir. 65 bayttan (536 KB) 64 bayta (4 MB) kadar olan katlar desteklenir.

Yemin ederim, bunu daha önce okumuştum ama bunların üretkenlik üzerinde ne kadar etkili olabileceği hiç aklıma gelmemişti. Üstelik görünüşe göre bir tür "açıklama" yapmam ve şu anda bile tam olarak ne yaptıklarını tam olarak anlamadığımı itiraf etmem gerekiyor. Muhtemelen ara belleğe alınmış G/Ç ve sabit sürücüyle çalışma hakkında daha fazla şey okumam gerekiyor. Bir gün bunu yapacağım ama şimdilik bu değerlerin yedeklemenin alınma hızını nasıl etkilediğini kontrol edecek bir komut dosyası yazabilirim.

Yaklaşık 10 GB boyutunda küçük bir veritabanı oluşturdum, onu SSD'ye koydum ve yedekleme dizinini HDD'ye koydum.

Sonuçları saklamak için geçici bir tablo oluşturuyorum (geçici bir tablom yok, bu yüzden sonuçları daha ayrıntılı olarak inceleyebilirim, ancak siz kendiniz karar verin):

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

Komut dosyasının prensibi basittir - her biri bir parametrenin değerini değiştiren iç içe döngüler, bu parametreleri BACKUP komutuna ekleyin, msdb.dbo.backupset'teki geçmişle birlikte son kaydı kaydedin, yedekleme dosyasını silin ve bir sonraki yinelemeyi yapın. . Yedekleme yürütme verileri yedekleme kümesinden alındığından, doğruluk bir miktar kaybolur (saniyelerin kesirleri yoktur), ancak bunu atlatacağız.

Yedeklemeleri silmek için öncelikle xp_cmdshell'i etkinleştirmeniz gerekir (daha sonra ihtiyacınız yoksa devre dışı bırakmayı unutmayın):

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

Aslında:

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

Aniden burada olup bitenler hakkında açıklamaya ihtiyaç duyarsanız, yorumlara veya PM'ye yazın. Şimdilik sadece YEDEKLEME VERİTABANI'na koyduğum parametrelerden bahsedeceğim.

BLOCKSIZE için "kapalı" bir değerler listemiz var ve BLOCKSIZE < 4KB ile yedekleme yapmadım. MAXTRANSFERSIZE, 64KB'den 64MB'a kadar 4KB'nin katı olan herhangi bir sayı. Sistemimin varsayılanı 1024KB, 512 - 1024 - 2048 - 4096'yı aldım.

BUFFERCOUNT ile daha zordu; herhangi bir pozitif sayı olabilir, ancak bağlantı şunu söylüyor: YEDEKLEME VERİTABANI'nda nasıl hesaplanır ve büyük değerler neden tehlikelidir?. Ayrıca yedeklemenin gerçekte hangi BUFFERCOUNT ile yapıldığı hakkında nasıl bilgi alınacağını da söylüyor - benim için 7. Bunu azaltmanın bir anlamı yoktu ve üst sınır deneysel olarak keşfedildi - BUFFERCOUNT = 896 ve MAXTRANSFERSIZE = 4194304 ile yedekleme düştü bir hata (yukarıdaki bağlantıda yazılanlarla ilgili):

Msg 3013, Seviye 16, Durum 1, Satır 7 YEDEKLEME VERİTABANI anormal şekilde sonlandırılıyor.

Msg 701, Seviye 17, Durum 123, Satır 7 Bu sorguyu çalıştırmak için 'varsayılan' kaynak havuzunda yeterli sistem belleği yok.

Karşılaştırma için, ilk önce herhangi bir parametre belirtmeden bir yedekleme çalıştırmanın sonuçlarını göstereceğim:

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

Peki, yedekleme ve yedekleme:

'bt' veritabanı, dosya 1070072'deki 'bt' dosyası için 1 sayfa işlendi.

'bt' veritabanı, dosya 2'deki 'bt_log' dosyası için 1 sayfa işlendi.

YEDEKLEME VERİTABANI 1070074 sayfayı 53.171 saniyede (157.227 MB/sn) başarıyla işledi.

Parametreleri test eden betiğin kendisi birkaç saat içinde çalıştı, tüm ölçümler yapıldı Google e-tablosu. Ve işte en iyi üç yürütme süresine sahip sonuçların bir seçkisi (Güzel bir grafik oluşturmaya çalıştım, ancak yazıda bir tabloyla yetinmek zorunda kalacağım ve yorumlarda) @karışım katma çok güzel grafikler).

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;

Dikkat, çok önemli bir not @karışım arasında yorumlar:

Bu değer aralıklarındaki parametreler ile yedekleme hızı arasındaki ilişkinin rastgele olduğunu, bir model olmadığını rahatlıkla söyleyebiliriz. Ancak yerleşik parametrelerden uzaklaşmanın sonuç üzerinde açıkça iyi bir etkisi oldu

Onlar. Yalnızca standart YEDEKLEME parametrelerinin yönetilmesi, yedekleme kaldırma süresinde 2 kat artış sağladı: başlangıçta 26 saniyeye kıyasla 53 saniye. Bu kötü değil, değil mi? Ancak restorasyonda ne olacağını görmemiz gerekiyor. Peki ya iyileşmek 4 kat daha uzun sürerse?

Öncelikle bir yedeği varsayılan ayarlarla geri yüklemenin ne kadar süreceğini ölçelim:

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

Eh, siz de biliyorsunuz ki, yollar var, değiştirmek değiştirmek değil, iyileşmek iyileşmek değil. Ve bunu şu şekilde yapıyorum:

'bt' veritabanı, dosya 1070072'deki 'bt' dosyası için 1 sayfa işlendi.

'bt' veritabanı, dosya 2'deki 'bt_log' dosyası için 1 sayfa işlendi.

RESTORE DATABASE, 1070074 sayfayı 40.752 saniyede (205.141 MB/sn) başarıyla işledi.

Şimdi değişen BLOCKSIZE, BUFFERCOUNT ve MAXTRANSFERSIZE ile alınan yedekleri geri yüklemeye çalışacağım.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE, 1070074 sayfayı 32.283 saniyede (258.958 MB/sn) başarıyla işledi.

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE, 1070074 sayfayı 32.682 saniyede (255.796 MB/sn) başarıyla işledi.

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE, 1070074 sayfayı 32.091 saniyede (260.507 MB/sn) başarıyla işledi.

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE, 1070074 sayfayı 32.401 saniyede (258.015 MB/sn) başarıyla işledi.

RESTORE DATABASE ifadesi kurtarma sırasında değişmez; bu parametreler içinde belirtilmez; SQL Server bunları yedeklemeden kendisi belirler. Ve iyileşmeyle bile neredeyse% 20 daha hızlı bir kazanç olabileceği açıktır (Dürüst olmak gerekirse, kurtarma için fazla zaman harcamadım, "en hızlı" yedeklemelerden birkaçını inceledim ve herhangi bir bozulma olmadığından emin oldum.).

Her ihtimale karşı, bunların herkes için en uygun parametreler olmadığını açıklığa kavuşturmama izin verin. Kendiniz için en uygun parametreleri ancak test ederek elde edebilirsiniz. Ben bu sonuçları aldım, siz farklı sonuçlar alacaksınız. Ancak yedeklerinizi "ayarlayabildiğinizi" ve aslında daha hızlı oluşturup dağıtabildiklerini görüyorsunuz.

Ayrıca dokümantasyonun tamamını okumanızı da şiddetle tavsiye ederim çünkü sisteminize özel nüanslar olabilir.

Yedeklemeler hakkında yazmaya başladığımdan beri, "ayarlama" parametrelerinden daha yaygın olan bir "optimizasyon" hakkında hemen yazmak istiyorum (anladığım kadarıyla, en azından bazı yedekleme yardımcı programları tarafından, belki de parametrelerle birlikte kullanılıyor) daha önce anlatılmıştı), ancak henüz Habré'de de tanımlanmadı.

Dokümantasyondaki ikinci satıra bakarsak, YEDEKLEME VERİTABANI'nın hemen altında şunu görürüz:

TO <backup_device> [ ,...n ]

Birkaç backup_devices belirtirseniz ne olacağını düşünüyorsunuz? Sözdizimi buna izin veriyor. Ve çok ilginç bir şey olacak - yedekleme birkaç cihaza basitçe "yayılacak". Onlar. her bir “cihaz” ayrı ayrı işe yaramaz hale gelecek, bir tanesini kaybedecek, tüm yedeği kaybedecektir. Peki bu tür bir lekelenme yedekleme hızını nasıl etkileyecek?

Aynı klasörde yan yana bulunan iki "cihaza" yedekleme yapmaya çalışalım:

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

Dünyanın babaları, bu neden yapılıyor?

'bt' veritabanı, dosya 1070072'deki 'bt' dosyası için 1 sayfa işlendi.

'bt' veritabanı ve 'bt' dosyası için 2 sayfa işlendiDosya 1'deki log'.

YEDEKLEME VERİTABANI 1070074 sayfayı 40.092 saniyede (208.519 MB/sn) başarıyla işledi.

Yedekleme birdenbire %25 daha hızlı mı oldu? Birkaç cihaz daha eklersek ne olur?

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

YEDEKLEME VERİTABANI 1070074 sayfayı 34.234 saniyede (244.200 MB/sn) başarıyla işledi.

Toplamda, yedeklemenin aynı anda bir diskteki 35 dosyaya yazılması nedeniyle kazanç, yedekleme alma süresinin yaklaşık% 4'idir. Daha büyük bir sayıyı kontrol ettim - dizüstü bilgisayarımda en iyi şekilde kazanç yok - 4 cihaz. Senin için - bilmiyorum, kontrol etmelisin. Bu arada, eğer bu cihazlara sahipseniz - bunlar gerçekten farklı diskler, tebrikler, kazanç daha da önemli olmalı.

Şimdi bu mutluluğu nasıl geri getireceğimizi konuşalım. Bunu yapmak için kurtarma komutunu değiştirmeniz ve tüm cihazları listelemeniz gerekecektir:

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, 1070074 sayfayı 38.027 saniyede (219.842 MB/sn) başarıyla işledi.

Biraz daha hızlı ama yakın bir yerde, önemli değil. Genel olarak, yedekleme daha hızlı kaldırılır ve aynı şekilde geri yüklenir - başarı mı? Bana gelince, oldukça başarılı. Bu bu çok önemli, bu yüzden tekrar ediyorum - eğer bu dosyalardan en az birini kaybederseniz yedeklemenin tamamını kaybedersiniz.

Günlüğe, Trace Flags 3213 ve 3605 kullanılarak görüntülenen yedekleme bilgilerine bakarsanız, birden fazla cihaza yedekleme yaparken en azından BUFFERCOUNT sayısının arttığını fark edeceksiniz. Muhtemelen BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE için daha uygun parametreler seçmeyi deneyebilirsiniz, ancak hemen başarılı olamadım ve bu tür testleri farklı sayıda dosya için tekrar yapamayacak kadar tembeldim. Ve tekerleklere yazık. Evde bu tür testleri düzenlemek istiyorsanız senaryoyu yeniden yapmak zor değil.

Son olarak fiyattan bahsedelim. Yedekleme, kullanıcıların çalışmasına paralel olarak kaldırılırsa, teste çok sorumlu bir yaklaşım sergilemeniz gerekir, çünkü yedekleme daha hızlı kaldırılırsa diskler daha fazla zorlanır, işlemci üzerindeki yük artar (yine de sıkıştırmanız gerekir). anında) ve buna bağlı olarak sistemin genel yanıt verme yeteneği azalır.

Şaka yapıyorum ama herhangi bir açıklama yapmadığımı gayet iyi anlıyorum. Yukarıda yazılanlar, yedekleme almak için en uygun parametreleri nasıl seçebileceğinizin bir gösterimidir.

Yaptığınız her şeyin sizin sorumluluğunuzda ve risk altında yapıldığını unutmayın. Yedeklemelerinizi kontrol edin ve DBCC CHECKDB'yi unutmayın.

Kaynak: habr.com