Միկրոծառայությունների ճարտարապետությունը, ինչպես ամեն ինչ այս աշխարհում, ունի իր դրական և բացասական կողմերը: Որոշ գործընթացներ դրա հետ ավելի հեշտ են դառնում, մյուսները՝ ավելի դժվար։ Եվ հանուն փոփոխությունների արագության և ավելի լավ մասշտաբայնության, դուք պետք է զոհաբերեք: Դրանցից մեկը վերլուծության բարդությունն է: Եթե մոնոլիտում բոլոր գործառնական վերլուծությունները կարող են կրճատվել SQL հարցումների՝ վերածելով վերլուծական կրկնօրինակի, ապա բազմասերվիսային ճարտարապետության մեջ յուրաքանչյուր ծառայություն ունի իր տվյալների բազան և թվում է, որ մեկ հարցումը բավարար չէ (կամ գուցե դա կլինի): Նրանց համար, ովքեր հետաքրքրված են, թե ինչպես մենք լուծեցինք օպերատիվ վերլուծության խնդիրը մեր ընկերությունում և ինչպես սովորեցինք ապրել այս լուծումով, ողջունում ենք:
Ես Պավել Սիվաշն եմ, DomClick-ում ես աշխատում եմ թիմում, որը պատասխանատու է վերլուծական տվյալների պահեստի պահպանման համար: Պայմանականորեն, մեր գործունեությունը կարող է վերագրվել տվյալների ճարտարագիտությանը, բայց, ըստ էության, առաջադրանքների շրջանակը շատ ավելի լայն է: Կան ստանդարտ տվյալների ճարտարագիտական ETL / ELT, տվյալների վերլուծության գործիքների աջակցություն և հարմարեցում և սեփական գործիքների մշակում: Մասնավորապես, գործառնական հաշվետվությունների համար մենք որոշեցինք «ձևացնել», թե ունենք մոնոլիտ և վերլուծաբաններին տալ մեկ տվյալների բազա, որը կպարունակի նրանց անհրաժեշտ բոլոր տվյալները:
Ընդհանուր առմամբ մենք տարբեր տարբերակներ ենք դիտարկել։ Հնարավոր էր կառուցել լիարժեք շտեմարան, մենք նույնիսկ փորձեցինք, բայց, ճիշտն ասած, մենք չկարողացանք ընկերանալ տրամաբանության բավականին հաճախակի փոփոխություններով պահեստ կառուցելու և դրանում փոփոխություններ կատարելու բավականին դանդաղ գործընթացով ( եթե ինչ-որ մեկին հաջողվել է, գրեք մեկնաբանություններում, թե ինչպես): Դուք կարող եք վերլուծաբաններին ասել. «Տղե՛րք, սովորե՛ք պիթոն և գնացե՛ք վերլուծական գծեր», բայց սա լրացուցիչ հավաքագրման պահանջ է, և թվում էր, որ հնարավորության դեպքում դա պետք է խուսափել: Մենք որոշեցինք փորձել օգտագործել FDW (Foreign Data Wrapper) տեխնոլոգիան. իրականում սա ստանդարտ 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 msVERBOSE հայտարարության օգտագործումը թույլ է տալիս տեսնել հարցումը, որը կուղարկվի հեռավոր սերվերին, և որի արդյունքները մենք կստանանք հետագա մշակման համար (RemoteSQL տող):
Եկեք մի փոքր առաջ գնանք և մի քանի զտիչներ ավելացնենք մեր հարցմանը բուլյան դաշտ, մեկ մուտքով timestamp մեկ ընդմիջումով և մեկով 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!= Ճշմարիտ) ԿԱՄ (սույն կետը զրոյական է).
Բուլյան հասկացությամբ՝ առաջ գնալով: Միևնույն ժամանակ, եկեք ֆիլտրն ըստ բուլյան արժեքի վերադարձնենք իր սկզբնական ձևին, որպեսզի ինքնուրույն դիտարկենք այլ փոփոխությունների ազդեցությունը:
timestamptz? հզ
Ընդհանրապես, հաճախ պետք է փորձարկել, թե ինչպես ճիշտ գրել հարցում, որը ներառում է հեռավոր սերվերներ, և միայն դրանից հետո բացատրություն փնտրել, թե ինչու է դա տեղի ունենում: Այս մասին շատ քիչ տեղեկություններ կարելի է գտնել ինտերնետում: Այսպիսով, փորձերի ժամանակ մենք պարզեցինք, որ ֆիքսված ամսաթվի ֆիլտրը հարվածով թռչում է դեպի հեռավոր սերվեր, բայց երբ մենք ուզում ենք ամսաթիվը դինամիկ կերպով սահմանել, օրինակ, now() կամ 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 անգամ ավելի արագ:
Կրկին, այստեղ կարևոր է զգույշ լինել. ենթհարցման տվյալների տեսակը պետք է լինի նույնը, ինչ այն դաշտի տեսակը, որով մենք զտում ենք, հակառակ դեպքում պլանավորողը կորոշի, որ քանի որ տեսակները տարբեր են, և նախ անհրաժեշտ է ստանալ բոլորը. տվյալները և զտեք դրանք տեղական մակարդակում:
Եկեք վերադարձնենք զտիչն ըստ ամսաթվի իր սկզբնական արժեքին:
Ֆրեդին ընդդեմ. 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 վարքագիծ, push down (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-ի հետ, միշտ ստուգեք, թե արդյոք բոլոր զտիչները ուղարկված են հեռավոր սերվերին, և դուք երջանիկ կլինեք... Համենայն դեպս մինչև հասնեք միանալ տարբեր սերվերների աղյուսակների միջև: Բայց դա պատմություն է մեկ այլ հոդվածի համար:
Շնորհակալություն ուշադրության համար! Ես կցանկանայի լսել մեկնաբանություններում ձեր փորձառությունների մասին հարցեր, մեկնաբանություններ և պատմություններ:
Source: www.habr.com