Architektura mikroserwisowa, podobnie jak wszystko w tym świecie, ma swoje zalety i wady. Niektóre procesy stają się prostsze, inne — bardziej skomplikowane. W imię szybkości zmian i lepszej skalowalności trzeba ponosić pewne ofiary. Jedną z nich jest skomplikowanie analityki. Jeśli w monolicie całą operacyjną analitykę można sprowadzić do zapytań SQL do repliki analitycznej, to w architekturze wieloserwisowej każda usługa ma swoją bazę i wydaje się, że jednym zapytaniem się nie obejdzie (a może się obejdzie?). Dla tych, którzy są ciekawi, jak rozwiązaliśmy problem operacyjnej analityki w naszej firmie i jak nauczyliśmy się żyć z tym rozwiązaniem — zapraszamy.
Nazywam się Paweł Siwaś, w DomKlick pracuję w zespole, który odpowiada za utrzymanie analitycznego magazynu danych. Naszą działalność można w dużej mierze klasyfikować jako inżynierię danych, ale tak naprawdę zakres zadań jest znacznie szerszy. Obejmuje standardowe dla inżynierii danych ETL/ELT, wsparcie i adaptację narzędzi analitycznych oraz rozwój własnych narzędzi. W szczególności, dla raportowania operacyjnego postanowiliśmy «udawać», że mamy monolit i dać analitykom jedną bazę, w której będą zawarte wszystkie niezbędne im dane.
Generalnie rozważaliśmy różne opcje. Można było stworzyć pełnoprawne magazyn — nawet próbowaliśmy, ale szczerze mówiąc, nie udało nam się zsynchronizować dość częstych zmian w logice z dość wolnym procesem budowania magazynu i wprowadzania w nim zmian (jeśli komuś się udało, proszę napisać w komentarzach jak). Można było powiedzieć analitykom: „Chłopaki, uczcie się Pythona i przechodźcie do replik analitycznych”, ale to dodatkowy warunek przy rekrutacji, którego chcieliśmy uniknąć, jeśli to możliwe. Postanowiliśmy spróbować zastosować technologię FDW (Foreign Data Wrapper): w zasadzie, to standardowy dblink, który znajduje się w standardzie SQL, ale z znacznie bardziej przyjaznym interfejsem. Na jego podstawie stworzyliśmy rozwiązanie, które ostatecznie się sprawdziło i na nim się zatrzymaliśmy. Szczegóły są tematem osobnego artykułu, a może nawet nie jednego, ponieważ chciałoby się opowiedzieć o wielu rzeczach: od synchronizacji schematów baz do zarządzania dostępem i anonimizacji danych osobowych. Należy również zaznaczyć, że to rozwiązanie nie jest zamiennikiem realnych baz analitycznych i magazynów, a jedynie rozwiązuje konkretne zadanie.
Na wysokim poziomie wygląda to tak:
Jest baza PostgreSQL, gdzie użytkownicy mogą przechowywać swoje dane robocze, a co najważniejsze — do tej bazy przez FDW podłączone są analityczne repliki wszystkich usług. To pozwala napisać zapytanie do kilku baz, niezależnie od tego, czy są to: PostgreSQL, MySQL, MongoDB, czy coś innego (plik, API, a jeśli nie ma odpowiedniego wrappera, można napisać swój). No, to wszystko, super! Rozchodzimy się?
Gdyby wszystko kończyło się tak szybko i prosto, to pewnie nie byłoby artykułu.
Waży uwzględnić, jak PostgreSQL przetwarza zapytania do zdalnych serwerów. Wydaje się to logiczne, jednak często nie zwraca się na to uwagi: PostgreSQL dzieli zapytanie na części, które są wykonywane na zdalnych serwerach niezależnie, zbiera te dane, a finalne obliczenia prowadzi sam, dlatego szybkość wykonania zapytania będzie w dużej mierze zależna od tego, jak jest napisane. Należy także zauważyć, że gdy dane przychodzą z zdalnego serwera, nie mają już indeksów, nie ma nic, co pomogłoby planistowi, więc tylko my możemy mu pomóc i podpowiedzieć. I o tym właśnie chciałbym opowiedzieć szczegółowo.
Prosty zapytanie i plan z nim
Aby pokazać, jak Postgres wykonuje zapytanie do tabeli zawierającej 6 milionów wierszy na zdalnym serwerze, serwerze, przyjrzyjmy się prostemu planowi.
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 msUżycie instrukcji VERBOSE pozwala zobaczyć zapytanie, które zostanie wysłane do zdalnego serwera oraz wyniki, które otrzymamy do dalszego przetwarzania (linia RemoteSQL).
Zróbmy krok dalej i dodajmy do naszego zapytania kilka filtrów: jeden według boolean pola, jeden według wystąpienia timestamp w interwale i jeden według 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 msTo właśnie tutaj kryje się moment, na który należy zwrócić uwagę przy pisaniu zapytań. Filtry nie zostały przekazane na zdalny serwer, co oznacza, że Postgres pobiera wszystkie 6 milionów wierszy, aby później lokalnie odfiltrować (linia Filter) i wykonać agregację. Kluczem do sukcesu jest napisanie zapytania w taki sposób, aby filtry były przekazywane do zdalnej maszyny, a my otrzymywaliśmy i agregowali tylko istotne wiersze.
To już przesada
Z polami boolean jest prosto. W pierwotnym zapytaniu problem pojawił się z powodu operatora is. Jeśli zamienimy go na =, otrzymamy następujący wynik:
wyjaśnij analizuj szczegółowo
SELECT count(1)
FROM fdw_schema.table
WHERE is_active = True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 miesiąc'
AND CURRENT_DATE - INTERVAL '6 miesięcy'
AND meta->>'source' = 'test';
Agregat (koszt=508010.14..508010.15 wiersze=1 szerokość=8) (rzeczywisty czas=19064.314..19064.314 wiersze=1 pętle=1)
Wyjście: count(1)
- → Zdalne skanowanie na fdw_schema."table" (koszt=100.00..507988.44 wiersze=8679 szerokość=0) (rzeczywisty czas=33.035..18951.278 wiersze=1360025 pętle=1)
Wyjście: "table".id, "table".is_active, "table".meta, "table".created_dt
Filtr: ((("table".meta ->> 'source'::text) = 'test'::text) AND ("table".created_dt >= (('teraz'::cstring)::date - '7 miesięcy'::interval)) AND ("table".created_dt <= ((('teraz'::cstring)::date)::timestamp with time zone - '6 miesięcy'::interval)))
Wiersze usunięte przez filtr: 3567989
Zdalne SQL: SELECT created_dt, meta FROM fdw_schema.table WHERE (is_active)
Czas planowania: 0.834 ms
Czas wykonania: 19064.534 msJak widzicie, filtr przeszedł na zdalny serwer, a czas wykonania skrócił się z 27 do 19 sekund.
Warto zauważyć, że operator is różni się od operatora = tym, że potrafi pracować z wartością Null. Oznacza to, że is not True w filtrze pozostawi wartości False i Null, podczas gdy != True zostawi tylko wartości False. Dlatego przy zamianie operatora is not należy przekazać do filtru dwa warunki z operatorem OR, na przykład, WHERE (col != True) OR (col is null).
Ze zmienną boolean się uporaliśmy, przechodzimy dalej. A tymczasem przywróćmy filtr po wartości logicznej do pierwotnej formy, aby niezależnie rozważyć efekt innych zmian.
timestamptz? hz
W rzeczywistości często trzeba eksperymentować z tym, jak poprawnie napisać zapytanie, w którym uczestniczą zdalne serwery, a następnie szukać wyjaśnień, dlaczego dzieje się tak, a nie inaczej. Bardzo mało informacji na ten temat można znaleźć w Internecie. Tak, w naszych eksperymentach odkryliśmy, że filtr na stałą datę przechodzi na zdalny serwer bez problemu, ale kiedy chcemy ustawić datę dynamicznie, na przykład, now() lub CURRENT_DATE, to już tak się nie dzieje. W naszym przykładzie dodaliśmy taki filtr, aby kolumna created_at zawierała dane dokładnie sprzed 1 miesiąca (BETWEEN CURRENT_DATE - INTERVAL ‘7 miesięcy’ AND CURRENT_DATE - INTERVAL ‘6 miesięcy’). Co więc w takim razie postanowiliśmy zrobić?
wyjaśnij analizę szczegółową
SELECT count(1)
FROM fdw_schema.table
WHERE is_active is True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 miesięcy')
AND created_dt >'source' = 'test';
Aggregate (koszt=306875.17..306875.18 wierszy=1 szerokość=8) (czas rzeczywisty=4789.114..4789.115 wierszy=1 pętli=1)
Wyjście: count(1)
InitPlan 1 (zwraca $0)
-> Wynik (koszt=0.00..0.02 wierszy=1 szerokość=8) (czas rzeczywisty=0.007..0.008 wierszy=1 pętli=1)
Wyjście: ((('teraz'::cstring)::date)::timestamp with time zone - '7 msc'::interval)
InitPlan 2 (zwraca $1)
-> Wynik (koszt=0.00..0.02 wierszy=1 szerokość=8) (czas rzeczywisty=0.002..0.002 wierszy=1 pętli=1)
Wyjście: ((('teraz'::cstring)::date)::timestamp with time zone - '6 msc'::interval)
-> Obco_Skanować na fdw_schema."table" (koszt=100.02..306874.86 wierszy=105 szerokość=0) (czas rzeczywisty=23.475..4681.419 wierszy=1360025 pętli=1)
Wyjście: "table".id, "table".is_active, "table".meta, "table".created_dt
Filtr: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text))
Usunięte wiersze przez filtr: 76934
Zdalne 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))
Czas planowania: 0.703 ms
Czas wykonania: 4789.379 msSugerowaliśmy planowaniu wstępnie obliczyć datę w podzapytaniu i przekazać gotową zmienną do filtru. Ta sugestia przyniosła nam wspaniały rezultat, zapytanie stało się szybsze prawie 6 razy!
Ponownie, ważne jest, aby być ostrożnym: typ danych w podzapytaniu musi być taki sam, co pole, według którego filtrujemy, w przeciwnym razie planista zdecyduje, że typy są różne i konieczne jest najpierw pobranie wszystkich danych, a następnie lokalne filtrowanie.
Przywróćmy filtr daty do pierwotnej wartości.
Freddy vs. Jsonb
W sumie, pola logiczne i daty już wystarczająco przyspieszyły nasze zapytanie, jednak pozostawał jeszcze jeden typ danych. Walka z filtrowaniem według niego, szczerze mówiąc, nadal trwa, chociaż tutaj są już pewne sukcesy. Tak więc, oto jak udało nam się przekazać filtr według jsonb pola na zdalny serwer.
wyjaśnij analizę szczegółową
SELECT count(1)
FROM fdw_schema.table
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 miesięcy'
AND CURRENT_DATE - INTERVAL '6 miesięcy'
AND meta @> '{"source":"test"}'::jsonb;
Aggregate (koszt=245463.60..245463.61 wierszy=1 szerokość=8) (czas rzeczywisty=6727.589..6727.590 wierszy=1 pętli=1)
Wyjście: count(1)
-> Obco_Skanować na fdw_schema."table" (koszt=1100.00..245459.90 wierszy=1478 szerokość=0) (czas rzeczywisty=16.213..6634.794 wierszy=1360025 pętli=1)
Wyjście: "table".id, "table".is_active, "table".meta, "table".created_dt
Filtr: (("table".is_active IS TRUE) AND ("table".created_dt >= (('teraz'::cstring)::date - '7 msc'::interval)) AND ("table".created_dt '{"source": "test"}'::jsonb))
Czas planowania: 0.747 ms
Czas wykonania: 6727.815 msZamiast operatorów filtrowania należy używać operatora is present jsonb w innym. 7 sekund zamiast pierwotnych 29. To jak na razie jedyny udany sposób przesyłania filtrów przez jsonb na zdalny serwer, ale ważne jest, aby uwzględnić jedno ograniczenie: używamy wersji bazy 9.6, jednak do końca kwietnia planujemy zakończyć ostatnie testy i przejść na wersję 12. Gdy się zaktualizujemy, napiszemy, jak to wpłynęło, ponieważ zmian, na które wielu liczy, jest całkiem sporo: json_path, nowe zachowanie CTE, push down (istniejące od wersji 10). Naprawdę chcemy to jak najszybciej przetestować.
Zakończ go
Sprawdziliśmy, jak każda zmiana wpływa na szybkość zapytania indywidualnie. Teraz przyjrzyjmy się, co się stanie, gdy wszystkie trzy filtry będą napisane poprawnie.
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 '{"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 '{"source": "test"}'::jsonb))
Planning time: 0.820 ms
Execution time: 2279.087 msTak, zapytanie wygląda na bardziej skomplikowane, to wymuszona cena, ale czas wykonania wynosi 2 sekundy, co jest ponad 10 razy szybciej! A mówimy o prostym zapytaniu do relatywnie niewielkiego zbioru danych. W przypadku rzeczywistych zapytań uzyskiwaliśmy przyrosty rzędu setek razy.
Podsumowując: jeśli używasz PostgreSQL z FDW, zawsze sprawdzaj, czy wszystkie filtry są przesyłane na zdalny serwer, a będziesz szczęśliwy... Przynajmniej dopóki nie dojdziesz do joinów między tabelami z różnych serwerów. Ale to już historia na kolejny artykuł.
Dziękuję za uwagę! Będę wdzięczny za pytania, komentarze oraz historie o twoim doświadczeniu w komentarzach.
Źródło: habr.com
