צעד אחר צעד: הקמת תשתית לטיפול בלוגים מבוססת ELK (חלק שני)

בחלק הקודם של הפוסט הצגתי דיזיין אפשרי לתשתית לוגים– שכולל את RabbitMQ כמערכת ההודעות שלנו, logstash בתפקיד רכיב פירסור הלוגים, ElasticSearch בתפקיד מנוע האינדוקס (ולמעשה מסד הנתונים של כל התשתית) ו- Kibana בתור רכיב הויזואליזציה שיאפשר לנו לדעת מה קורה במערכת. להזכירכם – כך זה נראה:

בפוסט הקודם הראיתי איך מבצעים את שלבי הקמת התשתית:  התקנת ElasticSearch גם על windows וגם על linux וקינפוג של cluster, התקנת RabbitMQ והתקנת וקינפוג logstash. בפוסט הזה נמשיך עם התהליך ונשלים את שתי הקוביות החסרות: service שיאפשר כתיבה ל-log והתקנת וקינפוג Kibana, שיאפשר לנו לצפות בהודעות הלוג שלנו.

Service קליטת ההודעות

בתשתית שאנחנו מקימים – ברורה לנו מה המטרה הסופית. אנחנו רוצים יכולת לראות ולנהל את הלוגים שלנו בצורה נוחה. בפוסט הקודם גם הקמנו את התשתית הדרושה לנו לאכסון ולאחזור המידע, ובהמשך נדבר על הויזואליזציה של זה עבורנו – איך נתחקר את הלוגים.
אבל, שאלה בסיסית היא איך מתבצעת קליטת ההודעות מהאפליקציות השונות שכותבות ללוג – מדובר למעשה על החלק שאני סימנתי בתרשים למעלה בתור ה- “Logging Service”. מטרתו של ה- logging service פשוטה: להיות endpoint שמולו האפליקציות שכותבות ללוג מדברות, לקבל מהם את ההודעה ולכתוב ל- RabbitMQ בפורמט JSON מוסכם.

אפשר לשאול, ובצדק, מה התועלת בסרביס הזה אם דברים יכולים לכתוב את ההודעות ישירות ל- RabbitMQ? יותר מזה – אפשר לקנפג את logstash עצמו כדי שיקרא את הלוג הטקסטואלי של כל אחת מהאפליקציות שלנו ויכתוב אותו פשוט ישירות ל- ElasticSearch.

יש לכך כמה סיבות:

  • סכימה: כל אחת ואחת מהתחנות בדרך, היא schema-less. אף אחת מהן לא אוכפת עבורנו באף רמה את הסכימה של המידע שהיא מעבדת. זה יתרון מצד אחד, וחסרון מצד שני. החסרון בא לידי ביטוי כשנרצה לתשאל – אם יהיו לנו מיליון שדות שאומרים אותו דבר, ועבור כל אפליקציה חדשה שמגיעה לכתוב הודעות היא תכתוב אותם בפורמט קצת שונה, ככה שיתקבלו שדות קצת שונים – אם נרצה לשאול שאלות פשוטות כמו “איזה הודעות קריטיות היו” או “איזה exceptions חוזרים הכי הרבה פעמים” או כל שאלה הכי פשוטה שתהיה – זאת תהיה משימה מורכבת שתדרוש תחזוקה, כי דברים לא יהיו באותו מבנה.
    אם כולם ידברו מול service מרכזי, שיקבל את המידע וייצר JSON שאותו הוא יכתוב לראביט שהוא כבר בפורמט המוסכם – אזי נפתור את הבעייה הזאת.
  • להימנע מ- Polling: העבודה מול RabbitMQ כשה- logstash מקבל ממנו input טובה משמעותית מלקנפג את ה- logstash לעשות tail ללוגים של אפליקציות שונות מבחינת ביצועים – כי ל- polling יש עלות.
  • להקל את התחזוקה: אם יש service מרכזי שאוכף פורמט מוסכם, לא נידרש לערוך קונפיגורציות כשנרצה להכניס אפליקציות נוספות.

פורמט ההודעה

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

{

  "EventDateTime": "2016-12-14-21:02:07.149",

  "SourceApplication": "MyApp",

  "SourceModule": "MyModule",

  "SourceFile": null,

  "TextMessage": "error occured while doing math operation",

  "Level": "Error",

  "MessageVersion": 1,

  "ExceptionMessage": "Attempted to divide by zero.",

  "ExceptionType": "DivideByZeroException",

  "ExceptionStackTrace": "   at LogGenerator.Program.Main(String[] args) in c:\\users\\shaha\\Google Drive\\Documents\\Visual Studio 2015\\Projects\\LogGenerator\\LogGenerator\\Program.cs:line 18",

  "ExceptionString": "System.DivideByZeroException: Attempted to divide by zero.\r\n   at LogGenerator.Program.Main(String[] args) in c:\\users\\shaha\\Google Drive\\Documents\\Visual Studio 2015\\Projects\\LogGenerator\\LogGenerator\\Program.cs:line 18",

  "AdditionalField_FirstNum": 5,

  "AdditionalField_SecondNum": 0

}

האובייקט שהגדרתי אצלי לטובת הבדיקות נראה כך:

public enum VerboseLevel

  {

      Unspecified = 0,

      Debug,

      Verbose,

      Error,

      Critical

  }

  public class LogMessage

  {

      private readonly Dictionary<string, object> _additionalFields = new Dictionary<string, object>();

      public LogMessage(string sourceApp, string sourceModule)

      {

          SourceApplication = sourceApp;

          SourceModule = sourceModule;

      }

 

      public LogMessage(string sourceApp, string sourceModule, string msg, Exception exception = null) : this(sourceApp, sourceModule)

      {

          TextMessage = msg;

          if (exception != null)

          {

              Exception = exception;

              VerboseLevel = VerboseLevel.Error;

          }

      }

 

      public readonly int MessageVersion = 1;

      public string SourceApplication { get; set; }

      public string SourceModule { get; set; }

      public string SourceFile { get; set; }

      public Exception Exception { get; set; }

      public string TextMessage { get; set; }

      public DateTime EventDateTime { get; set; } = DateTime.Now;

      public VerboseLevel VerboseLevel { get; set; }

 

      public object this[string fieldName]

      {

          get { return _additionalFields[fieldName]; }

          set { _additionalFields[fieldName] = value; }

      }

 

      public string ToJson()

      {

          JObject obj = new JObject

          {

              {"EventDateTime", EventDateTime.ToString("yyyy-MM-dd-HH:mm:ss.fff")},

              {"SourceApplication", SourceApplication },

              {"SourceModule", SourceModule },

              {"SourceFile", SourceFile },

              {"TextMessage",TextMessage },

              {"Level",VerboseLevel.ToString() },

              {"MessageVersion", MessageVersion },

          };

          if (Exception != null)

          {

              obj.Add("ExceptionMessage", Exception.Message);

              obj.Add("ExceptionType", Exception.GetType().Name);

              obj.Add("ExceptionStackTrace", Exception.StackTrace);

              obj.Add("ExceptionString", Exception.ToString());

          }

          foreach (var additionalField in _additionalFields)

          {

              obj.Add($"AdditionalField_{additionalField.Key}", JToken.FromObject(additionalField.Value));

          }

          return obj.ToString(Formatting.Indented);

      }

  }

זמנים

אחד הדברים החשובים במעקב אחרי הודעות הוא נושא הזמן. למעשה, הזמן הוא השדה היחיד ש-logstash בקונפיגורציה שעשינו בפוסט הקודם מתייחס אליו באופן מיוחד. הוא מזהה את שדה ה- EventDateTime לפי הפורמט שקבענו בקונפיגורציית logstash בפוסט הקודם (שהוא בכוונה שונה מהפורמט ה-JSON-י הרגיל כדי להראות איך מקנפגים את זה), ומתייחס אליו בתור שדה ה- timestamp שמולו נעשה את כל ה- time based filtering כשנגיע לתחקור המידע. ולכן, חשוב לוודא שתמיד קיים השדה הזה ושהוא מפורסר כהלכה.

נקודה נוספת שחשוב לשים לב אליה בהקשרי זמנים היא רמת הדיוק. שדה התאריך והשעה ב-ElasticSearch נשמר עד לרמת המילי-שניות. זה אולי נשמע מדוייק, אבל זה ממש לא מדוייק מספיק. למשל, באפליקציה שפועלת וכותבת לוגים בקצב גבוהה יחסית, אנחנו עלולים בהחלט להיות במצב שכמה רשומות לוג נכתבו כשהשוני בינהן הוא מעבר לרוזולציית המילי-שניות. במקרה כזה, כאשר ננסה לחקור איזושהי בעייה שדורשת מאיתנו להסתכל על הודעות הלוג האלה, ונרצה באופן טבעי למיין אותם לפי הזמן שבו הם נכתבו – כדי שנעקוב אחרי הדברים בסדר ההתרחשות שלהם, אנחנו עלולים לראות דברים בסדר לא נכון. אירועים שקרו קודם יופיעו כאילו הם קרו אח”כ ולהיפך. צריך להיות מודעים לכך, כי זה עלול לבלבל. ניתן גם לעשות workaround מסויים שיתאים בחלק מהמקרים (בעיקר אם יש לנו פיקים במהלך השנייה, אבל באופן כללי יש לנו פחות מ-1000 הודעות בשנייה) והוא ברמת ה- service לדרוס את שדה המילי-שניות בהודעה ככה שהוא יהיה עולה באופן מלאכותי לפי סדר קליטת ההודעה ב- service כך שההודעה הראשונה בשנייה מסויימת תקבל את הערך 1 ל-ms שלה, השנייה את הערך 2 וכו’. כמובן שזה לא מתאים בכל המצבים, ופוגע את היכולת לעשות התאמות-זמנים מול מקורות מידע אחרים (אם ישנם כאלה), אבל זה יכול להיות שימושי.

ה- service עצמו

כאמור, הפונקציונאליות שה- service הזה אמור לספק היא מאד פשוטה: לקבל את המידע הנדרש (בד”כ שדות מסגרת מסויימים, כפי שניתן לראות באובייקט שהגדרתי קודם ובנוסף תמיכה בשדות חופשיים שממומשים באובייקט ששמתי קודם באמצעות Dictionary שאנחנו מסרלזים להודעה את הערכים שלו) ולכתוב אותו בתור JSON כמו שהראיתי קודם ל-RabbitMQ.

מכיוון שהפוסט הזה והקודם לא מוגבלים לפלטפורמות מסויימות, או לשפות פיתוח מסויימות – אני לא רואה טעם להעמיק ולהראות איך לממש service פשוט כזה, וכל אחד יכול לממש אותו בהתאם לצרכים שלו ולהעדפות שלו (ASP.NET Core, WCF, Node.JS, Python, PHP…). עם זאת, אני כן אפנה לתיעוד המעולה של RabbitMQ שמסביר טוב מאד איך כותבים אליו הודעות מכל שפה שיש SDK רשמי של RabbitMQ עבורה. מרגע שקיבלתם את המידע, וכתבתם את ההודעה ל- RabbitMQ (הודעה בדומה לפורמט שהראיתי קודם) – logstash שקינפגנו קודם יעשה את השאר, יקבל את ההודעה, יעשה עליה פירסור מינימלי בשביל לחלץ את שדה הזמן, ויכניס אותה ל-ElasticSearch.

Kibana

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

התקנה

תחילה יש להיכנס לעמוד ההורדה ולהוריד את הגרסא המתאימה. בהסבר פה אני מדבר על גרסא 5.1.1 ל- Windows. את ה- ZIP שהורדנו נפתח לתוך c:\Kibana. לאחר מכן ניכנס ל c:\Kibana\Config ונערוך את kibana.yml כדי להגדיר את הכתובת לשרת ה- ElasticSearch שלנו (השדה שנקרא elasticsearch.url).  בנוסף, נרצה לשנות את זה שנוכל לגשת ל- Kibana מרחוק (ולא רק מהשרת שעליו היא מותקנת). כך זה ייראה אחרי שערכנו את ההגדרות (שימו לב שפה ה-Kibana מותקנת על אותו node שעליו מותקן ElasticSearch):

image_thumb5

לאחר מכן, נריץ את ה- bat שנמצא ב c:\kibana\bin\kibana.bat. אחרי כמה שניות נראה את ההודעות הבאות:

image_thumb1

וזה אומר ש- Kibana מוכנה לשימוש – רק צריך לגלוש על המכונה שבה התקנו ל http://localhost:5601. כשנגלוש, יופיע לנו המסך הבא:

image_thumb3

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

[הערת אגב: כמובן שבסביבת production נרצה להתקין את Kibana בתור windows service, ואת זה אפשר לעשות עם nssm כמו שהראיתי בפוסט הקודם בסדרה עבור logstash, פשוט עם הכוונה ל- batch file של kibana.bat במקום לזה של logstash].

שימוש

image_thumb7

image_thumb12כאשר ניכנס ל- Kibana פעם ראשונה זה מה שנראה. בואו נבין רגע איזה חלקים יש לנו למסך. מימין למעלה, יש לנו את פילטר הזמנים. מדובר באחד האלמנטים הכי חשובים, וזה שמאפשר למעשה לעבד ולנהל כמות גדולה של לוגים בביצועים טובים – פשוט לא מסתכלים על כולם. בקונפיגורציה שבה עשינו, למעשה יש אינדקס לכל יום. גם בתוך אותו היום, יש לנו שדה @timestamp שמאפשר פילטור לפי הזמנים. לכל חיפוש שלא נעשה, כל תצוגה שלא נסתכל עליה – פילטר הזמנים מלווה אותנו. אנחנו יכולים לשנות ולהגדיל אותו מ-15 דק’ לזמנים ארוכים יותר, לזמנים שבין תאריכים מסויימים – ולפי זה ייקבע איזה נתונים אנחנו רואים.

 

מצד שמאל, יש לנו למעשה את הבחירה בין שלושת סוגי התצוגה העיקריים של Kibana: תצוגת ה- Discover (שעליה אנחנו נמצאים עכשיו), ה- Visualise (שמאפשרת לנו להכין ויזואליזציות של נתונים שונים) וה- Dashboard שמאפשר לנו לשלב את כל אותם הויזואליזציות ל- dashboards נוחים.

מלמעלה, אנחנו יכולים לראות את תיבת החיפוש. כזכור, הכל מתבסס פה על ElasticSearch שהוא מעל הכל שרת אינדוקס טקסטואלי. אנחנו יכולים לחפש מילה מסויימת, לחפש ביטויים לוגיים שונים (exception AND (sql OR hadoop)) וכו’.

אם נלחץ על החץ שליד אחד השורות שמופיעות, נוכל לראות בצורה טבלאית את השדות ששמורים עבור ה- document המסויים:

image_thumb14

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

image_thumb16

נוכל כמובן גם לשמור את החיפוש הזה (שזה אומר גם את החיפוש, הפילטורים שעשינו, והתצוגה שבחרנו) ע”י לחיצה על כפתור ה- save. למשל, אם נלחץ על סימן הכוכב שיש כשפותחים את אחת הרשומות ליד שדה ה- Exception Message, נוכל לייצר לנו תצוגה שכוללת רק את השגיאות שקרו – כלומר, תוכלו לראות שאין כבר שורות שאין להן Exception Message, ומתחת לשורת החיפוש מופיע ה-filter שלנו שמיישם את זה:

image_thumb18

יצירת Dashboard

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

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

 

חלופות

ה- stack הטכנולוגי שראינו בפוסט הקודם ובפוסט הזה, מתבסס כולו על כלי open-source חינמיים, כאלה שאתם יכולים כבר היום להוריד ולהתקין. עם זאת, כמובן שלא מדובר בפיתרון היחיד בתחום ניהול הלוגים. אי אפשר לכתוב פוסט על לוגים בלי לכל הפחות להזכיר את Splunk,  המתחרה המסחרי החזק ביותר בתחום כנראה. בנוסף, יש לא מעט חברות שמציעות גם את פלטטפורמת ה- ELK בתור service ענני – במקום להקים את התשתיות אצלכם. ניתן למנות ברשימה את logit.io, או את הסטארטאפ הישראלי logz.io.

סיכום

בפוסט הקודם ובפוסט הזה סקרתי את כל מה שצריך כדי להתחיל ולהרים תשתית לוגים מבוססת על ELK stack: משתמשת ב- ElasticSearch כמנוע אינדוקס, logstash כמנוע קליטת הלוגים (למרות שהשימוש שעשינו בו היה מינימליסטי מאד בהשוואה ליכולות הרחבות שלו) ו- Kibana שנגענו בה קצת לטובת ויזואליזציה של הלוגים.

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

בהצלחה.

צעד אחר צעד: הקמת תשתית לטיפול בלוגים מבוססת ELK (חלק ראשון)

כאשר מדברים על טיפול בכמויות גדולות של מידע (“ביג-דאטה”), אחת הדוגמאות הנפוצות היא טיפול בלוגים. הסיבה היא שטיפול בלוגים משלב ביחד כמה אתגרים: יש הרבה מאד “event-ים” (הודעות לוג במקרה הזה), קצב ההגעה שלהם גדול (אפליקציות כותבות הרבה הודעות לוג), אנחנו רוצים לנתח אותם בצורות שונות שכוללות בין היתר גם חיפוש full text search על מחרוזות שונות כדי למצוא הודעות שמעניינות אותנו, וגם ביצוע אגרגציות על שדות שונים שיש בהודעת הלוג. למעשה, אפשר להגיד שמדובר פה בשילוב של לא מעט תחומים, מה שהופך באמת את נושא הלוגים לנושא “חם” יחסית.
כאשר מסתכלים על עולם הפתרונות לבעייה הזאת, אחד המוצרים העיקריים המתחרים בקטגוריה הזאת הוא ה- “ELK stack”. כאשר, בפועל מדובר בשילוב של שלושה מוצרים: Elasticsearch, Logstash ו-Kibana.

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

דיזיין כללי

הפיתרון שלנו יורכב ממס’ חלקים, כפי שאפשר לראות בשרטוט הכללי הבא:

image_thumb1_thumb

תחילה, יש לנו את האפליקציות השונות שלנו שיכתבו לוגים לתוך תשתית ה- logging שנפרוס. אנחנו נרצה לעשות איזושהי סטנדרטיזציה של צורת השליחה, ולכן בדיזיין שלנו נעשה את זה באמצעות סרביס שאחראי  על כתיבת ההודעות בפורמט המתאים ל- RabbitMQ שנתקין. RabbitMQ הוא message broker, שישמש אותנו להעברת ההודעות בין ה- service שאנחנו מחצינים כלפי האפליקציות לבין הרכיב שבסוף אחראי לקרוא משם את ההודעות, לעשות עליהם עיבוד כלשהו (במקרה שלנו זה יהיה עיבוד די בסיסי) ולהכניס אותם למעשה ל-Elasticsearchשבמקרה הזה ישמש בתור בסיס הנתונים שלנו לאכסון ההודעות.
בסוף, יש לנו את משתמש הקצה, הבנאדם המסכן שצריך לדבג משהו תוך הסתמכות על הלוגים. הוא יעבוד מול ממשק גרפי, שנקרא Kibana, שמאפשר יצירת dashboards שמתשאלים מידע שנמצא ב- Elasticsearch ועבודה מולם.

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

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

RabbitMQ

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

RabbitMQ הוא כאמור message broker שמאזן מאד טוב בין ביצועים גבוהים לבין נוחות.
Message broker הוא רכיב שהתפקיד שלו זה להעביר הודעות מהצד השולח (Producer) לצד המקבל (Consumer). מכאן, אפשר להבין שהישות הבסיסית שעליה אנחנו מדברים כשאנחנו עובדים עם RabbitMQ היא “הודעה”.
הודעה  יכולה להיות למעשה כל רצף בינארי שאנחנו רוצים. עם כי, בפועל די נפוץ להיצמד לפורמטים טקסטואליים, כמו JSON או XML.

