Архитектурата на микросервизите, както всичко на този свят, има своите плюсове и минуси. Някои процеси стават по-лесни с него, други по-трудни. И в името на скоростта на промяната и по-добрата мащабируемост трябва да правите жертви. Една от тях е сложността на анализа. Ако в монолит всички оперативни анализи могат да бъдат намалени до SQL заявки към аналитична реплика, тогава в мултисервизна архитектура всяка услуга има своя собствена база данни и изглежда, че една заявка не е достатъчна (или може би ще стане?). За тези, които се интересуват как решихме проблема с оперативния анализ в нашата компания и как се научихме да живеем с това решение - добре дошли.

Казвам се Павел Сиваш, в DomClick работя в екип, който отговаря за поддържането на хранилището на аналитични данни. Условно нашите дейности могат да бъдат приписани на инженеринг на данни, но всъщност обхватът на задачите е много по-широк. Има стандартно инженерство на данни ETL / ELT, поддръжка и адаптиране на инструменти за анализ на данни и разработване на собствени инструменти. По-специално, за оперативното отчитане решихме да се „преструваме“, че имаме монолит и да дадем на анализаторите една база данни, която ще съдържа всички необходими данни.

Като цяло обмисляхме различни варианти. Беше възможно да се изгради пълноценно хранилище - дори се опитахме, но, честно казано, не успяхме да се сприятелим с доста чести промени в логиката с доста бавен процес на изграждане на хранилище и правене на промени в него ( ако някой е успял, напишете в коментарите как). Бихте могли да кажете на анализаторите: „Момчета, научете Python и отидете на аналитични линии“, но това е допълнително изискване за набиране на персонал и изглежда, че това трябва да се избягва, ако е възможно. Решихме да опитаме с технологията FDW (Foreign Data Wrapper): всъщност това е стандартен dblink, който е в стандарта SQL, но с много по-удобния интерфейс. Въз основа на него взехме решение, което в крайна сметка се затвърди, спряхме се на него. Неговите подробности са тема на отделна статия, а може би и повече от една, защото искам да говоря за много: от синхронизирането на схемата на базата данни до контрола на достъпа и обезличаването на лични данни. Трябва също да се отбележи, че това решение не е заместител на реални аналитични бази данни и хранилища, то решава само конкретен проблем.

На най-високо ниво изглежда така:

Има PostgreSQL база данни, където потребителите могат да съхраняват своите работни данни, и най-важното е, че аналитичните реплики на всички услуги са свързани към тази база данни чрез FDW. Това дава възможност да напишете заявка към няколко бази данни и няма значение какво е: PostgreSQL, MySQL, MongoDB или нещо друго (файл, API, ако изведнъж няма подходяща обвивка, можете да напишете своя собствена). Е, всичко изглежда страхотно! Късаме?

Ако всичко приключи толкова бързо и просто, тогава, вероятно, статията нямаше да съществува.

Важно е да е ясно как postgres обработва заявки към отдалечени сървъри. Това изглежда логично, но често хората не му обръщат внимание: postgres разделя заявката на части, които се изпълняват независимо на отдалечени сървъри, събира тези данни и сама извършва окончателните изчисления, така че скоростта на изпълнение на заявката ще зависи до голяма степен от това как написано е. Трябва също да се отбележи: когато данните идват от отдалечен сървър, те вече нямат индекси, няма нищо, което да помогне на планировчика, следователно само ние самите можем да помогнем и да го предложим. И това е, за което искам да говоря по-подробно.

Проста заявка и план с нея

За да покажем как Postgres прави запитвания към таблица с 6 милиона реда на отдалечен сървър, нека разгледаме един прост план.

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

Използването на оператора VERBOSE ви позволява да видите заявката, която ще бъде изпратена до отдалечения сървър и резултатите от която ще получим за по-нататъшна обработка (RemoteSQL низ).

Нека отидем малко по-далеч и да добавим няколко филтъра към нашата заявка: един по булева поле, едно по вх клеймото на интервал и един по 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

