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

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

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

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

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

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

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

Прост запит и план с него

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

explain analyze verbose  
SELECT count(1)
FROM fdw_schema.table;

Агрегат (cost=418383.23..418383.24 rows=1 width=8) (actual time=3857.198..3857.198 rows=1 loops=1)
  Изход: count(1)
  ->  Външно сканиране на fdw_schema."table"  (cost=100.00..402376.14 rows=6402838 width=0) (actual time=4.874..3256.511 rows=6406868 loops=1)
        Изход: "table".id, "table".is_active, "table".meta, "table".created_dt
        Отдалечен SQL: SELECT NULL FROM fdw_schema.table
Време за планиране: 0.986 ms
Време за изпълнение: 3857.436 ms

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

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

Агрегат (cost=577487.69..577487.70 rows=1 width=8) (actual time=27473.818..25473.819 rows=1 loops=1)
  Изход: count(1)
  ->  Външно сканиране на fdw_schema."table"  (cost=100.00..577469.21 rows=7390 width=0) (actual time=31.369..25372.466 rows=1360025 loops=1)
        Изход: "table".id, "table".is_active, "table".meta, "table".created_dt
        Филтър: (("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)))
        Редове отстранени от филтъра: 5046843
        Отдалечен SQL: SELECT created_dt, is_active, meta FROM fdw_schema.table
Време за планиране: 0.665 ms
Време за изпълнение: 27474.118 ms

Тук именно се крие моментът, на който трябва да обърнем внимание при написването на запроси. Филтрите не бяха предадени на отдалечения сървър, което означава, че PostgreSQL извлича всичките 6 милиона реда, за да може след това локално да ги филтрира (редица Filter) и да извърши агрегация. Успехът зависи от това да напишем запит така, че филтрите да се предават на отдалечената машина, а ние да получаваме и агрегитираме само необходимите редове.

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

С boolean полета — всичко е просто. Проблемът в оригиналния запит възникна поради оператора и. Ако го заменим с =, тогава ще получим следния резултат:

объясните анализ в подробностях
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';

Агрегат (стоимость=508010.14..508010.15 строки=1 ширина=8) (фактическое время=19064.314..19064.314 строки=1 циклы=1)
  Вывод: count(1)
  ->  Иностранное сканирование на fdw_schema."table" (стоимость=100.00..507988.44 строки=8679 ширина=0) (фактическое время=33.035..18951.278 строки=1360025 циклы=1)
        Вывод: "table".id, "table".is_active, "table".meta, "table".created_dt
        Фильтр: ((("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)))
        Удаленные строки фильтром: 3567989
        Удаленный SQL: SELECT created_dt, meta FROM fdw_schema.table WHERE (is_active)
Время планирования: 0.834 мс
Время выполнения: 19064.534 мс

Как видите, фильтр был отправлен на удалённый сервер, а время выполнения сократилось с 27 до 19 секунд.

Стоит отметить, что оператор и отличается от оператора = тем, что умеет работать со значением Null. Это означает, что is not True в фильтре оставит значения False и Null, тогда как != True оставит только значения False. Поэтому при замене оператора is not следует передавать в фильтр два условия с оператором OR, к примеру, WHERE (col != True) OR (col is null).

С boolean разобрались, двигаемся дальше. А пока вернем фильтр по булевому значению в изначальный вид, чтобы независимо рассмотреть эффект от других изменений.

timestamptz? hz

Вообще, часто приходится экспериментировать с тем, как правильно написать запрос, в котором участвуют удаленные серверы, а уже потом искать объяснение, почему происходит именно так. Очень мало информации по этому поводу можно найти в Интернете. Так, в экспериментах мы обнаружили, что фильтр по фиксированной дате улетает на удаленный сервер на ура, а вот когда мы хотим задать дату динамически, например, now() или CURRENT_DATE, такого не происходит. В нашем примере, мы добавили такой фильтр, чтобы столбец created_at содержал в себе данные ровно за 1 месяц в прошлом (BETWEEN CURRENT_DATE - INTERVAL ‘7 month’ AND CURRENT_DATE - INTERVAL ‘6 month’). Что же мы предприняли в данном случае?

объяснить анализ подробный
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 >'source' = 'test';

Агрегат (cost=306875.17..306875.18 rows=1 width=8) (фактическое время=4789.114..4789.115 rows=1 loops=1)
  Вывод: count(1)
  InitPlan 1 (возвращает $0)
    ->  Результат (cost=0.00..0.02 rows=1 width=8) (фактическое время=0.007..0.008 rows=1 loops=1)
          Вывод: ((('now'::cstring)::date)::timestamp with time zone - '7 mons'::interval)
  InitPlan 2 (возвращает $1)
    ->  Результат (cost=0.00..0.02 rows=1 width=8) (фактическое время=0.002..0.002 rows=1 loops=1)
          Вывод: ((('now'::cstring)::date)::timestamp with time zone - '6 mons'::interval)
  ->  Иностранное сканирование на fdw_schema."table"  (cost=100.02..306874.86 rows=105 width=0) (фактическое время=23.475..4681.419 rows=1360025 loops=1)
        Вывод: "table".id, "table".is_active, "table".meta, "table".created_dt
        Фильтр: (("table".is_active IS TRUE) И (("table".meta ->> 'source'::text) = 'test'::text))
        Удаленные строки фильтром: 76934
        Удаленный SQL: SELECT is_active, meta FROM fdw_schema.table WHERE ((created_dt >= $1::timestamp with time zone)) И ((created_dt < $2::timestamp with time zone))
Время планирования: 0.703 ms
Время выполнения: 4789.379 ms

Нам удалось подсказать планировщику заранее вычислить дату в подзапросе и передать уже готовую переменную в фильтр. Эта подсказка дала нам отличный результат, запрос стал быстрее почти в 6 раз!

Важно быть внимательным: тип данных в подзапросе должен соответствовать полю, по которому мы фильтруем, иначе планировщик решит, что типы разные, и будет сначала получать все данные, а затем фильтровать их локально.

Вернем фильтр по дате к исходному значению.

Freddy против Jsonb

В общем, булевы поля и даты уже значительно ускорили наш запрос, однако оставался еще один тип данных. Битва с фильтрацией по нему, честно говоря, до сих пор не завершена, хотя мы здесь добились успеха. Итак, вот как удалось передать фильтр по jsonb полю на удаленный сервер.

объяснить анализ подробный
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;

Агрегат (cost=245463.60..245463.61 rows=1 width=8) (фактическое время=6727.589..6727.590 rows=1 loops=1)
  Вывод: count(1)
  ->  Иностранное сканирование на fdw_schema."table"  (cost=1100.00..245459.90 rows=1478 width=0) (фактическое время=16.213..6634.794 rows=1360025 loops=1)
        Вывод: "table".id, "table".is_active, "table".meta, "table".created_dt
        Фильтр: (("table".is_active IS TRUE) И ("table".created_dt >= (('now'::cstring)::date - '7 mons'::interval)) И ("table".created_dt  '{"source": "test"}'::jsonb))
Время планирования: 0.747 ms
Время выполнения: 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  '{"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 ms

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

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

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

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