Mikrozerbitzuen arkitekturak, mundu honetako guztiak bezala, bere alde onak eta txarrak ditu. Prozesu batzuk errazagoak dira horrekin, beste batzuk zailagoak. Eta aldaketaren abiadura eta eskalagarritasun hobea lortzeko, sakrifizioak egin behar dituzu. Horietako bat analitikaren gero eta konplexutasuna da. Monolito batean analisi operatibo guztiak SQL kontsultetara erreplika analitiko batera murriztu daitezke, orduan zerbitzu anitzeko arkitektura batean zerbitzu bakoitzak bere datu-basea du eta badirudi ezin dela kontsulta bat egin (edo agian?). Gure enpresan analitika operatiboaren arazoa nola konpondu genuen eta irtenbide honekin bizitzen nola ikasi genuen jakiteko interesa dutenentzat - ongi etorriak.
Nire izena Pavel Sivash da, DomClick-en datu analitikoen biltegia mantentzeaz arduratzen den talde batean lan egiten dut. Ohikoki, gure jarduerak datuen ingeniaritza gisa sailka daitezke, baina, egia esan, zereginen aukera askoz zabalagoa da. Datuen ingeniaritzarako ETL/ELT estandarrak daude, datuak aztertzeko eta zure tresnen garapenerako tresnen laguntza eta egokitzapenerako. Bereziki, txosten operatiboak egiteko, monolito bat dugula βitxuraβ egitea eta analistei behar dituzten datu guztiak jasoko dituen datu-base bat ematea erabaki dugu.
Oro har, aukera desberdinak aztertu ditugu. Biltegi oso bat eraikitzea posible zen; saiatu ere egin ginen, baina, egia esateko, ezin izan ginen konbinatu nahiko maiz logikako aldaketak biltegi bat eraikitzeko eta aldaketak egiteko prozesu nahiko geldoarekin (norbaitek arrakasta izango balu). , idatzi iruzkinetan nola). Analistei esatea posible zen: βMutilak, ikasi python eta zoaz erreplika analitikoetaraβ, baina hau kontratatzeko baldintza gehigarria da, eta hori ahal izanez gero saihestu behar zela zirudien. FDW (Foreign Data Wrapper) teknologia erabiltzen saiatzea erabaki genuen: funtsean, hau dblink estandarra da, SQL estandarrean dagoena, baina bere interfaze askoz erosoagoa duena. Horretan oinarrituta, irtenbide bat egin genuen, azkenean harrapatu zuena, eta horretan finkatu ginen. Bere xehetasunak artikulu bereizi baten gaia dira, eta agian bat baino gehiago, askotaz hitz egin nahi dudanez: datu-baseen eskemak sinkronizatzen hasi eta datu pertsonalen sarbideen kontrola eta despertsonalizazioa. Era berean, erreserba egin behar da irtenbide hau ez dela benetako datu-base eta biltegi analitikoen ordezkoa; arazo zehatz bat baino ez du konpontzen.
Goiko mailan honelakoa da:
PostgreSQL datu-base bat dago, non erabiltzaileek beren laneko datuak gorde ditzaketen eta, batez ere, zerbitzu guztien erreplika analitikoak datu-base honetara konektatzen dira FDW bidez. Horrek hainbat datu-basetan kontsulta bat idaztea ahalbidetzen du, eta berdin dio zer den: PostgreSQL, MySQL, MongoDB edo beste zerbait (fitxategia, APIa, bat-batean bilgarri egokirik ez badago, zurea idatz dezakezu). Beno, dena bikaina dirudi! Hausten ari gara?
Dena hain azkar eta erraz amaituko balitz, orduan, ziurrenik, ez litzateke artikulurik egongo.
Garrantzitsua da argi izatea Postgres-ek urruneko zerbitzarietarako eskaerak nola prozesatzen dituen. Honek logikoa dirudi, baina askotan jendeak ez dio kasurik egiten: Postgres-ek eskaera urruneko zerbitzarietan modu independentean exekutatzen diren zatietan banatzen du, datu horiek biltzen ditu eta azken kalkuluak berak egiten ditu, beraz, kontsultaren exekuzioaren abiaduraren araberakoa izango da. nola idatzita dagoen. Kontuan izan behar da ere: datuak urruneko zerbitzari batetik iristen direnean, jada ez dauka indizerik, ez dago programatzaileari lagunduko dion ezer, beraz, guk geuk bakarrik lagundu eta aholkatu diezaiokegu. Eta horixe da zehatzago hitz egin nahi dudana.
Kontsulta sinple bat eta berarekin plan bat
Postgres-ek urruneko zerbitzari batean 6 milioi errenkada-taula nola kontsultatzen duen erakusteko, ikus dezagun plan sinple bat.
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 sententzia erabiliz, urruneko zerbitzarira bidaliko den kontsulta eta gerora prozesatzeko jasoko ditugun emaitzak (RemoteSQL lerroa) ikusteko aukera ematen digu.
Goazen pixka bat harago eta gehi gaitezen hainbat iragazki gure eskaerari: bata boolean eremua, bat agerraldiz denbora-marka tartean eta banan-banan 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 msHor dago kontsultak idaztean arreta jarri behar duzun puntua. Iragazkiak ez ziren urruneko zerbitzarira transferitu, hau da, exekutatzeko, Postgres-ek 6 milioi errenkada guztiak ateratzen ditu, gero lokalean iragazteko (Filter row) eta agregazioa egiteko. Arrakastaren gakoa kontsulta bat idaztea da, iragazkiak urruneko makinara transferitzeko, eta beharrezko errenkadak soilik jaso eta gehitzen ditugu.
Hori booleankeria bat da
Eremu boolearrekin dena erraza da. Jatorrizko eskaeran, arazoa operadoreari zor zitzaion is. ordezkatzen baduzu =, orduan emaitza hau lortuko dugu:
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 msIkus dezakezunez, iragazkiak urruneko zerbitzari batera hegan egin zuen, eta exekuzio-denbora 27tik 19ra murriztu zen.
Nabarmentzekoa da operadorea is operadorearen desberdina = Null balioarekin lan egin dezakeelako. Horrek esan nahi du ez da Egia False eta Null balioak iragazkian utziko ditu, berriz != Egia Balore faltsuak bakarrik utziko ditu. Hori dela eta, operadorea ordezkatzean ez da OR operadorearekin bi baldintza iragazkira pasatu behar dira, adibidez, NON (col != Egia) EDO (col nulua da).
Boolearrari buruz aritu gara, goazen aurrera. Oraingoz, itzul dezagun iragazki boolearra jatorrizko formara, beste aldaketen eragina modu independentean kontuan hartzeko.
timestamptz? hz
Oro har, sarritan esperimentatu behar duzu urruneko zerbitzariak inplikatzen dituen eskaera bat nola idatzi behar den zuzen, eta orduan bakarrik bilatu behar duzu zergatik gertatzen den horren azalpena. Honi buruzko informazio gutxi aurki daiteke Interneten. Beraz, esperimentuetan data finkoko iragazkia urruneko zerbitzarira hegan egiten duela ikusi dugu, baina data dinamikoki ezarri nahi dugunean, adibidez, orain() edo CURRENT_DATE, ez da hori gertatzen. Gure adibidean, iragazkia gehitu dugu, sortu_at zutabeak iraganeko hilabete bateko datuak izan ditzan (UNGUN_DATA - TARTEA '1 hilabete' ETA UNA_DATA - TARTEA '7 hilabete'). Zer egin dugu kasu honetan?
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 msPlanifikatzaileari esan genion aldez aurretik azpikontsultan data kalkulatzeko eta prest egindako aldagaia iragazkira pasatzeko. Eta aholku honek emaitza bikaina eman zigun, eskaera ia 6 aldiz azkarragoa bihurtu zen!
Berriz ere, kontuz ibili behar da hemen: azpikontsultako datu motak iragazten ari garen eremuaren berdina izan behar du, bestela planifikatzaileak erabakiko du mota desberdinak direnez, beharrezkoa dela lehenik guztiak jasotzea. datuak eta iragazi lokalean.
Itzul dezagun data-iragazkia jatorrizko baliora.
Freddy vs. Jsonb
Oro har, eremu boolearrak eta datek nahikoa azkartu dute dagoeneko gure kontsulta, baina beste datu mota bat geratzen zen. Horren bidez iragazteko borroka, egia esateko, oraindik ez da amaitu, nahiz eta hemen ere arrakasta izan. Beraz, horrela lortu dugu iragazkia pasatzea jsonb eremua urruneko zerbitzariari.
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 msOperadoreak iragazi beharrean, operadore baten presentzia erabili behar duzu jsonb ezberdin batean. 7 segundo jatorrizkoaren ordez 29. Orain arte hau da iragazkiak bidez transmititzeko aukera arrakastatsu bakarra jsonb urruneko zerbitzari batera, baina hemen garrantzitsua da muga bat kontuan hartzea: datu-basearen 9.6 bertsioa erabiltzen ari gara, baina apirilaren amaierarako azken probak amaitu eta 12. bertsiora pasatzeko asmoa dugu. Eguneratzen dugunean, nola eragin duen idatziko dugu, itxaropen handia duten aldaketa dezente daudelako: json_path, CTE portaera berria, push down (10. bertsioaz geroztik). Benetan laster probatu nahi dut.
Amaitu hura
Aldaketa bakoitzak eskaeraren abiadura nola eragiten duen probatu dugu banan-banan. Ikus dezagun orain zer gertatzen den hiru iragazkiak behar bezala idazten direnean.
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 msBai, eskaera konplikatuagoa dirudi, hau behartutako kuota da, baina exekuzio abiadura 2 segundokoa da, hau da, 10 aldiz azkarragoa da! Eta datu multzo nahiko txiki baten aurkako kontsulta sinple bati buruz ari gara. Benetako eskaeretan, ehunka aldiz igoera jaso genuen.
Laburbilduz: PostgreSQL FDWrekin erabiltzen baduzu, egiaztatu beti iragazki guztiak urruneko zerbitzarira bidaltzen direla, eta pozik egongo zara... Zerbitzari ezberdinetako taulen arteko bateetaraino iritsi arte behintzat. Baina hori beste artikulu baterako istorio bat da.
Eskerrik asko zure arretagatik! Gustatuko litzaidake iruzkinetan zure esperientziei buruzko galderak, iruzkinak eta istorioak entzutea.
Iturria: www.habr.com