Mikroservicearkitektur, som allt i den här världen, har sina för- och nackdelar. Vissa processer blir lättare med det, andra svårare. Och för förändringshastighetens skull och bättre skalbarhet måste du göra uppoffringar. En av dem är analysens ökande komplexitet. Om i en monolit all operationell analys kan reduceras till SQL-frågor till en analytisk replik, så har varje tjänst i en multiservicearkitektur sin egen databas och det verkar som att en fråga inte kan göras (eller kanske det kan?). För dig som är intresserad av hur vi löste problemet med operationsanalys i vårt företag och hur vi lärde oss att leva med denna lösning - välkommen.
Jag heter Pavel Sivash, på DomClick arbetar jag i ett team som ansvarar för att underhålla det analytiska datalagret. Konventionellt kan våra aktiviteter klassificeras som datateknik, men i själva verket är utbudet av uppgifter mycket bredare. Det finns ETL/ELT-standard för datateknik, support och anpassning av verktyg för dataanalys och utveckling av egna verktyg. Speciellt för operativ rapportering bestämde vi oss för att "låtsas" att vi har en monolit och ge analytiker en databas som kommer att innehålla all data de behöver.
I allmänhet övervägde vi olika alternativ. Det var möjligt att bygga ett fullfjädrat arkiv - vi försökte till och med, men för att vara ärlig kunde vi inte kombinera ganska frekventa förändringar i logik med den ganska långsamma processen att bygga ett arkiv och göra ändringar i det (om någon lyckades , skriv i kommentarerna hur). Det var möjligt att säga till analytikerna: "killar, lär dig python och gå till analytiska repliker", men detta är ett ytterligare krav för att rekrytera, och det verkade som att detta borde undvikas om möjligt. Vi bestämde oss för att försöka använda FDW (Foreign Data Wrapper) teknologi: i huvudsak är detta en standard dblink, som är i SQL-standarden, men med sitt eget mycket bekvämare gränssnitt. Utifrån det gjorde vi en lösning som så småningom slog igenom och vi bestämde oss för det. Dess detaljer är ämnet för en separat artikel, och kanske mer än en, eftersom jag vill prata om mycket: från synkronisering av databasscheman till åtkomstkontroll och depersonalisering av personuppgifter. Det är också nödvändigt att reservera att denna lösning inte är en ersättning för riktiga analytiska databaser och arkiv, den löser bara ett specifikt problem.
På översta nivån ser det ut så här:
Det finns en PostgreSQL-databas där användare kan lagra sin arbetsdata, och viktigast av allt är att analytiska repliker av alla tjänster är kopplade till denna databas via FDW. Detta gör det möjligt att skriva en fråga till flera databaser, och det spelar ingen roll vad det är: PostgreSQL, MySQL, MongoDB eller något annat (fil, API, om det plötsligt inte finns något lämpligt omslag kan du skriva ditt eget). Tja, allt verkar bra! Gör vi slut?
Om allt slutade så snabbt och enkelt, skulle det förmodligen inte finnas en artikel.
Det är viktigt att vara tydlig med hur Postgres behandlar förfrågningar till fjärrservrar. Detta verkar logiskt, men ofta uppmärksammar folk inte det: Postgres delar upp begäran i delar som exekveras oberoende på fjärrservrar, samlar in denna data och utför de slutliga beräkningarna själv, så hastigheten för exekveringen av frågan kommer till stor del att bero på hur det är skrivet. Det bör också noteras: när data kommer från en fjärrserver har den inte längre index, det finns inget som hjälper schemaläggaren, därför är det bara vi själva som kan hjälpa och ge honom råd. Och det är precis vad jag vill prata mer om.
En enkel fråga och en plan med den
För att visa hur Postgres frågar en tabell med 6 miljoner rader på en fjärrserver, låt oss titta på en enkel plan.
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 msGenom att använda VERBOSE-satsen kan vi se frågan som kommer att skickas till fjärrservern och vars resultat vi kommer att få för vidare bearbetning (RemoteSQL-raden).
Låt oss gå lite längre och lägga till flera filter till vår begäran: ett för boolean fält, ett efter förekomst tidsstämpel i intervallet och en efter 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 msDet är här punkten som du måste vara uppmärksam på när du skriver frågor ligger. Filtren överfördes inte till fjärrservern, vilket innebär att Postgres för att exekvera det drar ut alla 6 miljoner rader för att sedan filtrera lokalt (Filter row) och utföra aggregering. Nyckeln till framgång är att skriva en fråga så att filtren överförs till fjärrmaskinen, och vi tar emot och aggregerar endast de nödvändiga raderna.
Det är lite booleanshit
Med booleska fält är allt enkelt. I den ursprungliga begäran berodde problemet på operatören is. Om du ersätter den med =, då får vi följande resultat:
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 msSom du kan se flög filtret till en fjärrserver, och exekveringstiden minskade från 27 till 19 sekunder.
Det är värt att notera att operatören is skiljer sig från operatören = eftersom det kan fungera med Null-värdet. Det betyder att är inte sant kommer att lämna värdena False och Null i filtret, medan != Sant lämnar endast falska värden. Därför vid byte av operatör är inte två villkor med OR-operatorn ska skickas till filtret, till exempel, WHERE (col != True) OR (col är null).
Vi har hanterat boolean, låt oss gå vidare. Låt oss för nu återställa det booleska filtret till dess ursprungliga form för att självständigt överväga effekten av andra ändringar.
tidsstämpel? hz
I allmänhet måste du ofta experimentera med hur du korrekt skriver en begäran som involverar fjärrservrar, och först då leta efter en förklaring till varför detta händer. Mycket lite information om detta finns på Internet. Så i experiment fann vi att ett filter med fast datum flyger till fjärrservern med en smäll, men när vi vill ställa in datumet dynamiskt, till exempel now() eller CURRENT_DATE, händer inte detta. I vårt exempel lade vi till ett filter så att kolumnen create_at innehöll data för exakt 1 månad i det förflutna (MELLAN CURRENT_DATE - INTERVAL '7 månad' OCH CURRENT_DATE - INTERVAL '6 månad'). Vad gjorde vi i det här fallet?
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 sa till planeraren att beräkna datumet i underfrågan i förväg och skicka den färdiga variabeln till filtret. Och detta tips gav oss ett utmärkt resultat, förfrågan blev nästan 6 gånger snabbare!
Återigen är det viktigt att vara försiktig här: datatypen i underfrågan måste vara densamma som för fältet som vi filtrerar på, annars kommer planeraren att bestämma att eftersom typerna är olika, är det nödvändigt att först hämta alla data och filtrera den lokalt.
Låt oss återställa datumfiltret till dess ursprungliga värde.
Freddy vs. Jsonb
Generellt sett har booleska fält och datum redan påskyndat vår fråga tillräckligt, men det fanns ytterligare en datatyp kvar. Kampen med att filtrera efter det, ärligt talat, är fortfarande inte över, även om det finns framgång även här. Så här lyckades vi passera filtret jsonb fältet till fjärrservern.
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 msIstället för att filtrera operatorer måste du använda närvaron av en operator jsonb i en annan. 7 sekunder istället för originalet 29. Hittills är detta det enda framgångsrika alternativet för att överföra filter via jsonb till en fjärrserver, men här är det viktigt att ta hänsyn till en begränsning: vi använder version 9.6 av databasen, men i slutet av april planerar vi att slutföra de sista testerna och gå över till version 12. När vi har uppdaterat kommer vi att skriva om hur det påverkade, eftersom det finns ganska många förändringar som det finns mycket hopp om: json_path, nytt CTE-beteende, push down (existerar sedan version 10). Jag vill verkligen prova det snart.
Gör klart honom
Vi testade hur varje ändring påverkade förfrågningshastigheten individuellt. Låt oss nu se vad som händer när alla tre filtren är korrekt skrivna.
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 msJa, begäran ser mer komplicerad ut, detta är en påtvingad avgift, men exekveringshastigheten är 2 sekunder, vilket är mer än 10 gånger snabbare! Och vi pratar om en enkel fråga mot en relativt liten datamängd. På riktiga förfrågningar fick vi en ökning på upp till flera hundra gånger.
För att sammanfatta: om du använder PostgreSQL med FDW, kontrollera alltid att alla filter skickas till fjärrservern, så kommer du att vara nöjd... Åtminstone tills du får kopplingar mellan tabeller från olika servrar. Men det är en historia för en annan artikel.
Tack för din uppmärksamhet! Jag skulle gärna höra frågor, kommentarer och berättelser om dina upplevelser i kommentarerna.
Källa: will.com