אתה זוכר איך הכל התחיל. הכל היה פעם ראשונה ושוב

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

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

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

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

כנראה יהיה מעניין לשחזר את שרשרת האירועים הקודמים.
ההיסטוריה שמרה את תאריך ההתחלה המדויק - 2018-09-10 18:02:48.
כמו כן, בסיפור יש בקשה שממנה הכל התחיל:
בקשת בעיהלבחור
p.“PARAMETER_ID” בתור parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s.“SPENT_DATE” AS spent_date,
extract(year from "SPENT_DATE") AS year,
extract(month from "SPENT_DATE") כחודש,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
מ-wdata w,
בילה s,
pmtr p,
spend_pd sp,
pd pd
WHERE s.“SPENT_ID” = w.“SPENT_ID”
ו-p."PARAMETER_ID" = w."PARAMETER_ID"
AND s.“SPENT_ID” = sp.“SPENT_ID”
AND pd."PD_ID" = sp."PD_ID"
AND s.“SPENT_DATE” >= '2018-07-01' ו-s.“SPENT_DATE” <= '2018-09-30'
ו-s.“SPENT_DATE” = (בחר MAX(s2.“SPENT_DATE”)
מהוצאת s2,
wdata w2
WHERE s2.“SPENT_ID” = w2.“SPENT_ID”
AND w2.“LRM” = w.“LRM”);

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

הלומר מגיע אל ההאקר.
-שום דבר לא עובד לי, תגיד לי איפה הבעיה.
-ב-DNA...

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

החלה חקירה

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

המעניין והשימושי ביותר, כרגיל, הוא בהתחלה ובסוף.
לולאה מקוננת (עלות=935.84..479763226.18 שורות=3322 רוחב=135) (זמן בפועל=31.536..8220420.295 שורות=8111656 לולאות=1)
זמן תכנון: 3.807 אלפיות השנייה
זמן ביצוע: 8222351.640 ms
זמן ההשלמה הוא יותר משעתיים.

השערות שווא שלקח זמן

השערה 1 - המייעל עושה טעות ובונה את התוכנית הלא נכונה.

כדי להמחיש את תוכנית הביצוע, נשתמש באתר https://explain.depesz.com/. עם זאת, האתר לא הראה שום דבר מעניין או שימושי. במבט ראשון ושני, אין שום דבר שיכול באמת לעזור. האם ייתכן שסריקה מלאה היא מינימלית. לך על זה.

השערה 2-השפעה על הבסיס מצד הוואקום האוטומטי, אתה צריך להיפטר מהבלמים.

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

השערה 3 - הסטטיסטיקה מיושנת, צריך לחשב הכל מחדש

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

בואו נתחיל לבצע אופטימיזציה

הטבלה הראשית 'wdata' היא בהחלט לא קטנה, כמעט 3 מיליון רשומות.
וטבלה זו עוקבת אחר סריקה מלאה.

Hash Cond: ((w."SPENT_ID" = s."SPENT_ID") AND ((SubPlan 1) = s"SPENT_DATE"))
-> סריקת Seq על wdata w (עלות=0.00..574151.49 rows=26886249 width=46) (זמן בפועל=0.005..8153.565 rows=26873950 לולאות=1)
אנחנו עושים את הדבר הסטנדרטי: "יאללה, בואו נעשה אינדקס והכל יעוף".
יצר אינדקס בשדה "SPENT_ID".
כתוצאה מכך:
תוכנית ביצוע שאילתה באמצעות אינדקס

ובכן, זה עזר?
זה היה: 8 222 351.640 MS (קצת יותר משעתיים)
זה הפך: 6 985 431.575 אלפיות השנייה (כמעט שעתיים)
באופן כללי, אותם תפוחים, מבט מהצד.
בואו נזכור את הקלאסיקה:
"יש לך אותו אחד, אבל בלי כנפיים? יחפש".

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

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

שלב ראשון - השתמש ב-JOIN

הבקשה המשוכתבת נראית כעת כך (טוב לפחות יותר יפה):
שאילתה באמצעות JOINלבחור
p.“PARAMETER_ID” בתור parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s.“SPENT_DATE” AS spent_date,
extract(year from "SPENT_DATE") AS year,
extract(month from "SPENT_DATE") כחודש,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN בילה s ON w.“SPENT_ID”=s.”“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
איפה
s.“SPENT_DATE” >= '2018-07-01' ו-s.“SPENT_DATE” <= '2018-09-30'AND
s.“SPENT_DATE” = (בחר MAX(s2.“SPENT_DATE”)
FROM wdata w2 INNER JOIN הוצא s2 ON w2.“SPENT_ID”=s2.“SPENT_ID”
INNER JOIN wdata w
ON w2.“LRM” = w.“LRM” );
זמן תכנון: 2.486 אלפיות השנייה
זמן ביצוע: 1223680.326 ms

