MS SQL Server: پشتیبان گیری در استروئیدها

صبر کن! صبر کن! درست است، این مقاله دیگری در مورد انواع پشتیبان گیری از SQL Server نیست. من حتی در مورد تفاوت بین مدل های بازیابی و نحوه برخورد با یک سیاهه بزرگ صحبت نمی کنم.

شاید (فقط شاید)، پس از خواندن این پست، بتوانید مطمئن شوید که بک آپی که با استفاده از ابزارهای استاندارد از شما حذف می شود، فردا شب، خوب، 1.5 برابر سریعتر حذف خواهد شد. و فقط به این دلیل که از پارامترهای BACKUP DATABASE کمی بیشتر استفاده می کنید.

اگر محتوای پست برای شما واضح بود، متاسفم. من همه چیزهایی را که گوگل برای عبارت "پشتیبان گیری از سرور sql habr" به دست آورد، خواندم و در هیچ مقاله ای به این واقعیت اشاره نکردم که زمان پشتیبان گیری می تواند به نوعی با استفاده از پارامترها تحت تاثیر قرار گیرد.

من بلافاصله توجه شما را به نظر الکساندر گلادچنکو جلب می کنم (@mssqlhelp):

هرگز پارامترهای BUFFERCOUNT، BLOCKSIZE، MAXTRANSFERSIZE را در تولید تغییر ندهید. آنها فقط برای نوشتن چنین مقالاتی ساخته شده اند. در عمل در کمترین زمان از شر مشکلات حافظه خلاص خواهید شد.

مطمئناً جالب است که باهوش ترین باشید و محتوای انحصاری ارسال کنید، اما، متأسفانه، اینطور نیست. مقالات/پست‌هایی به زبان انگلیسی و روسی (من همیشه گیج هستم که آنها را به درستی چه نامی بگذارم) به این موضوع اختصاص دارد. در اینجا تعدادی از مواردی است که من با آنها برخورد کردم: زمان, два, سه (در sql.ru).

بنابراین، برای شروع، من یک سینتکس BACKUP کمی حذف شده از آن را پیوست می کنم MSDN (به هر حال، من در بالا در مورد BACKUP DATABASE نوشتم، اما همه اینها برای پشتیبان گیری گزارش تراکنش و پشتیبان گیری دیفرانسیل صدق می کند، اما شاید با یک اثر کمتر آشکار):

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

<…> - به این معنی است که چیزی آنجا بود، اما من آن را حذف کردم زیرا اکنون به موضوع مربوط نمی شود.

معمولاً چگونه پشتیبان می گیرید؟ چگونه آنها "آموزش" می دهند که چگونه از میلیاردها مقاله پشتیبان تهیه کنید؟ به طور کلی، اگر من نیاز داشته باشم که یک نسخه پشتیبان از یک پایگاه داده نه چندان بزرگ تهیه کنم، به طور خودکار چیزی شبیه به این می نویسم:

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

و به طور کلی، احتمالاً 75 تا 90 درصد از تمام پارامترهایی که معمولاً در مقالات مربوط به پشتیبان‌گیری ذکر می‌شوند، در اینجا فهرست شده‌اند. خوب، INIT، SKIP نیز وجود دارد. آیا از MSDN بازدید کرده اید؟ آیا دیده اید که گزینه هایی برای صفحه نمایش یک و نیم وجود دارد؟ من هم دیدم...

احتمالاً قبلاً متوجه شده اید که در ادامه در مورد سه پارامتر باقی مانده در بلوک اول کد - BLOCKSIZE، BUFFERCOUNT و MAXTRANSFERSIZE صحبت خواهیم کرد. در اینجا توضیحات آنها از MSDN آمده است:

BLOCKSIZE = { مسدود کردن | @ blocksize_variable } - اندازه بلوک فیزیکی را بر حسب بایت نشان می دهد. اندازه های پشتیبانی شده 512، 1024، 2048، 4096، 8192، 16،384، 32،768، و 65،536 بایت (64 کیلوبایت) می باشد. مقدار پیش فرض برای دستگاه های نوار 65 و برای دستگاه های دیگر 536 است. معمولاً این پارامتر ضروری نیست زیرا عبارت BACKUP به طور خودکار اندازه بلوک مناسب را برای دستگاه انتخاب می کند. تنظیم اندازه بلوک به صراحت انتخاب اندازه بلوک خودکار را لغو می کند.

BUFFERCOUNT = { بافر شمار | @ buffercount_variable } - تعداد کل بافرهای ورودی/خروجی را که برای عملیات پشتیبان استفاده خواهند شد را مشخص می کند. شما می توانید هر عدد صحیح مثبتی را مشخص کنید، اما تعداد زیادی از بافرها ممکن است به دلیل فضای بیش از حد آدرس مجازی در فرآیند Sqlservr.exe باعث خطای خارج از حافظه شوند.

