Mikroservicearkitektur har, som alt i denne verden, sine fordele og ulemper. Nogle processer bliver nemmere med det, andre sværere. Og af hensyn til forandringshastigheden og bedre skalerbarhed er du nødt til at ofre dig. En af dem er kompleksiteten af analyser. Hvis alle operationelle analyser i en monolit kan reduceres til SQL-forespørgsler til en analytisk replika, så har hver tjeneste i en multiservice-arkitektur sin egen database, og det ser ud til, at en forespørgsel ikke er nok (eller måske vil den?). Til dem, der er interesseret i, hvordan vi løste problemet med operationel analyse i vores virksomhed, og hvordan vi lærte at leve med denne løsning - velkommen.
Mit navn er Pavel Sivash, hos DomClick arbejder jeg i et team, der er ansvarlig for at vedligeholde det analytiske datavarehus. Konventionelt kan vores aktiviteter henføres til datateknik, men i virkeligheden er rækken af opgaver meget bredere. Der er standard data engineering ETL/ELT, support og tilpasning af dataanalyseværktøjer og udvikling af egne værktøjer. Specielt til operationel rapportering besluttede vi at "foregive", at vi har en monolit og give analytikere en database, der vil indeholde alle de data, de har brug for.
Generelt overvejede vi forskellige muligheder. Det var muligt at bygge et fuldgyldigt depot - vi prøvede endda, men for at være ærlig var vi ikke i stand til at blive venner med ret hyppige ændringer i logikken med en ret langsom proces med at bygge et lager og foretage ændringer i det ( hvis nogen lykkedes, skriv i kommentarerne hvordan). Man kunne sige til analytikere: "Gunner, lær python og gå til analytiske linjer," men dette er et yderligere rekrutteringskrav, og det så ud til, at dette skulle undgås, hvis det var muligt. Vi besluttede at prøve at bruge FDW-teknologien (Foreign Data Wrapper): Faktisk er dette en standard dblink, som er i SQL-standarden, men med dens meget mere bekvemme grænseflade. På baggrund af den traf vi en beslutning, som til sidst slog rod, vi slog os fast på den. Dens detaljer er emnet for en separat artikel, og måske mere end én, fordi jeg vil tale om meget: fra synkronisering af databaseskemaer til adgangskontrol og depersonalisering af personlige data. Det skal også bemærkes, at denne løsning ikke er en erstatning for rigtige analytiske databaser og repositories, den løser kun et specifikt problem.
På øverste niveau ser det sådan ud:
Der er en PostgreSQL-database, hvor brugere kan gemme deres arbejdsdata, og vigtigst af alt er analytiske replikaer af alle tjenester forbundet til denne database via FDW. Dette gør det muligt at skrive en forespørgsel til flere databaser, og det er lige meget hvad det er: PostgreSQL, MySQL, MongoDB eller noget andet (fil, API, hvis der pludselig ikke er en passende wrapper, kan du skrive din egen). Nå, alt ser ud til at være fantastisk! Slår op?
Hvis alt sluttede så hurtigt og enkelt, ville artiklen sandsynligvis ikke eksistere.
Det er vigtigt at være klar over, hvordan postgres håndterer anmodninger til fjernservere. Dette virker logisk, men ofte er folk ikke opmærksomme på det: postgres opdeler forespørgslen i dele, der udføres uafhængigt på fjernservere, indsamler disse data og udfører selv de endelige beregninger, så forespørgselsudførelseshastigheden vil i høj grad afhænge af, hvordan det er skrevet. Det skal også bemærkes: når dataene kommer fra en fjernserver, har de ikke længere indekser, der er intet, der hjælper planlæggeren, derfor er det kun vi selv, der kan hjælpe og foreslå det. Og det er det, jeg vil tale mere om.
En simpel anmodning og en plan med det
For at vise, hvordan Postgres forespørger på en tabel med 6 millioner rækker på en ekstern server, lad os se på en simpel 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 at bruge VERBOSE-sætningen kan du se den forespørgsel, der sendes til fjernserveren, og resultaterne af, som vi modtager til videre behandling (RemoteSQL-streng).
Lad os gå lidt længere og tilføje flere filtre til vores forespørgsel: et efter boolean felt, en ved indtastning tidsstempel pr. interval og 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 er her øjeblikket ligger, som du skal være opmærksom på, når du skriver forespørgsler. Filtrene blev ikke overført til fjernserveren, hvilket betyder, at for at udføre det, trækker postgres alle 6 millioner rækker for at filtrere lokalt (Filter-linjen) og udføre aggregering senere. Nøglen til succes er at skrive en forespørgsel, så filtrene overføres til den eksterne maskine, og vi modtager og samler kun de nødvendige rækker.
Det er noget booleanshit
Med booleske felter er alt enkelt. I den oprindelige forespørgsel skyldtes problemet operatøren is. Hvis vi erstatter det med =, så 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øj filteret til fjernserveren, og udførelsestiden blev reduceret fra 27 til 19 sekunder.
Det skal bemærkes, at operatøren is forskellig fra operatøren = den, der kan arbejde med Null-værdien. Det betyder at Det er ikke sandt i filteret vil efterlade værdierne False og Null, mens != Sandt vil kun efterlade falske værdier. Derfor ved udskiftning af operatør er ikke du skal overføre to betingelser til filteret med OR-operatoren, f.eks. WHERE (col != True) OR (col er null).
Med boolesk regnet ud, gå videre. Lad os i mellemtiden returnere filteret efter boolesk værdi til dets oprindelige form for selvstændigt at overveje effekten af andre ændringer.
tidsstempel? hz
Generelt er du ofte nødt til at eksperimentere med, hvordan du korrekt skriver en forespørgsel, der involverer fjernservere, og først derefter lede efter en forklaring på, hvorfor dette sker. Meget lidt information om dette kan findes på internettet. Så i eksperimenter fandt vi ud af, at et filter med fast dato flyver til en ekstern server med et brag, men når vi ønsker at indstille datoen dynamisk, for eksempel now() eller CURRENT_DATE, sker det ikke. I vores eksempel har vi tilføjet et filter, så kolonnen create_at indeholder data for præcis 1 måned i fortiden (MELLEM CURRENT_DATE - INTERVAL '7 month' OG CURRENT_DATE - INTERVAL '6 month'). Hvad gjorde vi i denne sag?
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 bad planlæggeren om at beregne datoen på forhånd i underforespørgslen og sende den allerede forberedte variabel til filteret. Og dette tip gav os et fantastisk resultat, forespørgslen blev næsten 6 gange hurtigere!
Igen er det vigtigt at være forsigtig her: datatypen i underforespørgslen skal være den samme som i det felt, som vi filtrerer efter, ellers vil planlæggeren beslutte, at da typerne er forskellige, og det er nødvendigt først at få alle data og filtrer dem lokalt.
Lad os returnere filteret efter dato til dets oprindelige værdi.
Freddy vs. jsonb
Generelt har booleske felter og datoer allerede accelereret vores forespørgsel tilstrækkeligt, men der var endnu en datatype. Kampen med at filtrere efter det, for at være ærlig, er stadig ikke slut, selvom der også er succeser her. Så her er, hvordan vi formåede at passere filteret forbi jsonb felt 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 at filtrere operatører skal du bruge tilstedeværelsen af én operatør. jsonb i en anden. 7 sekunder i stedet for de originale 29. Indtil videre er dette den eneste vellykkede mulighed for at overføre filtre over jsonb til en fjernserver, men her er det vigtigt at tage højde for én begrænsning: vi bruger version 9.6 af databasen, men inden udgangen af april planlægger vi at gennemføre de sidste test og gå over til version 12. Så snart vi opdaterer, vil vi skrive, hvordan det påvirkede, for der er mange ændringer, som der er mange forhåbninger om: json_path, ny CTE-adfærd, push down (eksisterer fra version 10). Jeg vil virkelig gerne prøve det snart.
Gøre det af med ham
Vi kontrollerede, hvordan hver ændring påvirker forespørgselshastigheden individuelt. Lad os nu se, hvad der sker, når alle tre filtre er skrevet korrekt.
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, forespørgslen ser mere kompliceret ud, det er en tvungen pris, men udførelseshastigheden er 2 sekunder, hvilket er mere end 10 gange hurtigere! Og vi taler om en simpel forespørgsel på et relativt lille sæt data. På reelle forespørgsler modtog vi en stigning på op til flere hundrede gange.
For at opsummere det: hvis du bruger PostgreSQL med FDW, så tjek altid om alle filtre er sendt til fjernserveren, og du vil være glad... I hvert fald indtil du kommer til joins mellem tabeller fra forskellige servere. Men det er en historie til en anden artikel.
Tak for din opmærksomhed! Jeg vil meget gerne høre spørgsmål, kommentarer og historier om dine oplevelser i kommentarerne.
Kilde: www.habr.com