Mikrotjenestearkitektur, som alt i denne verden, har sine fordeler og ulemper. Noen prosesser blir lettere med det, andre vanskeligere. Og av hensyn til endringshastigheten og bedre skalerbarhet, må du ofre. En av dem er kompleksiteten til analyser. Hvis i en monolitt kan all operasjonell analyse reduseres til SQL-spørringer til en analytisk replika, så i en multitjenestearkitektur har hver tjeneste sin egen base, og det ser ut til at en spørring ikke er nok (eller kanskje det vil?). For de som er interessert i hvordan vi løste problemet med operasjonell analyse i selskapet vårt og hvordan vi lærte å leve med denne løsningen - velkommen.
Mitt navn er Pavel Sivash, hos DomClick jobber jeg i et team som er ansvarlig for å vedlikeholde det analytiske datavarehuset. Konvensjonelt kan aktivitetene våre tilskrives datateknikk, men faktisk er spekteret av oppgaver mye bredere. Det er standard datateknikk ETL/ELT, støtte og tilpasning av dataanalyseverktøy og utvikling av egne verktøy. Spesielt for operasjonell rapportering bestemte vi oss for å "late som" at vi har en monolitt og gi analytikere en database som vil inneholde alle dataene de trenger.
Generelt vurderte vi ulike alternativer. Det var mulig å bygge et fullverdig depot - vi prøvde til og med, men for å være ærlig var vi ikke i stand til å bli venner med ganske hyppige endringer i logikken med en ganske langsom prosess med å bygge et depot og gjøre endringer i det ( hvis noen lyktes, skriv i kommentarfeltet hvordan). Du kan si til analytikere: «Gutter, lær python og gå til analytiske linjer», men dette er et ekstra rekrutteringskrav, og det så ut til at dette burde unngås om mulig. Vi bestemte oss for å prøve å bruke FDW (Foreign Data Wrapper)-teknologien: faktisk er dette en standard dblink, som er i SQL-standarden, men med sitt mye mer praktiske grensesnitt. På bakgrunn av den tok vi en beslutning, som til slutt slo rot, vi slo til på det. Detaljene er tema for en egen artikkel, og kanskje mer enn én, fordi jeg vil snakke om mye: fra synkronisering av databaseskjemaer til tilgangskontroll og depersonalisering av personlige data. Det bør også bemerkes at denne løsningen ikke er en erstatning for ekte analytiske databaser og repositories, den løser bare et spesifikt problem.
På toppnivå ser det slik ut:
Det er en PostgreSQL-database hvor brukere kan lagre arbeidsdataene sine, og viktigst av alt er analytiske replikaer av alle tjenester koblet til denne databasen via FDW. Dette gjør det mulig å skrive en spørring til flere databaser, og det spiller ingen rolle hva det er: PostgreSQL, MySQL, MongoDB eller noe annet (fil, API, hvis det plutselig ikke er en passende wrapper, kan du skrive din egen). Vel, alt ser ut til å være bra! Slå opp?
Hvis alt endte så raskt og enkelt, ville sannsynligvis ikke artikkelen eksistert.
Det er viktig å være tydelig på hvordan postgres håndterer forespørsler til eksterne servere. Dette virker logisk, men ofte legger folk ikke merke til det: postgres deler spørringen inn i deler som kjøres uavhengig på eksterne servere, samler inn disse dataene og utfører selv de endelige beregningene, så utførelseshastigheten for spørringen vil i stor grad avhenge av hvordan det er skrevet. Det bør også bemerkes: når dataene kommer fra en ekstern server, har de ikke lenger indekser, det er ingenting som vil hjelpe planleggeren, derfor er det bare vi selv som kan hjelpe og foreslå det. Og det er det jeg vil snakke mer om.
En enkel forespørsel og en plan med det
For å vise hvordan Postgres spør etter en tabell på 6 millioner rader på en ekstern server, la oss se 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 msVed å bruke VERBOSE-setningen kan du se spørringen som vil bli sendt til den eksterne serveren og resultatene som vi vil motta for videre behandling (RemoteSQL-streng).
La oss gå litt lenger og legge til flere filtre i søket vårt: ett etter boolean felt, ett ved oppføring tidsstempel per intervall og en etter 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 er her øyeblikket ligger, som du må være oppmerksom på når du skriver spørsmål. Filtrene ble ikke overført til den eksterne serveren, noe som betyr at for å utføre den, trekker postgres alle 6 millioner rader for å filtrere lokalt (filterlinjen) og utføre aggregering senere. Nøkkelen til suksess er å skrive en spørring slik at filtrene blir overført til den eksterne maskinen, og vi mottar og samler bare de nødvendige radene.
Det er litt booleanshit
Med boolske felt er alt enkelt. I den opprinnelige spørringen skyldtes problemet operatøren is. Hvis du erstatter den med =, da får vi følgende 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øy filteret til den eksterne serveren, og utførelsestiden ble redusert fra 27 til 19 sekunder.
Det skal bemerkes at operatøren is forskjellig fra operatøren = den som kan fungere med Null-verdien. Det betyr at det er ikke sant i filteret vil verdiene False og Null, mens != Sant vil bare etterlate falske verdier. Derfor ved bytte av operatør er ikke du bør sende to betingelser til filteret med OR-operatøren, for eksempel, WHERE (col != True) OR (col er null).
Med boolesk funnet ut, gå videre. I mellomtiden, la oss returnere filteret etter boolsk verdi til sin opprinnelige form for uavhengig å vurdere effekten av andre endringer.
tidsstempel? hz
Generelt må du ofte eksperimentere med hvordan du skriver en spørring som involverer eksterne servere riktig, og først da se etter en forklaring på hvorfor dette skjer. Svært lite informasjon om dette finnes på Internett. Så i eksperimenter fant vi ut at et filter med fast dato flyr til en ekstern server med et smell, men når vi ønsker å sette datoen dynamisk, for eksempel now() eller CURRENT_DATE, skjer ikke dette. I vårt eksempel har vi lagt til et filter slik at kolonnen create_at inneholder data for nøyaktig 1 måned i fortiden (MELLOM CURRENT_DATE - INTERVAL '7 month' OG CURRENT_DATE - INTERVAL '6 month'). Hva gjorde vi i dette tilfellet?
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 ba planleggeren om å forhåndsberegne datoen i underspørringen og sende den allerede forberedte variabelen til filteret. Og dette hintet ga oss et flott resultat, spørringen ble nesten 6 ganger raskere!
Igjen, det er viktig å være forsiktig her: datatypen i underspørringen må være den samme som for feltet som vi filtrerer etter, ellers vil planleggeren bestemme at siden typene er forskjellige og det er nødvendig å først hente alle data og filtrere dem lokalt.
La oss returnere filteret etter dato til den opprinnelige verdien.
Freddy vs. jsonb
Generelt har boolske felt og datoer allerede akselerert søket vårt tilstrekkelig, men det var en annen datatype. Kampen med å filtrere etter det, for å være ærlig, er fortsatt ikke over, selv om det er suksesser her også. Så her er hvordan vi klarte å passere filteret jsonb feltet til en ekstern server.
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 msI stedet for å filtrere operatører, må du bruke tilstedeværelsen av én operatør. jsonb i en annen. 7 sekunder i stedet for de originale 29. Så langt er dette det eneste vellykkede alternativet for å overføre filtre over jsonb til en ekstern server, men her er det viktig å ta hensyn til én begrensning: vi bruker versjon 9.6 av databasen, men innen utgangen av april planlegger vi å fullføre de siste testene og gå over til versjon 12. Så snart vi oppdaterer, vil vi skrive hvordan det påvirket, fordi det er mange endringer som det er mange forhåpninger om: json_path, ny CTE-atferd, push down (eksisterer fra versjon 10). Jeg har veldig lyst til å prøve det snart.
Gjør ham ferdig
Vi sjekket hvordan hver endring påvirker spørringshastigheten individuelt. La oss nå se hva som skjer når alle tre filtrene er skrevet riktig.
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, spørringen ser mer komplisert ut, det er en tvungen pris, men utførelseshastigheten er 2 sekunder, som er mer enn 10 ganger raskere! Og vi snakker om en enkel spørring på et relativt lite sett med data. På reelle forespørsler fikk vi en økning på opptil flere hundre ganger.
For å oppsummere det: hvis du bruker PostgreSQL med FDW, sjekk alltid om alle filtre er sendt til den eksterne serveren, og du vil være fornøyd... I hvert fall til du kommer til sammenføyninger mellom tabeller fra forskjellige servere. Men det er en historie for en annen artikkel.
Takk for din oppmerksomhet! Jeg vil gjerne høre spørsmål, kommentarer og historier om opplevelsene dine i kommentarene.
Kilde: www.habr.com