Mikroservis arxitekturası, bu dünyadakı hər şey kimi, müsbət və mənfi cəhətləri var. Bəzi proseslər onunla asanlaşır, bəziləri daha çətin olur. Dəyişiklik sürəti və daha yaxşı miqyaslılıq naminə, siz qurbanlar verməlisiniz. Onlardan biri analitikanın mürəkkəbliyinin artmasıdır. Monolitdə bütün əməliyyat analitikasını SQL sorğularına analitik replikaya endirmək olarsa, multiservisli arxitekturada hər bir xidmətin öz verilənlər bazası var və görünür ki, bir sorğu yerinə yetirilə bilməz (yaxud bəlkə də?). Şirkətimizdə əməliyyat analitikası problemini necə həll etdiyimiz və bu həll ilə yaşamağı necə öyrəndiyimizlə maraqlananlar üçün xoş gəlmisiniz.

Mənim adım Pavel Sivaş, DomClick-də mən analitik məlumat anbarının saxlanmasına cavabdeh olan komandada işləyirəm. Şərti olaraq, fəaliyyətlərimizi məlumat mühəndisliyi kimi təsnif etmək olar, lakin əslində, tapşırıqların diapazonu daha genişdir. Məlumat mühəndisliyi, verilənlərin təhlili üçün alətlərin dəstəklənməsi və uyğunlaşdırılması və öz alətlərinizin inkişafı üçün ETL/ELT standartı mövcuddur. Xüsusilə, operativ hesabat üçün biz monolitimiz olduğunu "iddia etmək" və analitiklərə lazım olan bütün məlumatları ehtiva edən bir verilənlər bazası vermək qərarına gəldik.

Ümumiyyətlə, biz müxtəlif variantları nəzərdən keçirdik. Tam hüquqlu bir anbar qurmaq mümkün idi - biz hətta cəhd etdik, amma düzünü desəm, məntiqdəki kifayət qədər tez-tez dəyişiklikləri bir depo qurmaq və ona dəyişikliklər etmək (kimsə bacardısa) kifayət qədər yavaş prosesi ilə birləşdirə bilmədik. , necə olduğunu şərhlərdə yazın). Analitiklərə: "Uşaqlar, piton öyrənin və analitik replikalara gedin" demək mümkün idi, lakin bu işə qəbul üçün əlavə bir tələbdir və mümkünsə bunun qarşısını almaq lazımdır. Biz FDW (Foreign Data Wrapper) texnologiyasından istifadə etməyə cəhd etmək qərarına gəldik: mahiyyətcə bu, SQL standartında olan, lakin daha rahat interfeysə malik standart dblinkdir. Buna əsaslanaraq biz bir həll yolu tapdıq, bu da nəticədə tutdu və biz buna qərar verdik. Onun təfərrüatları ayrı bir məqalənin mövzusudur və bəlkə də birdən çox, çünki mən çox şey haqqında danışmaq istəyirəm: verilənlər bazası sxemlərinin sinxronizasiyasından tutmuş, giriş nəzarəti və şəxsi məlumatların şəxsiyyətsizləşdirilməsinə qədər. Həm də qeyd etmək lazımdır ki, bu həll real analitik verilənlər bazası və repozitoriyaları əvəz etmir, yalnız müəyyən bir problemi həll edir.

Üst səviyyədə bu belə görünür:

İstifadəçilərin öz iş məlumatlarını saxlaya biləcəyi PostgreSQL verilənlər bazası mövcuddur və ən əsası bütün xidmətlərin analitik surətləri FDW vasitəsilə bu verilənlər bazasına qoşulur. Bu, bir neçə verilənlər bazasına sorğu yazmağa imkan verir və bunun nə olduğunun fərqi yoxdur: PostgreSQL, MySQL, MongoDB və ya başqa bir şey (fayl, API, birdən uyğun paket yoxdursa, özünüz yaza bilərsiniz). Yaxşı, hər şey əla görünür! Biz ayrılırıq?

Hər şey bu qədər tez və sadə bitsəydi, yəqin ki, məqalə də olmazdı.

Postgres-in uzaq serverlərə sorğuları necə emal etdiyini aydın bilmək vacibdir. Bu məntiqli görünür, lakin çox vaxt insanlar buna əhəmiyyət vermirlər: Postgres sorğunu uzaq serverlərdə müstəqil icra olunan hissələrə bölür, bu məlumatları toplayır və son hesablamaları özü həyata keçirir, ona görə də sorğunun icra sürəti çox asılı olacaq. necə yazılıb. Onu da qeyd etmək lazımdır: məlumatlar uzaq bir serverdən gəldikdə, onun artıq indeksləri yoxdur, planlaşdırıcıya kömək edəcək heç bir şey yoxdur, buna görə də ona yalnız özümüz kömək edə və məsləhət verə bilərik. Və daha ətraflı danışmaq istədiyim budur.

Sadə bir sorğu və onunla bir plan

Postgres-in uzaq serverdə 6 milyon sıra cədvəlini necə sorğuladığını göstərmək üçün sadə plana baxaq.

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 ifadəsindən istifadə bizə uzaq serverə göndəriləcək sorğunu və sonrakı emal üçün alacağımız nəticələri (RemoteSQL xətti) görməyə imkan verir.

Bir az da irəli gedək və sorğumuza bir neçə filtr əlavə edək: biri üçün boolean sahə, hadisə ilə bir vaxt damgası intervalda və bir-bir 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

