DBA: organizojmë në mënyrë të duhur sinkronizimet dhe importet

Kur bëhet fjalë për përpunimin kompleks të grupeve të mëdha të dhënash (të ndryshme proceset ETL: importime, konvertime dhe sinkronizime me burime të jashtme) shpesh lind nevoja për të "mbajtur mend" përkohësisht dhe për t'i përpunuar shpejt diçka voluminoze.

Një detyrë tipike e këtij lloji zakonisht formulon si më poshtë: "Këtu financial po eksporton nga banka e klientit pagesat më të fundit të pranuara, duhet t'i ngarkojmë shpejt në faqe dhe t'i lidhim me llogaritë"

Por kur volumi i këtij "diçkaje" fillon të matet në qindra megabajt, dhe shërbimi duhet të vazhdojë të punojë me bazën si në modin 24x7, lindin shumë efekte anësore, të cilat do t'ju prishin jetën.

Për t'u përballur me ta në PostgreSQL (po ashtu edhe në të tjerët), mund të përdoren disa mundësi për optimizim, të cilat do të lejojnë të përpunoni gjithçka më shpejt dhe me më pak burime.

1. Ku të ngarkojmë?

Së pari, le të përcaktojmë se ku mund të ngarkojmë të dhënat që dëshirojmë "të procesojmë".

1.1. Tabela përkohshme (TEMPORARY TABLE)

Në princip, për PostgreSQL, tabelat e përkohshme janë si çdo tabelë tjetër. Prandaj, superstitions si "ato ruhen vetëm në memorie, dhe ajo mund të mbarojë"janë të pasakta. Po ashtu, ka disa dallime të rëndësishme.

Një "hapësirë emri" për çdo lidhje në BDB

Nëse dy lidhje përpiqen njëkohësisht të kryejnë KRIJO TABELA x, atëherë dikush patjetër do të marrë një gabim unikaliteti të objekteve të BDB.

Por nëse të dy përpiqen të kryejnë KRIJO TEMPORARY TABELA x, atëherë të dy e realizojnë normalisht, dhe secili merr ekzemplarin e vet tabelës. Dhe nuk ka asgjë të përbashkët midis tyre.

"Vetë-shkatërrimi" kur u ndale lidhja

Kur lidhja mbyllet, të gjitha tabelat përkohshme fshihen automatikisht, prandaj nuk ka asnjë kuptim të bëni DROP TABLE x përveç…

Nëse punoni përmes pgbouncer në modin e transaksionit, atëherë baza vazhdon të mendojë se kjo lidhje është ende aktive, dhe tabela përkohshme ende ekziston në të.

Prandaj, përpjekja për ta krijuar përsëri, tashmë nga një lidhje tjetër në pgbouncer, do të çojë në një gabim. Por kjo mund të zgjidhet duke përdorur KRIJONI TABELË TEMPORALE NËSE NUK EKZISTON x.

Megjithatë, është më mirë ta bëni kështu, sepse mund të "zbuloni papritur" ato të dhëna të mbetura nga "pronari i mëparshëm". Në vend të kësaj, është shumë më mirë të lexoni përgjithësisht manualin, dhe të shikoni se gjatë krijimit të tabelës ka mundësinë të shtoni PËR ANGAZHIM DROP — pra, në përfundim të transaksionit, tabela do të fshihet automatikisht.

Jo-replikimi

Për shkak të përkatësisë vetëm ndaj një lidhjeje të caktuar, tabelat përkohshme nuk replikohen. Për më tepër, kjo ndihmon për të shmangur nevojën për shkrim të dyfishtë të të dhënave në heap + WAL, kështu që INSERT/UPDATE/DELETE në to është shumë më i shpejtë.

Por për shkak se tabelat e përkohshme janë përfundimisht "dhe" tabelat "normale", ato nuk mund të krijohen as në replikë gjithashtu. Të paktën për momentin, megjithëse një patch përkatës qarkullon prej një kohe të gjatë.

1.2. Tabela të pa-journal (UNLOGGED TABLE)

Por, çfarë duhet të bëni, për shembull, nëse keni një proces ETL të rëndë, që nuk mund ta realizoni brenda një transaksioni, dhe ju keni pgbouncer në modin e transaksionit?..

Ose fluksi i të dhënave është aq i madh sa kapaciteti i një lidhje me BDB (lexo, një proces në CPU) nuk është i mjaftueshëm? ..

