मायक्रोसर्व्हिस आर्किटेक्चर, या जगातील प्रत्येक गोष्टीप्रमाणे, त्याचे फायदे आणि तोटे आहेत. काही प्रक्रिया त्यासह सुलभ होतात, तर काही अधिक कठीण. आणि बदलाच्या गतीसाठी आणि चांगल्या स्केलेबिलिटीसाठी, तुम्हाला त्याग करणे आवश्यक आहे. त्यापैकी एक विश्लेषणाची वाढती जटिलता आहे. जर मोनोलिथमध्ये सर्व ऑपरेशनल विश्लेषणे SQL क्वेरीमध्ये विश्लेषणात्मक प्रतिकृतीमध्ये कमी केली जाऊ शकतात, तर मल्टीसर्व्हिस आर्किटेक्चरमध्ये प्रत्येक सेवेचा स्वतःचा डेटाबेस असतो आणि असे दिसते की एक क्वेरी केली जाऊ शकत नाही (किंवा कदाचित ते होऊ शकते?). आम्ही आमच्या कंपनीतील ऑपरेशनल अॅनालिटिक्सची समस्या कशी सोडवली आणि आम्ही या सोल्यूशनसह जगणे कसे शिकलो याबद्दल स्वारस्य असलेल्यांसाठी - स्वागत आहे.

माझे नाव पावेल शिवाश आहे, DomClick येथे मी विश्लेषणात्मक डेटा वेअरहाऊस राखण्यासाठी जबाबदार असलेल्या टीममध्ये काम करतो. पारंपारिकपणे, आमच्या क्रियाकलापांना डेटा अभियांत्रिकी म्हणून वर्गीकृत केले जाऊ शकते, परंतु, प्रत्यक्षात, कार्यांची श्रेणी खूप विस्तृत आहे. डेटा अभियांत्रिकीसाठी ETL/ELT मानक आहेत, डेटा विश्लेषण आणि तुमच्या स्वतःच्या साधनांच्या विकासासाठी साधनांचे समर्थन आणि अनुकूलन. विशेषतः, ऑपरेशनल रिपोर्टिंगसाठी, आमच्याकडे एक मोनोलिथ आहे असे आम्ही "बसवण्याचे" ठरवले आणि विश्लेषकांना एक डेटाबेस द्या ज्यामध्ये त्यांना आवश्यक असलेला सर्व डेटा असेल.

सर्वसाधारणपणे, आम्ही विविध पर्यायांचा विचार केला. पूर्ण वाढीचे भांडार तयार करणे शक्य होते - आम्ही प्रयत्न देखील केले, परंतु, खरे सांगायचे तर, आम्ही भांडार तयार करण्याच्या आणि त्यात बदल करण्याच्या ऐवजी संथ प्रक्रियेसह तर्कशास्त्रातील बर्‍याच वेळा बदलांना एकत्र करू शकलो नाही (जर कोणी यशस्वी झाले तर , कसे ते टिप्पण्यांमध्ये लिहा). विश्लेषकांना सांगणे शक्य होते: "मुलांनो, अजगर शिका आणि विश्लेषणात्मक प्रतिकृतींवर जा," परंतु भरतीसाठी ही एक अतिरिक्त आवश्यकता आहे आणि असे दिसते की हे शक्य असल्यास टाळले पाहिजे. आम्ही FDW (फॉरेन डेटा रॅपर) तंत्रज्ञान वापरण्याचा प्रयत्न करण्याचा निर्णय घेतला: मूलत:, हे एक मानक dblink आहे, जे SQL मानकात आहे, परंतु त्याच्या स्वतःच्या अधिक सोयीस्कर इंटरफेससह. त्यावर आधारित, आम्ही एक उपाय केला, जो अखेरीस पकडला गेला आणि आम्ही त्यावर सेटल झालो. त्याचे तपशील हा एका स्वतंत्र लेखाचा विषय आहे, आणि कदाचित एकापेक्षा जास्त, कारण मला बरेच काही बोलायचे आहे: डेटाबेस स्कीमा सिंक्रोनाइझ करण्यापासून ते प्रवेश नियंत्रण आणि वैयक्तिक डेटाचे वैयक्तिकरण. हे आरक्षण करणे देखील आवश्यक आहे की हे समाधान वास्तविक विश्लेषणात्मक डेटाबेस आणि भांडारांची जागा नाही; ते केवळ विशिष्ट समस्येचे निराकरण करते.

शीर्ष स्तरावर असे दिसते:

एक 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 दशलक्ष पंक्ती बाहेर काढते जेणेकरून नंतर स्थानिक पातळीवर फिल्टर करा (फिल्टर पंक्ती) आणि एकत्रीकरण करा. यशाची गुरुकिल्ली म्हणजे क्वेरी लिहिणे जेणेकरुन फिल्टर रिमोट मशीनवर हस्तांतरित केले जातील आणि आम्ही फक्त आवश्यक पंक्ती प्राप्त करतो आणि एकत्रित करतो.

ते काही बूलेनशीट आहे

बुलियन फील्डसह सर्वकाही सोपे आहे. मूळ विनंतीमध्ये, समस्या ऑपरेटरमुळे होती 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 ऑपरेटरसह दोन अटी फिल्टरला पास केल्या पाहिजेत, उदाहरणार्थ, कुठे (col != खरे) किंवा (col null आहे).

आम्ही बुलियनचा सामना केला आहे, चला पुढे जाऊया. आत्तासाठी, इतर बदलांच्या परिणामाचा स्वतंत्रपणे विचार करण्यासाठी बुलियन फिल्टरला त्याच्या मूळ स्वरूपात परत करू.

timestamptz hz

सर्वसाधारणपणे, रिमोट सर्व्हरचा समावेश असलेली विनंती योग्यरित्या कशी लिहायची याचा तुम्हाला अनेकदा प्रयोग करावा लागतो आणि त्यानंतरच हे का घडते याचे स्पष्टीकरण शोधा. इंटरनेटवर याबद्दल फारच कमी माहिती मिळू शकते. तर, प्रयोगांमध्ये आम्हाला आढळले की एक निश्चित तारीख फिल्टर रिमोट सर्व्हरवर धमाकेदारपणे उडतो, परंतु जेव्हा आम्हाला डायनॅमिकली तारीख सेट करायची असते, उदाहरणार्थ, now() किंवा CURRENT_DATE, तेव्हा असे होत नाही. आमच्या उदाहरणात, आम्ही एक फिल्टर जोडला आहे जेणेकरून भूतकाळातील 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

सर्वसाधारणपणे, बुलियन फील्ड आणि तारखांनी आधीच आमच्या क्वेरीला पुरेसा वेग दिला आहे, परंतु आणखी एक डेटा प्रकार शिल्लक होता. खरे सांगायचे तर, याद्वारे फिल्टरिंगची लढाई अद्याप संपलेली नाही, जरी येथेही यश आहे. तर, अशा प्रकारे आम्ही फिल्टर पास करण्यात व्यवस्थापित केले 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

मायक्रोसर्व्हिस आर्किटेक्चरमधील ऑपरेशनल अॅनालिटिक्स: मदत आणि प्रॉम्प्ट पोस्टग्रेस FDW

एक साधी क्वेरी आणि त्यासोबत एक योजना

ते काही बूलेनशीट आहे

timestamptz hz

फ्रेडी वि. Jsonb

संपवून टाक त्याला

एक टिप्पणी जोडा Отменить ответ