MS SQL Server: النسخ الاحتياطي على المنشطات

انتظر! انتظر! صحيح أن هذه ليست مقالة أخرى حول أنواع النسخ الاحتياطية لـ SQL Server. لن أتحدث حتى عن الاختلافات بين نماذج الاسترداد وكيفية التعامل مع السجل المتضخم.

ربما (ربما فقط)، بعد قراءة هذا المنشور، ستتمكن من التأكد من أن النسخة الاحتياطية التي تمت إزالتها منك باستخدام الوسائل القياسية ستتم إزالتها ليلة الغد، حسنًا، أسرع بمقدار 1.5 مرة. وفقط بسبب حقيقة أنك تستخدم المزيد من معلمات قاعدة بيانات النسخ الاحتياطي.

إذا كان محتوى المنشور واضحًا لك، فأنا آسف. لقد قرأت كل ما توصلت إليه Google بشأن عبارة "النسخ الاحتياطي لخادم habr sql"، ولم أجد في أي مقال أي ذكر لحقيقة أن وقت النسخ الاحتياطي يمكن أن يتأثر بطريقة ما باستخدام المعلمات.

سألفت انتباهكم على الفور إلى تعليق ألكسندر جلادشينكو (@mssqlhelp):

لا تقم مطلقًا بتغيير معلمات BUFFERCOUNT وBLOCKSIZE وMAXTRANSFERSIZE في الإنتاج. إنها مصنوعة فقط لكتابة مثل هذه المقالات. ومن الناحية العملية، سوف تتخلص من مشاكل الذاكرة في وقت قصير.

سيكون من الرائع بالطبع أن تكون الأذكى وتنشر محتوى حصريًا، ولكن لسوء الحظ، ليس هذا هو الحال. هناك مقالات/منشورات باللغتين الإنجليزية والروسية (أنا دائمًا في حيرة بشأن ما أسميها بشكل صحيح) مخصصة لهذا الموضوع. فيما يلي بعض ما صادفته: وقت, اثنان, ثلاثة (على sql.ru).

لذا، في البداية، سوف أرفق بناء جملة النسخ الاحتياطي المبسط قليلاً من MSDN (بالمناسبة، كتبت أعلاه عن قاعدة بيانات النسخ الاحتياطي، ولكن كل هذا ينطبق على كل من النسخ الاحتياطي لسجل المعاملات والنسخ الاحتياطي التفاضلي، ولكن ربما بتأثير أقل وضوحًا):

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_variable } - يشير إلى حجم الكتلة الفعلية بالبايت. الأحجام المدعومة هي 512 و1024 و2048 و4096 و8192 و16 و384 و32 بايت (768 كيلوبايت). القيمة الافتراضية هي 65 لأجهزة الشريط و536 للأجهزة الأخرى. عادةً ما تكون هذه المعلمة غير ضرورية لأن عبارة BACKUP تحدد تلقائيًا حجم الكتلة المناسب للجهاز. يؤدي تعيين حجم الكتلة بشكل صريح إلى تجاوز التحديد التلقائي لحجم الكتلة.

العدد المؤقت = { com.buffercount | @ buffercount_variable } - يحدد إجمالي عدد مخازن الإدخال/الإخراج المؤقتة التي سيتم استخدامها لعملية النسخ الاحتياطي. يمكنك تحديد أي قيمة عددية موجبة، ولكن وجود عدد كبير من المخازن المؤقتة قد يتسبب في حدوث خطأ نفاد الذاكرة بسبب زيادة مساحة العنوان الظاهرية في عملية Sqlservr.exe.

يتم تحديد إجمالي المساحة المستخدمة بواسطة المخازن المؤقتة بواسطة الصيغة التالية: BUFFERCOUNT * MAXTRANSFERSIZE.

ماكس ترانسفيرسيزي = { com.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
);

مبدأ البرنامج النصي بسيط - حلقات متداخلة، كل منها تغير قيمة معلمة واحدة، وإدراج هذه المعلمات في أمر النسخ الاحتياطي، وحفظ السجل الأخير مع التاريخ من 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. في الوقت الحالي، سأخبرك فقط عن المعلمات التي أضعها في قاعدة بيانات النسخ الاحتياطي.

بالنسبة لـ BLOCKSIZE، لدينا قائمة "مغلقة" من القيم، ولم أقم بإجراء نسخة احتياطية بحجم BLOCKSIZE < 4 كيلو بايت. MAXTRANSFERSIZE أي رقم من مضاعفات 64 كيلو بايت - من 64 كيلو بايت إلى 4 ميجابايت. الافتراضي على نظامي هو 1024 كيلو بايت، أخذت 512 - 1024 - 2048 - 4096.