مقدار کل فضای استفاده شده توسط بافرها با فرمول زیر تعیین می شود: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } بزرگترین اندازه بسته داده را بر حسب بایت برای تبادل بین SQL Server و رسانه مجموعه پشتیبان مشخص می کند. چندین 65 بایت (536 کیلوبایت) تا 64 بایت (4 مگابایت) پشتیبانی می شود.

قسم می خورم - قبلاً این را خوانده بودم، اما هرگز به ذهنم نرسید که چقدر می توانند بر بهره وری تأثیر بگذارند. علاوه بر این، ظاهراً باید نوعی "بیرون آمدن" انجام دهم و اعتراف کنم که حتی اکنون نیز کاملاً نمی دانم آنها دقیقاً چه می کنند. احتمالاً باید در مورد ورودی/خروجی بافر و کار با هارد دیسک بیشتر مطالعه کنم. روزی این کار را انجام خواهم داد، اما در حال حاضر فقط می توانم یک اسکریپت بنویسم که بررسی کند این مقادیر چگونه بر سرعت تهیه نسخه پشتیبان تأثیر می گذارد.

من یک دیتابیس کوچک با حجم حدودا 10 گیگابایت درست کردم و روی SSD گذاشتم و دایرکتوری بک آپ را روی هارد دیسک قرار دادم.

من یک جدول موقت برای ذخیره نتایج ایجاد می کنم (من آن را موقتی ندارم، بنابراین می توانم نتایج را با جزئیات بیشتری بررسی کنم، اما شما خودتان تصمیم بگیرید):

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

اصل اسکریپت ساده است - حلقه های تو در تو، که هر کدام مقدار یک پارامتر را تغییر می دهد، این پارامترها را در دستور BACKUP وارد کنید، آخرین رکورد را با تاریخچه از msdb.dbo.backupset ذخیره کنید، فایل پشتیبان را حذف کنید و تکرار بعدی را حذف کنید. . از آنجایی که داده های اجرای پشتیبان از پشتیبان گرفته می شود، دقت تا حدودی از بین می رود (کسری از ثانیه وجود ندارد)، اما ما از این وضعیت جان سالم به در خواهیم برد.

ابتدا باید xp_cmdshell را فعال کنید تا نسخه‌های پشتیبان را حذف کند (سپس فراموش نکنید اگر به آن نیاز ندارید آن را غیرفعال کنید):

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

خوب، در واقع:

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

اگر به طور ناگهانی نیاز به توضیح در مورد آنچه در اینجا اتفاق می افتد دارید، در نظرات یا PM بنویسید. در حال حاضر، من فقط در مورد پارامترهایی که در BACKUP DATABASE قرار داده ام به شما می گویم.

برای BLOCKSIZE ما یک لیست "بسته" از مقادیر داریم، و من یک نسخه پشتیبان با BLOCKSIZE < 4KB انجام ندادم. MAXTRANSFERSIZE هر عددی که مضرب 64 کیلوبایت است - از 64 کیلوبایت تا 4 مگابایت. پیش فرض روی سیستم من 1024 کیلوبایت است، من 512 - 1024 - 2048 - 4096 را گرفتم.

با BUFFERCOUNT دشوارتر بود - می تواند هر عدد مثبت باشد، اما پیوند می گوید چگونه در BACKUP DATABASE محاسبه می شود و چرا مقادیر بزرگ خطرناک هستند؟. همچنین می‌گوید چگونه می‌توان اطلاعاتی در مورد اینکه واقعاً با کدام BUFFERCOUNT پشتیبان تهیه شده است - برای من 7 است. کاهش آن فایده‌ای نداشت و حد بالایی به صورت آزمایشی کشف شد - با BUFFERCOUNT = 896 و MAXTRANSFERSIZE = 4194304 پشتیبان‌گیری با کاهش یافت. یک خطا (که در مورد آن در لینک بالا نوشته شده است):

Msg 3013, Level 16, State 1, Line 7 BACKUP DATABASE به طور غیر عادی در حال پایان است.

Msg 701, Level 17, State 123, Line 7 حافظه سیستم کافی برای اجرای این پرس و جو در مخزن منبع "پیش فرض" کافی نیست.

برای مقایسه، ابتدا نتایج اجرای یک نسخه پشتیبان را بدون تعیین هیچ پارامتری نشان خواهم داد:

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

خوب، پشتیبان گیری و پشتیبان گیری:

1070072 صفحه برای پایگاه داده 'bt'، فایل 'bt' در فایل 1 پردازش شده است.

2 صفحه برای پایگاه داده 'bt'، فایل 'bt_log' در فایل 1 پردازش شده است.

