Örþjónustuarkitektúr, eins og allt í þessum heimi, hefur sína kosti og galla. Sum ferli verða auðveldari með því, önnur erfiðari. Og vegna hraða breytinga og betri sveigjanleika þarftu að færa fórnir. Eitt af því er vaxandi flókið greiningar. Ef hægt er að minnka alla rekstrargreiningu í einliða í SQL fyrirspurnir í greinandi eftirmynd, þá hefur hver þjónusta sinn gagnagrunn í fjölþjónustuarkitektúr og það virðist sem ekki sé hægt að gera eina fyrirspurn (eða kannski?). Fyrir þá sem hafa áhuga á því hvernig við leystum vandamálið við rekstrargreiningu í fyrirtækinu okkar og hvernig við lærðum að lifa með þessari lausn - velkomin.
Ég heiti Pavel Sivash, hjá DomClick vinn ég í teymi sem ber ábyrgð á að viðhalda greiningargagnageymslunni. Venjulega er hægt að flokka starfsemi okkar sem gagnaverkfræði, en í raun er verkefnasviðið miklu meira. Það eru ETL/ELT staðall fyrir gagnaverkfræði, stuðning og aðlögun tækja fyrir gagnagreiningu og þróun eigin verkfæra. Sérstaklega, fyrir rekstrarskýrslugerð, ákváðum við að „þykjast“ að við séum með einliða og gefa greinendum einn gagnagrunn sem mun innihalda öll þau gögn sem þeir þurfa.
Almennt litum við á mismunandi valkosti. Það var hægt að byggja upp fullgilda geymslu - við reyndum meira að segja, en satt að segja gátum við ekki sameinað nokkuð tíðar breytingar á rökfræði við frekar hæga ferli að byggja geymslu og gera breytingar á henni (ef einhverjum tókst það , skrifaðu í athugasemdirnar hvernig). Það var hægt að segja sérfræðingunum: „Krakkar, lærðu python og farðu í greiningar eftirmyndir,“ en þetta er viðbótarkrafa fyrir ráðningar og það virtist sem það ætti að forðast þetta ef mögulegt var. Við ákváðum að prófa að nota FDW (Foreign Data Wrapper) tækni: í meginatriðum er þetta staðlað dblink, sem er í SQL staðlinum, en með sitt eigið miklu þægilegra viðmót. Út frá henni gerðum við lausn sem náði að lokum og við sættum okkur við hana. Upplýsingar um það eru efni í sérstakri grein, og kannski fleiri en eina, þar sem ég vil tala um margt: allt frá samstillingu gagnagrunnsskemu til aðgangsstýringar og afpersónunar persónuupplýsinga. Það er líka nauðsynlegt að gera fyrirvara um að þessi lausn komi ekki í stað raunverulegra greiningargagnagrunna og gagnasöfn, hún leysir aðeins ákveðið vandamál.
Á efsta stigi lítur það svona út:
Það er PostgreSQL gagnagrunnur þar sem notendur geta geymt vinnugögn sín og síðast en ekki síst eru greiningar eftirlíkingar allra þjónustu tengdar þessum gagnagrunni í gegnum FDW. Þetta gerir það mögulegt að skrifa fyrirspurn í nokkra gagnagrunna, og það skiptir ekki máli hvað það er: PostgreSQL, MySQL, MongoDB eða eitthvað annað (skrá, API, ef skyndilega er enginn hentugur umbúðir, geturðu skrifað þitt eigið). Jæja, allt virðist frábært! Erum við að hætta saman?
Ef allt endaði svo fljótt og einfaldlega, þá væri líklega engin grein.
Það er mikilvægt að hafa skýrt hvernig Postgres vinnur úr beiðnum til ytri netþjóna. Þetta virðist rökrétt, en oft tekur fólk ekki eftir því: Postgres skiptir beiðninni í hluta sem eru keyrðir sjálfstætt á ytri netþjónum, safnar þessum gögnum og framkvæmir lokaútreikninga sjálft, þannig að hraðinn á framkvæmd fyrirspurnar mun ráðast mjög af hvernig það er skrifað. Það skal líka tekið fram: þegar gögnin koma frá ytri netþjóni hafa þau ekki lengur vísitölur, það er ekkert sem mun hjálpa tímaáætlunarmanninum, því aðeins við sjálf getum hjálpað og ráðlagt honum. Og þetta er einmitt það sem ég vil ræða nánar.
Einföld fyrirspurn og áætlun með henni
Til að sýna hvernig Postgres biður um 6 milljón raða töflu á ytri netþjóni skulum við skoða einfalda áætlun.
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 msMeð því að nota VERBOSE yfirlýsinguna getum við séð fyrirspurnina sem verður send á ytri netþjóninn og niðurstöðurnar sem við fáum til frekari vinnslu (RemoteSQL lína).
Við skulum ganga aðeins lengra og bæta nokkrum síum við beiðni okkar: einni fyrir boolískt reit, einn eftir tilviki timestamp í millibili og eitt af 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Þetta er þar sem punkturinn sem þú þarft að borga eftirtekt til þegar þú skrifar fyrirspurnir liggur. Síurnar voru ekki fluttar yfir á ytri netþjóninn, sem þýðir að til að keyra hann, dregur Postgres út allar 6 milljónir línanna til að sía síðan á staðnum (Filter row) og framkvæma samsöfnun. Lykillinn að velgengni er að skrifa fyrirspurn þannig að síurnar séu fluttar yfir á ytri vélina og við tökum á móti og tökum aðeins saman nauðsynlegar línur.
Það er eitthvað bull
Með Boolean sviðum er allt einfalt. Í upphaflegu beiðninni var vandamálið vegna rekstraraðilans is. Ef þú skiptir um það með =, þá fáum við eftirfarandi niðurstöðu:
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 msEins og þú sérð flaug sían á ytri netþjón og framkvæmdartíminn var styttur úr 27 í 19 sekúndur.
Það er athyglisvert að rekstraraðili is öðruvísi en rekstraraðili = vegna þess að það getur unnið með núllgildinu. Það þýðir að er ekki satt mun skilja gildin False og Null eftir í síunni, en != Satt mun skilja aðeins eftir fölsk gildi. Því þegar skipt er um rekstraraðila er ekki tvö skilyrði með OR rekstraraðila ættu að fara yfir á síuna, til dæmis, HVAR (col != True) EÐA (col er núll).
Við höfum tekist á við Boolean, við skulum halda áfram. Í bili skulum við skila Boolean síunni í upprunalegt form til að íhuga sjálfstætt áhrif annarra breytinga.
tímastamptz? hz
Almennt þarftu oft að gera tilraunir með hvernig á að skrifa rétt beiðni sem felur í sér ytri netþjóna og aðeins þá að leita að skýringu á því hvers vegna þetta gerist. Mjög litlar upplýsingar um þetta er að finna á netinu. Þannig að í tilraunum komumst við að því að föst dagsetningarsía flýgur til ytri netþjónsins með hvelli, en þegar við viljum stilla dagsetninguna á virkan hátt, til dæmis now() eða CURRENT_DATE, gerist það ekki. Í dæminu okkar bættum við við síu þannig að create_at dálkurinn innihélt gögn fyrir nákvæmlega 1 mánuð í fortíðinni (MILLI CURRENT_DATE - MILLI '7 mánaða' OG CURRENT_DATE - MILLI '6 mánuðir'). Hvað gerðum við í þessu tilfelli?
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 msVið sögðum skipuleggjanda að reikna út dagsetninguna í undirfyrirspurninni fyrirfram og senda tilbúnu breytuna í síuna. Og þessi vísbending gaf okkur frábæra niðurstöðu, beiðnin varð næstum 6 sinnum hraðari!
Aftur er mikilvægt að vera varkár hér: gagnategundin í undirfyrirspurninni verður að vera sú sama og reitsins sem við erum að sía á, annars mun skipuleggjandinn ákveða að þar sem gerðirnar eru mismunandi, þá er nauðsynlegt að fyrst fá allar gögnin og sía þau á staðnum.
Við skulum skila dagsetningarsíu í upprunalegt gildi.
Freddy vs. Jsonb
Almennt séð hafa Boolean reitir og dagsetningar þegar flýtt fyrir fyrirspurninni okkar nægilega mikið, en það var enn ein gagnategund eftir. Baráttan við síun eftir henni, satt best að segja, er enn ekki lokið, þó árangur sé hér líka. Svo, svona tókst okkur að fara framhjá síunni jsonb reit til ytri netþjónsins.
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Í stað þess að sía rekstraraðila verður þú að nota viðveru eins rekstraraðila jsonb í öðru. 7 sekúndur í stað upprunalegu 29. Enn sem komið er er þetta eini árangursríki kosturinn til að senda síur um jsonb yfir á fjarþjón, en hér er mikilvægt að taka tillit til einnar takmörkunar: við erum að nota útgáfu 9.6 af gagnagrunninum, en fyrir lok apríl ætlum við að klára síðustu prófin og fara yfir í útgáfu 12. Þegar við uppfærum munum við skrifa um hvernig það hafði áhrif, því það eru talsvert margar breytingar sem það er mikil von um: json_path, ný CTE hegðun, ýta niður (til frá útgáfu 10). Mig langar virkilega að prófa það bráðum.
Kláraðu hann
Við prófuðum hvernig hver breyting hafði áhrif á beiðnihraða fyrir sig. Við skulum nú sjá hvað gerist þegar allar þrjár síurnar eru skrifaðar rétt.
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 msJá, beiðnin lítur út fyrir að vera flóknari, þetta er þvingað gjald, en framkvæmdarhraði er 2 sekúndur, sem er meira en 10 sinnum hraðari! Og við erum að tala um einfalda fyrirspurn gegn tiltölulega litlu gagnasafni. Við raunverulegar beiðnir fengum við allt að nokkur hundruð sinnum fjölgun.
Til að draga saman: ef þú notar PostgreSQL með FDW, athugaðu alltaf að allar síur séu sendar á ytri þjóninn, og þú verður ánægður... Að minnsta kosti þangað til þú kemst í joins á milli borða frá mismunandi netþjónum. En það er saga fyrir aðra grein.
Takk fyrir athyglina! Ég myndi elska að heyra spurningar, athugasemdir og sögur um reynslu þína í athugasemdunum.
Heimild: www.habr.com