DBA: organizoni me kompetencë sinkronizimet dhe importet

Për përpunimin kompleks të grupeve të mëdha të të dhënave (të ndryshme Proceset ETL: importet, konvertimet dhe sinkronizimi me një burim të jashtëm) shpesh ka nevojë "mbani mend" përkohësisht dhe përpunoni menjëherë diçka voluminoze.

Një detyrë tipike e këtij lloji zakonisht tingëllon diçka si kjo: "Mu ketu departamenti i kontabilitetit i shkarkuar nga banka e klientit pagesat e fundit të marra, duhet t'i ngarkoni shpejt në faqen e internetit dhe t'i lidhni me llogaritë tuaja"

Por kur vëllimi i kësaj "diçkaje" fillon të matet në qindra megabajt dhe shërbimi duhet të vazhdojë të punojë me bazën e të dhënave 24x7, lindin shumë efekte anësore që do t'ju shkatërrojnë jetën.

Për t'u marrë me to në PostgreSQL (dhe jo vetëm në të), mund të përdorni disa optimizime që do t'ju lejojnë të përpunoni gjithçka më shpejt dhe me më pak konsum burimesh.

1. Ku të dërgohet?

Së pari, le të vendosim se ku mund të ngarkojmë të dhënat që duam të "përpunojmë".

1.1. Tabelat e përkohshme (TABELA E PËRKOHSHME)

Në parim, për PostgreSQL tabelat e përkohshme janë të njëjta si çdo tjetër. Prandaj, besëtytnitë si "Gjithçka atje ruhet vetëm në kujtesë dhe mund të përfundojë". Por ka edhe disa dallime të rëndësishme.

"Hapësira e emrit" tuaj për çdo lidhje me bazën e të dhënave

Nëse dy lidhje përpiqen të lidhen në të njëjtën kohë CREATE TABLE x, atëherë dikush patjetër do të marrë gabim jo-unikaliteti objektet e bazës së të dhënave.

Por nëse të dy përpiqen të ekzekutojnë CREATE TEMPORARY TABLE x, atëherë të dy do ta bëjnë normalisht, dhe të gjithë do ta marrin kopjen tuaj tabelat. Dhe nuk do të ketë asgjë të përbashkët mes tyre.

"Vetë-shkatërrojë" kur shkëputet

Kur lidhja mbyllet, të gjitha tabelat e përkohshme fshihen automatikisht, pra me dorë DROP TABLE x nuk ka kuptim pervec...

Nëse jeni duke punuar pgbouncer në modalitetin e transaksionit, atëherë baza e të dhënave vazhdon të besojë se kjo lidhje është ende aktive, dhe në të kjo tabelë e përkohshme ekziston ende.

Prandaj, përpjekja për ta krijuar atë përsëri, nga një lidhje tjetër me pgbouncer, do të rezultojë në një gabim. Por kjo mund të anashkalohet duke përdorur CREATE TEMPORARY TABLE IF NOT EXISTS x.

Vërtetë, është më mirë të mos e bëni këtë gjithsesi, sepse atëherë mund të gjeni "papritmas" atje të dhënat e mbetura nga "pronari i mëparshëm". Në vend të kësaj, është shumë më mirë të lexoni manualin dhe të shihni se kur krijoni një tabelë është e mundur të shtoni ON COMMIT DROP - domethënë, kur transaksioni të përfundojë, tabela do të fshihet automatikisht.

Mospërsëritje

Për shkak se ato i përkasin vetëm një lidhjeje specifike, tabelat e përkohshme nuk përsëriten. Por kjo eliminon nevojën për regjistrim të dyfishtë të të dhënave në grumbull + WAL, kështu që INSERT/UPDATE/DELETE në të është shumë më i shpejtë.

Por meqenëse një tabelë e përkohshme është ende një tabelë "pothuajse e zakonshme", ajo nuk mund të krijohet as në një kopje. Të paktën tani për tani, megjithëse patch-i përkatës qarkullon prej kohësh.

