Architektura mikroservisů, stejně jako vše na tomto světě, má své klady a zápory. Některé procesy se s ním stanou jednodušší, jiné obtížnější. A kvůli rychlosti změn a lepší škálovatelnosti je třeba se obětovat. Jedním z nich je složitost analýzy. Pokud lze v monolitu veškerou provozní analytiku zredukovat na dotazy SQL na analytickou repliku, pak v architektuře více služeb má každá služba svou vlastní databázi a zdá se, že jeden dotaz nestačí (nebo možná bude?). Pro ty, které zajímá, jak jsme vyřešili problém provozní analytiky v naší společnosti a jak jsme se s tímto řešením naučili žít - vítáme.

Jmenuji se Pavel Sivash, ve společnosti DomClick pracuji v týmu, který má na starosti správu analytického datového skladu. Běžně lze naše aktivity připsat datovému inženýrství, ale ve skutečnosti je rozsah úkolů mnohem širší. K dispozici je standardní datové inženýrství ETL/ELT, podpora a adaptace nástrojů pro analýzu dat a vývoj vlastních nástrojů. Zejména pro operativní reporting jsme se rozhodli „předstírat“, že máme monolit, a dát analytikům jednu databázi, která bude obsahovat všechna data, která potřebují.

Obecně jsme zvažovali různé možnosti. Bylo možné vybudovat plnohodnotné úložiště - dokonce jsme to zkoušeli, ale upřímně řečeno, nedokázali jsme se spřátelit s poměrně častými změnami v logice s poměrně pomalým procesem budování úložiště a provádění změn ( pokud se to někomu povedlo, napište do komentářů jak). Dalo by se říci analytikům: „Kluci, naučte se python a jděte na analytické linie,“ ale to je další požadavek na nábor a zdálo se, že by se tomu mělo pokud možno vyhnout. Rozhodli jsme se zkusit použít technologii FDW (Foreign Data Wrapper): ve skutečnosti se jedná o standardní dblink, který je ve standardu SQL, ale s mnohem pohodlnějším rozhraním. Na jeho základě jsme udělali rozhodnutí, které nakonec zakořenilo, ustáli jsme se na něm. Jeho podrobnosti jsou tématem samostatného článku a možná více než jednoho, protože chci mluvit o mnohém: od synchronizace schémat databáze po řízení přístupu a depersonalizaci osobních údajů. Je třeba také poznamenat, že toto řešení není náhradou skutečných analytických databází a úložišť, pouze řeší konkrétní problém.

Na nejvyšší úrovni to vypadá takto:

K dispozici je PostgreSQL databáze, kam si uživatelé mohou ukládat svá pracovní data a hlavně jsou k ní prostřednictvím FDW připojeny analytické repliky všech služeb. To umožňuje napsat dotaz do několika databází a nezáleží na tom, co to je: PostgreSQL, MySQL, MongoDB nebo něco jiného (soubor, API, pokud najednou není vhodný obal, můžete napsat vlastní). No, všechno se zdá být skvělé! Rozchod?

Pokud by vše skončilo tak rychle a jednoduše, pak by pravděpodobně článek neexistoval.

Je důležité mít jasno v tom, jak postgres zpracovává požadavky na vzdálené servery. Zdá se to logické, ale lidé tomu často nevěnují pozornost: postgres rozděluje dotaz na části, které jsou prováděny nezávisle na vzdálených serverech, shromažďuje tato data a sám provádí konečné výpočty, takže rychlost provádění dotazu bude značně záviset na tom, jak Je to napsané. Je třeba také poznamenat: když data pocházejí ze vzdáleného serveru, již nemají indexy, plánovači nepomůže nic, proto jen my sami můžeme pomoci a navrhnout to. A o tom chci mluvit podrobněji.

Jednoduchá žádost a s ní plán

Abychom ukázali, jak Postgres dotazuje tabulku s 6 miliony řádků na vzdáleném serveru, podívejme se 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

Pomocí příkazu VERBOSE můžete vidět dotaz, který bude odeslán na vzdálený server a jehož výsledky obdržíme k dalšímu zpracování (RemoteSQL řetězec).

Pojďme trochu dále a přidejte k našemu dotazu několik filtrů: jeden po druhém boolean pole, jeden po vstupu timestamp 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

