Микросервисная архитектура, как и все в этом мире, имеет свои плюсы и минусы.
С ним одни процессы становятся проще, другие сложнее.
И ради скорости изменений и лучшей масштабируемости нужно идти на жертвы.
Одним из них является возрастающая сложность аналитики.
Если в монолите всю оперативную аналитику можно свести к SQL-запросам к аналитической реплике, то в мультисервисной архитектуре каждый сервис имеет свою базу данных и кажется, что один запрос сделать невозможно (а может, и можно?).
Для тех, кому интересно, как мы решили проблему оперативной аналитики в нашей компании и как мы научились с этим решением жить — добро пожаловать.
Меня зовут Павел Сиваш, в ДомКлик я работаю в команде, которая отвечает за ведение аналитического хранилища данных.
Условно нашу деятельность можно отнести к Data Engineering, но на самом деле круг задач гораздо шире.
Есть стандарт ETL/ELT для инжиниринга данных, поддержки и адаптации инструментов анализа данных и разработки собственных инструментов.
В частности, для оперативной отчетности мы решили «сделать вид», что у нас монолит, и предоставить аналитикам одну базу данных, которая будет содержать все необходимые им данные.
В общем, мы рассматривали разные варианты.
Собрать полноценный репозиторий удалось - мы даже пытались, но, честно говоря, нам не удалось совместить достаточно частые изменения логики с достаточно медленным процессом сборки репозитория и внесения в него изменений (если у кого-то получилось , напишите в комментариях как).
Можно было сказать аналитикам: «Ребята, учите питон и идите на аналитические реплики», но это дополнительное требование при подборе персонала, и казалось, что этого следует по возможности избегать.
Мы решили попробовать использовать технологию FDW (Foreign Data Wrapper): по сути, это стандартный dblink, который есть в стандарте SQL, но со своим гораздо более удобным интерфейсом.
На основе этого мы приняли решение, которое в итоге прижилось, и мы на нем остановились.
Подробности ее — тема отдельной статьи, а может и не одной, поскольку рассказать хочется о многом: от синхронизации схем баз данных до контроля доступа и обезличивания персональных данных.
Также необходимо оговориться, что данное решение не является заменой реальных аналитических баз данных и хранилищ; он решает только конкретную проблему.
На верхнем уровне это выглядит так: 
Есть база данных PostgreSQL, где пользователи могут хранить свои рабочие данные, и самое главное, к этой базе через FDW подключены аналитические реплики всех сервисов.
Это дает возможность написать запрос к нескольким базам данных, причем не важно, что это будет: PostgreSQL, MySQL, MongoDB или что-то еще (файловое, API, если вдруг нет подходящей обертки, можно написать свою).
Ну вроде все отлично! Мы расстаемся? Если бы все закончилось так быстро и просто, то, наверное, и статьи бы не было.
Важно четко понимать, как Postgres обрабатывает запросы к удаленным серверам.
Это кажется логичным, но зачастую на это не обращают внимания: Postgres делит запрос на части, которые выполняются независимо на удаленных серверах, собирает эти данные и сам производит окончательные вычисления, поэтому скорость выполнения запроса будет во многом зависеть от как это написано.
Также следует отметить: когда данные поступают с удаленного сервера, он уже не имеет индексов, нет ничего, что могло бы помочь планировщику, поэтому только мы сами можем ему помочь и посоветовать.
И именно об этом я хочу поговорить подробнее.
Простой запрос и план с ним Чтобы показать, как Postgres запрашивает таблицу из 6 миллионов строк на удаленном сервере, давайте рассмотрим простой план.
Использование оператора VERBOSE позволяет нам увидеть запрос, который будет отправлен на удаленный сервер и результаты которого мы получим для дальнейшей обработки (строка RemoteSQL).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
Пойдем немного дальше и добавим к нашему запросу несколько фильтров: один для логическое значение поле, одно по вхождению временная метка в интервале и по одному 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
Вот в этом и заключается тот момент, на который нужно обращать внимание при написании запросов.
Фильтры не были переданы на удаленный сервер, а это значит, что для его выполнения Постгрес вытаскивает все 6 миллионов строк, чтобы затем отфильтровать локально (Filter row) и выполнить агрегацию.
Залог успеха — написать запрос так, чтобы фильтры передавались на удаленную машину, а мы получали и агрегировали только нужные строки.
Это какая-то ерунда С логическими полями все просто.
В исходном запросе проблема была в операторе является .
Если вы замените его на = , то получим следующий результат: 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 секунд.
Стоит отметить, что оператор является отличается от оператора = потому что он может работать со значением Null. Это означает, что не правда оставит в фильтре значения False и Null, тогда как != Верно оставит только ложные значения.
Поэтому при замене оператора не является в фильтр необходимо передать два условия с оператором ИЛИ, например, ГДЕ (столбец != True) ИЛИ (столбец равен нулю) .
С логическим значением мы разобрались, идем дальше.
А пока вернем булев фильтр в исходный вид, чтобы самостоятельно учесть влияние других изменений.
временная метка? хз В общем, часто приходится экспериментировать, как правильно написать запрос с участием удаленных серверов, и только потом искать объяснение, почему это происходит. В Интернете можно найти очень мало информации об этом.
Итак, в ходе экспериментов мы обнаружили, что фильтр с фиксированной датой улетает на удаленный сервер на ура, но когда мы хотим установить дату динамически, например, now() или CURRENT_DATE, этого не происходит. В нашем примере мы добавили фильтр, чтобы столбец «create_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 поле на удаленный сервер.
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, всегда проверяйте, чтобы все фильтры отправлялись на удаленный сервер, и будет вам счастье.
По крайней мере, пока вы не доберетесь до соединений между таблицами с разных серверов.
Но это уже история для другой статьи.
Спасибо за внимание! Буду рад услышать вопросы, комментарии и истории о вашем опыте в комментариях.
Теги: #Хранилища данных #Хранилище данных #postgresql #Инженерия данных #postgres #fdw #обертка внешних данных
-
Печать Формата А2 Для Деловых Целей
19 Oct, 24 -
Часть 1. Краткие Советы По Трафику
19 Oct, 24
