none
גיבוי ואירכוב בסיס נתונים גדול RRS feed

  • דיון כללי

  • בחברה שאני עובד יש בסיס נתונים מרכזי אחד, אשר הגיבוי שלו הוא כ 60G עם גידול של 5G לשנה או יותר.

    התבקשתי להציע פתרונות לגיבוי יעיל יותר. כרגע הם מגבים כל יום Full . 

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

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

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

    CREATE PARTITION FUNCTION
    CREATE PARTITION SCHEME  

    יש לי מספר שאלות:

    1) האם ביצוע Full backup בסוף כל שבוע עם הסתמכות על Differentials נפוצה ותקינה כפתרון לבעיה?

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

    יום רביעי 11 ינואר 2017 09:00

כל התגובות

  • אהלן מתניה

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

    עד 10 GB זה נחשב בסיס מאוד קטן מאוד, ולכן גם יש את האפשרות להחזיק אותו בגרסה החינמית של SQL Express.
    אני הייתי אומר שבין 10 לבין 50 זה נחשב קטן (רגיל)
    בסיס נתונים גדול נמדד בעשרות או מאות טרה בייט ובסיס נתונים ענק יכול להגיע גם לגדלים מרשימים הרבה יותר :-)

    כדי לשים דברים בפרופורציה שרתי SQL מאפשרים תיאורטית עבודה עם מסדי נתונים בגודל של 524,272 טרה בייט
    * אני לא מכיר מסד נתונים בפועל אבל כמה מאות טרה בייט בהחלט אפשר למצוא בקלות

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

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

    >> התבקשתי להציע פתרונות לגיבוי יעיל יותר. כרגע הם מגבים כל יום Full .

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

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

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

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

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

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

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

    ניהול הגיבויים לשני מערכות אלא יההי שונה לחלוטין!
    וכמובן שיש הרבה מאוד אפשרויות ואפיונים שונים של מערכות.

    מה השלב הבא?!?

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

    2. אני ממליץ בחום לבצע חיפוש בגוגל על צירוף המילים הבא לועבור על כמה בלוגים בנושא:
    sql server partitioned minimal tables size best practices

    יש הרבה חומר על כללי אצבע וניסיון של אחרים

    לדוגמה הנה מאמר טוב מאוד שמצאתי:
    How To Decide if You Should Use Table Partitioning

    וגם בקישור הבא מצאתי כמה דברי חוכמה:
    http://dba.stackexchange.com/questions/87467/at-what-point-should-i-split-or-partition-a-very-large-but-simple-table

    >> הבעיה היא שלא מצאתי כלי אוטומטי שמבצע זאת

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

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

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

    בדרך כלל במערכות כבדות יחסית טוענים את המידע לטבלאות Staging -> יוצרים אינדקסים ותנאים אחרי שהמידע בטבלה -> ומבצעים פעולה של switch להעברת המחיצה לטבלת הארכיון הסופית (נקראות טבלאות fact). הפעולה של switch מחיצה שלמה בין טבלאות ללא כמעט זמן היא אחד הייתרונות הבולטים ביותר של שמוש במחיצות.

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

    >> 1) האם ביצוע Full backup בסוף כל שבוע עם הסתמכות על Differentials נפוצה ותקינה כפתרון לבעיה?

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

    >> 2)האם partition מחליף לאורך זמן את הצורך להעביר נתונים לבסיס נתונים נפרד לארכיב. כלומר אני שואל, האם התוצאה מבחינת הביצועים תהיה זהה.

    כמובן שהתשובה שלי תהיה תמיד "תלוי" :-)

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

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

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

    >> וזה מוביל למחשבה שבסופו של דבר הכרחי להפריד את הנתונים הישנים לבסיס נתונים אחר?

    הכרחי לא אבל כמו שהזכרתי בהחלט נפוץ ויכול לעזור מאוד במקרים שונים :-)

    ....

    אני מקווה שזה נותן לך את התשובות להתחלה ונקודת מוצא :-)
    מחיצות זה נושא מתקדם עם הרבה אפשרויות לדיון


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]

    יום שני 16 ינואר 2017 11:27
    מנחה דיון
  • ממש תודה, זה ממש מאלף. 

    העלת בדבריך נושא חדש עבורי, ואני מתחיל ללמוד עליו: sql server partition staging table.

    כמו כן אעיין בשאר הלינקים וההפניות.

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

    כמו כן יש שם עוד מספר טבלאות בעלות 20 מליון רשומות. עם נתונים שרובם ארכיון.

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

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

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




    • נערך על-ידי Matanya Zac יום ראשון 22 ינואר 2017 21:23
    יום ראשון 22 ינואר 2017 08:27
  • הי מתניה,

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

    -----------------------------
    גיא גלנצר
    יועץ והוגה דעות
    http://www.madeiradata.com

    יום שלישי 31 ינואר 2017 06:46
    מנחה דיון
  • צהריים טובים מתן,

    מצטער אבל ניראה לי שהשאלה שלך התפספסה (נשכחה על ידי) משום מה

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

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

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

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

    מצטער :-)

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

    * לא לנצל את האפשרות צהזו לרעה בבקשה!
    אם לא עבר פרק זמן סביר של לפחות שבוע אז אין להקפיץ הודעות

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

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

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

    כיצד המסמכים שמורים במסד הנתונים?!?

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

    >> מדובר בגודל כולל של כל בסיס הנתונים כ 800 גיג

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

    >> הנה כמה נקודות כלליות שאתה מזכיר שאומרים לנו מעט על אופן השימוש

    1. כמו כן יש שם עוד מספר טבלאות בעלות 20 מליון רשומות. עם נתונים שרובם ארכיון.
    2. המסמכים אינם משתנים לאחר הכנסתם או לפחות לאחר שעברו מספר חודשים.

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

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

    >> המצב כרגע שהם עושים גיבוי Full, שלוקח להם שעות רבות

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

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

    אני דיי בטוח (רק מנחש ללא מידע!) שניהול הקבצים של מסדי הנתונים השונים כולל tempDB למשל לא נעשה אצלכם בצורה קרובה לצורה מיטבית. ההחלטה על מה להעביר לדיסקים מהירים כרוכה בעובדות מספריות של כמה מקום יש על דיסקים מהירים וכמה כאלו יש לכם כמובן (אלו החלטות הקשורות למשאבים שאתם יכולים להשקיע או למשאבים קיימים). ייתכן שעדיף להעביר משהו אחר על מנת ללשפר את הפעילות הנוכחית :-)

    האם בדקתם נעילות והמתנות בשרת?

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

    ----------------------

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

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

    אני ממליץ לעבור על הקישור הבא לגבי שיטות שונות לשמירת קבצים במסד הנתונים:
    https://msdn.microsoft.com/en-us/library/hh403405.aspx

    בקיצור השיטות השונות הן:
    1. שמירת קבצים חיצונית וניהול ידני שלהם
    2. שימוש בטור בינארי במסד הנתונים
    3. שימוש ב FILESTREAM
    4. שימוש ב FileTable

    לא עברתי עדיין על כל הזפר אבל תראה מה מצאתי!
    ספר של 487 עמודים רק על הנושא של שמירת קבצים במסד הנתונים :-)
    אני מאוד ממליץ להוריד את הספר ולקרוא לעומק (אני מתכוון לעבור עליו לעומק בהמשך כשיהיה לי זמן... כמעט 500 עמודים... בטוח משהו יכול לעור לכל אחד אם המידע עדכני ומדוייק)
    http://assets.red-gate.com/community/books/art-of-ss-filestream.pdf


    signature   Ronen Ariely
     [Personal Site]    [Blog]    [Facebook]    [Linkedin]


    יום ראשון 12 פברואר 2017 13:07
    מנחה דיון