תגית: database

MySQL Transactions – חלק א’

פוסטים בסדרה:
“תסביר לי” – גרסת ה SQL
לקחת את הביצועים ברצינות, בעזרת MySQL Performance Schema
נושאים מתקדמים ב MySQL: חלק א’ – עבודה עם JSON
נושאים מתקדמים ב MySQL: חלק ב’ – json_each  ו Generated Columns

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

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

כולנו, אני מניח, מכירים בסיסי-נתונים רלציוניים: על כך שהם ACID, ועל כך שיש להם טרנזקציות.

איך משתמשים בטרנזקציה? בבסיס, באופן הבא:

START TRANSACTION;
— do something 
COMMIT;
— or: ROLLBACK;

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


למה להשתמש בטרנזקציות?

כיום, טרנזקציות הוא דבר “לא-קולי” (“not cool”).

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

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

עדיין, הקמפיין להשמצת הטרנזקציות היה יעיל יותר – והטרנזקציות נותרו חבוטות ודחויות על רצפת חדר-התכנון של ארגונים רבים.
ובכן:
  • טרנזקציות הן אויב ל Scalability (ברוב המקרים), וכאשר צריך הרבה Scalability – עלינו להימנע מהן.
    • גם במערכות המתמודדות עם Scalability, ישנם flows ותסריטים שעובדים ב volume נמוך יותר – ויש להם בעיות שטרנזקציות יכולות לפתור.
  • חטאנו (גם) בשנות האלפיים, ועשינו שימוש-יתר, ביכולות שונות של בסיסי-הנתונים הרלציוניים: כמו טרנזקציות, Foreign Keys, או Triggers. עדיין, בשימוש מידתי – אלו כלים שימושים שיכולים לפתור בעיות רבות.
    • הסיפור של Overselling של כלים וטכניקות, הוא לא מקרה יחידני. הוא קורה שוב ושוב, ויקרה שוב ושוב. תתרגלו.
  • טרנזקציות הן פעמים רבות, הכלי הנכון והטוב ביותר לפתור בעיות.
    • אם אתם מסוגלים להתגבר על הקושי שלא המציאו את הטרנזקציות בשנת 2018 בגוגל, והן לא מככבות במצעד ה”טכנולוגיות היפות והנכונות של 2019 [א]” – אז יש לכם סיכוי טוב להעשיר את סט הכלים שלכם בצורה מועילה.
נחזור לשאלה המקורית, שמסתבר שהיא לא טריוויאלית: “מדוע / מתי להשתמש בטרנזקציות”?
הנה שימושים מרכזיים לדוגמה:
  • אנו רוצים לעדכן שתיים (או יותר) טבלאות בבסיס הנתונים בצורה עקבית: שלא יוותר מצב שאחת מעודכנת, אבל של תקלה – השנייה לא עודכנה, ויש לנו אי-עקביות בנתונים / נתונים חסרים.
  • אנו רוצים לעדכן ערך בטבלה, בצורה עקבית ותחת racing condition אפשרי: שני threads (או מנגנון מקבילי אחר) רוצים לבצע שינוי שתלוי במצב הקיים – אבל ללא הגנה התוצאה יכולה להיות שגויה.
  • אנו רוצים להשתמש בבסיס-הנתונים כמגנון סנכרון פשוט בין כמה שרתים.
    • אין לנו מנגנון אחר, ובסיס-הנתונים הוא מספיק טוב למשימה.
  • שיפור ביצועים – זה נשמע לא אינטואטיבי, אבל במקרים מסוימים טרנזקציות דווקא יכולות לעזור לשפר ביצועים.
  • ביצוע שינוי בנתונים בצורה זהירה ומפוקחת – (בסביבת פרודקשיין, למשל). נבצע את השינוי, נבחן את השלכותיו, ורק אז נעשה commit.
לכאורה אפשר לחשוב (בתמימות) שלכל המקרים יש פתרון זהה. שבשימוש בפקודות START TRANSACTION … COMMIT – נפתרו בעיותינו. מובטח לנו שמה שציפינו, אכן יקרה, וללא תופעות לוואי מיותרות – כמובן.
הבעיה היא שבכדי להבטיח ACID, בסיס הנתונים משתמש בנעילות שפוגעות מאוד ביכולת לרוץ במקביל על אותם הנתונים.

ישנו Trade-off מאוד בסיסי פה:
יותר בטיחות כנגד יותר מקביליות / ביצועים.

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

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

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

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

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

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

זו הזדמנות טובה להזכיר את Amdahl’s law המראה את הקשר בין החלק בפעולה שאינו מקבילי – לחסמים על מקביליות, לא משנה בכמה threads נשמש….
כדי להשלים את התמונה, שווה להכיר גם את ה Universal Scalability Law (בקיצור USL) שמפרמל מהי מקביליות, וממנו ניתן לראות שניסיון לדחוף יותר עבודה מקיבולת מסויימת – דווקא תאט את המערכת עוד יותר.

מודל המקביליות של InnoDB

לב מודל המקביליות של InnoDB, בדומה לבסיסי-נתונים רבים אחרים, מבוסס על שני כלים עיקריים:

  • נעילה פסימיסטית – לקיחת מנעול על משאב (טבלה, רשומה, וכו’) בצורה שתגביל פעולות מקביליות אחרות – אך תמנע בעיות של עקביות-נתונים.
    • מחלקים את הנעילות ל-2 סוגים:
      • נעילה משותפת (Shared lock) עבור פעולות קריאה. אני קורא ערך ואוסר על שינוי הערך – אבל לא יפריע לי שטרנזקציות נוספות יקראו את הערך גם.
      • נעילה בלעדית (Exclusion lock) – לצורך שינוי הערך. אני תופס את המנעול, ולא אתן לשום טרנזקציה אחרת אפילו לקרוא את הערך (כי הוא עומד להשתנות)
  • נעילה אופטימיסטית – מאפשרת לתת לפעולות לרוץ במקביל, לגלות “התנגשויות” ואז להתמודד איתן.
    • לפעמים נרצה להכשיל את אחת הטרנזקציות (או יותר – אם יש כמה). לפעמים נסכים לקבל חוסר אחידות בקוד.
    • למרות שיש עבודה משמעותית נוספת בגילוי וטיפול ב”התנגשויות”, אנו מאפשרים מקביליות בין הפעולות – שזה יתרון שלא יסולא בפז (הציצו שוב בתרשים שלמעלה – כמה צמצום החלקים ה”בלעדיים” הוא חשוב).

מבחינת אלגוריתמים MySQL, בדומה לרוב בסיסי-הנתונים הרלציוניים משתמשים בשני אלגוריתמים עיקריים:

  • 2PL (קיצור של Two-Phase Locking) עבור נעילה פסימיסטית. הרעיון הוא שנחלק את הפעולה לשני שלבים:
    • שלב ראשון – בו ניתן רק לתפוס מנעולים.
    • שלב שני – בו ניתן רק לשחרר מנעולים.
    • לרוב נרצה לתפוס מנעולים ע”פ סדר מסוים (“קודם אובייקט מסוג X ורק אז אובייקט מסוג Y”) – על מנת להימנע מ deadlocks.
      במקרים אחרים, אנו יכולים להחליט לתפוס בסדר לא-קבוע על מנת לצמצם זמני-נעילה ולהגביר מקביליות, במחיר שטרנזקציות יבוטלו לנו. המשמעות: נצטרך לפעמים לנסות להריץ אותן כמה ניסיונות – עד שנצליח לתפוס את המנעולים הרצויים.
  • MVCC (קיצור של Multi-version concurrency control) הרעיון שבו אני “מעתיק” הנתונים שטרנקזציה צריכה הצידה, ואז היא חיה ב״סביבה וירטואלית משלה״, ללא התעסקות ב racing conditions או צורך בנעילות.
    • במימוש של InnoDB, לא באמת מעתיקים נתונים, אלא משתמשים בעמודות טכנית של המערכת לכל טבלה, המנהלת איזה עותק של הנתונים שייך לכל טרנזציה, ואלו ערכים נמחקו.
    • כמובן שיש מחיר שנוסף בניהול “העותק הוירטואלי”. למשל: כאשר טרנזקציה מבקשת ערך שבטיפול של טרנזקציה אחרת – על בסיס הנתונים לבצע הדמיה של rollback של הטרנזקציה האחרת על מנת לדעת אלו ערכים צריכים להיות לטרנזקציה הנוכחית.
      • למרות המחיר הנוסף בפעולות הללו – הוא אינו דורש בלעדיות ולכן scales well.

בעיות חוסר-עקביות

כל טרנזקציה ב MySQL היא, כברירת מחדל, ברמת הפרדה (Isolation Level) שנקראת Repeatable Read.
ישנן 4 רמות הפרדה שהוגדרו ע”י התקן ANSI-SQL 92 ומקובלות בכל בסיסי-הנתונים הרלציוניים המוכרים.

למרות שרמות ההפרדה והתופעות האפשריות[ב] הוגדרו בתקן ה ANSI-SQL – ההתנהגויות בין בסיסי-הנתונים עדיין מעט שונות.
למשל: SQL Server לא מגן בפני Phantom Reads ברמת הפרדה של Repeatable Read, אבל כן מגן בפני Write Skew או Lost Update. אורקל משתמש רק ב MVCC ולא ב 2PL, מה שתורם למקביליות – אבל גם אומר שרמת הפרדה של Serializable לא מגנה בפני Write Skew….

בקיצור: Tradeoffs. Tradeoffs everywhere.

