אופטימיזציה של שאילתות מסד נתונים באמצעות הדוגמה של שירות B2B עבור בונים

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

אני עושה שירות לניהול תהליכים עסקיים בחברות בנייה. כ-3 חברות עובדות איתנו. יותר מ-10 אלף אנשים עובדים עם המערכת שלנו מדי יום במשך 4-10 שעות. זה פותר בעיות שונות של תכנון, הודעה, אזהרה, אימות... אנו משתמשים ב-PostgreSQL 9.6. יש לנו כ-300 טבלאות במסד הנתונים ומתקבלות עד 200 מיליון שאילתות (10 אלף שונות) מדי יום. בממוצע יש לנו 3-4 אלף בקשות בשנייה, ברגעים הפעילים ביותר יותר מ-10 אלף בקשות בשנייה. רוב השאילתות הן OLAP. יש הרבה פחות תוספות, שינויים ומחיקות, כלומר עומס ה-OLTP קל יחסית. סיפקתי את כל המספרים האלה כדי שתוכל להעריך את קנה המידה של הפרויקט שלנו ולהבין עד כמה הניסיון שלנו יכול להיות שימושי עבורך.

תמונה אחת. לִירִי

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

תמונה שניה. סטָטִיסטִי

אז יש לנו בערך 10 שאילתות שונות שמתבצעות על מסד הנתונים שלנו ביום. מתוך 10 אלף אלו, יש מפלצות שמבוצעות 2-3 מיליון פעמים עם זמן ביצוע ממוצע של 0.1-0.3 ms, ויש שאילתות עם זמן ביצוע ממוצע של 30 שניות שנקראות 100 פעמים ביום.

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

בקשות מובילות

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

הנוהג המקובל של כל החברות הוא לעבוד עם בקשות TOP. יש מעט מהם; אופטימיזציה אפילו של שאילתה אחת יכולה לפנות 5-10% ממשאבים. עם זאת, ככל שהפרויקט מתבגר, אופטימיזציה של שאילתות TOP הופכת למשימה יותר ויותר לא טריוויאלית. כל השיטות הפשוטות כבר עובדו, והבקשה ה"כבדה" ביותר גוזלת "רק" 3-5% מהמשאבים. אם שאילתות TOP בסך הכל לוקחות פחות מ-30-40% מהזמן, סביר להניח שכבר עשית מאמצים לגרום להן לעבוד במהירות והגיע הזמן לעבור לאופטימיזציה של שאילתות מהקבוצה הבאה.
נותר לענות על השאלה כמה שאילתות מובילות יש לכלול בקבוצה זו. אני בדרך כלל לוקח לפחות 10, אבל לא יותר מ-20. אני משתדל שהזמן של הראשון והאחרון בקבוצת ה-TOP יהיה שונה בלא יותר מ-10 פעמים. כלומר, אם זמן ביצוע השאילתה יורד בחדות מהמקום ה-1 ל-10, אז אני לוקח TOP-10, אם הירידה היא הדרגתית יותר, אז אני מגדיל את גודל הקבוצה ל-15 או 20.

איכרים בינוניים

כל אלו הן בקשות שמגיעות מיד אחרי TOP, למעט 5-10% האחרונים. בדרך כלל, באופטימיזציה של שאילתות אלו טמונה ההזדמנות להגדיל במידה ניכרת את ביצועי השרת. בקשות אלו יכולות לשקול עד 80%. אבל גם אם חלקם עלה על 50%, אז הגיע הזמן להסתכל עליהם יותר בקפידה.

זָנָב

כאמור, שאילתות אלו מגיעות בסוף ולוקחות 5-10% מהזמן. אתה יכול לשכוח מהם רק אם אתה לא משתמש בכלים אוטומטיים לניתוח שאילתות, אז גם אופטימיזציה שלהם יכולה להיות זולה.

איך להעריך כל קבוצה?

אני משתמש בשאילתת SQL שעוזרת לבצע הערכה כזו עבור PostgreSQL (אני בטוח שניתן לכתוב שאילתה דומה עבור DBMS רבים אחרים)

שאילתת SQL להערכת הגודל של קבוצות TOP-MEDIUM-TAIL

SELECT sum(time_top) AS sum_top, sum(time_medium) AS sum_medium, sum(time_tail) AS sum_tail
FROM
(
  SELECT CASE WHEN rn <= 20              THEN tt_percent ELSE 0 END AS time_top,
         CASE WHEN rn > 20 AND rn <= 800 THEN tt_percent ELSE 0 END AS time_medium,
         CASE WHEN rn > 800              THEN tt_percent ELSE 0 END AS time_tail
  FROM (
    SELECT total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query,
    ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn
    FROM pg_stat_statements
    ORDER BY total_time DESC
  ) AS t
)
AS ts

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

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

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