كان الأمر أكثر صعوبة مع BUFFERCOUNT - يمكن أن يكون أي رقم موجب، لكن الرابط يقول كيف يتم حسابها في قاعدة بيانات النسخ الاحتياطي ولماذا تعتبر القيم الكبيرة خطيرة؟. كما توضح أيضًا كيفية الحصول على معلومات حول العدد BUFFERCOUNT الذي تم إنشاء النسخة الاحتياطية به بالفعل - بالنسبة لي هو 7. لم يكن هناك أي فائدة في تقليله، وتم اكتشاف الحد الأعلى تجريبيًا - مع BUFFERCOUNT = 896 وMAXTRANSFERSIZE = 4194304 سقطت النسخة الاحتياطية خطأ (مكتوب عنه في الرابط أعلاه):

Msg 3013، المستوى 16، الحالة 1، السطر 7 يتم إنهاء قاعدة بيانات النسخ الاحتياطي بشكل غير طبيعي.

الرسالة 701، المستوى 17، الحالة 123، السطر 7 لا توجد ذاكرة نظام كافية في تجمع الموارد "الافتراضي" لتشغيل هذا الاستعلام.

للمقارنة، سأعرض أولاً نتائج تشغيل النسخة الاحتياطية دون تحديد أي معلمات على الإطلاق:

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

حسنًا ، النسخ الاحتياطي والنسخ الاحتياطي:

تمت معالجة 1070072 صفحة لقاعدة البيانات "bt"، والملف "bt" في الملف 1.

تمت معالجة صفحتين لقاعدة البيانات "bt"، والملف "bt_log" في الملف 2.

نجحت قاعدة بيانات النسخ الاحتياطي في معالجة 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 من تعليقات:

يمكننا أن نقول بثقة أن العلاقة بين المعلمات وسرعة النسخ الاحتياطي ضمن نطاقات القيم هذه عشوائية، ولا يوجد نمط. لكن من الواضح أن الابتعاد عن المعلمات المضمنة كان له تأثير جيد على النتيجة

أولئك. فقط من خلال إدارة معلمات النسخ الاحتياطي القياسية تم تحقيق مكاسب مضاعفة في وقت إزالة النسخة الاحتياطية: 2 ثانية، مقابل 26 في البداية. هذا ليس سيئا، أليس كذلك؟ ولكن علينا أن نرى ما سيحدث مع عملية الترميم. ماذا لو استغرق التعافي 53 مرات وقتًا أطول؟

أولاً، دعونا نقيس المدة التي تستغرقها استعادة النسخة الاحتياطية بالإعدادات الافتراضية:

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

حسنًا، أنت بنفسك تعلم أن الطرق موجودة، والاستبدال لا يعني الاستبدال، والاسترداد ليس استردادًا. وأنا أفعل ذلك مثل هذا:

تمت معالجة 1070072 صفحة لقاعدة البيانات "bt"، والملف "bt" في الملف 1.

تمت معالجة صفحتين لقاعدة البيانات "bt"، والملف "bt_log" في الملف 2.

نجحت عملية استعادة قاعدة البيانات في معالجة 1070074 صفحة في 40.752 ثانية (205.141 ميجابايت/ثانية).

سأحاول الآن استعادة النسخ الاحتياطية التي تم التقاطها باستخدام BLOCKSIZE وBUFFERCOUNT وMAXTRANSFERSIZE.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

نجحت عملية استعادة قاعدة البيانات في معالجة 1070074 صفحة في 32.283 ثانية (258.958 ميجابايت/ثانية).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

نجحت عملية استعادة قاعدة البيانات في معالجة 1070074 صفحة في 32.682 ثانية (255.796 ميجابايت/ثانية).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

نجحت عملية استعادة قاعدة البيانات في معالجة 1070074 صفحة في 32.091 ثانية (260.507 ميجابايت/ثانية).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

نجحت عملية استعادة قاعدة البيانات في معالجة 1070074 صفحة في 32.401 ثانية (258.015 ميجابايت/ثانية).

لا يتغير بيان RESTORE DATABASE أثناء الاسترداد؛ ولم يتم تحديد هذه المعلمات فيه؛ ويحددها SQL Server نفسه من النسخة الاحتياطية. ومن الواضح أنه حتى مع التعافي يمكن أن يكون هناك ربح - أسرع بنسبة 20٪ تقريبًا (لأكون صادقًا، لم أقضي الكثير من الوقت في عملية الاسترداد، لقد قمت بإجراء العديد من عمليات النسخ الاحتياطي "الأسرع" وتأكدت من عدم وجود أي تدهور).