רשימת התופעות הבעייתיות האפשריות:

  • Dirty Write – כאשר שתי טרנזקציות יכולות לשנות את אותו השדה, כך שטרנזקציה אחת משנה את הערך לשנייה.
    • בגלל השימוש ב MVCC (או גישה יותר מחמירה: 2PL) – זה לא יקרה אף פעם בטרנזקציה של MySQL.
  • Dirty Read – הטרנזקציה יכולה לקרוא שינוי של טרנזקציה אחרת שהוא עדיין לא committed. הערך הזה יכול להתבטל (rollback) מאוחר יותר – בעוד הטרנזקציה שלנו משתמשת בו. התוצאה עשויה להיות שנשתמש בערך שאין לו ייצוג תקין בשאר המערכת (למשל: id לרשומה שלא קיימת). לא משהו…
  • Not Repeatable Read (בקיצור: NRR) – הטרנזקציה קוראת ערך משדה x בנקודת זמן t1, וכאשר היא קוראת שוב את השדה הזה בנקודת זמן מאוחרת יותר, t2 – ערך השדה הוא שונה. כלומר: טרנזקציה אחרת עשתה commit (אולי autocommit) – והערך שאנו רואים איננו עקבי. ההתנהגות ה NRR שוברת את תמונת “העולם המבודד” שרצינו ליצור לטרנזקציה שלנו – ובמקרים רבים היא יכולה להיות בעייתית.
  • Phantom Read – הטרנזקציה ביצעה קריאה של תנאי מסוים (נניח: year between 2016 and 2018) וקיבלה סדרה של רשומות, אך בינתיים טרנזקציה אחרת עשתה commit והוסיפה רשומות חדשות לטווח. כלומר: אם ניגש שוב לטווח – התוצאה תהיה שונה.
    • זו וריאציה מורכבת יותר של NRR. בעוד NRR ניתן לפתור בעזרת נעילה המאפשרת קריאות-בלבד מרשומה שאליה ניגשה הטרנזקציה, נעילה של טווח שנובע מפרדיקט היא דבר מסובך למדי – גם עבור בסיס נתונים משוכלל.
  • Read Skew – וריאציה נוספת של NRR: ישנן 2 טבלאות עם קשר לוגי ביניהן. שדה x באחת משפיע או מושפע על שדה y בטבלה השנייה. הטרנזקציה קוראת ערך x מטבלה אחת, אך בינתיים טרנזקציה אחרת משנה את x וגם את y (בצורה עקבית). בסיס הנתונים לא יודע בוודאות על הקשר, וכאשר אנו קוראים את y – עדיין עלולים לקבל את הערך הישן – לפני העדכון של הטרנזקציה השנייה. התסריט הזה מעט מבלבל – הנה דוגמה מפורטת.
  • Write Skew – וריאציה דומה, בה שתי טרנזקציות קוראות את שני הערכים x ו y, ואז אחת מעדכנת את x – בעוד השנייה מעדכנת את y. התוצאה – עקביות הנתונים נשברה. הלינק מלמעלה מספק גם דוגמה מפורטת ל Write skew.
  • Lost update – שתי טרנזקציות קוראות ערך ומעדכנות אותו. אחד העדכונים יאבד – מבלי שנדע שכך אכן קרה. במקרה של counter, למשל – הנזק מוחשי מאוד. גם בשדות המכילים ריבוי פריטים (כמו json) – הנזק הוא ברור. ישנם גם מקרים נוספים בהם התוצאה היא בעייתית.
למרות ההגנה הרבה שהן מספקות, חשוב לנסות ולהימנע מרמת הפרדה של Serializable – היכולה לפגוע משמעותית במקביליות, במיוחד כאשר הטרנזקציות אינן קצרות.

חזרה לתסריטים מתחילת הפוסט

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

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

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

אם מעוניינים ב Atomicity, היכולת של טרנזקציה להתבטל מבלי להשאיר “שאריות” אחריה בפעולת Rollback, אזי רמת ההפרדה של Read Committed עשויה להספיק – ולאפשר מקביליות רבה.

כאשר אנו רוצים להתגונן בפני Racing condition אפשרי (למשל: מתעסקים בדברים רגישים, כמו כסף או פעולות שיש לדייק בהן) – אזי לרוב נרצה להשתמש ברמת הפרדה של Repeatable Read.

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

בשתי הדוגמאות האחרונות, אם אנו יודעים בוודאות שהגישה הקריטית היא רק לרשומה בודדת (למשל: קריאת ערך – ואז עדכון) – אזי Read committed היא רמה מספיק טובה. כל הרמות הגבוהות יותר מגינות מפני תסריטים של ריבוי רשומות / טבלאות.
עם אופטימיזציות כאלו כדאי להיות זהירים: משהו עשוי להשתנות בתוכן הטרנזצקיה, מבלי שמבצע השינוי יזכור / יבין שעליו להעלות את רמת ההפרדה.
מצד שני, יש כמה יתרונות משמעותיים לשימוש ב Read Committed (בקיצור RC) על פני Repeatable Read (בקיצור RR) מבחינת ביצועים:

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

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

עוד שני תסריטים שציינתי ולא כיסינו הם אלו:

שיפור ביצועים – זה מקרה ייחודי – אך שימושי.
InnoDB מחזיק binary log, על כל שינוי שבוצע בינתיים עבור התאוששות מהתרסקות ועבור replications. כדי לשמור על הלוג ככשיר להתאוששות, עליו לבצע flush ללוג (כלומר: לדיסק) על כל טרנזקציה שמתבצעת.
אם אנו מבצעים עשרות או מאות שינויים (למשל: הכנסה של רשומות חדשות), בשל ה autocommit – אנו נחייב את המנוע לבצע עשרות או מאות flushes לדיסק (פעולה יקרה).
שימוש בטרנזקציה (הכי פשוטה) – יאפשר לבסיס הנתונים לבצע flush יחיד.

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

בד”כ נשתמש ברמת Repeatable Read לכאלו שינויים. כבני-אדם, אנו נחזיק את הטרנזקציה פתוחה לנצח (דקות? יותר?) – ולכן כדאי להימנע מ Serialization – במידת האפשר.

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

כיצד עושים “התערבות ידנית”?
אלו מנגנוני-נעילה נוספים, הרצים מ”אחורי-הקלעים”, קיימים ב InnoDB? (למשל: הזכרנו את ה gap lock)
כיצד מאתרים בעיות של נעילות בעייתיות / עודפות?

על כל זה – נדבר בפוסט ההמשך.

שיהיה בהצלחה!

——

[א] החל מחודש ספטמבר, נהוג כבר להתמקד רק בטכנולוגיות השנה הבאה. #שנה_נוכחית_זה_פח

[ב] התקן המקורי של ANSI-SQL 92 זיהה רק 3 תופעות אפשריות של חוסר עקביות בנתונים, אך מאמר שהגיע שלוש שנים אחריו, הציף עוד 4 מקרים בעייתיים נוספים.

[ג] בעצם, בסיסי-נתונים לא רלציוניים היו תמיד: היררכיים (קדמו, והתחרו עם בסיסי-נתונים רלציוניים – תקופה מסוימת), Time series, מבוססי-אובייקטים, מבוססי-XML, גיאוגרפיים, ועוד.
הם פשוט לרוב נשארו נישה, ולא הצליחו לייצר רעש, כמו הגל האחרון.

נושאים מתקדמים ב MySQL: חלק ב’ – Generated columns ו json_each

פוסטים בסדרה:
“תסביר לי” – גרסת ה SQL
לקחת את הביצועים ברצינות, בעזרת MySQL Performance Schema
נושאים מתקדמים ב MySQL: חלק א’ – עבודה עם JSON
נושאים מתקדמים ב MySQL: חלק ב’ – json_each  ו Generated Columns

בפוסט הקודם דיברנו על שימוש ב json column ב MySQL.

אחת המגבלות הגדולות של שדות json הם מהירות החיפוש בהם:

  • אם השדות שמורים כ json – יש לקרוא את ה json כולו מהדיסק לזיכרון – על מנת לשלוף ערך בודד.
  • אם השדות שמורים כ text column – אזי יש עוד עבודת CPU של פענוח מבנה ה json – זמן לא זניח ב jsons גדולים. להזכיר: בעמודה מטיפוס json הם שמורים כ key-value עם גישה מהירה יותר לפי מפתח.
הפתרון האפליקטיבי המתבקש שבו מפתחים שונים נוקטים הוא לאחסן “עותק” של מספר מצומצם של שדות עליהם יש חיפוש – ב columns מקבילים לזה של ה json. ההשפעה על הביצועים – תהיה לרוב דרמטית.

הנה סכמה כזו לדוגמה:

[ Id (guid/auto-inc), json_data (json), field_x (varchar(100)), field_y (int) ]

את השדות x ו y – נרצה לרוב להחזיק גם בתוך ה josn וגם בצד לצורך גישה מהירה / שימוש באינדקסים.
למה שכפול נתונים?
אם נאחסן אותם רק מחוץ ל json – העבודה עם אובייקט ה json תהיה מסורבלת יותר.
לפעמים השדה שאנו מוציאים הוא גם ערך וגם מיקום באובייקט – למשל: המחיר של ההזמנה המאוחרת ביותר (כאשר יש לנו מערך של גרסאות של הזמנות).

את התהליך הנ”ל – ניתן לבצע בצורה פשוטה יותר בעזרת יכולת של MySQL שנקראת generated column, או בעברית “עמודות מחוללות” (מלשון לחולל, ולא מלשון חילול).

נתחיל בדוגמה, איך מגדירים עמודה שכזו:

