Mikroserva arkitekturo, kiel ĉio en ĉi tiu mondo, havas siajn avantaĝojn kaj malavantaĝojn. Iuj procezoj fariĝas pli facilaj kun ĝi, aliaj pli malfacilaj. Kaj pro rapideco de ŝanĝo kaj pli bona skaleblo, vi devas fari oferojn. Unu el ili estas la kreskanta komplekseco de analizo. Se en monolito ĉiuj operaciaj analizoj povas esti reduktitaj al SQL-demandoj al analiza kopio, tiam en plurserva arkitekturo ĉiu servo havas sian propran datumbazon kaj ŝajnas ke unu konsulto ne povas esti farita (aŭ eble ĝi povas?). Por tiuj, kiuj interesiĝas pri kiel ni solvis la problemon de operacia analizo en nia kompanio kaj kiel ni lernis vivi kun ĉi tiu solvo - bonvenon.

Mia nomo estas Pavel Sivash, ĉe DomClick mi laboras en teamo, kiu respondecas pri konservado de la analiza datuma stokejo. Konvencie, niaj agadoj povas esti klasifikitaj kiel datuma inĝenierado, sed, fakte, la gamo de taskoj estas multe pli larĝa. Estas ETL/ELT normo por datuma inĝenierado, subteno kaj adapto de iloj por datuma analizo kaj disvolviĝo de viaj propraj iloj. Precipe, por operacia raportado, ni decidis "ŝajnigi", ke ni havas monoliton kaj doni al analizistoj unu datumbazon, kiu enhavos ĉiujn datumojn, kiujn ili bezonas.

Ĝenerale, ni konsideris malsamajn eblojn. Eblis konstrui plenrajtan deponejon - ni eĉ provis, sed, sincere, ni ne povis kombini sufiĉe oftajn ŝanĝojn en logiko kun la sufiĉe malrapida procezo konstrui deponejon kaj fari ŝanĝojn al ĝi (se iu sukcesis. , skribu en la komentoj kiel). Eblis diri al la analizistoj: "Knaboj, lernu python kaj iru al analizaj kopioj", sed ĉi tio estas aldona postulo por varbado, kaj ŝajnis, ke tio devus esti evitita se eble. Ni decidis provi uzi FDW (Foreign Data Wrapper) teknologion: esence, ĉi tio estas norma dblink, kiu estas en la SQL-normo, sed kun sia propra multe pli oportuna interfaco. Surbaze de ĝi, ni faris solvon, kiu finfine atingis, kaj ni decidis por ĝi. Ĝiaj detaloj estas la temo de aparta artikolo, kaj eble pli ol unu, ĉar mi volas paroli multon: de sinkronigado de datumbazaj skemoj ĝis alirkontrolo kaj malpersonigo de personaj datumoj. Ankaŭ necesas rezervi, ke ĉi tiu solvo ne anstataŭas realajn analizajn datumbazojn kaj deponejojn; ĝi solvas nur specifan problemon.

Ĉe la supera nivelo ĝi aspektas jene:

Estas PostgreSQL-datumbazo kie uzantoj povas stoki siajn laborajn datumojn, kaj plej grave, analizaj kopioj de ĉiuj servoj estas konektitaj al ĉi tiu datumbazo per FDW. Ĉi tio ebligas skribi konsulton al pluraj datumbazoj, kaj ne gravas kio ĝi estas: PostgreSQL, MySQL, MongoDB aŭ io alia (dosiero, API, se subite mankas taŭga envolvaĵo, vi povas skribi vian propran). Nu, ĉio ŝajnas bonega! Ĉu ni disiĝas?

Se ĉio finiĝis tiel rapide kaj simple, tiam, verŝajne, ne estus artikolo.

Gravas esti klare pri kiel Postgres procesas petojn al foraj serviloj. Ĉi tio ŝajnas logika, sed ofte homoj ne atentas ĝin: Postgres dividas la peton en partojn, kiuj estas ekzekutitaj sendepende sur foraj serviloj, kolektas ĉi tiujn datumojn kaj mem faras la finajn kalkulojn, do la rapideco de demanda ekzekuto multe dependos de kiel ĝi estas skribita. Oni ankaŭ rimarku: kiam la datumoj alvenas de fora servilo, ĝi ne plu havas indeksojn, estas nenio, kio helpos la planiston, do nur ni mem povas helpi kaj konsili lin. Kaj ĝuste pri tio mi volas paroli pli detale.

Simpla demando kaj plano kun ĝi

Por montri kiel Postgres demandas 6 milionojn da vicotabelo sur fora servilo, ni rigardu simplan planon.

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

Uzado de la deklaro VERBOSE permesas al ni vidi la demandon, kiu estos sendita al la fora servilo kaj kies rezultojn ni ricevos por plua prilaborado (RemoteSQL-linio).

Ni iru iom plu kaj aldonu plurajn filtrilojn al nia peto: unu por bulea kampo, unu per okazo tempstampo en la intervalo kaj unu per 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

