שיפור ביצועים לגיבוי שיחזור? מה, מה קשור ביצועים לגיבוי?

19/03/2015

אין תגובות

ניר לוי

DBA בפרוייקט ממשלתי מטעם נאיה טכנולוגיות


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

דוגמא לשיחת מסדרון:

מנהל פיתוח : כמה זמן ייקח הגיבוי שיחזור  .

DBA: (בלב) גיבוי בערך 40 ד' שיחזור בערך 20 ד' , ההההמ… בערך שעה.

 לי בכל אופן היה נראה שהדבר היחיד שיכול לשנות את מהירות גיבוי שיחזור הוא החומרה.

אך לא כך הם פניי הדברים.

 במאמר הנ"ל אנו נעבוד על שיפור ביצועים לגיבוי מלא(FULL).

* כאשר במאמר מדובר על  גיבוי הכוונה גם לשחזור בהקשר של אופטימיזציה.

קצת מסביב :

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

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

אחרי שסגרנו את הקטע האומנותי(לוגי), נרד לשטח .

נתוני השרת:

Sql Server : Microsoft SQL Server 2014 Enterprise Edition

  זיכרון 64GB

מעבד: 2 מעבדי Xeon 3.07GHz

בסיס נתונים בגודל 248GB

כוננים: כונן בודד שמשמש לקבציי ה-DATA  וה-BAK(נתונים וגיבויים)- (לא Best Practice)

מתחילים:

ע"מ לנתר את הגיבוי יש להפעיל את ה-TRACE FLAG

הבאים:

מספר הסבר Script
3213 מספק הסבר יותר מפורט על פעולת הגיבוי והשחזור בתוך ה- Error Log  DBCC TRACEON (3213)
3605 שולח פירוט פעולות DBCC ל-error log של השרת DBCC TRACEON (3605)

ניסוי מספר :1 נתחיל בבדיקת גיבוי בררת המחדל .

DBCC TRACEON (3213)
DBCC TRACEON (3605)
BACKUP DATABASE [XXX] TO  DISK = N'D:\SQL\Backup\XXX\Test1'
 WITH  COPY_ONLY, NOFORMAT, NOINIT,  NAME = N'XXX-Full Database Backup',
  SKIP, NOREWIND,COMPRESSION, NOUNLOAD,  STATS = 10

נריץ  xp_readerrorlog ע"מ לקבל את נתוני ההרצה:

BufferCount:                7

Sets Of Buffers:            3

MaxTransferSize:            1024 KB=1MB

Min MaxTransferSize:        64 KB

Total buffer space:         21 MB

Tabular data device count:  1

Fulltext data device count: 0

Filestream device count:    1

TXF device count:           0

Filesystem i/o alignment:   512

Media Buffer count:         7

Media Buffer size:          1024KB

BufferCount מספר התאים בזיכרון ("מספר קרונות ברציף") אותם אנו מקצים לגיבוי
Sets Of Buffers מספר הקבוצות בהם משתמש המנוע ליצירת הגיבוי, כאשר סוג הגיבוי הוא מכווץ, מספר הקבוצות הוא שלוש, גיבוי רגיל מספר הקבוצות 1.
MaxTransferSize גודל יחידה("גודל כל קרון") כלומר גודל Buffer
Total buffer space סה"כ הזיכרון(סה"כ אורך הרכבת) שאנו לוקחים לטובת הגיבוי.

כלומר החישוב הוא:

  BufferCount*  MaxTransferSize* Sets Of Buffers= Total buffer space

במקרה שלנו השימוש הכולל בזיכרון לפי החישוב הנ"ל

7*3*1=MB21

תוצאות ניסוי מספר 1: הגיבוי בקונפיגורציה הנ"ל ארך 29 דקות.

כעת ננסה להגדיל את כמות הזיכרון לטובת הגיבוי, כמובן בהתחשב בזיכרון הכללי בשרת

ע"מ לחשב כמה זיכרון ניתן להקצות נשתמש בסקריפט הבא:

