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

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

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

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

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

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

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

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

За да покажем как 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).

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

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) и да направи агрегация. Ключът към успеха е да напишем заявката така, че филтрите да се предават на отдалеченото устройство, а ние да получаваме и агрегатираме само нужните редове.

That’s some booleanshit

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

обясни анализ подробно
ИЗБЕРИ count(1)
ИЗ fdw_schema.table
ГДЕ is_active = True
И created_dt МЕЖДУ ТЕКУЩАЯ_ДАТА - ИНТЕРВАЛ '7 мес'
И ТЕКУЩАЯ_ДАТА - ИНТЕРВАЛ '6 мес'
И 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) И ("table".created_dt >= (('now'::cstring)::date - '7 мес'::интервал)) И ("table".created_dt <= ((('now'::cstring)::date)::timestamp с временной зоной - '6 мес'::интервал)))
        Удаленные строки: 3567989
        Удаленный SQL: ИЗБЕРИ created_dt, meta ИЗ fdw_schema.table ГДЕ (is_active)
Время планирования: 0.834 мс
Время выполнения: 19064.534 мс

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

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

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

timestamptz? hz

Вообще, часто приходится экспериментировать с тем, как правильно написать запрос, в котором участвуют удаленные сервера, а потом искать объяснение, почему это происходит именно так. Очень мало информации по этому поводу можно найти в Интернете. В ходе экспериментов мы обнаружили, что фильтр по фиксированной дате проходит на удаленный сервер без проблем, но когда мы пытаемся задать дату динамически, например, now() или CURRENT_DATE, этого не происходит. В нашем примере мы добавили такой фильтр, чтобы столбец created_at содержал данные ровно за 1 месяц назад (МЕЖДУ ТЕКУЩАЯ_ДАТА — ИНТЕРВАЛ '7 мес' И ТЕКУЩАЯ_ДАТА — ИНТЕРВАЛ '6 мес'). Что же мы предприняли в данном случае?

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

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

Ние подсказахме на планировчика предварително да изчисли датата в подзапроса и да предаде готовата променлива в фильтъра. И тази подсказка ни даде прекрасен резултат, заявката стана почти 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;

Агрегат  (стоимость=245463.60..245463.61 строки=1 ширина=8) (фактическое время=6727.589..6727.590 строки=1 циклы=1)
  Вывод: count(1)
  ->  Иностранен скан на fdw_schema."table"  (стоимость=1100.00..245459.90 строки=1478 ширина=0) (фактическое време=16.213..6634.794 строки=1360025 цикли=1)
        Вывод: "table".id, "table".is_active, "table".meta, "table".created_dt
        Филтър: (("table".is_active IS TRUE) AND ("table".created_dt >= (('сега'::cstring)::date - '7 месеца'::интервал)) AND ("table".created_dt  '{"source": "test"}'::jsonb))
Време за планиране: 0.747 мс
Време за изпълнение: 6727.815 мс

Вместо операторите за филтриране трябва да се използва оператор на наличието на един 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 months') 
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 months'::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 months'::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 секунди, което е повече от 10 пъти по-бързо! И тук говорим за просто запитване към относително малък набор от данни. При реални запитвания получавахме увеличаване до няколко стотин пъти.

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

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

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