Mikroteenused arhitektuur, nagu kõik maailmas, omab oma plusse ja miinuseid. Mõned protsessid muutuvad selle abil lihtsamaks, teised aga keerukamaks. Kiiremate muudatuste ja parema skaleeritavuse nimel tuleb teha ohverdusi. Üks neist on analüüsi keerukuse suurenemine. Kui monoliitses lähenemises saab kogu operatiivanalüüsi kokku võtta SQL-päringuteks analüütilisele koopiale, siis mikroteenuste arhitektuuris on iga teenuse jaoks eraldi andmebaas ja näib, et ühe päringuga ei saa hakkama (aga võib-olla saab?). Neile, keda huvitab, kuidas me oma ettevõttes operatiivanalüüsi probleemi lahendasime ja kuidas me sellega kohanema õppisime — tere tulemast.

Minu nimi on Pavel Sivash, DomKlikis töötan meeskonnas, mis vastutab andmeanalüütika laohaldamise eest. Üldiselt võib meie tegevust nimetada andmeinseneri tööks, kuid tegelikult on ülesannete ulatus palju laiem. On olemas andmeinsenerile standardsed ETL/ELT, tööriistade toetamine ja kohandamine andmeanalüüsi jaoks ning meie enda tööriistade arendamine. Eelkõige operatiivsete aruannete jaoks otsustasime "teha nägu, et meil on monoliit" ja anda analüütikutele üks andmebaas, kus on kõik vajalikud andmed.

Oleme tõepoolest kaalunud erinevaid variante. Me oleksime võinud ehitada täieliku andmelao — me isegi proovime, aga ausalt öeldes ei õnnestunud meil piisavalt kiiresti muutuvaid loogikaid kohandada aeglase andmelao ehitamise ja seal muudatuste tegemisega (kui kellelgi on õnnestunud, kirjutage kommentaarides, kuidas). Me oleksime võinud öelda analüütikutele: "Poiss, õppige pythonit ja minge analüütilistele replikatele", aga see oleks tähendanud, et peame tegema lisavaate inimeste valikule, ja tundus, et seda võiks vältida, kui võimalik. Otsustasime proovida tehnoloogiat FDW (Foreign Data Wrapper): põhimõtteliselt on see standardne dblink, mis on olemas SQL standardis, kuid oma palju mugavama liidese kaudu. Selle alusel tõime välja lahenduse, mis lõpuks juurdus, ja sellele me jääme. Selle detailsed aspektid on eraldi artikli teema, võib-olla isegi mitte ühe, kuna on palju, millest rääkida: andmebaasi skeemide sünkroniseerimisest kuni juurdepääsu haldamise ja isikuandmete deanonüümimisega. Samuti tuleb märkida, et see lahendus ei asenda tegelikke analüütilisi andmebaase ja andmelaosid, see lahendab ainult kindla probleemi.

Üldiselt näeb see välja nii:

On PostgreSQL andmebaas, kus kasutajad saavad hoida oma tööandmeid, ja mis veelgi olulisem – sellele andmebaasile on FDW kaudu ühendatud kõik teenuste analüütilised koopiad. See võimaldab esitada päringut mitmele andmebaasile sõltumata sellest, kas need on PostgreSQL, MySQL, MongoDB või midagi muud (fail, API, kui sobivat wrapperit ei ole, saab kirjutada oma). Noh, tundub, et kõik on hästi! Kas me võime lahkuda?

Kui kõik lõppeks nii kiiresti ja lihtsalt, siis ilmselt poleks seda artiklit olemas.

On oluline selgelt mõista, kuidas PostgreSQL käsitleb päringuid kaugserverite suhtes. See tundub loogiline, kuid sageli ei pöörata sellele tähelepanu: PostgreSQL jagab päringu osadeks, mis täidetakse kaugserverites iseseisvalt, kogub need andmed ja lõpuks teeb lõplikud arvestused juba ise, seega sõltub päringu täitmise kiirus oluliselt sellest, kuidas see on kirjutatud. Samuti tuleks märkida: kui andmed tulevad kaugserverist, ei ole neil enam indekseid, pole midagi, mis aitaks planeerijal, seega saame aidata ja suunata teda ainult meie ise. Ja just sellest tahaksin lähemalt rääkida.

Lihtne päring ja plaan selle jaoks