DECLARE @SqlServerMemory TINYINT
DECLARE @Totalmemory NUMERIC(10, 2) 
DECLARE @CPUNum TINYINT
DECLARE @BufferSizeMB INT = 6
DECLARE @BufferCount INT 
DECLARE @MemoryBackupMB INT 


SELECT  @CPUNum = cpu_count
FROM    sys.dm_os_sys_info
SELECT  @Totalmemory = CONVERT(FLOAT, physical_memory_kb) / 1024 / 1024
FROM    sys.dm_os_sys_info;
SELECT  @SqlServerMemory = cntr_value / 1024 / 1024
FROM    sys.dm_os_performance_counters
WHERE   counter_name = 'Total Server Memory (KB)'
SELECT  @Totalmemory = ( @Totalmemory - @SqlServerMemory ) * 0.3

SELECT  @MemoryBackupMB = ( @Totalmemory ) * 1024
SELECT  @BufferCount = @MemoryBackupMB / @BufferSizeMB

SELECT  @Totalmemory AS TotalmemoryGB ,
        @BufferCount AS BufferCount ,
        @BufferSizeMB / 3 * 1024 * 1024 AS BufferSizeB ,
        @MemoryBackupMB AS MemoryBackupMB ,
        @CPUNum AS CPUNum ,
        @SqlServerMemory AS 'SqlServerMemory'

 כעת נריץ את הקוד ונקבל את התוצאות הנ"ל

SqlServerMemory  CPUNum  MemoryBackupMB  BufferSizeB  BufferCount  TotalmemoryGBForbackUp 
49 4               4608 2097152 768 4.50
  • המטרה שלנו היא להקצות מספיק זיכרון לתהליך, כך שזרימת הנתונים לכוננים תהיה בקצב היכולת של הכוננים לקרוא ולכתוב. בפעולת הגיבוי, ברירת המחדל ניא הקצאת מעט זיכרון. כך שנוצר צוואר בקבוק בזיכרון, ולא ממומשות יכולות הכוננים .

 ניסוי מספר 2 : ע"מ להגדיל את הזיכרון באופן מבוקר לתהליך הגיבוי נשתמש בתוצאות בדיקת המערכת שלמעלה.

DBCC TRACEON (3213)
DBCC TRACEON (3605)
BACKUP DATABASE [XXX] 
TO  DISK = N'D:\SQL\Backup\XXX\Test1'
 WITH FORMAT, INIT,  NAME = N'XXX-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, ,COMPRESSION
MAXTRANSFERSIZE = 2097152, BUFFERCOUNT=768

 נריץ  xp_readerrorlog ע"מ לקבל את נתוני ההרצה:

 Memory limit:               12287MB

BufferCount:                768

Sets Of Buffers:            3

MaxTransferSize:            2048 KB

Min MaxTransferSize:        64 KB

Total buffer space:         4608 MB

Tabular data device count:  1

Fulltext data device count: 0

Filestream device count:    1

TXF device count:           0

Filesystem i/o alignment:   512

Media Buffer count:         768

Media Buffer size:          2048KB

Encode Buffer count:        768

 כפי שאנו רואים סה"כ הזיכרון לביצוע הגיבוי Total buffer space: 4608 MB

 תוצאות הניסוי 2: הגיבוי ארך 15.23 ד'  שיפור משמעותי של 50%

 ניסוי מספר 3: הקטנת מספר הקרונות(BUFFERCOUNT) מ-768  ל-  256 כלומר הקטנת גודל הזיכרון.

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

DBCC TRACEON (3213)
DBCC TRACEON (3605)
BACKUP DATABASE [XXX] 
TO  DISK = N'D:\SQL\Backup\XXX\Test1'
 WITH FORMAT, INIT,  NAME = N'XXX-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, ,COMPRESSION,
MAXTRANSFERSIZE = 2097152, BUFFERCOUNT=256

 נתוני הרצה :

Memory limit:               12287MB

BufferCount:                256

Sets Of Buffers:            3

MaxTransferSize:            2048 KB

Min MaxTransferSize:        64 KB

Total buffer space:         1536 MB