Ose disa operacione shkojnë asinhron në lidhje të ndryshme?..

Në këtë raste, një mundësi e vetme mbetet — të krijoni përkohësisht një tabelë jo-përkohëshe.Një ironia, po. Domethënë,

• krijoni "tabelat" tuaja me emra sa më rastësorë dhe unikë, për të mos u ndërprerë
• Ekstrakt: ngarko në to të dhëna nga burimi i jashtëm
• Transformo: transformuat, plotësuan fushat kyçe lidhëse
• Ngarko: transferuan të dhënat e gatshme në tabelat përkatëse
• fshini "tabelat" tuaja

Dhe tani — një lugë helm. Në thelb, i gjithë shkrimi në PostgreSQL ndodh dy herë — së pari në WAL, pastaj në trupat e tabelave/indekseve. Të gjitha këto janë bërë për të mbështetur ACID dhe për të siguruar dukshmërinë e saktë të të dhënave midis COMMIT'brendshme dhe RIVENDOS'brendshme transaksionet.

Por ne nuk e duam këtë! Të gjithë procesi ose ka kaluar me sukses, ose jo.Nuk ka rëndësi se sa transaksione ndërmjetësore do të ketë — nuk na intereson "të vazhdojmë procesin nga mesi", sidomos kur nuk është e qartë ku ishte.

Për këtë, zhvilluesit e PostgreSQL që në versionin 9.1 zbatuan një gjë si tabela të pa-journal (UNLOGGED):

Me këtë përcaktim, tabela krijohet si e pa-journal. Të dhënat e shkruara në tabelat e pa-journal nuk kalojnë përmes regjistrit të parashkrimit (shih Kapitullin 29), si rezultat i së cilës këto tabela punojnë shumë më shpejt se të zakonshmet.Megjithatë, ato nuk janë të mbrojtura nga dështimi; në rast dështimi ose ndalimi të papritur të serverit, tabela e pa-journal automatikisht pritet.Për më tepër, përmbajtja e tabelës së pa-journal nuk replikon në serverë të drejtpërdrejtë. Çdo indeks i krijuar për një tabelë të pasiguruar automatikisht bëhet i tillë.

Për të shpejtuar, do të jetë shumë më shpejt, por nëse serveri i të dhënave "bjerë" — do të jetë e pakëndshme. Por a ndodh shpesh kjo, dhe a arrin procesi juaj ETL ta përmirësojë "nga mesi" pas "ringjalljes" së DB?..

Nëse jo, dhe rasti lart është i ngjashëm me tuajin — përdorni UNLOGGED, por kurrë mos e aktivizoni këtë atribut në tabelat reale, të dhënat nga të cilat ju rëndojnë.

1.3. ON COMMIT { DELETE ROWS | DROP }

Kjo ndërtim lejon që gjatë krijimit të tabelës të caktosh sjelljen automatike pas përfundimit të transaksionit.

Për PËR ANGAZHIM DROP e kam shkruar më parë, ai gjeneron DROP TABLE, por me PËR ANGAZHIM Fshi Rreshtat situata është më interesante — këtu gjenerohet TRUNCATE TABLE.

Nëse e gjithë infrastruktura e ruajtjes së meta përshkrimit të tabelës përkohshme është pikërisht e njëjtë si ajo e normales, atëherë krijimi dhe fshirja e vazhdueshme e tabelave të përkohshme çon në "shkëmbim" të madh të tabelave sistemike pg_class, pg_attribute, pg_attrdef, pg_depend,…

Tani imagjinoni se keni një punëtor që ka një lidhje të drejtpërdrejtë me DB, i cili çdo sekondë hap një transaksion të ri, krijon, mbush, përpunon dhe fshin një tabelë të përkohshme… Do të akumulohet tepri në tabelat sistemike, dhe kjo do të shkaktojë ngadalësime në çdo operacion.

Në përgjithësi, mos e bëni kështu! Në këtë rast, është shumë më efektive CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS ta nxjerrësh jashtë ciklit të transaksioneve — atëherë në fillim të çdo transaksioni të ri, tabelat do të ekzistojnë (shkurtim i thirrjes KRIJO), por do të jenë bosh, falë TRUNCATE (thirrjen e tij gjithashtu e kursyem) pas përfundimit të transaksionit të mëparshëm.

