สถาปัตยกรรมไมโครเซอร์วิสก็เหมือนกับทุกสิ่งในโลกนี้ มีทั้งข้อดีและข้อเสีย กระบวนการบางอย่างก็ง่ายขึ้นและบางกระบวนการก็ยากขึ้น และเพื่อความรวดเร็วของการเปลี่ยนแปลงและความสามารถในการขยายขนาดที่ดีขึ้น คุณต้องเสียสละ หนึ่งในนั้นคือความซับซ้อนที่เพิ่มขึ้นของการวิเคราะห์ หากการวิเคราะห์การปฏิบัติงานทั้งหมดสามารถลดลงเป็นการสืบค้น SQL ไปจนถึงแบบจำลองเชิงวิเคราะห์ได้ ดังนั้นในสถาปัตยกรรมแบบหลายบริการ แต่ละบริการจะมีฐานข้อมูลของตัวเอง และดูเหมือนว่าการสืบค้นครั้งเดียวไม่สามารถทำได้ (หรืออาจทำได้?) สำหรับผู้ที่สนใจว่าเราแก้ไขปัญหาการวิเคราะห์การดำเนินงานในบริษัทของเราอย่างไร และวิธีที่เราเรียนรู้ที่จะใช้ชีวิตกับโซลูชันนี้ ยินดีต้อนรับ

ฉันชื่อ Pavel Sivash ที่ DomClick ฉันทำงานในทีมที่รับผิดชอบในการดูแลรักษาคลังข้อมูลเชิงวิเคราะห์ ตามอัตภาพ กิจกรรมของเราสามารถจัดได้ว่าเป็นวิศวกรรมข้อมูล แต่ในความเป็นจริงแล้ว ขอบเขตของงานนั้นกว้างกว่ามาก มีมาตรฐาน ETL/ELT สำหรับวิศวกรรมข้อมูล การสนับสนุนและการปรับใช้เครื่องมือสำหรับการวิเคราะห์ข้อมูลและการพัฒนาเครื่องมือของคุณเอง โดยเฉพาะอย่างยิ่งสำหรับการรายงานการปฏิบัติงาน เราตัดสินใจที่จะ "แกล้งทำเป็น" ว่าเรามีฐานข้อมูลขนาดใหญ่และมอบฐานข้อมูลเดียวให้กับนักวิเคราะห์ซึ่งจะมีข้อมูลทั้งหมดที่พวกเขาต้องการ

โดยทั่วไปเราพิจารณาตัวเลือกที่แตกต่างกัน เป็นไปได้ที่จะสร้างพื้นที่เก็บข้อมูลที่ครบถ้วนสมบูรณ์ - เราได้ลองแล้ว แต่พูดตามตรง เราไม่สามารถรวมการเปลี่ยนแปลงตรรกะที่ค่อนข้างบ่อยเข้ากับกระบวนการที่ค่อนข้างช้าในการสร้างพื้นที่เก็บข้อมูลและทำการเปลี่ยนแปลงได้ (ถ้ามีคนประสบความสำเร็จ เขียนในความคิดเห็นว่าอย่างไร) เป็นไปได้ที่จะบอกนักวิเคราะห์ว่า: “พวกนาย เรียนรู้ Python และไปที่แบบจำลองเชิงวิเคราะห์” แต่นี่เป็นข้อกำหนดเพิ่มเติมสำหรับการสรรหาบุคลากร และดูเหมือนว่าควรหลีกเลี่ยงสิ่งนี้หากเป็นไปได้ เราตัดสินใจลองใช้เทคโนโลยี FDW (Foreign Data Wrapper) โดยพื้นฐานแล้วนี่คือ dblink มาตรฐานซึ่งอยู่ในมาตรฐาน SQL แต่มีอินเทอร์เฟซที่สะดวกกว่าในตัวมันเอง จากข้อมูลดังกล่าว เราได้ทำวิธีแก้ปัญหา ซึ่งในที่สุดก็เจอ และเราก็ตกลงตามนั้น รายละเอียดเป็นหัวข้อของบทความแยกต่างหากและอาจมากกว่าหนึ่งบทความเนื่องจากฉันต้องการพูดคุยมากมาย: จากการซิงโครไนซ์สคีมาฐานข้อมูลไปจนถึงการควบคุมการเข้าถึงและการลดความเป็นส่วนตัวของข้อมูลส่วนบุคคล นอกจากนี้ จำเป็นต้องจองล่วงหน้าว่าโซลูชันนี้ไม่สามารถทดแทนฐานข้อมูลและที่เก็บเชิงวิเคราะห์จริงได้ แต่จะแก้ปัญหาเฉพาะได้เท่านั้น

