Arkitektura e mikroshërbimeve, si çdo gjë në këtë botë, ka të mirat dhe të këqijat e saj. Disa procese bëhen më të lehta me të, të tjerët më të vështirë. Dhe për hir të shpejtësisë së ndryshimit dhe shkallëzueshmërisë më të mirë, ju duhet të bëni sakrifica. Një prej tyre është kompleksiteti i analitikës. Nëse në një monolit të gjitha analitikët operacionale mund të reduktohen në pyetje SQL në një kopje analitike, atëherë në një arkitekturë multiservice çdo shërbim ka bazën e tij të të dhënave dhe duket se një pyetje nuk është e mjaftueshme (ose ndoshta do të jetë?). Për ata që janë të interesuar se si e zgjidhëm problemin e analitikës operacionale në kompaninë tonë dhe si mësuam të jetojmë me këtë zgjidhje - mirëpritur.
Emri im është Pavel Sivash, në DomClick punoj në një ekip që është përgjegjës për mirëmbajtjen e depove të të dhënave analitike. Në mënyrë konvencionale, aktivitetet tona mund t'i atribuohen inxhinierisë së të dhënave, por, në fakt, gama e detyrave është shumë më e gjerë. Ka inxhinieri standarde të të dhënave ETL / ELT, mbështetje dhe përshtatje të mjeteve të analizës së të dhënave dhe zhvillimin e mjeteve të tyre. Në veçanti, për raportimin operacional, ne vendosëm të "pretendojmë" se kemi një monolit dhe t'u japim analistëve një bazë të dhënash që do të përmbajë të gjitha të dhënat që u nevojiten.
Në përgjithësi, ne shqyrtuam opsione të ndryshme. Ishte e mundur të ndërtonim një depo të plotë - ne madje u përpoqëm, por, të jem i sinqertë, nuk ishim në gjendje të bënim miq me ndryshime mjaft të shpeshta në logjikë me një proces mjaft të ngadaltë të ndërtimit të një depoje dhe bërjes së ndryshimeve në të ( nëse dikush ia doli, shkruani në komente si). Ju mund t'u thoni analistëve: "Djema, mësoni python dhe shkoni te linjat analitike", por kjo është një kërkesë shtesë për rekrutim dhe dukej se kjo duhej shmangur nëse ishte e mundur. Ne vendosëm të provojmë të përdorim teknologjinë FDW (Foreign Data Wrapper): në fakt, ky është një dblink standard, i cili është në standardin SQL, por me ndërfaqen e tij shumë më të përshtatshme. Në bazë të tij, ne morëm një vendim, i cili përfundimisht zuri rrënjë, ne u vendosëm mbi të. Detajet e tij janë temë e një artikulli të veçantë, dhe ndoshta më shumë se një, sepse dua të flas për shumë: nga sinkronizimi i skemës së bazës së të dhënave deri te kontrolli i aksesit dhe depersonalizimi i të dhënave personale. Duhet të theksohet gjithashtu se kjo zgjidhje nuk është një zëvendësim për bazat e të dhënave dhe depove reale analitike, ajo zgjidh vetëm një problem specifik.
Në nivelin më të lartë duket kështu:
Ekziston një bazë të dhënash PostgreSQL ku përdoruesit mund të ruajnë të dhënat e punës së tyre, dhe më e rëndësishmja, kopjet analitike të të gjitha shërbimeve janë të lidhura me këtë bazë të dhënash përmes FDW. Kjo bën të mundur shkrimin e një pyetjeje në disa baza të dhënash, dhe nuk ka rëndësi se çfarë është: PostgreSQL, MySQL, MongoDB ose diçka tjetër (skedar, API, nëse papritmas nuk ka mbështjellës të përshtatshëm, mund të shkruani tuajin). Epo, gjithçka duket të jetë e shkëlqyeshme! Te ndahesh?
Nëse gjithçka do të përfundonte kaq shpejt dhe thjesht, atëherë, me siguri, artikulli nuk do të ekzistonte.
Është e rëndësishme të jemi të qartë se si postgres trajton kërkesat për serverët në distancë. Kjo duket logjike, por shpesh njerëzit nuk i kushtojnë vëmendje: postgres e ndan pyetjen në pjesë që ekzekutohen në mënyrë të pavarur në serverë të largët, mbledh këto të dhëna dhe kryen vetë llogaritjet përfundimtare, kështu që shpejtësia e ekzekutimit të pyetjes do të varet shumë nga mënyra se si është shkruar. Duhet të theksohet gjithashtu: kur të dhënat vijnë nga një server i largët, ato nuk kanë më indekse, nuk ka asgjë që do të ndihmojë planifikuesin, prandaj, vetëm ne vetë mund ta ndihmojmë dhe sugjerojmë atë. Dhe për këtë dua të flas më në detaje.
Një kërkesë e thjeshtë dhe një plan me të
Për të treguar se si Postgres kërkon një tabelë me 6 milionë rreshta në një server të largët, le të shohim 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 deklaratës VERBOSE ju lejon të shihni pyetjen që do të dërgohet në serverin në distancë dhe rezultatet e të cilit do t'i marrim për përpunim të mëtejshëm (varg RemoteSQL).
Le të shkojmë pak më tej dhe të shtojmë disa filtra në pyetjen tonë: një nga Boolean fushë, një me hyrje timestamp për interval dhe një nga 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 msKëtu qëndron momenti, të cilit duhet t'i kushtoni vëmendje kur shkruani pyetje. Filtrat nuk u transferuan në serverin në distancë, që do të thotë se për ta ekzekutuar atë, postgres tërheq të gjitha 6 milion rreshtat në mënyrë që të filtrohet lokalisht më vonë (linja e Filterit) dhe të kryejë grumbullimin. Çelësi i suksesit është të shkruani një pyetje në mënyrë që filtrat të transmetohen në makinën në distancë, dhe ne të marrim dhe të grumbullojmë vetëm rreshtat e nevojshëm.
Kjo është një budallallëk
Me fushat boolean, gjithçka është e thjeshtë. Në pyetjen origjinale, problemi ishte për shkak të operatorit is. Nëse e zëvendësojmë me =, atëherë 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ç mund ta shihni, filtri fluturoi në serverin e largët dhe koha e ekzekutimit u ul nga 27 në 19 sekonda.
Duhet theksuar se operatori is ndryshe nga operatori = ai që mund të punojë me vlerën Null. Do të thotë se nuk eshte e vertete në filtër do të lërë vlerat False dhe Null, ndërsa != E vërtetë do të lërë vetëm vlera të rreme. Prandaj, kur zëvendësoni operatorin nuk është ju duhet të kaloni dy kushte në filtër me operatorin OR, për shembull, WHERE (color != E vërtetë) OSE (color është null).
Me boolean të kuptuar, duke ecur përpara. Ndërkohë, le ta kthejmë filtrin sipas vlerës boolean në formën e tij origjinale në mënyrë që të shqyrtojmë në mënyrë të pavarur efektin e ndryshimeve të tjera.
timestamptz? hz
Në përgjithësi, shpesh ju duhet të eksperimentoni se si të shkruani saktë një pyetje që përfshin serverë të largët dhe vetëm atëherë të kërkoni një shpjegim se pse po ndodh kjo. Shumë pak informacion në lidhje me këtë mund të gjenden në internet. Pra, në eksperimente, ne zbuluam se një filtër i datës fikse fluturon në një server të largët me zhurmë, por kur duam të vendosim datën në mënyrë dinamike, për shembull, tani() ose CURRENT_DATE, kjo nuk ndodh. Në shembullin tonë, ne kemi shtuar një filtër në mënyrë që kolona e krijuar_at të përmbajë të dhëna për saktësisht 1 muaj në të kaluarën (BETWEEN CURRENT_DATE - INTERVAL '7 muaj' DHE CURRENT_DATE - INTERVAL '6 muaj'). Çfarë bëmë në këtë rast?
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 msNe e nxitëm planifikuesin të llogariste datën paraprakisht në nënpyetje dhe të kalonte variablin e përgatitur tashmë në filtër. Dhe kjo aluzion na dha një rezultat të shkëlqyeshëm, pyetja u bë pothuajse 6 herë më e shpejtë!
Përsëri, është e rëndësishme të keni kujdes këtu: lloji i të dhënave në nënpyetje duhet të jetë i njëjtë me atë të fushës me të cilën filtojmë, përndryshe planifikuesi do të vendosë që meqenëse llojet janë të ndryshme dhe është e nevojshme që fillimisht të merren të gjitha të dhënat dhe filtrojini ato në nivel lokal.
Le ta kthejmë filtrin sipas datës në vlerën e tij origjinale.
Fredi vs. jsonb
Në përgjithësi, fushat dhe datat boolean tashmë e kanë përshpejtuar mjaftueshëm kërkesën tonë, por kishte një lloj tjetër të dhënash. Beteja me filtrimin prej tij, të them të drejtën, ende nuk ka përfunduar, megjithëse edhe këtu ka suksese. Pra, ja si arritëm të kalonim filtrin jsonb fushë në një server të largët.
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 msNë vend të filtrimit të operatorëve, duhet të përdorni praninë e një operatori. jsonb në një tjetër. 7 sekonda në vend të 29 origjinalit. Deri më tani, ky është i vetmi opsion i suksesshëm për transmetimin e filtrave mbi jsonb në një server të largët, por këtu është e rëndësishme të merret parasysh një kufizim: ne përdorim versionin 9.6 të bazës së të dhënave, por deri në fund të prillit planifikojmë të përfundojmë testet e fundit dhe të kalojmë në versionin 12. Kur të përditësojmë, do të shkruajmë se si ka ndikuar, sepse ka shumë ndryshime për të cilat ka shumë shpresa: json_path, sjellje e re CTE, push poshtë (që ekziston nga versioni 10). Unë me të vërtetë dua ta provoj së shpejti.
Përfundojeni atë
Ne kontrolluam se si çdo ndryshim ndikon në shpejtësinë e pyetjes individualisht. Le të shohim tani se çfarë ndodh kur të tre filtrat janë shkruar saktë.
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 msPo, pyetja duket më e ndërlikuar, është një çmim i detyruar, por shpejtësia e ekzekutimit është 2 sekonda, që është më shumë se 10 herë më e shpejtë! Dhe ne po flasim për një pyetje të thjeshtë në një grup relativisht të vogël të dhënash. Me kërkesa reale kemi marrë një rritje deri në disa qindra herë.
Për ta përmbledhur: nëse përdorni PostgreSQL me FDW, kontrolloni gjithmonë nëse të gjithë filtrat dërgohen në serverin në distancë dhe do të jeni të lumtur... Të paktën derisa të arrini të bashkoheni midis tabelave nga serverë të ndryshëm. Por kjo është një histori për një artikull tjetër.
Faleminderit per vemendjen! Do të doja të dëgjoja pyetje, komente dhe histori për përvojat tuaja në komente.
Burimi: www.habr.com