כאשר אפליקציה רוצה לקבל הודעה, היא למעשה פותחת connection מול RabbitMQ, באמצעות ה- connection הזה היא פותחת channel (שעליו אפשר לחשוב כמו “ערוץ שיחה” לוגי) ומאזינה בפועל ל- Queue. זה יכול להיות תור שנוצר דינמית (עוד רגע נדבר מול מה הוא נוצר), או תור שהיה קיים כבר קודם והצטברו בו הודעות.
כאשר אפליקציה רוצה לשלוח הודעה, היא יכולה לשלוח אותו לתור ספיציפי (שעליו אפליקציה אחרת אמורה להאזין), אבל זה לא מקובל.  בד”כ, כאשר אפליקציה תשלח הודעה היא תשלח אותה ל- Exchange.

ה- Exchange הוא למעשה “גשר” בין  מי שרוצה לשלוח הודעה למי שרוצה לקבל הודעה והוא זה שמאפשר לנו למעשה לפצל הודעה שנשלחת פעם אחת כך שהיא תגיע למס’ אפליקציות, או לאפשר לאפליקציה להירשם רק להודעות מסוג מסויים שמעניין אותה וכו’.
למשל, בדוגמא שלנו אנחנו נשלח הודעות log. כאשר את ההודעות log לא נשלח ישירות ל- queue שעליו מאזין logstash, הרכיב שבפועל יטפל בהודעות שלנו. את ההודעות נשלח ל- Exchange שייקרא, למשל LogMessages. אנחנו יכולים להגדיר אפס או יותר תורים שהם binded ל-exchange הזה. כלומר, לשכפל את ההודעה לאפס או יותר תורים. במקרה שלנו, למשל, נגדיר תור שיהיה binded ל- exchange הזה שאליו יגיעו ההודעות ומשם יימשכו לעיבוד ע”י logstash. אם מחר נחליט שאנחנו רוצים שעוד אפליקציה תקבל את ההודעות הללו (למשל, שהם ייכנסו ל- Splunk) – לא נצטרך לכתוב את ההודעה פעמיים. רק נגדיר עוד queue שיהיה גם הוא binded ל- exchange הזה ויקבל למעשה שכפול של כל ההודעות, באופן בלתי תלוי בתור של logstash, ויאפשר גם לאפליקציה האחרת להאזין ולקבל הודעות.

המנגנון של ה- Exchnage אפילו יותר מתוחכם מזה. עד עכשיו, מה שהזכרנו זה רק היכולת “לשכפל” הודעות למס’ תורים, כלומר exchange מסוג Fanout. אנחנו יכולים גם להגדיר exchange שמפצל הודעות לתורים לפי topic, כלומר לפי routing key.
ה- routing key הוא string אופציונאלי שמוצמד להודעה, ומורכב למעשה מאוסף של פרמטרים מופרדים בנקודות (הפרמטרים הם מה שנגדיר שיהיו). למשל, routing key של אפליקציה יכול להיראות משהו בסגנון הזה: MyAppName.ModuleName.Critical או MyAppName.OtherModuleName.Verbose . כאשר, במקרה הזה כשתור הוא binded ל-exchange, הוא יכול לבחור להיות binded רק לסוג מסויים של הודעות. למשל, ל MyAppName.*.Verbose [כל הודעות ה- verbose של MyAppName, בלי תלות בשם הרכיב באפליקציה] או MyAppName.# [כל ההודעות מ- MyAppName] או *.*.Critical [כל ההודעות הקריטיות] וכו’.

אחרי שהבנו מה נותן לנו RabbitMQ, בואו נתקין אותו. כדי להתקין את RabbitMQ, צריך קודם כל להתקין את Erlang, השפה וה- framework שעל בסיסם מפותח RabbitMQ. ניתן להוריד אותם מהאתר הרשמי, כאשר יש installer-ים עבור windows. משתמשי לינוקס יכולים להתקין באמצעות ה- package manager. לאחר שמורידים ומתקינים את Erlang, יש להוריד ולהתקין גם את RabbitMQ. ההתקנה פשוטה והיא למעשה כמה לחיצות על Next.

אחת הסיבות שאני אוהב את RabbitMQ היא ממשק הניהול הנוח שלו. כדי להפעיל את ממשק הניהול הזה, צריך למעשה להפעיל את הפלאגין שכולל אותו. כדי לעשות זאת, נפתח את RabbitMQ Command Prompt (יופיע לנו ב- Start Menu לאחר ההתקנה) ונריץ את הפקודה:

rabbitmq-plugins enable rabbitmq_management

לאחר ההרצה, ממשק הניהול יהיה זמין בפורט 15672 (עד כדי לאפשר ב- firewall קודם לכן). כלומר, ניתן מהמכונה עצמה לגלוש ל http://localhost:15672 ולהיכנס לממשק ניהול עם היוזר והסיסמא guest / guest. עם זאת, לא ניתן יהיה להתחבר עם השם משתמש והסיסמא האלה מרחוק.
כדי לאפשר התחברות מרחוק, ניצור יוזר אדמיניסטרטיבי על גבי המכונה, דרך אותו ה- command prompt שפתחנו קודם, באמצעות הפקודות הבאות:

rabbitmqctl add_user test test

rabbitmqctl set_user_tags test administrator

rabbitmqctl set-permissions -p / test ".*" ".*" ".*"

