Микросервис архитектурасы, осы әлемдегі барлық нәрсе сияқты, оның жақсы және жаман жақтары бар. Кейбір процестер онымен оңайырақ, басқалары қиынырақ болады. Өзгеріс жылдамдығы мен жақсырақ ауқымды болу үшін сізге құрбандық қажет. Олардың бірі – аналитиканың күрделілігі. Егер монолитте барлық операциялық аналитиканы SQL сұрауларына аналитикалық репликаға дейін азайтуға болатын болса, онда көпсервистік архитектурада әрбір қызметтің өз деректер қоры бар және бір сұрау жеткіліксіз сияқты (немесе мүмкін болар?). Біздің компанияда операциялық аналитика мәселесін қалай шешкенімізге және осы шешіммен қалай өмір сүруді үйренгенімізге қызығушылық танытатындар үшін - қош келдіңіз.
Менің атым Павел Сиваш, DomClick-те мен аналитикалық деректер қоймасын жүргізуге жауапты топта жұмыс істеймін. Әдеттегідей, біздің әрекеттерімізді деректер инженериясына жатқызуға болады, бірақ іс жүзінде міндеттер ауқымы әлдеқайда кең. Стандартты ETL/ELT деректер инженериясы, деректерді талдау құралдарын қолдау және бейімдеу және өз құралдарын әзірлеу бар. Атап айтқанда, жедел есеп беру үшін бізде монолит бар деп «үшін көрсетуді» шештік және талдаушыларға барлық қажетті деректерді қамтитын бір дерекқорды беруді шештік.
Жалпы, біз әртүрлі нұсқаларды қарастырдық. Толық репозиторий құру мүмкін болды - біз тіпті тырыстық, бірақ, шынымды айтсам, репозиторийді құру және оған өзгертулер енгізу процесі өте баяу логикада жиі өзгерістермен дос бола алмадық ( егер біреу сәтті болса, түсініктемелерде қалай екенін жазыңыз). Сіз аналитиктерге: «Жігіттер, питонды үйреніп, аналитикалық сызықтарға өтіңіз» деп айтуға болады, бірақ бұл жұмысқа қабылдаудың қосымша талабы және мүмкіндігінше бұған жол бермеу керек сияқты. Біз FDW (Foreign Data Wrapper) технологиясын қолданып көруді шештік: шын мәнінде бұл SQL стандартында, бірақ әлдеқайда ыңғайлы интерфейсі бар стандартты dblink. Соның негізінде біз шешім қабылдадық, ол түбегейлі тамыр жайды, соған тоқтадық. Оның егжей-тегжейлері - жеке мақаланың тақырыбы, мүмкін біреуден көп, өйткені мен көп нәрсе туралы айтқым келеді: дерекқор схемасын синхрондаудан бастап қол жеткізуді басқаруға және жеке деректерді иеліктен шығаруға дейін. Сондай-ақ, бұл шешім нақты аналитикалық деректер базасы мен репозиторийлерді алмастырмайды, ол тек белгілі бір мәселені шешеді.
Жоғарғы деңгейде ол келесідей көрінеді:
Пайдаланушылар өздерінің жұмыс деректерін сақтай алатын 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 msVERBOSE операторын пайдалану қашықтағы серверге жіберілетін сұрауды және одан әрі өңдеу үшін біз алатын нәтижелерін көруге мүмкіндік береді (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 миллион жолды тартады. Сәттіліктің кілті - сүзгілер қашықтағы машинаға берілетіндей сұрау жазу және біз тек қажетті жолдарды аламыз және біріктіреміз.
Бұл қандай да бір логикалық сөз
Логикалық өрістермен бәрі қарапайым. Бастапқы сұрауда мәселе операторға байланысты болды 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 мәнімен жұмыс істей алатын. Бұл дегеніміз дұрыс емес сүзгіде False және Null мәндерін қалдырады, ал != Рас тек Жалған мәндерді қалдырады. Сондықтан операторды ауыстырған кезде емес НЕМЕСЕ операторымен сүзгіге екі шартты беру керек, мысалы, WHERE (col != True) НЕМЕСЕ (col нөл).
Логикалық есептеліп, әрі қарай жылжу. Осы арада басқа өзгерістердің әсерін дербес қарастыру үшін логикалық мән бойынша сүзгіні бастапқы түріне қайтарайық.
уақыт белгісі? hz
Тұтастай алғанда, қашықтағы серверлерді қамтитын сұрауды қалай дұрыс жазуға болатынын жиі тәжірибе жасау керек, содан кейін ғана бұл неліктен болып жатқанын түсіндіруді іздеу керек. Бұл туралы Интернетте өте аз ақпаратты табуға болады. Сонымен, эксперименттерде біз бекітілген күн сүзгісі қашықтағы серверге соққымен ұшатынын анықтадық, бірақ біз күнді динамикалық түрде орнатқымыз келгенде, мысалы, now() немесе CURRENT_DATE, бұл болмайды. Біздің мысалда жасалған_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 әрекеті, төмен басыңыз (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