Мікрасэрвісная архітэктура, як і ўсё ў гэтым свеце, мае свае плюсы і свае мінусы. Адны працэсы з ёй становяцца прасцей, іншыя - складаней. І ва ўгоду хуткасці змен і лепшай маштабаванасці трэба прыносіць свае ахвяры. Адна з іх - ускладненне аналітыкі. Калі ў маналіце ўсю аператыўную аналітыку можна звесці да SQL запытаў да аналітычнай рэплікі, то ў мультысэрвіснай архітэктуры ў кожнага сэрвісу свая база і, здаецца, што адным запытам не абысціся (а можа абысціся?). Для тых, каму цікава, як мы вырашылі праблему аператыўнай аналітыкі ў сябе ў кампаніі і як навучыліся жыць з гэтым рашэннем – welcome.
Мяне клічуць Павел Сіваш, у ДомКліку я працую ў камандзе, якая адказвае за суправаджэнне аналітычнага сховішча дадзеных. Умоўна нашу дзейнасць можна аднесці да дата інжынерыі, але, насамрэч, спектр задач значна шырэй. Ёсць стандартныя для дата інжынерыі ETL/ELT, падтрымка і адаптацыя інструментаў для аналізу даных і распрацоўка сваіх інструментаў. У прыватнасці, для аператыўнай справаздачнасці мы вырашылі "прыкінуцца", што ў нас маналіт і даць аналітыкам адну базу, у якой будуць усе неабходныя ім дадзеныя.
Наогул, мы разглядалі розныя варыянты. Можна было пабудаваць паўнавартаснае сховішча — мы нават спрабавалі, але, калі сапраўды, так і не атрымалася пасябраваць досыць частыя змены ў логіцы з досыць павольным працэсам пабудовы сховішча і занясенні ў яго змен (калі ў кагосьці атрымалася, напішыце ў каментарах як). Можна было сказаць аналітыкам: "Хлопцы, вучыце python і хадзіце ў аналітычныя рэплікі", але гэта дадатковае патрабаванне да падбору персаналу, і здавалася, што гэтага варта пазбегнуць, калі магчыма. Вырашылі паспрабаваць выкарыстоўваць тэхналогію FDW (Foreign Data Wrapper): у сутнасці, гэта стандартны dblink, які ёсць у стандарце SQL, але са сваім значна зручнейшым інтэрфейсам. На базе яе мы зрабілі рашэньне, якое ў выніку і прыжылося, на ім мы спыніліся. Яго падрабязнасці - тэма асобнага артыкула, а можа і не адной, паколькі распавесці хочацца аб многім: ад сінхранізацыі схем баз да кіравання доступам і абязлічвання персанальных дадзеных. Таксама трэба абмовіцца, што гэтае рашэнне не з'яўляецца заменай рэальным аналітычным базам і сховішчам, яно вырашае толькі пэўную задачу.
Верхнеўзроўнева гэта выглядае так:
Ёсць база PostgreSQL, тамака карыстачы могуць захоўваць свае працоўныя дадзеныя, а найважнейшае — да гэтай базы праз FDW падлучаныя аналітычныя рэплікі ўсіх сэрвісаў. Гэта дае магчымасць напісаць запыт да некалькіх баз, прычым усё роўна, што гэта: PostgreSQL, MySQL, MongoDB ці яшчэ нешта (файл, API, калі раптам няма падыходнага враппера, можна напісаць свой). Ну накшталт усё, супер! Разыходзімся?
Калі б усё сканчалася так хутка і проста, то, мусіць, артыкулы б і не было.
Важна выразна ўсведамляць, як постгрэс апрацоўвае запыты да выдаленых сервераў. Гэта здаецца лагічным, аднак часцяком на гэта не зважаюць: постгрэс дзеліць запыт на часткі, якія выконваюцца на выдаленых серверах незалежна, збірае гэтыя дадзеныя, а фінальныя вылічэнні праводзіць ужо сам, таму хуткасць выканання запыту будзе моцна залежаць ад таго, як ён напісаны. Варта гэтак жа адзначыць: калі дадзеныя паступаюць з выдаленага сервера ў іх ужо няма азначнікаў, няма нічога, што дапаможа планавальніку, такім чынам, дапамагчы і падказаць яму можам толькі мы самі. І менавіта пра гэта хочацца расказаць падрабязней.
Просты запыт і план з ім
Каб паказаць, як постгрэс выконвае запыт да табліцы на 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Вось менавіта тут і крыецца момант, на які неабходна зважаць пры напісанні запытаў. Фільтры не перадаліся на выдалены сервер, а гэта значыць, што для яго выканання постгрэс выцягвае ўсе 6 мільёнаў радкоў, каб ужо потым лакальна адфільтраваць (радок Filter) і вырабіць агрэгацыю. Заклад поспеху - гэта напісаць запыт так, каб фільтры перадаваліся на выдаленую машыну, а мы атрымлівалі і агрэгавалі толькі патрэбныя радкі.
That's some booleanshit
З boolean палямі - усё проста. У зыходным запыце праблема ўзнікала з-за аператара 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 адрозніваецца ад аператара = тым, што ўмее працаваць са значэннем Null. Гэта азначае, што is not True у фільтры пакіне значэння False і Null, тады як != True пакіне толькі значэння False. Таму пры замене аператара ня варта перадаваць у фільтр дзве ўмовы з аператарам 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'). Што ж мы зрабілі ў дадзеным выпадку?
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 разоў!
Ізноў жа, тут важна быць уважлівым: тып дадзеных у подзапросе павінен быць такім жа, што і ў поля, па якім які фільтруецца, інакш планавальнік вырашыць, што раз тыпы розныя і неабходна спачатку дастаць усе дадзеныя і ўжо лакальна адфільтраваць.
Вернем фільтр па даце ў зыходнае значэнне.
Freddy vs. 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, аднак да канца красавіка плануем завяршыць апошнія тэсты і пераехаць на 10 версію. Як абновімся, напішам, як гэта паўплывала, бо змен, на якія шмат надзей, дастаткова шмат: json_path, новыя паводзіны CTE, push down (існуючы з XNUMX версіі). Вельмі жадаецца хутчэй паспрабаваць.
Finish him
Мы праверылі, як кожнае змяненне ўплывае на хуткасць запыту па асобнасці. Давайце зараз паглядзім, што будзе, калі ўсе тры фільтры будуць напісаны правільна.
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, заўсёды правярайце, ці ўсе фільтры адпраўляюцца на выдалены сервер, і будзе вам шчасце… Прынамсі, пакуль вы не дойдзеце да джойнаў паміж табліцамі з розных сервераў. Але гэта ўжо гісторыя для яшчэ аднаго артыкула.
Дзякуй за ўвагу! Буду рады пачуць пытанні, каментары, а таксама гісторыі аб вашым вопыце ў каментарах.
Крыніца: habr.com