ALTER TABLE policies
ADD COLUMN state VARCHAR(30) GENERATED ALWAYS
AS (json_unquote(json_extract(`json_data`,‘$.address.state’))) STORED
;

אני מוסיף שערכו יהיה הביטוי (expression) שבסוגריים לאחר המילה השמורה AS.

  • חשוב לי להשתמש ב json_unquote על מנת שהעמודה לא תכיל מירכאות – וכך אוכל לבצע חיפוש יעיל מול האינדקס.
  • הביטוי GENERATED ALWAYS הוא רשות – ועוזר להבליט את העובדה שמדובר ב generated column – עבור קוראים עתידיים.

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

…
WHERE my_state = json_unquote(state)

לא טוב!

השינוי הבא יאפשר שימוש באינדקס (כי את הערך “כפי שהוא” ניתן להשוות בינרית לאינדקס):
…
WHERE json_quote(my_state) = state
אבל הכי טוב זה להכין את האינדקס מראש בצורה הכי “טבעית” לשאילתה שאפשר – וכך לחסוך טעויות מרגיזות.

בגדול יש שני סוגים של generated columns:

  • Virtual – המחושב בזמן הגישה לרשומה.
    • בסיס הנתונים ישמור את הביטוי החישובי (expression) כ metadata על הסכמה, ובכל גישה לרשומה / הפעלת trigger – יתבצע החישוב מחדש. קונספט דומה מאוד ל “calculated field” במערכות BI.
    • אנו חוסכים מקום בדיסק – אבל מכבידים על ה CPU בכל גישה.
    • זהו ערך ברירת המחדל – אם לא ציינתם כלום.
  • Stored – הערכת הביטוי תבוצע בזמן יצירה / עדכון של הרשומה, וישמר לדיסק כמו עמודות אחרות.
    • יש מחיר בנפח אכסון, אך לרוב – הרבה פחות עבודת CPU.
    • זו הגישה הטבעית בעבודה עם json.

לצורך העניין generated column יכול לשמש לצרכים נוספים מלבד json.

למשל:

CREATE TABLE periods
id VARCHAR(32),
start_date_millis INT, # WARN: https://en.wikipedia.org/wiki/Year_2038_problem 
end_date_millis INT,   # WARN: https://en.wikipedia.org/wiki/Year_2038_problem 
start_date_sec AS (state_date_millis * 1000) VIRTUAL
;

יש כמה מגבלות על שימוש ב generated columns שכדאי להכיר. בביטוי של עמודה מחוללת לא ניתן להשתמש ב:

  • פונקציות לא דטרמיניסטיות – כאלו שיציגו ערכים שונים אם הופעלו פעם שניה. למשל: ()now או ()current_user.
  • sub-queries
  • Parameters / Variables או User Defined Functions.
  • עמודת AUTO_GENERATED או תכונת AUTO_INCREMENT.
  • שדה generated יכול להיות מבוסס רק על שדות generated המופיעים לפניו.
  • לא ניתן להשתמש על ה stored generated column באילוצים של FK מסוגי ON_UPDATE / ON_DELETE

עוד פרט מעניין: ניתן להשתמש באינדקס (משני) גם על virtual generated column מעל innoDB. הוא יהיה מסוג BTree בלבד (כלומר: לא FULLTEXT או GIS).

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

קצת יותר על השימוש בפונקציות ה JSON של MySQL

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

רקע: אני רוצה לבצע מיגרציה לשם של אלמנט בתוך מבנה json שאנו עושים בו שימוש. שם האלמנט היום הוא breakdown אבל השם לא כ”כ נכון – ואני רוצה לשנות אותו ל amounts. הנה דוגמה פשוטה למבנה ה json הקיים לפני השינוי:

[{“policyId”:”policy_id”,”lob”:”GL”,”breakdown”:{“PREMIUM”:{“amount”:423.17}}}]

וכך אני רוצה שמבנה יראה לאחר השינוי:

[{“policyId”:”policy_id”,”lob”:”GL”,”amounts“:{“PREMIUM”:{“amount”:423.17}}}]
איך לעזאזל ניגשים למיגרציה “עמוקה” בתוך שדה json? האם זה חייב להיות פרויקט מורכב?
ברור שלא.
אז הנה אני אעשה את השינוי בכמה שלבים פשוטים:
ראשית, אני רוצה ליצור שדות כפולים: גם breakdown וגם amounts – כי באופן טבעי בזמן deployment ירוצו 2 גרסאות של הקוד (גרסה חדשה שמחפשת אחר amounts וגרסה ישנה שמחפשת אחר breakdown). אני צריך להתכונן לכפילות הזו.
אני דואג לכך שבזמן ה deploy (כמה דקות) – לא יווצרו רשומות חדשות (במקרים אחרים ניתן למצוא אותן בעזרת זמן יצירה, ולתקן ידנית אח״כ).
בניית שאילתה של SQL היא עניין של ניסוי וטעיה, והדבר הזה נכון גם בעבודה עם  json.

אני מתחיל בשאילתה פשוטה של SELECT על מנת לראות אם קלעתי נכון ב query.
בחרתי לי רשומה שהמפתח שלה הוא ‘009277a371b8c3def40996a754085030’ על מנת לבצע את הניסויים ב scale קטן.

SELECT Json_insert(financial_attribution, ‘$[0].foo’, 2)
FROM   `payments`
WHERE  id = ‘009277a371b8c3def40996a754085030’;
במבט ראשון זה קצת מבלבל שאני משתמש ב ()JSON_INSERT בפקודת SELECT.
מה קורה כאן?
אני שולף שדה json בשם `financial_attribution` ואז מבצע עליו מניפולציה בזיכרון. המניפולציה שבחרתי היא הכנסה של ערך. מפתח מוזר בשם foo עם ערך של 2. רק לצורך הבדיקה.
הנה התוצאה:
[{“foo”: 2, “lob”: “GL”, “policyId”: “policy_id”, “breakdown”: {“PREMIUM”: {“amount”: 423.17}}}]
השדה נוסף בהצלחה (לי לקח ניסיון או שניים עד שזה עבד) – אך שום מידע לא השתנה בבסיס הנתונים.
עכשיו ננסה משהו יותר אמיתי:
SELECT Json_insert(financial_attribution, ‘$[0].amounts’,                    financial_attribution -> ‘$[0].breakdown’)
FROM   `payments`
WHERE  id = ‘009277a371b8c3def40996a754085030’;
לקחתי מתוך ה json את אובייקט ה breakdown – והכנסתי אותו בחזרה כ amounts:
[{“lob”: “GL”, “amounts”: {“PREMIUM”: {“amount”: 423.17}}, “policyId”: “policy_id”, “breakdown”: {“PREMIUM”: {“amount”: 423.17}}}]
אחרי שאני רואה שזה מצליח אני יכול להסיר את תנאי ה where ולראות את התוצאה על מגוון ערכים. מוודא שהכל תקין.
אנסה בצד דוגמה שגם שאילתת ה update תקינה:
UPDATE `payments`
SET    `financial_attribution` = Json_insert(`financial_attribution`, ‘$[0].amounts’,
financial_attribution -> ‘$[0].breakdown’)
;
ואז הוסיף אותה ל db migrations של השינוי.
אחרי שכל המיגרציה הסתיימה (בהצלחה), אני יכול למחוק את אובייקט ה breakdown מתוך ה json בעזרת פקודת  ()JSON_REMOVE. 
אפשרות אחרת היא פשוט להשאיר את הנתונים העודפים – אם הם לא מפריעים לנו.

עוד דרך מהירה (ולעתים יותר יעילה) לבצע תיקונים ב jsons היא בעזרת ()REPLACE פשוט על טקסט – אבל חשוב מאוד להיזהר מהחלפות לא-צפויות.

השלמות ליכולות ה json של MySQL 5.7

אם אתם זוכרים את הפוסט הקודם – הבטחתי לכם workarounds למחסור של MySQL 5.7 ב-2 פונקציות שימושיות בעבודה עם json.

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

קריאת הערך האחרון במערך שבתוך json

אני רוצה לקבל את השדה האחרון במערך שבתוך json:

SELECT JSON_EXTRACT(`from`,CONCAT(“$[“,JSON_LENGTH(`from`)-1,”]”)) FROM `table`;

זה קצת תרגיל: אני מוצא את אורך המערך (במקרה שלנו, תחת תכונה בשם from) ואז מרכיב שאילתה בעזרת ()CONCAT – ומריץ אתה. הנה קלט לדוגמה שעליו תעבוד השאילתה:

{ from: [“a”,”b”,”c”], “to”: [ “d”, “e” ] }

התוצאה תהיה ״c״.

הנה דוגמה ב DB Fiddle שאפשר קצת ״לשחק״ איתה:

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

גרסה מאולתרת ל json_each – טיפול בכל איבר במערך שבתוך json

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

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

select  n,
JSON_EXTRACT(`data`, concat(‘$.items[‘, n – 1, ‘].price’)) as price
from `my_table`
join numbers on (n <= JSON_LENGTH(`data`->’$.items’) and n > 0)
;

כאשר התוצאה נראית כך:

n הוא מספר האיבר ברשימה (אינדקס על בסיס 1), ו price הוא… המחיר.

והנתונים נראים כך:

עשינו join עם טבלה של מספרים (0-255 לרוב מספיקה) ואז, עבור כל מספר באורך המערך של ה items – ביצענו פעולת שליפה מתוך ה json על המקום הזה, בהתבסס על ״תרגיל״ ה CONCAT שהשתמשנו בו קודם לכן.

הכי-אלגנטי-בעולם? – לא.
עובד ושימושי – כן!

