Микросервис архитектур нь энэ дэлхий дээрх бүх зүйлтэй адил давуу болон сул талуудтай. Үүний тусламжтайгаар зарим үйл явц илүү хялбар болж, зарим нь илүү хэцүү болдог. Өөрчлөлтийн хурд, илүү сайн цар хүрээтэй байхын тулд та золиослол хийх хэрэгтэй. Үүний нэг нь аналитикийн улам бүр нарийн төвөгтэй байдал юм. Хэрэв цул хэлбэрээр бүх үйл ажиллагааны аналитикийг SQL асуулга болгон аналитик хуулбар болгон бууруулж болох юм бол олон үйлчилгээний архитектурт үйлчилгээ бүр өөрийн гэсэн мэдээллийн сантай байдаг бөгөөд нэг асуулга хийх боломжгүй юм шиг санагддаг (эсвэл үүнийг хийх боломжтой юу?). Манай компанид үйл ажиллагааны аналитикийн асуудлыг хэрхэн шийдсэн, бид энэ шийдлээр хэрхэн амьдарч сурсан талаар сонирхож буй хүмүүст тавтай морилно уу.
Намайг Павел Сиваш гэдэг, DomClick-д би аналитик мэдээллийн агуулахыг хадгалах үүрэгтэй багт ажилладаг. Уламжлал ёсоор бол бидний үйл ажиллагааг өгөгдлийн инженерчлэл гэж ангилж болох боловч үнэн хэрэгтээ даалгаврын хүрээ илүү өргөн байдаг. Өгөгдлийн инженерчлэл, өгөгдөлд дүн шинжилгээ хийх хэрэгслүүдийг дэмжих, дасан зохицох, өөрийн хэрэгслийг хөгжүүлэхэд зориулсан ETL/ELT стандарт байдаг. Ялангуяа үйл ажиллагааны тайлан гаргахын тулд бид нэг цултай гэж "дүр эсгэж", шинжээчдэд шаардлагатай бүх өгөгдлийг агуулсан нэг мэдээллийн сан өгөхөөр шийдсэн.
Ерөнхийдөө бид янз бүрийн хувилбаруудыг авч үзсэн. Бүрэн хэмжээний агуулах барих боломжтой байсан - бид ч гэсэн оролдсон, гэхдээ үнэнийг хэлэхэд бид логикийн нэлээд давтамжтай өөрчлөлтийг репозитор барьж, түүнд өөрчлөлт оруулах нэлээд удаан үйл явцтай хослуулж чадаагүй (хэрэв хэн нэгэн амжилтанд хүрсэн бол) , коммент хэсэгт яаж бичээрэй). Шинжээчдэд: "Залуус аа, питон хэл сур, аналитик хуулбар руу ороорой" гэж хэлэх боломжтой байсан ч энэ нь элсүүлэхэд тавигдах нэмэлт шаардлага бөгөөд боломжтой бол үүнээс зайлсхийх хэрэгтэй юм шиг санагдсан. Бид FDW (Гадаад Мэдээллийн Боодол) технологийг ашиглахаар шийдсэн: үндсэндээ энэ нь 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 мөр).
Жаахан урагшлаад хүсэлтдээ хэд хэдэн шүүлтүүр нэмье: нэг нь 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 сая мөрийг бүгдийг нь татаж, дараа нь дотоод шүүлтүүр (Шүүлтүүрийн мөр) болон нэгтгэх ажлыг гүйцэтгэдэг гэсэн үг юм. Амжилтанд хүрэх түлхүүр бол шүүлтүүрийг алсын зайн машин руу шилжүүлэхийн тулд асуулга бичих явдал бөгөөд бид зөвхөн шаардлагатай мөрүүдийг хүлээн авч нэгтгэдэг.
Энэ бол ямар нэгэн утгагүй зүйл юм
Булийн талбаруудын хувьд бүх зүйл энгийн байдаг. Анхны хүсэлтэд асуудал нь оператороос үүдэлтэй байсан 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 утгуудыг үлдээх болно != Үнэн зөвхөн Худал утгыг үлдээх болно. Тиймээс операторыг солих үед биш OR оператортой хоёр нөхцөлийг шүүлтүүрт шилжүүлэх ёстой, жишээлбэл, WHERE (col != True) OR (col ).
Бид логикийн асуудлыг шийдсэн, цаашаа явцгаая. Одоохондоо бусад өөрчлөлтүүдийн үр нөлөөг бие даан авч үзэхийн тулд Boolean шүүлтүүрийг анхны хэлбэрт нь буцацгаая.
цаг тэмдэг? hz
Ерөнхийдөө, та алсын серверүүдийг хамарсан хүсэлтийг хэрхэн зөв бичих талаар туршилт хийх шаардлагатай болдог бөгөөд зөвхөн дараа нь яагаад ийм зүйл болж байгааг тайлбарлахыг хайж олох хэрэгтэй. Интернетээс энэ талаар маш бага мэдээлэл олж болно. Тиймээс, туршилтаар бид тогтсон огнооны шүүлтүүр нь алсын сервер рүү тэсрэлттэй нисдэг болохыг олж мэдсэн боловч бид огноог динамикаар тохируулахыг хүсвэл, жишээ нь, now() эсвэл CURRENT_DATE, ийм зүйл болохгүй. Бидний жишээн дээр бид шүүлтүүр нэмсэн бөгөөд ингэснээр created_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 дахин хурдан болсон!
Дахин хэлэхэд, энд болгоомжтой байх нь чухал: дэд асуулга дахь өгөгдлийн төрөл нь бидний шүүж буй талбарынхтай ижил байх ёстой, эс тэгвээс төрөл нь өөр тул эхлээд бүгдийг нь авах шаардлагатай гэж төлөвлөгч шийднэ. өгөгдлийг локал байдлаар шүүнэ.
Огнооны шүүлтүүрийг анхны утга руу нь буцаацгаая.
Фредди vs. Жсонб
Ерөнхийдөө Boolean талбарууд болон огноонууд нь бидний хүсэлтийг хангалттай хурдасгасан боловч дахиад нэг өгөгдлийн төрөл үлдсэн байна. Үнэнийг хэлэхэд, үүгээр шүүх тэмцэл дуусаагүй байгаа ч энд бас амжилт бий. Тиймээс бид шүүлтүүрийг ингэж өнгөрч чадсан юм 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 зан төлөв, доош түлхэх (XNUMX хувилбараас хойш байгаа). Би үүнийг удахгүй туршиж үзэхийг үнэхээр хүсч байна.
Түүнийг дуусга
Өөрчлөлт бүр хүсэлтийн хурдад хэрхэн нөлөөлж байгааг бид тус тусад нь туршиж үзсэн. Гурван шүүлтүүрийг зөв бичсэн тохиолдолд юу болохыг одоо харцгаая.
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