שיחות על Storage – אזהרה ל DBA: המאמר לפניך הולך להכיל מושגי אחסון
לפני זמן מה החלו אצלנו תלונות מצד המפתחים על אטיות משמעותית בביצועים של אחת הסביבות החדשות שהקמנו. המפתחים שבדקו את הסביבה ראו את קצה הקרחון.
להלן מוצג הפתרון שלי לבעיה. הרעיון הכללי לקבל בעיה ולהתחיל לפרק אותה לחלקים – שיטת הסלאמי(סלנג עממי). החלקים:
האפליקציה.
שכבת ביניים – קשר בין האפליקציה לבין הנתונים DAL.
שכבת מסד הנתונים.
שכבת האחסון.
הבעיה: תלונות של המפתחים על איטיות האפליקציה.
השלבים לפתרון: כאשר לקחנו את הקוד הSQL שהורץ (בשלילת פרמטרים כללים – Set Option) והרצנו אותו על שרת הSQL ראינו שהזמנים נשארו איטיים. בעצם המטרה הייתה לשלול את שרת האפליקציה.
בסביבה החדשה שהקמנו(על VM), מסד הנתונים האפליקטיבי בפיתוח היה זהה לכאורה לסביבה החדשה. גם הגדרת השרת הייתה זהה לכאורה –
כמות CPU.
זיכרון פנימי גדול משמעותית.
מערכת הפעלה.
Sys.configurations.
כמות קבצים של TempDB.
ארכיטקטורת השרת תמכה בהגדרה כללית:
כונן F לטובת גיבויים.
כונן D לטובת קבצי המידע.
וכונן L לטובת הלוגים וTempDB.
כאשר כונן L אמור לשבת על LUN שהוא יותר מהיר מD וF.
שלב א – הרצת סקריפטים כללים
במטרה למצוא את הבעיה באותה הסביבה, מספר שאילתות בלטו – שאילתא 1- sp_Blitz – PartOf_sp_Bliz התוצאות –
שאילתא 2 – Glenn Berry – Glenn Berry Diagnostic Queries התוצאות -
שלב ב – פנינו לדוחות של Performance Monitor
הדו"ח נילקח משרת DWH. ובשעות שהרצנו את תהליך הETL ניתן לראות שקיים עומס רב.
Make Long Story Short, ניתן לראות שיש בעיה בגישה לStorage.
מבחינת חלוקת הסלאמי, ניתן לראות שהגענו לקצה הנקניק.
שלב ג - למיקרוסופט יש המלצות מסוימות לגבי העניין – ניתן לשלב בין ההגדרות שביקשנו תחילה לבין הBest Practice של Microsoft. מתוך המלצות Microsoft בחרתי ליישם: א. כונן מהיר = Raid1/0 ב. כוננים נפרדים לקבציי הלוג וTempDB. (שאמורים להיות מוגדרים על כוננים מהירים) – אך לא הבנתי בדיוק למה והגדרתי אותם על אותו הכונן.
בואו וננתח את הרעיון של הפרדת הכוננים ואת יישום לאנשי הStorage – קבצי Data(*.mdf\*.ndf): מהקבצים האלה מתבצעות גם קריאות וגם כתיבות בצורה רנדומאלית על פני הקבצים. קבצי (*.ldf) Log: כאשר אנו כותבים ללוג, הכתיבה מתבצעת באופן עוקב (Sequential) קבצי TempDB: נועדו לקריאות וכתיבות אשר צריכים ביצוע זמני של פעולה/ות(Order By/Temp Table etc…) מסוימות בצורהרנדומאלית. קבצי גיבוי: מתבצעת באופן עוקב (Sequential).
Appendix A – SQL Server I/O Characteristics
כונן לDATA – לתמיכה בקריאה/כתיבה רנדומלית.
כונן לLOG- לכתיבה עוקבת ומהירה.
כונן לTempDB לקריאה/כתיבה רנדומלית ומהירה.
כונן לשמירת הגיבויים בצורה עוקבת.
השאלה היא איך משפיע גורם סוג הקריאה/כתיבה – Sequential/ Random ? אז ההמלצות שמצאתי ברשת הראו-
Sequential – יעבוד טוב עם כונן קשיח. מכוון, כאשר המחט עובדת בצורה רציפה זאת הצורה הכי אופטימלית לכונן בשביל עבודה.
Random – יעבוד מעולה בכונני SSD. מכוון שאין חלקים נעים בflash וקריאות כתיבות על הג'וק הן מאוד מהירות.
http://www.dell.com/downloads/global/products/pvaul/en/ssd_vs_hdd_price_and_performance_study.pdf השאלה השנייה זה איך משפיע גורם המהירות מבחינת הגדרת הRAID בכוננים?
לקריאות/כתיבות רנדומליות (DATA files) העדיפות היא –Raid1/0 (אצלנו כרגע Raid5 עקב החלטה תקציבית).
לקריאות/כתיבות רציפות (LOG files) ניתן להבחין שבכל האופציות המוצגות התוצאה היא זהה, ולכן נבחן את ה- Data Availability ואת הCapacity.
אם נבדוק את המסמכים הרשמיים של המלצות Microsoft, ההמלצה המובהקת היא – Raid1/0.
חשוב לדעת
כל הגדרה של סוג Raid משפיעה לנו על כמות הIO:
הנוסחה כדלקמן –
Functional IOPS = (Raw IOPS * Write%)/(Raid Penalty) + (Raw IOPS * Read %)
כלומר, אם אני בחרתי בRAID5 אני אצטרך לכתוב יותר IO מאשר בRAID10.
נקודות חשובות נוספות:
האם הכונן הלוגי שמוקצה לי בשרת מקבל Private Pool או Shared Pool.
מי שותף איתי Pool במקרה שקיבלנו Shared Pool.
חלוקת הדיסקים הקיימים. במידה והקצו לנו Mixed Pool– כמה אחוזים מכל סוג כונן מוקצים לי?
(N.L.SAS(Sata 7200rpm – IOps 75-100
SAS 15Krpm – 175 -210 IOps
SSD – 400- 9,608,000 IOps – תלוי בדגם – אצלנו זה 3000
מאיזה כונן מתוך אחד מה3 בסעיף הקודם אני מתחיל את סדר הכתיבות בStorage.
כמות הדיסקים שמוקצות לעבודה בכל כונן – בתור המלצות מהרשת המינימום הוא רצועות של 8 דיסקים לפחות.
כמות הCash המוקצת לStorage.
מה היחס קריאה כתיבה לכול כונן.
אז בואו ונמפה מחדש את השאילתא של Brant Ozar וGlenn Berry עם שינויים קלים: MixSharon הקוד ניראה קצת עמוס. אבל התוצאה הרבה יותר ברורה.
איור 3 – תוצאות שאילתא 3נחלק את הפלט קודם לפי הסוג – קריאות/ כתיבות.
לאחר מכן נבדוק את הסוג ע"פ סוג הקובץ. ניתן לראות קיימת בעיה בכל החזיתות. א. כונן המוגדר כDATA – D:\ עומד על 57ms בקריאה ו447ms בכתיבה. – כזכור אנו אמורים לעמוד ב – 25ms. ב. כונן המוגדר לLOG – L:\ עומד על 17ms בקריאה, יחסית בסדר לעומת 10ms(AVG),
אך בכתיבה על אותו כונן (בקבצי TempDB) אנו עומדים על 447ms. ג. אחרון, אלו קבצי TempDB – כאמור אמורים לקבל כונן נפרד, מכוון שסוג הקריאות, כתיבות בו הוא רנדומאלי.
גם כן מחייב להעביר לכונן נפרד (447ms ?!).נקודה חשובה נוספת.
התנהגות הכונן הווירטואלי שאנו מקבלים מאנשי ה Storage מגיע עם הגדרות ברירת מחדל שאינם תמיד זהות להתנהגות אופטימלית של שרת SQL Server. כאשר אנו מקבלים כונן, ההגדרה דיפולטית לגבי גודל הקצאה היא 4kb. ומומלץ לשנות זאת ל64kb.
ניתן לבדוק את הנקודה הזאת בצורה מאוד פשוטה. תיצור קובץ טקסט בכונן הרצוי והכנס תו אחד ושמור. בדוק את מאפייני הקובץ, בשורה של גודל הקובץ בדיסק, נוכל לראות את הגודל המינימאלי.
איור 4 – מאפייני קובץ טקסט.או שניתן לבצע זאת ע"י פקודה בSSMS.
EXEC xp_cmdshell'wmic.exe volume get BLOCKSIZE,Caption'
שאילתא 4 – גודל בלוק להקצאה.
איור 5 – תוצאות שאילתא גודל בלוק להקצאה.קריאה נוספת –
בנוסף, עקב שינוי בגרסאות מערכת ההפעלה, Best Practice של Storage הם לקבוע את הoffset על 1024kb
EXEC xp_cmdshell'wmic.exe partition get StartingOffset, Name, Index, Caption'
שאילתא 5 – Starting Offset.
איור 6 – תוצאות שאילתא Starting Offset.
קריאה נוספת
המלצות לסיכום:
1. כוננים נפרדים -
2. הגדרות לפני מיקום קבצים בכונן :
* בקש מאיש האחסון אלוקציה של 64Kb לבלוק.
* בקש Starting Partition Offset של 1204Kb.
3. Cluster נפרד לשרתי הDB.
אשמח לשמוע חוויות שלכם בנושא.
