لهندسة الخدمات الصغيرة ، مثل كل شيء في هذا العالم ، إيجابياتها وسلبياتها. تصبح بعض العمليات معه أسهل ، والبعض الآخر أكثر تعقيدًا. ومن أجل سرعة التغيير وتحسين قابلية التوسع ، يجب تقديم التضحيات. واحد منهم هو تعقيد التحليلات. إذا كان بالإمكان اختزال جميع التحليلات التشغيلية في مجموعة متجانسة إلى استعلامات SQL لنسخة متماثلة تحليلية ، فإن لكل خدمة في بنية متعددة الخدمات قاعدتها الخاصة ويبدو أنه لا يمكن الاستغناء عن استعلام واحد (أو يمكن الاستغناء عنه؟). بالنسبة لأولئك المهتمين بكيفية حل مشكلة التحليلات التشغيلية في شركتنا وكيف تعلمنا التعايش مع هذا الحل - مرحبًا.
طلب بسيط وخطة معه
لإظهار كيفية تنفيذ 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 جميع الخطوط الستة ملايين ، ثم فقط للتصفية محليًا (خط التصفية) وأداء التجميع. مفتاح النجاح هو كتابة طلب حتى يتم نقل المرشحات إلى الآلة البعيدة ، ولا نتلقى ونجمع سوى الصفوف اللازمة.هذا بعض booleanshit
مع الحقول المنطقية ، كل شيء بسيط. في الطلب الأصلي ، كانت المشكلة بسبب العبارة 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 ثانية.ومن الجدير بالذكر أن هو المشغل يختلف من = عامل في أنه يمكن أن تعمل مع قيمة خالية. هذا يعني أن هذا ليس صحيحًا في الفلتر سيغادر False و Null ، بينما ! = True سيترك False فقط. لذلك ، عند استبدال عامل التشغيل ليس ، يجب تمرير شرطين باستخدام عامل التشغيل OR إلى عامل التصفية ، على سبيل المثال ، WHERE (col! = True) OR (col null) .مع فرز منطقي ، انتقل. في هذه الأثناء ، قم بإرجاع عامل التصفية حسب القيمة المنطقية إلى شكله الأصلي ، للنظر بشكل مستقل في تأثير التغييرات الأخرى.timestamptz؟ هرتز
بشكل عام ، غالبًا ما يتعين على المرء تجربة كيفية كتابة استعلام يتضمن خوادم بعيدة ، وعندها فقط يبحث عن تفسير لسبب حدوث ذلك. يمكن العثور على معلومات قليلة جدًا حول هذا على الإنترنت. لذلك ، في التجارب ، وجدنا أن المرشح حسب تاريخ ثابت يطير إلى الخادم البعيد مع حدوث ضجة ، ولكن عندما نريد تعيين التاريخ ديناميكيًا ، على سبيل المثال ، الآن () أو CURRENT_DATE ، لا يحدث هذا. في مثالنا ، أضفنا فلترًا بحيث يحتوي عمود create_at على بيانات لمدة شهر واحد بالضبط في الماضي (BETWEEN CURRENT_DATE - INTERVAL '7 month' AND CURRENT_DATE - INTERVAL '6 month'). ماذا فعلنا في هذه الحالة؟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 الجديد ، الضغط لأسفل (موجود من الإصدار 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
نعم ، يبدو الطلب أكثر تعقيدًا ، فهو عبارة عن لوحة قسرية ، ولكن سرعة التنفيذ هي ثانيتان ، أي أسرع بعشر مرات! ونحن نتحدث عن استعلام بسيط عن مجموعة بيانات صغيرة نسبيًا. في الطلبات الحقيقية ، تلقينا نموًا يصل إلى عدة مئات من المرات.للتلخيص: إذا كنت تستخدم PostgreSQL مع FDW ، فتأكد دائمًا من إرسال جميع المرشحات إلى الخادم البعيد ، وستكون سعيدًا ... على الأقل حتى تصل إلى الصلات بين الجداول من خوادم مختلفة. لكن هذه قصة مقال آخر.شكرا للانتباه! يسعدني سماع أسئلة وتعليقات وقصص عن تجربتك في التعليقات.