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 KB). ערך ברירת המחדל הוא 65 עבור התקני טייפ ו-536 עבור מכשירים אחרים. בדרך כלל פרמטר זה אינו נחוץ מכיוון שהצהרת BACKUP בוחרת אוטומטית את גודל הבלוק המתאים עבור ההתקן. הגדרת גודל הבלוק עוקפת באופן מפורש את בחירת גודל הבלוק האוטומטית.

BUFFERCOUNT = { ספירת חיץ | @ buffercount_variable } - מגדיר את המספר הכולל של מאגרי קלט/פלט שישמשו עבור פעולת הגיבוי. אתה יכול לציין כל ערך שלם חיובי, אך מספר רב של מאגרים עלול לגרום לשגיאה מחוץ לזיכרון עקב שטח כתובות וירטואלי מוגזם בתהליך Sqlservr.exe.

כמות החלל הכוללת המשמשת מאגרים נקבעת על ידי הנוסחה הבאה: BUFFERCOUNT * MAXTRANSFERSIZE.

MAXTRANSFERSIZE = { maxtransfersize | @ maxtransfersize_variable } מציין את גודל מנות הנתונים הגדול ביותר, בבתים, להחלפה בין SQL Server למדיה של ערכת הגיבוי. תומכים בכפולות של 65 בתים (536 קילו-בייט) עד 64 בתים (4 מגה-בייט).

אני נשבע - קראתי את זה בעבר, אבל לא עלה בדעתי כמה השפעה יכולה להיות להם על הפרודוקטיביות. יתרה מכך, כנראה, אני צריך לעשות סוג של "יציאה" ולהודות שגם עכשיו אני לא לגמרי מבין מה בדיוק הם עושים. אני כנראה צריך לקרוא עוד על קלט/פלט מאוחסן ועבודה עם כונן קשיח. מתישהו אני אעשה את זה, אבל בינתיים אני יכול פשוט לכתוב סקריפט שיבדוק איך הערכים האלה משפיעים על המהירות שבה הגיבוי מתבצע.

הכנתי מסד נתונים קטן, בגודל של כ-10 ג'יגה-בייט, שמתי אותו על ה-SSD, ושמתי את הספרייה לגיבויים ב-HDD.

אני יוצר טבלה זמנית לאחסון התוצאות (אין לי אותה זמנית, אז אני יכול לחפור בתוצאות ביתר פירוט, אבל אתה מחליט בעצמך):

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 כל מספר שהוא כפולה של 64KB - מ-64KB ל-4MB. ברירת המחדל במערכת שלי היא 1024KB, לקחתי 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 MB/sec).

התסריט עצמו, בדיקת הפרמטרים, עבד תוך כמה שעות, כל המדידות היו בפנים גיליון אלקטרוני של גוגל. והנה מבחר תוצאות עם שלושת זמני הביצוע הטובים ביותר (ניסיתי לעשות גרף יפה, אבל בפוסט אצטרך להסתפק בטבלה, ובתגובות @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 MB/sec).

כעת אנסה לשחזר גיבויים שצולמו עם BLOCKSIZE, BUFFERCOUNT ו-MAXTRANSFERSIZE שהשתנו.

