Mikroservis arxitekturasının, bu dünyadakı hər şey kimi, öz üstünlükləri və çatışmazlıqları var. Bəzi proseslər bununla daha asan, digərləri isə daha çətin olur. Dəyişikliklərin sürəti və daha yaxşı miqyaslana bilmək üçün bəzi qurbanlar vermək lazımdır. Bunlardan biri, analitik prosesin çətinləşməsidir. Monolitdə bütün operativ analitika SQL sorğularına çevrilə bilirsə, çoxsaylı xidmətlər arxitekturasında hər xidmətin öz bazası var və bir sorğu ilə iş keçirmək mümkün olmur (ya da mümkündür?). Özümüzdə operativ analitika problemini necə həll etdiyimizi və bu həll ilə necə yaşamağı öyrəndiyimizi maraqlananlar üçün - buyurun.
Mənim adım Pavel Sivashdır, DomKlikdə analitik məlumat anbarının dəstəklənməsi ilə məşğul olan komandada çalışıram. Şərti olaraq fəaliyyətimizi data mühəndisliyinə aid etmək olar, lakin əslində, işlərin spektri daha genişdir. Data mühəndisliyi üçün standart ETL/ELT prosesləri, məlumat analizi üçün alətlərin dəstəklənməsi və uyğunlaşdırılması və öz alətlərimizin hazırlanması var. Xüsusilə, operativ hesabat üçün «monolit» olduğumuzu iddia edərək, analitiklərə bütün lazım olan məlumatların olduğu bir baza təqdim etməyə qərar verdik.
Ümumiyyətlə, biz müxtəlif variantları nəzərdən keçirdik. Tam funksional bir anbar qurmaq mümkün idi - hətta bunu sınadıq, amma etiraf etməliyəm ki, kifayət qədər tez-tez olan məntiq dəyişikliklərini anbarı yaratma və ona dəyişikliklər etmə prosesinin slowluğuyla dostlaşdıra bilmədik (kimsə bunu bacarıbsa, şərhlərdə yazsın). Analitiklərə belə demək olardı: "Dostlar, python öyrənib analitik replikalara gedin", lakin bu, kadr seçimi üçün əlavə tələbat yaradır və mümkün olduqda bundan qaçmağın daha məqsədəuyğun olduğunu düşündük. FDW (Foreign Data Wrapper) texnologiyasını sınamağa qərar verdik: əslində, bu, SQL standartında mövcud olan standart dblink-dir, lakin daha rahat bir interfeysi var. Baza olaraq bunun üzərində bir həll yaratdıq ki, sonunda bu da məqbul oldu, bu ətrafında dayanmağı seçdik. Onun detalları - ayrıca bir yazının mövzusu, bəlkə də bir neçəsinin, çünki çox şey danışmaq istərdim: bazaların sxemlərinin sinxronizasiyasından tutmuş, girişin idarə edilməsinə və şəxsi məlumatların anonimləşdirilməsinə qədər. Həmçinin qeyd etmək lazımdır ki, bu həll real analitik bazalar və anbarlar üçün əvəz deyil, yalnız konkret bir problemi həll edir.
Yuxarı səviyyədə belə görünür:
PostgreSQL bazası var, orada istifadəçilər öz iş məlumatlarını saxlaya bilərlər, ən vacib isə - bu bazaya FDW vasitəsilə bütün xidmətlərin analitik replikaları qoşulub. Bu, bir neçə bazaya sorğu yazmağa imkan tanıyır, və bunun nə olduğu vacib deyil: PostgreSQL, MySQL, MongoDB, ya da başqa bir şey (fayl, API; əgər uyğun bir wrapper yoxdursa, özünüz yaza bilərsiniz). Görünür ki, hər şey bu qədər sadədir, eləmi? Ayrılırıq?
Əgər hər şey bu qədər sürətli və sadə olsaydı, bəlkə də bu yazı da olmazdı.
PostgreSQL-in uzaq serverlərə sorğuları necə emal etdiyini aydın təsəvvür etmək vacibdir. Bu, məntiqli görünür, lakin çox vaxt buna diqqət yetirilmir: PostgreSQL sorğunu uzlaşmalara bölür ki, bunlar uzaq serverlərdə müstəqil yerinə yetirilir, bu məlumatları toplayır və son hesablamaları artıq özü aparır, buna görə də sorğunun icra sürəti yazılış tərzindən çox asılı olacaq. Həmçinin, uzaq serverdən alınan məlumatlarda indekslər yoxdur, planlaşdırıcıya kömək edəcək heç bir şey yoxdur, buna görə də onu yalnız biz məsləhət verə bilərik. Və məhz bu barədə daha ətraflı məlumat vermək istəyirəm.
Sadə sorğu və onunla plan
Postgres-in 6 milyon sətirli cədvələ necə sorğu göndərdiyini göstərmək üçün sunucusu, sadə plana nəzər salaq.
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 əmri istifadəsi, bizə uzaq serverə göndəriləcək sorğunu və onun nəticələrini görməyə imkan tanıyır (RemoteSQL sətiri).
Bir az irəliləyək və sorğumuza bir neçə filtr əlavə edək: biri boolean sahəsi üzrə, biri daxilolma üzrə timestamp aralıqda və biri 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 msBurada, sorğuları yazarkən diqqət yetirilməsi lazım olan məqam gizlidir. Filtrlər uzaq serverə ötürülmədi, bu da o deməkdir ki, Postgres bütün 6 milyon sətiri çəkməli olur ki, sonra onları yerli olaraq süzgəcdən keçirib (Filter sətiri) və toplama edək. Uğurun açarı — sorğunu elə yazmaqdır ki, filtrlər uzaq maşına ötürülsün və biz yalnız lazım olan sətirləri alıb toplayaq.
Bu, bəzi boolean məsələləridir
Boolean sahələrilə — hər şey sadədir. Orijinal sorğuda problem əməliyyatçıdan yaranırdı is. Əgər onu =ilə əvəz etsək, o zaman biz aşağıdakı nəticəni alarıq:
açıqlama analizi ətraflı
SEÇİN count(1)
FROM fdw_schema.table
HARADA is_active = Doğru
VƏ created_dt İKİNDƏ CURRENT_DATE - INTERVAL '7 ay'
VƏ CURRENT_DATE - INTERVAL '6 ay'
VƏ meta->>'source' = 'test';
Toplama (xərclər=508010.14..508010.15 sətir=1 genişlik=8) (realdan 19064.314..19064.314 sətir=1 dövrlər=1)
Çıxış: count(1)
-> Xarici Skana fdw_schema."table" (xərclər=100.00..507988.44 sətir=8679 genişlik=0) (realdan 33.035..18951.278 sətir=1360025 dövrlər=1)
Çıxış: "table".id, "table".is_active, "table".meta, "table".created_dt
Filtr: ((("table".meta ->> 'source'::text) = 'test'::text) VƏ ("table".created_dt >= (('now'::cstring)::date - '7 ay'::interval)) VƏ ("table".created_dt <= ((('now'::cstring)::date)::timestamp ilə zaman zonası - '6 ay'::interval)))
Filtr ilə çıxarılan Sətirlər: 3567989
Uzaq SQL: SEÇİN created_dt, meta FROM fdw_schema.table HARADA (is_active)
Planlama vaxtı: 0.834 ms
İcra müddəti: 19064.534 msGördüyünüz kimi, filtr uzaq serverə keçdi, icra müddəti isə 27-dən 19 sekунда azaldı.
Qeyd etmək lazımdır ki, operator is operatorundan fərqlənir = ortaq işləməyi bacardığına görə. Bu, deməkdir ki, True deyil filtrdə False və Null dəyərləri saxlayacaq, halbuki != True yalnız False dəyərlərini saxlayacaq. Ona görə də, operatoru dəyişdirərkən is not filtrə, məsələn, OR operatoru ilə iki şərt daxil etməyi unutmayın: HARADA (col != True) VƏ (col is null).
Boolean-u anladıq, davam edirik. Bu arada, boolean dəyərini filtrin ilkin görünümünə qaytaraq ki, digər dəyişikliklərin effektini müstəqil şəkildə qiymətləndirə bilək.
timestamptz? hz
Ümumiyyətlə, uzaq serverlərin iştirak etdiyi sorğuları düzgün yazmağı təcrübə aparmaq lazım gəlir, sonra isə niyə belə olduğunu aydınlaşdırmaq üçün izah axtarırlar. Bu mövzuda şəbəkədə çox az məlumat var. Məsələn, eksperimentlərimizdə gördük ki, sabit tarix üçün filtr uğurla uzaq serverə keçir, ancaq dinamik tarix qoyarkən, məsələn, now() və ya CURRENT_DATE, belə şey baş vermir. Bizim nümunəmizdə, created_at sütununun tam 1 ay əvvəlki məlumatları saxladığını təmin etmək üçün belə bir filtr əlavə etdik (BETWEEN CURRENT_DATE - INTERVAL '7 ay' VƏ CURRENT_DATE - INTERVAL '6 ay'). Bəs, bu halda nə etdik?
izah et analiz et, ətraflı
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 >'source' = 'test';
Toplanma (xərclər=306875.17..306875.18 sətirlər=1 genişlik=8) (real vaxt=4789.114..4789.115 sətirlər=1 dövrlər=1)
Çıxış: count(1)
InitPlan 1 (qaytarır $0)
-> Nəticə (xərclər=0.00..0.02 sətirlər=1 genişlik=8) (real vaxt=0.007..0.008 sətirlər=1 dövrlər=1)
Çıxış: ((('indiki'::cstring)::date)::timestamp with time zone - '7 ay'::interval)
InitPlan 2 (qaytarır $1)
-> Nəticə (xərclər=0.00..0.02 sətirlər=1 genişlik=8) (real vaxt=0.002..0.002 sətirlər=1 dövrlər=1)
Çıxış: ((('indiki'::cstring)::date)::timestamp with time zone - '6 ay'::interval)
-> Xarici Skan fdw_schema."table" (xərclər=100.02..306874.86 sətirlər=105 genişlik=0) (real vaxt=23.475..4681.419 sətirlər=1360025 dövrlər=1)
Çıxış: "table".id, "table".is_active, "table".meta, "table".created_dt
Filtr: (("table".is_active IS TRUE) AND (("table".meta ->> 'source'::text) = 'test'::text))
Filtr tərəfindən çıxarılan sətirlər: 76934
Uzaq 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))
Planlaşdırma vaxtı: 0.703 ms
İcra vaxtı: 4789.379 msBiz planlayıcıya əvvəlcədən alt sorğuda tarixi hesablamağı və hazır olan dəyişəni filtrə göndərməyi tövsiyə etdik. Bu tövsiyə bizə gözəl nəticə verdi, sorğu təxminən 6 dəfə sürətlənmiş oldu!
Yenə də burada diqqətli olmaq vacibdir: alt sorğuda verilənlərin növü filtr üçün istifadə edilən sahənin növü ilə eyni olmalıdır, əks halda planlayıcı düşünəcək ki, növlər fərqlidir və əvvəlcə bütün verilənləri əldə etməli, daha sonra yerli olaraq filtr etməlidir.
Filtri tarixi əvvəlki vəziyyətinə qaytaraq.
Freddy vs. Jsonb
Əslində, bool sahələri və tarixlər artıq sorğumuzu kifayət qədər sürətləndirmişdi, amma hələ də bir verilən növü qalırdı. Onunla filtrasiya mübarizəsi, açığı, hələ də tam bitməyib, amma burada da uğurlarımız var. Beləliklə, filtrimizi necə ötürdüyümüz budur. jsonb sahəni uzaq serverə.
izah et analiz et, ətraflı
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;
Toplanma (xərclər=245463.60..245463.61 sətirlər=1 genişlik=8) (real vaxt=6727.589..6727.590 sətirlər=1 dövrlər=1)
Çıxış: count(1)
-> Xarici Skan fdw_schema."table" (xərclər=1100.00..245459.90 sətirlər=1478 genişlik=0) (real vaxt=16.213..6634.794 sətirlər=1360025 dövrlər=1)
Çıxış: "table".id, "table".is_active, "table".meta, "table".created_dt
Filtr: (("table".is_active IS TRUE) AND ("table".created_dt >= (('indiki'::cstring)::date - '7 ay'::interval)) AND ("table".created_dt '{"source": "test"}'::jsonb))
Planlaşdırma vaxtı: 0.747 ms
İcra vaxtı: 6727.815 msFiltrasiya operatorları yerinə birliyin operatorunu istifadə etmək lazımdır. jsonb başqa birində. 29 əvəzinə 7 saniyə. Hal-hazırda bu, filtr ötürmələrinin uğurlu yerinə yetirilməsi üçün tək variantdır. jsonb uzaq serverə, amma burada bir məhdudiyyəti nəzərə almaq vacibdir: biz 9.6 versiyasını istifadə edirik, amma aprel ayının sonuna qədər son testləri bitirib 12-ci versiyaya keçməyi planlaşdırırıq. Yeniləndikdə, bunun necə təsir etdiyini yazacağıq, çünki gözlənilən dəyişikliklər var: json_path, CTE-nin yeni davranışı, push down (10-cu versiyadan mövcuddur). Daha tez cəhd etmək istəyirik.
Finish him
Hər bir dəyişiklik sorğunun sürətinə ayrı-ayrılıqda necə təsir etdiyini yoxladıq. İndi isə gəlin, üç filtr düzgün yazıldıqda nə olacağını baxaq.
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 '{"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 '{"source": "test"}'::jsonb))
Planning time: 0.820 ms
Execution time: 2279.087 msBəli, sorğu daha mürəkkəb görünür, bu, məcburi bir xərcdir, ancaq icra sürəti 2 saniyədir ki, bu da 10 dəfədən çox daha sürətlidir! Və bu, nisbətən kiçik bir məlumat dəstinə sadə bir sorğu haqqında danışırıq. Real sorğularda yüzlərlə qat artım əldə etdik.
Nəticə çıxaraq: əgər siz PostgreSQL-dən FDW ilə istifadə edirsinizsə, həmişə bütün filtrərin uzaq serverə göndərildiyinə əmin olun, sizə şans gətirəcək... Ən azından, tabelaların fərqli cədvəlləri arasında qoşulmalara qədər. serverləri üçün mükəmməl seçim edir.. Amma bu artıq başqa bir məqalənin mövzusudur.
Diqqətiniz üçün təşəkkür edirəm! Suallarınızı, şərhlərinizi və eyni zamanda təcrübəniz haqqında hekayələri eşitməkdən məmnun olardım.
Mənbə: habr.com
