• Register
תנו לנו לייק וקבלו עדכונים ישירות לפיד

SSIS - Buffer Size Optimization

פורסם על ידי ב ב קטגוריה ראשית
  • גודל פונט: Larger Smaller
  • Hits: 4574
  • 4 תגובות
  • עשו מנוי לעדכונים מהבלוג
  • Print

תוכן עניינים


רקע

מדריך זה עוסק באחד החלקים החשובים ביותר בפיתוח תהליך ה-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).

Default Values

שני פרמטרים אלה עוזרים לנו למצוא את 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 לצורךבדיקת זמן הריצה:

Default - 1

 

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

Default - 2

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

Data Types

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

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 ונראה את ההשפעה על זמן הריצה:

Optimize - 1

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

Optimize - 2

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

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

3

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

מספר דוגמא 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. ככל שנתקרב לערכים האופטימליים של שני הפרמטרים, כך נקבל ביצועים טובים יותר של זמני הריצה.

בהצלחה!

תגיות BI

תגובות

  • מאור
    מאור רביעי, 08 אפריל 2015

    תודה רבה עמית, כתבה מצוינת!

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

    תודה,
    מאור

  • Amit Aharon
    Amit Aharon רביעי, 08 אפריל 2015

    היי מאור,

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

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

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

    בהצלחה!

  • Shmuel M
    Shmuel M שלישי, 28 מרס 2017

    היי עמית ,כתבה נהדרת , ממש עזרה לי .
    2 שאלות:
    1. איך להתחשב כאן ב - LOOKUPS? וכו'? שמוסיפים לזרם המידע.
    2. האם תוכל למקור מידע שנותן כמה כל DATATYPE ב SSIS שוקל? משום מה לא ניתקלתי.

    תודה!

  • Guest
    עמית חמישי, 30 מרס 2017

    היי מאור,

    הוספה של שדות נוספים (דרך Lookup, Derived Columns וכו') לא משפיעה על מספר השורות ב-Buffer. כאשר מוסיפים רכיבים א-סינכרונים (Sort, Aggregeate וכדו'), ה-Buffer מחושב מחדש ופוגע בביצועים.

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

    בהצלחה!

כיתבו תגובה

Guest שבת, 23 ספטמבר 2017

Sap

Oracle

Informatica

SAS

Microstrategy

IBM

Microsoft

Qlikview

Teradata

Webfocus

קליק אחד ואתם מחוברים. מהיר .קל .מאובטח.

מעניין? שתפו דף זה באמצעות הטלפון הנייד

SSIS - Buffer Size Optimization - מערכת הבלוגים של אתר dwh.co.il - QR Code Friendly
Powered by QR Code Friendly

מאמרים

מגמות של ביג דאטה בעולם הביטוח
CA Technologies
SSIS - Buffer Size Optimization
קטגוריה ראשית
בדיקות BI ו-DWH לעומת הבדיקות בתחומים אחרים
קטגוריה ראשית
איסוף דרישות לפרויקטי BI
קטגוריה ראשית
כח המידע במיקוד
קטגוריה ראשית
0

הדף שלנו בפייסבוק

התחברות

כניסות למאמרים
6571311