Tabular data device count:  1

Fulltext data device count: 0

Filestream device count:    1

TXF device count:           0

Filesystem i/o alignment:   512

Media Buffer count:         256

Media Buffer size:          2048KB

Encode Buffer count:        256

 תוצאות ניסוי 3: הגיבוי ארך 14.37 ד, שיפור קל

 ניסוי מספר 4: הגדלת מספר הקרונות(BUFFERCOUNT) מ-256 ל-  512 כלומר הגדלת גודל הזיכרון.

ראינו כי נוצר שיפור קל, עכשיו אנו מבצעים דיוקים.

DBCC TRACEON (3213)
DBCC TRACEON (3605)
BACKUP DATABASE XXX 
TO  DISK = N'D:\SQL\Backup\XXX\Test1'
 WITH FORMAT, INIT,  NAME = N'XXX-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10, ,COMPRESSION, 
MAXTRANSFERSIZE = 2097152, BUFFERCOUNT=512

נתוני הגיבוי

Memory limit: 12287MB

BufferCount:                512

Sets Of Buffers:            3

MaxTransferSize:            2048 KB

Min MaxTransferSize:        64 KB

Total buffer space:         3072 MB

Tabular data device count:  1

Fulltext data device count: 0

Filestream device count:    1

TXF device count:           0

Filesystem i/o alignment:   512

Media Buffer count:          512

Media Buffer size:          2048KB

Encode Buffer count:         512

 תוצאות ניסוי מספר 4 : אורך הגיבוי 12:33 דקות קבלנו שיפור של 2.50 דקות לעומת הנוסי הקודם.

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