1.2. TABELA E SHKAKTUAR

Por çfarë duhet të bëni, për shembull, nëse keni një lloj procesi të rëndë ETL që nuk mund të zbatohet brenda një transaksioni, por ju ende keni pgbouncer në modalitetin e transaksionit? ..

Ose fluksi i të dhënave është aq i madh sa Nuk ka gjerësi bande të mjaftueshme në një lidhje nga një bazë të dhënash (lexo, një proces për CPU)?..

Ose disa operacione po zhvillohen në mënyrë asinkrone ne lidhje te ndryshme?..

Këtu ka vetëm një opsion - krijoni përkohësisht një tabelë jo të përkohshme. Puna, po. Kjo eshte:

• krijoi tabela "të miat" me emra maksimalisht të rastësishëm në mënyrë që të mos kryqëzoheshin me askënd
• Ekstrakt: i mbushi me të dhëna nga një burim i jashtëm
• Transformoj: i konvertuar, i plotësuar në fushat kryesore lidhëse
• Ngarkesë: derdhi të dhënat e gatshme në tabelat e synuara
• fshiu tabelat "e mia".

Dhe tani - një mizë në vaj. Në fakt, të gjitha shkrimet në PostgreSQL ndodhin dy herë - së pari në WAL, pastaj në trupat e tabelës/indeksit. E gjithë kjo është bërë për të mbështetur ACID dhe për të korrigjuar dukshmërinë e të dhënave ndërmjet tyre COMMIT'i arra dhe ROLLBACK'transaksione të pavlefshme.

Por ne nuk kemi nevojë për këtë! Ne kemi të gjithë procesin Ose ishte plotësisht i suksesshëm ose jo.. Nuk ka rëndësi se sa transaksione të ndërmjetme do të ketë - ne nuk jemi të interesuar të "vazhdojmë procesin nga mesi", veçanërisht kur nuk është e qartë se ku ishte.

Për ta bërë këtë, zhvilluesit PostgreSQL, përsëri në versionin 9.1, prezantuan një gjë të tillë si tavolina të UNLOGGED:

Me këtë tregues, tabela krijohet si e paloguar. Të dhënat e shkruara në tabela të paloguara nuk kalojnë nëpër regjistrin e parashkrimit (shih Kapitullin 29), duke bërë që tabela të tilla të punoni shumë më shpejt se zakonisht. Megjithatë, ata nuk janë të imunizuar ndaj dështimit; në rast të dështimit të serverit ose mbylljes emergjente, një tabelë e paloguar shkurtohet automatikisht. Për më tepër, përmbajtja e tabelës së paloguar nuk përsëritet te serverët skllevër. Çdo indeks i krijuar në një tabelë të paloguar bëhet automatikisht i çloguar.

Me pak fjalë do të jetë shumë më shpejt, por nëse serveri i bazës së të dhënave "bie", do të jetë i pakëndshëm. Por sa shpesh ndodh kjo dhe a di procesi juaj ETL se si ta korrigjojë këtë saktë "nga mesi" pas "rigjallërimit" të bazës së të dhënave?..

Nëse jo, dhe rasti i mësipërm është i ngjashëm me tuajin, përdorni UNLOGGEDpor kurrë mos e aktivizoni këtë atribut në tabelat reale, të dhënat nga e cila janë të dashura për ju.

1.3. PËR KRYERJE { FSHI RRESHTAT | HIQ}

Ky konstrukt ju lejon të specifikoni sjelljen automatike kur një transaksion përfundon kur krijoni një tabelë.

në ON COMMIT DROP Tashmë kam shkruar më lart, ajo gjeneron DROP TABLE, por me ON COMMIT DELETE ROWS situata është më interesante - ajo krijohet këtu TRUNCATE TABLE.

