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

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

באופן כללי, שקלנו אפשרויות שונות. אפשר היה לבנות מאגר מלא - אפילו ניסינו, אבל למען האמת, לא הצלחנו לשלב שינויים תכופים למדי בלוגיקה עם התהליך האיטי למדי של בניית מאגר וביצוע שינויים בו (אם מישהו הצליח , כתוב בתגובות איך). אפשר היה לומר לאנליסטים: "חבר'ה, למדו פיתון ולכו להעתקים אנליטיים", אבל זו דרישה נוספת לגיוס, ונראה היה שצריך להימנע מכך במידת האפשר. החלטנו לנסות להשתמש בטכנולוגיית FDW (Foreign Data Wrapper): בעצם, מדובר ב-dblink סטנדרטי, שנמצא בתקן SQL, אבל עם ממשק משלו הרבה יותר נוח. על סמך זה עשינו פתרון שבסופו של דבר תפס והסתדרנו עליו. הפרטים שלו הם נושא של מאמר נפרד, ואולי יותר מאחד, שכן אני רוצה לדבר על הרבה: מסנכרון סכימות מסד נתונים ועד בקרת גישה ודה-פרסונליזציה של נתונים אישיים. כמו כן, יש צורך להסתייג שפתרון זה אינו תחליף למאגרי מידע ומאגרים אנליטיים אמיתיים, הוא פותר רק בעיה ספציפית.

ברמה העליונה זה נראה כך:

ישנו מסד נתונים PostgreSQL שבו משתמשים יכולים לאחסן את נתוני העבודה שלהם, והכי חשוב, העתקים אנליטיים של כל השירותים מחוברים למסד נתונים זה באמצעות FDW. זה מאפשר לכתוב שאילתה לכמה מסדי נתונים, וזה לא משנה מה זה: PostgreSQL, MySQL, MongoDB או משהו אחר (קובץ, API, אם פתאום אין עטיפה מתאימה, אתה יכול לכתוב משלך). ובכן, הכל נראה נהדר! אנחנו נפרדים?

אם הכל היה נגמר כל כך מהר ופשוט, אז, כנראה, לא היה מאמר.

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

שאילתה פשוטה ותוכנית איתה

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

explain analyze verbose  
SELECT count(1)
FROM fdw_schema.table;