הנה אתם יכולים לשחק ב fiddle שיצרתי לקוד הזה: https://www.db-fiddle.com/f/dmA8af4CHJ3xkx4fzV99Zw/0

בוודאי שמתם לב למבנה קצת לא-שגרתי, שנועד בכדי ליצור את ה View המספק את המספרים. גם כאן יש תרגיל ״מוכר״ בעולם של MySQL:

CREATE OR REPLACE VIEW generator_16
AS SELECT 0 n UNION ALL SELECT 1  UNION ALL SELECT 2  UNION ALL
SELECT 3   UNION ALL SELECT 4  UNION ALL SELECT 5  UNION ALL
SELECT 6   UNION ALL SELECT 7  UNION ALL SELECT 8  UNION ALL
SELECT 9   UNION ALL SELECT 10 UNION ALL SELECT 11 UNION ALL
SELECT 12  UNION ALL SELECT 13 UNION ALL SELECT 14 UNION ALL
SELECT 15
;
CREATE OR REPLACE VIEW numbers
AS SELECT ( hi.n * 16 + lo.n ) AS n
FROM generator_16 lo, generator_16 hi
;

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

אפשר למצוא את Fiddle ממקוד של ה generator על מנת ״לשחק״ איתו: https://www.db-fiddle.com/f/jCRetSiTaKqz5SUiQQG8Py/0

סיכום

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

שיהיה בהצלחה!

גם “Data Science בשקל” – יכול להיות שווה הרבה! (על Tableau)

נתונים של מערכת הם לרוב משאב שלא מוצה.

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

למשל:

  • למצוא קשרים לא-צפויים בין נתונים, למשל: הידע שכרטיסי אשראי עם מאפיינים מסוימים אחראים לפי-19 הונאות משימוש בכרטיסים אחרים – הוא ידע שניתן להפוך אותו ליתרון עסקי.
  • היכולת לזהות שתקלה או מצב עומד להתרחש בעזרת סדרה של נתונים מקדימים.
  • היכולת לזהות שמקרה קצה מסוים לא מתרחש או כמעט ולא מתרחש – הוא הזדמנות לקחת החלטה במערכת ולא לתמוך במקרה הזה / לבצע אופטימיזציה עסקית או של ביצועי המערכת.
הדרך להשיג ידע שכזה היא לא קלה, ולרבות מההצלחות להשיג תובנה משמעותית – קודמים כמות ניסיונות כושלים.
בעקבות הטרנדים החמים היום של “Big Data” ושל “AI/ML” – מפתחים רבים מחליטים להשקיע ולהעשיר את הידע שלהם בכיוונים של Data Science.
לפעמים זה ידע תאורטי, לפעמים זו התנסות בסיסית ביצירת רשת ניורונים או Random forest.
בעזרת הטכנולוגיות הללו, נעשים בעולם דברים מדהימים – ואותם אנשי-תוכנה מקווים להגיע להישגים באזורים שלהם.
אני חושב שזו טעות טקטית נפוצה:

  • Data Science, בעיקרML ובמיוחד Deep Learning – הם תחומים עם עקומת למידה תלולה למדי, עדיין.
    • איש תוכנה יכול להשקיע עשרות ומאות שעות בלמידה ופיתוח skill – שעדיין יהיה בסיסי מאוד, יחסית למי שעוסק בתחום במשרה מלאה. לא יהיה לאיש התוכנה יתרון יחסי ברור מול איש-מקצוע ב Data Science, במיוחד לא כזה עם ניסיון לא-מבוטל.
    • אני מעריך שככל שהזמן יעבור – יהיה קל יותר ללמוד וליישם פתרונות Data Science, כך שייתכן ש 100 שעות למידה היום – יהפכו ל 20 שעות למידה בעוד 5 שנים. חלקים רבים מהידע שנלמד היום – יהפכו לכמעט-לא-חשובים, עבור מגוון נפוץ של יישומים / בעיות.
  • דווקא שיטות “פחות-מתוחכמות” של Data Science עשויות להניב לאיש התוכנה יתרון יחסי: שיטות כגון שאילתות SQL, סקריפטים שמעבדים ומנתחים נתונים, או כלי ויזואליזציה.
    • התחומים / שיטות הללו מפותחים כבר מאוד – קל ללמוד אותם מהר, ויש מגוון רחב מאוד של כלים ופרקטיקות שתומכים בהם.
    • יש כאן יתרון יחסי ברור של איש תוכנה המכיר את המערכת מקרוב:
      • הוא מבין את הנתונים (או לפחות חלקים מהם) – בצורה עמוקה.
      • נתון שאינו מכיר – הוא יכול למצוא את הקוד וללמוד בדיוק כיצד הוא מתנהג.
      • הוא יכול להוסיף נתונים ולטייב נתונים, ולהבין בצורה מהירה מה המורכבות של שיפור / טיוב נתונים שכאלו.
        • מה הבעיה ללכת לקוד ולבצע עוד כמה בדיקות / להזיז מקום את הקוד שאוסף נתונים – כך שיהיה מדויק יותר? – לאיש Data Science זוהי מלאכה קשה מאוד.
ארצה להציג דוגמה לשימוש בכלי Data Science “פשוט”, שאינו קשור ללמידת מכונה או “Big Data”. ספציפית, אסקור כלי בשם Tableau שאני משתמש בו לאחרונה.
Workbook לדוגמה מ Tableau Public
מקור: https://public.tableau.com/en-us/s/gallery/books-made-movies

למה טאבלו (Tableau)?

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

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

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

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

כלים דיי ידועים הם MyDBR או Redash (הישראלי / של יוצא GetTaxi) – שהם טובים ופשוטים, וקל מאוד להתחיל לעבוד איתם בזמן קצר.

אני אכתוב על Tableau שהוא “כלי BI”, כלומר שהוא יקר יותר (תשלום ע”פ מספר משתמשים, 30-70 דולר בחודש למשתמש), וההטמעה שלו היא מורכבת יותר.

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

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

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

לטאבלו יש כמה גרסאות, אך אני רוצה לציין את החשובות שבהן:

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

ב Tableau Public אתם יכולים להגביל את הגישה של משתמשים אחרים לנתונים / לקוד המקור (ה Workbook) – אם כי משתמשים רבים מתירים את ההורדה.

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

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

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

Undo הוא חברכם הטוב 

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

להבין את ההבדל בין Measures ל Dimension

זה קצת מבלבל בהתחלה:

  • Measure הוא נתון שאנו רוצים להציג אותו, או בד”כ – Aggregation שלו (כמו SUM, AVG, אחוזון מסוים, וכו’). מכירות, אחוז הצלחה, וזמן ריצה – הם measures קלאסיים.
  • Dimension הוא נתון שלפיו אנחנו רוצים לפלח את הנתונים ולהציג אותם כשורה / עמודה / אזור בגרף.
    למשל: תאריך (שנה / חודש / יום), מיקום גאוגרפי, קטגוריה, סטטוס (הצלחה / כישלון) וכו’ – הם dimension קלאסיים.
איך יודעים מה זה מה? – זה לא כ”כ חד משמעי.

טאבלו ינחש בצורה “תבונית” מה הוא מה – ויקטלג לכם אותם ב Data pane:
טאבלו יטעה מדי פעם – ויהיה עליכם לתקן אותו, ע”י פעולות ה”Convert to Measure” או “Convert to Dimension”.
Measures יהיו בד”כ מספרים – עליהם באמת אפשר לבצע Aggregations.
Dimension יהיו בד”כ מחרוזות ותאריכים.
אבל מה אם אני רוצה להשתמש בנתון מספרי טהור, כמו זמן ריצה – כ dimension? לדומה להציג תהליכים מהירים ואטיים בנפרד?
במקרה הזה עליכם ליצור Bins (בתפריט של פריט המידע: …create/bins), שהוא בעצם שדה חדש המקטלג טווחים של הנתון (הרציף, עם אינספור ערכים) שבחרתם.
בטאבלו, כחצי מהזמן שמשקיעים בויזואליזציה יהיה בארגון הנתונים בצורה שטאבלו ידע לעבוד איתם בצורה נכונה. זה תהליך של ניסוי-וטעיה, שמתרחש תוך כדי בניית הויזואליזציה.

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

להבין את ההבדל בין Columns, Rows, ל Marks

גם זה מאוד בסיסי, אם כי מעט מבלבל בהתחלה.

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

באופן הזה מאוד קל לחשוב על טורים ועמודות כמימדים שונים בהם אתם עושים חישוב – ו marks – כנתונים (measures) שאותם נרצה להציג:

טור ושורה הם שני מימדים שמאוד קל להבין במבנה של טבלה.
הרבה פעמים נרצה 3 ויותר מימדים.

הנה הוספתי לטבלה הנ”ל עוד שני מימדים נוספים:

ההפרדה בין עמודות ושורות היא פחות חשובה, ההפרדה החשובה היא בין מימדים ל Marks, שלרוב יהיו גם מימדים ו measures.

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

Customer הוא אחד ה Segments, ומתחתיו ניתן לראות את השנים.

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

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

הנה אותה הטבלה בדיוק, כאשר אני מציג כ 3 measures כ marks שונים:

  • סכום העסקאות – כגדול הסימון (עיגול).
  • מספר העסקאות – כטקסט (label). הביטוי CNTD הוא קיצור של Count Distinct.
  • אחוז הרווח – כצבע (gradient), כאשר כחול הוא רווח, וכתום הוא הפסד. כתום גדול = הפסד גדול!

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

