بنية الخدمات المصغرة ، مثل كل شيء في هذا العالم ، لها مزاياها وعيوبها. تصبح بعض العمليات أسهل معها ، والبعض الآخر أكثر صعوبة. ومن أجل سرعة التغيير وقابلية التوسع بشكل أفضل ، تحتاج إلى تقديم تضحيات. واحد منهم هو تعقيد التحليلات. إذا كان من الممكن اختزال جميع التحليلات التشغيلية في وحدة متجانسة إلى استعلامات SQL إلى نسخة متماثلة تحليلية ، فعندئذٍ في بنية الخدمات المتعددة ، يكون لكل خدمة قاعدة بيانات خاصة بها ويبدو أن استعلامًا واحدًا غير كافٍ (أو ربما يكون كذلك؟). بالنسبة لأولئك المهتمين بكيفية حلنا لمشكلة التحليلات التشغيلية في شركتنا وكيف تعلمنا التعايش مع هذا الحل - مرحبًا بكم.
اسمي بافل سيفاش ، في DomClick أعمل في فريق مسؤول عن صيانة مستودع البيانات التحليلية. تقليديًا ، يمكن أن تُعزى أنشطتنا إلى هندسة البيانات ، ولكن في الواقع ، نطاق المهام أوسع بكثير. توجد هندسة بيانات قياسية ETL / ELT ، ودعم وتكييف أدوات تحليل البيانات وتطوير أدواتها الخاصة. على وجه الخصوص ، بالنسبة لإعداد التقارير التشغيلية ، قررنا "التظاهر" بأن لدينا وحدة متراصة ومنح المحللين قاعدة بيانات واحدة تحتوي على جميع البيانات التي يحتاجون إليها.
بشكل عام ، نظرنا في خيارات مختلفة. كان من الممكن بناء مستودع كامل - حتى أننا حاولنا ، ولكن بصراحة ، لم نتمكن من تكوين صداقات مع تغييرات متكررة إلى حد ما في المنطق مع عملية بطيئة نوعًا ما لبناء مستودع وإجراء تغييرات عليه ( إذا نجح شخص ما ، فاكتب في التعليقات كيف). يمكنك أن تقول للمحللين: "أيها الرجال ، تعلموا لغة بيثون واذهبوا إلى الخطوط التحليلية" ، لكن هذا مطلب توظيف إضافي ، ويبدو أنه يجب تجنب ذلك إن أمكن. قررنا محاولة استخدام تقنية FDW (غلاف البيانات الأجنبية): في الواقع ، هذا هو 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 جميع الصفوف الستة ملايين من أجل التصفية محليًا لاحقًا (خط الفلتر) وتنفيذ التجميع. مفتاح النجاح هو كتابة استعلام بحيث يتم نقل المرشحات إلى الآلة البعيدة ، ولا نتلقى ونجمع الصفوف الضرورية فقط.
هذا بعض منطقية
مع الحقول المنطقية ، كل شيء بسيط. في الاستعلام الأصلي ، كانت المشكلة بسبب عامل التشغيل 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 يختلف عن المشغل = الذي يمكنه العمل مع القيمة الخالية. هذا يعني انه ليس صحيحا في الفلتر سيترك القيم False و Null ، بينما ! = صحيح لن يترك سوى القيم الخاطئة. لذلك ، عند استبدال المشغل ليس يجب عليك تمرير شرطين للمرشح باستخدام عامل التشغيل OR ، على سبيل المثال ، أين (العمود! = صحيح) أو (العمود فارغ).
مع منطقية برزت ، المضي قدما. في غضون ذلك ، دعنا نعيد عامل التصفية حسب القيمة المنطقية إلى شكله الأصلي من أجل النظر بشكل مستقل في تأثير التغييرات الأخرى.
timestamptz؟ هرتز
بشكل عام ، غالبًا ما يتعين عليك تجربة كيفية كتابة استعلام بشكل صحيح يتضمن خوادم بعيدة ، وعندها فقط تبحث عن تفسير لسبب حدوث ذلك. يمكن العثور على معلومات قليلة جدًا حول هذا الأمر على الإنترنت. لذلك ، في التجارب ، وجدنا أن مرشح التاريخ الثابت ينتقل إلى خادم بعيد مع إحداث ضجة ، ولكن عندما نريد تعيين التاريخ ديناميكيًا ، على سبيل المثال ، الآن () أو CURRENT_DATE ، فإن هذا لا يحدث. في مثالنا ، أضفنا عامل تصفية بحيث يحتوي العمود create_at على بيانات لمدة شهر واحد بالضبط في الماضي (BETWEEN CURRENT_DATE - INTERVAL '1 month' AND CURRENT_DATE - INTERVAL '7 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 مرات!
مرة أخرى ، من المهم توخي الحذر هنا: يجب أن يكون نوع البيانات في الاستعلام الفرعي هو نفسه نوع الحقل الذي نقوم بالتصفية من خلاله ، وإلا سيقرر المخطط ذلك نظرًا لأن الأنواع مختلفة ومن الضروري أولاً الحصول على كل البيانات وتصفيتها محليًا.
دعنا نعيد الفلتر حسب التاريخ إلى قيمته الأصلية.
فريدي vs. 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نعم ، يبدو الاستعلام أكثر تعقيدًا ، فهو سعر إجباري ، لكن سرعة التنفيذ تبلغ ثانيتين ، وهي أسرع بأكثر من 2 مرات! ونحن نتحدث عن استعلام بسيط حول مجموعة صغيرة نسبيًا من البيانات. بناءً على الطلبات الحقيقية ، تلقينا زيادة تصل إلى عدة مئات من المرات.
لتلخيص ذلك: إذا كنت تستخدم PostgreSQL مع FDW ، فتحقق دائمًا من إرسال جميع المرشحات إلى الخادم البعيد وستكون سعيدًا ... على الأقل حتى تصل بين الجداول من خوادم مختلفة. لكن هذه قصة لمقال آخر.
شكرًا لكم على اهتمامكم! أحب سماع الأسئلة والتعليقات والقصص حول تجاربك في التعليقات.
المصدر: www.habr.com