1.4. LIKE… INCLUDING …

E përmenda në fillim, se një nga rastet tipike për tabelat e përkohshme është lloje të ndryshme importesh — dhe zhvilluesi ngre duar e kopjon listën e fushave të tabelës qëllimore për deklaratën e tabelës së tij të përkohshme…

Por lenia — është motor progresi! Prandaj krijimi i një tabele të re "në bazë të modelit" mund të bëhet shumë më lehtë:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Duke pasur parasysh se në këtë tabelë mund të gjenerohen shumë të dhëna, kërkimet në të do të jenë aspak të shpejta. Por për këtë ka një zgjidhje tradicionale — indikes! Po, po ashtu, tabela e përkohshme mund të ketë indikes.

Duke qenë se, shpesh, indiket e nevojshme përputhen me indiket e tabelës së qëllimit, mund të shkruani thjesht SI target_table DUKE INDICES.

Nëse ju nevojiten edhe DEFAULT-vlerat (për shembuj, për të mbushur vlerat e çelësit primar), mund të përdorni SI target_table PËRFSHIRË DHE TË DHËNAT E KUFIZUARA. Ose thjesht — SI target_table PËRFSHIJË GJITHA — do të kopjojë defoltet, indiket, kufizimet,…

Por në këtë rast, duhet të kuptohet se nëse e keni krijuar tabelën e importit menjëherë me indikes, të dhënat do të futen më ngadalë, sesa nëse fillimisht i futni të gjitha, dhe më pas i aplikoni indiket — shikoni për të mësuar se si e bën pg_dump.

Në përgjithësi, RTFM!

2. Si të shkruani?

Do ta them thjesht — përdorni COPY-rrjedhën në vend të "kufizëve" INSERT, përshpejtim me përqindje. Mund të bëhet edhe direkt nga një skedar i formuar paraprakisht.

3. Si të përpunoni?

Pra, le të thjeshtojmë se këtu kemi një hyrje të ngjashme:

• keni një tabelë në bazë të të dhënave me 1M regjistrime
• çdo ditë klienti ju dërgon një të plotë "model"
• sipër përvojës, e dini se nga një herë në tjetër ndryshojnë jo më shumë se 10K regjistrime

Një shembull klasik i një situate të tillë është baza KLA-DR — gjithsej adresa të shumta, por në çdo përpunim të përjavshëm të ndryshimeve (ndryshimet e emrave të vendeve, bashkimeve të rrugëve, shfaqja e shtëpive të reja) ka shumë pak madje edhe në shkallë të gjatë.

3.1. Algoritmi i sinkronizimit të plotë

Për thjeshtësi, le të themi se nuk është e nevojshme ta rishtrosh të dhënat — thjesht t'i sillni tabelës pamjen e duhur, dmth:

• të fshijë gjithë ato që nuk janë më
• përditësoni gjithë ato që tashmë ishin dhe nevojiten për përditësim
• futni gjithë ato që akoma nuk ishin

Pse pikërisht në këtë rend do të duhej të bënit operacionet? Sepse kështu do të rritet sa më pak madhësia e tabelës (mbani mend për MVCC!).

DELETE FROM dst

Jo, natyrisht mund ta përdorni vetëm dy operacione:

• të fshijë (DELETE) të gjithë
• futni të gjithë nga modeli i ri

Por ndodhi që falë MVCC, madhësia e tabelës do të rritet pikërisht dyfish! Të merrni +1M modele regjistrimesh në tabelë për shkak të përditësimit të 10K — nuk është tërheqja më e madhe…

TRUNCATE dst

Një zhvillues më me përvojë e di se është mjaft e lirë të fshini tërë tabelën:

• pastroni (TRUNCATE) tabelën e tërë
• futni të gjithë nga modeli i ri

Metoda është efektive, ndonjëherë plotësisht e zbatueshme, por ka një shqetësim… Të futni 1M regjistrime do të zgjasë shumë, prandaj nuk mund ta lejojmë tabelën të jetë bosh për gjithë këtë kohë (siç do ndodhte pa u vendosur në një transaksion të vetëm).

Dhe kështu:

• jemi duke filluar transaksionin e gjatë
• TRUNCATE ngarkon bllokimin AccessExclusivene e bëjmë ngadalë futjen, ndërsa të tjerët gjatë kësaj kohe
• nuk mund as Nuk është gjithçka në rregull... SELECT