כדי לקבל את טקסט הבקשות אנו משתמשים בבקשה הבאה

SELECT * FROM (
  SELECT ROW_NUMBER () OVER (ORDER BY total_time DESC) AS rn, total_time / (SELECT sum(total_time) FROM pg_stat_statements) * 100 AS tt_percent, query
  FROM pg_stat_statements
  ORDER BY total_time DESC
) AS T
WHERE
rn <= 20 -- TOP
-- rn > 20 AND rn <= 800 -- MEDIUM
-- rn > 800  -- TAIL

להלן רשימה של הטכניקות הנפוצות ביותר שעזרו לנו להאיץ את השאילתות המובילות:

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

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

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

• במקום לבדוק נוכחות של רשומות באמצעות COUNT וסריקת טבלה מלאה, החלו להשתמש ב-EXISTS
• נפטר מ-DISTINCT (אין מתכון כללי, אבל לפעמים אפשר להיפטר ממנו בקלות על ידי זירוז הבקשה פי 10-100).

  לדוגמה, במקום שאילתה לבחור את כל הנהגים מטבלת משלוחים גדולה (DELIVERY)

  SELECT DISTINCT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM DELIVERY D JOIN PERSON P ON D.DRIVER_ID = P.ID
  

  עשה שאילתה על PERSON קטן יחסית בטבלה

  SELECT P.ID, P.FIRST_NAME, P.LAST_NAME
  FROM PERSON
  WHERE EXISTS(SELECT D.ID FROM DELIVERY WHERE D.DRIVER_ID = P.ID)
  

  נראה שהשתמשנו בשאילתת משנה בקורלציה, אבל זה נותן מהירות של יותר מפי 10.

• במקרים רבים, COUNT ננטש לחלוטין ו
  הוחלף בחישוב ערך משוער
• במקום

  UPPER(s) LIKE JOHN%’ 
  

  שימוש

  s ILIKE “John%”
  

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

כתוצאה מכך, אנו עובדים על אותה חומרה כבר שלוש שנים. העומס היומי הממוצע הוא כ-30%, בשיאים הוא מגיע ל-70%. מספר הבקשות, כמו גם מספר המשתמשים, גדל פי 10 בערך. וכל זה הודות למעקב מתמיד אחר אותן קבוצות של בקשות TOP-MEDIUM. ברגע שמופיעה בקשה חדשה בקבוצת TOP, אנו מיד מנתחים אותה ומנסים לזרז אותה. אנו בודקים את קבוצת MEDIUM פעם בשבוע באמצעות סקריפטים לניתוח שאילתות. אם אנו נתקלים בשאילתות חדשות שאנו כבר יודעים כיצד לבצע אופטימיזציה, אנו משנים אותן במהירות. לפעמים אנו מוצאים שיטות אופטימיזציה חדשות שניתן ליישם על מספר שאילתות בו זמנית.

לפי התחזיות שלנו, השרת הנוכחי יעמוד בגידול במספר המשתמשים פי 3-5 נוספים. נכון, יש לנו עוד אס אחד בשרוול - עדיין לא העברנו שאילתות SELECT למראה, כפי שמומלץ. אבל אנחנו לא עושים זאת במודע, כי אנחנו רוצים קודם כל למצות לחלוטין את האפשרויות של אופטימיזציה "חכמה" לפני הפעלת ה"ארטילריה הכבדה".
מבט ביקורתי על העבודה שנעשתה עשויה להציע שימוש בקנה מידה אנכי. קנה שרת חזק יותר במקום לבזבז זמן של מומחים. ייתכן שהשרת לא יעלה כל כך הרבה, במיוחד מכיוון שעדיין לא מיצינו את גבולות ההרחבה האנכית. עם זאת, רק מספר הבקשות גדל פי 10. במהלך מספר שנים גדלה הפונקציונליות של המערכת וכעת יש יותר סוגי בקשות. הודות ל-caching, הפונקציונליות שהייתה מבוצעת בפחות בקשות, ובקשות יעילות יותר. זה אומר שאתה יכול להכפיל בבטחה בעוד 5 כדי לקבל את מקדם התאוצה האמיתי. לכן, על פי ההערכות השמרניות ביותר, אנו יכולים לומר שהתאוצה הייתה פי 50 או יותר. הפעלת שרת אנכית תעלה פי 50 יותר. במיוחד בהתחשב בכך שברגע שמבצעים אופטימיזציה זה עובד כל הזמן, והחשבון עבור השרת המושכר מגיע מדי חודש.

מקור: www.habr.com