Meqenëse e gjithë infrastruktura për ruajtjen e meta-përshkrimit të një tabele të përkohshme është saktësisht e njëjtë me atë të një tabele të rregullt, atëherë Krijimi dhe fshirja e vazhdueshme e tabelave të përkohshme çon në "ënjtje" të rëndë të tabelave të sistemit pg_class, pg_attribute, pg_attrdef, pg_vare,…

Tani imagjinoni që keni një punëtor në lidhje direkte me bazën e të dhënave, i cili hap një transaksion të ri çdo sekondë, krijon, mbush, përpunon dhe fshin një tabelë të përkohshme... Do të ketë një tepricë të mbeturinave të grumbulluara në tabelat e sistemit, dhe kjo do të shkaktojë frena shtesë për çdo operacion.

Në përgjithësi, mos e bëni këtë! Në këtë rast është shumë më efektive CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS hiqeni atë nga cikli i transaksionit - atëherë në fillim të çdo transaksioni të ri tabelat janë tashmë do të ekzistojë (ruaj një telefonatë CREATE), por do të jetë bosh, falë TRUNCATE (ne ruajtëm gjithashtu thirrjen e tij) kur përfunduam transaksionin e mëparshëm.

1.4. LIKE... PËRFSHIRË...

E përmenda në fillim se një nga rastet tipike të përdorimit të tabelave të përkohshme janë lloje të ndryshme importesh - dhe zhvilluesi i lodhur kopjon listën e fushave të tabelës së synuar në deklaratën e tij të përkohshme...

Por dembelizmi është motori i përparimit! Kjo është arsyeja pse krijoni një tabelë të re "bazuar në mostër" mund të jetë shumë më e thjeshtë:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Meqenëse më pas mund të gjeneroni shumë të dhëna në këtë tabelë, kërkimi në të nuk do të jetë kurrë i shpejtë. Por ekziston një zgjidhje tradicionale për këtë - indekset! Dhe po, një tabelë e përkohshme mund të ketë edhe indekse.

Meqenëse, shpesh, indekset e kërkuara përkojnë me indekset e tabelës së synuar, thjesht mund të shkruani LIKE target_table INCLUDING INDEXES.

Nëse keni nevojë gjithashtu DEFAULT-vlerat (për shembull, për të plotësuar vlerat kryesore të çelësit), mund të përdorni LIKE target_table INCLUDING DEFAULTS. Ose thjesht - LIKE target_table INCLUDING ALL — kopjon parazgjedhjet, indekset, kufizimet,...

Por këtu ju duhet të kuptoni se nëse keni krijuar importoni menjëherë tabelën me indekse, atëherë të dhënat do të marrin më shumë kohë për t'u ngarkuarsesa nëse së pari plotësoni gjithçka dhe vetëm atëherë grumbulloni indekset - shikoni se si e bën këtë si shembull pg_dump.

Në përgjithësi RTFM!

2. Si të shkruajmë?

Më lejoni të them vetëm - përdorni atë COPY- rrjedh në vend të "paketë" INSERT, përshpejtimi nganjëherë. Mund edhe direkt nga një skedar i krijuar paraprakisht.

3. Si të përpunohet?

Pra, le ta lëmë hyrjen tonë të duket diçka si kjo:

• ju keni një tabelë me të dhënat e klientit të ruajtura në bazën e të dhënave tuaja 1 milion rekorde
• çdo ditë një klient ju dërgon një të re "imazhi" i plotë
• nga përvoja ju e dini që herë pas here nuk ndryshohen më shumë se 10 mijë rekorde

Një shembull klasik i një situate të tillë është Baza e KLADR — gjithsej ka shumë adresa, por në çdo ngarkim javor ka shumë pak ndryshime (riemërtimi i vendbanimeve, kombinimi i rrugëve, shfaqja e shtëpive të reja) edhe në shkallë kombëtare.

3.1. Algoritmi i plotë i sinkronizimit

Për thjeshtësi, le të themi se nuk keni nevojë as të ristrukturoni të dhënat - thjesht sillni tabelën në formën e dëshiruar, domethënë:

