Mikroteenuste arhitektuur, nagu kõik selles maailmas, omab oma plusse ja miinuseid. Mõnel juhul lihtsustavad protsessid seda, teistel juhtudel aga keerulisemaks. Muudatuste kiirus ja parem skaleeritavus nõuavad teatud ohvrid tooma. Üks neist on analüütika keerukus. Kui monoliidis saab kogu operatiivse analüütika vähendada SQL-päringuteks analüütilisele koopia jaoks, siis mitmete teenuste arhitektuuris on igal teenusel oma andmebaas ja tundub, et ühe päringuga ei piisa (aga võib-olla siiski piisab?). Neile, keda huvitab, kuidas me lahendasime operatiivse analüütika probleemi meie ettevõttes ja kuidas me õppisime selle lahendusega elama - olete teretulnud.

Minu nimi on Pavel Sivaš, töötan DomKlikis meeskonnas, mis vastutab analüütilise andmehoidla hooldamise eest. Tinglikult saab meie tegevuse klassifitseerida andmeinseneri valdkonda, kuid tõeliselt on ülesannete spekter palju laiem. Olemas on standardsetele andmeinseneri ülesannetele iseloomulikud ETL/ELT, andmeanalüüsi tööriistade toetamine ja kohandamine ning oma tööriistade arendamine. Eelkõige otsustasime jooksva aruandluse jaoks „teeskleda”, et meil on monoliit ja anda analüütikutele üks andmebaas, kus on kõik vajalikud andmed.

Meie oleme kaalunud erinevaid variante. Täieliku andmehoidla ehitamine oleks olnud võimalik — me isegi proovisime, kuid ausalt öeldes ei õnnestunud meil piisavalt sageli muutuva loogika ühitamine piisavalt aeglase andmehoidla ehitamise ja muutmise protsessiga (kui kellelgi on see õnnestunud, kirjutage kommentaaridesse, kuidas). Me oleksime võinud analüütikutele öelda: „Poisid, õppige pythonit ja kasutage analüütilisi replikaate,” kuid see oleks olnud lisataotlus töötajate valikule ja tundus, et seda oleks parem vältida, kui võimalik. Otsustasime proovida kasutada FDW (Foreign Data Wrapper) tehnoloogiat: tegelikult on see standardne dblink, mis on SQL standardis, kuid oma palju mugavama liidese kaudu. Selle põhjal lõime lahenduse, mis lõpuks ka kinnistus ja millele me jääme. Selle üksikasjad on eraldi artikli teema, võib-olla isegi mitte ühe, kuna tahaks rääkida paljust: alates andmebaaside skeemide sünkroniseerimisest kuni juurdepääsu haldamise ja isikuandmete anonüümimiseni. Peame samuti märkima, et see lahendus ei asenda tegelikke analüütilisi andmebaase ja hoidlasi, vaid lahendab vaid konkreetse ülesande.

Üldiselt näeb see välja nii:

On PostgreSQL andmebaas, kus kasutajad saavad hoida oma tööandmeid ja kõige olulisem on see, et sellele andmebaasile on FDW kaudu ühendatud kõigi teenuste analüütilised replikad. See võimaldab esitada päringu mitmele andmebaasile, olgu need siis PostgreSQL, MySQL, MongoDB või miski muu (fail, API, kui sobivat wrapperit ei ole, võib kirjutada oma). Noh, kõlab hästi, eks? Lõpetame siin?

Kui kõik lõppeks nii kiiresti ja lihtsalt, siis ilmselt ei oleks ka artiklit.

On oluline selgelt mõista, kuidas PostgreSQL töötleb päringuid kaugserveritele. 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õplikud arvutused teeb juba ise, seega sõltub päringu täitmise kiirus oluliselt sellest, kuidas see on kirjutatud. Tuleb samuti märkida: kui andmed saabuvad kaugserverist, siis neil pole enam indekseid, pole midagi, mis aitaks planeerijal, seega saame me ise vaid aidata ja juhendada teda. Ja just sellest tahakski rohkem rääkida.

Lihtne päring ja plaan selle jaoks

Et näidata, kuidas PostgreSQL täidab päringu 6 miljoni rea tabelile kaugserveris, serveris, vaatame 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äsk kasutamine võimaldab näha päringut, mis saadetakse kaugserverisse, ja tulemusi, mida saame edasiseks töötlemiseks (rida RemoteSQL).

