Mikroservisna arhitektura, kao i sve na ovom svijetu, ima svoje prednosti i nedostatke. Neki procesi s njim postaju lakši, drugi teži. A zarad brzine promjene i bolje skalabilnosti, morate se žrtvovati. Jedna od njih je složenost analitike. Ako se u monolitu sva operativna analitika može svesti na SQL upite do analitičke replike, onda u multiservisnoj arhitekturi svaki servis ima svoju bazu podataka i čini se da jedan upit nije dovoljan (ili možda hoće?). Za one koje zanima kako smo riješili problem operativne analitike u našoj kompaniji i kako smo naučili živjeti s ovim rješenjem - dobrodošli.

Moje ime je Pavel Sivash, u DomClick-u radim u timu koji je odgovoran za održavanje skladišta analitičkih podataka. Uobičajeno, naše aktivnosti se mogu pripisati inženjeringu podataka, ali, zapravo, raspon zadataka je mnogo širi. Postoje standardni data inženjering ETL/ELT, podrška i adaptacija alata za analizu podataka i razvoj vlastitih alata. Konkretno, za operativno izvještavanje odlučili smo da se “pretvaramo” da imamo monolit i damo analitičarima jednu bazu podataka koja će sadržavati sve podatke koji su im potrebni.

Općenito, razmatrali smo različite opcije. Bilo je moguće napraviti punopravno spremište - čak smo i pokušali, ali, da budemo iskreni, nismo bili u mogućnosti da se sprijateljimo s prilično čestim promjenama u logici s prilično sporim procesom izgradnje spremišta i unošenja promjena u njega ( ako je neko uspio, napišite u komentarima kako). Analitičarima biste mogli reći: „Momci, naučite python i idite na analitičke linije“, ali ovo je dodatni zahtjev za zapošljavanje i činilo se da to treba izbjegavati ako je moguće. Odlučili smo pokušati koristiti FDW (Foreign Data Wrapper) tehnologiju: u stvari, ovo je standardni dblink, koji je u SQL standardu, ali sa svojim mnogo praktičnijim sučeljem. Na osnovu toga smo donijeli odluku koja je na kraju zaživjela, mi smo se na njoj dogovorili. Njegovi detalji su tema posebnog članka, a možda i više od jednog, jer želim da pričam o mnogo čemu: od sinhronizacije šeme baze podataka do kontrole pristupa i depersonalizacije ličnih podataka. Također treba napomenuti da ovo rješenje nije zamjena za prave analitičke baze podataka i spremišta, već samo rješava konkretan problem.

Na najvišem nivou to izgleda ovako:

Postoji PostgreSQL baza podataka u koju korisnici mogu pohranjivati ​​svoje radne podatke, a što je najvažnije, analitičke replike svih servisa su povezane sa ovom bazom podataka preko FDW-a. Ovo omogućava pisanje upita za nekoliko baza podataka, i nije važno šta je to: PostgreSQL, MySQL, MongoDB ili nešto drugo (fajl, API, ako odjednom nema odgovarajućeg omotača, možete napisati svoj). Pa, izgleda da je sve super! Raskid?

Da se sve završilo tako brzo i jednostavno, onda, vjerovatno, članak ne bi postojao.

Važno je da bude jasno kako postgres rukuje zahtjevima udaljenim serverima. Ovo se čini logičnim, ali ljudi često ne obraćaju pažnju na to: postgres dijeli upit na dijelove koji se samostalno izvršavaju na udaljenim serverima, prikuplja te podatke i sam obavlja konačne proračune, tako da će brzina izvršavanja upita uvelike ovisiti o tome kako napisano je. Također treba napomenuti: kada podaci dolaze sa udaljenog servera, oni više nemaju indekse, ne postoji ništa što će pomoći planeru, stoga samo mi sami možemo pomoći i predložiti. I to je ono o čemu želim detaljnije da pričam.

Jednostavan zahtjev i plan sa njim

Da pokažemo kako Postgres ispituje tabelu od 6 miliona redova na udaljenom serveru, pogledajmo jednostavan 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 ms

Korišćenje naredbe VERBOSE vam omogućava da vidite upit koji će biti poslan udaljenom serveru i čije ćemo rezultate dobiti na dalju obradu (RemoteSQL string).

Idemo malo dalje i dodamo nekoliko filtera našem upitu: jedan po boolean polje, jedan po unos timestamp po intervalu i jedan po 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 ms

