SSIS - Buffer Size Optimization

תוכן עניינים

• רקע
• אנלוגיה
• אופן קביעת המשתנים
• דוגמא
• סיכום

רקע

מדריך זה עוסק באחד החלקים החשובים ביותר בפיתוח תהליך ה-ETL, שיפור ביצועים.

ישנן דרכים רבות לשפר את זמני הריצה: הרצת תהליכים במקביל, הורדת שדות שאינן בשימוש, שימוש בזיכרון המחשב (cache memory) וכדומה.

במדריך זה נלמד על אופן קביעת ה-Buffer size, החל מברירת המחדל ועד לאופטימום האפשרי.

אנלוגיה

לצורך הבנת המושג Buffer size והמשתנים השונים, ניעזר בדוגמא הבאה:

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

נסמן ב-X את מספר הארגזים במשאית.

כיצד נמצא את הערך האופטמלי של X? כדי לענות על השאלה יש צורך במספר נתונים:

1. נפח ארגז (Vbox) .

2.נפח משאית (Vtrack).

לפיכך מספר הארגזים האופטימלי במשאית הינו נפח המשאית לחלק לנפח הארגז: Vtrack/Vbox.

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

נשכלל את שתי המשוואת יחד (עם פונקציית מינימום) ונקבל:

X=MIN{ Y , Vtrack/Vbox }

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

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

נחזור כעת ל-SSIS:

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

ב-SSIS קיימים שני פרמטרים:

1. DefaultBufferMaxRows - פרמטר הקובע את מספר השורות בחבילה (מספר ארגזים במשאית). ערך ברירת מחדל שווה ל-10,000.

2. DefaultBufferSize - פרמטר הקובע את נפח החבילה (נפח המשאית). ערך ברירת המחדל שווה ל-10485760 (10MB).

שני פרמטרים אלה עוזרים לנו למצוא את X - מספר שורות בחבילה (מספר הארגזים במשאית).

אופן קביעת המשתנים

כאשר מריצים את ה-Package, המערכת מחשבת את נפח הזיכרון של כל שורה. נניח שנפח שורה (RowSize) הינו 100 Byte. לאחר מכן המערכת מחשבת את גודל החבילה (Buffer) לפי הנוסחה הבאה:

X = MIN {DefaultBufferMaxRows , DefaultBufferSize / RowSize} = MIN { 10,000 , 10,485,760 / 100 } = MIN { 10,000 , 104,857} = 10,000

במקרה זה, נראה את השורות עוברות בזרם המידע בכפולות של 10,000 זאת למרות שיש עוד הרבה מקום ב-Buffer לשורות נוספות.

גודל ה-Buffer הוא 10,485,760 Bytes. מתוכו, הנפח המנוצל הינו נפח שורה (100 Bytes) * מספר השורות ב-Buffer כלומר 1,000,000 Bytes.

המסקנה: רק 9.5% מה-Buffer מנוצל (משאית יוצאת עם 95 ארגזים בלבד במקום 1,000).

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

לכל סוג שדה (Data type) יש נפח שונה כאשר מחרוזת טקסט תלויה באורכה.

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

כעת נחזור לנוסחה שהגדרנו קודם עם כל הנתונים:

X = MIN {DefaultBufferMaxRows , DefaultBufferSize / RowSize}

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

דוגמא

בדוגמא הבאה נראה איך שינוי של הפרמטרים משפיע על X ומכאן על זמן הריצה.

כאשר אנו מריצים Package, ישנה כתיבה לטבלת Log המכילה נתונים אודות הריצה (מתי תהליך מתחיל, מסיים וכו'). נניח ואנו רוצים להעתיק את הנתונים מטבלה זו לטבלה אחרת:

נריץ תחילה את ה-Package עם ערכי ברירת המחדל של הפרמטרים: DefaultBufferMaxRows = 10,000 ו- DefaultBufferSize = 10,485,760 לצורךבדיקת זמן הריצה:

 זמן ריצה כולל - מעל 25 דקות:  

כעת נראה איך ניתן לשפר נתון זה. תחילה נעבור על השדות השונים ונחשב את הנפח בזיכרון:

בזרם המידע עוברים שלושה שדות, נחשב את הנפח שלהם בזיכרון על פי הטבלה לעיל:

1*DT_I4 = 1*8 = 8

2*DT_DBTIMESTAMP = 2*20 = 40

Additional row Bytes = 20

Total = 8 + 40 + 20 = 68 Bytes

מספר השורות בכל Buffer הינו 10,000 (ערך ברירת המחדל). נחשב את ערכו האופטימלי:

X = DefaultBufferSize / RowSize = 10,485,760 / 68 = 154,202

X = MIN {DefaultBufferMaxRows , 154,202}

נשנה תחילה את DefaultBufferMaxRows ל-100,000 ונראה את ההשפעה על זמן הריצה:

זמן ריצה כולל - 11 דקות (שיפור של 14 דקות).

נשנה את DefaultBufferMaxRows ל-150,000:

זמן ריצה כולל - פחות מ-10 דקות:

סיכום תוצאות:

למעשה, ניתן לראות שככל שנתקרב לערך האופטימלי (אחוז התפוסה ב-Buffer קרוב ל-100%), זמן הריצה קטן.

שימו לב איך שינוי קטן של פרמטר יכול להשפיע באופן דרמטי על זמן הריצה.

הערות:

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

2. באותו אופן ניתן לשנות את פרמטר ה-DefaultBufferSize (אין צורך לשחק עם שני הפרמטרים, מספיק עם אחד בלבד).

3. לכל Data Flow ישנם ערכים שונים של שני הפרמטרים. אם ב-Data Flow מסוים קיימים יותר מ-Data Source אחד, יש לחשב לכל אחד בנפרד את נפח השורה ולהתייחס לזה המקסימלי בנוסחה. 4. מתי כדאי לחשב ערכים חדשים? כאשר מדובר בנפחים גדולים של נתונים (אם היינו שולפים אלף נתונים אין זה משנה אם גודל ה-Buffer הוא 10,000 או 150,000).

4. מתי כדאי לחשב ערכים חדשים? כאשר מדובר בנפחים גדולים של נתונים (אם היינו שולפים אלף נתונים אין זה משנה אם גודל ה-Buffer הוא 10,000 או 150,000).

סיכום

שינוי מספר הרשומות בכל Buffer יכול להשפיע דרמטית על ביצועי ה-Package. ככל שנתקרב לערכים האופטימליים של שני הפרמטרים, כך נקבל ביצועים טובים יותר של זמני הריצה.

בהצלחה!