فقط في حالة، اسمحوا لي أن أوضح أن هذه ليست بعض المعلمات الأمثل للجميع. يمكنك فقط الحصول على المعلمات المثالية لنفسك عن طريق الاختبار. لقد حصلت على هذه النتائج، وسوف تحصل على نتائج مختلفة. لكنك ترى أنه يمكنك "ضبط" النسخ الاحتياطية الخاصة بك ويمكنها فعليًا تشكيلها ونشرها بشكل أسرع.

أوصي أيضًا بشدة بقراءة الوثائق بأكملها، لأنه قد تكون هناك فروق دقيقة خاصة بنظامك.

منذ أن بدأت الكتابة عن النسخ الاحتياطية، أريد أن أكتب على الفور عن "تحسين" آخر، وهو أكثر شيوعًا من معلمات "الضبط" (بقدر ما أفهم، يتم استخدامه بواسطة بعض أدوات النسخ الاحتياطي على الأقل، ربما مع المعلمات) الموصوفة سابقًا)، ولكن لم يتم وصفها بعد على حبري أيضًا.

إذا نظرنا إلى السطر الثاني في الوثائق، أسفل قاعدة بيانات النسخ الاحتياطي مباشرةً، فسنرى:

TO <backup_device> [ ,...n ]

ما رأيك سيحدث إذا قمت بتحديد عدة أجهزة النسخ الاحتياطي؟ بناء الجملة يسمح بذلك. وسيحدث شيء مثير للاهتمام - سيتم ببساطة "نشر" النسخة الاحتياطية عبر عدة أجهزة. أولئك. سيكون كل "جهاز" على حدة عديم الفائدة، وسيفقد واحدًا، وستفقد النسخة الاحتياطية بأكملها. ولكن كيف سيؤثر هذا التلطيخ على سرعة النسخ الاحتياطي؟

دعنا نحاول عمل نسخة احتياطية على "جهازين" موجودين جنبًا إلى جنب في نفس المجلد:

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

يا آباء العالم لماذا يحدث هذا؟

تمت معالجة 1070072 صفحة لقاعدة البيانات "bt"، والملف "bt" في الملف 1.

تمت معالجة صفحتين لقاعدة البيانات "bt"، الملف "bt"قم بتسجيل الدخول إلى الملف 1.

نجحت قاعدة بيانات النسخ الاحتياطي في معالجة 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;

نجحت قاعدة بيانات النسخ الاحتياطي في معالجة 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;

نجحت عملية استعادة قاعدة البيانات في معالجة 1070074 صفحة في 38.027 ثانية (219.842 ميجابايت/ثانية).

أسرع قليلا، ولكن في مكان ما قريب، ليست كبيرة. بشكل عام، تتم إزالة النسخة الاحتياطية بشكل أسرع واستعادتها بنفس الطريقة - النجاح؟ بالنسبة لي، إنه نجاح كبير. هذا مهملذلك أكرر - إذا كنت إذا فقدت واحدًا على الأقل من هذه الملفات، فستفقد النسخة الاحتياطية بأكملها.

إذا نظرت في السجل إلى معلومات النسخ الاحتياطي المعروضة باستخدام Trace Flags 3213 و3605، ستلاحظ أنه عند النسخ الاحتياطي لعدة أجهزة، يزداد عدد BUFFERCOUNT على الأقل. ربما، يمكنك محاولة تحديد المزيد من المعلمات الأمثل لـ BUFFERCOUNT، BLOCKSIZE، MAXTRANSFERSIZE، لكنني لم أنجح على الفور، وكنت كسولًا جدًا لإجراء مثل هذا الاختبار مرة أخرى، ولكن لعدد مختلف من الملفات. ومن المؤسف للعجلات. إذا كنت ترغب في تنظيم مثل هذا الاختبار في المنزل، فليس من الصعب إعادة صياغة البرنامج النصي.

وأخيرا، دعونا نتحدث عن السعر. إذا تمت إزالة النسخة الاحتياطية بالتوازي مع عمل المستخدمين، فأنت بحاجة إلى اتباع نهج مسؤول للغاية للاختبار، لأنه إذا تمت إزالة النسخة الاحتياطية بشكل أسرع، فإن الأقراص متوترة أكثر، ويزداد الحمل على المعالج (لا يزال يتعين عليك ضغط على الطاير)، وبالتالي، تنخفض الاستجابة الشاملة للنظام.

مجرد مزاح، لكنني أفهم جيدًا أنني لم أكشف عن أي شيء. ما هو مكتوب أعلاه هو مجرد عرض لكيفية تحديد المعلمات المثلى لأخذ النسخ الاحتياطية.

تذكر أن كل ما تفعله يتم على مسؤوليتك الخاصة. تحقق من النسخ الاحتياطية الخاصة بك ولا تنس DBCC CHECKDB.

المصدر: www.habr.com