לאחר הקשת הפקודות האלה, נוכל להתחבר מרחוק (למשל ע”י גלישה במקרה שלי ל http://192.168.1.104:15672/) עם היוזר test והסיסמא test. כמובן, שבסביבת production נרצה לדאוג שב- firewall לא נאםשר גישה בפורטים של RabbitMQ משרתים שאינם שרתי האפליקציה שלנו.

עכשיו, כל מה שנשאר לנו לעשות מבחינת קונפיגורציית RabbitMQ, זה להגדיר את ה- Exchange שלנו ואז להגדיר את התור. נתחבר לממשק הניהול, ונלך לטאב Exchanges:

image_thumb111

נמלא את הפרטים בחלק של ה- Add a new exchange בתחתית העמוד,  בהתאם לתמונה המצורפת:

image_thumb3

לאחר שנלחץ על Add exchange, נראה אותו ברשימה. לאחר מכן, נעבור לטאב Queues בראש העמוד ונייצר Queue חדש:

image8_thumb

ונלחץ על Add queue כדי להוסיף.  בשלב הזה, יש לנו ביד תור ו- Exchange – אבל אין קשר בינהם. נרצה להוסיף binding כזה שיעביר את כל ההודעות שמגיעות ל- exchange שנקרא LogExchange שיצרנו קודם לתור שיצרנו עכשיו.
מכיוון שהגדרנו את ה- Exchange כ- topic, אנחנו יכולים להעביר pattern מסויים שנקבל הודעות שה- routing key שלהם מתאים ל- pattern הזה. בתור התחלה לא נעשה את זה, ונרשם לקבל את כל ההודעות.

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

image13_thumb

לאחר שמילאנו את הפרטים, נלחץ על Bind.ונראה שזה התווסף לרשימת ה- Bindings שאנחנו רואים (הרשימה ריקה בצילומסך שלמעלה, טרם הוספת ה- binding). למעשה, מה שהגדרנו פה זה שכל הודעה שמגיעה ל- Exchange שנקרא LogExchange תגיע לתור הזה ותמתין שמישהו ייקח אותה. מי זה המישהו הזה? במקרה שלנו, מדובר ברכיב בשם Logstash שבהמשך נגיע ללהתקין ולקנפג אותו.

Elasticsearch

בסוף התהליך, אנחנו רוצים לשמור את המידע של הלוגים ב-DB מסוג כלשהו. הדרישה העיקרית שלנו בעבודה עם לוגים היא יכולת חיפוש טקסטואלי, כלומר full text search. אנחנו רוצים לחפש על ההודעה, או על שדות ספיציפיים בצורה חופשית. למשל, לחפש את כל ההודעות שמופיעה בהם NullReferenceException או לחפש הודעות שמופיעה בהם שם משתמש, או שם של קובץ קוד שלנו – בקיצור, חיפוש טקסטואלי רחב. עם זאת, כמובן שחיפוש טקסטואלי לא מספיק לנו. אנחנו רוצים יכולת לעשות אגרגציות  מסוגים שונים על מידע (כדי לקבל למשל סטטיסטיקות על רכיבים בעייתיים). ושאילתות גם יותר כלליות. את כל אלה מספק לנו Elasticsearch.

אפשר לחשוב על Elasticsearchכעל Document DB (בדומה, למשל, למונגו) שההתמקדות שלו היא חיפוש טקסטואלי. הוא מבוסס על מנוע חיפוש טקסטואלי בשם Lucene (שעליו מבוסס גם מתחרה עיקרי שלו, גם הוא אופן סורסי, Solr) ומציע למעשה ממשק שמאפשר לשמור documents ואז לבצע עליו חיפושים מסוגים שונים. הוא מותאם ל- scale-out, ויש לו גם יכולת בדומה לרוב המנועים המבוזרים לרפליקציה פנימית שנותנת מענה במקרה של נפילת node אחד או יותר (בהתאם לקונפיגורציה).

מונחים בסיסיים ב- Elasticsearch

כאשר אנחנו מתקינים Elasticsearch, אנחנו למעשה נתקין node בודד. בסופו של דבר, node זה שרת שמריץ service של Elasticsearch שיודע לעשות עבודה, וגם לדבר עם nodes אחרים שפועלים במקביל אליו בשרתים אחרים. כל ה- nodes מאוגדים ביחד תחת ישות לוגית שהיא ה- cluster.
ברגע נתון, יש node שהוא ה- master – הוא זה שאחראי על הניהול של ה- cluster. אם ה- master נופל, אז נבחר עבורו מחליף ע”י ה- nodes האחרים (מבין שרתים שמוגדר להם שיכולים לשמש כ- master).

מרבית ה- nodes ב- cluster של Elasticsearch משמשים כ- data nodes. כלומר, הם מחזיקים אצלם חלק מהמידע שמאוכסן ב- cluster, יודעים לקבל מידע חדש, ולשרת שליפות שונות על מידע קיים.

מה זה המידע שהם מחזיקים? אז בסופו של דבר, היחידה הבסיסית ביותר היא document. מדובר למעשה באובייקט JSON, שמכיל שדות שונים וערכים שונים (בכל סכימה שהיא, לא נדרש שום סוג של הצהרה על הסכימה מראש). כברירת מחדל, כל השדות מתאנדקסים וניתן לחפש על כולם. הזיהוי של ה- data types מתבצע גם הוא לפי הערך הראשון שהוכנס (אבל יש API שלם שמאפשר שליטה על כל אחת ואחת מההגדרות האלה).

כל document נמצא למעשה כחלק מ- “index”. ב- cluster יכולים להיות אינדקסים רבים, והם נותנים חלוקה בין documents שהיא גם לוגית וגם פיזית. כלומר, אפשר להפריד אינדקסים למטרת הפרדה לוגית (אני מפריד את המערכת logging שלי מהמערכת של החיפוש על תוכן האתר) וגם למטרת הפרדה פיזית של מידע (יש לי לוגים של שנה אחורה, כאשר אני רוצה שהם יתאכסנו בקבצים שונים, אולי תהיה להם מדיניות שכפול שונה, אולי אני רוצה אפשרות למחוק את חלקם – ואז אני משיג באמצעות ההפרדה הפיזית יותר שליטה).

אינדקס יחיד יכול להכיל כמות נתונים גדולה, שפוטנציאלית יותר גדולה גם מכמה שנכנס ל- node בודד. בנוסף, Elasticsearch רוצה לתת מענה ל- scaling (רוצה לשפר ביצועים או להגדיל נפח? תדחוף עוד כמה שרתים ל- cluster) ולשרידות (מה קורה אם node נופל, או סתם יורד לשדרוג). את זה משיגים באמצעות שני מונחים חשובים: Shards ו- Replica. כל אינדקס מחולק פנימית למס’ shard-ים, שיכולים לשבת על nodes שונים. כלומר, שחלק מהמידע של האינדקס נמצא ב- node א’, חלק ב- node ב’ וכו’. ואז בשליפות העבודה מתחלקת על יותר שחקנים, מה שמאפשר ביצועי שליפות טובים יותר ביחס לאם זה היה נופל על כתפיו (ובעיקר, על ה- spindles) של שרת בודד.
כמובן, שאנחנו לא רוצים שיהיה העתק בודד לכל Shard – כי אז זה אומר שברגע שנפל node שמכיל את ה- shard הזה, השלמות של האינדקס כולו נפגעת. ולכן, יש לנו את מונח ה- Replica: כמה עותקים של אותו Shard נשמרים ב- cluster.

הן החיפוש, והן הניהול של cluster מבוסס Elasticsearch מתבצעים באמצעות REST API שנכיר אותו באופן בסיסי בהמשך. בכל אופן, בתהליך הזה אנחנו לא נידרש כמעט לנגיעה ב-API בהקשרי חיפוש, כי בהמשך “נלביש” רכיב (Kibana) שיספק לנו את ממשק המשתמש ואת כל יכולות החיפוש.

עכשיו, אחרי שאנחנו מבינים באופן בסיסי מה זה Elasticsearch, ניגש ללהתקין אותו. לא צריך להיות מומחה Elasticsearchכדי להתקין סביבה שכוללת מס’ nodes שונים. אני אראה כיצד עושים את זה, ונעשה את ההתקנה במקביל – גם על מכונת Windows Server 2016 וגם על מכונת Ubuntu 16.04.1.

התקנה על Windows

לפני שנוכל להתקין Elasticsearch, נצטרך להתקין JRE (סביבת הריצה של Java) מהלינק הזה (שימו לב להוריד את גרסת ה- 64 ביט). אחרי שהתקנו, נוריד את ה- ZIP של Elasticsearchמעמוד ההורדה (אני מתייחס לגרסא 5.0).  נחלץ מה- ZIP שהורדנו את התיקייה, ונשים אותה במקום כלשהו. בדוגמאות אני אניח שיש לנו עכשיו תיקייה C:\elasticsearch-5.0.0. דבר ראשון, נרצה להתקין את ה- service של Elasticsearchעל המכונה שלנו (כדי שההפעלה לא תהיה תלוייה בהרצת batch על ידנו…).

לטובת זאת, נצטרך לערוך תחילה את הקובץ C:\elasticsearch-5.0.0\config\jvm.options ולהוסיף אליו את השורה הבאה: –Xss1m, או שנקבל בעת ההתקנה את השגיאה “thread stack size not set”. הנה צילומסך של הקובץ לאחר הוספת השורה:

 

image_thumb10

לטובת התקנת הסרביס נריץ את הפקודות הבאות מ- cmd שמורץ כ- administrator:

cd c:\elasticsearch-5.0.0\bin

elasticsearch-service.bat install

כדי שנוכל להפעיל את ה- service, נצטרך גם להגדיר environment variable בשם JAVA_HOME. נעשה את זה ע”י הרצת הפקודה הבאה מ- PowerShell שרץ כ- administrator:

[Environment]::SetEnvironmentVariable("JAVA_HOME", "C:\Program Files\Java\jre1.8.0_111", "Machine")

לפני שנפעיל את ה- service שהותקן, נרצה לעשות עוד כמה שינויי קונפיגורציה קטנים בקובץ C:\elasticsearch-5.0.0\config\elasticsearch.yml. חלק מהשינויים האלה הם למעשה לשנות שורות שמסומנות בהערה, ולטובת הנוחות אני פשוט כותב את כל השינויים כבלוק אחד (ולא ב- section-ים שהם מופיעים בקובץ למטרת סדר):

cluster.name: logs-cluster

node.name: node-1

node.master: true

node.data: true

network.host: 0.0.0.0

http.cors.allow-origin: "*"

http.cors.enabled: true

נשמור את השינויים הללו בקובץ הקונפיגורציה, ולאחר מכן נעשה start ל- service שנקרא Elasticsearch5.0.0. כדי לראות שזה עובד, ננסה לגלוש למכונה שלנו בפורט 9300 (עדיף מבחוץ עם IP חיצוני של המכונה, ולא עם 127.0.0.1 כדי לאמת שאתם מצליחים לתקשר משרתים אחרים וה- firewall לא עומד באמצע – כי נזדקק לזה בהמשך). ככה למשל זה נראה אצלי:

image_thumb12

מזל טוב. יש לנו Elasticsearchמותקן.

ניהול Cluster של Elasticsearch

הממשק ש- Elasticsearchחושף כלפי חוץ הוא REST API. אנחנו יכולים לעבוד מולו, עם כלים כמו Postman ולתשאל את ה- REST API, אבל זה קצת פחות כיף.

גם אם עובדים עם cluster קטן של שני nodes בלבד, עדיין נעדיף ממשק נוח.  בד”כ, הממשק שבו אני משתמש הוא ElasticHQ.  ההורדה וההתקנה קלות מאד, והשימוש אינטואיטיבי ביותר. עם זאת, במדריך הזה הראיתי עד עכשיו איך מתקינים את Elasticsearchבגרסא 5.0, הגרסא העדכנית ביותר שיצאה. גרסא זו כללה כמה שינויי API, שגרמו לכך ש- ElasticHQ (וכלי ניהול אחרים) צריכים לעשות התאמות על מנת לעבוד. נכון לזמן שבו אני כותב את הפוסט הזה, ההתאמות הללו עוד לא התבצעו,  עם כי יש להניח (מניסיון העבר) שיתבצעו בקרוב.

התקנה על Linux

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

אז דבר ראשון, צריך להתקין java באמצעות הרצת הפקודה הבאה:

sudo apt-get install default-jre

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

sudo dpkg -i elasticsearch-5.0.0.deb

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

sudo systemctl enable elasticsearch.service

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

sudo sysctl -w vm.max_map_count=262144

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

כדי לערוך אותה, נכניס את הפקודה הבאה:

sudo nano /usr/share/elasticsearch/config/elasticsearch.yml

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

cluster.name: logs-cluster

node.name: node-2

node.master: true

network.host: 0.0.0.0

http.port: 9200

discovery.zen.ping.unicast.hosts: ["192.168.1.105"]

 

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

  • cluster.name: אנחנו רוצים להגדיר את אותו שם cluster, כי אנחנו רוצים למעשה לצרף את ה- node החדש ל-cluster עם ה-node שהקמנו קודם על שרת windows
  • node.name: זה השם שניתן ל- node החדש, פשוט נמשיך עם מוסכמת השמות שהתחלנו קודם.
  • node.master: פה אנחנו מגדירים האם ה- node יכול לשמש בתור master. זה לא אומר שהוא יהיה ה- maser בפועל, אלא זה אומר שהוא משתתף בתהליך הבחירה (election) במקרה של כשל של ה- master הקיים, ויכול לקבל את התפקיד על עצמו
  • network.host: אנחנו אומרים להאזין במקרה הזה על כל האינטרפייסים בפורט שמופיע ב- http.port. כמובן שהיינו יכולים להגדיר IP ספיציפי
  • discovery.zen.ping.unicast.hosts: פה אנחנו יכולים להעביר לו שמות או IP-ים של node-ים אחרים, ופה שמתי לו את ה- IP של שרת ה- windows שהתקנו עליו Elasticsearchקודם לכן. ל- ElasticSearch יש מנגנון discovery מתוחכם. כדי לוודא שהקונפיגורציה במדריך הזה עובדת בדיוק כפי שהיא אנחנו מציינים פה ספיציפית את ה- IP של השרת השני, אבל כמובן שבסביבת production שבה יש הרבה שרתים, לא צריך לציין את כולם (ובכלל, יש מנגנון discovery מבוסס unicast-ים וכו’).

זהו, סיימנו את שלב הקונפיגורציה. עכשיו מה שנשאר לעשות זה רק להפעיל את ה- service:

sudo systemctl start elasticsearch

ונראה את הסטאטוס שלו באמצעות הפקודה:

sudo systemctl start elasticsearch

ואת הסטאטוס נוכל לראות באמצעות הפקודה הבאה:

image_thumb16

אם נגלוש עכשיו לכתובת של השרת Elasticsearchשהתקנו בפורט 9200 נצפה לקבל את הפלט הבא:

{

  "name" : "node-2",

  "cluster_name" : "logs-cluster",

  "cluster_uuid" : "g2In6mvAT3OXFmqTOemWPw",

  "version" : {

    "number" : "5.0.0",

    "build_hash" : "253032b",

    "build_date" : "2016-10-26T04:37:51.531Z",

    "build_snapshot" : false,

    "lucene_version" : "6.2.0"

  },

  "tagline" : "You Know, for Search"

}

בדיקת סטאטוס של cluster

יופי. התקנו שני node-ים של Elasticsearch. עכשיו אנחנו רוצים לראות שבאמת הכל עובד תקין, הם מדברים אחד עם השני, רואים שהם באותו ה- cluster וכו’, ועל הדרך נראה איך ניתן לעבוד עם ה- REST API של ElasticSearch.

כל מי שעובד או עבד בעבר עם REST API מכל סוג שהוא צריך להכיר את Postman.  הוא זמין גם בתור פלאגין לכרום, וגם (ולטעמי הרבה יותר נוח) בתור אפליקציה חלונאית. אחרי שנתקין ונריץ את Postman נרצה לפנות ל-API של ElasticSearch בבקשת GET אל /cluster/Health (המיקום של האנדרסקור קצת השתבש פה, תסתכלו בצילום המסך):

image_thumb17

סימנתי בצהוב את הכתובת שאליה צריך לפנות ואת שני השדות הכי מעניינים: ה- status שאמור להיות green (אחרי שהוספתם והרצתם שני nodes לפי הגדרות הברירת מחדל) והשדה של number_of_nodes שאמור להראות 2 (כי הוספנו node אחד על windows ואחד על לינוקס).

אם אנחנו רוצים לראות קצת יותר פירוט על ה- nodes השונים, אנחנו יכולים לשלוח בקשת GET ל- _nodes ולראות פירוט רב יותר:

image_thumb5

Elasticsearchas a Service

אם אתם לא רוצים להתקין בעצמכם Elasticsearchעל מכונות on-prem, אתם יכולים להשתמש באחד מבין השירותים השונים שמציעים Elasticsearchas a service ב- cloud. למעשה, החברה שמאחורי Elasticsearchמציעה בעצמה את שירות Elastic Cloud שמציע בדיוק את זה מעל התשתית של אמזון. יש גם עוד לא מעט מתחרים אחרים, חיפוש של “Hosted Elasticsearch” בגוגל מניב לא מעט תוצאות של שירותים שנותניםי בדיוק את זה.

Logstash

אז אמרנו כבר שאנחנו רוצים לאכסן מידע על לוגים, ושהשכבה שתשמש אותנו בתור database בסיפור הזה זה Elasticsearch– כדי שנוכל להנות מיתרונות החיפוש הטקסטואלי.
בתכלס, מה שנרצה לעשות זה לייצג כל הודעת לוג בתור document, בפורמט json, כאשר הפורמט של ה- json הזה יכלול מספר שדות metadata קבועים, ועוד מספר כלשהו של שדות שרלוונטיים לאפליקציה שכותבת את הודעות הלוג (למשל, אפליקציה שמעבדת קבצים תוכל לכתוב את נתיב הקובץ שההודעת לוג נוגעת אליו בתור שדה, אפליקציה שמשרתת משתמשים תוכל לשים את שם המשתמש בתור שדה וכו’).

בשרטוט שהוצג בתחילת הפוסט אחנו רואים שה-front מול האפליקציות הוא למעשה איזשהו web service כלשהו (שבתכלס, לא באמת משנה האם הוא WCF, ASP.NET WebAPI, NodeJS או כל דבר אחר) שמקבל את הודעות הלוג וכותב אותם ל- RabbitMQ.
שאלה לגיטימית וטובה היא למה לא לכתוב את ההודעות הללו ישירות ל- Elasticsearch? מסיבה פשוטה – אנחנו רוצים לאפשר רמה נוספת של גמישות.  למשל, מה נעשה אם מחר נרצה לשלב אפליקצייה שיודעת לכתוב לוגים רק ל- event log של windows? או אפליקציה ששולחת syslog-ים לכתובת מסויימת וזה כל הלוגים שהיא יודעת להוציא? או אולי יש לנו לוג טקסטואלי של שרת apache שנרצה להזרים גם לתשתית הלוגים שלנו?

המשותף לכל הדברים האלה, הוא שאנחנו נצטרך משהו שיידע לקחת אותם מאיזשהו input, לעשות עליהם איזשהו משחק של המרה כדי לנרמל אותם לפורמט שבסוף אנחנו רוצים לשמור ל- Elasticsearchואז יידע להכניס אותם ל- Elasticsearchעצמו.
על הדרך, אם הדבר הזה מטפל בלוג הלוגים שלנו, אז גם נרצה שהוא יידע אולי לא רק לשלוח ל- Elasticsearch. למשל, אולי נרצה לכתוב חלק מהלוגים האלה גם לאיזשהו קובץ טקסטואלי? לפלטר את הלוגים ועבור חלקם גם להריץ איזשהו סקריפט? אולי חלקם מייצגים באגים שאנחנו רוצים לפתוח ישר עבורם task ב- JIRA?

כלומר, אנחנו מבינים שה- front הזה של הסרביס שמאפשר לכתוב הודעות ב- push לפלטפורמת ה- logging שלנו הוא רק ממשק אחד להכנסת מידע, מתוך רבים פוטנציאליים אחרים. גם “לאכסן ב- Elasticsearch” היא פעולה אחת שאנחנו רוצים לעשות עם המידע – אבל לא בהכרח היחידה.

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

Logstash הוא כלי חינמי ואופן-סורסי שאפשר להריץ גם על windows וגם על linux. הוא מאפשר לנו לתת קונפיגורציה שמכילה למעשה את החלקים הבאים:

  • input: מאיפה אנחנו מקבלים מידע – זה יכול להיות קובץ, RabbitMQ, או הרבה דברים אחרים
  • output: לאן אנחנו כותבים את המידע – למשל, במקרה שלנו, ל- Elasticsearch
  • filter / data manipulation: אנחנו יכולים לקבוע חוקים שמפלטרים את המידע לפי פרמטרים שונים, ועושים איתו מניפולציות כאלה ואחרות (הוספת שדה, מחיקת שדה, שינויי פורמט) ולמעשה עושים איזשהו branching של הלוגיקה שאנחנו מפעילים עליו.

logstash תומך בפלאגינים, כך שבנוסף לפיצ’רים המובנים שהוא מגיע איתם יש לא מעט פלאגינים שמתוחזקים ע”י הקהילה שמאפשרים מקורות input ו- output נוספים – ודברים שונים שאפשר לעשות עם המידע.

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

התקנת וקינפוג Logstash

את ההתקנה הפעם נעשה על שרת ה- Windows שלנו. תחילה נוריד את ה- ZIP, ונפתח אותו לתוך c:\logstash-5.0.1.

נייצר קובץ c:\logstash-5.0.1\config\logs-pipeline.conf. נדביק בו את התוכן הבא:

input {

    rabbitmq {

        host => "127.0.0.1" 

        subscription_retry_interval_seconds => 90

        queue => "LogstashQueue"

        threads => 2

        passive => true

        codec => "json"

    }

}

filter {

    date {

        locale => "en"

        match => ["EventDateTime", "YYYY-MM-dd-HH:mm:ss.SSS"]

        target => "@timestamp"

        add_field => { "debug" => "timestampMatched"}

   }

}

output {

  elasticsearch{

        hosts => ["127.0.0.1"]

    }

}

נשים לב שיש לנו בקונפיגורציה 3 חלקים:

  • input: אנחנו מגדירים מקור מידע יחיד, שהוא ה- RabbitMQ. אנחנו מגדירים ל- Logstash להפעיל שני consumer threads על תור בשם LogstashQueue שהצהרנו עליו כבר (זאת המשמעות של passive, כלומר התור כבר קיים ואנחנו דאגנו לזה מראש).
  • filter: בהודעת ה- JSON שנוציא מהסרביס שלנו, נרצה שיהיה שדה שמתאר את הזמן שבו קרה האירוע שעליו היה הלוג. בדיפולט, Logstash מתייחס לשדה @timestamp בתור שדה הזמן, ויש פורמטים מסויימים של תאריך ושעה שהוא מצפה לקבל. נניח, לטובת ההדגמה, שאנחנו רוצים שהוא יוציא את התאריך והשעה שלפיהם הוא שומר את הלוגים בשדה אחר (הלוגים נשמרים כברירת מחדל באינדקסים מופרדים לפי ימים ב- Elasticsearch, וכשנסתכל בהמשך על איך מתבצעים החיפושים וכו’, אז הזמן הוא מרכיב די משמעותי בפילטור) . נניח ששדה הזמן מופיע בהודעה (שהיא ב-JSON) בשם EventDateTime. אנחנו רוצים לשנות לו את השם ל- @timestamp וכמובן להתאים אותו לפי הפורמט שבו אנחנו כותבים. כל זה אנחנו מבצעים פה בחלק של ה- filter.
  • output: אנחנו אומרים לו לכתוב ל – Elasticsearch שרץ על אותו השרת.

כדי להריץ את logstash ולראות שהקונפיגורציה שהכנסנו עובדת, נשתמש בפקודה הבאה שאותה נריץ ב- cmd:

c:\logstash-5.0.1\bin\logstash.bat -f c:\logstash-5.0.1\config\logs-pipeline.conf

הפלט שאנחנו אמורים לראות הוא משהו כזה:

image_thumb7

איך בודקים שזה עובד?

כדי לראות שהכל עובד, נשלח הודעת בדיקה ב- RabbitMQ, נראה שלא מופיעות שגיאות ב- logstash (אם יופיעו שגיאות נראה אותם ב- console שבו אנחנו מריצים את logstash) ואז נראה שבאמת נוצר לנו אינדקס ב- Elasticsearch ושיש בו document.

  1. נגלוש לממשק של ה- RabbitMQ .בהנחה שזה באותו השרת שהתקנו עליו קודם, הממשק יהיה זמין בכתובת http://127.0.0.1:15672
  2. נלך למעלה לטאב Queues
  3. ניכנס לתור LogstashQueue שיצרנו קודם
  4. אנחנו אמורים לראות את המסך הבא:

image_thumb18

כמו שאתם רואים, תחת ה- Consumers אנחנו רואים את שני ה- consumers שהעלה logstash.

קצת יותר למטה בעמוד, יש לנו חלק של Publish Message. נדביק בו את ההודעה הבאה:

{

    "msg": "Hello World!",

    "EventDateTime": "2016-01-01-13:23:00.999"

}

וכך זה אמור להראות:

image_thumb20

ולסיום – נלחץ על Publish message.

נסתכל שוב ב- console שבו הרצנו את Logstash ונראה שלא התווספו שורות שגיאה. אם אין שורות שגיאה – אנחנו בכיוון הנכון.

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

image_thumb19

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

הגדרת Logstash בתור Windows Service

אחרי שעשינו את הבדיקה דרך command line (וכמובן כל שינוי קונפיגורציה שנעשה נוכל להרים instance של logstash שעובד מול הקונפיגורציה החדשה ב- cmd ולראות שהכל עובד טוב, ואיזה שגיאות יש) – אנחנו כמובן לא נרצה להשאיר את העבודה בתצורה של cmd שחייב לרוץ כל הזמן, אלא נרצה לתת ל- service manager לנהל את הסיפור הזה. לצערנו, Logstash לא כולל באופן מובנה התקנה כ- Windows Service כמו Elasticsearch. לשמחתנו, יש כלי בשם NSSM (Non-Sucking Service Manager) שמאפשר לנו בקלות להפוך את Logstash (ולמעשה הרבה דברים אחרים שכתובים ב- java) ל- WIndows Services.

תחילה, נוריד את הגרסא האחרונה של NSSM ונפתח אותה ל c:\nssm (שתחתיו יהיו התיקיות win32 ו- win64 שב-ZIP).  נריץ את הפקודה הבאה: c:\nssm\win64\nssm.exe install ונמלא את הפרטים בחלון שייפתח כך:

image_thumb15

נלחץ על Install service, ניכנס ל- services.msc ונעשה Start ל-service החדש שיצרנו (שייקרא logstash בדוגמה הזאת).  כמובן, כדאי לעשות שוב את בדיקת ה- sanity שתוארה קודם כדי לראות שגם הסרביס עובד כמו שצריך.

 

סיכום ביניים של מה שיש לנו עד עכשיו

עד עכשיו מימשנו למעשה את ה- core של המערכת שלנו:

  • יש לנו שני שרתי Elasticsearch ב- cluster מוכנים ומזומנים לקבל מידע ולאנדקס אותו
  • יש לנו תור (RabbitMQ) שישמש אותנו כדי לקבל את המידע
  • יש לנו רכיב שיודע לעבד מידע מהתור הזה (לא רק) ולהכניס אותו ל-Elasticsearch שהרמנו

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

SQL Server למפתחים: Unique Index ו- IGNORE_DUP_KEY

אחד הפיצ’רים הבסיסיים לסכימה של מסדי נתונים, היא היכולת להגדיר unique constraint.
מאחורי הקלעים, Unique Constraints מתבססים על Unique Indexes שנוצרים כדי לשרת את ה- constraint, כלומר – כדי לאפשר ל- SQL Server לבדוק בזמן ביצוע פעולות הוספה ועדכון שה- constraint לא מופר.

את הפוסט הזה אני רוצה להקדיש לפיצ’ר שימושי שמפתחים צריכים גם כן להכיר – שימוש ב- unique index על טבלה לסינון כפילויות בזמן ההכנסה. צריך להבדיל גם בין שני תתי-תרחישים אפשריים: לפעמים, נכונות המידע דורשת מאיתנו שלא יהיו שורות כפולות. למשל, אם אנחנו מנהלים טבלה של אזרחי ישראל – לא יכולים להיות שני אזרחים עם אותו מספר ת”ז.

עם זאת, לפעמים נרצה לסנן שורות כפולות כדי להקל על השימוש במידע. למשל, אם יש לנו טבלת קישור שמקשרת בין אזרח בישראל לאבא שלו. ונניח שאנחנו מקבלים כל הזמן מידע על אזרחי ישראל ומי האבא שלהם, כאשר לעיתים המידע יחדש לנו (יספר לנו על קשר שלא ידענו עליו) ולעיתים לא (נקבל שוב מידע על קשר שאנחנו מכירים).
נניח גם (לטובת התרחיש הקצת מופרך) שעצם קבלת המידע לא מעניינת, כלומר אין לנו עניין לשמור תיעוד של עצם העובדה שקיבלנו קישור כזה (נניח כי הוא נשמר במקום אחר).
בתרחיש הזה – אין בעייה מבחינת נכונות המידע לשמור שורות כפולות. א’ הוא אבא של ב’ גם אם זה מופיע תיעוד לזה פעם אחת וגם אם מופיע לזה תיעוד מיליון פעמים. עם זאת, יש יתרונות להחזיק טבלה קטנה לעומת טבלה גדולה, ככה שיש לנו יתרון לשמור את הקישור הזה באופן יחיד.
כדי לעשות את זה אנחנו יכולים לנקוט באחת מהרבה הדרכים שקיימות כדי לשמור מידע סיכומי, או תצוגה סיכומית של המידע (כתבתי למשל על Indexed Views, שבהחלט אפשר לעשות בהם שימוש במקרה כזה).  אבל מה אם אנחנו מראש לא רוצים להחזיק את המידע ה-“מיותר” (כלומר, כל כפילות שהיא)?

לכאורה אנחנו נמצאים פה עם בעיית “UPSERT” מנוונת: אנחנו לא רוצים להכניס-או-לעדכן,  אנחנו רוצים להכניס-או-לוותר [אם יש כפילות].

לפני שאני אמשיך בהצגת Unique Indexes, אני רוצה להתייחס רגע לפיתרון הנאיבי של “נבדוק אם לא קיים ואז נכניס”. כלומר, נעשה תנאי של IF NOT EXISTS וע”ס הערכים שלו נפעל.
פיתרון הזה פחות טוב, ולא רק בגלל שהוא פחות אלגנטי ונוח. הוא גם לא מאפשר לנו לעבוד בצורה נוחה עם BULK INSERT-ים (כי אז אנחנו לא יכולים לשלב את הבדיקת IF NOT EXISTS), הוא מקשה עלינו מבחינת טרנזקציות שרצות במקביל (צריך לבחור isolation level כזה שיאפשר לנו לוודא שבין הבדיקה של ה- IF NOT EXISTS להכנסה לא הוכנס הערך מטרנזקציה אחרת) – ובנוסף, הוא גם לא חוסך לנו את האינדקס (כי אם יש לנו הרבה שורות בטבלה, הבדיקת IF NOT EXISTS עצמה תהיה יקרה). ואם כבר אינדקס, אז עדיף שזה יהיה תוך שימוש בפיצ’ר שמובנה במנגנון ומאפשר לנו להנות מיתרונות נוספים : –)

Unique Indexes והתנהגות במקרה של כפילויות

בגדול, unique index, זה אינדקס רגיל שמסומן ל- SQL Server שכל קומבינציה של ה- key columns שלו (לשם פשטות, אני אקרא לזה “הערך” שלו – למרות שכמובן זה יכול להיות שילוב של ערכים) הוא יוניקי. הוא למעשה “הרוח החיה” מאחורי כל unique constraint (כי הוא זה שמאפשר גישה מהירה כדי לבדוק שה- constraint נשמר).
Unique Indexes יכולים לשמש אותנו לאכיפת constraint, ובנוסף הם מספקים מידע חשוב לטובת ייצור Execution Plan. כאשר מובטחת ייחודיות של ערכים מדובר למעשה באמירה חזקה מאד על המידע שמאפשרת לבצע פעולות מסויימות בצורה יעילה יותר מה שמאפשר לייצר execution plan טוב יותר.

כאשר אנחנו יוצרים unique index, ההתנהגות הדיפולטית במקרה שאנחנו מכניסים ערך שקיים כבר היא פשוטה: לזרוק שגיאה (וכמובן שהשורה לא מתווספת). אם השגיאה הזאת קרתה כחלק מטרנזקציה (למשל, כאשר אנחנו עושים רצף של פעולות בטרנזקציה אחת, או למשל עושים שימוש ב- Bulk Insert ומכניסים מס’ שורות בבת אחת) – יתבצע rollback של הטרנזקציה, כלומר המצב יחזור לקדמותו ואף אחת מהפעולות שהתבצעה כחלק מהטרנזקציה לא תקרה. ההיגיון מאחורי זה ברור – אם רצף הפעולות הזה הביא למצב שבו ניסינו להכניס שורה עם ערך כפול, למרות שהוא אמור להיות ייחודי, אז משהו לא נכון קרה.

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

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

 

יצירת Unique Index עם IGNORE_DUP_KEY

.איך עושים את זה? משתמשים ב- flag שנקרא IGNORE_DUP_KEY ומגדירים אותו ל- ON בעת יצירת ה- index.

CREATE UNIQUE NONCLUSTERED INDEX [IX_FatherSonInfo_Unique_SonID_FatherID] ON [dbo].[FatherSonInfo]

(

    [SonID] ASC,

    [FatherID] ASC

)WITH (IGNORE_DUP_KEY = ON)