Sorğu yazarkən diqqət etməli olduğunuz məqam budur. Filtrlər uzaq serverə ötürülməyib, yəni onu yerinə yetirmək üçün Postgres yerli olaraq filtrləmək (Filtr sətiri) və toplama yerinə yetirmək üçün bütün 6 milyon cərgəni çıxarır. Müvəffəqiyyətin açarı filtrlərin uzaq maşına köçürülməsi üçün sorğu yazmaqdır və biz yalnız lazımi sətirləri qəbul edirik və birləşdiririk.

Bu bir az məntiqdir

Boolean sahələri ilə hər şey sadədir. İlkin sorğuda problem operatorla bağlı idi is. ilə əvəz etsəniz =, onda aşağıdakı nəticəni alırıq:

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

Gördüyünüz kimi, filtr uzaq serverə uçdu və icra müddəti 27 saniyədən 19 saniyəyə endirildi.

Qeyd etmək lazımdır ki, operator is operatordan fərqlidir = çünki Null dəyəri ilə işləyə bilər. Bu o deməkdir ki Doğru deyil filtrdə False və Null dəyərlərini tərk edəcək, halbuki != Doğrudur yalnız Yanlış dəyərlər buraxacaq. Buna görə də, operatoru əvəz edərkən deyil OR operatoru ilə iki şərt filtrə ötürülməlidir, məsələn, WHERE (col != True) OR (color null).

Boolean ilə məşğul olduq, davam edək. Hələlik, digər dəyişikliklərin təsirini müstəqil nəzərdən keçirmək üçün Boolean filtrini orijinal formasına qaytaraq.

zaman damgası? hz

Ümumiyyətlə, siz tez-tez uzaq serverləri əhatə edən sorğunun necə düzgün yazılacağını sınamalı və yalnız bundan sonra bunun niyə baş verdiyini izah etməyə çalışmalısınız. İnternetdə bu barədə çox az məlumat tapmaq olar. Beləliklə, təcrübələrdə biz müəyyən etdik ki, sabit tarix filtri partlama ilə uzaq serverə uçur, lakin biz tarixi dinamik olaraq təyin etmək istədikdə, məsələn, now() və ya CURRENT_DATE, bu baş vermir. Nümunəmizdə biz filter əlavə etdik ki, yaradılmış_at sütununda keçmişdə düz 1 ay üçün məlumat var idi (CURRENT_DATE - INTERVAL '7 ay' VƏ CURRENT_DATE - INTERVAL '6 ay'). Bu halda biz nə etdik?

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

Planlayıcıya dedik ki, alt sorğuda tarixi əvvəlcədən hesablayın və hazır dəyişəni filtrə keçirək. Və bu işarə bizə əla nəticə verdi, sorğu demək olar ki, 6 dəfə sürətli oldu!

Yenə də burada diqqətli olmaq vacibdir: alt sorğudakı məlumat növü süzgəcdən keçirdiyimiz sahə ilə eyni olmalıdır, əks halda planlayıcı qərar verəcək ki, növlər fərqli olduğundan, əvvəlcə bütün məlumatları əldə etmək lazımdır. məlumatları yerli olaraq süzün.

Tarix filtrini orijinal dəyərinə qaytaraq.

Freddy vs. Jsonb

Ümumiyyətlə, Boolean sahələri və tarixlər sorğumuzu artıq kifayət qədər sürətləndirib, lakin daha bir məlumat növü qalıb. Düzünü desəm, süzgəclə mübarizə hələ də bitməyib, baxmayaraq ki, burada da uğur var. Beləliklə, filtrdən keçməyi bacardıq jsonb sahəni uzaq serverə köçürün.

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

Operatorları süzmək əvəzinə, bir operatorun mövcudluğundan istifadə etməlisiniz jsonb fərqli şəkildə. Orijinal 7 əvəzinə 29 saniyə. İndiyə qədər bu, filtrləri vasitəsilə ötürmək üçün yeganə uğurlu seçimdir jsonb uzaq serverə, lakin burada bir məhdudiyyəti nəzərə almaq vacibdir: biz verilənlər bazasının 9.6 versiyasından istifadə edirik, lakin aprelin sonuna qədər son sınaqları başa çatdırmağı və 12-ci versiyaya keçməyi planlaşdırırıq. Yenilədikdən sonra bunun necə təsir etdiyini yazacağıq, çünki çoxlu ümid olan dəyişikliklər var: json_path, yeni CTE davranışı, aşağı itələyin (versiya 10-dan bəri mövcuddur). Mən, həqiqətən, tezliklə cəhd etmək istəyirəm.

Onu bitir

Hər dəyişikliyin sorğu sürətinə necə təsir etdiyini test etdik. İndi görək hər üç filtr düzgün yazıldığında nə baş verir.

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

Bəli, sorğu daha mürəkkəb görünür, bu məcburi ödənişdir, lakin icra sürəti 2 saniyədir, bu da 10 dəfədən çox sürətlidir! Və biz nisbətən kiçik məlumat dəstinə qarşı sadə sorğudan danışırıq. Həqiqi müraciətlər əsasında bir neçə yüz dəfəyə qədər artım əldə etdik.

Xülasə etmək üçün: PostgreSQL-dən FDW ilə istifadə edirsinizsə, həmişə bütün filtrlərin uzaq serverə göndərildiyini yoxlayın və siz xoşbəxt olacaqsınız... Ən azı müxtəlif serverlərin cədvəlləri arasında birləşməyə çatana qədər. Ancaq bu başqa bir məqalə üçün bir hekayədir.

Diqqətinizə görə təşəkkürlər! Şərhlərdə təcrübələrinizlə bağlı suallar, şərhlər və hekayələr eşitmək istərdim.

Mənbə: www.habr.com