Näitamaks, kuidas Postgres täidab päringut 6 miljoni reaga tabelile, serveril, uurime lihtsat plaani.

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 käskluse kasutamine võimaldab näha päringut, mis saadetakse kaugsüsteemile, ning tulemusi, mida me edasiseks töötlemiseks saame (venna RemoteSQL).

Vaatame veidi edasi ja lisame meie päringusse mõned filtrid: üks boolean välja põhjal, üks sissetoomise põhjal timestamp vahemikku ja üks 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

Just siin peitub hetk, millele tuleb pöörata tähelepanu päringute koostamisel. Filtrid ei edastatud kaugsõlmele, mis tähendab, et Postgres tõmbab välja kõik 6 miljonit rida, et seejärel kohapeal filtreerida (rida Filter) ja teostada agregatsiooni. Edu võti on koostada päring nii, et filtrid edastataks kaugsõlmele ning me saaksime ja agregiseeriksime vaid vajalikud read.

See on tõeliselt peene teema.

Boolean väliidega — kõik on lihtne. Algses päringus tekkis probleem operaatorist is. Kui asendada see =, siis saame järgmise tulemuse:

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

Nagu näete, filter viidi kaugserverisse ja täitmise aeg vähenes 27-lt 19 sekundile.

On oluline märkida, et operaator is erineb operaatorist = selle poolest, et suudab töötada Null väärtusega. See tähendab, et is not True filtris jäävad väärtused False ja Null, samas kui != True jätab alles ainult väärtused False. Seetõttu operaatori asendamisel is not filterisse tuleb edastada kaks tingimust OR operaatoriga, näiteks WHERE (col != True) OR (col is null).

Oleme boolean'iga selgeks saanud, liigume edasi. Samas viime filtri algsesse vormi, et eraldi vaadata, kuidas teised muutused mõjuvad.

timestamptz? hz

Tavaliselt tuleb katsetada, kuidas õigesti kirja panna päring, mis hõlmab kaugserve, ning seejärel otsida seletust, miks nii juhtub. Internetis on selle teema kohta väga vähe teavet. Eksperimentide käigus avastasime, et fikseeritud kuupäeva filter jõuab kaugsesse serverisse sujuvalt, aga kui tahame kuupäeva määrata dünaamiliselt, näiteks now() või CURRENT_DATE, siis see ei õnnestu. Meie näites lisasime filtri, et created_at veerg sisaldaks andmeid, mis on täpselt 1 kuu tagasi (BETWEEN CURRENT_DATE - INTERVAL '7 month' AND CURRENT_DATE - INTERVAL '6 month'). Mida me siis selle puhul ette võtsime?

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

Me soovisime, et planeerija arvutaks kuupäeva eelnevalt allpäringus ja edastaks juba valminud muutujat filtrisse. Ja see soovitus andis meile suurepärase tulemuse, päring kiirus tõusis peaaegu 6 korda!

Oluline on olla tähelepanelik: alamküsitluse andmetüüp peab olema sama, mis filtritava välja tüüp, vastasel juhul otsustab planeerija, et kuna tüübid on erinevad, tuleb esmalt andmed kätte saada ja seejärel kohalikult filtreerida.

Tagastame kuupäevafiltri algse väärtuse.

Freddy vs. Jsonb

Tegelikult on booli väljendid ja kuupäevad juba piisavalt kiirendanud meie päringut, kuid alles jäi üks andmetüüp. Ausalt öeldes pole võitlus selle filtrimisega siiani lõppenud, kuigi siin on ka edusamme. Nii et näete, kuidas õnnestus meil edastada filter jsonb välja eemalolevale serverile.

selgit analüüs üksikasjalikult
VALI count(1)
FROM fdw_schema.tabel 
KUS is_active on Tõene
JA created_dt ON BETWEEN CURRENT_DATE - INTERVAL '7 kuud' 
JA CURRENT_DATE - INTERVAL '6 kuud'
JA meta @> '{"source":"test"}'::jsonb;