עוד Marks שניתן להשתמש בהם הוא סוג הצורה (בשימוש ב Shapes) או tool-tip שמופיע ב popup כאשר מצביעים על האיבר.

Show Me הוא כלי חשוב – לא רק למתחילים!

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

בפינה הימנית עליונה נמצא כפתור Show Me – שעוזר לי לדעת למה אני זקוק.
עבור היסטוגרמה, אומרים לספק measure אחד (קל!) – ומודיעים לי שטאבלו ייצור bin field מתאים. יש גם אזהרה שלא כל measure יעבוד.

אני זורק את ה measure של Sales לאחד המימדים (עמודות או שורות – לא משנה) – טאבלו אוטומטית מנחש שאני רוצה לעשות לו aggregation של Sum.
אח”כ אני לוחץ ב Show Me על גרף ההיסטוגרמה – ומקבל את התוצאה הבאה:

הערה: שיניתי את השנתות של ציר ה Y ל logarithmic scale – אחרת היה קשה להבחין בערכים השונים.

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

מה הצבעים ירוק וכחול אומרים?

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

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

למשל: אם ציר ה X שלכם הוא חודש בשנה, ומופיעות שנתות לערכים 0 ו 13 – זה בגלל שהתאריך הוא שדה רציף. הפכו אותו לבדיד – וזה יסתדר.

השתמשו ב Calculated Fields

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

למשל, הנה סקריפט פשוט שיוצר מימד חדש מכמה שדות (שמותיהם – בכתום):

היה לי קשה יותר לבנות את המימד הזה בדרך אחרת / SQL query.

לטאבלו יש רשימה של פונקציות זמינות, ותחביר שמזכיר כתיבת פונקציות ב SQL – בהם ניתן להשתמש ב calculated fields. שימו לב שחלק מהפונקציות זמין רק מול data sources ספציפיים כמו Hive או Google BigQuery (המספקים את הפונקציות בצורה טבעית)

עבור חישובים מורכבים יותר אפשר להשתמש בשפת R – שפת תכנות לכל דבר ועניין.
כדי לכתוב Calculated Fields בשפת R יש להתקין מנוע חישובי בשם RServe המריץ את R. טאבלו ישלח את הנתונים ל RServe – שיבצע את החישוב של השדה הנתון – ויחזיר את התוצאות.

SCRIPT_STR(`substr(.arg1, regexpr(” “, .arg1) -1 )`, ATTR([Business Name ]))

הפונקציה SCRIPT_STR שולחת ל R ביטוי בשפה העטוף במירכאות + פריט המידע שאותו יש לעבד – ומחזירה תשובה מסוג מחרוזת. האינטגרציה היא סבירה – אבל לא מצוינת. למשל: איתור תקלות היה יכול להיות פשוט בהרבה.

השתמשו ב Filters

בכל ויזואליזציה – ניתן “לגרור” שדות לאזור ה “filters” ולסנן לפי ערכים מסוימים של השדה הזה. זה שימוש אחד ויעיל ל Filters.

שימוש נוסף חשוב הוא ב Dashboards, כאשר אני מצרף כמה ויזואליזציות ובמקום אחד יכול לפלטר את כולם באותה הצורה. מה שנחמד שה Filters ב Dashboard מופיעים ב View Mode (אם לא סילקנו אותם משם) – וכך הקהל הרחב של המשתמשים יכול להשתמש בהם, מבלי להכנס לעומק ה Data Model.

הנה דוגמה של Dashboard פשוט שיצרתי מ-2 הויזואליזציות הנ”ל:

הוספתי Filter דרך אחד מהויזואליזציות (תפריט = חץ למטה/filters – מציג לי את כל המימדים / measures שבשימוש ולכן ניתן לפלטר לפיהם).

בשלב הבא, אני משייך את הפילטר (דרך התפריט שלו) – לכל הנתונים על ה Dashboard:

עכשיו אפשר לראות שצמצום השנים בעזרת הפילטר – משפיע על כל הנתונים ב Dashboard. איזה יופי!

סיכום

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

הוא כלי רב-עוצמה, אך לא מורכב כ”כ לשימוש.

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

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

שיהיה בהצלחה!

לקחת את הביצועים ברצינות, בעזרת MySQL Performance Schema

פוסטים בסדרה:
“תסביר לי” – גרסת ה SQL
לקחת את הביצועים ברצינות, בעזרת MySQL Performance Schema
נושאים מתקדמים ב MySQL: חלק א’ – עבודה עם JSON
נושאים מתקדמים ב MySQL: חלק ב’ – json_each  ו Generated Columns

—-

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

איך יודעים אלו שאילתות רצות לאט?

הדרך הנפוצה ביותר היא בעזרת ה slow-log.

MySQL slow Query Log

  • בכדי להפעיל אותו יש לקבוע 1 בפרמטר slow_query_log. הוא ירשום כל שאילתה שארכה יותר מ 10 שניות.
  • ניתן לקבוע סף אחר בעזרת הפרמטר long_query_time, בו ניתן לקבוע גם ערכים שאינם מספרים שלמים, למשל “0.6”.
    • אם תקבעו בפרמטר ערך של 0 – סביר להניח שכתיבה ללוג תהיה החלק האטי במערכת שלכם 😀.
    • שינוי הפרמטר ישפיע רק על connections חדשים ל DB, כך שאם שיניתם את הערך ושרת סורר מחזיק חיבורים פתוחים בעזרת ה connection pool שלו, שאילתות שעמודות בסף החדש שנקבע – לא ירשמו ללוג.
  • בכדי לשלוף ערכים מה slow log פשוט בצעו שאילתה על הטבלה mysql.slow_log.
    • למשל: SELECT * FROM mysql.slow_log ORDER BY start_time DESC limit 50;
    • אני לא זוכר מה הם ערכי ברירת המחדל, ייתכן וצריך לשנות עוד קונפיגורציה בכדי שהלוג ייכתב לטבלה.

התוצאה נראית כך:

  • user_host (הסתרתי את כתובות ה IP) – עוזר להבין איזה צרכן יצר את השאילתה. זה חשוב ב DB מונולטיים – המשרתים צרכנים רבים ומגוונים.
  • lock_time (בשניות) – הזמן בו ממתינים ל”תפיסת מנעול” על מנת להתחיל ולהריץ את השאילתה. למשל: הטבלה נעולה ע”י פעולה אחרת.
  • query_time (בשניות) – זמן הריצה הכולל.
    • הרצה עוקבת של אותה שאילתה אמורה להיות מהירה יותר – כאשר הנתונים טעונים כבר ל buffer pool.
    • כמובן שגם השפעות חיצוניות שונות (שאילתות אחרות שרצות ברקע ומתחרות על משאבי ה DB Server) – ישפיעו על זמני הריצה. אני לא מכיר דרך פשוטה להוסיף מדדים כמו cpu ו/או disk_queue_depth לשאילתה, בכדי לקשר את זמני הביצוע שלה למה שהתרחש ב DB Server באותו הרגע.
  • מספר שורות שנשלח / נסרק – בשונה מפקודת ה Explain, זהו המספר בפועל ולא הערכה.
    • ייתכנו מקרים בהם Explain יעריך תוכנית אחת – אך ה slow log יראה שבוצע משהו אחר (יקר יותר) בפועל. זה עלול לקרות.
  • db – שם בסיס הנתונים (= סכמה) שבה בוצעה השאילתה.
    • למשל, אני יכול לבחור לפלטר את הסכמה של redash – כי רצות שהם הרבה שאילתות אטיות, וזה בסדר מבחינתי.

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

ה Performance Schema

אפשרות מודרנית יותר לאתר שאילתות אטיות היא על בסיס ה Performance Schema (בקיצור: ps)

מאז MySQL 5.6 ה ps מופעלת כברירת מחדל, אבל אם אתם רצים על AWS RDS – יהיה עליכם להדליק אותה ולבצע restart לשרת, לפני שתוכלו להשתמש בה.

השאילתה ה”קצרה” לשלוף נתונים של שאילתות אטיות מה ps היא:

SELECT * FROM sys.statements_with_runtimes_in_95th_percentile;

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

אתם שמים לב, בוודאי, שאנו קוראים ל system_schema  (השימוש ב FROM sys) ולא ל ps (השימוש ב FROM performance_schema).
ה ps חושפת כמות אדירה של נתונים (+ כמות גדולה של קונפיגורציה מה לאסוף ובאיזו רזולוציה) – מה שמקשה על השימוש בה עבור רוב המשתמשים.
ה system schema משמשת כשכבת הפשטה המציגה רשימה של views המרכזים מידע מתוך ה ps (וכמה מקומות אחרים) – ומנגישים את המידע המורכב למשתמש הפשוט.

נ.ב. –  על גרסה שהותקנה כ 5.7 ה system schema מגיעה כברירת-מחדל. על גרסאות ישנות יותר – ייתכן ותצטרכו להתקין אותה.

הנה תוצאה לדוגמה:

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

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

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

את המידע הזה אני שולף בעזרת השאילתה הבאה:

