Architektura mikrousług, jak wszystko na tym świecie, ma swoje wady i zalety. Niektóre procesy stają się dzięki niemu łatwiejsze, inne trudniejsze. A w imię szybkości zmian i lepszej skalowalności trzeba się poświęcić. Jednym z nich jest rosnąca złożoność analityki. Jeśli w monolicie całą analitykę operacyjną da się sprowadzić do zapytań SQL do repliki analitycznej, to w architekturze wielousługowej każda usługa ma swoją bazę danych i wydaje się, że jednego zapytania nie da się wykonać (a może da się?). Wszystkich zainteresowanych tym, jak rozwiązaliśmy problem analityki operacyjnej w naszej firmie i jak nauczyliśmy się z tym rozwiązaniem żyć – zapraszamy.
Nazywam się Pavel Sivash, w DomClick pracuję w zespole odpowiedzialnym za utrzymanie hurtowni danych analitycznych. Umownie nasze działania można zaliczyć do inżynierii danych, jednak w rzeczywistości zakres zadań jest znacznie szerszy. Istnieją standardy ETL/ELT dotyczące inżynierii danych, wsparcia i adaptacji narzędzi do analizy danych oraz rozwoju własnych narzędzi. W szczególności w przypadku raportowania operacyjnego postanowiliśmy „udawać”, że mamy monolit i oddawać analitykom jedną bazę danych, która będzie zawierać wszystkie potrzebne im dane.
Ogólnie rozważaliśmy różne opcje. Udało się zbudować pełnoprawne repozytorium - nawet próbowaliśmy, ale szczerze mówiąc nie udało nam się połączyć dość częstych zmian w logice z dość powolnym procesem budowy repozytorium i wprowadzania w nim zmian (jeśli komuś się udało , napisz w komentarzu jak). Można było powiedzieć analitykom: „Chłopaki, nauczcie się Pythona i przejdźcie do replik analitycznych”, ale jest to dodatkowy wymóg przy rekrutacji i wydawało się, że w miarę możliwości należy tego unikać. Postanowiliśmy spróbować zastosować technologię FDW (Foreign Data Wrapper): w zasadzie jest to standardowy dblink, który jest w standardzie SQL, ale ma własny, znacznie wygodniejszy interfejs. Na tej podstawie zrobiliśmy rozwiązanie, które ostatecznie się przyjęło i na nim zdecydowaliśmy się. Jego szczegóły to temat na osobny artykuł, a może i więcej niż jeden, bo chcę omówić naprawdę wiele: od synchronizacji schematów baz danych, po kontrolę dostępu i depersonalizację danych osobowych. Należy także zastrzec, że rozwiązanie to nie zastępuje rzeczywistych baz analitycznych i repozytoriów, lecz rozwiązuje jedynie konkretny problem.
Na najwyższym poziomie wygląda to tak:
Istnieje baza danych PostgreSQL, w której użytkownicy mogą przechowywać swoje dane służbowe, a co najważniejsze, repliki analityczne wszystkich usług są podłączone do tej bazy poprzez FDW. Dzięki temu możliwe jest napisanie zapytania do kilku baz danych i nie ma znaczenia jakie to będzie: PostgreSQL, MySQL, MongoDB czy coś innego (plik, API, jeśli nagle nie będzie odpowiedniego wrappera, można napisać własne). Cóż, wszystko wydaje się świetne! Zrywamy?
Gdyby wszystko skończyło się tak szybko i prosto, prawdopodobnie nie byłoby artykułu.
Ważne jest, aby mieć jasność co do sposobu, w jaki Postgres przetwarza żądania kierowane do zdalnych serwerów. Wydaje się to logiczne, ale często ludzie nie zwracają na to uwagi: Postgres dzieli żądanie na części, które są wykonywane niezależnie na zdalnych serwerach, zbiera te dane i sam wykonuje końcowe obliczenia, więc szybkość wykonania zapytania będzie w dużym stopniu zależała od jak to jest napisane. Należy również zauważyć: kiedy dane docierają ze zdalnego serwera, nie ma on już indeksów, nic nie pomoże planiście, dlatego tylko my sami możemy mu pomóc i doradzić. I właśnie o tym chcę porozmawiać bardziej szczegółowo.
Proste zapytanie i plan z nim
Aby pokazać, jak Postgres wysyła zapytanie do tabeli zawierającej 6 milionów wierszy na zdalnym serwerze, spójrzmy na prosty 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 msUżycie instrukcji VERBOSE pozwala nam zobaczyć zapytanie, które zostanie wysłane do zdalnego serwera i którego wyniki otrzymamy do dalszego przetwarzania (linia RemoteSQL).
Pójdźmy trochę dalej i dodajmy do naszej prośby kilka filtrów: jeden dla boolean pole, jedno według wystąpienia znak czasu w przerwie i jeden po drugim 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 msWłaśnie na to należy zwrócić uwagę podczas pisania zapytań. Filtry nie zostały przesłane na zdalny serwer, co oznacza, że aby je wykonać, Postgres pobiera wszystkie 6 milionów wierszy, aby następnie przefiltrować lokalnie (wiersz filtra) i wykonać agregację. Kluczem do sukcesu jest napisanie zapytania, dzięki któremu filtry zostaną przeniesione na zdalną maszynę, a my otrzymamy i zagregujemy tylko potrzebne wiersze.
To jakieś bzdury
Z polami boolowskimi wszystko jest proste. W pierwotnym żądaniu przyczyną problemu był operator is. Jeśli zastąpisz go =, wówczas otrzymujemy następujący wynik:
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 msJak widać filtr poleciał na zdalny serwer, a czas wykonania został skrócony z 27 do 19 sekund.
Warto dodać, że operator is różni się od operatora = ponieważ może działać z wartością Null. To znaczy, że to nie jest prawda pozostawi w filtrze wartości False i Null, natomiast != Prawda pozostawi tylko wartości False. Dlatego przy wymianie operatora nie jest do filtra należy przekazać dwa warunki z operatorem OR, np. WHERE (col!= True) LUB (col ma wartość null).
Zajęliśmy się booleanem, przejdźmy dalej. Na razie przywróćmy filtr Boolean do jego pierwotnej postaci, aby niezależnie rozważyć wpływ innych zmian.
znacznik czasu? Hz
Ogólnie rzecz biorąc, często trzeba poeksperymentować, jak poprawnie napisać żądanie obejmujące zdalne serwery, a dopiero potem szukać wyjaśnienia, dlaczego tak się dzieje. W Internecie można znaleźć bardzo niewiele informacji na ten temat. Tak więc w eksperymentach odkryliśmy, że filtr ze stałą datą leci do zdalnego serwera z hukiem, ale gdy chcemy ustawić datę dynamicznie, na przykład now() lub CURRENT_DATE, tak się nie dzieje. W naszym przykładzie dodaliśmy filtr tak, aby kolumna create_at zawierała dane za dokładnie 1 miesiąc w przeszłości (BETWEEN CURRENT_DATE - INTERVAL „7 miesięcy” ORAZ CURRENT_DATE - INTERVAL „6 miesięcy”). Co zrobiliśmy w tym przypadku?
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 msPowiedzieliśmy planiście, aby wcześniej obliczył datę w podzapytaniu i przekazał gotową zmienną do filtra. I ta wskazówka dała nam doskonały wynik, żądanie stało się prawie 6 razy szybsze!
Ponownie trzeba tu zachować ostrożność: typ danych w podzapytaniu musi być taki sam jak w polu, po którym filtrujemy, w przeciwnym razie planista uzna, że skoro typy są różne, to trzeba najpierw zdobyć wszystkie dane i filtrować je lokalnie.
Przywróćmy oryginalną wartość filtra daty.
Freddy kontra Jsonba
Ogólnie rzecz biorąc, pola logiczne i daty już wystarczająco przyspieszyły nasze zapytanie, ale pozostał jeszcze jeden typ danych. Walka z filtrowaniem przez nią, prawdę mówiąc, jeszcze się nie skończyła, choć i tu jest sukces. W ten sposób udało nam się ominąć filtr jsonb pole do zdalnego serwera.
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 msZamiast filtrować operatory, musisz użyć obecności jednego operatora jsonb w innym. 7 sekund zamiast pierwotnych 29. Jak dotąd jest to jedyna udana opcja przesyłania filtrów przez jsonb na zdalny serwer, ale tutaj należy wziąć pod uwagę jedno ograniczenie: korzystamy z wersji bazy 9.6, ale do końca kwietnia planujemy zakończyć ostatnie testy i przejść na wersję 12. Jak już dokonamy aktualizacji, napiszemy jak to wpłynęło, bo zmian jest całkiem sporo, co do których pokładane są spore nadzieje: json_path, nowe zachowanie CTE, push down (istniejące od wersji 10). Naprawdę chcę wkrótce spróbować.
Wykończ go
Sprawdziliśmy, jak każda zmiana indywidualnie wpływa na szybkość żądania. Zobaczmy teraz, co się stanie, gdy wszystkie trzy filtry zostaną zapisane 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 <(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 msTak, żądanie wygląda na bardziej skomplikowane, jest to opłata wymuszona, ale szybkość realizacji wynosi 2 sekundy, czyli ponad 10 razy szybciej! Mówimy o prostym zapytaniu na stosunkowo małym zbiorze danych. Na realne prośby otrzymaliśmy podwyżkę nawet kilkusetkrotną.
Podsumowując: jeśli używasz PostgreSQL z FDW, zawsze sprawdzaj, czy wszystkie filtry są wysyłane do zdalnego serwera, a będziesz szczęśliwy... Przynajmniej do czasu, gdy uda Ci się połączyć tabele z różnych serwerów. Ale to historia na inny artykuł.
Dziękuję za uwagę! Chętnie wysłucham pytań, komentarzy i historii na temat Twoich doświadczeń w komentarzach.
Źródło: www.habr.com