Tu leži trenutak na koji morate obratiti pažnju prilikom pisanja upita. Filteri nisu prebačeni na udaljeni server, što znači da da bi ga izvršio, postgres povlači svih 6 miliona redova kako bi filtrirao lokalno (filter linija) i kasnije izvršio agregaciju. Ključ uspjeha je napisati upit tako da se filteri prenesu na udaljenu mašinu, a mi primamo i agregiramo samo potrebne redove.

To je glupost

Sa logičkim poljima, sve je jednostavno. U originalnom upitu, problem je nastao zbog operatera is. Ako ga zamijenimo sa =, tada dobijamo sljedeći rezultat:

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 ms

Kao što vidite, filter je odleteo na udaljeni server, a vreme izvršenja je smanjeno sa 27 na 19 sekundi.

Treba napomenuti da operater is različito od operatera = onaj koji može raditi sa Null vrijednošću. To znači da nije istina u filteru će ostaviti vrijednosti False i Null, while != Tačno će ostaviti samo False vrijednosti. Stoga, prilikom zamjene operatera nije trebali biste prenijeti dva uslova filteru sa OR operatorom, na primjer, WHERE (col != Tačno) OR (kol je null).

Sa logički izračunatim, idemo dalje. U međuvremenu, vratimo filter po booleanskoj vrijednosti u originalni oblik kako bismo samostalno razmotrili efekte drugih promjena.

timestamptz? hz

Općenito, često morate eksperimentirati kako ispravno napisati upit koji uključuje udaljene servere, a tek onda tražiti objašnjenje zašto se to događa. Na internetu se može naći vrlo malo informacija o tome. Dakle, u eksperimentima smo otkrili da filter fiksnog datuma leti na udaljeni server sa praskom, ali kada želimo da postavimo datum dinamički, na primjer, now() ili CURRENT_DATE, to se ne dešava. U našem primjeru smo dodali filter tako da kolona created_at sadrži podatke za tačno 1 mjesec u prošlosti (IZMEĐU TRENUTNOG_DATE - INTERVAL '7 mjeseci' I CURRENT_DATE - INTERVAL '6 mjeseci'). Šta smo uradili u ovom slučaju?

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 ms

Podstaknuli smo planera da unapred izračuna datum u potupitu i prosledi već pripremljenu varijablu u filter. I ovaj savjet nam je dao odličan rezultat, upit je postao skoro 6 puta brži!

Opet, ovdje je važno biti oprezan: tip podataka u potupitu mora biti isti kao u polju po kojem filtriramo, inače će planer odlučiti da su tipovi različiti i potrebno je prvo dobiti sve podatke i filtrirajte ih lokalno.

Vratimo filter po datumu na njegovu originalnu vrijednost.

Freddy vs. jsonb

Općenito, logička polja i datumi su već dovoljno ubrzali naš upit, ali postojao je još jedan tip podataka. Bitka sa filtriranjem po njoj, da budemo iskreni, još uvijek nije završena, iako i ovdje ima uspjeha. Dakle, evo kako smo uspjeli proći kroz filter jsonb polje na udaljeni 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 ms

Umjesto operatora filtriranja, morate koristiti prisustvo jednog operatora. jsonb u drugačijem. 7 sekundi umjesto originalnih 29. Do sada je ovo jedina uspješna opcija za prijenos filtera preko jsonb na udaljeni server, ali ovdje je važno uzeti u obzir jedno ograničenje: koristimo verziju 9.6 baze podataka, ali do kraja travnja planiramo završiti posljednje testove i preći na verziju 12. Kada ažuriramo, napisaćemo kako je to utjecalo, jer ima puno promjena za koje postoji mnogo nade: json_path, novo CTE ponašanje, push down (postoje od verzije 10). Zaista želim da probam uskoro.

Završiti ga

Provjerili smo kako svaka promjena pojedinačno utječe na brzinu upita. Hajde sada da vidimo šta se dešava kada su sva tri filtera ispravno napisana.

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 ms

Da, upit izgleda komplikovanije, to je iznuđena cijena, ali brzina izvršavanja je 2 sekunde, što je više od 10 puta brže! A mi govorimo o jednostavnom upitu na relativno malom skupu podataka. Na stvarne zahtjeve dobili smo povećanje i do nekoliko stotina puta.

Da sumiramo: ako koristite PostgreSQL sa FDW, uvijek provjerite da li su svi filteri poslani na udaljeni server i bićete sretni... Barem dok ne dođete do spajanja između tabela sa različitih servera. Ali to je priča za drugi članak.

Hvala vam na pažnji! Volio bih čuti pitanja, komentare i priče o vašim iskustvima u komentarima.

izvor: www.habr.com