Aggregate  (cost=418383.23..418383.24 rows=1 width=8) (actual time=3857.198..3857.198 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..402376.14 rows=6402838 width=0) (actual time=4.874..3256.511 rows=6406868 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Remote SQL: SELECT NULL FROM fdw_schema.table
Planning time: 0.986 ms
Execution time: 3857.436 ms

שימוש במשפט VERBOSE מאפשר לנו לראות את השאילתה שתישלח לשרת המרוחק ואת תוצאותיה נקבל להמשך עיבוד (שורת RemoteSQL).

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

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';

Aggregate  (cost=577487.69..577487.70 rows=1 width=8) (actual time=27473.818..25473.819 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..577469.21 rows=7390 width=0) (actual time=31.369..25372.466 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 5046843
        Remote SQL: SELECT created_dt, is_active, meta FROM fdw_schema.table
Planning time: 0.665 ms
Execution time: 27474.118 ms

כאן טמונה הנקודה שעליך לשים לב אליה בעת כתיבת שאילתות. המסננים לא הועברו לשרת המרוחק, מה שאומר שכדי להפעיל אותו, Postgres שולף את כל 6 מיליון השורות על מנת לסנן אחר כך מקומית (Filter row) ולבצע צבירה. המפתח להצלחה הוא כתיבת שאילתה כך שהפילטרים יועברו למכונה המרוחקת, ואנו מקבלים ומצברים רק את השורות הדרושות.

זה קצת בוליאני

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

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active = True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta->>'source' = 'test';

Aggregate  (cost=508010.14..508010.15 rows=1 width=8) (actual time=19064.314..19064.314 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.00..507988.44 rows=8679 width=0) (actual time=33.035..18951.278 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: ((("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 3567989
        Remote SQL: SELECT created_dt, meta FROM fdw_schema.table WHERE (is_active)
Planning time: 0.834 ms
Execution time: 19064.534 ms

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

ראוי לציין כי המפעיל is שונה מהמפעיל = כי זה יכול לעבוד עם הערך Null. זה אומר ש לא נכון ישאיר את הערכים False ו-Null במסנן, ואילו != נכון ישאיר רק ערכי שקר. לכן, בעת החלפת המפעיל לא יש להעביר שני תנאים עם האופרטור OR למסנן, למשל, WHERE (קול != נכון) OR (קול הוא ריק).

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

חותמת זמן? הרץ

באופן כללי, לעתים קרובות אתה צריך להתנסות כיצד לכתוב בצורה נכונה בקשה הכוללת שרתים מרוחקים, ורק אז לחפש הסבר למה זה קורה. מעט מאוד מידע על כך ניתן למצוא באינטרנט. לכן, בניסויים מצאנו שמסנן תאריך קבוע טס לשרת המרוחק ברעש, אבל כשאנחנו רוצים להגדיר את התאריך בצורה דינמית, למשל, now() או CURRENT_DATE, זה לא קורה. בדוגמה שלנו, הוספנו מסנן כך שהעמודה create_at הכילה נתונים בדיוק עבור חודש אחד בעבר (בין CURRENT_DATE - INTERVAL '1 חודשים' ו-CURRENT_DATE - INTERVAL '7 חודשים'). מה עשינו במקרה הזה?

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') 
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta->>'source' = 'test';

Aggregate  (cost=306875.17..306875.18 rows=1 width=8) (actual time=4789.114..4789.115 rows=1 loops=1)
  Output: count(1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.02..306874.86 rows=105 width=0) (actual time=23.475..4681.419 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text))
        Rows Removed by Filter: 76934
        Remote SQL: SELECT is_active, meta FROM fdw_schema.table WHERE ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone))
Planning time: 0.703 ms
Execution time: 4789.379 ms

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

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

הבה נחזיר את מסנן התאריכים לערכו המקורי.

פרדי נגד Jsonb

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

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 month' 
AND CURRENT_DATE - INTERVAL '6 month'
AND meta @> '{"source":"test"}'::jsonb;

Aggregate  (cost=245463.60..245463.61 rows=1 width=8) (actual time=6727.589..6727.590 rows=1 loops=1)
  Output: count(1)
  ->  Foreign Scan on fdw_schema."table"  (cost=1100.00..245459.90 rows=1478 width=0) (actual time=16.213..6634.794 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)))
        Rows Removed by Filter: 619961
        Remote SQL: SELECT created_dt, is_active FROM fdw_schema.table WHERE ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.747 ms
Execution time: 6727.815 ms

במקום לסנן אופרטורים, עליך להשתמש בנוכחות של אופרטור אחד jsonb בשונה. 7 שניות במקום 29 המקוריות. עד כה זו האפשרות המוצלחת היחידה להעברת מסננים באמצעות jsonb לשרת מרוחק, אבל כאן חשוב לקחת בחשבון מגבלה אחת: אנחנו משתמשים בגרסה 9.6 של מסד הנתונים, אבל עד סוף אפריל אנחנו מתכננים להשלים את הבדיקות האחרונות ולעבור לגרסה 12. לאחר שנעדכן, נכתוב איך זה השפיע, כי יש די הרבה שינויים שיש להם הרבה תקווה: json_path, התנהגות חדשה של CTE, push down (קיים מאז גרסה 10). אני ממש רוצה לנסות את זה בקרוב.

גמור אותו

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

explain analyze verbose
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active = True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 month') 
AND created_dt <(SELECT CURRENT_DATE::timestamptz - INTERVAL '6 month')
AND meta @> '{"source":"test"}'::jsonb;

Aggregate  (cost=322041.51..322041.52 rows=1 width=8) (actual time=2278.867..2278.867 rows=1 loops=1)
  Output: count(1)
  InitPlan 1 (returns $0)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.010..0.010 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
  InitPlan 2 (returns $1)
    ->  Result  (cost=0.00..0.02 rows=1 width=8) (actual time=0.003..0.003 rows=1 loops=1)
          Output: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  Foreign Scan on fdw_schema."table"  (cost=100.02..322041.41 rows=25 width=0) (actual time=8.597..2153.809 rows=1360025 loops=1)
        Output: "table".id, "table".is_active, "table".meta, "table".created_dt
        Remote SQL: SELECT NULL FROM fdw_schema.table WHERE (is_active) AND ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone)) AND ((meta @> '{"source": "test"}'::jsonb))
Planning time: 0.820 ms
Execution time: 2279.087 ms

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

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

תודה לך על תשומת הלב! אשמח לשמוע שאלות, הערות וסיפורים על החוויות שלך בתגובות.

מקור: www.habr.com