BACKUP DATABASE 1070074 صفحه را در 53.171 ثانیه (157.227 مگابایت بر ثانیه) با موفقیت پردازش کرد.

خود اسکریپت، با آزمایش پارامترها، در چند ساعت کار کرد، همه اندازه‌گیری‌ها انجام شد صفحه گسترده گوگل. و در اینجا منتخبی از نتایج با سه بهترین زمان اجرا وجود دارد (من سعی کردم یک نمودار خوب ایجاد کنم، اما در پست باید به جدول و در نظرات بسنده کنم @mixsture اضافه شده گرافیک بسیار باحال).

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;

توجه، یک نکته بسیار مهم از @mixsture از تفسیر:

با اطمینان می توان گفت که رابطه بین پارامترها و سرعت پشتیبان گیری در این محدوده از مقادیر تصادفی است، هیچ الگوی وجود ندارد. اما دور شدن از پارامترهای داخلی به وضوح تأثیر خوبی بر نتیجه داشت

آن ها تنها با مدیریت پارامترهای BACKUP استاندارد، زمان حذف نسخه پشتیبان 2 برابر افزایش یافت: 26 ثانیه، در مقابل 53 در ابتدا. این بد نیست، درست است؟ اما باید ببینیم با بازسازی چه اتفاقی می‌افتد. اگر اکنون 4 برابر بیشتر طول بکشد تا بازیابی شود چه؟

ابتدا، بیایید اندازه گیری کنیم که بازیابی یک نسخه پشتیبان با تنظیمات پیش فرض چقدر طول می کشد:

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

خوب، خودت می دانی که راه ها وجود دارد، جایگزینی جایگزین نیست، بازیابی بهبودی نیست. و من این کار را به این صورت انجام می دهم:

1070072 صفحه برای پایگاه داده 'bt'، فایل 'bt' در فایل 1 پردازش شده است.

2 صفحه برای پایگاه داده 'bt'، فایل 'bt_log' در فایل 1 پردازش شده است.

RESTORE DATABASE 1070074 صفحه را در 40.752 ثانیه (205.141 مگابایت بر ثانیه) با موفقیت پردازش کرد.

اکنون سعی می کنم بک آپ های گرفته شده با تغییر BLOCKSIZE، BUFFERCOUNT و MAXTRANSFERSIZE را بازیابی کنم.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE 1070074 صفحه را در 32.283 ثانیه (258.958 مگابایت بر ثانیه) با موفقیت پردازش کرد.

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE 1070074 صفحه را در 32.682 ثانیه (255.796 مگابایت بر ثانیه) با موفقیت پردازش کرد.

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE 1070074 صفحه را در 32.091 ثانیه (260.507 مگابایت بر ثانیه) با موفقیت پردازش کرد.

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE 1070074 صفحه را در 32.401 ثانیه (258.015 مگابایت بر ثانیه) با موفقیت پردازش کرد.

عبارت RESTORE DATABASE در طول بازیابی تغییر نمی کند؛ این پارامترها در آن مشخص نشده اند؛ خود SQL Server آنها را از پشتیبان گیری تعیین می کند. و واضح است که حتی با بهبودی نیز می تواند افزایش یابد - تقریباً 20٪ سریعتر (صادقانه بگویم، من زمان زیادی را برای بازیابی صرف نکردم، چندین "سریع ترین" نسخه پشتیبان را اجرا کردم و مطمئن شدم که هیچ خرابی وجود ندارد.).

در هر صورت، اجازه دهید توضیح دهم که اینها پارامترهایی نیستند که برای همه بهینه باشند. شما فقط با آزمایش می توانید پارامترهای بهینه را برای خود بدست آورید. من این نتایج را گرفتم، شما نتایج متفاوتی خواهید گرفت. اما می‌بینید که می‌توانید نسخه‌های پشتیبان خود را «تنظیم» کنید و در واقع می‌توانند سریع‌تر تشکیل و مستقر شوند.

همچنین اکیداً توصیه می کنم که اسناد را به طور کامل مطالعه کنید، زیرا ممکن است تفاوت های ظریف مختص سیستم شما وجود داشته باشد.

از زمانی که شروع به نوشتن در مورد پشتیبان‌گیری کردم، می‌خواهم فوراً در مورد یک «بهینه‌سازی» دیگر بنویسم که رایج‌تر از پارامترهای «تنظیم» است (تا آنجا که من متوجه شدم، حداقل توسط برخی از ابزارهای پشتیبان، شاید همراه با پارامترها استفاده می‌شود. قبلا توضیح داده شد)، اما هنوز در هابره نیز توضیح داده نشده است.

اگر به خط دوم مستندات، درست در زیر BACKUP DATABASE نگاه کنیم، در آنجا می بینیم:

TO <backup_device> [ ,...n ]