SELECT `schema_name` AS db,
digest_text AS query,
IF (( ( `stmts`.`sum_no_good_index_used` > 0 )
OR ( `stmts`.`sum_no_index_used` > 0 ) ), ‘*’, ”) AS `full_scan`,
Format(count_star, 0) AS events_count,
sys.Format_time(sum_timer_wait) AS total_latency,
sys.Format_time(avg_timer_wait) AS avg_latency,
sys.Format_time(max_timer_wait) AS max_latency,
Format(sum_rows_examined, 0) AS rows_scanned_sum,
Format(Round(Ifnull(( `stmts`.`sum_rows_examined` /
Nullif(`stmts`.`count_star`, 0) ), 0), 0), 0) AS `rows_scanned_avg`,
Format(Round(Ifnull(( `stmts`.`sum_rows_sent` /
Nullif(`stmts`.`count_star`, 0) ), 0), 0), 0) AS `rows_sent_avg`,
Format(sum_no_index_used, 0) AS rows_no_index_sum,
last_seen,
digest
FROM   performance_schema.events_statements_summary_by_digest AS `stmts`
WHERE  last_seen > ( Curdate() – INTERVAL 15 day )
ORDER  BY sum_timer_wait DESC;
השאילתה הזו ממיינת את התוצאה ע”פ sum_timer_wait, כלומר – זמן ההמתנה הכולל לכל המופעים של אותה השאילתה.
השאילתה הנ״ל תשמיט מהתוצאה שאילתות שלא נראו מופעים שלהן ב 15 הימים האחרונים. מה שנפתר / השתנה – כבר לא מעניין.

ניתוח ריצה של שאילתות

שלב הבא אחרי איתור שאילתה בעייתית הוא הרצה שלה, עם Explain וכלים נוספים.

כלי שימושי במיוחד של ה ps הוא הפרוצדורה trace_statement_digest. אנו מעתיקים את תוצאת ה digest (זיהוי ייחודי לדפוס של query) מתוך העמודה האחרונה של השאילתה הנ״ל, ומעתיקים אותה לתוך הביטוי הבא:

CALL sys.ps_trace_statement_digest(‘1149…405b’, 60, 0.1, TRUE, TRUE);

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

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

אם אין לכם הרשאות אדמין, הנה כלים נוספים שניתן להפעיל:

ראשית יש את ה optimizer trace שניתן להפעיל על שאילתה בודדת.

— Turn tracing on (it’s off by default):
SET optimizer_trace=“enabled=on”;

SELECT …; — your query here
SELECT * FROM information_schema.optimizer_trace;

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

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

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

SET optimizer_trace=”enabled=off”;

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

SET profiling = 1;             — turn on profiling
SELECT …;                    — your query here
SHOW profiles;                 — list profiled queries
SHOW profile cpu FOR query 12; — place the proper query_id

בדרך ל profile הנכסף עליכם להפעיל את איסוף ה profiling, לבצע את השאילתה, לראות את השאילתות שנאספו – ולבחור את המספר השאילתה שלכם. התוצאה נראית כך:

“מה?? כמעט 5 שניות על שליחת נתונים בחזרה ללקוח? – אני מתקשר להוט!”

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

בד”כ ה Sending Data יהיה החלק הארי, ודווקא כאשר סעיף אחר תופס נפח משמעותי – יש משהו חדש ללמוד מזה.

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

לבסוף, אל תשכחו לסגור את איסוף ה profiling:

SET profiling = 0;

עוד שאילתות שימושיות מתוך ה System Schema

הזכרנו את ה system schema, העוטפת את ה performance schema (ועוד קצת) בצורה נוחה יותר.

שווה להזכיר כמה שאילתות שימושיות:

SELECT * FROM sys.version;

הצגת גרסה מדויקת של שרת בסיס הנתונים, מתוך ממשק ה SQL.

SELECT * FROM sys.schema_tables_with_full_table_scans;

הצגה של רשימת הטבלאות שסרקו בהן את כל הרשמות כחלק משאילתה.

SELECT * FROM sys.schema_table_statistics;
SELECT * FROM sys.schema_index_statistics;

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

SELECT * FROM sys.schema_unused_indexes;
SELECT * FROM sys.schema_redundant_indexes;

דרך טובה למצוא אינדקסים לא נחוצים.
אינדקסים מיותרים גורמים להכנסות (insert/update) להיות אטיות יותר – כי צריך לעדכן גם את האינדקס.

SELECT * FROM sys.statements_with_errors_or_warnings;
SELECT * FROM sys.statements_with_temp_tables;

עוד נתונים שכדאי להסתכל עליהם מדי-פעם.

SELECT * FROM sys.io_global_by_wait_by_latency;
SELECT * FROM sys.io_global_by_file_by_latency;

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

בסיס נתונים מונוליטי

אתם בוודאי מכירים את המצב בו שרת בסיס נתונים אחד מריץ loads שונים ושונים של עבודה.
בד”כ זה יהיה בסיס נתונים של מערכת מונוליטית, שמשמש גם לעבודות BI כאלו או אחרות (הוא מונוליטי, וכל הנתונים נמצאים בו – למה להשתמש במערכת אחרת?)

בבסיס נתונים מונוליטי יש מגוון רחב של workloads שרצים במקביל. כאשר בסיס הנתונים מתחיל להתנהג בצורה לא יפה (הרבה CPU, וגמגומים) – אנחנו מנסים לאתר מי מכל המשתמשים הרבים והמגוונים של בסיס הנתונים יוצר את העומס החריג. הרבה פעמים נוכל להרוג תהליך אחד (בשאיפה: לא mission critical) – ולייצב את שרת בסיס הנתונים.

כלי העבודה הבסיסי הוא השאילתה הזו:

SELECT * FROM sys.session ORDER BY command;

היא דרך טובה לדעת מי עושה מה. זוהי “גרסה משופרת” של השאילתה SHOW PROCESSLIST.
כעת נוכל לראות אלו משתמשים גורמים לעומס הרב ביותר / עומס חריג – ונתחיל לנטר אותם:

SELECT * FROM sys.user_summary;

היא שאילתה שתספק בזריזות כמה ואיזה סוג יוצר כל משתמש לבסיס הנתונים (שורה לכל משתמש). אפשר להשוות את הנתון הזה ל session data בכדי לזהות מי מתנהג בצורה חריגה כרגע.
באופן דומה, השאילתות הבאות יספקו לנו קצת יותר drill down:

SELECT * FROM sys.user_summary_by_statement_type;
SELECT * FROM sys.user_summary_by_statement_latency;
SELECT * FROM performance_schema.user_variables_by_thread;
מכאן ייתכן ונרצה לפרק את העבודה לרמת ה thread של בסיס הנתונים. אולי שרת ספציפי של האפליקציה גורם לעומס החריג?

SELECT * FROM sys.io_by_thread_by_latency;

לא מוצאים את השאילתה?

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

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

SET global general_log = ‘ON’;
SELECT …; — your query here

SELECT *
FROM mysql.general_log
WHERE argument LIKE ‘/*%’
AND event_time >= ( Curdate() – INTERVAL 5 minute )
ORDER BY event_time DESC
LIMIT 50;
SET global general_log = ‘OFF’;

ה General Log יותר overhead כבד למדי על בסיס הנתונים, ולכן אם מדובר בסביבת production – אני מפעיל אותו לזמן קצר בלבד (בעזרת ‘general_log = ‘ON/OFF). על סביבת הפיתוח אני יכול לעבוד כשהוא דלוק כל הזמן.

נ.ב. גם כאן, ייתכן ותצטרכו לכוון את כתיבת הנתונים לטבלה בעזרת:

SET global log_output = ‘FILE,TABLE’;

מכיוון שה framework שאני עובד איתו, JDBI, מוסיף לשאילות מזהה המופיע כהערה בתחילת השאילתה “/* comment */” – הוספתי תנאי המסנן את הלוג לשאילות בהם מופיעה הערה (להלן …argument LIKE).

שיהיה בהצלחה!

"תסביר לי" – גרסת ה SQL

פוסטים בסדרה:
“תסביר לי” – גרסת ה SQL
לקחת את הביצועים ברצינות, בעזרת MySQL Performance Schema
נושאים מתקדמים ב MySQL: חלק א’ – עבודה עם JSON
נושאים מתקדמים ב MySQL: חלק ב’ – json_each  ו Generated Columns

יש מי שאמר:

“EXPLAIN היא הפקודה החשובה ביותר ב SQL, אחרי הפקודה SELECT”

בבסיס נתונים אנחנו רוצים:

  • לשמור נתונים (לאורך זמן, בצורה אמינה)
  • לשלוף נתונים / לבצע שאילתות.

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

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

אפשר להחליט שזהו. “בואי שרהל’ה, אנחנו אורזים ועוברים – ל Cassandra”.
מי אמר שעל קסנדרה יהיה טוב יותר?
כבר נתקלתי במקרים בהם גילוי שאילתה אטית לווה מיד בקריאות “ה DB איטי – בואו נעבור ל !” – עוד לפני שבוצע ניתוח בסיסי. זו כנראה הגרסה המודרנית לקריאה “!A Witch! – burn her“.

נתקלתי פעם בפרויקט שביצע מעבר ל Cassandra לאורך לשנה – ורק אז הסיק ש Cassandra היא no-go לצרכים שלו (הם היו זקוקים Graph Database… זו הייתה טעות בסיסית בזיהוי).

בפוסט הזה אנסה לעבור על כמה כלים חשובים לניתוח בעיות הביצועים של בסיס נתונים רלציוני (אתמקד ב MySQL 5.7 המאוד-נפוץ). עם כל הכבוד ל NoSQL – רוב המערכות עדיין עובדות (גם) עם בסיס-נתונים רלציוני.
אני לא טוען ש DB רלציוני הוא (בהכרח, תמיד) הדרך הנכונה לנהל נתונים – כתבתי כמה פוסטים שהראו שלפעמים זה אחרת, אבל לבסיס נתונים רלציוני יש כמה יתרונות חשובים, וחבל לוותר עליהם עם קושי ראשון.

 

דוגמה בסיסית

אני מריץ את השאילתה הבאה, ואינני מרוצה מזמני הביצוע שלה.

