Arhitectura de microservicii, ca tot ce este în această lume, are avantaje și dezavantaje. Unele procese devin mai ușoare cu el, altele mai dificile. Și de dragul vitezei de schimbare și al unei scalabilitati mai bune, trebuie să faceți sacrificii. Una dintre ele este complexitatea tot mai mare a analizei. Dacă într-un monolit toată analiza operațională poate fi redusă la interogări SQL la o replică analitică, atunci într-o arhitectură multiservicii fiecare serviciu are propria sa bază de date și se pare că o singură interogare nu se poate face (sau poate se poate?). Pentru cei care sunt interesați de modul în care am rezolvat problema analizei operaționale în compania noastră și cum am învățat să trăim cu această soluție - bine ați venit.

Numele meu este Pavel Sivash, la DomClick lucrez într-o echipă care este responsabilă cu menținerea depozitului de date analitice. În mod convențional, activitățile noastre pot fi clasificate ca inginerie de date, dar, de fapt, gama de sarcini este mult mai largă. Există standarde ETL/ELT pentru ingineria datelor, suport și adaptare a instrumentelor pentru analiza datelor și dezvoltarea propriilor instrumente. În special, pentru raportarea operațională, am decis să „pretindem” că avem un monolit și să oferim analiştilor o bază de date care va conține toate datele de care au nevoie.

În general, am luat în considerare diferite opțiuni. A fost posibil să construim un depozit cu drepturi depline - chiar am încercat, dar, să fiu sincer, nu am reușit să combinăm schimbările destul de frecvente ale logicii cu procesul destul de lent de construire a unui depozit și de a-i face modificări (dacă cineva a reușit , scrie in comentarii cum). A fost posibil să le spun analiștilor: „Băieți, învățați python și mergeți la replici analitice”, dar aceasta este o cerință suplimentară pentru recrutare și se părea că acest lucru ar trebui evitat dacă este posibil. Am decis să încercăm să folosim tehnologia FDW (Foreign Data Wrapper): în esență, acesta este un dblink standard, care este în standardul SQL, dar cu propria sa interfață mult mai convenabilă. Pe baza ei, am făcut o soluție, care a prins până la urmă și ne-am hotărât. Detaliile sale fac subiectul unui articol separat, și poate mai mult de unul, deoarece vreau să vorbesc despre multe: de la sincronizarea schemelor bazelor de date până la controlul accesului și depersonalizarea datelor personale. De asemenea, este necesar să facem o rezervare că această soluție nu este un înlocuitor pentru baze de date și depozite analitice reale, ea rezolvă doar o problemă specifică.

La nivelul superior arată astfel:

Există o bază de date PostgreSQL în care utilizatorii își pot stoca datele de lucru și, cel mai important, replicile analitice ale tuturor serviciilor sunt conectate la această bază de date prin FDW. Acest lucru face posibilă scrierea unei interogări în mai multe baze de date și nu contează ce este: PostgreSQL, MySQL, MongoDB sau altceva (fișier, API, dacă dintr-o dată nu există un wrapper potrivit, îl puteți scrie pe al dvs.). Ei bine, totul pare grozav! Ne despărțim?

Dacă totul s-ar termina atât de repede și simplu, atunci, probabil, nu ar mai fi un articol.

Este important să fie clar modul în care Postgres procesează cererile către serverele de la distanță. Acest lucru pare logic, dar de multe ori oamenii nu-i acordă atenție: Postgres împarte cererea în părți care sunt executate independent pe servere la distanță, colectează aceste date și efectuează singuri calculele finale, astfel încât viteza de execuție a interogării va depinde foarte mult de cum este scris. De remarcat: atunci când datele sosesc de la un server la distanță, acesta nu mai are indexuri, nu există nimic care să-l ajute pe planificator, prin urmare, doar noi înșine îl putem ajuta și sfătui. Și despre asta vreau să vorbesc mai detaliat.

O interogare simplă și un plan cu ea

Pentru a arăta cum Postgres interogează un tabel de 6 milioane de rânduri pe un server la distanță, să ne uităm la un plan simplu.

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

Folosirea instrucțiunii VERBOSE ne permite să vedem interogarea care va fi trimisă către serverul de la distanță și rezultatele căreia le vom primi pentru procesare ulterioară (linia RemoteSQL).

Să mergem puțin mai departe și să adăugăm mai multe filtre la cererea noastră: unul pentru boolean câmp, unul după ocurență timestamp-ul în interval şi una câte 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