מה אמרנו בפועל? ייצרנו אינדקס יוניקי חדש, ואמרנו שבמידה שמתבצעת פעולת הכנסה/עדכון לאינדקס הזה שתפגע בייחודיות – הוא ימנע את זה, מעצם הגדרתו כאינדקס יוניקי, אבל יעשה את זה עם אזהרה ולא עם שגיאה (לטובת הבהרה: כאשרמתבצעת פעולת הכנסה/עדכון על הטבלה אז מתבצעת פעולה על האינדקס – כי כל פעולה על הטבלה כרוכה בפעולה מתאימה על כל אינדקס שמוגדר בטבלה [למעט היוצא מין הכלל של Filtered Index, ועל זה בפוסט אחר]).

כמובן, שכאשר מריצים את ה- statement הזה חייבים שבטבלה לא יהיו כפילויות, אחרת נקבל שגיאה כזאת:

Msg 1505, Level 16, State 1, Line 5

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.FatherSonInfo' and the index name 'IX_FatherSonInfo_Unique_FatherID_SonID'. The duplicate key value is (1, 1).

The statement has been terminated.

——————

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

with cte as (

    SELECT    ID,

            DateCreated,

            FatherID,

            SonID,

            RN = ROW_NUMBER()OVER(PARTITION BY FatherID, SonID ORDER BY ID)

    FROM FatherSonInfo

)

 

DELETE FROM cte

WHERE RN > 1

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

——————

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

שימוש באינדקס שמוגדר עם IGNORE_DUP_KEY=ON

אז אחרי שיצרנו את האינדקס הזה, שכאמור הוא אינדקס יוניקי שמסומן ב IGNORE_DUP_KEY = ON, כך שההתנהגות במקרה של כפילויות היא אזהרה בלבד, בואו נראה באמת מה קורה.

נתבונן רגע בשאילתה הבאה:

 

INSERT INTO FatherSonInfo(DateCreated, FatherID, SonID)

VALUES

(GETDATE(), 1, 1),

(GETDATE(), 1,1),

(GETDATE(), 1,2)

אנחנו מכניסים פה באותו statement (שקל וחומר שזה אומר שזה באותה טרנזקציה) מס’ ערכים לטבלת FatherSonInfo – כאשר הראשון והשני כפולים.  התוצאה שנקבל היא ההודעה הבאה:

Duplicate key was ignored.

 

(2 row(s) affected)

כלומר, קיבלנו אזהרה – אבל השורות הוכנסו. בצורה הזאת, היינו יכולים להבטיח כבר בשלב ההכנסה שמצד אחד לא יהיו לנו שורות “מיותרות” בטבלה, מצד שני לא להעסיק את עצמנו עצמאית בשאילתה של IF NOT EXISTS ובכל זאת להבטיח ייחודיות של ערכים.

לעומת זאת, אם זה היה מתבצע מול טבלה שהאינדקס היוניקי מוגדר בה בתור IGNORE_DUP_KEY = OFF (או פשוט בלי אזכור של IGNORE_DUP_KEY, כי ה- default הוא OFF) אז היינו מקבלים שגיאה שנראית ככה:

Msg 2601, Level 14, State 1, Line 2

Cannot insert duplicate key row in object 'dbo.FatherSonInfo' with unique index 'IX_FatherSonInfo_Unique_FatherID_SonID'. The duplicate key value is (1, 1).

The statement has been terminated.

ולא הייתה מוכנסת אף שורה לטבלה.

אם הכפילות הייתה למשל בתאריך ובשעה, שהם לא חלק מה-key columns של ה- unique index שלנו – אז כמובן שבאף אחד מהמקרים זה לא היה מכשיל את הפעולה.

חשוב לציין גם שבעוד שהדוגמאות שהראיתי היו כשה- IGNORE_DUP_KEY מוגדר על nonclustered index, הוא יכול להיות מוגדר גם על unique clustered index באותה הצורה (וגם על ה- primary key).

מה עושים כאשר יש הרבה Key Columns?

אם יש לכם הרבה Key Columns שלפיהם אתם רוצים לעשות את היוניקיזציה – אתם עלולים להיתקל באחת משתי בעיות: (1) שמכיוון שיש הרבה עמודות, שלוקחות פוטנציאלית הרבה נפח, האינדקס יהיה גדול מבחינת הנפח שלו (וגם פעולות שונות עליו יהיו יותר איטיות, כי יערבו יותר IO), או ש- (2) תעברו את מגבלת 900 הבתים של ה- Key Column ואז בכלל לא תוכלו להגדיר את האינדקס כמו שאתם רוצים.

במקרה כזה, אפשר להוסיף עמודה, נקרא לה UniqueHash, שבה בזמן ההכנסה תחשבו MD5 hash (למשל) על ה- string שנוצר מ- concat של השדות שאתם רוצים שיהיו ה- key שלכם מופרדים באיזשהו seperator. למשל, נניח שהערכים שלי הם DataA, DataB, DataC,…DataZ אז נחשב hash על DataA~DataB~DataC ונשמור את ה- hash, כאשר את ה- unique index נגדיר על ה- hash בלבד.

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

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

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

 

השפעה של IGNORE_DUP_KEY על עבודה מהקוד

אני רוצה להתייחס לכמה אספקטים חשובים שנוגעים לעבודה מקוד מול טבלאות שיש עליהם unique index שמוגדר עליו IGNORE_DUP_KEY = ON.

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

ADO.NET “קלאסי”

אם אתם משתמשים באובייקטים הבסייים של ADO.NET לטובת עבודה, כלומר עובדים עם SqlCommand / SqlConnection ישירות, אז אם היה מוגדר unique index עם IGNORE_DUP_KEY = OFF (הדיפולט) והייתם מכניסים ערכים כפולים, היה עף SqlException.
אם אתם מכניסים ערכים כפולים לאינדקס שמוגדר עם IGNORE_DUP_KEY = ON, תוכלו להירשם ל-event של SqlConnection שנקרא InfoMessage כדי לקבל את כל הטקסט של ה- messages, ובכלל זה את המידע על ה- duplicate key ignored.
דרך יותר אלגנטית תהיה לשלוף את הערך של @@ROWCOUNT, כלומר מספר השורות שהושפעו ע”י השאילתות הכנסה שלכם. אם הוא קטן ממספר השורות שהכנסתם בטבלה שיש לה אינדקס שמוגדר עם IGNORE_DUP_KEY=ON, הגיוני שלשם “נעלמו” השורות החסרות.

BULK INSERT

אם אתם משתמשים ב- SqlBulkCopy לטובת ההכנסות לטבלה שמוגדרת עם IGNORE_DUP_KEY=ON – הכל יהיה שקוף לכם. פשוט תזרקו את המידע, וייכנס רק מה שצריך להיכנס.

Entity Framework

כאשר אתם מבצעים הכנסת שורות באמצעות Entity Framework(ועדכון, כמובן) אז כשאתם עושים SaveChanges, מאחורי הקלעים Entity Framework בודק שהפעולה של ההוספה/עדכון אכן התבצעה. אם היא לא, נזרק exception מ- SaveChanges ששמו DbUpdateException. כלומר, כשננסה להריץ את הקוד הבא:

using (var data = new check1Entities())

{

    data.FatherSonInfoes.Add(new FatherSonInfo() {FatherID = 1, SonID = 2, DateCreated = DateTime.Now});

    data.FatherSonInfoes.Add(new FatherSonInfo() { FatherID = 1, SonID = 2, DateCreated = DateTime.Now });

    data.SaveChanges();

}

נקבל:

image

ה- Inner Exception פה הוא מסוג OptimisticConcurrencyException.

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

חשוב לזכור שכשאנחנו עושים Save Changes, מאחורי הקלעים entity framework לוקח את רשימת השינויים שהוא מכיר (כברירת מחדל הוא עושה change tracking אוטומטי, אם מבטלים את זה [למשל כדי לשפר ביצועים של קריאה למתודה Add], חשוב לסמן את ה- Entity ב- state המתאים [Added/Modified/Deleted…]).
בהסתמך על רשימת השינויים, Entity Framework מבין איזה שאילתות צריך להריץ וגם באיזה סדר. למשל, אם יש לנו אובייקט A ש-“מכיל” הרבה אובייקטים מסוג B (כלומר, ל-B שמור המפתח של A) – אז קודם כל נדרשת הכנסה של A, קבלת ה-ID (המפתח) ואז ההכנסה של כל האובייקטים מסוג B ששוייכו ל-A, תוך שימוש במפתח כדי לעשות את הקישור ברמת ה-DB.

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

exec sp_executesql N'INSERT [dbo].[FatherSonInfo]([DateCreated], [FatherID], [SonID])

VALUES (@0, @1, @2)

SELECT [ID]

FROM [dbo].[FatherSonInfo]

WHERE @@ROWCOUNT > 0 AND [ID] = scope_identity()',N'@0 datetime2(7),@1 int,@2 int',@0='2016-10-26 21:37:55.0438727',@1=1,@2=2

 

נשים לב שהוא עושה INSERT, ואז שולף את ה-ID שהוקצה לישות (כי זה ה- key שמתמלא ע”י ה-DB, והוא רוצה למלא את ה- entity בצד של ה- client), אבל עושה את זה רק בתנאי שה- @@ROWCOUNT (כמות השורות שהושפעו) גדול מ-0.

המטרה של Entity Framework בבדיקות הללו ב-WHERE, היא ששיטת העבודה של Entity Framework עצמו מבוססת Optimistic Concurrecy (וכמובן, גם השאילתה יכולה לרוץ ב- Isolation Level מבוסס optimistic concurrency ברמת הדטאבייס). ולמעשה, באמצעות ה- WHERE הזה הוא בודק שההנחות שלו התקיימו בפועל. ששורה שהוא חשב שהתווספה אכן התווספה, או ששורה שהוא רוצה לעדכן באמת נראית טרם תחית העדכון כמו שהוא חושב שהיא נראית (כלומר, שהיא לא השתנתה במקביל “מתחת לרגליים”).

במקרה שיש כישלון, ולמעשה לא מתאמת שום דבר על ה-WHERE כמו במקרה שלנו, הוא מניח שהאופטימיות נכשלה – ושזה מעיד על בעייה. ואז נזרק DbUpdateException שעוטף InnerException מסוג OptimisticConcurrencyException.

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

אנחנו יכולים לכתוב קוד בסגנון הזה:

using (var data = new check1Entities())

{

    data.FatherSonInfoes.Add(new FatherSonInfo() { FatherID = 1, SonID = 2, DateCreated = DateTime.Now });

    data.FatherSonInfoes.Add(new FatherSonInfo() { FatherID = 1, SonID = 2, DateCreated = DateTime.Now });

    data.FatherSonInfoes.Add(new FatherSonInfo() { FatherID = 91, SonID = 8, DateCreated = DateTime.Now });

    try

    {

        data.SaveChanges();

    }

    catch (DbUpdateException ex)

    {

        var optimisticConcurrencyException = ex.InnerException as OptimisticConcurrencyException;

        if (optimisticConcurrencyException != null)

        {

            if (optimisticConcurrencyException.StateEntries.Any(k => k.State != EntityState.Added))

            {

                throw;

            }

        }

    }

}

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

אפשר לעקוף את זה בכל מיני דרכים, למשל להפריד לחלקים ולהכניס כל פעם entity בודד ולעשות SaveChanges. או לחלופין, במקרה של exception, לעשות לולאה של retries ולהוריד כל פעם את ה- entity הבעייתי. אבל, כאמור, כל אלה הם מעקפים – כי אין באמת פיתרון אלגנטי (לפחות לא כזה שאני מכיר).

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

מעבר לזה, ה- exception הזה הוא לא הבעייה היחידה שלנו. כאשר אנחנו מיישמים את הטריק הזה ברמת ה-DB, אנחנו צריכים להיות מודעים לו ברמת הקוד כאשר אנחנו עובדים עם Entity Framework, כי חלק מה-“קסמים” שהוא עושה עבורנו עלולים להיפגע. למשל, אם הישות שאנחנו מוסיפים לטבלה מהסוג הזה מכילה relationship מול טבלאות אחרות שבה לידי ביטוי בתור object graph, גם אם “נבלע” את הדילוג על השורה הספיציפית, נשאלת השאלה מה אנחנו עושים עם כל האובייקטים שהוא מפנה אליהם ולהיפך? במקרה כזה, לא ניתן יהיה לייצג את ה- object graph של ה- entities בתור קשרים בין שורות שונות בטבלאות שלנו – כי למשל אחת מהשורות לא הוכנסה.

למעשה, ברגע שאנחנו מגדירים IGNORE_DUP_KEY = ON, זה מכריח אותנו להתמודד עם מצב שבו אנחנו “מדלגים” על הכנסת שורה מסויימת. וזה אומר שאנחנו לא יכולים להשתמש בחלק מהפיצ’רים של Entity Framework אם entities שייכנסו לטבלה שבה קיים אינדקס כזה.

מה שמביא אותנו להמלצה שלי – אם אתם מיישמים את הטריק הזה עם Entity Framework, תדאגו להבדיל בקוד שלכם את ההכנסה לטבלה המדוברת ביחס לטבלאות אחרות.

עבור פריטים בודדים

אם אתם בד”כ מכניסים פריטים בודדים בכל פעם, אתם יכולים להפריד את זה ליחידת קוד (מתודה/מחלקה/whatever) שאחראית על ההוספה, בצורה שיהיה ברור למי שמשתמש בה שלא מדובר בהוספה סטנדרטית של Entity Framework, כי מכיוון שאנחנו מראש מודעים למצב שיש סיכוי שההכנסה שלנו לא תתבצע ואנחנו רוצים שזה יקרה, זה מגביל אותנו בפיצ’רים מסויימים (כמו relationship שמתמפה ל- object graph) שלא נרצה לעשות בהם שימוש.

במקרה כזה מה שנעשה זה נוודא שאת ההכנסה אנחנו עושים מ- instance נפרד של ה-context שלנו, ואז אנחנו יודעים שרק הפריט המסויים הזה כלול למעשה במשימה שמגולמת ב- SaveChanges, ולכן אם עף exception של OptimisticConcurrencyException, נוכל להתעלם ממנו בבטחה.

static void Main(string[] args)

{

    AddSingleItem(new FatherSonInfo() { FatherID = 1, SonID = 2, DateCreated = DateTime.Now });

    AddSingleItem(new FatherSonInfo() { FatherID = 1, SonID = 2, DateCreated = DateTime.Now });

    AddSingleItem(new FatherSonInfo() {FatherID = 91, SonID = 81, DateCreated = DateTime.Now});

}

 

static void AddSingleItem(FatherSonInfo item)

{

    using (var data = new check1Entities())

    {

        data.FatherSonInfoes.Add(item);

        try

        {

            data.SaveChanges();

        }

        catch (DbUpdateException ex) when (ex.InnerException is OptimisticConcurrencyException)

        {

            //ignore.

        }

    }

}

 

אם אנחנו מכניסים מספר פריטים

במקרה כזה, מומלץ שכבר נעשה שימוש בפיצ’ר ה- Bulk Insert ואז אפשר להשתמש ב- EntityFramework.BulkInsert לטובת ההכנסה, כמו בקוד הבא (אפשר לקרוא עוד על BULK INSERT פה):

using (var data = new check1Entities())

{

    List<FatherSonInfo> items = new List<FatherSonInfo>()

    {

        new FatherSonInfo() {FatherID = 1, SonID = 2, DateCreated = DateTime.Now},

        new FatherSonInfo() {FatherID = 1, SonID = 2, DateCreated = DateTime.Now},

        new FatherSonInfo() {FatherID = 91, SonID = 8, DateCreated = DateTime.Now}

    };

 

    data.BulkInsert(items);

}

 

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

 

בהצלחה!

SQL Server למפתחים: Transaction Isolation Level

הקדמה

SQL Server, כמו כל דטאבייס רלציוני טוב, עונה על סט של עקרונות שנקראים ACID: ר”ת של Atomicity, Consistency, Isolation, Durability שמטרתם להבטיח את שלמות הנתונים במסד הנתונים. ממש ממש בגדול – העקרונות הללו אומרים שכשאתם מכניסים מידע לדטאבייס הוא נשאר שם, כשאתם שולפים נתונים אתם מקבלים נתונים אמיתיים ונכונים [בלי להיכנס להגדרה של “אמיתיים ונכונים” עכשיו]. בפוסט הזה אני רוצה להתמקד רק באחת מהמילים הללו: Isolation. מדובר למעשה בהגדרה (לכאורה) פשוטה: ההבטחה שגם אם שתי טרנזקציות רצות במקביל, תוצאת הביצוע שלהן בפועל תהיה כאילו הן רצו אחת אחרי השנייה.
כמובן, שבפועל אנחנו כן נרצה שדברים יעבדו במקביל. ולכן, נרצה להגדיר באמת מה מובטח במקרה ששתי טרנזקציות רצות במקביל ומתעסקות מול אותם הנתונים, ומה לא מובטח.

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

למה אמור להיות אכפת לך מזה כמפתח?

נניח שאתה מפתח אפליקציה שהיא multi-threaded. די ברור לך למה אכפת לך איזה פעולה היא thread-safe ואיזה לא. מה אתה מבצע בצורה שמבטיחה thread safety (למשל עם Interlocked, או עם lock וכו’) ומה לא.
מאותה הסיבה צריך להיות אכפת לך מה ההתנהגות ברמת ה- DB. ההתנהגות במקרה של עבודה במקביל מול ה-DB מגדירה למעשה את התנהגות האפליקציה שלך.

כמה מונחים חשובים

לפני שנצלול לסוגי ה- Isolation Levels השונים, בואו נסביר רגע מס’ מושגים ובעיות שיעזרו לנו להבדיל בין סוגי ה- Isolation Levels שזמינים לנו.

  • העדכון האבוד: נניח שלמה יש 250 ש”ח. בנוסף, נניח שיש לנו טרנזקציה A וטרנזקציה B. ושתיהן מתעסקות עם החשבון של משה כאשר טרנזקציה A קוראת כמה כסף יש בחשבון של משה ומוסיפה לו 100 ש”ח, וטרזנקציה B קוראת כמה כסף יש בחשבון של משה ומוריד ממנו 50 ש”ח. כמה כסף יהיה למשה בסוף? ככל הנראה, היינו רוצה שיהיה לו 300 ש”ח. אבל, אם אנחנו מסתכלים על ריצה מקבילית, ללא שימוש באיזשהם כלים שדואגים ליצירת אפקט שמדמה סדר הגיוני של ההרצה – היינו יכולים לקבל גם 200 ש”ח וגם 350 ש”ח כתשובות אפשריות לאחר ריצת שתי הטרנזקציות, כי כל אחת מהן קראה איזשהו ערך ועדכנה.
    ברור לנו שהתמודדות עם מצב כזה היא הבסיס שלנו בדרישה של Isolation. אנחנו מוכנים לפעילות מקבילית, כל עוד התוצאה הסופית נכונה.
  • Dirty Reads: המונח הזה מדבר על קריאות של מידע ששונה ע”י טרנזקציה כלשהי שטרם הסתיימה. כלומר, אנחנו קוראים מידע שלא מובטח שאי פעם היה נכון. לא מדובר על מידע שאולי לא יהיה נכון עוד שנייה (ואז אנחנו יכולים להגיד שהוא נכון לרגע הקריאה), אלא מידע שייתכן שלעולם לא היה אמור להיקרא כי הוא שונה ע”י טרנזקציה שעדיין בתהליך ואז ייתכן שיש לה עוד שינויים שהיא רוצה לעשות על המידע, או שהיא סיימה לעשות את השינויים עם המידע שלנו, אבל תיכשל בהמשך הפעולה שלה, תעשה rollback ואז המידע שלנו מעולם לא היה נכון. האם אני רוצה לאפשר לקרוא מידע כזה?
  • Phantom Reads: דמיינו שיש לנו טרנזקציה שמריצה את אותה שליפה בהתחלה ובסוף, כאשר השליפות הן על טווח של שורות. למשל, אנחנו מביאים את כל העסקאות שבוצעו מישראל בתחילת הטרנזקציה, ואז בסוף הטרנזקציה שוב עושים את אותה השליפה. נניח גם שבמקביל רצה עוד טרנזקציה שהוסיפה עוד עסקה מישראל. כלומר, השורות שראינו בטרנזקציה A בתחילת הטרנזקציה ובסוף הטרנזקציה הן לא אותן שורות, כי התווספה שורה באמצע. האם מצב זה תקין מבחינתנו? אולי כן ואולי לא, ובכל אופן יש לזה השפעה על רמת הבידוד הנדרשת בין טרנזקציות.
  • Repetable Reads: בדומה ל- Phantom Reads, אבל עכשיו אנחנו לא מסתכלים על טווח שורות אלא על שורה ספיציפית. למשל, קראתי את הנתונים על לקוח מסויים בתחילת הטרנזקציה, ולאחר מכן בסוף הטרנזקציה אני שוב קורא את הנתונים של אותו הלקוח. מה ההתנהגות שאני רוצה שתהיה אם במקביל רצה טרנזקציה אחרת, שמעדכנת את הנתונים של אותו הלקוח? אני רוצה לראות את אותם הנתונים שראיתי בהתחלה? את הנתונים העדכניים (ואז ראיתי בתוך אותה טרזנקציה שתי גרסאות שונות של אותה השורה)? או אולי אני בכלל לא רוצה שמצב כזה יקרה?

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