SELECT `value` 
FROM `quote_job_execution_internals` 
WHERE `quote_job_id` = ( 
  SELECT `quote_job_id` 
  FROM `quote_job_execution_internals` 
  WHERE `key` = ‘some key’ AND `value` = ‘1290268’ 
) AND `key` = ‘other key’ 
;

מה אני יכול לעשות?

שניה!
בואו נתחיל בבקשה מבסיס הנתונים להסביר כיצד הוא מתכנן להריץ את השאילתה (להלן ה query plan):

EXPLAIN SELECT `value` 
FROM `quote…

התוצאה תראה משהו כזה:

 

אמנם זהו Query אחד – אך תוצאת ה Explain מציגה שורה עבור כל Select (בדוגמה שלנו: שניים).

ה Quickwin בטיפול ב Explain נמצא בשתי עמודות: key ו type

    • key – באיזה אינדקס נעשה שימוש בכדי להגיע לנתונים. לרוב נרצה שיהיה שימוש באינדקס.
      • נשאל: האם האינדקס מתאים?
        • אולי חסר אינדקס?
        • אולי תנאי ה WHERE מכיל 2 עמודות – אך האינדקס מכסה רק אחת מהן? (ואז כדאי לשנות אינדקס / להוסיף עמודות לאינדקס).
      • לפעמים יש מקרים “לא צפויים” בהם האומפטימייזר בוחר לא להשתמש באינדקס. 
          למשל: התנאי WHERE ref_number = 1023 לא גרם לשימוש באינדקס כי העמודה ref_number היא מסוג varchar. השאילתה תחזיר תשובה נכונה – אבל האופטימייזר מבצע השוואת טיפוסים ומפספס שיש אינדקס מתאים. שינוי התנאי ל
        • ‘WHERE ref_number = ‘1023 – יחולל שינוי דרמטי בזמני הריצה.
      • דוגמה נוספת:  ‘WHERE Lower(some_column) = ‘some_value…
        – לא ישתמש באינדקס על some_column (כי המידע לא מאונדקס ב lower_case) בעוד:(‘WHERE  some_column = Upper(‘some_value
        – דווקא כן. 
  • type – כיצד טענו את הנתונים מהטבלה? גם כשיש שימוש באינדקס, יש הבדל אם סורקים את כל האינדקס, רשומה אחר רשומה, או מצליחים להגיע לערכים הנכונים באינדקס מהר יותר.
    • בקירוב, אלו הערכים העיקריים שנראה, ממוינים מה”טוב” ל”רע”:
      • system / const – יש עמודה אחת רלוונטית לשליפה (למשל: טבלה עם רשומה בודדת).
      • eq_ref – יש תנאי ב WHERE המבטיח לנו איבר יחיד באינדקס שהוא רלוונטי – אנו ניגשים לאיבר יחיד באינדקס. למשל: כאשר העמודה היא UNIQUE + NOT NULL.
      • ref – אנו הולכים לסרוק איברים באינדקס כמספר הרשומות שנשלוף. הגישה לאינדקס היא “מדויקת”.
      • range – עלינו לסרוק טווח (או מספר טווחים) באינדקס – שכנראה יש בהם יותר איברים ממספר הרשומות שאנו עומדים לשלוף.
      • index – יש צורך לסרוק את כל האינדקס. 
      • ALL – יש צורך לסרוק את כל הרשומות בטבלה. 😱
    • ייתכנו מקרי-קצה בהם index ו ALL – יהיו עדיפים על range או ref. אפרט אותם בהמשך.
רק להזכיר: האינדקס הוא “טבלה” רזה יותר המכילה רק עמודה בודדת (או מספר עמודות בודדות) מהטבלה – וממוינת ע”פ הסדר הזה. לא נדיר שאינדקס הוא רזה פי 10 ויותר מהטבלה (במיוחד אם בטבלה יש שדות מטיפוס varchar) – ואז גם סריקה מלאה של האינדקס הוא טעינה של (נניח) 10MB מדיסק – מול 100MB או יותר של נתונים שיש לטעון עבור סריקה מלאה של הטבלה.

 

מצד שני, גישה לאינדקס היא רק הקדמה לגישה לטבלה.

למשל: 20% מהרשומות עלולה בקלות להתמפות ל 50% מהבלוקים בדיסק בהם מאוחסנת הטבלה (במיוחד כאשר הרשומות קטנות). רזולוציית השליפה היא בלוקים – ולא רשומות בודדות.

יתרה מכך: שליפת כל הבלוקים של הטבלה כקובץ רציף תהיה לרוב מהירה יותר משליפת חצי מהבלוקים – כקבצים “רנדומליים”.

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

 

זהו. אם רציתם לקבל 50% מה value בקריאת 20% מהפוסט – אתם יכולים לעצור כאן, ולהמשיך בעיסוקכם.
 

 

תזכורת

מידע נוסף שניתן לשאוב מתוך פקודת ה Explain

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

 

קבוצה #1: “איך הנתונים נשלפים”

שלושת העמודות הראשונות עוזרות לנו לקשר בין השורה ב output לחלק המתאים בשאילתה.
בד”כ תוצאה של explain תהיה שורה או שתיים – אך גם נתקלתי גם ב 6 או 7 שורות בשאילתות מורכבות במיוחד.

  • Select Type הוא שדה זה נועד לאתר סוג ה SELECT שאליו מתייחסת השורה: SIMPLE – כאשר אין קינון שאילתות או איחוד שלהן, PRIMARY – השאילתה החיצונית ביותר (כאשר יש קינון) או הראשונה (ב UNION), יש גם SUBQUERY, UNION וכו’.
    • מעבר למה שציינתי / מה שאינטואטיבי – חבל להשקיע בהבנת העמודה הזו. בהמשך אראה לכם מה עושים אם יש שאילתה מורכבת במיוחד.
  • table – הטבלה שעליה המתבצע ה Select. זה יכול להיות שם של טבלה, או ביטוי כגון <Union <table1, table2.
  • partition ה partition של הטבלה, במידה ויש כזה.
    • partition היא היכולת להגדיר שחלקים שונים של הטבלה יאוחסנו על הדיסק בנפרד זה מזה (על מנת לשפר ביצועים בגישה לחלק המסוים). לדוגמה: כל הרשומות של שנת 2018 נשמרות בנפרד מהרשומות של שנת 2017 – למרות שלוגית זו טבלה אחת. התוצאה: שאילתות בתוך שנה בודדת יהיו מהירות יותר – על חשבון שאילתות הדורשות נתונים מכמה שנים.

העמודה החשובה יותר היא type – אותה כבר הזכרנו. הנה כמה פרטים נוספים ששווה להכיר:

סריקת index יכולה להיות יעילה יותר מ range או ref

שימוש באינדקס נעשה לרוב ב-2 שלבים:
  1. האינדקס נסרק / נקרא, וממנו נשלפו מזהיי (primary key) הרשומות שיש לקרוא מהטבלה.
  2. נעשית גישה נוספת לטבלה על מנת לטעון את ערכי הרשומות המתאימות.

אם בעמודה “Extra” (האחרונה) מופיע הערך “Using Index” משמע שלא היה צורך בשלב 2 – כי ערכי העמודות שביקשנו – נמצאו כבר באינדקס.

למשל: ביקשנו SELECT x FROM table1 WHERE y = 4
אם יש לנו אינדקס על עמודות x ו y – הרי שניתן ניתן לספק את התשובה מתוך קריאת האינדקס בלבד – וללא גישה לטבלה. זהו מצב מצוין.

 

סריקת ALL עשויה להיות יעילה יותר מ index, range או ref

בהמשך להסבר של שני שלבי השליפה:

  1. כאשר הטבלאות מאוד קטנות (KBs מעטים) – טעינת האינדקס ואז טעינת הטבלה – דורשת לפחות שתי גישות לדיסק.
  2. במקרים כאלו עדיף כבר לטעון את תוכן הטבלה (“ALL”) ולסרוק אותה. העלות העיקרית היא מספר הגישות לדיסק – ולא הסריקה בזיכרון.
קבוצה #2: “אינדקסים”
 
  • possible_key – היא רשימת האינדקסים שמכילים את העמודות בתנאי ה WHERE / JOIN.
    האופטימייזר בוחר אינדקס ספציפי (זה שהופיע בעמודה key) ע”פ יוריסטיקות מסוימות – והוא עשוי גם לפספס.
    • אם אתם משוכנעים שהוא לא בחר נכון (הוא טועה פחות ממה שנדמה לנו) אתם יכולים להנחות אותו באיזה אינדקס להשתמש בעזרת ההנחיה (USE INDEX (idx המגדילה את ציון האינדקס באלגוריתם הבחירה.
    • אתם יכולים להשתמש גם ב  FORCE INDEX – אבל התוצאה עשויה להכאיב: מגיעה שאילתה (או אלפי מופעמים של שאילתה) שאין טעם באינדקס – אך האופטימייזר ישתמש באינדקס, כי אתם אמרתם.
    • הנחיה אחרונה שימושית היא IGNORE INDEX – אתם יכולים לקרוא עוד בנושא כאן.
  • key_len – נשמעת כמו עמודה משעממת עד חרפה, אך זה בכלל לא המצב!
    • key_len עוזרת לאתר בזריזות אינדקסים “שמנים”. 
      • למשל: בתמונה למעלה יש key באורך 3003 בתים. כיצד זה ייתכן?
      • כל Character ב Unicode היא 3 בתים (בייצוג של MySQL) אז 3*1000 = 3000 בתים. עוד שלושה בתים, כך נראה לי, מגיעים מכך שהשדה הוא Nullable (בד”כ Nullable מוסיף 1+ לגודל).
        כלומר: גודל האינדקס הוא כ 90% מגודל הטבלה.
      • אם יש לי שאילתות הסורקות את כל האינדקס (type=index) – הרי שהאינדקס גורם לפי 2 נתונים להיטען מהדיסק, מאשר לו היינו פשוט סורקים את הטבלה.
      • כשאינדקס הוא כ”כ גדול (3000 בתים!), ובמידה ונעשה שימוש תדיר באינדקס – שווה לחשוב על הוספת עמודה נוספת, רזה יותר, המכילה subset של תוכן העמודה המקורית (למשל: 50 תווים ראשונים) – ואת העמודה הזו נאנדקס. הפתרון הספציפי מאוד תלוי בסוג הנתונים והגישה אליהם.
    • תובנה חשובה נוספת, שעמודת ה key_len יכולה לספק לי היא בכמה עמודות מתוך אינדקס-מרובה-עמודות נעשה שימוש. אם יש לי אינדקס של שני שדות מטיפוס INT (כלומר: ביחד 8 בתים), אך העמודה key_len מחזירה ערך 4 – סימן שנעשה שימוש רק בעמודה הראשונה שבאינדקס (שימוש באינדקס יכול להיעשות רק ע”פ סדר האינדקסים “שמאל לימין”).
      • לא קל להבחין במקרים הללו (צריך לחשב גדלים של שדות) – אך הם יכולים להעיד על בעיה בלתי-צפויה. למשל: תנאי שעוטף את ערך המספר במרכאות – וכך נמנע שימוש בשדה המשני של האינדקס. לכאורה: היה שימוש באינדקס – אבל שיפור שכזה עשוי לשפר את זמן הריצה פי כמה.
      • נ.ב. שדה Int תמיד יהיה 4 בתים. ה “length” משפיע על מספר הספרות בתצוגה.
      • אם אתם לא בטוחים מה מבנה הטבלה, תוכלו להציג אותו בזריזות בעזרת
          SHOW CREATE TABLE tbl_name 
    • ref – איזה סוג של ערכים מושווים כנגד האינדקס. לא כזה חשוב.
      • const הוא הערך הנפוץ, המתאר שההשוואה היא מול ערך “קבוע”, למשל: ערך מתוך טבלה אחרת.
      • NULL הכוונה שלא נעשתה השוואה. זה מתרחש כאשר יש Join ומדובר בטבלה המובילה את ה join.
      • func משמע שנעשה שימוש בפונקציה. 
 
אם קיבלתם ערך ref=func, ואתם רוצים לדעת איזה פונקציה הופעלה, הפעילו את הפקודה SHOW WARNINGS מיד לאחר ה Explain. היא תספק לכם מידע נוסף (מה שנקרא בעבר “extended explain“).
 
זוכרים שאמרתי לכם שאם יש שאילתה מורכבת מאוד, חבל לנסות ולשבור את הראש איזו שורה ב Explain מתאימה לאיזה חלק בשאילה? 
כאשר אתם קוראים ל Show Warnings אתם גם מקבלים את הגרסה ה “expanded” של השאילתה.
 
/* select#1 */ SELECT `quote_job_execution_internals`.`value` AS `value` 
FROM  `quote_job_execution_internals` 
WHERE  ( ( `quote_job_execution_internals`.`quote_job_id` 
           = ( /* select#2 */ SELECT            `quote_job_execution_internals`.`quote_job_id` 
           FROM   `quote_job_execution_internals` 
           WHERE  ( (            `quote_job_execution_internals`.`key` = ‘nimiGlPolicyId’ ) 
           AND (`quote_job_execution_internals`.`value` = ‘1290268’ ) )) ) 
           AND ( `quote_job_execution_internals`.`key` = ‘storageId’ ) ) 
 

הסימון בהערות (“select#1” ו “select#2“) הוא הדרך הקלה לזהות אלו חלקים בשאילתה מתייחסים לאלו שורות ב Explain. 

 
 
 
קבוצה #3: כמות הרשומות

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

  • עמודת ה rows מספקת הערכה כמה נתונים ייסרקו. השאילתה עדיין לא רצה – אז לא ניתן לדעת.
    • במקרה שלנו, כאשר ה subquery עתיד לסרוק 2 שורות, וה primary select עתיד לסרוק 6 שורות – אנו מצפים לסריקה של 6 * 2 = 12 שורות, וזה מספר קטן. 
    • אם יש לנו 3 שאילתות מקוננות, וכל אחת סורקת 50 שורות – אזי נגיע סה”כ לסריקה של עד 125,000 שורות – וזה כבר הרבה!
  • עמודת ה filtered מספקת הערכה (לא מדויקת) באיזה אחוז מהרשומות שנסרקו באינדקס – ייעשה שימוש. 
    • בדוגמה למעלה קיבלנו הערכה שנטען 6 או 2 רשומות, וב “10%” מהן יעשה שימוש, כלומר: באחת.
    • כאשר הערך הוא נמוך מאוד (פחות מאחוזים בודדים, או שבריר האחוז), ובמיוחד כאשר מספר הרשומות הנסרק (rows) הוא גבוה – זהו סימן שהשימוש באינדקס הוא לא יעיל. ייתכן ואינדקס אחר יכול להתאים יותר?
    • השאיפה שלנו היא להגיע ל rows = 1 ו filtered = 100% – זהו אינדקס המנוצל בצורה מיטבית!
 
לצורך האופטימייזר, בסיס הנתונים שומר לעצמו נתונים סטטיסטיים על התפלגות הנתונים בטבלאות השונות. ב MySQL המידע נשמר ב Information_schema.statistics – והוא מתעדכן עם הזמן. 
 
אם הרגע הוספתם אינדקס, ו/או הכנסתם / עדכנתם חלקים גדולים מהטבלה – יש סיכוי טוב שייקח זמן עד שיאספו נתונים חדשים, שיעזרו לאופטימייזר להשתמש נכון באינדקסים במצב החדש.
 
הפקודה ANALYZE TABLE your_table גורמת ל MySQL לאוסף את הנתונים מחדש. זה עשוי לארוך קצת זמן.

 

הפקודה OPTIMIZE TABLE your_table גורמת ל MySQL לבצע דחיסה (סוג של defrag) על הקבצים של הטבלה – ואז להריץ Analyze Table. זה ייקח יותר זמן – ולכן מומלץ להריץ את הפקודה הזו רק “בשעות המתות”.

 

 

 

קבוצה #4: Extra


עמודת האקסטרא בד”כ תאמר לכם “Using Where” (דאא?), אבל יש כמה ערכים שהיא יכולה לקבל שדווקא מעניינים:

  • Using Index – ציינו את המקרה הזה למעלה. נעשה שימוש באינדקס בלבד על מנת לספק את הערכים (מצוין!)
  • Using filesort – מכיוון שלא הצליח לבצע מיון (ORDER BY) על בסיס אינדקס ו/או כמות הנתונים גדולה מדי – בסיס הנתונים משתמש באלגוריתם בשם filesort בכדי לבצע את המיון. בסיס הנתונים ינסה להקצות שטח גדול בזיכרון ולבצע אותו שם – לפני שהוא באמת משתמש בקבצים. 
    • פעמים רבות אפשר להימנע מ filesort ע״י אינדקס על העמודה לפיה עושים מיון. 
    • [עודכן] כמובן שבאינדקס הכולל כמה שדות – רק השדה הראשון (ה״שמאלי״) שימושי למיון, או לחלופין אם סיננו ע”פ האינדקס (WHERE) – השדה השמאלי לשדה/שדות על פיהם נעשה הסינון (תודה לאנונימי על ההערה).
    • אם יש ORDER BY על יותר מעמודה אחת – שימוש באינדקס יהיה יעיל רק אם האינדקס מכיל את השדות לפיהם ממיינים בסדר הנכון.
  • Using temporary – מקרה דומה: יש פעולה גדולה (בד״כ GROUP BY) שלא ניתן לבצע בזיכרון – ולכן משתמשים בטבלה זמנית (בזיכרון). גם זה מצב שכדאי לנסות להימנע ממנו.
    • בד”כ אין פתרונות קלים למצב הזה: לנסות ולהסתדר ללא GROUP BY? לבצע את פעולת הקיבוץ אפליקטיבית בקוד? (כמעט תמיד custom code שנכתב בחכמה ינצח את בסיס הנתונים בביצועים – אבל יהיה יקר יותר לפיתוח ותחזוקה)

 

 

אחרית דבר

לאחר הפוסט הזה אתם אמורים להבין את תוצאות הפקודה EXPLAIN בצורה דיי טובה!

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

שווה להזכיר את ה Slow Log וה Performance Schema שעוזרים לאתר בכלל את השאילתות הבעייתיות.
את Profile ו Trace (+ מידע שניתן לשלוף מה Performance Schema) – שבעזרתם ניתן לבחון כיצד השאילתה רצה בפועל.

הפרמטר  format=json (כלומר EXPLAIN format=json SELECT something) גורם ל Explain לספק פירוט עשיר יותר (ובפורמט json).

יש גם עוד הנחיות שונות שניתן להוסיף בשאילתה על מנת להנחות את האופטימייזר לפעול באופן מסוים (למשל SQL_BIG_RESULT או STRAIGHT_JOIN), או אפילו הנחיות הנובעות מהחומרה שבשימוש (להלן טבלת ה mysql.server_cost , וה optimizer_switch)
אבל אני חושב שאת תחום זה מוטב להשאיר ל DBAs…

שיהיה בהצלחה!