בפועל בסביבה בה אני מבצע את הניסוי לא קבלתי שיפור כלל (לקח 13 ד').

לביצוע מהלך זה יש חיסרון שהוא כמובן תחזוקה של מספר קבצים מקשה על התהליך.

 להלן הקטע קוד והתוצאות:

DBCC TRACEON (3213)
DBCC TRACEON (3605)
BACKUP DATABASE [XXX] 
TO  DISK = N'D:\SQL\Backup\XXX\Test1',
    DISK = N'D:\SQL\Backup\XXX\Test2',
    DISK = N'D:\SQL\Backup\XXX\Test3',
    DISK = N'D:\SQL\Backup\XXX\Test4'
	WITH  COPY_ONLY, FORMAT, INIT,
	  NAME = XXX-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10,COMPRESSION,
	  MAXTRANSFERSIZE = 2097152, BUFFERCOUNT=512

 תוצאות ניסוי מספר 5: הגיבוי ארך 13 דקות, לא קבלנו שיפור

 שחזורים

*בתהליך שחזור חישוב הזיכרון משתנה מפני שהשרת מקצה שני סטים(Sets Of Buffers:2)

 ניסוי מספר 6: ביצוע שחזור  שיחזור במצב ברירת מחדל (GUI)

DBCC TRACEON (3213)
DBCC TRACEON (3605)
USE [master]
RESTORE DATABASE [XXX] FROM  DISK = N'D:\SQL\Backup\XXX\Test1' WITH  FILE = 1, 
NOUNLOAD,  STATS = 5

תוצאות ניסוי מספר 6 :ארך 13:35 ד' בהגדרת ברירת מחדל

פרמטרים של השחזור :

Memory limit: 8191MB

BufferCount:                6

Sets Of Buffers:            2

MaxTransferSize:            1024 KB

Min MaxTransferSize:        1024 KB

Total buffer space:         12 MB

Tabular data device count:  1

Fulltext data device count: 0

Filestream device count:    1

TXF device count:           0

Filesystem i/o alignment:   512

Media Buffer count:         6

Media Buffer size:          1024KB

Encode Buffer count:           6

  ניסוי מספר 7 הוספת זיכרון לתהליך : שיחזור עפ"י הגדרות אשר מגדילות את השימוש בזיכרון

DBCC TRACEON (3213)
DBCC TRACEON (3605)
USE [master]
RESTORE DATABASE [XXX] FROM  DISK = N'D:\SQL\Backup\XXX\Test1' WITH  FILE = 1, 
NOUNLOAD,  STATS = 5 ,MAXTRANSFERSIZE = 2097152, BUFFERCOUNT=512

 תוצאות ניסוי 7: הגיבוי ערך 11:00 ד' שיפור של 15% לערך

ניסוי  8 : כעת ננסה להשתמש ביותר זיכרון נכפיל את גודל ה- BUFFER לצורך השיחזור:

DBCC TRACEON (3213)
DBCC TRACEON (3605)
USE [master]
RESTORE DATABASE [XXX] FROM  DISK = N'D:\SQL\Backup\XXX\Test1' WITH  FILE = 1, 
NOUNLOAD,  STATS = 5 ,MAXTRANSFERSIZE = 4194304
, BUFFERCOUNT=512

תוצאות ניסוי  8: השחזור ארך 11:51 כלומר קבלנו כלומר קבלנו האטה:

הגענו לקצה היכולות של הכוננים ולכן נוצרו תורים מה שמצריך ניהול שלהם ובכך יוצר תוספת עומס על השחזור

ניסוי  9 : כעת נשחזר ממספר קבצים(4 ככמות המעבדים בשרת):

DBCC TRACEON (3213)
DBCC TRACEON (3605)
USE [master]
RESTORE DATABASE [XXX]
FROM  DISK = N'D:\SQL\Backup\Customs_Dev\Test1',  
DISK = N'D:\SQL\Backup\Customs_Dev\2', 
 DISK = N'D:\SQL\Backup\Customs_Dev\3',  
 DISK = N'D:\SQL\Backup\Customs_Dev\4' 
 WITH  FILE = 1,MAXTRANSFERSIZE = 2097152, BUFFERCOUNT=512 ,  NOUNLOAD,  STATS = 5

 תוצאות ניסוי  9: השחזור ארך 11:40 לא קבלנו שיפור:

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

 ניסוי  10 : כעת נשחזר ממספר קבצים והכפלת הזיכרון(4 ככמות המעבדים בשרת):

 תוצאות ניסוי  10: זמן הגיבוי כמעט הוכפל 18:13 דקות

מסקנה: הגדלת זיכרון מעבר ליכולות הכונן לקרוא ולכתוב מאט את התהליך

DBCC TRACEON (3213)
DBCC TRACEON (3605)
USE [master]
RESTORE DATABASE [Customs_Dev] 
FROM  DISK = N'D:\SQL\Backup\Customs_Dev\Test1',  
DISK = N'D:\SQL\Backup\Customs_Dev\2', 
DISK = N'D:\SQL\Backup\Customs_Dev\3',  
DISK = N'D:\SQL\Backup\Customs_Dev\4' 
WITH  FILE = 1,MAXTRANSFERSIZE = 2097152, BUFFERCOUNT=1024 ,  NOUNLOAD,  STATS = 5

לסיכום :

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

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

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

השיפור המשמעותי הוא מימוש מקסימום היכולת של הדיסקים והזיכרון.

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

מימשנו את כל הזיכרון, סיימנו, לא ניתן לשפר מעבר לכך.

 כאשר מדובר במצבי משבר או בתהליכי גרסה בהם השרת לשירותכם בלבד, אני ממליץ לשנות את הגדרות ברירת המחדל ולא לחשוש ליצור עומס על השרת.  לא יתווכחו  אתכם אם הגיבוי יארך 15 דקות במקום 13 דקות, אך חסכון של יותר מ-50%  במעבר מ-29 דקות לא 12.5 דקות, משמעותי ביותר, ביחוד במצבי משבר.

 המאמר נכתב בהשראת סרטון Pass2014 של שון מקוואן

בכותרת: PERFORMANCE TUNING YOUR BACKUPS [DBA-307]

 תודה לצוות מכס דור חדש שרון רימר ומולי כהן על כיוון והנחיה.

הוסף תגובה
facebook linkedin twitter email

כתיבת תגובה

האימייל לא יוצג באתר. שדות החובה מסומנים *