Dicka nuk po shkon mirë...

ALTER TABLE… RENAME… / DROP TABLE …

Një mundësi është të ngarkoni gjithçka në një tabelë të re, dhe pastaj thjesht ta ribeni me emrin e tabelës së vjetër. Disa detaje të pakëndshme:

• edhe kjo bllokimin AccessExclusive, ndonëse ndjeshëm më pak në kohë
• të gjithë planet e kërkesave/statistikën e kësaj tabele do të humbasin, duhet të ekzekutohet ANALYZE
• të gjithë çelësat e jashtëm (FK) për tabelën

Ishte një patch WIP nga Simon Riggs, i cili propozonte të bënte ALTER-operacion për të zëvendësuar trupin e tabelës në nivelin e skedarit, pa prekur statistikën dhe FK, por nuk arriti të mbledhë shumicën.

DELETE, UPDATE, INSERT

Pra, ndalojmë në variantin që nuk bllokon nga tre operacione. Pothuajse tre… Si ta bëjmë këtë sa më efikasht?

-- gjithçka bëhet brenda transaksionit, në mënyrë që askush të mos shohë "gjendjet" e "ndërmjetme"
BEGIN;

-- krijojmë një tabelë përkohshme me të dhënat e importuara
CREATE TEMPORARY TABLE tmp(
  LIKE dst INCLUDING INDEXES -- sipas modelit, së bashku me indeksat
) ON COMMIT DROP; -- jashtë transaksionit nuk na nevojitet

-- shpejt e shpejt e derdhim imazhin e ri përmes COPY
COPY tmp FROM STDIN;
-- ...
-- .

-- fshini ata që nuk mungojnë
DELETE FROM
  dst D
USING
  dst X
LEFT JOIN
  tmp Y
    USING(pk1, pk2) -- fushat e çelësit të parë
WHERE
  (D.pk1, D.pk2) = (X.pk1, X.pk2) AND
  Y IS NOT DISTINCT FROM NULL; -- "anti-join"

-- përditësojmë të mbeturit
UPDATE
  dst D
SET
  (f1, f2, f3) = (T.f1, T.f2, T.f3)
FROM
  tmp T
WHERE
  (D.pk1, D.pk2) = (T.pk1, T.pk2) AND
  (D.f1, D.f2, D.f3) IS DISTINCT FROM (T.f1, T.f2, T.f3); -- nuk ka nevojë të përditësojmë përputhjet

-- shtojmë të munguarit
INSERT INTO
  dst
SELECT
  T.*
FROM
  tmp T
LEFT JOIN
  dst D
    USING(pk1, pk2)
WHERE
  D IS NOT DISTINCT FROM NULL;

COMMIT;

3.2. Pastrimi i importit

Në të njëjtin KLADr, të gjithë regjistrat e ndryshuar duhet të kalojnë përmes pastrimit - të normalizohen, të nxjerrin fjalë kyçe, të sjellin në struktura të nevojshme. Por si mund ta dimë - çfarë është ndryshuar, pa e komplikuar kodin e sinkronizimit, në mënyrë ideale, madje pa e prekur atë?

Nëse akseset për shkrim gjatë sinkronizimit janë vetëm për procesin tuaj, mund të përdorni një trigger, i cili do të mbledhë të gjitha ndryshimet për ne:

-- tabelat e synuara
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);

-- tabelat me historinë e ndryshimeve
CREATE TABLE kladr$log(
  ro kladr, -- këtu ruajmë imazhet e plota të regjistrave të vjetër/të rinj
  rn kladr
);

CREATE TABLE kladr_house$log(
  ro kladr_house,
  rn kladr_house
);

-- funksioni i përbashkët për logimin e ndryshimeve
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
  dst varchar = TG_TABLE_NAME || '$log';
  stmt text = '';
