Architektúra mikroservisov, ako všetko na tomto svete, má svoje klady a zápory. Niektoré procesy sú s ním jednoduchšie, iné ťažšie. A kvôli rýchlosti zmien a lepšej škálovateľnosti musíte niečo obetovať. Jedným z nich je zložitosť analýzy. Ak sa v monolite dá všetka prevádzková analytika zredukovať na dotazy SQL na analytickú repliku, potom v architektúre viacerých služieb má každá služba svoju vlastnú databázu a zdá sa, že jeden dotaz nestačí (alebo možno bude?). Pre tých, ktorých zaujíma, ako sme vyriešili problém prevádzkovej analytiky v našej spoločnosti a ako sme sa s týmto riešením naučili žiť - vítaní.

Volám sa Pavel Sivash, v DomClick pracujem v tíme, ktorý je zodpovedný za údržbu analytického dátového skladu. Bežne možno naše aktivity pripísať dátovému inžinierstvu, ale v skutočnosti je rozsah úloh oveľa širší. Nechýba štandardné dátové inžinierstvo ETL/ELT, podpora a adaptácia nástrojov na analýzu dát a vývoj vlastných nástrojov. Najmä pri operatívnom reportingu sme sa rozhodli „predstierať“, že máme monolit, a poskytnúť analytikom jednu databázu, ktorá bude obsahovať všetky údaje, ktoré potrebujú.

Vo všeobecnosti sme zvažovali rôzne možnosti. Bolo možné vybudovať plnohodnotné úložisko - dokonca sme to skúšali, ale úprimne povedané, nedokázali sme sa spriateliť s pomerne častými zmenami v logike s pomerne pomalým procesom budovania úložiska a vykonávania zmien ( ak sa to niekomu podarilo, napíšte do komentárov ako). Analytikom by ste mohli povedať: „Chlapci, naučte sa python a choďte na analytické línie,“ ale toto je dodatočná požiadavka na nábor a zdalo sa, že by sme sa tomu mali vyhnúť, ak je to možné. Rozhodli sme sa vyskúšať technológiu FDW (Foreign Data Wrapper): v skutočnosti ide o štandardný dblink, ktorý je v štandarde SQL, ale s oveľa pohodlnejším rozhraním. Na základe toho sme urobili rozhodnutie, ktoré sa nakoniec udomácnilo, ustáli sme sa na ňom. Jeho podrobnosti sú témou samostatného článku a možno viac ako jedného, ​​pretože chcem hovoriť o veľa: od synchronizácie schém databázy po riadenie prístupu a depersonalizáciu osobných údajov. Treba tiež poznamenať, že toto riešenie nie je náhradou skutočných analytických databáz a repozitárov, rieši len špecifický problém.

Na najvyššej úrovni to vyzerá takto:

Existuje PostgreSQL databáza, do ktorej si užívatelia môžu ukladať svoje pracovné dáta, a čo je najdôležitejšie, analytické repliky všetkých služieb sú k tejto databáze pripojené cez FDW. To umožňuje napísať dotaz do niekoľkých databáz a nezáleží na tom, čo to je: PostgreSQL, MySQL, MongoDB alebo niečo iné (súbor, API, ak zrazu neexistuje vhodný obal, môžete napísať svoj vlastný). No, všetko sa zdá byť skvelé! Rozísť sa?

Ak by sa všetko skončilo tak rýchlo a jednoducho, článok by pravdepodobne neexistoval.

Je dôležité mať jasno v tom, ako postgres spracováva požiadavky na vzdialené servery. Zdá sa to logické, ale ľudia tomu často nevenujú pozornosť: postgres rozdeľuje dotaz na časti, ktoré sa vykonávajú nezávisle na vzdialených serveroch, zbiera tieto údaje a sám vykonáva konečné výpočty, takže rýchlosť vykonania dotazu bude značne závisieť od toho, ako je to napísané. Malo by sa tiež poznamenať: keď údaje pochádzajú zo vzdialeného servera, už nemajú indexy, plánovaču nič nepomôže, preto len my sami môžeme pomôcť a navrhnúť to. A o tom chcem hovoriť podrobnejšie.

Jednoduchá požiadavka a plán s ňou

Aby sme ukázali, ako Postgres dopytuje tabuľku so 6 miliónmi riadkov na vzdialenom serveri, pozrime sa na jednoduchý plán.

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

Pomocou príkazu VERBOSE môžete vidieť dopyt, ktorý bude odoslaný na vzdialený server a ktorého výsledky dostaneme na ďalšie spracovanie (reťazec RemoteSQL).

