Mikroservisna arhitektura, kao i sve na ovom svijetu, ima svoje prednosti i nedostatke. Neki procesi s njim postaju lakši, drugi teži. A radi brzine promjena i bolje skalabilnosti morate se žrtvovati. Jedan od njih je sve veća 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 se jedan upit ne može napraviti (ili možda može?). Za one koje zanima kako smo riješili problem operativne analitike u našoj tvrtki i kako smo naučili živjeti s tim rješenjem - dobrodošli.

Moje ime je Pavel Sivash, u DomClicku radim u timu koji je odgovoran za održavanje skladišta analitičkih podataka. Konvencionalno se naše aktivnosti mogu klasificirati kao podatkovni inženjering, no zapravo je raspon zadataka puno širi. Postoji ETL/ELT standard za podatkovni inženjering, podršku i prilagodbu alata za analizu podataka i razvoj vlastitih alata. Konkretno, za operativno izvješćivanje odlučili smo se “praviti” da imamo monolit i dati analitičarima jednu bazu koja će sadržavati sve podatke koji su im potrebni.

Općenito, razmatrali smo različite mogućnosti. Bilo je moguće izgraditi punopravno spremište - čak smo i pokušali, ali, da budemo iskreni, nismo uspjeli kombinirati prilično česte promjene u logici s prilično sporim procesom izgradnje spremišta i izmjena u njemu (ako je netko uspio , napišite u komentarima kako). Moglo se reći analitičarima: “Dečki, naučite python i idite na analitičke replike”, ali to je dodatni uvjet za zapošljavanje i činilo se da to treba izbjeći ako je moguće. Odlučili smo pokušati koristiti FDW (Foreign Data Wrapper) tehnologiju: u biti, ovo je standardni dblink, koji je u SQL standardu, ali sa svojim mnogo praktičnijim sučeljem. Na temelju toga smo napravili rješenje, koje je na kraju zaživjelo i na njega smo se ustalili. Njegovi detalji tema su zasebnog članka, a možda i više od jednog, jer želim razgovarati o puno toga: od sinkronizacije shema baze podataka do kontrole pristupa i depersonalizacije osobnih podataka. Također je potrebno napomenuti da ovo rješenje nije zamjena za prave analitičke baze podataka i repozitorije, ono rješava samo određeni problem.

Na najvišoj razini to izgleda ovako:

Postoji PostgreSQL baza podataka u koju korisnici mogu pohraniti svoje podatke o radu, a što je najvažnije, analitičke replike svih servisa povezane su s ovom bazom putem FDW-a. To omogućuje pisanje upita u nekoliko baza podataka, i nije važno što je to: PostgreSQL, MySQL, MongoDB ili nešto drugo (datoteka, API, ako iznenada nema odgovarajućeg omotača, možete napisati svoj). Pa sve se čini super! Da li se rastajemo?

Da je sve završilo tako brzo i jednostavno, onda, vjerojatno, ne bi bilo članka.

Važno je jasno znati kako Postgres obrađuje zahtjeve udaljenim poslužiteljima. Ovo se čini logičnim, ali ljudi često ne obraćaju pažnju na to: Postgres dijeli zahtjev na dijelove koji se samostalno izvršavaju na udaljenim poslužiteljima, prikuplja te podatke i sam izvodi konačne izračune, tako da će brzina izvršenja upita uvelike ovisiti o kako je napisano. Također treba napomenuti: kada podaci stignu s udaljenog poslužitelja, oni više nemaju indekse, ne postoji ništa što bi moglo pomoći planeru, stoga mu samo mi sami možemo pomoći i savjetovati ga. I upravo o tome želim detaljnije govoriti.

Jednostavan upit i plan s njim

Da pokažemo kako Postgres postavlja upite tablici od 6 milijuna redaka na udaljenom poslužitelju, 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štenje naredbe VERBOSE omogućuje nam uvid u upit koji će biti poslan na udaljeni poslužitelj i čije rezultate ćemo dobiti na daljnju obradu (RemoteSQL linija).