Kogumine  (kulu=245463.60..245463.61 read=1 laius=8) (tõeline aeg=6727.589..6727.590 read=1 tsükleid=1)
  Väljund: count(1)
  ->  Välismaine skannimine fdw_schema."tabel"  (kulu=1100.00..245459.90 read=1478 laius=0) (tõeline aeg=16.213..6634.794 read=1360025 tsükleid=1)
        Väljund: "tabel".id, "tabel".is_active, "tabel".meta, "tabel".created_dt
        Filter: (("tabel".is_active ON TRUE) JA ("tabel".created_dt >= (('nüüd'::cstring)::kuupäev - '7 kuud'::intervall)) JA ("tabel".created_dt <= ((('nüüd'::cstring)::kuupäev)::aeg tsoonis - '6 kuud'::intervall)))
        Ridu eemaldatud Filtrist: 619961
        Kaug- SQL: VALI created_dt, is_active FROM fdw_schema.tabel KUS ((meta @> '{"source": "test"}'::jsonb))
Planeerimise aeg: 0.747 ms
Täidamise aeg: 6727.815 ms

Filtreerimise operaatorite asemel tuleb kasutada olemasolu operaatorit jsonb teises. 7 sekundit selle asemel, et algsed 29. Seni on see ainus edukas variant filtrite edastamiseks jsonb kaugserverile, kuid siin on oluline arvestada ühe piiranguga: kasutame versiooni 9.6, kuid plaanime aprilli lõpuks lõpetada viimased testid ja minna üle versioonile 12. Kui oleme uuendatud, kirjutame, kuidas see mõjutas, sest muudatusi, millele on suured lootused, on piisavalt palju: json_path, uus CTE käitumine, push down (olemas alates versioonist 10). Sooviksime seda võimalikult kiiresti proovida.

Finish him

Oleme kontrollinud, kuidas iga muudatus mõjutab päringu kiiruselt eraldi. Nüüd vaatame, mis juhtub, kui kõik kolm filtrit on õigesti kirjutatud.

selgit analüüs üksikasjalikult
VALI count(1)
FROM fdw_schema.tabel 
KUS is_active = Tõene
JA created_dt >= (VALI CURRENT_DATE::timestamptz - INTERVAL '7 kuud') 
JA created_dt <(VALI CURRENT_DATE::timestamptz - INTERVAL '6 kuud')
JA meta @> '{"source":"test"}'::jsonb;

Kogumine  (kulu=322041.51..322041.52 read=1 laius=8) (tõeline aeg=2278.867..2278.867 read=1 tsükleid=1)
  Väljund: count(1)
  InitPlan 1 (tagastab $0)
    ->  Tulem  (kulu=0.00..0.02 read=1 laius=8) (tõeline aeg=0.010..0.010 read=1 tsükleid=1)
          Väljund: ((('nüüd'::cstring)::kuupäev)::aeg tsoonis - '7 kuud'::intervall)
  InitPlan 2 (tagastab $1)
    ->  Tulem  (kulu=0.00..0.02 read=1 laius=8) (tõeline aeg=0.003..0.003 read=1 tsükleid=1)
          Väljund: ((('nüüd'::cstring)::kuupäev)::aeg tsoonis - '6 kuud'::intervall)
  ->  Välismaine skannimine fdw_schema."tabel"  (kulu=100.02..322041.41 read=25 laius=0) (tõeline aeg=8.597..2153.809 read=1360025 tsükleid=1)
        Väljund: "tabel".id, "tabel".is_active, "tabel".meta, "tabel".created_dt
        Kaug- SQL: VALI NULL FROM fdw_schema.tabel KUS (is_active) JA ((created_dt >= $1::aeg tsoonis)) JA ((created_dt < $2::aeg tsoonis)) JA ((meta @> '{"source": "test"}'::jsonb))
Planeerimise aeg: 0.820 ms
Täidamise aeg: 2279.087 ms

Jah, päring näeb keerulisem välja, see on sunnitud hind, kuid täitmise kiirus on 2 sekundit, mis on rohkem kui 10 korda kiirem! Ja me räägime siin lihtsast päringust suhteliselt väikese andmestiku kohta. Reaalsetes päringutes oleme saavutanud kasvu kuni mitme sadu kordi.

Kokkuvõtteks: kui kasutate PostgreSQL-i koos FDW-ga, kontrollige alati, kas kõik filtrid saadetakse kaugserverisse, ja see toob teile õnne… vähemalt kuni olete jõudnud erinevate tabelite vaheliste join’ideni. serverite. Kuid see on juba lugu veel ühe artikli jaoks.

Aitäh tähelepanu eest! Ootan küsimusi, kommentaare ja lugusid teie kogemustest kommentaarides.

Allikas: habr.com