Liigume natuke edasi ja lisame meie päringule mõned filterd: üks järgi boolean välja, üks sisendi järgi timestamp ajavahemikus ja üks järgi 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 oluline punkt, millele tuleks tähelepanu pöörata päringute kirjutamisel. Filterid ei edastunud kaugserverisse, mis tähendab, et PostgreSQL tõmbab kõik 6 miljonit rida, et seejärel kohalikul tasandil filtreerida (rida Filter) ja teostada agregatsiooni. Edu võti on kirjutada päring nii, et filterid edastatakse kaugmasinale ja saame ja agreggeerida ainult vajalikud read.

See on mõnes mõttes hämardus

Boolean väljadega — kõik on lihtne. Algse päringu probleem tekkis operaatori tõttu is. Kui asendada see =, saame järgmise tulemuse:

selgitada analüüsige üksikasjalikult
SELECT count(1)
FROM fdw_schema.table
WHERE is_active = True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 months'
AND CURRENT_DATE - INTERVAL '6 months'
AND meta->>'source' = 'test';

Kogus (kulu=508010.14..508010.15 read=1 laius=8) (reaalne aeg=19064.314..19064.314 read=1 silmus=1)
  Sisend: count(1)
  ->  Välismaine kontroll fdw_schema."table"  (kulu=100.00..507988.44 read=8679 laius=0) (reaalne aeg=33.035..18951.278 read=1360025 silmus=1)
        Väljund: "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 months'::interval)) AND ("table".created_dt <= ((('now'::cstring)::date)::timestamp with time zone - '6 months'::interval)))
        Filteri tõttu eemaldatud read: 3567989
        Kaug SQL: SELECT created_dt, meta FROM fdw_schema.table WHERE (is_active)
Planeerimise aeg: 0.834 ms
Täitmise aeg: 19064.534 ms

Nagu näete, viidi filter kaugarvutisse ja täitmise aeg vähenes 27 sekundilt 19 sekundile.

On oluline märkida, et operaator is erineb operaatorist = sellega, et see oskab töötada Null väärtusega. See tähendab, et is not True filtris jätab väärtused False ja Null, samas kui != True jätab alles ainult väärtused False. Seetõttu, kui vahetate operaatorit is not tuleb filtri jaoks edastada kaks tingimust operaatoriga OR, näiteks: WHERE (col != True) OR (col is null).

Booleani oleme selgeks teinud, liigume edasi. Ja enne kui läheme edasi, taastame filter booleseks väärtuseks algsesse seisundisse, et eraldi uurida teiste muudatuste mõju.

timestamptz? hz

Tegelikult tuleb tihti katsetada, kuidas õigesti kirjutada päringut, milles osalevad kaugarvutid, ja seejärel otsida seletust, miks asjad just nii toimuvad. Internetist on selle kohta väga vähe teavet saada. Katsetustes avastasime, et fikseeritud kuupäeva filter läheb kaugarvutisse probleemideta, kuid kui soovime määrata kuupäeva dünaamiliselt, näiteks now() või CURRENT_DATE, ei juhtu seda. Meie näites lisasime sellise filtri, et veerg created_at sisaldaks andmeid täpselt 1 kuu tagasi (BETWEEN CURRENT_DATE - INTERVAL '7 months' AND CURRENT_DATE - INTERVAL '6 months'). Mida me sel juhul tegime?

selgitamine analüüs verboselt
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt >= (SELECT CURRENT_DATE::timestamptz - INTERVAL '7 kuu') 
AND created_dt >'source' = 'test';

Kogus  (kulu=306875.17..306875.18 read=1 laius=8) (reaalne aeg=4789.114..4789.115 read=1 tsüklis=1)
  Väljund: count(1)
  InitPlan 1 (tagastab $0)
    ->  Tulem  (kulu=0.00..0.02 read=1 laius=8) (reaalne aeg=0.007..0.008 read=1 tsüklis=1)
          Väljund: ((('nüüd'::cstring)::kuupäev)::töötlemisaeg koos ajavööndiga - '7 kuud'::interval)
  InitPlan 2 (tagastab $1)
    ->  Tulem  (kulu=0.00..0.02 read=1 laius=8) (reaalne aeg=0.002..0.002 read=1 tsüklis=1)
          Väljund: ((('nüüd'::cstring)::kuupäev)::töötlemisaeg koos ajavööndiga - '6 kuud'::interval)
  ->  Võõrsilt skaneerimine fdw_schema."table"  (kulu=100.02..306874.86 read=105 laius=0) (reaalne aeg=23.475..4681.419 read=1360025 tsüklis=1)
        Väljund: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND (("table".meta->>'source'::text) = 'test'::text))
        Ridu eemaldatud filtri tõttu: 76934
        Kaugarvuti SQL: SELECT is_active, meta FROM fdw_schema.table WHERE ((created_dt >= $1::töötlemisaeg koos ajavööndiga)) AND ((created_dt < $2::töötlemisaeg koos ajavööndiga))