Poďme trochu ďalej a do nášho dopytu pridáme niekoľko filtrov: jeden by boolean pole, jeden po vstupe časová značka za interval a jeden 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 ten moment, na ktorý si treba dať pozor pri písaní dopytov. Filtre neboli prenesené na vzdialený server, čo znamená, že na ich vykonanie postgres stiahne všetkých 6 miliónov riadkov, aby sa filtroval lokálne (riadok Filter) a vykonal agregáciu neskôr. Kľúčom k úspechu je napísať dotaz tak, aby sa filtre preniesli na vzdialený počítač a my sme dostali a agregovali len potrebné riadky.

To je nejaká hlúposť

S booleovskými poľami je všetko jednoduché. V pôvodnom dopyte bol problém spôsobený operátorom is. Ak ho nahradíme =, potom dostaneme nasledujúci výsledok:

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

Ako vidíte, filter preletel na vzdialený server a čas vykonania sa skrátil z 27 na 19 sekúnd.

Treba si uvedomiť, že prevádzkovateľ is odlišný od operátora = ten, ktorý dokáže pracovať s hodnotou Null. Znamená to, že to nie je pravda vo filtri ponechajú hodnoty False a Null, zatiaľ čo != Pravda ponechá len Falošné hodnoty. Preto pri výmene operátora nie je mali by ste zadať dve podmienky do filtra s operátorom OR, napr. WHERE (stĺpec != True) OR (stĺpec je null).

Po vyriešení booleanov ideme ďalej. Medzitým vráťme filter podľa boolovskej hodnoty do pôvodnej podoby, aby sme nezávisle zvážili vplyv iných zmien.

timestamptz? hz

Vo všeobecnosti musíte často experimentovať s tým, ako správne napísať dotaz, ktorý zahŕňa vzdialené servery, a až potom hľadať vysvetlenie, prečo sa to deje. Na internete sa o tom dá nájsť veľmi málo informácií. V experimentoch sme teda zistili, že filter s pevným dátumom letí na vzdialený server s ranou, ale keď chceme dátum nastaviť dynamicky, napríklad now() alebo CURRENT_DATE, nestane sa to. V našom príklade sme pridali filter, takže stĺpec created_at obsahuje údaje presne za 1 mesiac v minulosti (BETWEEN CURRENT_DATE – INTERVAL „7 mesiacov“ A CURRENT_DATE – INTERVAL „6 mesiacov“). Čo sme urobili v tomto prípade?

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

Plánovača sme vyzvali, aby v poddotaze vopred vypočítal dátum a filtru odovzdal už pripravenú premennú. A tento tip nám dal skvelý výsledok, dopyt sa stal takmer 6-krát rýchlejším!

Tu je opäť dôležité dávať pozor: typ údajov v poddotazi musí byť rovnaký ako typ poľa, podľa ktorého filtrujeme, inak plánovač rozhodne, že keďže typy sú odlišné a je potrebné najprv získať všetky údaje a lokálne ich filtrovať.

Vráťme filter podľa dátumu na pôvodnú hodnotu.

Freddy vs. jsonb

Vo všeobecnosti boolovské polia a dátumy už dostatočne zrýchlili náš dotaz, no bol tu ešte jeden typ údajov. Úprimne povedané, boj s filtrovaním sa stále neskončil, hoci aj tu existujú úspechy. Takže, tu je návod, ako sa nám podarilo prejsť filtrom jsonb poľa na vzdialený 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

Namiesto filtrovania operátorov musíte použiť prítomnosť jedného operátora jsonb v inom. 7 sekúnd namiesto pôvodných 29. Toto je zatiaľ jediná úspešná možnosť prenosu filtrov jsonb na vzdialený server, ale tu je dôležité vziať do úvahy jedno obmedzenie: používame verziu databázy 9.6, no do konca apríla plánujeme dokončiť posledné testy a prejsť na verziu 12. Hneď ako aktualizujeme, napíšeme, ako to ovplyvnilo, pretože je veľa zmien, na ktoré sa veľa dúfa: json_path, nové správanie CTE, push down (existuje od verzie 10). Naozaj to chcem čoskoro vyskúšať.

Dokončiť ho

Skontrolovali sme, ako každá zmena ovplyvňuje rýchlosť dopytovania jednotlivo. Pozrime sa teraz, čo sa stane, keď sú všetky tri filtre napísané správne.

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

Áno, dotaz vyzerá komplikovanejšie, je to vynútená cena, ale rýchlosť vykonania je 2 sekundy, čo je viac ako 10-krát rýchlejšie! A to hovoríme o jednoduchom dotaze na relatívne malý súbor údajov. Na reálne požiadavky sme dostali až niekoľko stonásobné navýšenie.

Aby som to zhrnul: ak používate PostgreSQL s FDW, vždy skontrolujte, či sú všetky filtre odoslané na vzdialený server a budete spokojní... Aspoň kým sa nedostanete k spojom medzi tabuľkami z rôznych serverov. Ale to je už príbeh na iný článok.

Ďakujem za tvoju pozornosť! Rád by som si v komentároch vypočul otázky, komentáre a príbehy o vašich skúsenostiach.

Zdroj: hab.com