סיפורה של חקירת SQL אחת

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

פרהיסטוריה

כדי להבהיר על מה אני מדבר, אספר לכם קצת על VWO. זוהי פלטפורמה שבאמצעותה תוכלו להשיק קמפיינים ממוקדים שונים באתרי האינטרנט שלכם: לערוך ניסויי A/B, לעקוב אחר מבקרים והמרות, לנתח את משפך המכירות, להציג מפות חום ולהפעיל הקלטות ביקורים.

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

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

הצג את כל הקליקים בדף "abc.com" מ-<date d1> עד <date d2> עבור אנשים שהשתמשו ב-Chrome OR (ממוקם באירופה והשתמשו באייפון)

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

בקשה איטית

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

הצג את כל רשומות ההפעלה עבור משתמשים שביקרו בדף כלשהו עם כתובת אתר המכילה "/jobs"

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

חקירה ראשונית

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

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND sessions.referrer_id = recordings_urls.id 
    AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   ) 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0 ;

והנה התזמונים:

זמן מתוכנן: 1.480 ms זמן ביצוע: 1431924.650 ms

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

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

1. הפעלות: להצגת פרטי הפעלה: דפדפן, סוכן משתמש, מדינה וכן הלאה.
2. recording_data: כתובות URL מוקלטות, דפים, משך ביקורים
3. כתובות אינטרנט: כדי להימנע משכפול של כתובות URL גדולות במיוחד, אנו מאחסנים אותן בטבלה נפרדת.

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

מחפש רמזים

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

urls && array(
	select id from acc_{account_id}.urls 
	where url  ILIKE  '%enterprise_customer.com/jobs%'
)::text[]

המחשבה הראשונה הייתה שאולי בגלל ILIKE בכל כתובות האתרים הארוכות הללו (יש לנו יותר מ-1,4 מיליון ייחודי כתובות אתרים שנאספו עבור חשבון זה) עלולים להיפגע.

אבל לא, זה לא העניין!

SELECT id FROM urls WHERE url ILIKE '%enterprise_customer.com/jobs%';
  id
--------
 ...
(198661 rows)

Time: 5231.765 ms

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

החשוד הבא ברשימה הוא כמה JOIN. אולי השימוש המופרז שלהם גרם להאטה? בְּדֶרֶך כְּלַל JOINהם המועמדים הברורים ביותר לבעיות ביצועים, אבל לא האמנתי שהמקרה שלנו אופייני.

analytics_db=# SELECT
    count(*)
FROM
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data_0 as recording_data,
    acc_{account_id}.sessions_0 as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND sessions.referrer_id = recordings_urls.id
    AND r_time > to_timestamp(1542585600)
    AND r_time < to_timestamp(1545177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
-------
  8086
(1 row)

Time: 147.851 ms

וזה גם לא היה המקרה שלנו. JOINהתברר להיות די מהיר.

צמצום מעגל החשודים

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

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

SELECT
	count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls,
    acc_{account_id}.recording_data as recording_data,
    acc_{account_id}.sessions as sessions
WHERE
    recording_data.usp_id = sessions.usp_id
    AND  (  1 = 1  )
    AND sessions.referrer_id = recordings_urls.id
    AND  (exists(select id from acc_{account_id}.urls where url  ILIKE '%enterprise_customer.com/jobs%'))
    AND r_time > to_timestamp(1547585600)
    AND r_time < to_timestamp(1549177599)
    AND recording_data.duration >=5
    AND recording_data.num_of_pages > 0 ;
 count
 32519
(1 row)
Time: 1636.637 ms

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

• העברת שאילתת המשנה ל-CTE : אם השאילתה מהירה בפני עצמה, נוכל פשוט לחשב תחילה את התוצאה המהירה ולאחר מכן לספק אותה לשאילתה הראשית

WITH matching_urls AS (
    select id::text from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%'
)

SELECT 
    count(*) FROM acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions,
    matching_urls
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id
    AND (urls && array(SELECT id from matching_urls)::text[])
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545107599)
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0;

אבל זה עדיין היה איטי מאוד.

מציאת האשם

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

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

בבקשה המקורית זה:

AND  (  urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]   )

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

עם חשדות גוברים לגבי &&, ניסיתי למצוא עבורם אישור בתוכנית השאילתות שנוצרה EXPLAIN ANALYZE (כבר נשמרה לי תוכנית, אבל בדרך כלל נוח לי יותר להתנסות ב-SQL מאשר לנסות להבין את האטימות של מתכנני שאילתות).