• heq gjithçka që nuk ekziston më
• azhurnimi gjithçka që ekzistonte dhe duhet të përditësohet
• për të futur gjithçka që nuk ka ndodhur ende

Pse duhet të kryhen operacionet në këtë mënyrë? Sepse kjo është mënyra se si madhësia e tryezës do të rritet minimalisht (mbani mend MVCC!).

FSHIJE NGA dst

Jo, sigurisht që mund t'ia dilni me vetëm dy operacione:

• heq (DELETE) gjithçka në përgjithësi
• për të futur të gjitha nga imazhi i ri

Por në të njëjtën kohë, falë MVCC, Madhësia e tabelës do të rritet saktësisht dy herë! Marrja e +1 milion imazhe të të dhënave në tabelë për shkak të një përditësimi 10K është kaq e tepërt...

PRUNKOJ dst

Një zhvillues më me përvojë e di se i gjithë tableti mund të pastrohet mjaft lirë:

• i pastër (TRUNCATE) të gjithë tabelën
• për të futur të gjitha nga imazhi i ri

Metoda është efektive, ndonjëherë mjaft i zbatueshëm, por ka një problem... Ne do të shtojmë rekorde 1 milion për një kohë të gjatë, kështu që nuk mund të përballojmë ta lëmë tryezën bosh gjatë gjithë kësaj kohe (siç do të ndodhë pa e mbështjellë atë në një transaksion të vetëm).

Që do të thotë:

• po fillojmë transaksion afatgjatë
• TRUNCATE imponon AccessExclusive- bllokim
• ne e bëjmë futjen për një kohë të gjatë, dhe të gjithë të tjerët në këtë kohë as nuk mundet SELECT

Diçka nuk po shkon mirë...

NDRYSHO TABELA… RIEMËROJ… / HIQ TABELA…

Një alternativë është të plotësoni gjithçka në një tabelë të re të veçantë dhe më pas thjesht ta riemërtoni në vend të asaj të vjetër. Disa gjëra të vogla të këqija:

• ende gjithashtu AccessExclusive, edhe pse dukshëm më pak kohë
• të gjitha planet e pyetjeve/statistikat për këtë tabelë janë rivendosur, duhet të ekzekutohet ANALYZE
• të gjithë çelësat e huaj janë të prishur (FK) në tryezë

Kishte një patch WIP nga Simon Riggs që sugjeronte krijimin ALTER- një operacion për zëvendësimin e trupit të tabelës në nivel skedari, pa prekur statistikat dhe FK, por nuk ka mbledhur kuorum.

FSHI, PËRDITËSO, FAQE

Pra, ne vendosemi në opsionin e mosbllokimit të tre operacioneve. Pothuajse tre... Si ta bëni këtë në mënyrë më efektive?

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
BEGIN;

-- создаем временную таблицу с импортируемыми данными
CREATE TEMPORARY TABLE tmp(
  LIKE dst INCLUDING INDEXES -- по образу и подобию, вместе с индексами
) ON COMMIT DROP; -- за рамками транзакции она нам не нужна

-- быстро-быстро вливаем новый образ через COPY
COPY tmp FROM STDIN;
-- ...
-- .

-- удаляем отсутствующие
DELETE FROM
  dst D
USING
  dst X
LEFT JOIN
  tmp Y
    USING(pk1, pk2) -- поля первичного ключа
WHERE
  (D.pk1, D.pk2) = (X.pk1, X.pk2) AND
  Y IS NOT DISTINCT FROM NULL; -- "антиджойн"

-- обновляем оставшиеся
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); -- незачем обновлять совпадающие

-- вставляем отсутствующие
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. Importi pas përpunimit

Në të njëjtin KLADR, të gjitha regjistrimet e ndryshuara duhet të ekzekutohen shtesë përmes përpunimit pas - të normalizohen, fjalët kyçe të theksohen dhe të reduktohen në strukturat e kërkuara. Por si e dini - çfarë ka ndryshuar saktësishtpa e komplikuar kodin e sinkronizimit, në mënyrë ideale pa e prekur fare?