Тук се крие моментът, на който трябва да обърнете внимание при писане на заявки. Филтрите не са били прехвърлени към отдалечения сървър, което означава, че за да го изпълни, postgres изтегля всичките 6 милиона реда, за да филтрира локално (реда Filter) и да извърши агрегиране по-късно. Ключът към успеха е да напишете заявка, така че филтрите да се предават на отдалечената машина, а ние да получаваме и обобщаваме само необходимите редове.

Това са някакви булеви глупости

С булевите полета всичко е просто. В първоначалното запитване проблемът се дължи на оператора is. Ако го заменим с =, тогава получаваме следния резултат:

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

Както можете да видите, филтърът прелетя към отдалечения сървър и времето за изпълнение беше намалено от 27 на 19 секунди.

Трябва да се отбележи, че операторът is различен от оператора = този, който може да работи с нулевата стойност. Означава, че не е вярно във филтъра ще остави стойностите False и Null, докато != Вярно ще остави само False стойности. Следователно при смяна на оператора не трябва да подадете две условия към филтъра с оператора OR, например, WHERE (кола != True) ИЛИ (кола е нула).

С разбрано булево, продължаваме напред. Междувременно нека върнем филтъра по булева стойност в оригиналната му форма, за да разгледаме независимо ефекта от други промени.

timestamptz? хц

По принцип често трябва да експериментирате как правилно да напишете заявка, която включва отдалечени сървъри, и едва след това да търсите обяснение защо това се случва. Много малко информация за това може да се намери в интернет. И така, в експерименти установихме, че филтър с фиксирана дата лети до отдалечен сървър с гръм и трясък, но когато искаме да зададем датата динамично, например now() или CURRENT_DATE, това не се случва. В нашия пример сме добавили филтър, така че колоната created_at да съдържа данни за точно 1 месец в миналото (МЕЖДУ CURRENT_DATE - INTERVAL '7 месеца' И CURRENT_DATE - INTERVAL '6 месеца'). Какво направихме в този случай?

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

Подканихме плановия да изчисли датата предварително в подзаявката и да предаде вече подготвената променлива към филтъра. И тази подсказка ни даде страхотен резултат, заявката стана почти 6 пъти по-бърза!

Отново е важно да внимавате тук: типът данни в подзаявката трябва да е същият като този на полето, по което филтрираме, в противен случай плановият ще реши, че тъй като типовете са различни и е необходимо първо да получите всички данни и ги филтрирайте локално.

Нека върнем филтъра по дата към първоначалната му стойност.

Фреди срещу jsonb

Като цяло булевите полета и дати вече достатъчно ускориха нашата заявка, но имаше още един тип данни. Битката с филтрирането по него, честно казано, все още не е приключила, въпреки че и тук има успехи. И така, ето как успяхме да преминем филтъра 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"}'::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

Вместо да филтрирате оператори, трябва да използвате присъствието на един оператор. jsonb в различен. 7 секунди вместо първоначалните 29. Засега това е единственият успешен вариант за прехвърляне на филтри jsonb към отдалечен сървър, но тук е важно да вземем предвид едно ограничение: използваме версия 9.6 на базата данни, но до края на април планираме да завършим последните тестове и да преминем към версия 12. Веднага след като актуализираме, ще напишем как се е отразило, защото има много промени, за които има много надежди: json_path, ново CTE поведение, push down (съществуващи от версия 10). Много искам да го пробвам скоро.

Довърши го

Проверихме как всяка промяна влияе върху скоростта на заявката поотделно. Нека сега да видим какво се случва, когато и трите филтъра са написани правилно.

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

Да, заявката изглежда по-сложна, това е форсирана цена, но скоростта на изпълнение е 2 секунди, което е повече от 10 пъти по-бързо! И ние говорим за проста заявка за сравнително малък набор от данни. При реални заявки получихме увеличение до няколкостотин пъти.

За да обобщим: ако използвате PostgreSQL с FDW, винаги проверявайте дали всички филтри са изпратени към отдалечения сървър и ще сте доволни... Поне докато не стигнете до обединения между таблици от различни сървъри. Но това е история за друга статия.

Благодаря за вниманието! Ще се радвам да чуя въпроси, коментари и истории за вашите преживявания в коментарите.

Източник: www.habr.com