ProHoster > بلاګ > اداره > د مایکرو سرویس جوړښت کې عملیاتي تحلیلونه: مرسته او پرامپټ پوسټګریس FDW

د مایکرو سرویس جوړښت کې عملیاتي تحلیلونه: مرسته او پرامپټ پوسټګریس FDW

د مایکرو سرویس معماري ، لکه پدې نړۍ کې د هرڅه په څیر ، خپلې ګټې او زیانونه لري. ځینې ​​پروسې د دې سره اسانه کیږي، نور ډیر ستونزمن. او د بدلون سرعت او غوره پیمانه کولو لپاره ، تاسو اړتیا لرئ قرباني ورکړئ. یو له دوی څخه د تحلیلونو مخ په زیاتیدونکي پیچلتیا ده. که په یو واحد کې ټول عملیاتي تحلیلونه د تحلیلي نقل لپاره د SQL پوښتنو ته راټیټ شي ، نو بیا په څو خدماتو جوړښت کې هر خدمت خپل ډیټابیس لري او داسې بریښي چې یوه پوښتنه نشي ترسره کیدی (یا شاید دا کیدی شي؟). د هغو کسانو لپاره چې لیوالتیا لري چې څنګه موږ په خپل شرکت کې د عملیاتي تحلیلونو ستونزه حل کړه او موږ څنګه د دې حل سره ژوند کول زده کړل - ښه راغلاست.

د مایکرو سرویس جوړښت کې عملیاتي تحلیلونه: مرسته او پرامپټ پوسټګریس FDW
زما نوم پاول سیواش دی، په DomClick کې زه په یوه ټیم کې کار کوم چې د تحلیلي معلوماتو ګودام ساتلو مسولیت لري. په دودیز ډول، زموږ فعالیتونه د ډیټا انجینرۍ په توګه طبقه بندي کیدی شي، مګر، په حقیقت کې، د دندو لړۍ خورا پراخه ده. د ډیټا انجینرۍ لپاره د ETL/ELT معیارونه شتون لري ، د معلوماتو تحلیل او ستاسو د خپلو وسیلو پراختیا لپاره د وسیلو ملاتړ او موافقت. په ځانګړې توګه، د عملیاتي راپور ورکولو لپاره، موږ پریکړه وکړه چې "بهیر" وکړو چې موږ یو واحد لرو او شنونکو ته یو ډیټابیس ورکړو چې ټول هغه معلومات ولري چې دوی ورته اړتیا لري.

په عموم کې، موږ مختلف انتخابونه په پام کې نیولي دي. دا ممکنه وه چې یو بشپړ ذخیره جوړه کړو - موږ حتی هڅه وکړه، مګر، په ریښتیا سره، موږ ونه توانیدلو چې په منطق کې په کافي اندازه بدلونونه د ذخیره کولو د جوړولو او په هغې کې د بدلونونو د سست پروسې سره یوځای کړو (که څوک بریالی شي. په نظرونو کې ولیکئ چې څنګه). دا ممکنه وه چې شنونکو ته ووایاست: "هلکانو، پیتون زده کړئ او تحلیلي نقلونو ته لاړ شئ،" مګر دا د استخدام لپاره اضافي اړتیا ده، او داسې بریښي چې د امکان په صورت کې باید له دې څخه ډډه وشي. موږ پریکړه وکړه چې د FDW (بهرني ډیټا ریپر) ټیکنالوژۍ کارولو هڅه وکړو: په اصل کې ، دا یو معیاري dblink دی ، کوم چې د SQL معیاري کې دی ، مګر د خپل خورا خورا اسانه انٹرفیس سره. د دې پر بنسټ، موږ یو حل جوړ کړ، کوم چې په پای کې ونیول شو، او موږ په دې باندې بسیا شو. د دې توضیحات د جلا مقالې موضوع ده ، او شاید له یو څخه ډیر ، ځکه چې زه غواړم د ډیرو په اړه وغږیږم: د ډیټابیس سکیمونو همغږي کولو څخه د لاسرسي کنټرول او د شخصي معلوماتو بې ځایه کولو پورې. دا هم اړینه ده چې ریزرویشن وکړو چې دا حل د ریښتیني تحلیلي ډیټابیسونو او ذخیره کولو ځای نه دی؛ دا یوازې یوه ځانګړې ستونزه حل کوي.

په لوړ پوړ کې دا داسې ښکاري:

د مایکرو سرویس جوړښت کې عملیاتي تحلیلونه: مرسته او پرامپټ پوسټګریس FDW
دلته د PostgreSQL ډیټابیس شتون لري چیرې چې کارونکي کولی شي خپل کاري ډیټا ذخیره کړي ، او خورا مهم ، د ټولو خدماتو تحلیلي نقلونه د FDW له لارې دې ډیټابیس سره وصل دي. دا دا ممکنه کوي چې څو ډیټابیسونو ته یوه پوښتنه ولیکئ، او دا مهمه نده چې دا څه دي: PostgreSQL، MySQL، MongoDB یا بل څه (فایل، API، که ناڅاپه مناسب ریپر شتون نلري، تاسو کولی شئ خپل ځان ولیکئ). ښه، هرڅه ښه ښکاري! ایا موږ مات یو؟

که هر څه په چټکۍ او ساده توګه پای ته ورسیږي، نو شاید، یو مقاله به نه وي.

