Arkitektura mikroserve, si çdo gjĂ« tjetĂ«r nĂ« kĂ«tĂ« botĂ«, ka avantazhet dhe disavantazhet e saj. Disa procese bĂ«hen mĂ« tĂ« lehta me tĂ«, ndĂ«rsa tĂ« tjerat bĂ«hen mĂ« tĂ« komplikuara. PĂ«r t'u pĂ«rshtatur me shpejtĂ«sinĂ« e ndryshimeve dhe pĂ«r shkak tĂ« shkallĂ«zueshmĂ«risĂ« mĂ« tĂ« mirĂ«, Ă«shtĂ« e nevojshme tĂ« bĂ«hen disa sakrifica. NjĂ« prej tyre Ă«shtĂ« komplikuar analiza. NĂ« njĂ« arkitekturĂ« monolite, gjithĂ« analiza operative mund tĂ« reduktohet nĂ« pyetje SQL pĂ«r replikĂ«n analitike, por nĂ« arkitekturĂ«n me shumĂ« shĂ«rbime, çdo shĂ«rbim ka bazĂ«n e vet, dhe duket se nuk mjafton njĂ« pyetje (apo ndoshta mjafton?). PĂ«r ata qĂ« janĂ« tĂ« interesuar se si e zgjidhĂ«m problemin e analizĂ«s operative nĂ« kompaninĂ« tonĂ« dhe si mĂ«suam tĂ« jetojmĂ« me kĂ«tĂ« zgjidhje â mirĂ«serdhĂ«t.
Më quajnë Pavel Sivaƥ, në DomClick punoj në ekipin që është përgjegjës për mbështetje të depozitës analitike të të dhënave. Në mënyrë të kushtëzuar, aktiviteti ynë mund të klasifikohet si inxhinieri e të dhënave, por në të vërtetë, spektri i detyrave është shumë më i gjerë. Ka standardet e zakonshme për inxhinierinë e të dhënave ETL/ELT, mbështetje dhe adaptim të mjeteve për analizën e të dhënave dhe zhvillimi i mjeteve tona. Në veçanti, për raportimin operativ kemi vendosur të "pretendojmë" se kemi një monolit dhe t'u japim analistëve një bazë, ku do të jenë të gjitha të dhënat e nevojshme për ta.
NĂ« tĂ« vĂ«rtetĂ«, ne shqyrtuam disa mundĂ«si. Mund tĂ« ishim ndĂ«rtuar njĂ« depo tĂ« plotĂ« â madje e provuam, por, pĂ«r tĂ« qenĂ« tĂ« sinqertĂ«, nuk arritĂ«m ta pajtonim ndryshimet e shpeshta nĂ« logjikĂ« me procesin mjaft tĂ« ngadaltĂ« tĂ« ndĂ«rtimit tĂ« depo dhe pĂ«rfshirjes sĂ« ndryshimeve nĂ« tĂ« (nĂ«se dikush e arriti kĂ«tĂ«, na shkruani nĂ« komentet si). Mund tĂ« kishim thĂ«nĂ« analistĂ«ve: "Djem, mĂ«soni python dhe shkoni nĂ« replikat analitike", por kjo do tĂ« ishte njĂ« kĂ«rkesĂ« shtesĂ« pĂ«r punĂ«simin e personelit, dhe dukej se duhej ta evitonim kĂ«tĂ«, nĂ«se ishte e mundur. VendosĂ«m tĂ« provonim teknologjinĂ« FDW (Foreign Data Wrapper): nĂ« thelb, Ă«shtĂ« njĂ« dblink standard qĂ« ekziston nĂ« standardin SQL, por me njĂ« ndĂ«rfaqe shumĂ« mĂ« tĂ« pĂ«rshtatshme. NĂ« bazĂ« tĂ« saj ne krijuam njĂ« zgjidhje, e cila pĂ«rfundimisht mbeti nĂ« pĂ«rdorim. Detajet e saj janĂ« njĂ« temĂ« pĂ«r njĂ« artikull tĂ« veçantĂ«, ndoshta edhe pĂ«r mĂ« shumĂ« se njĂ«, pasi dua tĂ« flas mbi shumĂ« aspekte: nga sinkronizimi i skemave tĂ« bazave deri te menaxhimi i aksesit dhe anonimizimi i tĂ« dhĂ«nave personale. Gjithashtu, duhet tĂ« saktĂ«soj se kjo zgjidhje nuk Ă«shtĂ« njĂ« zĂ«vendĂ«sim pĂ«r bazat analitike tĂ« vĂ«rteta dhe depo, ajo zgjidh vetĂ«m njĂ« problem tĂ« caktuar.
Kështu duket në nivelin më të lartë:
Ka njĂ« bazĂ« PostgreSQL, ku pĂ«rdoruesit mund tĂ« ruajnĂ« tĂ« dhĂ«nat e tyre tĂ« punĂ«s, dhe mĂ« e rĂ«ndĂ«sishmja â nĂ« kĂ«tĂ« bazĂ« janĂ« tĂ« lidhura replikat analitike tĂ« tĂ« gjitha shĂ«rbimeve nĂ«pĂ«rmjet FDW. Kjo jep mundĂ«sinĂ« pĂ«r tĂ« shkruar njĂ« kĂ«rkesĂ« pĂ«r disa baza, pa marrĂ« parasysh se çfarĂ« janĂ« ato: PostgreSQL, MySQL, MongoDB, ose diçka tjetĂ«r (skedar, API, nĂ«se ndonjĂ«herĂ« nuk ka njĂ« vrapues tĂ« pĂ«rshtatshĂ«m, mund tĂ« shkruajmĂ« njĂ« tonin tonin tonin tĂ« vetin). E duket se Ă«shtĂ« gjithçka, super! A do tĂ« dalĂ« tĂ« gjithĂ«?
Nëse gjithçka do të përfundonte aq shpejt dhe thjesht, ndoshta nuk do të kishte artikuj.
ĂshtĂ« e rĂ«ndĂ«sishme tĂ« kuptohet qartĂ« se si PostgreSQL pĂ«rpunon kĂ«rkesat pĂ«r serverĂ« tĂ« largĂ«t. Kjo duket logjike, megjithatĂ« shpesh nuk i kushtohet vĂ«mendje: PostgreSQL ndan kĂ«rkesĂ«n nĂ« pjesĂ« qĂ« ekzekutohen nĂ« serverĂ« tĂ« largĂ«t nĂ« mĂ«nyrĂ« tĂ« pavarur, mbledh tĂ« dhĂ«nat dhe kryen llogaritjet pĂ«rfundimtare vetĂ«, prandaj shpejtĂ«sia e ekzekutimit tĂ« kĂ«rkesĂ«s do tĂ« varet shumĂ« nga mĂ«nyra se si Ă«shtĂ« shkruar ajo. Duhet gjithashtu tĂ« theksohet: kur tĂ« dhĂ«nat vijnĂ« nga njĂ« server i largĂ«t, ato nuk kanĂ« mĂ« indekse, nuk ka asgjĂ« qĂ« i ndihmon planifikuesit, pĂ«rveçse ne vetĂ«. Dhe pikĂ«risht pĂ«r kĂ«tĂ« do tĂ« dĂ«shironim tĂ« flasim mĂ« shumĂ«.
Kërkesë e thjeshtë dhe plani me të
Për të ilustruar se si Postgres ekzekuton një kërkesë në një tabelë me 6 milion rreshta në distancë serveri, le të shikojmë një plan të thjeshtë.
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 msPërdorimi i instrukcionit VERBOSE lejon të shohim kërkesën që do të dërgohet në serverin e largët dhe rezultatet të cilat do të marrim për përpunim të mëtejshëm (rreshti RemoteSQL).
Le të shkojmë pak më tutje dhe të shtojmë disa filtra në kërkesën tonë: një për boolean fushën, një për përfshirjen timestamp në interval dhe një për jsonb.
shpjegoni analizën verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active është True
AND created_dt MIDIS CURRENT_DATE - INTERVAL '7 muaj'
AND CURRENT_DATE - INTERVAL '6 muaj'
AND meta->>'source' = 'test';
Agregati (kost=577487.69..577487.70 rreshta=1 gjerësi=8) (koha aktuale=27473.818..25473.819 rreshta=1 loops=1)
Shkarkimi: count(1)
-> Skanohet e jashtme në fdw_schema."table" (kost=100.00..577469.21 rreshta=7390 gjerësi=0) (koha aktuale=31.369..25372.466 rreshta=1360025 loops=1)
Shkarkimi: "table".id, "table".is_active, "table".meta, "table".created_dt
Filtri: (("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 me zonë kohore - '6 mons'::interval)))
Rreshta të hequra nga Filtri: 5046843
SQL e largët: SELECT created_dt, is_active, meta FROM fdw_schema.table
Koha e planifikimit: 0.665 ms
Koha e ekzekutimit: 27474.118 msPikërisht këtu është momenti, për të cilin duhet të kushtohet vëmendje gjatë hartimit të kërkesave. Filtrit nuk iu kaluan në serverin e largët, që do të thotë se për ekzekutimin e tij, Postgres merr të gjitha 6 milion rreshta për të filtruar më pas lokal (rreshti Filtri) dhe për të kryer agregimin. Sekreti i suksesit është të shkruhet kërkesa në mënyrë që filtrat të kalojnë në makinën e largët, dhe ne të marrim dhe agregojmë vetëm rreshtat e nevojshëm.
Kjo është disa booleanshit
Me fushat boolean â gjithçka Ă«shtĂ« e thjeshtĂ«. NĂ« kĂ«rkesĂ«n origjinale problemi lindte pĂ«r shkak tĂ« operatorit Ă«shtĂ«. NĂ«se e ndryshojmĂ« atĂ« nĂ« =, do tĂ« marrim rezultatin e mĂ«poshtĂ«m:
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 msSiç e shihni, filtri shkoi në serverin e largët, dhe koha e ekzekutimit u ul nga 27 në 19 sekonda.
Vlen të theksohet se operatori është diferon nga operatori = për faktin se di të punojë me vlerën Null. Kjo do të thotë se is not True në filtrin do të lërë vlerat False dhe Null, ndërsa != True do të lërë vetëm vlerat False. Prandaj, kur zëvendësohet operatori is not duhet të kaloni dy kushte në filtrin me operatorin OR, për shembull, WHERE (col != True) OR (col is null).
Me boolean jemi zotuar, le të vazhdojmë. Ndërkohë, do ta kthejmë filtrin e vlerës boolean në formën e tij origjinale, për të shqyrtuar veprimin e ndryshimeve të tjera.
timestamptz? hz
NĂ« tĂ« vĂ«rtetĂ«, shpesh na duhet tĂ« eksperimentojmĂ« se si tĂ« shkruajmĂ« siç duhet njĂ« kĂ«rkesĂ« qĂ« pĂ«rfshin servera tĂ« largĂ«t, dhe mĂ« pas tĂ« gjejmĂ« shpjegimin pse ndodhin veçanĂ«risht kĂ«shtu. Informacione shumĂ« tĂ« pakta pĂ«r kĂ«tĂ« çështje mund tĂ« gjenden nĂ« internet. KĂ«shtu, nĂ« eksperimente ne zbuluam se filtri pĂ«r datĂ« fikse funksionon mirĂ« nĂ« serverin e largĂ«t, por kur duam tĂ« pĂ«rcaktojmĂ« datĂ«n nĂ« mĂ«nyrĂ« dinamike, pĂ«r shembull, now() ose CURRENT_DATE, kjo nuk ndodh. NĂ« shembullin tonĂ«, ne e shtuam njĂ« filtrim tĂ« tillĂ«, qĂ« kolona created_at pĂ«rmbante tĂ« dhĂ«na pikĂ«risht pĂ«r 1 muaj nĂ« tĂ« kaluarĂ«n (BETWEEN CURRENT_DATE â INTERVAL â7 monthâ AND CURRENT_DATE â INTERVAL â6 monthâ). ĂfarĂ« krova pĂ«r kĂ«tĂ« rast?
shpjego analizo 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 >'source' = 'test';
Aggregat (cost=306875.17..306875.18 rows=1 width=8) (actual time=4789.114..4789.115 rows=1 loops=1)
Dalja: count(1)
InitPlan 1 (kthen $0)
-> Rezultati (cost=0.00..0.02 rows=1 width=8) (actual time=0.007..0.008 rows=1 loops=1)
Dalja: ((('tani'::cstring)::date)::timestamp with time zone - '7 muaj'::interval)
InitPlan 2 (kthen $1)
-> Rezultati (cost=0.00..0.02 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1)
Dalja: ((('tani'::cstring)::date)::timestamp with time zone - '6 muaj'::interval)
-> Skanohu i Huaj për fdw_schema."table" (cost=100.02..306874.86 rows=105 width=0) (actual time=23.475..4681.419 rows=1360025 loops=1)
Dalja: "table".id, "table".is_active, "table".meta, "table".created_dt
Filtri: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text))
Rreshtat e hequra nga Filtri: 76934
SQL i Largë: SELECT is_active, meta FROM fdw_schema.table WHERE ((created_dt >= $1::timestamp with time zone)) AND ((created_dt < $2::timestamp with time zone))
Koha e planifikimit: 0.703 ms
Koha e ekzekutimit: 4789.379 msI kemi sugjeruar planifikuesit të llogarisë paraprakisht datën në nënkërkesë dhe të kalojë tashmë një ndryshor të gatshëm në filtrin. Dhe kjo sugjerim na dha një rezultat të shkëlqyer, kërkesa u bë më e shpejtë pothuajse 6 herë!
Përsëri, është e rëndësishme të jemi të kujdesshëm: tipi i të dhënave në nënpyetje duhet të jetë i njëjtë me atë të fushës, mbi të cilën po filtrojmë, nd otherwise the scheduler will decide that since the types are different, it is necessary to first fetch all the data and then filter it locally.
Le ta kthejmë filtrin sipas datës në vlerën origjinale.
Freddy vs. Jsonb
Në thelb, fushat logjike dhe datat tashmë e kanë përshpejtuar kërkesën tonë, megjithatë kishte ende një lloj të dhënash. Lufta me filtrimin për të, sinqerisht, ende nuk ka përfunduar, megjithëse këtu ka edhe suksese. Pra, ja si arritëm ta kalojmë filtrin për jsonb fushën në serverin e largët.
shpjego analiza verbose
SELECT count(1)
FROM fdw_schema.table
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 muaj'
AND CURRENT_DATE - INTERVAL '6 muaj'
AND meta @> '{"source":"test"}'::jsonb;
Agregate (cost=245463.60..245463.61 rows=1 width=8) (actual time=6727.589..6727.590 rows=1 loops=1)
Output: count(1)
-> Skanoj të Huaj në 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 muaj'::interval)) AND ("table".created_dt '{"source": "test"}'::jsonb))
Koha e planifikimit: 0.747 ms
Koha e ekzekutimit: 6727.815 msNë vend të operatorëve të filtrimit, duhet të përdoret operatori i pranisë së një jsonb në tjetrin. 7 sekonda në vend të 29. Deri tani, ky është varianti i vetëm i suksesshëm i kalimit të filtrave për jsonb në serverin e largët, por këtu është e rëndësishme të merret parasysh një kufizim: ne po përdorim versionin e bazës 9.6, megjithatë deri në fund të prillit planifikojmë të përfundojmë testet e fundit dhe të kalojmë në versionin 12. Pasi të përditësohemi, do t'ju njoftojmë se si ndikon kjo, sepse ka shumë ndryshime për të cilat kemi shumë shpresë: json_path, sjellje të reja CTE, push down (i pranishëm që nga versioni 10). Na pëlqen të provojmë sa më shpejt.
Finish him
Ne kemi verifikuar se si çdo ndryshim ndikon në shpejtësinë e kërkimit në veçanti. Tani le të shohim se çfarë do të ndodhë kur të tre filtrat të shkruhen siç duhet.
shpjego analizo me detaje
Zgjidh numrin(1)
Nga fdw_schema.tabela
Ku është_aktive = True
Dhe dat_created >= (Zgjidh DATE::timestamptz - INTERVAL '7 muaj')
Dhe dat_created <(Zgjidh DATE::timestamptz - INTERVAL '6 muaj')
Dhe meta @> '{"source":"test"}'::jsonb;
Agregati (kostot=322041.51..322041.52 rreshta=1 gjerësi=8) (koha aktuale=2278.867..2278.867 rreshta=1 loops=1)
Dalja: numri(1)
Planifikimi inicial 1 (kthen $0)
-> Rezultati (kostot=0.00..0.02 rreshta=1 gjerësi=8) (koha aktuale=0.010..0.010 rreshta=1 loops=1)
Dalja: ((('tani'::cstring)::date)::timestamp me zonë të caktuar - '7 muaj'::interval)
Planifikimi inicial 2 (kthen $1)
-> Rezultati (kostot=0.00..0.02 rreshta=1 gjerësi=8) (koha aktuale=0.003..0.003 rreshta=1 loops=1)
Dalja: ((('tani'::cstring)::date)::timestamp me zonë të caktuar - '6 muaj'::interval)
-> Skanoimi i jashtëm mbi fdw_schema."tabela" (kostot=100.02..322041.41 rreshta=25 gjerësi=0) (koha aktuale=8.597..2153.809 rreshta=1360025 loops=1)
Dalja: "tabela".id, "tabela".është_aktive, "tabela".meta, "tabela".dat_created
SQL-i i largët: Zgjidh NULL Nga fdw_schema.tabela Ku (është_aktive) Dhe ((dat_created >= $1::timestamp me zonë të caktuar)) Dhe ((dat_created < $2::timestamp me zonë të caktuar)) Dhe ((meta @> '{"source": "test"}'::jsonb))
Koha e planifikimit: 0.820 ms
Koha e ekzekutimit: 2279.087 msPo, pyetja duket më e ndërlikuar; kjo është një çmim i detyruar, por shpejtësia e ekzekutimit është 2 sekonda, që është më shumë se 10 herë më shpejt! Dhe ne po flasim për një pyetje të thjeshtë ndaj një grupi të dhënash relativisht të vogël. Në pyetje reale kemi marrë rritje deri në disa qindra herë.
Të japim disa përmbledhje: nëse përdorni PostgreSQL me FDW, kontrolloni gjithmonë nëse të gjitha filtrat dërgohen në serverin e largët, dhe do të keni fat⊠Të paktën, derisa të arrini deri te bashkimet midis tabelave të ndryshme. serverëvePor kjo është një histori për një artikull tjetër.
Faleminderit për vëmendjen! Do të isha i lumtur të dëgjoja pyetje, komente dhe histori për përvojën tuaj në komentet.
Burimi: habr.com