ה- Isolation Levels שקיימים ב- SQL Server

ב- SQL Server קיימים מס’ Isolation Levels שאפשר לקבוע לטרנזקציה. הקביעה מתבצעת באמצעות הוספת השורה SET TRANSACTION ISOLATION LEVEL xxx בתחילת הטרנזקציה, כאשר במקום xxx מופיע שם ה- Isolation Level.

מבוססי נעילות

דרך אחת להבטיח “Isolation” בין שתי טרנזקציות, היא להשתמש בנעילות. כלומר, שהטרנזקציה שמתעסקת עם המידע מסמנת (לוגית) את המידע הזה כנעול על ידה (כאשר יש סוגי נעילות שונים) וטרנזקציה אחרת שמגיעה להתעסק עם אותו המידע מכבדת את הנעילות שלקחה הטרנזקציה הקודמת.  בצורה כזאת, אנחנו משיגים אפקט של סנכרון שמבוסס על זה שבמקרה של “התנגשות רצונות” בין טרנזקציות, אחת ממתינה לשנייה שתסיים, תשחרר את הנעילות שלה – ואז ממשיכה. מנגנון הנעילות, וסוגי הנעילות השונים שיש ב- SQL Server, מורכבים וארוכים מספיק כדי למלא פוסט שלם משלהם, אבל מכיוון שזאת לא המטרה של הפוסט הזה, נגיד שיש נעילות, יש סוגי נעילות ששונים בינהם ומאפשרות נעילה על רכיבים שונים ובגרנולריות שונה.

SERIALIZABLE

נניח שהיינו רוצים לפתור איזושהי בעית סנכרון בין שני threads בקוד שאנחנו כותבים. אחת הדרכים הבסיסית ביותר הייתה לשים את כל הבלוק של הקוד שעלול להתנגש תחת lock, כאשר הוא כתוב ברצף. ואז מובטח לנו שעבור רצף הפעולות שלנו אנחנו מחזיקים את הנעילה, והיא לא הולכת לשום מקום עד שאנחנו מסיימים. את התרחיש הזה אפשר להקביל ל Serializable Isolation Level.

הקונספט פשוט: אנחנו רוצים להבטיח שאנחנו רואים רק את המידע הכי עדכני, ושהוא נשאר אותו הדבר לכל אורך הריצה של הטרנזקציה שלנו – אלא אם כן אנחנו שינינו אותו. בנוסף, אנחנו לא מאפשרים לטרנזקציות אחרות לראות data
איך אנחנו משיגים את זה? מפזרים lock על כל שורה שקראנו (ובוודאי שעל כל שורה שעדכנו). לא רק זה, אלא מפזרים lock על כל הטווח שבין שתי שורות שקראנו. כלומר, אם קראנו שורה שה- ID שלה הוא 10 ושורה שה- ID שלה הוא 100, נשים lock על כל הטווח שבאמצע.
בצורה הזאת אנחנו הולכים למעשה בגישה הכי מחמירה. אנחנו כמובן משלמים על זה במקביליות, שאותה אנחנו מורידים ל-0. לא תהיה מקביליות על אותו המשאב בין טרנזקציות שמוגדרות כ- Serializable.

REPEATABLE READ

הקונספט של REPETABLE READ הוא להיות כמו SERIALIZABLE, רק קצת פחות מחמיר. איך זה בא לידי ביטוי שזה “פחות מחמיר”? אמנם נועלים על כל שורה שקוראים, בצורה כזאת שאחרים לא יכולים לקרוא (ובטח שלא לעדכן) אותה. אבל, לא נועלים על הטווח בין שתי שורות שקוראים. כלומר, אם קוראים שורה שה- ID שלה הוא 5 ושורה שה- ID שלה הוא 100, תהיה נעילה על שתי השורות הללו שתמנע עדכון וקריאה שלהן מטרנזקציות אחרות, אבל על השורות 6-99 לא תהיה נעילה – בניגוד ל- SERIALIZABLE.

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

READ COMMITTED

בעוד שגם ב- SERIALIZABLE וגם ב- REPEATABLE READ מובטח לנו שאם אנחנו קוראים את אותה השורה אנחנו נקבל את אותו המידע, פה מובטח לנו משהו חלש משמעותית – שנקבל מידע שהוא committed. כלומר, אם קראנו את אותה שורה בתחלת ובסוף הטרנזקציה, יכול להיות שנקבל ערכים שונים אם טרנזקציה אחרת שרצה בתור READ COMMITTEDעדכנה את המידע בין לבין.  עם זאת, מובטח שבכל אופן לא נקבל Dirty Read.

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

READ COMMITTED הוא ה- isolation level הדיפולטי ב- SQL Server, כי הוא מצד אחד מבטיח נכונות של המידע במקרה של הרצות מקביליות, ומצד שני מאפשר רמה גבוהה יותר של מקביליות מ- REPEATABLE READ ובטח מ-SERIALIZABLE.

READ UNCOMMITEED

מדובר ב- Isolation Level המינימליסטי ביותר, שלא מבטיח כלום. הוא יכול לשמש רק לקריאות (לא ניתן לבצע פעולות CRUD – יצירה, עדכון או מחיקה) ומאפשר לקרוא מידע שטרנזקציה פעילה שינתה, אבל עדיין לא עשתה commit (כלומר, ייתכן שהוא מעולם לא היה נכון ולעולם לא יהיה נכון). במילים אחרות – מאפשר Dirty Reads. מדובר ב- Isolation Level שלא מתאים אם רוצים לקבל מידע נכון, אבל הוא כן מבטיח שנוכל לקבל מידע בלי קשר לטרנזקציות אחרות שרצות ומעדכנות מידע.  כלומר, הוא מאפשר לנו לקבל מידע כלשהו.

כדאי לציין שלא רק שלא מובטח שנקבל מידע שאי פעם היה נכון, אלא יש עוד כמה תופעות לוואי ל- Dirty Reads: אנחנו יכולים לקבל את אותה שורה מס’ פעמים, לקבל שורה שנמחקה, לקבל מידע “חצי נכון” – ובקיצור, לקבל מידע שאינו אמין בהגדרה.

מעקב ב- Production אחר המתנות לקבלת נעילות

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

ב- SQL Server Management Studio, ב- Object Explorer, קליק ימני על שרת אליו אתם מחוברים ופתיחת ה- Activity Monitor, תפתח מסך המאפשר הבנה של סטאטוס השרת ברגע זה (כמובן, בהנחה שלמשתמש שלכם יש הרשאות מתאימות). ה-section העליון, Processes, מאפשר לעקוב אחרי תהליכים שרצים כעת ב- SQL Server.

SQL Server כולל “מיני מערכת הפעלה” מובנית, פנימית שלו, שנקראת SOS (ר”ת של SQL Server OS). אחד הרכיבים הבסיסיים בה, הוא ה- Scheduler. כל Scheduler ממופה ל- thread של מערכת ההפעלה, ויש לו תור של משימות שהוא מריץ אותם. כמות ה- Schedulers שפועלת ברגע נתון חסומה מלמעלה ע”י כמות ה- logical cores של מערכת ההפעלה. אחת המטרות העיקריות בשיטת העבודה הזאת היא להקטין את כמות ה- context-switch שנוצרים ע”י ה- scheulder של windows, ולעשת ניהול עצמאי של המשימות.

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

כל פעם שמשימה כזאת מוותרת על המשך זמן הריצה שלה, היא מספרת גם למה היא ממתינה (IO, lock, latch וכו’), ואם היא נחסמת ע”י תהליך אחר, גם מה התהליך שחוסם אותה.

במסך ה- Activity Monitor ניתן לראות זאת בקלות:

image

בצילום מסך הזה ניתן לראות ש- Session ID מס’ 52 ממתין במשך 39 מילי-שניות בהמתנה מסוג LCK_M_S, כלומר היא ממתין לקבל shared lock. אנחנו רואים שמי שחוסם אותו הוא session מס’ 54, כאשר אם אנחנו מסתכלים על השורה של session מס’ 54 אנחנו רואים שהוא מריץ עכשיו UPDATE, והוא בעצמו ברגע זה ממתין ב-wait  מסוג LOGBUFFER. אם נעשה קליק ימני על השורה->Details נוכל לראות בדיוק מה מורץ שם.

בכל אופן, מה שאני עשיתי, זה לפתוח ב-session מס’ 54 טרנזקציה שבה אני מעדכן כמה שורות, ואני מנסה לשלוף עליהם במקביל ב- session מס’ 52, ולכן הוא נעול (כי הוא פועל ב-isolation level של READ COMMITTED) עד שהטרנזקציה שב- session 54 תעשה commit.

Isolation Levels שאינם מבוססי נעילה

אם נסתכל על ה- Isolation Levels שראינו בינתיים: SERIALIZABLE, REPEATABLE READ, READ COMMITTED ו- READ UNCOMMITTED הם כולם מבוססים על נעילות (ועל התעלמות מנעילות, במקרה של READ UNCOMMITTED).
עם זאת, נעילות זאת לא הדרך היחידה להבטיח isolation בין טרנזקציות שונות.

Optimistic Concurrency

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

ב- SQL Server נעשה שימוש ב- Optimistic Concurrency  משולב במנגנון של versioning גם בעת עבודה עם SNAPSHOT ISOLATION LEVEL ו- READ COMMITTED SNAPSHOT ISOLATION LEVEL (שנדבר עליהם ממש עוד מעט) וגם במנגנון ה- In-Memory OLTP (שלא יוזכר בפוסט זה).

שמירת גרסאות

אם תשימו לב ל- Isolation Levels מבוססי הנעילות, תשימו לב שיש שם בעייה מהותית: יש מצבים שבהם מישהו רוצה לקרוא מידע – רק לקרוא אותו – וננעל כי מישהו אחר עדכן אותו ועדיין לא עשה commit. ואז, כדי למנוע dirty reads, אם אנחנו סתם רוצים לקרוא מידע, אפילו אם נעשה את זה בטרנזקציה שהיא ב- READ COMMITTED (הדיפולט, ההכי פחות מחמיר שעדיין מבטיח נכונות של המידע) – נינעל ונצטרך להמתין שמישהו יסיים לכתוב. וכל מה שאנחנו רוצים לעשות זה רק לקרוא. מבאס.

דרך אחת “לפתור” את הבעייה היא לקרוא באמצעות READ UNCOMMITTED. אבל אז – לא מובטחת נכונות של המידע. דרך אחרת, היא להשתמש ב- isolation level מבוסס גרסאות (versioning). כלומר, כשאני אקרא את המידע, אני לא רוצה לקרוא את המידע שחלקו מעודכן וחלקו לא – אני רוצה לקרוא את הגרסא הנכונה האחרונה של המידע. אני לא רוצה לראות “שברי מידע” מטרנזקציה שפועלת ברגע זה, אבל אני לא רוצה להמתין שהיא תסתיים (ובטח לא רוצה לעכב טרנזקציה אחרת מלהתחיל).
אני רוצה לקרוא את הגרסא הנכונה האחרונה של המידע, ואם הוא משתנה באמצע, להמשיך ולהבטיח שכל הקריאות שאני עושה יהיו שייכות לאותה גרסא שראיתי בהתחלה.

למעשה, אנחנו רוצים “הפרדת רשויות” בין מי שקורא למי שכותב. שמי שקורא לא יחסום את מי שכותב, ושמי שכותב לא יחסום את מי שקורא. lock-free, אבל שהמידע בכל זאת יהיה נכון.

SNAPSHOT ISOLATION

ראשית, נציין שכדי לעשות שימוש ב- SNAPSHOT ISOLATION יש לאפשר את זה קודם ברמת ה-DB באמצעות הפקודה:

ALTER DATABASE [DBName] SET ALLOW_SNAPSHOT_ISOLATION ON

כאשר אנחנו עושים שימוש ב- SNAPSHOT ISOLATION מובטח לנו שנוכל לקרוא כל שורה שנרצה. מובטח לנו גם שכל שורה שנקרא שייכת לאותה “גרסא” של מידע, כי כל שינוי שקורה במקביל למעשה יוצר “version” של השורה שלנו ב- tempdb (ובכך בא לידי ביטוי אחד החסרונות של עבודה עם row-versioning, היא יוצרת יותר עומס על tempdb וצריך לוודא שהחומרה שלנו תעמוד בכך, בהתאם לעומס שיש לנו וכמות הגרסאות שיהיו). כלומר, אנחנו יכולים לקרוא כל מידע שאנחנו רוצים, ולא מונעים מאף עדכון לקרות בטרנזקציות מקבילות.

מה לגבי עדכון בטרנזקציה שלנו? אז אנחנו יכולים לעדכן כמה שאנחנו רוצים, ואולי זה יצליח ואולי לא. פה בא לידי ביטוי אלמנט ה- Optimistic Concurrency. במצבים מסויימים אנחנו עלולים להיתקל ב- update conflict. למשל, אם טרנזקציה אחרת עדכנה את הערכים “מתחת לרגליים” שלנו. יש כל מיני דרכים להתגבר על הבעייה, למשל באמצעות שימוש ב- hint בשם UPDLOCK כדי לסמן כבר בזמן הקריאה שאנחנו עומדים לעדכן ערכים מסויימים. עם זאת, בפועל, SNAPSHOT ISOLATION מתאים בעיקר לתרחיש שיש לנו הרבה reader-ים שאנחנו לא רוצים שייחסמו מ- writer-ים שפועלים ב- Isolation Level שונה. או לחלופין, יש לנו גם writer-ים שפועלים ב SNAPSHOT ISOLATION, אבל אנחנו יודעים שלא יהיו בינהם התנגשויות, לא בינם לבין עצמם ולא כלפי אחרים

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

READ COMMITTED SNAPSHOT ISOLATION

אחת האפשרויות שקיימות ב- SQL Server היא להחליף את ההתנהגות של READ COMMITTED בהתנהגות מבוססת versioning. ההחלפה הזאת מתבצעת באופן גורף, ברמת דטאבייס, ולאחר שעושים אותה READ COMMITTED יתפקד למעשה בתור “READ COMMITTED SNAPSHOT”.

אז מה בעצם אומר READ COMMITTED SNAPSHOT? הבסיס דומה ל- SNAPSHOT ISOLATION (במובן של הניהול גרסאות למידע) למעט ההבדלים הבאים:

  • בעוד שב-SNAPSHOT ISOLATION ניהול הגרסא מתבצע ברמת הטרנזקציה ב- READ COMMITTED SNAPSHOT ISOLATION הוא מתבצע ברמת ה-statement. כלומר, אם אנחנו פותחים טרנזקציה, בודקים את מס’ השורות בטבלה A ולאחר מכן את מס’ השורות בטבלה B, כאשר במקביל, בין בדיקת השורות בשתי הטבלאות – טרנזקציה אחרת מכניסה שורות לטבלה B ועושה commit:
    • ב-SNAPSHOT ISOLATION: נקבל את מס’ השורות בטבלה B כמו שהוא היה בתחילת הטרנזקציה, ולכן כשבוצעה ההוספה מקודם לטבלה B, עדיין נשמר המידע על הגרסא שבה אין את השינוי הזה – וזה המידע שנקרא.
    • ב-READ COMMITTED SNAPSHOT ISOLATION: נקבל את מס’ השורות בטבלה B לאחר ההוספה, כי ניהול הגרסאות הוא ברמת ה- statement ומכיוון שזה statement חדש אנחנו נסתכל על הגרסא הכי עדכנית עכשיו.
  • כפועל יוצא מה-bullet הקודם, העדכונים הם למעשה pessimistic concurrency, ולא optimistic concurrency. מכיוון שכל statement עומד בפני עצמו מבחינת versioning, כאשר מגיעים להריץ את ה-UPDATE, אז SQL Server לא מחוייב בגלל הגדרת ה- ISOLATION LEVEL לקרוא את הגרסא שמתאימה לתחילת הטרנזקציה. ולכן, ההתנהגות במקרה של עדכונים שונה. בפעולות עדכון לא מתבצעת גישה לגרסא ספיציפית, אלא מתבצעת גישה לנתון העדכני ביותר ונעשה שימוש בנעילות על מנת להבטיח עדכון.
    כלומר, בכל הקשור לעדכונים, READ COMMITTED SNAPSHOT ISOLATION מתבסס על נעילות ולא על optimistic concurrency, כך ש-writer-ים ימתינו אחד לשני מצד אחד, אך מצד שני – לא ניתקל בעדכונים שנכשלים בגלל שהערך שעודכן הוא לא הערך העדכני ביותר, כפי שעלול לקרות ב- SNAPSHOT ISOLATION.

למעשה, השילוב של שתי הנקודות הללו הופך את השימוש ב- READ COMMITTED SNAPSHOT ISOLATION למתאים לרוב סוגי האפליקציות, ללא יותר מדי התאמות ושינויים. בנוסף, באפליקציות שמערבות קריאות וכתיבות מקבילות, או שבמקביל אליהן נעשה מול ה-DB גם קריאות ועדכונים ע”י אפליקציות אחרות – היתרון בא אפילו יותר לידי ביטוי. מרוויחים שקריאות לא חוסמות כתיבות (ולהיפך), אולם גם מקבלים התנהגות עדכון “נורמלית” (במובן שהיא יותר כמו שאנחנו רגילים), שלא דורשת שינויים אפליקטיביים רבים.

למעשה, קיימים בשוק שרתי DB שונים שבהם ההתנהגות הדיפולטית היא למעשה ההתנהגות של READ COMMITTED SNAPSHOT. ב-SQL Server זה לא מופעל כברירת מחדל, ויש לאפשר את ההתנהגות באמצעות הפקודה:

ALTER DATABASE [DBName] SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT

צריך כמובן להיות מודעים להשפעות הנלוות, בעיקר העומס הגבוהה יותר שיווצר על tempdb בשל ה- versioning. יש עוד מס’ נקודות שצריך להכיר, ועליהם מומלץ לקרוא ב-msdn. בכל אופן, למרבית האפליקציות כנראה יש יתרון רב במעבר לעבודה עם READ COMMITTED SNAPSHOT ISOLATION

שליטה על ה- Isolation Level מקוד

הגדרת הטרנזקציה ובחירת ה- IsolationLevel

עבודה עם טרנזקציות מקוד .NET מתבצעת באמצעות שימוש ב- System.Transactions.TransactionScope, או באמצעות יצירת SqlTransaction עם המתודה BeginTransaction של SqlConnection.

לשני האובייקטים ניתן להעביר (ב- constructor של TransactionScope או כפרמטר של BeginTransaction) את ה- Isolation Level המבוקש באמצעות ה- enum המתאים.