Ĉi tie kuŝas la punkto, pri kiu vi devas atenti dum skribado de demandoj. La filtriloj ne estis transdonitaj al la fora servilo, kio signifas, ke por ekzekuti ĝin, Postgres eltiras ĉiujn 6 milionojn da vicoj por tiam filtri loke (Filtrilo-vicon) kaj fari agregadon. La ŝlosilo al sukceso estas skribi demandon, por ke la filtriloj estas translokigitaj al la fora maŝino, kaj ni ricevas kaj agregas nur la necesajn vicojn.

Tio estas ia buleano

Kun buleaj kampoj ĉio estas simpla. En la originala peto, la problemo estis pro la funkciigisto is. Se vi anstataŭigas ĝin per =, tiam ni ricevas la sekvan rezulton:

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

Kiel vi povas vidi, la filtrilo flugis al fora servilo, kaj la ekzekuttempo estis reduktita de 27 ĝis 19 sekundoj.

Indas noti, ke la telefonisto is malsama de funkciigisto = ĉar ĝi povas funkcii kun la Nula valoro. Ĝi signifas tion ne estas Vera lasos la valorojn False kaj Null en la filtrilo, dum != Vero lasos nur Falsajn valorojn. Sekve, kiam oni anstataŭigas la funkciigiston ne estas du kondiĉoj kun la OR-funkciigisto devus esti pasitaj al la filtrilo, ekzemple, KIE (kol != Vera) AŬ (kol estas nula).

Ni traktis bulean, ni pluiru. Nuntempe, ni redonu la Bulea filtrilon al ĝia originala formo por sendepende konsideri la efikon de aliaj ŝanĝoj.

timestamptz? hz

Ĝenerale, vi ofte devas eksperimenti pri kiel ĝuste skribi peton, kiu implikas forajn servilojn, kaj nur tiam serĉi klarigon pri kial tio okazas. Tre malmulte da informoj pri tio troviĝas en la Interreto. Do, en eksperimentoj ni trovis, ke fiksa dato-filtrilo flugas al la fora servilo kun bato, sed kiam ni volas agordi la daton dinamike, ekzemple, nun() aŭ CURRENT_DATE, tio ne okazas. En nia ekzemplo, ni aldonis filtrilon tiel ke la kolumno kreita_je enhavis datumojn por ekzakte 1 monato en la pasinteco (INTER KURRENT_DATE - INTERVAL '7 month' AND CURRENT_DATE - INTERVAL '6 month'). Kion ni faris en ĉi tiu kazo?

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

Ni diris al la planisto anticipe kalkuli la daton en la subdemando kaj transdoni la pretan variablon al la filtrilo. Kaj ĉi tiu sugesto donis al ni bonegan rezulton, la peto fariĝis preskaŭ 6 fojojn pli rapida!

Denove, gravas atenti ĉi tie: la datumtipo en la subdemando devas esti la sama kiel tiu de la kampo sur kiu ni filtras, alie la planisto decidos, ke ĉar la tipoj estas malsamaj, necesas unue ricevi ĉiujn. la datumojn kaj filtri ĝin loke.

Ni redonu la datfiltrilon al sia origina valoro.

Freddy vs. Jsonb

Ĝenerale, Buleaj kampoj kaj datoj jam sufiĉe rapidigis nian demandon, sed restis unu plia datumtipo. La batalo kun filtrado per ĝi, sincere, ankoraŭ ne finiĝis, kvankam ankaŭ ĉi tie estas sukceso. Do, jen kiel ni sukcesis preterpasi la filtrilon jsonb kampo al la fora servilo.

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

Anstataŭ filtri operatorojn, vi devas uzi la ĉeeston de unu operatoro jsonb en malsama. 7 sekundoj anstataŭ la originalaj 29. Ĝis nun ĉi tiu estas la nura sukcesa opcio por transdoni filtrilojn per jsonb al fora servilo, sed ĉi tie gravas konsideri unu limigon: ni uzas la version 9.6 de la datumbazo, sed antaŭ la fino de aprilo ni planas plenumi la lastajn testojn kaj transiri al la versio 12. Post kiam ni ĝisdatigos, ni skribos pri kiel ĝi influis, ĉar estas sufiĉe multaj ŝanĝoj por kiuj estas multe da espero: json_path, nova CTE-konduto, push down (ekzistanta ekde la versio 10). Mi vere volas provi ĝin baldaŭ.

Finu lin

Ni testis kiel ĉiu ŝanĝo influis petan rapidon individue. Ni nun vidu, kio okazas kiam ĉiuj tri filtriloj estas ĝuste skribitaj.

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

Jes, la peto aspektas pli komplika, ĉi tio estas devigita kotizo, sed la ekzekutrapideco estas 2 sekundoj, kio estas pli ol 10 fojojn pli rapida! Kaj ni parolas pri simpla demando kontraŭ relative malgranda datumaro. Laŭ veraj petoj, ni ricevis pliiĝon ĝis kelkcent fojojn.

Resume: se vi uzas PostgreSQL kun FDW, ĉiam kontrolu, ke ĉiuj filtriloj estas senditaj al la fora servilo, kaj vi estos feliĉa... Almenaŭ ĝis vi atingos kuniĝojn inter tabeloj de malsamaj serviloj. Sed tio estas rakonto por alia artikolo.

Dankon pro via atento! Mi ŝatus aŭdi demandojn, komentojn kaj rakontojn pri viaj spertoj en la komentoj.

fonto: www.habr.com