BLOCKSIZE = 16384, BUFFERCOUNT = 224, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE עיבד בהצלחה 1070074 דפים תוך 32.283 שניות (258.958 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE עיבד בהצלחה 1070074 דפים תוך 32.682 שניות (255.796 MB/sec).

BLOCKSIZE = 16384, BUFFERCOUNT = 448, MAXTRANSFERSIZE = 2097152

RESTORE DATABASE עיבד בהצלחה 1070074 דפים תוך 32.091 שניות (260.507 MB/sec).

BLOCKSIZE = 4096, BUFFERCOUNT = 56, MAXTRANSFERSIZE = 4194304

RESTORE DATABASE עיבד בהצלחה 1070074 דפים תוך 32.401 שניות (258.015 MB/sec).

הצהרת RESTORE DATABASE אינה משתנה במהלך השחזור; פרמטרים אלה אינם מצוינים בה; SQL Server עצמו קובע אותם מהגיבוי. וברור שגם עם התאוששות יכול להיות רווח - כמעט 20% מהר יותר (למען האמת, לא השקעתי זמן רב בהתאוששות, עברתי כמה מהגיבויים ה"מהירים" ביותר ווידאתי שאין הידרדרות).

ליתר בטחון, הרשו לי להבהיר שלא מדובר בפרמטרים אופטימליים עבור כולם. אתה יכול לקבל את הפרמטרים האופטימליים עבור עצמך רק על ידי בדיקה. קיבלתי את התוצאות האלה, אתה תקבל תוצאות שונות. אבל אתה רואה שאתה יכול "לכוון" את הגיבויים שלך והם יכולים למעשה להיווצר ולפרוס מהר יותר.

אני גם ממליץ לך בחום לקרוא את התיעוד במלואו, כי ייתכנו ניואנסים ספציפיים למערכת שלך.

מאז שהתחלתי לכתוב על גיבויים, אני רוצה מיד לכתוב על עוד "אופטימיזציה" אחת, שהיא יותר נפוצה מפרמטרים של "כוונן" (למיטב הבנתי, היא משמשת לפחות כמה כלי גיבוי, אולי יחד עם הפרמטרים שתואר קודם לכן), אבל זה עדיין לא תואר גם על Habré.

אם נסתכל על השורה השנייה בתיעוד, ממש מתחת ל-BACKUP DATABASE, שם נראה:

TO <backup_device> [ ,...n ]

מה לדעתך יקרה אם תציין מספר התקני גיבוי? התחביר מאפשר זאת. ודבר מעניין מאוד יקרה - הגיבוי פשוט "יתפזר" על פני מספר מכשירים. הָהֵן. כל "מכשיר" בנפרד יהיה חסר תועלת, איבד אחד, איבד את כל הגיבוי. אבל איך מריחה כזו תשפיע על מהירות הגיבוי?

בואו ננסה לבצע גיבוי בשני "מכשירים" הממוקמים זה לצד זה באותה תיקייה:

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

אבות העולם, למה זה נעשה?

עיבדו 1070072 דפים עבור מסד הנתונים 'bt', קובץ 'bt' בקובץ 1.

עיבדו 2 עמודים עבור מסד הנתונים 'bt', קובץ 'bt'log' בקובץ 1.

BACKUP DATABASE עיבד בהצלחה 1070074 עמודים תוך 40.092 שניות (208.519 MB/sec).

האם הגיבוי נעשה מהיר יותר ב-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 MB/sec).

בסך הכל הרווח הוא כ-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 MB/sec).

קצת יותר מהר, אבל איפשהו קרוב, לא משמעותי. באופן כללי, הגיבוי מוסר מהר יותר ומשוחזר באותו אופן - הצלחה? מבחינתי, זו די הצלחה. זֶה הוא חשוב, אז אני חוזר - אם אתה אם אתה מאבד לפחות אחד מהקבצים האלה, אתה מאבד את כל הגיבוי.

אם תסתכל ביומן על מידע הגיבוי המוצג באמצעות Trace Flags 3213 ו-3605, תבחין כי בעת גיבוי למספר מכשירים, לפחות מספר ה-BUFFERCOUNT עולה. כנראה, אתה יכול לנסות לבחור פרמטרים אופטימליים יותר עבור BUFFERCOUNT, BLOCKSIZE, MAXTRANSFERSIZE, אבל לא הצלחתי מיד, והתעצלתי לבצע שוב בדיקות כאלה, אבל למספר שונה של קבצים. וחבל על הגלגלים. אם אתה רוצה לארגן בדיקות כאלה בבית, זה לא קשה לעשות מחדש את התסריט.

לבסוף, בואו נדבר על המחיר. אם הגיבוי מוסר במקביל לעבודת המשתמשים, אתה צריך לנקוט בגישה אחראית מאוד לבדיקה, כי אם הגיבוי יוסר מהר יותר, הדיסקים מתאמצים יותר, העומס על המעבד גדל (עדיין צריך לדחוס זה תוך כדי תנועה), ובהתאם לכך, ההיענות הכללית של המערכת פוחתת.

סתם בצחוק, אבל אני מבין היטב שלא גיליתי שום גילוי. מה שכתוב למעלה הוא פשוט הדגמה של איך אתה יכול לבחור את הפרמטרים האופטימליים ללקיחת גיבויים.

זכרו שכל מה שאתם עושים נעשה על אחריותכם ועל אחריותכם בלבד. בדוק את הגיבויים שלך ואל תשכח את DBCC CHECKDB.

מקור: www.habr.com