מדריך זה עוסק באחד החלקים החשובים ביותר בפיתוח תהליך ה-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) של כל שורה:
סוג השדה | גודל קבוע | גודל משתנה |
---|---|---|
DT_STR | 5 | 1 |
DT_R8 | 12 | 0 |
DT_DBTIMESTAMP | 20 | 0 |
DT_NUMERIC | 23 | 0 |
DT_I1 | 5 | 0 |
DT_I2 | 6 | 0 |
DT_I4 | 8 | 0 |
לכל סוג שדה (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 דקות:
סיכום תוצאות:
מספר דוגמא | DefaultBufferMaxRows | אחוז תפוסה | זמן ריצה |
1 | 10,000 | 6.5% | 25:33 |
2 | 100,000 | 65% | 11:01 |
3 | 150,000 | 97.2% | 9:55 |
למעשה, ניתן לראות שככל שנתקרב לערך האופטימלי (אחוז התפוסה ב-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. ככל שנתקרב לערכים האופטימליים של שני הפרמטרים, כך נקבל ביצועים טובים יותר של זמני הריצה.
בהצלחה!
היי מאור,
החישוב שלך צריך לכלול רק את השדות העוברים בזרם המידע (שים לב שצילום המסך בכתבה נלקח מזרם המידע שיוצא מה-Source). אם בטבלה יש 20 עמודות ואתה מסמן רק שלושה, אז רק אותם אתה מכניס לחישוב שלך.
נושא זה מוזכר בהמון כתבות על אופטימיזציה של Packages. יש לבחור אך ורק את השדות שאתה משתמש בהם לאורך ה-Package. אחרת, אתה ממלא את ה-Buffer בשדות שאין בהם צורך וזה פוגע בביצועים.
אם ניעזר באנלוגיה שבכתבה, זה כאילו שאתה ממלא את המשאית שאתה שולח למאפייה בארגזי בגדים ושמיכות, למרות שאין בהם שימוש במאפייה. חשוב למלא את המשאית באופן אופטימלי אך ורק במוצרים הנדרשים למאפייה.
בהצלחה!
היי מאור,
הוספה של שדות נוספים (דרך Lookup, Derived Columns וכו') לא משפיעה על מספר השורות ב-Buffer. כאשר מוסיפים רכיבים א-סינכרונים (Sort, Aggregeate וכדו'), ה-Buffer מחושב מחדש ופוגע בביצועים.
גם אני לא נתקלתי במידע אודות נפח הזיכרון של כל משתנה. הדרך שבה גיליתי את הערך האמיתי היא שימוש ב-Cache Connection. כאשר מריצים את ה-Package ניתן לראות בחלון ה-Progress את כמות הרשומות והנפח שלהן שנכתבו ל-Cache. חילוק של הנפח במספר הרשומות יתן לך את הנפח של השורה.
בהצלחה!
תודה רבה עמית, כתבה מצוינת!
שאלה,
מה לגבי כמו העמודות הנקראת ב source, האם אין לזה השפעה בביצועים או על הבאפרים?
נניח ואנו קוראים ב source עשרים עמודות, אך מעבירים הלאה רק שלוש מהן, כמה עמודות יכנסו לבאפר? שלוש או עשרים? איך לבצע את החישוב.
תודה,
מאור