ที่ระดับบนสุดมีลักษณะดังนี้:

มีฐานข้อมูล PostgreSQL ที่ผู้ใช้สามารถจัดเก็บข้อมูลงานของตนได้ และที่สำคัญที่สุดคือ แบบจำลองเชิงวิเคราะห์ของบริการทั้งหมดเชื่อมต่อกับฐานข้อมูลนี้ผ่าน FDW ทำให้สามารถเขียนแบบสอบถามไปยังฐานข้อมูลหลายแห่งได้ และไม่สำคัญว่าคืออะไร: PostgreSQL, MySQL, MongoDB หรืออย่างอื่น (ไฟล์, API หากจู่ๆ ไม่มี wrapper ที่เหมาะสม คุณสามารถเขียนเองได้) ทุกอย่างดูดีมาก! เราเลิกกันเหรอ?

หากทุกอย่างจบลงอย่างรวดเร็วและง่ายดายก็คงจะไม่มีบทความ

สิ่งสำคัญคือต้องชัดเจนเกี่ยวกับวิธีที่ 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 ล้านแถวออกมาเพื่อกรองในเครื่อง (แถวตัวกรอง) และดำเนินการรวมกลุ่ม กุญแจสู่ความสำเร็จคือการเขียนแบบสอบถามเพื่อให้ตัวกรองถูกถ่ายโอนไปยังเครื่องระยะไกล และเราได้รับและรวมเฉพาะแถวที่จำเป็นเท่านั้น

นั่นเป็นเรื่องไร้สาระ

ด้วยฟิลด์บูลีนทุกอย่างก็ง่าย ในคำขอเดิม ปัญหาเกิดจากตัวดำเนินการ 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 (col != True) หรือ (col เป็นโมฆะ).

เราได้จัดการกับบูลีนแล้ว เรามาต่อกันดีกว่า ในตอนนี้ เราจะคืนตัวกรองบูลีนกลับไปเป็นรูปแบบดั้งเดิมเพื่อพิจารณาผลกระทบของการเปลี่ยนแปลงอื่นๆ อย่างอิสระ

การประทับเวลา? เฮิรตซ์

โดยทั่วไป คุณมักจะต้องทดลองวิธีเขียนคำขอที่เกี่ยวข้องกับเซิร์ฟเวอร์ระยะไกลอย่างถูกต้อง จากนั้นจึงมองหาคำอธิบายว่าเหตุใดจึงเกิดขึ้น ข้อมูลน้อยมากเกี่ยวกับเรื่องนี้สามารถพบได้บนอินเทอร์เน็ต ดังนั้น ในการทดลอง เราพบว่าตัวกรองวันที่แบบตายตัวส่งผลกระทบอย่างมากต่อเซิร์ฟเวอร์ระยะไกล แต่เมื่อเราต้องการตั้งค่าวันที่แบบไดนามิก เช่น now() หรือ CURRENT_DATE สิ่งนี้จะไม่เกิดขึ้น ในตัวอย่างของเรา เราได้เพิ่มตัวกรองเพื่อให้คอลัมน์ create_at มีข้อมูลในช่วง 1 เดือนที่ผ่านมาพอดี (ระหว่าง CURRENT_DATE - INTERVAL '7 เดือน' และ CURRENT_DATE - INTERVAL '6 เดือน') เราทำอะไรในกรณีนี้?

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 ไปยังเซิร์ฟเวอร์ระยะไกล

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 ใหม่, แบบพุชดาวน์ (มีมาตั้งแต่เวอร์ชัน 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 ให้ตรวจสอบเสมอว่าตัวกรองทั้งหมดถูกส่งไปยังเซิร์ฟเวอร์ระยะไกล แล้วคุณจะพอใจ... อย่างน้อยก็จนกว่าคุณจะได้เข้าร่วมระหว่างตารางจากเซิร์ฟเวอร์ที่แตกต่างกัน แต่นั่นเป็นเรื่องราวของบทความอื่น

ขอขอบคุณสำหรับความสนใจของคุณ! ฉันชอบที่จะได้ยินคำถาม ความคิดเห็น และเรื่องราวเกี่ยวกับประสบการณ์ของคุณในความคิดเห็น

ที่มา: will.com