Filter: ((urls && ($0)::text[]) AND (r_time > '2018-12-17 12:17:23+00'::timestamp with time zone) AND (r_time < '2018-12-18 23:59:59+00'::timestamp with time zone) AND (duration >= '5'::double precision) AND (num_of_pages > 0))
                           Rows Removed by Filter: 52710

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

בדקתי זאת על ידי בידוד המצב

SELECT 1
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data_30 as recording_data_30, 
    acc_{account_id}.sessions_30 as sessions_30 
WHERE 
	urls &&  array(select id from acc_{account_id}.urls where url  ILIKE  '%enterprise_customer.com/jobs%')::text[]

השאילתה הזו הייתה איטית. בגלל ה JOIN-ים מהירים ותתי שאילתות מהירות, הדבר היחיד שנותר היה && מַפעִיל.

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

בדרך לפתרון

&& איטי כי שני הסטים ענקיים. הפעולה תהיה מהירה יחסית אם אחליף urls על { "http://google.com/", "http://wingify.com/" }.

התחלתי לחפש דרך לעשות צומת סט ב-Postgres בלי להשתמש &&, אך ללא הצלחה יתרה.

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

SELECT urls.url
FROM 
	acc_{account_id}.urls as urls,
	(SELECT unnest(recording_data.urls) AS id) AS unrolled_urls
WHERE
	urls.id = unrolled_urls.id AND
	urls.url  ILIKE  '%jobs%'

במקום זאת JOIN תחביר פשוט השתמשתי בשאילתת משנה והרחבתי recording_data.urls מערך כך שתוכל להחיל ישירות את התנאי ב WHERE.

הדבר החשוב ביותר כאן הוא זה && משמש כדי לבדוק אם ערך נתון מכיל כתובת URL תואמת. אם תמצמץ מעט, תוכל לראות את הפעולה הזו עוברת דרך האלמנטים של מערך (או שורות של טבלה) ונפסקת כאשר מתקיים תנאי (התאמה). לא מזכיר לך כלום? כֵּן, EXISTS.

מאז recording_data.urls ניתן להפנות מחוץ להקשר של שאילתת המשנה, כאשר זה קורה נוכל לחזור על החבר הוותיק שלנו EXISTS ולעטוף איתה את שאילתת המשנה.

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

SELECT 
    count(*) 
FROM 
    acc_{account_id}.urls as recordings_urls, 
    acc_{account_id}.recording_data as recording_data, 
    acc_{account_id}.sessions as sessions 
WHERE 
    recording_data.usp_id = sessions.usp_id 
    AND  (  1 = 1  )  
    AND sessions.referrer_id = recordings_urls.id 
    AND r_time > to_timestamp(1542585600) 
    AND r_time < to_timestamp(1545177599) 
    AND recording_data.duration >=5 
    AND recording_data.num_of_pages > 0
    AND EXISTS(
        SELECT urls.url
        FROM 
            acc_{account_id}.urls as urls,
            (SELECT unnest(urls) AS rec_url_id FROM acc_{account_id}.recording_data) 
            AS unrolled_urls
        WHERE
            urls.id = unrolled_urls.rec_url_id AND
            urls.url  ILIKE  '%enterprise_customer.com/jobs%'
    );

וזמן ההובלה הסופי Time: 1898.717 ms הגיע הזמן לחגוג?!?

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

מבחן פשוט היה לרוץ count(*) בשאילתות איטיות ומהירות כאחד עבור מספר רב של מערכי נתונים שונים. לאחר מכן, עבור תת-קבוצה קטנה של הנתונים, וידאתי ידנית שכל התוצאות נכונות.

כל הבדיקות נתנו תוצאות חיוביות באופן עקבי. תיקנו הכל!

לקחים

יש הרבה לקחים שאפשר ללמוד מהסיפור הזה:

1. תוכניות שאילתות אינן מספרות את כל הסיפור, אבל הן יכולות לספק רמזים
2. החשודים העיקריים הם לא תמיד האשמים האמיתיים
3. ניתן לפרק שאילתות איטיות כדי לבודד צווארי בקבוק
4. לא כל האופטימיזציות הן רדוקטיביות בטבען
5. להשתמש EXIST, היכן שניתן, יכול להוביל לעלייה דרמטית בפריון

פלט

עברנו מזמן שאילתה של ~24 דקות ל-2 שניות - עלייה משמעותית בביצועים! למרות שהכתבה הזו יצאה גדולה, כל הניסויים שעשינו התרחשו ביום אחד, וההערכה היא שהם ארכו בין 1,5 לשעתיים לאופטימיזציות ובדיקות.

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

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

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

מקור: www.habr.com