Acesta este punctul la care trebuie să acordați atenție atunci când scrieți interogări. Filtrele nu au fost transferate pe serverul de la distanță, ceea ce înseamnă că pentru a le executa, Postgres scoate toate cele 6 milioane de rânduri pentru a filtra apoi local (Rând Filtru) și a realiza agregarea. Cheia succesului este să scriem o interogare, astfel încât filtrele să fie transferate pe mașina de la distanță, iar noi primim și agregam doar rândurile necesare.

Sunt niște prostii booleane

Cu câmpurile booleene totul este simplu. În cererea inițială, problema se datora operatorului is. Daca il inlocuiesti cu =, atunci obținem următorul rezultat:

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

După cum puteți vedea, filtrul a zburat către un server la distanță, iar timpul de execuție a fost redus de la 27 la 19 secunde.

Este de remarcat faptul că operatorul is diferit de operator = deoarece poate funcționa cu valoarea Null. Înseamnă că nu este adevărat va lăsa valorile False și Null în filtru, în timp ce != Adevărat va lăsa doar valori false. Prin urmare, la înlocuirea operatorului nu este două condiții cu operatorul SAU ar trebui trecute la filtru, de exemplu, WHERE (coloana != adevărat) SAU (coloana este nulă).

Ne-am ocupat de boolean, să mergem mai departe. Deocamdată, să readucem filtrul boolean la forma sa originală pentru a lua în considerare în mod independent efectul altor modificări.

timestamptz? hz

În general, de multe ori trebuie să experimentați cum să scrieți corect o solicitare care implică servere la distanță și abia apoi să căutați o explicație a motivului pentru care se întâmplă acest lucru. Foarte puține informații despre acest lucru pot fi găsite pe Internet. Așadar, în experimente am descoperit că un filtru de dată fixă ​​zboară către serverul de la distanță cu un bang, dar când dorim să setăm data dinamic, de exemplu, now() sau CURRENT_DATE, acest lucru nu se întâmplă. În exemplul nostru, am adăugat un filtru, astfel încât coloana create_at să conţină date pentru exact o lună în trecut (INTRE CURRENT_DATE - INTERVAL '1 month' AND CURRENT_DATE - INTERVAL '7 month'). Ce am facut in acest caz?

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

I-am spus planificatorului să calculeze în avans data în subinterogare și să treacă variabila gata făcută la filtru. Și acest indiciu ne-a dat un rezultat excelent, cererea a devenit de aproape 6 ori mai rapidă!

Din nou, este important să fiți atenți aici: tipul de date din subinterogare trebuie să fie același cu cel al câmpului pe care filtrăm, altfel planificatorul va decide că, deoarece tipurile sunt diferite, este necesar să obțineți mai întâi toate datele și filtrați-le local.

Să readucem filtrul de dată la valoarea inițială.

Freddy vs. Jsonb

În general, câmpurile și datele booleene au accelerat deja suficient interogarea noastră, dar a mai rămas un tip de date. Lupta cu filtrarea după ea, sincer să fiu, încă nu s-a încheiat, deși există succes și aici. Deci, așa am reușit să trecem de filtru jsonb câmp către serverul de la distanță.

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

În loc să filtrați operatorii, trebuie să utilizați prezența unui operator jsonb într-un alt. 7 secunde în loc de originalul 29. Până acum aceasta este singura opțiune de succes pentru transmiterea filtrelor prin jsonb la un server la distanță, dar aici este important să ținem cont de o limită: folosim versiunea 9.6 a bazei de date, dar până la sfârșitul lunii aprilie intenționăm să finalizăm ultimele teste și să trecem la versiunea 12. Odată ce vom actualiza, vom scrie despre modul în care a afectat, pentru că există destul de multe modificări pentru care există multe speranțe: json_path, comportament nou CTE, push down (existent de la versiunea 10). Chiar vreau să-l încerc curând.

Termina-l

Am testat modul în care fiecare modificare a afectat viteza solicitărilor în mod individual. Să vedem acum ce se întâmplă când toate cele trei filtre sunt scrise corect.

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

Da, cererea pare mai complicată, aceasta este o taxă forțată, dar viteza de execuție este de 2 secunde, ceea ce este de peste 10 ori mai rapidă! Și vorbim despre o interogare simplă împotriva unui set de date relativ mic. La cereri reale, am primit o creștere de până la câteva sute de ori.

Pentru a rezuma: dacă utilizați PostgreSQL cu FDW, verificați întotdeauna că toate filtrele sunt trimise la serverul de la distanță și veți fi mulțumit... Cel puțin până ajungeți la uniuni între tabele de pe servere diferite. Dar asta este o poveste pentru alt articol.

Vă mulțumim pentru atenție! Mi-ar plăcea să aud întrebări, comentarii și povești despre experiențele tale în comentarii.

Sursa: www.habr.com