دا مهمه ده چې روښانه شي چې څنګه پوسټګریس د لیرې پرتو سرورونو غوښتنې پروسس کوي. دا منطقي ښکاري، مګر ډیری وختونه خلک دې ته پام نه کوي: پوسټګریس غوښتنه په هغو برخو ویشي چې په ریموټ سرورونو کې په خپلواکه توګه اجرا کیږي، دا ډاټا راټولوي، او پخپله وروستۍ محاسبه ترسره کوي، نو د پوښتنو اجرا کولو سرعت به په پراخه کچه تکیه وکړي. دا څنګه لیکل شوی. دا هم باید په پام کې ونیول شي: کله چې ډاټا د ریموټ سرور څخه راځي، دا نور شاخصونه نلري، هیڅ شی شتون نلري چې د مهالویش سره مرسته وکړي، له همدې امله، یوازې موږ کولی شو د هغه سره مرسته او مشوره وکړو. او دا په حقیقت کې هغه څه دي چې زه یې په اړه په ډیر تفصیل سره خبرې کول غواړم.

یو ساده پوښتنه او د هغې سره یو پلان

د دې ښودلو لپاره چې پوسټګریس څنګه په ریموټ سرور کې د 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

دا هغه ځای دی چې تاسو ورته اړتیا لرئ د پوښتنو لیکلو پرمهال پاملرنه وکړئ. فلټرونه ریموټ سرور ته ندي لیږدول شوي ، پدې معنی چې د دې اجرا کولو لپاره ، پوسټګریس ټول 6 ملیون قطارونه راوباسي ترڅو بیا په محلي ډول فلټر کړي (فلټر قطار) او راټولول ترسره کړي. د بریالیتوب کلیدي د پوښتنې لیکل دي ترڅو فلټرونه ریموټ ماشین ته لیږدول کیږي، او موږ یوازې اړین قطارونه ترلاسه کوو او راټولوو.

دا یو څه 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 ثانیو پورې کم شوی.

د یادولو وړ ده چې چلونکی is د چلونکي څخه توپیر لري = ځکه چې دا کولی شي د Null ارزښت سره کار وکړي. دا پدې مانا ده ریښتیا نه ده په فلټر کې به False او Null ارزښتونه پریږدي، پداسې حال کې چې != رښتیا یوازې غلط ارزښتونه پریږدي. له همدې امله، کله چې آپریټر بدل کړئ نه دی د OR آپریټر سره دوه شرایط باید فلټر ته انتقال شي، د بیلګې په توګه، چیرته (کول! = ریښتیا) یا (کول نال دی).

موږ د بولین سره معامله کړې، راځئ چې پرمخ لاړ شو. د اوس لپاره، راځئ چې د بولین فلټر بیرته خپل اصلي بڼه ته واستوو ترڅو په خپلواکه توګه د نورو بدلونونو اغیز په پام کې ونیول شي.

timestamptz؟ hz

په عموم کې ، تاسو ډیری وختونه تجربه کوئ چې څنګه په سمه توګه یوه غوښتنه ولیکئ چې ریموټ سرورونه پکې شامل وي ، او یوازې بیا د وضاحت لپاره وګورئ چې ولې دا پیښیږي. د دې په اړه ډیر لږ معلومات په انټرنیټ کې موندل کیدی شي. نو، په تجربو کې موږ وموندله چې د ټاکل شوي نیټې فلټر د بنګ سره ریموټ سرور ته الوتنه کوي، مګر کله چې موږ غواړو نیټه په متحرک ډول تنظیم کړو، د بیلګې په توګه، اوس() یا CURRENT_DATE، دا نه کیږي. زموږ په مثال کې، موږ یو فلټر اضافه کړ ترڅو د جوړ شوي_at کالم په تیرو وختونو کې دقیقا د 1 میاشتې لپاره ډاټا ولري (BETWEEN 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 ځله ګړندۍ شوه!

یوځل بیا ، دلته احتیاط اړین دی: په فرعي پوښتنو کې د ډیټا ډول باید د هغه ساحې سره ورته وي چې موږ یې فلټر کوو ، که نه نو پلان جوړونکی به پریکړه وکړي چې څنګه چې ډولونه مختلف دي ، نو اړینه ده چې لومړی ټول ترلاسه کړئ. ډاټا او په محلي ډول یې فلټر کړئ.

راځئ چې د نیټې فلټر خپل اصلي ارزښت ته راستون کړو.

فریډي 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 ثانیې دی ، کوم چې له 10 ځله ډیر ګړندی دی! او موږ د نسبتا کوچني ډیټا سیټ پروړاندې د ساده پوښتنې په اړه خبرې کوو. په ریښتیني غوښتنو کې ، موږ تر څو سوه ځله زیاتوالی ترلاسه کړ.

د لنډیز لپاره: که تاسو د FDW سره PostgreSQL کاروئ، تل وګورئ چې ټول فلټرونه ریموټ سرور ته لیږل شوي، او تاسو به خوشحاله شئ ... لږترلږه تر هغه چې تاسو د مختلفو سرورونو څخه د میزونو ترمنځ یوځای کیدو ته ورسیږئ. مګر دا د بلې مقالې لپاره کیسه ده.

له پاملرنې څخه مو مننه! زه غواړم په نظرونو کې ستاسو د تجربو په اړه پوښتنې، تبصرې او کیسې واورم.

سرچینه: www.habr.com

Add a comment