Planeerimise aeg: 0.703 ms
Täideviimise aeg: 4789.379 ms

Me palusime planeerijal eelnevalt arvutada kuupäeva allküsimuses ja edastada juba valmisse viidatud muutuja filtrisse. Ja see soovitus andis meile suurepärase tulemuse, päring kiirus kasvas peaaegu 6 korda!

Taaskord on siin oluline olla tähelepanelik: allküsimuse andmetüüp peab olema sama, mis filtritav väljal, vastasel juhul otsustab planeerija, et kuna tüübid on erinevad, tuleb kõigepealt hankida kõik andmed ja alles seejärel need kohaliku filtri järgi sorteerida.

Tagastame kuupäeva filtri algse väärtuse.

Freddy vs. Jsonb

Kokkuvõttes on boolean väljad ja kuupäevad juba piisavalt kiirendanud meie päringut, kuid üks andmetüüpidest jäi veel alles. Ausalt öeldes pole meil vaatamata edusammudele filtri osas edasiminek lõpetatud. Nii et siin on, kuidas me suutsime filtri edastada jsonb välja kaugserverisse.

selgitamine analüüs verboselt
SELECT count(1)
FROM fdw_schema.table 
WHERE is_active is True
AND created_dt BETWEEN CURRENT_DATE - INTERVAL '7 kuu' 
AND CURRENT_DATE - INTERVAL '6 kuu'
AND meta @>'{"source":"test"}'::jsonb;

Kogus  (kulu=245463.60..245463.61 read=1 laius=8) (reaalne aeg=6727.589..6727.590 read=1 tsüklis=1)
  Väljund: count(1)
  ->  Võõrsilt skaneerimine fdw_schema."table"  (kulu=1100.00..245459.90 read=1478 laius=0) (reaalne aeg=16.213..6634.794 read=1360025 tsüklis=1)
        Väljund: "table".id, "table".is_active, "table".meta, "table".created_dt
        Filter: (("table".is_active IS TRUE) AND ("table".created_dt >= (('nüüd'::cstring)::kuupäev - '7 kuud'::interval)) AND ("table".created_dt '{"source": "test"}'::jsonb))
Planeerimise aeg: 0.747 ms
Täideviimise aeg: 6727.815 ms

Filtreerimise operaatorite asemel tuleb kasutada ühte sisaldumise operaatorit jsonb teises. 7 sekundit asemel algset 29. Praegu on see ainus edukas meetod filtrite edastamiseks jsonb kaugserverisse, kuid siin on oluline arvestada ühte piirangut: me kasutame andmebaasi versiooni 9.6, kuid plaanime aprilli lõpuks lõpetada viimased testid ja üle minna versioonile 12. Kui me uuendame, kirjutame, kuidas see mõjutas, sest muutusi, millele loodetakse, on palju: json_path, uued CTE käitumised, push down (mis on olemas alates versioonist 10). Tahaksime seda võimalikult kiiresti proovida.

Lõpeta ta

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

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 ms

Jah, päring näeb keerulisem välja, see on sunnitud hind, kuid täitmise kiirus on 2 sekundit, mis on üle 10 korra kiirem! Ja me räägime lihtsast päringust suhteliselt väikeses andmekogus. Reaalsetes päringutes oleme näinud kasvu kuni mitme sadadeni.

Teeme kokkuvõtte: kui kasutate PostgreSQL-i koos FDW-ga, kontrollige alati, kas kõik filtrid saadetakse kaugserverisse, ja teil on õnne... vähemalt kuni jõuate erinevate tabelite vaheliste JOIN-ide juurde. serverid. Kuid see on juba lugu veel ühe artikli jaoks.

Aitäh tähelepanu eest! Oleksin tänulik küsimuste, kommentaaride ja ka teie kogemuste lugude eest kommentaarides.

Allikas: habr.com