אם אתם עושים שימוש היום בטרנזקציות בקוד שלכם, ולא העברתם אף פרמטר במפורש, אז כנראה שכבר השתלם לכם לקרוא את הפוסט הזה. הדיפולט הוא IsolationLevel.Serializable, כלומר לעשות שימוש ב- Isolation Level המחמיר ביותר שיש.
ייתכן שזאת אופציה טובה לברירת-מחדל של ה- framework (פחות סיכוי לטעויות, תואם לסטנדרט של SQL), אבל מצד שני – מדובר בבחירה שהיא לא מתאימה למרבית האפליקציות. עכשיו, אחרי שהבנתם את המשמעות של כל ה- Isolation Levels השונים, כנראה שתוכלו לבחור משהו שמתאים באמת לאופי של הפעילות שאתם מבצעים מול ה-DB. ככל הנראה, במרבית המקרים רצוי לעבוד עם ReadCommitted (כאשר אם רוצים לעשות שימוש ב- row version, רצוי להפעיל ברמת ה-DB את הפיצ’ר של READ COMMITTED SNAPSHOT).

אם אתם לא מגדירים טרנזקציה בעצמכם, ולמשל עובדים עם Entity Framework, אז כשאתם קוראים ל- SaveChanges נעשה שימוש דווקא ב- default הגיוני יותר בעבודה מול SQL Server, שהוא READ COMMITTED.

השפעות הגדרת טרנזקציה על שאילתות עתידיות

כאשר אתם מייצרים connection חדש באמצעות SqlConnection (או באופן עקיף, באמצעות עבודה עם Entity Framework או ORM אחר), אתם למעשה לא בהכרח מייצרים connection חדש. כדי לשפר את ביצועי האפליקציות שעובדות מול מסדי נתונים, מופעל ע”י ADO.NET מאחורי הקלעים מנגנון של connection pool, ש-“ממחזר” למעשה connections בהם נעשה שימוש, כדי לא להקים ולסגור כל הזמן connection-ים מול ה-DB תוך תשלום ה- overhead של פעולות אלה.

כדי לייצר הפרדה לוגית בין instance-ים שונים של SqlConnection, או במילים אחרות כדי לעשות אשלייה של connection חדש, מורץ בין “מחזורים” שונים של ה- connnection פעולה שמטרתה “לאפס” את ה- connection, באמצעות קריאה ל- sp_reset_connection.

עד SQL Server 2014 לא התאפס ה- Isolation Level בעת “מיחזור” ה- connection. כלומר, אם בקוד הגדרנו טרנזקציה (ונניח לא העברנו Isolation Level, כך שהיא רצה כברירת מחדל בתור SERIALIZABLE), ולאחר מכן יצרנו SqlConnection אחר ואפילו לא הגדרנו בו טרנזקציה – עדיין הוא “יסחוב” את ה- Isolation Level שהגדרנו קודם. הרבה פעמים, זה ממש לא מה שנרצה. במיוחד אם לא היינו מודעים גם ל- default המחמיר.

החל מ- SQL Server 2014, בוצע שינוי כך ש- sp_reset_connection מאפס את ה- Isolation Level להיות READ COMMITTED, הדיפולט של SQL Server (שכמובן, יתפקד כ- READ COMMITTED SNAPSHOT אם הגדרנו זאת).

שילוב בין התנהגויות שונות באותה טרנזקציה

על אף שלטרנזקציה מוגדר ISOLATION LEVEL אחד, ניתן עדיין לשלב כדי לקבל התנהגויות של Isolation Level מסוג Y בתוך טרנזקציה שעובדת עם Isolation Level מסוג X.

דוגמא טובה לזה, היא שאם יש לנו טרנזקציה שרצה ב- READ COMMITTED ISOLATION, ואנחנו רוצים לקרוא מידע (שאילתת SELECT)  ולאפשר dirty-reads, אנחנו יכולים לעשות שימוש ב- hint שנקרא NOLOCK, כלומר לכתוב את השאילתה כך:

BEGIN TRANSACTION

 SELECT col1, col2

 FROM tbl1 WITH(NOLOCK)

 

 SELECT col1, col2

 FROM tbl2

COMMIT 

כך נשיג שהשליפה של tbl1 מתבצעת בתור READ UNCOMMITTED, בזכות ה- hint של ה- NOLOCK, ואילו השליפה מול tbl2 מתבצעת READ COMMITTED (ברירת המחדל, כי לא הגדרנו isolation level).

דוגמא אחרת, היא שאם הפעלנו את הפיצ’ר של READ COMMITTED SNAPSHOT ISOLATION, והיינו רוצים לעשות שליפה ספיציפית שמתנהגת בתור READ COMMITTED מבוסס הנעילות, היינו יכולים להשתמש ב- hint שנקרא READCOMMITTEDLOCK (צורת השימוש דומה לדוגמא מקודם, פשוט להחליף את NOLOCK) כדי לקבל התנהגות של READ COMMITTED מבוסס נעילות, על אף שלאחר הפעלת הפיצ’ר הזה ה-READ COMMITTED הופך להיות מבוסס versioning.

בצורה דומה יש hints נוספים – UPDLOCK, HOLDLOCK ועוד, שניתן לקרוא עליהם ב- msdn כאן. קחו בחשבון שיש לא מעט hints שם, חלקם לא קשורים ל- ISOLATION LEVEL, אבל בכל אופן כדאי לקרוא ולדעת מה האפשרויות השונות שיש.

 

 

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

SQL Server למפתחים: Indexed Views לטיפול בנתונים סיכומיים

הקדמה

Indexed view הוא פיצ’ר מאד שימושי, אבל גם באופן יחסי פחות מוכר – לפחות לאוכלוסיית המפתחים. מדובר למעשה בשילוב בין Views לבין אינדקסים “רגילים” (non-clustered indexes) שקיימים על טבלאות. לפני שנבין מה הבעייה, ואיך אפשר להשתמש ב- Indexed Views כדי לפתור אותה, ניישר קו לגבי מס’ מונחים.

View – אפשר לחשוב על view בתור שאילתת SELECT ששמורה בתור אובייקט ב-DB שלנו. אם, למשל, יש לנו view בשם MyView הוא יכול לייצג שאילתה כלשהי (למשל, SELECT ColumnA, Column B FROM MyTable WHERE ColymnC=5) וכל שאילתה שנריץ על ה- view תהיה בפועל כאילו הרצנו אותה על ה- result set שחוזר מהשאילתה של ה- VIEW. למשל, אם בדוגמא הזאת נריץ SELECT * FROM MyView WHERE ColumnA=1 אז אנחנו למעשה מתשאלים את כל השורות מטבלת MyTable שבהם ColumnC הוא 5 (מהגדרת ה- view) ו- ColumnA הוא 1 (מהשאילתה שאנחנו עושים).

Clustered Index – כאשר לטבלה קיים Clustered Index, המשמעות של זה היא שהנתונים של הטבלה מסודרים בדיסק לפי ה- key של ה- clustered index. כלומר, בפועל בדיסק שמור מבנה נתונים (B-tree) שהשורות עצמן הן האיברים בו. כלומר, בפועל, הטבלה מסודרת לפי ה- clustered index בדיסק. למשל, אם ה- clustered index שלנו הוא על עמודת ID שמהווה מספר סדרתי עולה, אז זה אומר שהנתונים של הטבלה בדיסק מסודרים לפי המספר הזה.

Non-clustered index – מדובר למעשה במבנה נתונים (גם כן, B-tree) ששמור ובנוי לפי ה- key columns שהוגדרו לו. אולם, בניגוד ל- clustered index, השורה עצמה לא כלולה בעץ, אלא כלול הערך של ה- clustered index של השורה (במידה שקיים), או מצביע לשורה (הקרוי RID) במידה שאין clustered-index, כלומר שמדובר למעשה ב- “heap” (טבלה ללא clustered index). כלומר, בניגוד ל- clustered index שמהווה “אינדקס ראשי” על הטבלה, אפשר להתייחס ל- non-clustered index בתור “secondary index” שהתפקיד שלו לעזור במצבים מסויימים של גישה לנתונים (למשל, בפילטור לפי ה- key columns שמוגדרים ב- non-clustered index).

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

הבעייה

Indexed View יכול לתת מענה למס’ בעיות שונות, ולהיות למעשה עוד כלי בארסנל הכלים לפיתרון בעיות ביצועים. אחת הבעיות לדוגמא ש- Indexed View יכול לפתור בקלות היא בעיית המידע הסיכומי. נניח לדוגמא שיש לנו טבלה שבה כל שורה מייצגת איזשהו “אירוע” כלשהו. למשל, ניקח טבלה שמתארת הדפסות שנעשות ע”י איזשהו בית דפוס שנראית ככה:

image

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

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

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

הפיתרון

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

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

אחרי שהבנו שיש יתרון ברור לשמירת המידע הסיכומי, נשאלת השאלה מה עושים איתו ואיך מייצרים אותו: נוכל למשל לעשות job שמייצר ומעדכן איזושהי טבלה סיכומית שאנחנו שומרים ב-DB שלנו (למשל, עושה MERGE למס’ השורות). נוכל לעשות job שטוען delta של המידע ל- SQL Server Analysis Services ולעשות את התשאול מולו. נוכל גם לתזמן יצירת דו”ח בכלי BI אחר שבו אנחנו עושים שימוש (למשל Tableau).  בדרכים האלה נוכל להשיג למעשה שמירה של המידע בצורה סיכומית, אבל לא זמינה מיידית (אלא מתעדכנת כשרץ ה- job המתאים).

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

דרך נוספת, אלגנטית יותר, שאפשר להשתמש בה לעיתים (אבל לא תמיד, נדבר על החסרונות בהמשך), היא לעשות שימוש ב- indexed views.

בעוד ש- view רגיל הוא למעשה metadada בלבד (כלומר, לא מבנה נתונים שנשמר בדיסק), indexed view הוא view שיש לו clustered index, כלומר ששמור עבורו מבנה נתונים בדיסק שמכיל את נתוני ה- view. ה-clustered index מופיע בתור index על ה- view, אבל בפועל אפשר להתייחס אליו גם בתור עוד אינדקס על טבלת המקור, כך שכל פעולה על טבלת המקור (או טבלאות המקור, כי נתמך גם join עם מגבלות מסויימות) מעדכן למעשה את מבנה הנתונים הזה ושומר על ה- indexed view מעודכן באופן מיידי.

דוגמא ליצירת Indexed View

תחילה, יש לייצר את ה-View שאנחנו נרצה לייצר עליו את ה- clustered index:

   1: CREATE VIEW PrintJobsAggregated WITH SCHEMABINDING 

   2: AS

   3: (

   4:     SELECT    DateStarted = CAST(JobSentEventTime as date),

   5:             UserID,

   6:             PrinterID,

   7:             TotalPages = SUM(NumPages),

   8:             PrintCounts = COUNT_BIG(*)

   9:     FROM dbo.PrintJobs

  10:     GROUP BY CAST(JobSentEventTime as date), UserID, PrinterID

  11: )

נשים לב שבשורה הראשונה אנחנו מגדירים את ה- view עם SCHEMABINDING. המשמעות של ההגדרה הזאת היא שלא ניתן יהיה לעשות שינויים בעמודות הטבלה המקורית, בצורה שתשבור את הגדרת ה- view (בניגוד ל- view שלא מכיל את ההגדרה הזאת, שלא מונע שינויים ל- base table בצורה כזאת ששוברת את ה- view). שימו לב שכאשר עושים שימוש ב SCHEMABINDING, כל התייחסות לאובייקט ב-DB צריכה להיות מורכבת מ <schema_name>.<object_name> (כלומר, לא רק שם הטבלה, אלא שם הסכימה לפניה).

דרישה נוספת שבאה לידי ביטוי היא ש- indexed view שמכיל GROUP BY חייב להכיל גם COUNT_BIG של מס’ השורות, כפי שניתן לראות בדוגמא שלנו (במקרה של שמירה סיכומית של נתונים, ממילא מדובר במשהו שבד”כ נרצה).

בשלב הזה אנחנו מחזיקים ביד סתם View רגיל – חסר ייחוד בינתיים. כעת, נרצה לייצר את ה- clustered index עליו. אם אנחנו משתמשים ב- SSMS, נוכל לעשות זאת גם דרך ה-GUI (כמו שיוצרים אינדקס על טבלה, פשוט על ה- view). ה-CREATE statement ייראה בסוף כך:

   1: CREATE UNIQUE CLUSTERED INDEX [CI_PrintJobsAggregated] ON [dbo].[PrintJobsAggregated]

   2: (

   3:     [DateStarted] ASC,

   4:     [UserID] ASC,

   5:     [PrinterID] ASC

   6: )

נשים לב לכך ה- clustered index על ה- view חייב להיות unique (בניגוד ל- clustered index על טבלה), ובאמת אנחנו עושים אותו על שילוב עמודות שמובטח לנו שהוא ייחודי (בגלל ה-GROUP BY שלנו).

שימוש ב- Indexed View

זהו, יצרנו indexed view. בואו נראה עכשיו קצת את ההשפעה של זה בפועל. לטובת זה, מילאתי את הטבלה ב- 2.1 מיליון שורות פיקטיביות שמתארות הדפסות בטווח תאריכים של 10 ימים. כאמור, מדובר בטבלה שבאופן שלה מתארת אירועים בעלי חזרתיות גבוהה של ערכים בעמודות UserID ו- PrinterID באותו היום (כלומר, יש הרבה אירועים באותו יום שנעשים ע”י אותו בן אדם מול אותה המדפסת), ולכן ב- indexed view שלנו יש בפועל קצת יותר מ-100,000 שורות. משמעותית פחות מאשר בטבלה המקורית.

אם נעשה שליפה של כמה שורות מה- indexed view נראה שהוא נראה כך:

image

כבר בשליפה הפשוטה של SELECT TOP … מה- indexed view ניתן לשים לב להבדל. נשווה את ה- execution plan בין השליפה מה- indexed view כמו שהוא מוגדר עכשיו, לבין שליפה מ- view זהה (פשוט לפני היצירה של ה- clustered index):

בלי ה-clustered index:

image

עם ה- clustered index:

image

 

נשים לב שבעוד בתמונה הראשונה, ללא ה-clustered index על ה- view, אנחנו מבצעים בפועל full scan של ה- clustered index של הטבלה עצמה, כאשר כן מוגדר ה-clustered index על ה- view, אז השליפה מתבצעת עליו והיא כפי שניתן לראות זולה משמעותית מבחינת ה- cost.

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

   1: SELECT    DateStarted = CAST(JobSentEventTime as date),

   2:         UserID,

   3:         TotalPages = SUM(NumPages)

   4: FROM dbo.PrintJobs

   5: GROUP BY CAST(JobSentEventTime as date), UserID

מדובר למעשה בשאילתה שמזכירה מאד את השאילתה של ה- indexed view עצמו, פשוט כאשר ה- grouping לא כולל את ה- PrinterID והשליפה לא כוללת את ה- COUNT_BIG. אם נסתכל על ה- execution planהוא נראה כך:

image

כלומר נבחר execution plan שעושה שימוש באינדקס של ה- indexed view. למה? מסיבה פשוטה. הוא עוזר לשליפה. בעוד שלעשות את השליפה מול הטבלה המלאה הייתה מייצרת למעשה מעבר על 2.1 מיליון שורות, ב-indexed view יש משמעותית פחות שורות, בגלל החזרתיות הגבוהה בטבלת המקור – ולכן השליפה מולו משתלמת יותר.

עם זאת, על אף שכמובן השליפה זולה יותר – אנחנו משלמים את המחיר של עדכון ה- indexed view בזמן ההכנסה:

image

NOEXPAND

בואו נניח שאני רוצה לעשות שליפה ולהביא את כל הדפסים שעבדו בתאריכים 15.10.2016-19.10.2016 ולא עבדו בתאריכים 21.10.2016-23.10.2016. לכאורה, מדובר בשליפה שאין שום יתרון ל- indexed view שלנו בביצוע שלה: היא לא כוללת אגרגציה משום סוג, לא משתמשת בעמודות האגרגטיביות של ה- view – בקיצור, לכאורה אין יתרון. עם זאת, ל- indexed view יש יתרון חשוב: הגודל שלו הוא כ-6% מגודל הטבלה המקורית. כלומר,  גם אם מדובר בסריקה של כל הנתונים, עדיין מדובר בסריקה של פחות נתונים.

ככה תראה למשל השאילתה שהגדרנו:

   1: SELECT DISTINCT UserID

   2:   FROM [check1].[dbo].[PrintJobsAggregated]

   3:   WHERE DateStarted BETWEEN '2016-10-15' AND '2016-10-19' 

   4:         AND NOT EXISTS (SELECT NULL

   5:                         FROM PrintJobsAggregated sec

   6:                         WHERE sec.UserID = PrintJobsAggregated.UserID AND sec.DateStarted BETWEEN '2016-10-21' AND '2016-10-23')

וכך נראה ה- execution plan שלה:

image

נשים לב לכך שהשליפה מתבצעת בפועל מול PrintJobs (ה- clustered index scan הוא על ה- PK של PrintJobs, שהוא ה- clustered index שלנו), ואנחנו מקבלים המלצה לאינדקס שייעל את השליפה: אם היה לנו nonclustered index על JobSentEventTime (או בעדיפות, אם הוא היה ה- clustered index) השליפה הייתה מהירה יותר (הזמן הנוכחי שלה הוא בממוצע 833ms על המכונה שלי, והיא מבצעת 74,163 logical reads).

אבל גם עכשיו, בלי לעשות שום שינוי במבנה הטבלה, אנחנו יכולים לייעל את השליפה הזאת תוך הסתמכות על ה- indexed view. נרצה למעשה להוסיף hint שרומז ל- query optimizer שלא לפתוח את הגדרת ה- view אלא לעבוד מול ה- indexed view בכל אופן. נעשה את זה באמצעות הוספת ה- hint שנקרא NOEXPAND – כך תראה השאילתה לאחר השינוי:

   1: SELECT DISTINCT UserID

   2:   FROM [check1].[dbo].[PrintJobsAggregated] WITH(NOEXPAND)

   3:   WHERE DateStarted BETWEEN '2016-10-15' AND '2016-10-19' 

   4:         AND NOT EXISTS (SELECT NULL

   5:                         FROM PrintJobsAggregated sec WITH(NOEXPAND)

   6:                         WHERE sec.UserID = PrintJobsAggregated.UserID AND sec.DateStarted BETWEEN '2016-10-21' AND '2016-10-23')

וכך נראה ה- execution plan:

image

זמן הריצה ירד ל- 22ms וכמות ה- logical reads ירדה ל-285. שיפור דרמטי לעומת ההרצה הקודמת.

למה זה קרה? משתי סיבות עיקריות. הראשונה, ה-clustered index של ה- indexed view קטן משמעותית מה- clustered index של הטבלה עצמה, ולכן שליפות מולו כוללות פחות קריאות. השנייה, העמודה הראשונה ב- clustered index של ה- indexed view היא למעשה DateStarted, כלומר ניתן לעשות seek עליה ולקבל את אותו המענה שהיינו מקבלים אם היינו יוצרים את האינדקס שהומלץ ע”י ה- query optimizer.

המסקנה פשוטה: כאשר יש indexed view אגרגטיבי, שמקטין לנו את מס’ השורות, נוכל לספק איתו מענה גם לשאילתות שלא עושות שימוש באגרגציות השונות ששמרנו, אלא רק בעובדה שפשוט יש פחות מידע. לעיתים, נצטרך להשתמש ב- hint של NOEXPAND כדי לקבל את ה- execution plan הרצוי.

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

יצירת non-clustered indexes על ה- Indexed View

