Mikrostoritvena arhitektura ima, tako kot vse na tem svetu, svoje prednosti in slabosti. Nekateri procesi z njim postanejo lažji, drugi težji. In zaradi hitrosti sprememb in boljše razširljivosti se morate žrtvovati. Eden od njih je vse večja kompleksnost analitike. Če je v monolitu vso operativno analitiko mogoče reducirati na poizvedbe SQL do analitične replike, potem ima v večstoritveni arhitekturi vsaka storitev svojo lastno bazo podatkov in zdi se, da ene poizvedbe ni mogoče narediti (ali morda lahko?). Za tiste, ki vas zanima, kako smo v našem podjetju rešili problem operativne analitike in kako smo se s to rešitvijo naučili živeti - dobrodošli.

Moje ime je Pavel Sivash, v DomClicku delam v ekipi, ki skrbi za vzdrževanje skladišča analitičnih podatkov. Konvencionalno lahko naše dejavnosti uvrstimo med podatkovni inženiring, v bistvu pa je nabor nalog veliko širši. Obstaja ETL/ELT standard za podatkovni inženiring, podporo in prilagajanje orodij za analizo podatkov ter razvoj lastnih orodij. Predvsem za operativno poročanje smo se odločili, da se »pretvarjamo«, da imamo monolit in damo analitikom eno bazo podatkov, ki bo vsebovala vse podatke, ki jih potrebujejo.

Na splošno smo obravnavali različne možnosti. Bilo je mogoče zgraditi polnopravno skladišče - celo poskušali smo, a, če smo iskreni, nismo mogli združiti dokaj pogostih sprememb v logiki s precej počasnim procesom gradnje skladišča in spreminjanja vanj (če je komu uspelo , v komentarje napišite kako). Analitikom je bilo mogoče reči: »Fantje, naučite se pythona in pojdite na analitične replike,« vendar je to dodatna zahteva za zaposlovanje in zdelo se je, da se je temu treba izogniti, če je le mogoče. Odločili smo se, da poskusimo uporabiti tehnologijo FDW (Foreign Data Wrapper): v bistvu je to standardni dblink, ki je v standardu SQL, vendar s svojim veliko bolj priročnim vmesnikom. Na podlagi tega smo izdelali rešitev, ki se je sčasoma prijela in smo se na njej odločili. Njegove podrobnosti so tema ločenega članka in morda več kot enega, saj želim govoriti o veliko: od sinhronizacije shem baz podatkov do nadzora dostopa in depersonalizacije osebnih podatkov. Prav tako je treba opozoriti, da ta rešitev ni nadomestilo za prave analitične baze podatkov in repozitorije, ampak rešuje le določen problem.

Na najvišji ravni je videti takole:

Obstaja baza podatkov PostgreSQL, kamor lahko uporabniki hranijo svoje delovne podatke, predvsem pa so prek FDW na to bazo povezane analitične replike vseh storitev. To omogoča pisanje poizvedbe v več bazah podatkov in ni pomembno, kaj je: PostgreSQL, MySQL, MongoDB ali kaj drugega (datoteka, API, če nenadoma ni ustreznega ovoja, lahko napišete svojo). No, vse se zdi super! Se razhajava?

Če bi se vse končalo tako hitro in preprosto, potem verjetno ne bi bilo članka.

Pomembno je, da je jasno, kako Postgres obdeluje zahteve oddaljenim strežnikom. To se zdi logično, a pogosto ljudje na to niso pozorni: Postgres razdeli zahtevo na dele, ki se izvajajo neodvisno na oddaljenih strežnikih, zbira te podatke in sam izvede končne izračune, zato bo hitrost izvajanja poizvedbe močno odvisna od kako je napisano. Treba je še opozoriti: ko podatki prispejo z oddaljenega strežnika, nimajo več indeksov, razporejevalniku ni ničesar, kar bi pomagalo, zato mu lahko pomagamo in svetujemo samo mi sami. In prav o tem želim govoriti podrobneje.

Preprosta poizvedba in načrt z njo

Da pokažemo, kako Postgres poizveduje po tabeli s 6 milijoni vrstic na oddaljenem strežniku, si poglejmo preprost načrt.

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

Uporaba stavka VERBOSE nam omogoča vpogled v poizvedbo, ki bo poslana na oddaljeni strežnik in katere rezultate bomo prejeli v nadaljnjo obdelavo (vrstica RemoteSQL).

Pojdimo še malo naprej in svoji zahtevi dodamo več filtrov: enega za boolean polje, eno po pojavitvi Časovni žig v intervalu in enega 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 je tista točka, na katero morate biti pozorni pri pisanju poizvedb. Filtri niso bili preneseni na oddaljeni strežnik, kar pomeni, da za njegovo izvedbo Postgres izvleče vseh 6 milijonov vrstic, da nato lokalno filtrira (Filter row) in izvede združevanje. Ključ do uspeha je napisati poizvedbo, tako da se filtri prenesejo na oddaljeno napravo, mi pa prejmemo in združimo samo potrebne vrstice.