Idemo malo dalje i našem zahtjevu dodamo nekoliko filtara: jedan za boolean polje, jedno po pojavi vremenska oznaka u intervalu i jedan po jedan 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 točka na koju morate obratiti pozornost prilikom pisanja upita. Filtri nisu prebačeni na udaljeni poslužitelj, što znači da za njegovo izvršenje Postgres izvlači svih 6 milijuna redaka kako bi zatim filtrirao lokalno (Filter row) i izvršio agregaciju. Ključ uspjeha je napisati upit tako da se filteri prenesu na udaljeni stroj, a mi primimo i agregiramo samo potrebne retke.

To je neko boolean sranje

S booleovim poljima sve je jednostavno. U izvornom zahtjevu problem je nastao zbog operatera is. Ako ga zamijenite sa =, tada dobivamo 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, filtar je odletio na udaljeni poslužitelj, a vrijeme izvršenja smanjeno je s 27 na 19 sekundi.

Vrijedno je napomenuti da operater is različit od operatera = jer može raditi s Null vrijednošću. To znači da nije istina ostavit će vrijednosti False i Null u filtru, dok != Istina ostavit će samo False vrijednosti. Stoga se kod zamjene operatera nije dva uvjeta s operatorom OR treba proslijediti filtru, na primjer, WHERE (kolona != Istina) ILI (kolona je nula).

S Booleovim smo se pozabavili, idemo dalje. Za sada vratimo Boolean filtar u izvorni oblik kako bismo samostalno razmotrili učinak ostalih promjena.

vremenska oznaka? hz

Općenito, često morate eksperimentirati kako pravilno napisati zahtjev koji uključuje udaljene poslužitelje, a tek onda tražiti objašnjenje zašto se to događa. Vrlo malo informacija o tome može se pronaći na internetu. Dakle, u eksperimentima smo otkrili da filtar fiksnog datuma leti do udaljenog poslužitelja s treskom, ali kada želimo dinamički postaviti datum, na primjer, now() ili CURRENT_DATE, to se ne događa. U našem smo primjeru dodali filtar tako da je stupac created_at sadržavao podatke za točno 1 mjesec u prošlosti (IZMEĐU CURRENT_DATE - INTERVAL '7 mjeseci' I CURRENT_DATE - INTERVAL '6 mjeseci'). Što smo učinili 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

Rekli smo planeru da unaprijed izračuna datum u podupitu i proslijedi gotovu varijablu filtru. I ovaj nam je savjet dao odličan rezultat, zahtjev je postao gotovo 6 puta brži!

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

Vratimo filtar datuma na izvornu vrijednost.

Freddy vs. Jsonb

Općenito, Booleova polja i datumi već su dovoljno ubrzali naš upit, ali je ostao još jedan tip podataka. Bitka s filtriranjem po njemu, istina, još uvijek nije gotova, iako i tu ima uspjeha. Dakle, ovako smo uspjeli proći filter jsonb polje na udaljeni poslužitelj.

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 filtriranja operatora, morate koristiti prisutnost jednog operatora jsonb u drugačijem. 7 sekundi umjesto originalnih 29. Za sada je to jedina uspješna opcija za prijenos filtara putem jsonb na udaljeni poslužitelj, no ovdje je važno uzeti u obzir jedno ograničenje: koristimo verziju 9.6 baze podataka, no do kraja travnja planiramo završiti posljednje testove i prijeći na verziju 12. Kada ažuriramo, pisat ćemo o tome kako je to utjecalo, jer postoji dosta promjena za koje ima puno nade: json_path, novo CTE ponašanje, push down (postoji od verzije 10). Stvarno ga želim uskoro isprobati.

Dovrši ga

Testirali smo kako je svaka promjena pojedinačno utjecala na brzinu zahtjeva. Pogledajmo sada što se događa 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, zahtjev izgleda kompliciranije, ovo je prisilna naknada, ali brzina izvršenja je 2 sekunde, što je više od 10 puta brže! A mi govorimo o jednostavnom upitu prema relativno malom skupu podataka. Na stvarne zahtjeve dobili smo povećanje i do nekoliko stotina puta.

Da rezimiramo: ako koristite PostgreSQL s FDW-om, uvijek provjerite šalju li se svi filteri na udaljeni poslužitelj i bit ćete sretni... Barem dok ne dođete do spajanja između tablica s različitih poslužitelja. Ali to je priča za drugi članak.

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

Izvor: www.habr.com