Nëse vetëm procesi juaj ka akses shkrimi në kohën e sinkronizimit, atëherë mund të përdorni një shkas që do të mbledhë të gjitha ndryshimet për ne:

-- целевые таблицы
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);

-- таблицы с историей изменений
CREATE TABLE kladr$log(
  ro kladr, -- тут лежат целые образы записей старой/новой
  rn kladr
);

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

-- общая функция логирования изменений
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
  dst varchar = TG_TABLE_NAME || '$log';
  stmt text = '';
BEGIN
  -- проверяем необходимость логгирования при обновлении записи
  IF TG_OP = 'UPDATE' THEN
    IF NEW IS NOT DISTINCT FROM OLD THEN
      RETURN NEW;
    END IF;
  END IF;
  -- создаем запись лога
  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 mund të aplikojmë nxitës përpara se të fillojmë sinkronizimin (ose t'i aktivizojmë ato nëpërmjet 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 më pas nxjerrim me qetësi të gjitha ndryshimet që na nevojiten nga tabelat e regjistrave dhe i drejtojmë ato përmes mbajtësve shtesë.

3.3. Importimi i grupeve të lidhura

Më sipër kemi shqyrtuar rastet kur strukturat e të dhënave të burimit dhe destinacionit janë të njëjta. Por, çka nëse ngarkimi nga një sistem i jashtëm ka një format të ndryshëm nga struktura e ruajtjes në bazën tonë të të dhënave?

Le të marrim si shembull ruajtjen e klientëve dhe llogarive të tyre, opsionin klasik "shumë-për-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)
);

Por shkarkimi nga një burim i jashtëm na vjen në formën e "të gjitha në një":

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

Natyrisht, të dhënat e klientit mund të dyfishohen në këtë version, dhe rekordi kryesor është "llogaria":

0123456789;Вася;A-01;2020-03-16;1000.00
9876543210;Петя;A-02;2020-03-16;666.00
0123456789;Вася;B-03;2020-03-16;9999.00

Për modelin, ne thjesht do të fusim të dhënat tona të testimit, por mbani mend - COPY më efikas!

INSERT INTO invoice_import
VALUES
  ('0123456789', 'Вася', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'Петя', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'Вася', 'B-03', '2020-03-16', 9999.00);

Së pari, le të theksojmë ato "prerje" të cilave u referohen "faktet" tona. Në rastin tonë, faturat u referohen klientëve:

CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
  client_inn inn
, client_name "name"
FROM
  invoice_import;

Për të lidhur saktë llogaritë me ID-të e klientëve, së pari duhet të zbulojmë ose gjenerojmë këta identifikues. Le të shtojmë fushat nën to:

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

Le të përdorim metodën e sinkronizimit të tabelës të përshkruar më sipër me një ndryshim të vogël - ne nuk do të përditësojmë ose fshijmë asgjë në tabelën e synuar, sepse ne importojmë klientët "vetëm shtojca":

-- проставляем в таблице импорта ID уже существующих записей
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  client D
WHERE
  T.inn = D.inn; -- unique key

-- вставляем отсутствовавшие записи и проставляем их ID
WITH ins AS (
  INSERT INTO client(
    inn
  , name
  )
  SELECT
    inn
  , name
  FROM
    client_import
  WHERE
    client_id IS NULL -- если ID не проставился
  RETURNING *
)
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  ins D
WHERE
  T.inn = D.inn; -- unique key

-- проставляем ID клиентов у записей счетов
UPDATE
  invoice_import T
SET
  client_id = D.client_id
FROM
  client_import D
WHERE
  T.client_inn = D.inn; -- прикладной ключ

Në fakt, gjithçka është brenda invoice_import Tani kemi të plotësuar fushën e kontaktit client_id, me të cilin do të fusim faturën.

Burimi: www.habr.com