To je nekaj logičnega sranja

Z logičnimi polji je vse preprosto. V prvotni zahtevi je bila težava posledica operaterja is. Če ga zamenjate z =, potem dobimo naslednji 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

Kot lahko vidite, je filter odletel na oddaljeni strežnik, čas izvajanja pa se je skrajšal s 27 na 19 sekund.

Omeniti velja, da operater is drugačen od operaterja = ker lahko deluje z vrednostjo Null. To pomeni, da ni res bo v filtru pustil vrednosti False in Null, medtem ko != Res je bo pustil samo vrednosti False. Zato pri zamenjavi operaterja ni filtru je treba posredovati dva pogoja z operatorjem ALI, na primer, WHERE (stolpec != True) ALI (stolpec je nič).

Ukvarjali smo se z logično vrednostjo, pojdimo naprej. Za zdaj vrnimo logični filter v prvotno obliko, da neodvisno upoštevamo učinek drugih sprememb.

časovni žig? hz

Na splošno morate pogosto eksperimentirati, kako pravilno napisati zahtevo, ki vključuje oddaljene strežnike, in šele nato poiskati razlago, zakaj se to zgodi. Na internetu je o tem mogoče najti zelo malo informacij. Tako smo v poskusih ugotovili, da filter s fiksnim datumom poleti do oddaljenega strežnika s treskom, ko pa želimo datum nastaviti dinamično, na primer zdaj() ali CURRENT_DATE, se to ne zgodi. V našem primeru smo dodali filter, tako da je stolpec created_at vseboval podatke za točno 1 mesec v preteklosti (MED CURRENT_DATE – INTERVAL '7 mesecev' IN CURRENT_DATE - INTERVAL '6 mesecev'). Kaj smo storili v tem primeru?

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

Načrtovalcu smo naročili, naj vnaprej izračuna datum v podpoizvedbi in filtru posreduje že pripravljeno spremenljivko. In ta namig nam je dal odličen rezultat, zahteva je postala skoraj 6-krat hitrejša!

Ponovno velja biti previden: tip podatkov v podpoizvedbi mora biti enak tipu podatkov polja, po katerem filtriramo, sicer se bo načrtovalec odločil, da je treba, ker so tipi različni, najprej pridobiti vse podatke in jih filtrirajte lokalno.

Vrnimo datumski filter na prvotno vrednost.

Freddy vs. Jsonb

Na splošno so logična polja in datumi že dovolj pospešili našo poizvedbo, vendar je ostal še en tip podatkov. Boj s filtriranjem po njem, resnici na ljubo, še ni končan, čeprav je tudi tu uspeh. Torej, tako nam je uspelo prestati filter jsonb polje v oddaljeni strežnik.

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

Namesto filtriranja operaterjev morate uporabiti prisotnost enega operaterja jsonb v drugačnem. 7 sekund namesto prvotnih 29. To je zaenkrat edina uspešna možnost prenosa filtrov prek jsonb na oddaljeni strežnik, vendar je pri tem pomembno upoštevati eno omejitev: uporabljamo verzijo baze podatkov 9.6, a do konca aprila načrtujemo dokončanje zadnjih testov in prehod na verzijo 12. Ko posodobimo, bomo pisali o tem, kako je vplivalo, ker je kar nekaj sprememb, za katere obstaja veliko upanja: json_path, novo obnašanje CTE, push down (obstaja od verzije 10). Zelo si ga želim kmalu poskusiti.

Pokončaj ga

Preizkusili smo, kako je vsaka sprememba vplivala na hitrost zahtevka posebej. Poglejmo zdaj, kaj se zgodi, ko so vsi trije filtri pravilno zapisani.

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, zahteva je videti bolj zapletena, to je prisilna pristojbina, vendar je hitrost izvedbe 2 sekundi, kar je več kot 10-krat hitreje! In govorimo o preprosti poizvedbi glede na relativno majhen nabor podatkov. Na realne zahteve smo prejeli tudi do nekajstokratno povečanje.

Če povzamem: če uporabljaš PostgreSQL s FDW, vedno preveri, ali so vsi filtri poslani na oddaljeni strežnik, pa boš srečen... Vsaj dokler ne prideš do združevanj med tabelami iz različnih strežnikov. Toda to je zgodba za drug članek.

Hvala za vašo pozornost! V komentarjih bi rad slišal vprašanja, komentarje in zgodbe o vaših izkušnjah.

Vir: www.habr.com