Mikropakalpojumu arhitektÅ«rai, tÄpat kÄ visam Å”ajÄ pasaulÄ, ir savi plusi un mÄ«nusi. Daži procesi ar to kļūst vieglÄki, citi grÅ«tÄki. Un, lai nodroÅ”inÄtu pÄrmaiÅu Ätrumu un labÄku mÄrogojamÄ«bu, jums ir jÄnes upuri. Viens no tiem ir analÄ«tikas sarežģītÄ«ba. Ja monolÄ«tÄ visu operacionÄlo analÄ«zi var reducÄt lÄ«dz SQL vaicÄjumiem lÄ«dz analÄ«tiskai replikai, tad daudzpakalpojumu arhitektÅ«rÄ katram pakalpojumam ir sava datu bÄze un Ŕķiet, ka ar vienu vaicÄjumu nepietiek (vai varbÅ«t arÄ« pietiks?). Tiem, kas interesÄjas par to, kÄ mÄs savÄ uzÅÄmumÄ atrisinÄjÄm operatÄ«vÄs analÄ«tikas problÄmu un kÄ mÄs iemÄcÄ«jÄmies sadzÄ«vot ar Å”o risinÄjumu - laipni lÅ«gti.
Mani sauc PÄvels SivaÅ”s. DomClick es strÄdÄju komandÄ, kas ir atbildÄ«ga par analÄ«tisko datu noliktavas uzturÄÅ”anu. TradicionÄli mÅ«su darbÄ«bas var attiecinÄt uz datu inženieriju, taÄu patiesÄ«bÄ uzdevumu loks ir daudz plaÅ”Äks. Ir standarta datu inženierijas ETL / ELT, datu analÄ«zes rÄ«ku atbalsts un pielÄgoÅ”ana un savu rÄ«ku izstrÄde. Jo Ä«paÅ”i operatÄ«vo pÄrskatu sniegÅ”anai mÄs nolÄmÄm āizliktiesā, ka mums ir monolÄ«ts, un dot analÄ«tiÄ·iem vienu datu bÄzi, kurÄ bÅ«s visi nepiecieÅ”amie dati.
KopumÄ mÄs izskatÄ«jÄm dažÄdas iespÄjas. PilnvÄrtÄ«gu repozitoriju bija iespÄjams uzbÅ«vÄt - mÄs pat mÄÄ£inÄjÄm, bet, godÄ«gi sakot, ar diezgan lÄnu repozitorija veidoÅ”anas un izmaiÅu veikÅ”anas procesu nevarÄjÄm sadraudzÄties ar diezgan biežÄm loÄ£ikas izmaiÅÄm ( ja kÄdam izdevÄs, rakstiet komentÄros kÄ). JÅ«s varÄtu teikt analÄ«tiÄ·iem: "PuiÅ”i, mÄcieties pitonu un dodieties uz analÄ«tiskajÄm lÄ«nijÄm", taÄu tÄ ir papildu prasÄ«ba darbÄ pieÅemÅ”anai, un Ŕķita, ka no tÄ, ja iespÄjams, vajadzÄtu izvairÄ«ties. MÄs nolÄmÄm izmÄÄ£inÄt FDW (Foreign Data Wrapper) tehnoloÄ£iju: patiesÄ«bÄ Å”Ä« ir standarta dblink, kas ir SQL standartÄ, bet ar daudz ÄrtÄku saskarni. Pamatojoties uz to, mÄs pieÅÄmÄm lÄmumu, kas galu galÄ iesakÅojÄs, mÄs pie tÄ pieÅÄmÄmies. SÄ«kÄka informÄcija par to ir atseviŔķa raksta tÄma un varbÅ«t vairÄk nekÄ viena, jo es vÄlos runÄt par daudz ko: no datu bÄzes shÄmas sinhronizÄcijas lÄ«dz piekļuves kontrolei un personas datu depersonalizÄcijai. JÄÅem vÄrÄ arÄ« tas, ka Å”is risinÄjums neaizstÄj reÄlas analÄ«tiskÄs datu bÄzes un repozitorijus, tas tikai atrisina konkrÄtu problÄmu.
AugÅ”ÄjÄ lÄ«menÄ« tas izskatÄs Å”Ädi:
Ir PostgreSQL datu bÄze, kurÄ lietotÄji var glabÄt savus darba datus, un pats galvenais, visu pakalpojumu analÄ«tiskÄs kopijas ir savienotas ar Å”o datu bÄzi, izmantojot FDW. Tas ļauj rakstÄ«t vaicÄjumu vairÄkÄm datu bÄzÄm, un nav svarÄ«gi, kas tas ir: PostgreSQL, MySQL, MongoDB vai kaut kas cits (fails, API, ja pÄkÅ”Åi nav piemÄrota iesaiÅojuma, varat rakstÄ«t savu). Nu, Ŕķiet, ka viss ir lieliski! IzŔķirties?
Ja viss beigtos tik Ätri un vienkÄrÅ”i, tad, iespÄjams, raksts nepastÄvÄtu.
Ir svarÄ«gi skaidri saprast, kÄ Postgres apstrÄdÄ pieprasÄ«jumus attÄliem serveriem. Tas Ŕķiet loÄ£iski, taÄu bieži vien cilvÄki tam nepievÄrÅ” uzmanÄ«bu: postgres sadala vaicÄjumu daļÄs, kas tiek izpildÄ«tas neatkarÄ«gi attÄlos serveros, apkopo Å”os datus un pats veic gala aprÄÄ·inus, tÄpÄc vaicÄjuma izpildes Ätrums lielÄ mÄrÄ bÅ«s atkarÄ«gs no tÄ, kÄ tas ir rakstÄ«ts. JÄpiebilst arÄ«: kad dati nÄk no attÄlÄ servera, tiem vairs nav indeksu, plÄnotÄjam nekas nepalÄ«dzÄs, tÄpÄc palÄ«dzÄt un ieteikt varam tikai mÄs paÅ”i. Un par to es gribu runÄt sÄ«kÄk.
VienkÄrÅ”s pieprasÄ«jums un plÄns ar to
Lai parÄdÄ«tu, kÄ Postgres vaicÄ 6 miljonu rindu tabulu attÄlÄ serverÄ«, apskatÄ«sim vienkÄrÅ”u plÄnu.
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 priekÅ”raksta izmantoÅ”ana ļauj redzÄt vaicÄjumu, kas tiks nosÅ«tÄ«ts uz attÄlo serveri un kura rezultÄtus saÅemsim tÄlÄkai apstrÄdei (RemoteSQL virkne).
Dosimies nedaudz tÄlÄk un pievienosim savam vaicÄjumam vairÄkus filtrus: pa vienam bÅ«la lauks, pa vienam pÄc ieraksta laikspiedolu intervÄlÄ un pa vienam 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
Å eit ir Ä«stais brÄ«dis, kam jÄpievÄrÅ” uzmanÄ«ba, rakstot vaicÄjumus. Filtri netika pÄrsÅ«tÄ«ti uz attÄlo serveri, kas nozÄ«mÄ, ka, lai to izpildÄ«tu, postgres izvelk visus 6 miljonus rindu, lai filtrÄtu lokÄli (rinda Filtrs) un vÄlÄk veiktu apkopoÅ”anu. PanÄkumu atslÄga ir uzrakstÄ«t vaicÄjumu, lai filtri tiktu pÄrsÅ«tÄ«ti uz attÄlo maŔīnu, un mÄs saÅemtu un apkopotu tikai nepiecieÅ”amÄs rindas.
Tas ir kaut kÄds bÅ«liÅÅ”
Ar BÅ«la laukiem viss ir vienkÄrÅ”i. SÄkotnÄjÄ vaicÄjumÄ problÄmu radÄ«ja operators is. Ja mÄs to aizstÄjam ar =, tad iegÅ«stam Å”Ädu rezultÄtu:
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
KÄ redzat, filtrs aizlidoja uz attÄlo serveri, un izpildes laiks tika samazinÄts no 27 lÄ«dz 19 sekundÄm.
JÄpiebilst, ka operators is atŔķiras no operatora = tas, kas var darboties ar Null vÄrtÄ«bu. Tas nozÄ«mÄ, ka nav PatiesÄ«ba filtrÄ atstÄs vÄrtÄ«bas False un Null, kamÄr != TaisnÄ«ba atstÄs tikai Viltus vÄrtÄ«bas. TÄpÄc, nomainot operatoru ir ne jums ir jÄnodod filtram divi nosacÄ«jumi ar operatoru VAI, piemÄram, WHERE (kolonna != patiesa) VAI (kola ir nulle).
Ar BÅ«la izdomÄtu, dodamies tÄlÄk. TikmÄr atgriezÄ«sim filtru pÄc BÅ«la vÄrtÄ«bas sÄkotnÄjÄ formÄ, lai neatkarÄ«gi apsvÄrtu citu izmaiÅu ietekmi.
laika zīmogs? hz
Parasti bieži vien ir jÄeksperimentÄ, kÄ pareizi uzrakstÄ«t vaicÄjumu, kas ietver attÄlos serverus, un tikai tad jÄmeklÄ skaidrojums, kÄpÄc tas notiek. InternetÄ par to var atrast ļoti maz informÄcijas. TÄtad eksperimentos mÄs noskaidrojÄm, ka fiksÄta datuma filtrs ar triecienu lido uz attÄlo serveri, taÄu, ja mÄs vÄlamies iestatÄ«t datumu dinamiski, piemÄram, now() vai CURRENT_DATE, tas nenotiek. MÅ«su piemÄrÄ esam pievienojuÅ”i filtru, lai kolonnÄ Created_at bÅ«tu dati par tieÅ”i 1 mÄnesi pagÄtnÄ (STARP CURRENT_DATE ā INTERVAL '7 mÄneÅ”i' UN CURRENT_DATE - INTERVAL '6 mÄneÅ”i'). Ko mÄs darÄ«jÄm Å”ajÄ gadÄ«jumÄ?
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
MÄs mudinÄjÄm plÄnotÄju apakÅ”vaicÄjumÄ iepriekÅ” aprÄÄ·inÄt datumu un nodot filtram jau sagatavoto mainÄ«go. Un Å”is mÄjiens mums deva lielisku rezultÄtu, vaicÄjums kļuva gandrÄ«z 6 reizes ÄtrÄks!
Å eit atkal ir svarÄ«gi bÅ«t uzmanÄ«giem: datu tipam apakÅ”vaicÄjumÄ ir jÄbÅ«t tÄdam paÅ”am kÄ laukam, pÄc kura mÄs filtrÄjam, pretÄjÄ gadÄ«jumÄ plÄnotÄjs nolems, ka, jo veidi ir atŔķirÄ«gi un vispirms ir jÄiegÅ«st visi datus un filtrÄjiet tos lokÄli.
AtgriezÄ«sim filtra pÄc datuma sÄkotnÄjo vÄrtÄ«bu.
Fredijs vs. jsonb
KopumÄ BÅ«la lauki un datumi jau ir pietiekami paÄtrinÄjuÅ”i mÅ«su vaicÄjumu, taÄu bija vÄl viens datu veids. CÄ«Åa ar filtrÄÅ”anu pÄc tÄ, godÄ«gi sakot, joprojÄm nav beigusies, lai gan arÄ« Å”eit ir panÄkumi. TÄtad, lÅ«k, kÄ mums izdevÄs tikt garÄm filtram jsonb uz attÄlo serveri.
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
TÄ vietÄ, lai filtrÄtu operatorus, ir jÄizmanto viena operatora klÄtbÅ«tne. jsonb citÄ. 7 sekundes oriÄ£inÄlo 29 vietÄ. LÄ«dz Å”im Ŕī ir vienÄ«gÄ veiksmÄ«gÄ iespÄja filtru pÄrsÅ«tÄ«Å”anai jsonb uz attÄlo serveri, taÄu Å”eit svarÄ«gi Åemt vÄrÄ vienu ierobežojumu: izmantojam datu bÄzes versiju 9.6, bet lÄ«dz aprīļa beigÄm plÄnojam pabeigt pÄdÄjos testus un pÄriet uz 12. versiju. TiklÄ«dz mÄs atjauninÄsim, mÄs rakstÄ«sim, kÄ tas ietekmÄja, jo ir daudz izmaiÅu, uz kurÄm ir daudz cerÄ«bu: json_path, jauna CTE uzvedÄ«ba, push down (pastÄv no versijas 10). Es ļoti vÄlos to izmÄÄ£inÄt drÄ«z.
Piebeidz viÅu
MÄs pÄrbaudÄ«jÄm, kÄ katras izmaiÅas ietekmÄ vaicÄjuma Ätrumu atseviŔķi. Tagad redzÄsim, kas notiek, ja visi trÄ«s filtri ir uzrakstÄ«ti pareizi.
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
JÄ, vaicÄjums izskatÄs sarežģītÄks, tÄ ir piespiedu cena, bet izpildes Ätrums ir 2 sekundes, kas ir vairÄk nekÄ 10 reizes ÄtrÄks! Un mÄs runÄjam par vienkÄrÅ”u vaicÄjumu salÄ«dzinoÅ”i nelielai datu kopai. PÄc reÄliem pieprasÄ«jumiem mÄs saÅÄmÄm palielinÄjumu lÄ«dz pat vairÄkiem simtiem reižu.
RezumÄjot: ja jÅ«s izmantojat PostgreSQL ar FDW, vienmÄr pÄrbaudiet, vai visi filtri ir nosÅ«tÄ«ti uz attÄlo serveri, un jÅ«s bÅ«siet laimÄ«gs... Vismaz lÄ«dz brÄ«dim, kad nokļūsit pie savienojumu starp tabulÄm no dažÄdiem serveriem. Bet tas ir stÄsts citam rakstam.
Paldies par jÅ«su uzmanÄ«bu! Es labprÄt komentÄros dzirdÄtu jautÄjumus, komentÄrus un stÄstus par jÅ«su pieredzi.
Avots: www.habr.com