Zde je ten moment, na který je potřeba si dát při psaní dotazů pozor. Filtry nebyly přeneseny na vzdálený server, což znamená, že k jejich provedení postgres stáhne všech 6 milionů řádků, aby se filtroval lokálně (řádek Filtr) a provedl agregaci později. Klíčem k úspěchu je napsat dotaz tak, aby se filtry přenesly na vzdálený stroj a my obdrželi a agregovali pouze potřebné řádky.

To je nějaká hloupost

S booleovskými poli je vše jednoduché. V původním dotazu byl problém na straně operátora is. Pokud jej nahradíte =, pak dostaneme následující výsledek:

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

Jak vidíte, filtr přeletěl na vzdálený server a doba provádění se zkrátila z 27 na 19 sekund.

Nutno podotknout, že provozovatel is odlišný od operátora = ten, který umí pracovat s hodnotou Null. Znamená to, že není pravda ve filtru ponechá hodnoty False a Null, while != Pravda ponechá pouze False hodnoty. Proto při výměně operátora není měli byste předat filtru dvě podmínky s operátorem OR, například WHERE (sloupec != True) OR (sloupec je null).

S vyřešeným booleanem pokračujeme. Mezitím vraťme filtr podle booleovské hodnoty do jeho původní podoby, abychom nezávisle zvážili vliv dalších změn.

časové razítko? Hz

Obecně platí, že často musíte experimentovat s tím, jak správně napsat dotaz, který zahrnuje vzdálené servery, a teprve potom hledat vysvětlení, proč se to děje. Na internetu se o tom dá najít velmi málo informací. V experimentech jsme tedy zjistili, že filtr s pevným datem letí na vzdálený server s třeskem, ale když chceme nastavit datum dynamicky, například now() nebo CURRENT_DATE, to se nestane. V našem příkladu jsme přidali filtr, takže sloupec created_at obsahuje data přesně za 1 měsíc v minulosti (BETWEEN CURRENT_DATE - INTERVAL '7 měsíců' AND CURRENT_DATE - INTERVAL '6 měsíců'). Co jsme v tomto případě udělali?

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

Vyzvali jsme plánovače, aby v poddotazu předem vypočítal datum a předal filtru již připravenou proměnnou. A tato nápověda nám poskytla skvělý výsledek, dotaz byl téměř 6krát rychlejší!

Zde je opět důležité být opatrný: datový typ v poddotazu musí být shodný s datovým typem pole, podle kterého filtrujeme, jinak plánovač rozhodne, že jelikož jsou typy různé a je nutné nejprve získat všechny data a lokálně je filtrovat.

Vraťme filtr podle data na původní hodnotu.

Freddy vs. jsonb

Obecně platí, že booleovská pole a data již dostatečně zrychlila náš dotaz, ale byl tu ještě jeden datový typ. Boj s filtrováním pomocí něj, upřímně řečeno, stále není u konce, i když i zde jsou úspěchy. Zde je návod, jak se nám podařilo projít filtrem jsonb pole na vzdálený 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

Místo filtrování operátorů musíte použít přítomnost jednoho operátoru. jsonb v jiném. 7 sekund místo původních 29. To je zatím jediná úspěšná možnost přenosu filtrů přes jsonb na vzdálený server, ale zde je důležité vzít v úvahu jedno omezení: používáme verzi databáze 9.6, ale do konce dubna plánujeme dokončit poslední testy a přejít na verzi 12. Jakmile aktualizujeme, napíšeme, jak se to dotklo, protože změn, na které se hodně doufáme, je spousta: json_path, nové chování CTE, push down (existující od verze 10). Opravdu to chci brzy vyzkoušet.

Dodělej ho

Zkontrolovali jsme, jak každá změna ovlivňuje rychlost dotazování jednotlivě. Pojďme se nyní podívat, co se stane, když jsou všechny tři filtry napsány správně.

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

Ano, dotaz vypadá složitější, jedná se o vynucenou cenu, ale rychlost provedení je 2 sekundy, což je více než 10x rychlejší! A to mluvíme o jednoduchém dotazu na relativně malý soubor dat. Na reálné požadavky jsme zaznamenali až několikasetnásobné navýšení.

Abych to shrnul: pokud používáte PostgreSQL s FDW, vždy zkontrolujte, zda jsou všechny filtry odeslány na vzdálený server a budete spokojeni... Alespoň dokud se nedostanete ke spojením mezi tabulkami z různých serverů. Ale to už je příběh na jiný článek.

Děkuji za pozornost! Rád bych v komentářích slyšel otázky, komentáře a příběhy o vašich zkušenostech.

Zdroj: www.habr.com