עוד פיצ’ר שכדאי להכיר, הוא שברגע שיש לנו clustered index על איזשהו view, אנחנו יכולים לייצר עליו non clustered indexes נוספים. למשל, נוכל לייצר את האינדקס הבא על ה- view:

   1:  

   2: CREATE NONCLUSTERED INDEX [IX_PrintJobsAggregated_PrinterID] ON [dbo].[PrintJobsAggregated]

   3: (

   4:     [PrinterID] ASC

   5: )WITH (SORT_IN_TEMPDB=ON)

ולהשתמש בו כדי לשפר שליפות על מדפסות מסויימות שנעשות מול ה- indexed view.

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

מתי אי אפשר לייצר Indexed View?

מי שיסתכל על התיעוד של Indexed Views ב- msdn, יראה שיש רשימה ארוכה של statements שאם נעשה בהם שימוש ב- view אז לא ניתן להפוך אותו ל- indexed view. למשל, אם היינו רוצים לעשות view שעבור כל שילוב של משתמש ומדפסת מציג את התאריך והשעה הראשונים שהם הופיעו, האחרונים שהם הופיעו, ובכמה ימים שונים הופיע הצימוד – היינו נכשלים, כי הוא מכיל MIN ו-MAX.

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

   1: CREATE VIEW PrintJobsMinMax 

   2: AS

   3: (

   4:     SELECT    UserID,

   5:             PrinterID,

   6:             FirstSeen = MIN(DateStarted),

   7:             LastSeen = MAX(DateStarted),

   8:             NumDistinctDays = COUNT(DISTINCT(DateStarted))

   9:     FROM PrintJobsAggregated

  10:     GROUP BY UserID, PrinterID

  11: )

כאשר בפועל ה- view הזה לא יכול להיות indexed view בפני עצמו, אבל הוא נעזר ב- indexed view שעושה עבורו “חצי מהעבודה” של האגרגציה לרוזולוציה של אחת ליום.

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

סיכום

ראינו אז מה זה indexed view, וכיצד במצבים מסויימים הוא יכול לתת מענה לשמירת מידע סיכומי ולשליפות עליו, בעיקר בתרחיש כמו זה שראינו – של שמירת מידע אל אירועים. יש כמובן מגוון של תרחישים אחרים שמערבים עבודה עם מידע סיכומי. עבור חלקם אפשר לעשות שימוש ב- indexed views, ועבור אחרים לא, או שלא מדובר בפיתרון המתאים. עם זאת, מדובר בכלי שצריך להכיר וצריך להיות בארסנל של מפתחים שעובדים על מערכות מידע שונות.

SQL Server למפתחים: Bulk Insert ו- SqlBulkCopy

הקדמה