אז, התוצאה הראשונה.
זה היה: 6 אלפיות השנייה (כמעט שעתיים).
זה הפך: 1 223 680.326 אלפיות השנייה (מעט יותר מ-20 דקות).
תוצאה טובה. באופן עקרוני, שוב, נוכל לעצור שם. אבל זה כל כך לא מעניין, אי אפשר להפסיק.
ל

שלב שני - היפטר משאילת המשנה המתואמת

טקסט בקשה שונה:
ללא שאילתת משנה בקורלציהלבחור
p.“PARAMETER_ID” בתור parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s.“SPENT_DATE” AS spent_date,
extract(year from "SPENT_DATE") AS year,
extract(month from "SPENT_DATE") כחודש,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN בילה s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN (בחר w2.“LRM”, MAX(s2.“SPENT_DATE”)
FROM בילה s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GROUP BY w2."LRM"
) md on w.“LRM” = md.“LRM”
איפה
s."SPENT_DATE" >= '2018-07-01' ו-s"SPENT_DATE" <= '2018-09-30';
זמן תכנון: 2.291 אלפיות השנייה
זמן ביצוע: 165021.870 ms

זה היה: 1 223 680.326 אלפיות השנייה (מעט יותר מ-20 דקות).
זה הפך: 165 021.870 ms (מעט יותר מ-2 דקות).
זה כבר די טוב.
עם זאת, כמו שאומרים הבריטים "אבל, תמיד יש אבל" תוצאה טובה מדי אמורה לעורר באופן אוטומטי חשד. משהו לא בסדר כאן.

ההשערה לגבי תיקון השאילתה כדי להיפטר מתת שאילתת המתאם נכונה. אבל אתה צריך לצבוט אותו מעט כדי שהתוצאה הסופית תהיה נכונה.
כתוצאה מכך, תוצאת הביניים הראשונה:
שאילתה ערוכה ללא שאילתת משנה בהתאמהלבחור
p.“PARAMETER_ID” בתור parameter_id,
pd."PD_NAME" AS pd_name,
pd."CUSTOMER_PARTNUMBER" AS customer_partnumber,
w. "LRM" AS LRM,
w. "LOTID" AS lotid,
w.“RTD_VALUE” AS RTD_value,
w.“LOWER_SPEC_LIMIT” AS lower_spec_limit,
w.“UPPER_SPEC_LIMIT” AS upper_spec_limit,
p."TYPE_CALCUL" AS type_calcul,
s."SPENT_NAME" AS spend_name,
s.“SPENT_DATE” AS spent_date,
extract(year from s.“SPENT_DATE”) AS year,
חלץ (חודש מתאריך s.“SPENT_DATE”) כחודש,
s."REPORT_NAME" AS report_name,
p."STPM_NAME" AS stpm_name,
p.“CUSTOMERPARAM_NAME” AS customerparam_name
FROM wdata w INNER JOIN בילה s ON s.“SPENT_ID” = w.“SPENT_ID”
INNER JOIN pmtr p ON p.“PARAMETER_ID” = w.“PARAMETER_ID”
INNER JOIN spent_pd sp ON s.“SPENT_ID” = sp.“SPENT_ID”
INNER JOIN pd pd ON pd.“PD_ID” = sp.“PD_ID”
INNER JOIN (בחר w2.“LRM”, MAX(s2.“SPENT_DATE”) כ-“SPENT_DATE”
FROM בילה s2 INNER JOIN wdata w2 ON s2.“SPENT_ID” = w2.“SPENT_ID”
GROUP BY w2."LRM"
) md ON md.“SPENT_DATE” = s.“SPENT_DATE” ו-md.“LRM” = w.“LRM”
איפה
s."SPENT_DATE" >= '2018-07-01' ו-s"SPENT_DATE" <= '2018-09-30';
זמן תכנון: 3.192 אלפיות השנייה
זמן ביצוע: 208014.134 ms

אז מה שאנחנו מקבלים הוא התוצאה המקובלת הראשונה, שלא חבל להראות ללקוח:
התחיל עם: 8 222 351.640 אלפיות השנייה (יותר משעתיים)
הצלחנו להשיג: 1 אלפיות השנייה (קצת יותר מ-223 דקות).
תוצאה (ביניים): 208 014.134 ms (מעט יותר מ-3 דקות).

תוצאה מעולה.

סך הכל

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

אז הגיע הזמן לעיצוב מחדש של מסד הנתונים. לא ניתן עוד לבצע אופטימיזציה של מבנה השאילתה עצמו (אם כי, כפי שהתברר מאוחר יותר, ישנה אפשרות להבטיח שהכל אכן ייכשל). אבל להתחיל לייעל ולפתח את עיצוב מסד הנתונים זה כבר רעיון מאוד מבטיח. והכי חשוב מעניין. שוב, זכור את נעוריך. הרי לא מיד הפכתי ל-DBA, גדלתי כמתכנת (BASIC, assembler, C, double-plus C, Oracle, plsql). נושא מעניין, כמובן, לספר זיכרונות נפרד ;-).
עם זאת, בואו לא נסיח את דעתנו.

לפיכך,

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

אבל זה סיפור אחר לגמרי...

המשך יבוא…

מקור: www.habr.com