فکر می کنید اگر چندین backup_devices را مشخص کنید چه اتفاقی می افتد؟ نحو اجازه می دهد. و یک چیز بسیار جالب اتفاق می افتد - نسخه پشتیبان به سادگی در چندین دستگاه "گسترش" می یابد. آن ها هر "دستگاه" به طور جداگانه بی فایده خواهد بود، یکی از دست رفته، کل نسخه پشتیبان را از دست داده است. اما چگونه چنین لکه گیری بر سرعت پشتیبان گیری تأثیر می گذارد؟

بیایید سعی کنیم از دو "دستگاه" که در کنار هم در یک پوشه قرار دارند یک نسخه پشتیبان تهیه کنیم:

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

ای پدران دنیا چرا این کار انجام می شود؟

1070072 صفحه برای پایگاه داده 'bt'، فایل 'bt' در فایل 1 پردازش شده است.

2 صفحه پردازش شده برای پایگاه داده 'bt'، فایل 'bt'ورود به فایل 1.

BACKUP DATABASE 1070074 صفحه را در 40.092 ثانیه (208.519 مگابایت بر ثانیه) با موفقیت پردازش کرد.

آیا نسخه پشتیبان 25 درصد سریعتر شده است؟ اگر چند دستگاه دیگر اضافه کنیم چه؟

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

BACKUP DATABASE 1070074 صفحه را در 34.234 ثانیه (244.200 مگابایت بر ثانیه) با موفقیت پردازش کرد.

در مجموع، تنها به این دلیل که نسخه پشتیبان روی 35 فایل روی یک دیسک در یک دیسک نوشته می شود، حدود 4 درصد از زمان تهیه نسخه پشتیبان دریافت می شود. من یک عدد بزرگتر را بررسی کردم - در لپ تاپ من هیچ سودی وجود ندارد، به طور مطلوب - 4 دستگاه. برای شما - نمی دانم، باید بررسی کنید. خوب، به هر حال، اگر این دستگاه ها را دارید - این ها دیسک های واقعاً متفاوتی هستند، تبریک می گویم، سود باید حتی مهم تر باشد.

حالا بیایید در مورد چگونگی بازگرداندن این شادی صحبت کنیم. برای انجام این کار، باید دستور بازیابی را تغییر دهید و همه دستگاه ها را لیست کنید:

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 صفحه را در 38.027 ثانیه (219.842 مگابایت بر ثانیه) با موفقیت پردازش کرد.

کمی سریعتر، اما در جایی نزدیک، قابل توجه نیست. به طور کلی، نسخه پشتیبان سریعتر حذف می شود و به همان روش بازیابی می شود - موفقیت؟ در مورد من، این یک موفقیت کامل است. این مهم است، بنابراین تکرار می کنم - اگر شما اگر حداقل یکی از این فایل ها را گم کنید، کل نسخه پشتیبان را از دست خواهید داد.

اگر به اطلاعات پشتیبان نمایش داده شده با استفاده از Trace Flags 3213 و 3605 در گزارش نگاه کنید، متوجه خواهید شد که هنگام پشتیبان گیری از چندین دستگاه، حداقل تعداد BUFFERCOUNT افزایش می یابد. احتمالاً می توانید سعی کنید پارامترهای بهینه تری را برای BUFFERCOUNT، BLOCKSIZE، MAXTRANSFERSIZE انتخاب کنید، اما من فوراً موفق نشدم و برای انجام دوباره چنین آزمایشی بسیار تنبل بودم، اما برای تعداد متفاوتی از فایل ها. و این شرم آور است در مورد چرخ ها. اگر می خواهید چنین آزمایشی را در خانه سازماندهی کنید، بازسازی اسکریپت دشوار نیست.

در نهایت بیایید در مورد قیمت صحبت کنیم. اگر نسخه پشتیبان به موازات کار کاربران حذف شود، باید رویکرد بسیار مسئولانه ای را برای آزمایش در نظر بگیرید، زیرا اگر نسخه پشتیبان سریعتر حذف شود، دیسک ها بیشتر تحت فشار قرار می گیرند، بار روی پردازنده افزایش می یابد (هنوز باید فشرده سازی کنید. آن را در حال پرواز)، و بر این اساس، پاسخگویی کلی سیستم کاهش می یابد.

شوخی کردم، اما به خوبی می فهمم که هیچ افشاگری نکردم. آنچه در بالا نوشته شده است به سادگی نشان می دهد که چگونه می توانید پارامترهای بهینه برای تهیه نسخه پشتیبان را انتخاب کنید.

به یاد داشته باشید که هر کاری که انجام می دهید با خطر و ریسک خودتان انجام می شود. نسخه پشتیبان خود را بررسی کنید و DBCC CHECKDB را فراموش نکنید.

منبع: www.habr.com