כל מפתח שעובד/עבד מול דטאבייס באשר הוא יודע איך מכניסים שורות לטבלה. מדובר באחת מהפעולות הבסיסיות, INSERT,. למשל, אם יש לי טבלה שמכילה 3 עמודות: עמודת ID עולה (IDE

CREATE TABLE [dbo].[BulkInsertDemo](

    [ID] [int] IDENTITY(1,1) NOT NULL,

    [NumCol] [int] NOT NULL,

    [LongTextCol] [varchar](2000) NOT NULL,

 CONSTRAINT [PK_BulkInsertDemo] PRIMARY KEY CLUSTERED 

(

    [ID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

NTITY), עמודה שמכילה ערך מספרי ועמודה של טקסט:

אזי ה- SQL Statement שבו נשתמש כדי להכניס שורות לטבלה הזאת הוא:

INSERT INTO BulkInsertDemo(NumCol, LongTextCol) VALUES (1, 'BlahBlahBlah')

שום דבר מתוחכם עד עכשיו.

כיום, בהרבה מאד פרוייקטים נעשה שימוש ב ORM כלשהו, כדוגמת Entity Framework, או NHibernate, ובפרוייקטים כאלה אנחנו לא נייצר את ה- INSERT Statement בעצמנו, אלא הוא יורץ עבורנו באמצעות שכבת ה- ORM.  זה לא באמת משנה בפועל. מבחינת SQL Server, מורץ INSERT Statement כדוגמת זה שראינו עד עכשיו.

הבעייה

כשעושים INSERT לשורה אחת, ל-10 שורות ואפילו ל-1,000 שורות – הזמן סביר לחלוטין. אבל כאשר עושים INSERT ל-100,000 שורות או ל-200,000 שורות – מגיעים כבר לזמנים פחות סימפטיים.
בגרף הבא אפשר לראות את זמני ההכנסה במילי שניות (ציר ה- y) כתלות בכמות השורות (ציר ה- x). כאשר אנחנו מכניסים נתונים לאותה טבלה שיצרתי קודם, כלומר כל שורה כוללת שתי עמודות מספריות, ועמודה טקסטואלית באורך של 480 תווים.

image

 

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

  • latency רשת: כל בקשה, קטנה ככל שתהיה, שעוברת מעל הרשת גוררת איתה את ה-“קנס” של latency רשתי. בדוגמא המסויימת שהבאתי הקנס הזה למעשה “מוזנח” בגלל שהאפליקציה שמכניסה את המידע וה-DB יושבים מעל אותו מחשב. בפועל, בתרחיש real-world, בד”כ יהיה לנו שרת DB שעל כל גישה אליו אנחנו משלמים קנס latency קטן (כמה מילי-שניות). אם אנחנו מכניסים רק שורה אחת בכל גישה, אז אנחנו משלמים את הקנס הזה במכפלות גדולות.
  • המתנה לנעילות: כאשר אנחנו כותבים לטבלה, אנחנו צריכים לקחת נעילות מסויימות כדי לשמור על הנכונות הלוגית (והפיזית) של המידע. במצבים מסויימים, אם מתבצעות פעולות במקביל, אנחנו עלולים להמתין עד שאנחנו מקבלים את הנעילות הללו. בדוגמא המסויימת שלנו, לא באמת היה צורך להמתין לסיום טרנזקציות אחרות, כי לא רצו פעולות אחרות במקביל. כמובן שבכל אופן היו locks ו- latches בשימוש, אבל לא נדרשנו להמתין להם.
  • עלות CPU: מין הסתם, יש לנו עלות מסויימת של זמן CPU בביצוע ה- insert. במקרה שלנו, העלות הזאת יחסית מינורית, אבל כמובן בכל זאת קיימת. מדובר בעלות שכרוכה למשל בייצור ה- execution plan של ביצוע הפעולה, והעלות שדרושה להפעלת פעולת ה- IO (בצורה אסינכרונית, כמובן) וניהול התהליך. פעולות ה- CPU מתבצעות ב-SQL Server ע”י ה- schedulers, כאשר עבור כל ליבה לוגית שחשופה ל- SQL Server קיים scheduler שלוקח משימות מתור המשימות, ומתחיל את הביצוע שלהם עד שהוא מגיע לאיזושהי פעולה שדורשת ממנו המתנה, ואז הוא עובר לבצע פעולה אחרת (מנגנון שקיים במטרה להקטין context switch).
  • עלות כתיבה ל- transaction log: כל פעולה שמתבצעת מול ה-DB וכוללת שינוי של data files, לא מבתצעת ישירות על ה- data files, אלא ראשית נכתבת “רשומת לוג” המתעדת את הפעולה ב- transaction log [קובץ ייעודי המשמש למטרה הזאת, ופתוח ע”י SQL Server בתור memory mapped file]. המטרה של התהליך הזה היא לאפשר rollback של הטרנזקציה במידת הצורך, ולהבטיח את שלמות הנתונים בכל מצב (גם במצב של הפסקת חשמל באמצע תהליך הכתיבה, למשל).  כמובן, יש עוד שימושים ל- transaction log, אבל הם מחוץ ל- scope של הפוסט הזה.
    בפועל, זה אומר שהמידע שאנחנו רוצים לכתוב לטבלאות נכתב קודם ל- transaction log (בצירוף כמובן רשומות נוספות המבטאות פעולות כמו הקצאת page-ים וכו’). במקרה שלנו, כל פעולת INSERT גררה כתיבה של כל המידע שהוספנו לטבלה גם ל- transaction log עצמו. כתיבה לקובץ מגלמת בתוכה עלות של I/O. קצב הביצוע של פעולות ה-IO מהווה גם איזשהו חסם עליון על מהירות ביצוע פעולת כתיבה. כלומר, אם יש לנו throughput נמוך במיוחד לדיסק של 1MB/sec (לטובת ההקצנה), אז זה אומר שלא-משנה-מה לא נוכל לכתוב מידע יותר מהקצב הזה. בנוסף למגבלת ה- throughput קיימת מגבלת IOPS: כמה פעולות IO שונות הדיסק יכול לבצע בשנייה.

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

הפיתרון: BULK INSERT

SQL Server כולל תמיכה מובנית לתרחיש שנדרשת טעינה של כמות גדולה של נתונים, שהוא בפועל תרחיש די נפוץ. התמיכה הזאת מתבצעת באמצעות statement שניתן להריץ דרך SQL Server שנקרא BULK INSERT שמאפשר לקבל קובץ (ותיאור של הפורמט שלו) ולטעון את הערכים ממנו לטבלה בצורה יעילה.

בנוסף, קיים command line utility בשם bcp.exe שמאפשר ביצוע של הפעולה הזאת דרך command line, בלי SQL. עבור כאלה שעובדים עם SSIS, אז יש יכולת להשתמש ב- bulk insert באופן מובנה. בנוסף, כאשר עושים שימוש ב Import and Export wizzard, עבור import של נתונים לתוך SQL Server – זה נעשה תוך שימוש ב- BULK INSERT.

כל זה נחמד, אבל כמפתחים אנחנו רוצים לעשות דרך לבצע את הפעולה בצורה אפליקטיבית. לשם כך קיים אובייקט מובנה ב- .NET Framework בשם System.Data.SqlClient.SqlBulkCopy., שבו אני אתמקד בהמשך ההסבר.

נדגים רגע את השימוש ב- SqlBulkCopy:

   1: //Prep data

   2: DataTable dt = new DataTable();

   3: dt.Columns.Add("NumCol", typeof(int));

   4: dt.Columns.Add("LongTextCol", typeof(string));

   5: for (int i = 0; i < rows; i++)

   6: {

   7:     var row = dt.NewRow();

   8:     row["NumCol"] = i;

   9:     row["LongTextCol"] = _longString;

  10:     dt.Rows.Add(row);

  11: }

  12:  

  13:        

  14: //Load data

  15: using (var bulkCopy = new SqlBulkCopy(_connectionString) { DestinationTableName = "BulkInsertDemo" })

  16: {

  17:     bulkCopy.ColumnMappings.Add("NumCol", "NumCol");

  18:     bulkCopy.ColumnMappings.Add("LongTextCol", "LongTextCol");

  19:     bulkCopy.WriteToServer(dt);

  20: }

לפני שנפענח מה קורה פה, נראה רגע איך נראה הגרף שמשווה בין INSERT ל-BULK INSERT עם SqlBulkCopy (הכחול זה INSERT-ים והכתום זה SqlBulkCopy) [הבהרה: ה- benchmark הזה מיועד כדי לתת תחושה בסיסית לגבי השוואת המהירות, ולא בודק קריטריונים נוספים שונים שיכולים להשפיע על המהירות. אם אתם מעוניינים, יש הרבה benchmarks מפורטים הרבה יותר ברשת]

image

כמו שאפשר לשים לב, ההבדל דרמטי, במיוחד בקצב הגידול. עבור הכנסה של 200,000 שורות: הכנסה באמצעות INSERT-ים בודדים לוקחת 39.5 שניות, בעוד ששימוש ב- SqlBulkCopy מוריד את הזמן ל- 3.1 שניות (הזמן כולל את הזמן של יצירת ה- data table והכנסת המידע אליו. הזמן של ביצוע פעולת הכתיבה עצמה, הוא 1.8 שניות). גם עבור כמות שורות קטנה יותר יש פער משמעותי לטובת bulk insert. למשל, עבור 1,000 שורות מדובר בפער בין 205ms ל- 17ms.

אז אחרי שהבנו שאנחנו מקבלים מזה תמורה אמיתית, בואו נבין ביחד את הקוד שמופיע קודם שעושה שימוש ב- SqlBulkCopy. תחילה, כפי שאפשר לראות, אנחנו מייצרים DataTable וממלאים אותו בערכים. הסיבה היא של- WriteToServer, המתודה של SqlBulkCopy שמשמשת לכתיבת המידע בפועל, יש overload שמקבל DataTable.  בהמשך נראה דרכים נוספות.
אחרי שמילאנו את ה- DataTable, אנחנו מייצרים instance של SqlBulkCopy, נותנים לו את ה- connection string ב- constructor (הוא תומך בקבלת פרמטרים נוספים, אבל אנחנו לא מעבירים כאלה, כך שהוא עובד לפי ה- defaults שלו). בנוסף, אנחנו מגדירים את ה- DestinationTableName להיות שם הטבלה שאליה אנחנו מכניסים את המידע.

בחלק הבא אנחנו מבצעים mapping בין שמות העמודות כפי שהן מופיעות ב- DataTable לשמות העמודות בטבלה. השמות במקרה הזה זהים, כך שאפשר היה לעבור בלולאה על העמודות ולהוסיף את ה- mapping תוך הסתמכות על השמות הזהים.  בסוף אנחנו קוראים ל- WriteToServer שמבצע את הפעולה בפועל תוך שימוש ב- BULK INSERT. קיימת גם מקבילה async-ית שנקראת WriteToServerAsync שניתן להשתמש בה בשילוב עם async-await.

לעטוף את זה יפה

עבודה עם DataTable ישירות רק לטובת ביצוע ה- bulk insert זה די מגעיל, ותכלס – אין סיבה טובה לעשות את זה אם יש לנו איזושהי שכבה כבר באפליקציה שעושה אבסטרקציה של המידע לאובייקטים (ORM כלשהו).

מי שעובד עם Entity Framework יכול להשתמש ב- EntityFramework.BulkInsert. מדובר בפרוייקט שזמין כ- NuGet package שמוסיף את הפונקציונאליות של BulkInsert ל- context ומאפשר לקרוא ל- context.BulkInsert ולהעביר IEnumerable של entities של entity framework (בין אם database first ובין אם code first) ולהנות מהביצועים של bulk insert.

מי שעובד עם ORM אחר, או משהו משלו , ורוצה להשתמש ב- SqlBulkCopy בלי להוסיף את ה- overhead של לייצר DataTable ולהעתיק אליו את המידע, יכול לראות את הפוסט הזה ב- stackoverflow  שמכיל בסיס של מימוש ל- ObjectDataReader, ש- WriteToServer תומך גם בלקבל אותו. בצורה הזאת ניתן יחסית בקלות לשלב את זה עם אובייקטים משלכם (בין אם במיפוי של 1:1 כאשר השמות באובייקטים זהים לשמות של השדות בטבלאות, ובין אם לעשות עוד קצת שינויים ולהוסיף attributes שמתארים את הקשר בין האובייקט לטבלה ולהתאים את הקוד לשימוש בו).

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

למה זה יותר מהיר?

יש כמה גורמים שהופכים את פעולת ה- BULK INSERT להיות יותר מהירה:

הקטנת ה- overheadהתקשורתי

כאשר עובדים מול שרת מרוחק, אז כאמור יש לנו “קנס” של latency לכל בקשה שאנחנו מבצעים. כלומר, נרצה לייצר בקשות גדולות כמה שיותר כדי להקטין את ה-overhead התקשורתי. למשל, אם נגדיר שה- latency הרשתי שלנו פר-בקשה הוא 1ms. כלומר, אם נעשה 200,000 הכנסות של שורה אחת כל פעם – יוצא ששילמנו 200,000ms (כלומר, קצת יותר מ-3 דקות) עוד לפני שעשינו ולא פעולה אחת – רק עלות גישה רשתית. 
כאשר אנחנו משתמשים ב- BULK INSERT אנחנו מאפשרים “לאגד” הכנסה של כמה שורות באותה פאקטה שיוצאת מהמחשב שלנו, וכך מקטינים את הקנס הרשתי.
כמובן שאנחנו לא יכולים להקטין אותו ל-0, כי יש מגבלה של packet size (גם ברמת ה- SQL Server, גם ברמת המערכת הפעלה, גם ברמת הראוטרים שבדרך, גם ברמת הכרטיס רשת…). אבל, אם למשל גודל שורה שאנחנו מכניסים הוא 300 בתים, ואנחנו עובדים בסביבה שתומכת ב- jumbo frames, כלומר MTU של 9000 בתים, אנחנו יכולים להכניס כ-22 שורות לפאקטה אחת שעוברת פיזית בכרטיס רשת (כמובן שכתלות ב- packet size שמוגדר  ב- connection string יכול להיות שבפועל נשלחות יותר שורות, והפיצול מתבצע ברמת הכרטיס רשת). כלומר, אנחנו כבר מקטינים באופן משמעותי את ה- overhead התקשורתי.

הקטנת ה- overhead הכללי בטיפול בקשה-בקשה

חוץ מה- overhead התקשורתי, טיפול במרוכז ב-batch של שורות שיש להכניס ולא שורה-שורה מאפשר ל- SQL Server לייעל את התהליך: לא נדרש ייצור execution plan(גם אם טריוויאלי) מחדש עבור כל שורה, מציאת page-ים לאכסון המידע מתבצעת בבת אחת לכמות גדולה יותר ולכן מפחית את ההשפעה, אם מוגדרת דחיסה ברמת ה- page, אז היא יכולה להתבצע פעם אחת ולא הכנסה של כל שורה גוררת “פתיחה ודחיסה” מחדש וכו’. בקיצור, יש יתרון גדול לאגד מספר בקשות יחד אם אפשר.

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

Minimal-logging

בעבודה עם ריבוי INSERT-ים, כפי שנאמר קודם, המידע נכתב ל- transaction log ולאחר מכן נכתב ל- data files. כלומר, בפועל, כמות ה- IO המצטבר על השרת שלנו גדולה יותר: אם סופרים את כל פעולות ה- IO המעורבות בכתיבת הנתונים החדשים, אז אנחנו גם כתבנו את כל השורות החדשות למעשה ל- transaction log (כי תיעדנו כל שינוי שנעשה ב- page-ים ב- buffer pool בזיכרון) וגם לאחר מכן כל המידע הזה נכתב ל- data files ע”י ה- lazy writer או בעקבות פעולת CHECKPOINT.

כאשר ה-DB מוגדר ב- recovery model של Simple או Bulk-Logged (בתמצית, אפשר להתייחס ל- recovery model כמדיניות שמירת רשומות ה- transaction log שכבר “אין בהן צורך” מבחינת התקינות של ה-DB. הגדרות ה- recovery mode השונות משפיעות על סוגי הגיבויים והשחזורים שאפשר לעשות, ויש להן חשיבות רבה – שהיא מחוץ ל- scope בפוסט הזה), אז מתאפשר פיצ’ר של SQL Server שנקרא minimal logging (יש עוד תנאים שנדרשים להתקיים, למשל שהטבלה לא מסומנת ל-replication ושמגדירים table lock) שבמסגרתו נכתב משמעותית פחות מידע ל- transaction log (נכתב למעשה רק מידע על page-ים חדשים שהוקצו לטובת אכסון המידע) והמידע נכתב למעשה ישירות ל- data files. בפועל, זה אומר משמעותית פחות I/O ולכן משפר את ביצועי טעינת המידע ונותן למעשה שיפור נוסף מעבר להקטנת ה- overhead

 

נקודות שצריך להיות מודעים אליהם בעבודה עם BULK INSERT

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

הגדרות שאפשר להגדיר לפעולת ה- BULK INSERT

ב- constructor של SqlBulkCopy ניתן להעביר לו הגדרות שונות באמצעות SqlBulkCopyOptions. למשל:

   1: using (var bulkCopy = new SqlBulkCopy(_connectionString, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers) { DestinationTableName = "BulkInsertDemo" })

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

  • CheckConstraints: מאפשר להגדיר שתהיה בדיקה של constraints שמוגדרים על הטבלה בזמן ההכנסה. כברירת מחדל, לא מתבצעת בדיקה של constraints.
  • FireTriggers: כאשר מוגדר, טריגרים של AFTER INSERT יופעלו.
  • KeepIdentity: כברירת מחדל, עמודו שמוגדרות בתור IDENTITY – גם אם מועבר אליהן ערך הוא יידרס ע”י הערך שנבחר ע”י SQL SERVER. אם מוגדר, אז יישמר הערך של עמודות ה- IDENTITY.
  • TableLock: מגדיר שתילקח נעילה מלאה על הטבלה. מאפשר לנו (בכפוף לקיום התנאים האחרים) להנות מהיתרונות של minimal logging

הגדרה חשובה נוספת, שלא מועברת באמצעות SqlBulkCopyOptions אלא באמצעות property אחר של SqlBulkCopy, בשם BatchSize היא הגודל של ה- batch. כברירת מחדל, כשאנחנו קוראים ל- WriteToServer כל המידע נכתב ל- SQL Server בתור batch בודד. המשמעות של batch היא כמות השורות שנשלחות כל פעם ל-SQL Server. אם מוגדר UseInternalTransaction (אופציה של SqlBulkCopyOptions), אז כל batch נחשב כטרנזקציה בפני עצמו, כך ש-rollback יתבצע בגרנולריות של batch.

עוד הגדרה חשובה שכדאי להגדיר, בכלל לא מוגדרת במסגרת ה- SqlBulkCopy היא ה- packet size. את ההגדרה הזאת מוסיפים ל- connection string, והיא מאפשרת לנו למעשה להגדיל עוד יותר את ה- throughput של ההכנסות. ברירת המחדל היא 4096, כאשר את ההגדלה צריך לעשות בזהירות (זה משפיע גם על צריכת הזיכרון בשרת), אבל במקרה של פעולת bulk insert בסביבה שעושה שימוש ב- jumbo frames, אז בוודאי שנרצה להגדיל לפחות פי 2.

BULK INSERT ופעולה מקבילית

כמו כל פעולה ב- SQL Server, גם BULK INSERT לוקח נעילות. אולם, כל עוד לא מוגדר TableLock, אין שום מניעה להריץ ממספר קליינטים שונים Bulk Insert ושהפעולות יתבצעו במקביל. צריך להבדיל פה בין מקביליות שמתבצעת ברמת ה- clients, שקורית כאשר יש מספר thread-ים או פרוססים שעושים Bulk insert לאותה הטבלה במקביל לבין parallel execution plan שאומר שהפעולה מתמקבלת ברמת SQL Server.

כדי לבצע טעינות מקביליות ממספר קליינטים, אופציית ה- TableLock חייבת להיות לא מסומנת.

כדי לאפשר ל- SQL Server לבצע את פעולת ה- bulk insert כ- parallel operation, אז אופציית ה- TableLock חייב כן להיות מסומנת, ובנוסף חייב לא להיות מוגדר אף אינדקס על הטבלה שאליה טוענים, כך שזה תרחיש שמתאים בעיקר אם עובדים עם טבלאות staging.

 

BULK INSERT וקשרים בין טבלאות

כאשר אנחנו עובדים עם Entity Framework, אחד הפיצ’רים החזקים הוא היכולת לייצר קשרים בין אובייקטים (associations) שלאחר מכן כאשר אנחנו עושים SaveChanges, אז EntityFramework יודע לייצר מהם את ה- insert statements שיבצעו את ההכנסה כך שהקשרים הללו יישמרו גם ברמת הטבלאות ב-DB. הצורה שבה זה מתבצע היא שלאחר כל insert, מתבצעת שליפה ע”י Entity Framework של הערך שחוזר מ- SCOPE_IDENTITY, כלומר של ה- “id” של השורה שהוכנסה, מה שמשמש לטובת יצירת ה-relationship באמצעות שימוש בערך הזה ב- insert-ים העתידיים.

כאשר עובדים עם BULK INSERT, אז אנחנו מכניסים את המידע כ- bulk ובפרט לא מקבלים את ה-IDENTITY עבור כל ערך.  כלומר, צורת העבודה הזאת לא מתאימה להכנסה “בבת אחת” של object graph שמתאר קשרים שמתמפים ל- relationship ברמת ה-DB.

מה אפשר לעשות?

  • דה-נורמליזציה לטבלאות שמבצעים אליהם טעינות מאסיביות: אם למשל אנחנו רוצים לטעון מידע בכמויות מאסיביות לטבלת LogEvents, שלה למשל יש relationship של 1:1 מול טבלת LogEventsExtendedDetails כך שחלק מהמידע נשמר פה וחלק נשמר שם . במקרה זה ייתכן שביצוע דה-נורמליזציה ואיחוד לטבלה אחת יהיה פיתרון פשוט ויעיל – שיאפשר לנו להנות מהיתרונות של bulk insert בלי להתקשות עם החיסרון של relationships.
  • שימוש בערכים שממולאים כבר ע”י ה- client:  אם למשל יש לנו טבלת LogEvents וטבלת LogEventsParameters שמכילה עבור כל שורה בטבלת LogEvents מספר פרמטרים שאנחנו רוצים לשמור. במקרה כזה, אם נרצה גם להשתמש ב- bulk insert לא נוכל במידה שהקישור בין LogEventsParameters ל- LogEvents מתבצע באמצעות ערך של עמודת IDENTITY, או כל ערך אחר שמיוצר ברמת ה-DB. אם, לעומת זאת, נייצר את הערך בצד המכניס, למשל נייצר GUID ונשתמש בו לטובת הקישור, נוכל לייצר את המידע כך שנשמר הקישור על בסיס ה-GUID הזה שנקבע ב- client ולבצע שתי פעולות bulk insert עם שני instance-ים בלתי תלויים של SqlBulkCopy לטובת הכנסה לשתי הטבלאות. ניתן לעשות עוד כל מיני מניפולציות המתבססות על “בישול” המידע ב- client שמבצע את ההכנסה.

BULK INSERT וכשלונות

ההתנהגות הכללית של BULK INSERT היא של רצף INSERT-ים בלתי תלויים (מבחינה לוגית), ולכן כישלון שקורה לא גורר rollback של כל ה- INSERT-ים . אם מוגדר UseInternalTransaction, אז כן יתבצע rollback של ה- batch הנוכחי (שגודלו כגודל ה- batch size, או בדיפולט כל השורות).

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

 

מעקב אחרי קצב ה- BULK INSERT בסביבת ה- production

ניתן לעקוב אחר קצב פעולות ה- bulk insert דרך ה- performance monitor (נכנסים ל- run וכותבים permon.msc). ה-counter הרלוונטי נמצא ב- SQL Server : Databases –>Bulk Copy rows/sec, Bulk Copy throughput/sec:

image

 

שיפור מהירות ההכנסה של פעולות INSERT

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

INSERT של מספר שורות מרובה

פיצ’ר יחסית חדש ב- SQL Server מאפשר להעביר מספר values ב- INSERT רגיל:

   1: INSERT INTO BulkInsertDemo(NumCol, LongTextCol) 

   2: VALUES 

   3: (1, 'BlahBlahBlah'),

   4: (1, 'BlahBlahBlah'),

   5: (2,'a')

כמות השורות שניתן להעביר בצורה הזאת מוגבלת ל-1000 שורות. עבור 1000 שורות, הכנסה באמצעות רצף פעולות INSERT לקחה 205ms, באמצעות SqlBulkCopy לקחה 17ms ובאמצעות INSERT עם ערכים מרובים, כמו בשיטה הזאת, 143ms. עדיין נהנים מחלק מה- benefits (למשל, חיסכון מסויים ב- overhead של בקשה אחר בקשה), אבל בכל אופן מדובר בתהליך שהוא פחות optimized מ- BULK INSERT (על אף שעבור מי שכותב SQL הוא יותר פשוט, כי הוא לא דורש טעינה מקובץ חיצוני, הגדרת פורמטים וכו’).

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

שימוש ב- Delayed Durability

פיצ’ר נוסף שחדש יחסית ב- SQL Server הוא delayed durability שמאפשר לנו לשפר את ביצועי ההכנסה באמצעות ניתוק הצורך להמתין לכתיבה ל- transaction log (וכשיש כתיבה, היא יעילה יותר, כי מתבצעת כתיבה של buffer גדול יותר בד”כ), זאת במחיר הסיכון שקיים לאיבוד מידע (שלא קיים בעת עבודה “רגילה”, כלומר ללא איפשור delayed durability.

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

שימוש ב- In-Memory OLTP

עוד פיצ’ר חדש ב- SQL Server, הוא In-Memory OLTP שמאפשר למעשה שינוי צורת העבודה מול טבלאות מסויימות כך שכל נתוני הטבלה נשמרים בזיכרון. לא מדובר רק בפיצ’ר שמשנה את המיקום שבו הנתונים נשמרים, אלא הוא כולל גם מנגנון locking (או ליתר דיוק, מנגנון העדר locking) שונה, סוגי אינדקסים שונים וכו’. אולם, הוא מאפשר גם שיפור משמעותי בפעילויות הכנסת המידע, במיוחד כאשר נעשה שילוב של זה עם delayed durability או עם טבלאות שהן ללא data durability (כלומר, קיימות בזיכרון בלבד).

 

יש עוד לא מעט דברים שיכולים להשפיע על ביצועי INSERT, כולל נושאים שלא נגעתי אליהם בכלל (כי הם פחות נוגעים לזווית של מפתחים) כמו חומרה, והגדרות ברמת ה- DB וכו’, ומי שמתעניין יכול למצוא כמובן שפע של מידע בנושא ברחבי הרשת.

 

שחר.

ייצור אוטומטי של סקריפטי התקנה והסרה של SQL CLR Functions / Stored Procedures

לפני כמה ימים פרסמתי פוסט על משהו קטן שעבדתי עליו בין תרגיל אינפי לתרגיל לינארית, שכולל למעשה אוסף של functions ו- stored procedures המאפשרים עבודה מול Redis מ- SQL Server, כאשר מרבית הפרוייקט זה סה”כ תפירה בין קוד C# קיים לעבודה מול Redis ל- SQL Server – שמתבצעת באמצעות SQL CLR, פיצ’ר חביב (למרות שהיה יכול להיות טוב משמעותית) שמאפשר לשלב ולכתוב רכיבים שונים בדטאבייס (functions, stored procedures וכו’) ב- C#.

הדרישה שהייתה לי לצרכי פיתוח הייתה פשוטה – שבסוף כל build יהיה לי קובץ install.sql שכשאני מריץ אותו הוא מכיל את כל ה- CREATE statements של ה- CLR functions / procedures שלי, ו- uninstall.sql שכולל את ה- DROP statements הרלוונטיים. הדבר העיקרי ממנו רציתי להימנע זה התענוג (המפוקפק) בלהכין create statements לכל פונקציה בנפרד, ואז כל פעם שמשנים במהלך הפיתוח את הפרמטרים לעדכן גם בקוד C# וגם בסקריפט ה-SQL-י ולייצר למעשה שכפול מיותר של הגדרות.

הפיתרון הראשון שאליו הסתכלתי היה לממש הכל באמצעות SSDT. למי שלא מכיר, SSDT (SQL Server Data Tools), הוא פרוייקט של מיקרוסופט של תוסף ל- Visual Studio שמאפשר לייצג דטאבייס בתור פרוייקט ב-Visual Studioהכולל item-ים שונים: טבלאות, views, stored procedures, טריגרים, SQL CLR Functions – בקיצור, פחות או יותר כל מה שרוצים. SSDT תומך בתרחיש deployment באמצעות dacpac או באמצעות יצירת סקריפטי מיגרציה SQL-יים.
בעבר עשיתי מספר ניסיונות ב-SSDT לשימושים היותר קונבנציונליים שלו (ניהול סכימה של DB בתור פרוייקט) ופסלתי אותו כי הוא לא היה נוח (גם מבחינת תרחיש העבודה, וגם לשימוש באופן כללי). החלטתי שזה יכול להיות ניסיון טוב להשתמש בו.

צריך להבין שהתרחיש המקורי אליו מיועד SSDT שונה מהתרחיש שהיה לי במקרה הספיציפי הזה. התרחיש המקורי הוא כאמור שימוש לניהול סכימה של DB. בתרחיש המסויים שלי, אני רוצה לנהל פרוייקט שמייצג משהו שאנשים יכולים “להתקין” לתוך ה-DB שלהם. ובכל זאת, חשבתי שבזכות היכולת שלו לעקוב אחרי שינויי סכימאות, והעובדה שניתן להוסיף אליו CLR functions וכו’ הוא יתאים. די מהר התבדיתי – העבודה מולו מאד לא נוחה. כשאומרים לו לייצר סקריפטים (ולא לעבוד עם DAC), אז כל ייצור סקריפט לפרוייקט קטנטן לוקח מס’ דקות. אמנם לא רב, אבל הרבה יותר מהמס’ שניות שנראה לי סביר ונוח לצרכי עבודה שוטפת. בנוסף, התברר לי שהוא לא תומך בפונקציונאליות בסיסית מאד שרציתי – למשל קביעת default values שונים ל- CLR functions (ב-connect זה עדיין בקשה פתוחה).
מכיוון שהוא לא היה כיפי לשימוש, וגם לא נתן כל מה שרציתי – החלטתי שהוא לא מתאים לצורך הזה.

אז מכיוון שלמרות הכל לא רציתי לכתוב בעצמי באמצעות copy-paste את ה-SQL-ים של ההתקנה וההסרה, החלטתי לפתח משהו פשוט שייצר אותם עבורי עם Reflection. בסופו של דבר, יצרתי פרוייקט שמכיל אוסף של attributes שמתארים את כל ה- metadata הנוסף שצריך עבור ייצור הסקריפטים הללו, מעבר לזה שקיים כבר ב SqlFunctionAttribute וב- SqlProcedureAttribute.  כל מתודה שיצרתי שאמורה להתווסף כ- CLR function/procedure כוללת את ה- attributes הרלוונטיים.
בנוסף, יצרתי console application קטן שמייצר ע”ס ה-attributes האלה CREATE / DROP statements ומשלב אותם בקובץ טקסט שמכיל טמפלייט רלוונטי (שכולל אפשרות גם לעשות include לקבצי SQL אחרים). יש קובץ טמפלייט גם להתקנה וגם להסרה, שעל פיהם מג’ונרט הסקריפט התקנה והסרה הסופי, באמצעות post build events.

מכיוון שרוב הפרוייקט שלי מבוסס על CLR funcitons ו- CLR procedures, זה מה שהיה חשוב לי לפתור. את הקוד T-SQL שכן קיים, אני משלב בקובץ template באמצעות תמיכה ב- include (כלומר, אין משהו חכם שמתמודד עם stored procedures / functions שכתובים ב- T-SQL).  כמובן שפונקציונאליות אחרת שאני לא משתמש בה בכלל (כמו CLR Triggers) בכלל  לא כלולה בפרוייקט הזה.

אם אתם כותבים משהו שמבוסס על SQL CLR (ובכנות, נראה לי שזה משהו די נדיר), יכול להיות שזה יהיה רלוונטי עבורכם (למרות שאני מקווה ש- SSDT פשוט ישתפר, כי הוא הפיתרון הטבעי לזה). הקוד הרלוונטי זמין ב- GitHub.

SQL: להפוך XML במבנה לא ידוע (חלקית) לתצוגה טבלאית

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

למשל, אם אנחנו רוצים לשלוף מ- sys.tables ולהציג את התוצאות כ-XML בפורמט טבלאי, כל שצריך לעשות זה להוסיף FOR XML statement בסוף. ניתן בצורה הזאת גם להשיג שליטה מוגבלת על מבנה ה-XML שמתקבל. למשל, בדוגמא הבאה, אני מגדיר שיהיה root element בשם items ושכל שורה תהיה עטופה בתור item:

SELECT *

FROM sys.tables

FOR XML PATH('item'), ROOT('items')

ככה ייראה ה-XML שיתקבל:

image

אבל, מה קורה כשאנחנו רוצים להפוך את ה-XML הזה לתצורה טבלאית בחזרה? לצורך כך יש לנו את הפונקציה OPENXML. הפונקציה הזאת יודעת לקבל handle שמתקבל ע”י קריאה ל- sp_xml_preparedocument שעושים לפני כן, ויודעת להפוך את ה-XML הזה לתצורה של שורות וטבלאות.

מתי זה מסתבך? כשרוצים לעשות משהו גנרי שהופך XML מהסגנון הזה שראינו ל- rowset, בלי שאנחנו יודעים את שמות העמודות. למה זה מסתבך? כי OPENXML דורשת לקבל את שמות העמודות וה- data type שיהיה ב- rowset שיוחזר.

מה אפשר לעשות? לחלק את הבעייה לשתי שלבים: בשלב הראשון, לקבל את השמות של האלמנטים. בשלב השני, לקרוא ל- OPENXML ולהעביר את שמות האלמנטים שיקבלנו.

ה-stored procedure הבא עושה בדיוק את זה.. מייצר את רשימת העמודות מה-XML (הוא מצפה ל-XML במבנה של root element בשם items ובתוכו אלמנטים בשם item) ואז משתמש ב-OPENXML בשביל להפוך את ה-XML ל-rowset עם שמות העמודות שמועברות דינמית:

 

CREATE PROCEDURE [dbo].[ConvertXmlToRowset]

(

    @input xml

)

AS

BEGIN

    DECLARE @cols table (ColName nvarchar(500))

 

    INSERT INTO @cols(ColName)

    SELECT    

            keyValueSet.Name

    FROM   @input.nodes('/items/item') T(c)

    OUTER APPLY (

                    SELECT CAST(T.c.query('.') as xml) xmlData

                )  rowXml

    OUTER APPLY (

                    SELECT

                        C.Name

                    FROM rowXml.xmlData.nodes('/item/*') as T(C)

                    OUTER APPLY (

                                    SELECT

                                        T.C.value('local-name(.)', 'nvarchar(max)') as Name

                                    UNION ALL

                                    SELECT

                                        A.C.value('.', 'nvarchar(max)') as Value

                                    FROM T.C.nodes('@*') as A(C)

                                ) as C

                ) keyValueSet

 

    DECLARE @colsNames NVARCHAR(2000)

    SELECT @colsNames =COALESCE(@colsNames + ', ', '') + '[' +ColName + '] nvarchar(max)'

    FROM @cols

    GROUP BY ColName

 

    DECLARE @query nvarchar(max)=

    '

    DECLARE @doc nvarchar(max) = ''' + CAST(@input as nvarchar(max)) + '''

    DECLARE @idoc int 

    EXEC sp_xml_preparedocument @idoc OUTPUT, @doc

    

    SELECT    *

    FROM       OPENXML (@idoc, ''/items/item'',2)

            WITH (' + @colsNames + ')

    '

    EXECUTE(@query)

END

 

 

 

GO

 

 

ודוגמא לשימוש:

DECLARE @xml xml = (SELECT * FROM sys.tables FOR XML PATH('item'), ROOT('items'))

EXECUTE redisql.ConvertXmlToRowset @input = @xml

image

החסרון בשיטה הזאת, הוא שה-rowset שמתקבל בסוף לא זהה למקורי. ה- data type המקורי של העמודות לא נשמר, ובמימוש הזה גם הסדר לא נשמר. הפיתרון הנ”ל היה אחד הפתרונות ששקלתי כשמימשתי את שמירת ה- Rowsets ב- RediSql, ופסלתי אותו בסוף בגלל הסיבה הזאת – הוא לא מייצג נאמנה את המקור. עם זאת, במידה שזה פחות חשוב לכם ומבחינתכם לקבל את כל המידע בתור nvarchar(max) מתאים, עדיין יכול להיות מדובר בפיתרון שימושי.

בהצלחה.