BEGIN
  -- kontrollojmë nevojën për logimin kur përditësohet një regjistër
  IF TG_OP = 'UPDATE' THEN
    IF NEW IS NOT DISTINCT FROM OLD THEN
      RETURN NEW;
    END IF;
  END IF;
  -- krijojmë një regjistër logu
  stmt = 'INSERT INTO ' || dst::text || '(ro,rn)VALUES(';
  CASE TG_OP
    WHEN 'INSERT' THEN
      EXECUTE stmt || 'NULL,$1)' USING NEW;
    WHEN 'UPDATE' THEN
      EXECUTE stmt || '$1,$2)' USING OLD, NEW;
    WHEN 'DELETE' THEN
      EXECUTE stmt || '$1,NULL)' USING OLD;
  END CASE;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Tani ne mund të aplikojmë triggers para fillimit të sinkronizimit (ose t’i aktivizojmë përmes ALTER TABLE ... ENABLE TRIGGER ...):

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

CREATE TRIGGER log
  AFTER INSERT OR UPDATE OR DELETE
  ON kladr_house
    FOR EACH ROW
      EXECUTE PROCEDURE diff$log();

Dhe pastaj qetë nga tabelat log nxjerrim të gjitha ndryshimet që na nevojiten dhe i kalojmë përmes përpunuesve të tjerë.

3.3. Importi i grupeve të lidhura

Më sipër kemi shqyrtuar rastet kur strukturat e të dhënave të burimit dhe të pranimit bien ndesh. Por çfarë të bëjmë, nëse eksportimi nga një sistem të jashtëm ka një format ndryshe nga struktura që kemi në bazën tonë?

Merrni për shembull ruajtjen e klientëve dhe faturave për ta, një variant klasik 'shumë-në-një':

CREATE TABLE client(
  client_id
    serial
      PRIMARY KEY
, inn
    varchar
      UNIQUE
, name
    varchar
);

CREATE TABLE invoice(
  invoice_id
    serial
      PRIMARY KEY
, client_id
    integer
      REFERENCES client(client_id)
, number
    varchar
, dt
    date
, sum
    numeric(32,2)
);

Dhe kështu eksportimi nga një burim të jashtëm vjen në formën e 'gjithçka në një':

CREATE TEMPORARY TABLE invoice_import(
  client_inn
    varchar
, client_name
    varchar
, invoice_number
    varchar
, invoice_dt
    date
, invoice_sum
    numeric(32,2)
);

E qartë se të dhënat për klientët mund të dyfishohen në këtë variant, dhe regjistri kryesor është 'fatura':

0123456789;Vasia;A-01;2020-03-16;1000.00
9876543210;Petya;A-02;2020-03-16;666.00
0123456789;Vasia;B-03;2020-03-16;9999.00

Për modelin thjesht do të fusim të dhënat tona të testimit, por mbajmë mend— COPY më efikas!

INSERT INTO invoice_import
VALUES
  ('0123456789', 'Vasia', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'Petya', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'Vasia', 'B-03', '2020-03-16', 9999.00);

Së pari, do të nxjerrim ato 'segmentet', të cilat referojnë 'faktet' tona. Në rastin tonë, faturat referojnë tek klientët:

CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- mund të jetë thjesht SELECT DISTINCT, nëse të dhënat janë të përmbledhura
  client_inn inn
, client_name "name"
FROM
  invoice_import;

Për të lidhur fatura me ID e klientëve, na duhet së pari t'i njohim ose t'i gjenerojmë këto identifikues. Ta shtojmë atyre fushat:

ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;

Do të shfrytëzojmë metodën e përshkruar më lart për sinkronizimin e tabelave me disa ndryshime - nuk do të përditësojmë dhe hiqnim asgjë nga tabela e destinacionit, pasi importi i klientëve është "append-only":

-- vendosim në tabelën e importit ID-të e regjistrimeve ekzistuese
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  client D
WHERE
  T.inn = D.inn; -- çelësi unik

-- Shtojmë regjistrimet që mungojnë dhe vendosim ID-të e tyre
WITH ins AS (
  INSERT INTO client(
    inn
  , name
  )
  SELECT
    inn
  , name
  FROM
    client_import
  WHERE
    client_id IS NULL -- nëse ID nuk është vendosur
  RETURNING *
)
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  ins D
WHERE
  T.inn = D.inn; -- çelësi unik

-- vendosim ID-të e klientëve për regjistrimet e faturave
UPDATE
  invoice_import T
SET
  client_id = D.client_id
FROM
  client_import D
WHERE
  T.client_inn = D.inn; -- çelësi aplikativ

Pra, gjithçka - në invoice_import tani kemi mbushur fushën lidhëse client_id, me të cilën do të vendosim faturën.

Burimi: habr.com