ProHoster > blog > Amministrazzjoni > DBA: torganizza b'mod kompetenti sinkronizzazzjonijiet u importazzjonijiet

DBA: torganizza b'mod kompetenti sinkronizzazzjonijiet u importazzjonijiet

Għall-ipproċessar kumpless ta’ settijiet kbar ta’ data (differenti Proċessi ETL: importazzjonijiet, konverżjonijiet u sinkronizzazzjoni ma 'sors estern) ħafna drabi jkun hemm bżonn temporanjament "ftakar" u immedjatament ipproċessa malajr xi ħaġa voluminuża.

Kompitu tipiku ta’ dan it-tip normalment jinstema’ xi ħaġa bħal din: "Hawnhekk dipartiment tal-kontabilità ħatt mill-bank klijent l-aħħar pagamenti li rċevejt, trid ittellahom malajr fuq il-websajt u torbothom mal-kontijiet tiegħek.

Iżda meta l-volum ta 'din "xi ħaġa" jibda jitkejjel f'mijiet ta' megabytes, u s-servizz irid ikompli jaħdem mad-database 24x7, jinqalgħu ħafna effetti sekondarji li jħassru ħajtek.
DBA: torganizza b'mod kompetenti sinkronizzazzjonijiet u importazzjonijiet
Biex tittrattahom f'PostgreSQL (u mhux fiha biss), tista 'tuża xi ottimizzazzjonijiet li jippermettulek tipproċessa kollox aktar malajr u b'inqas konsum ta' riżorsi.

1. Fejn tibgħat?

L-ewwel, ejja niddeċiedu fejn nistgħu ntellgħu d-dejta li rridu "nipproċessaw."

1.1. Tabelli temporanji (TEMPORARY TABLE)

Fil-prinċipju, għal PostgreSQL it-tabelli temporanji huma l-istess bħal kull ieħor. Għalhekk, superstizzjonijiet bħal "Kollox hemm huwa maħżun biss fil-memorja, u jista 'jispiċċa". Iżda hemm ukoll diversi differenzi sinifikanti.

"Ispazju tal-isem" tiegħek għal kull konnessjoni mad-database

Jekk żewġ konnessjonijiet jippruvaw jgħaqqdu fl-istess ħin CREATE TABLE x, allura xi ħadd definittivament se tikseb żball ta' non-uniċità oġġetti tad-database.

Imma jekk it-tnejn jippruvaw jesegwixxu CREATE TEMPORARY TABLE x, imbagħad it-tnejn se jagħmlu dan b'mod normali, u kulħadd se tikseb kopja tiegħek tabelli. U ma jkun hemm xejn komuni bejniethom.

"Awtoqerda" meta tiskonnettja

Meta l-konnessjoni tingħalaq, it-tabelli temporanji kollha jitħassru awtomatikament, għalhekk manwalment DROP TABLE x m'hemmx skop ħlief...

Jekk qed taħdem permezz pgbouncer fil-modalità tat-tranżazzjoni, allura d-database tkompli temmen li din il-konnessjoni għadha attiva, u fiha din it-tabella temporanja għadha teżisti.

Għalhekk, jekk tipprova terġa' toħloqha, minn konnessjoni differenti għal pgbouncer, tirriżulta fi żball. Iżda dan jista 'jiġi evitat bl-użu CREATE TEMPORARY TABLE IF NOT EXISTS x.

Veru, huwa aħjar li ma tagħmilx dan xorta waħda, għax allura tista '"f'daqqa" ssib hemm id-dejta li fadal mis-"sid preċedenti". Minflok, huwa ħafna aħjar li taqra l-manwal u tara li meta toħloq tabella huwa possibbli li żżid ON COMMIT DROP - jiġifieri, meta t-tranżazzjoni titlesta, it-tabella titħassar awtomatikament.

Non-replikazzjoni

Minħabba li jappartjenu biss għal konnessjoni speċifika, it-tabelli temporanji mhumiex replikati. Iżda dan jelimina l-ħtieġa għal reġistrazzjoni doppja tad-data f'borġ + WAL, għalhekk Daħħal/Aġġorna/ĦAssaS fih huwa ħafna aktar mgħaġġel.

Iżda peress li tabella temporanja għadha tabella "kważi ordinarja", lanqas ma tistax tinħoloq fuq replika. Mill-inqas għalissa, għalkemm il-garża korrispondenti ilha tiċċirkola għal żmien twil.

1.2. TABELLA UNLOGGED

Imma x'għandek tagħmel, pereżempju, jekk għandek xi tip ta 'proċess ETL ingombranti li ma jistax jiġi implimentat fi tranżazzjoni waħda, iżda xorta jkollok pgbouncer fil-modalità tat-tranżazzjoni? ..

Jew il-fluss tad-data huwa tant kbir li M'hemmx biżżejjed bandwidth fuq konnessjoni waħda minn database (aqra, proċess wieħed għal kull CPU)?...

Jew għaddejjin xi operazzjonijiet b'mod mhux sinkroniku f'konnessjonijiet differenti?..

Hemm għażla waħda biss hawn - temporanjament toħloq tabella mhux temporanja. Pun, iva. Jiġifieri:

  • ħoloq tabelli "tiegħi stess" b'ismijiet massimu każwali sabiex ma jaqsmu ma 'ħadd
  • Extract: imliethom b'dejta minn sors estern
  • Transform: konvertiti, mimlija fl-oqsma ewlenin li jgħaqqdu
  • tagħbija: jitferra data lesta fit-tabelli fil-mira
  • it-tabelli "tiegħi" imħassra

U issa - fly fl-ingwent. Fil-fatt, il-kitbiet kollha f'PostgreSQL iseħħu darbtejn - l-ewwel f'WAL, imbagħad fil-korpi tabella/indiċi. Dan kollu jsir biex jappoġġja ACID u jikkoreġi l-viżibilità tad-dejta bejn COMMIT‘inkwetat u ROLLBACK‘tranżazzjonijiet nulli.

Imma dan m'għandniex bżonn! Għandna l-proċess kollu Jew kien kompletament suċċess jew ma kienx.. Ma jimpurtax kemm se jkun hemm tranżazzjonijiet intermedji - m'aħniex interessati li "nkomplu l-proċess min-nofs", speċjalment meta ma jkunx ċar fejn kien.

Biex tagħmel dan, l-iżviluppaturi PostgreSQL, lura fil-verżjoni 9.1, introduċew ħaġa bħal tabelli UNLOGGED:

B'din l-indikazzjoni, it-tabella tinħoloq bħala mhux logged. Id-dejta miktuba f’tabelli mhux irreġistrati ma tgħaddix mir-reġistru tal-kitba bil-quddiem (ara l-Kapitolu 29), u b’hekk tabelli bħal dawn taħdem ħafna aktar malajr mis-soltu. Madankollu, mhumiex immuni għall-falliment; f'każ ta' ħsara fis-server jew għeluq ta' emerġenza, tabella mhux logged awtomatikament maqtugħ. Barra minn hekk, il-kontenut tat-tabella mhux logged mhux replikat għal servers slave. Kwalunkwe indiċi maħluqa fuq tabella unlogged awtomatikament issir unlogged.

Fil-qosor, se jkun ħafna aktar mgħaġġel, imma jekk is-server tad-database "jaqa", ikun spjaċevoli. Imma kemm-il darba jiġri dan, u l-proċess tal-ETL tiegħek jaf kif jikkoreġi dan b'mod korrett "minn-nofs" wara li "rivitalizza" id-database?...

Jekk le, u l-każ ta' hawn fuq huwa simili għal tiegħek, uża UNLOGGEDimma qatt ma jippermettux dan l-attribut fuq tabelli reali, id-data li minnha hija għażiża għalik.

1.3. FUQ L-IMPENJA {ĦASSAR RANGELI | qatra}

Dan il-kostrutt jippermettilek li tispeċifika mġiba awtomatika meta titlesta tranżazzjoni meta toħloq tabella.

fuq ON COMMIT DROP Diġà ktibt hawn fuq, tiġġenera DROP TABLE, iżda ma ON COMMIT DELETE ROWS is-sitwazzjoni hija aktar interessanti - hija ġġenerata hawn TRUNCATE TABLE.

Peress li l-infrastruttura kollha għall-ħażna tal-meta-deskrizzjoni ta 'tabella temporanja hija eżattament l-istess bħal dik ta' tabella regolari, allura Il-ħolqien u t-tħassir kostanti ta 'tabelli temporanji jwasslu għal "nefħa" severa tat-tabelli tas-sistema pg_class, pg_attribute, pg_attrdef, pg_depend,...

Issa immaġina li għandek ħaddiem fuq konnessjoni diretta mad-database, li tiftaħ tranżazzjoni ġdida kull sekonda, toħloq, timla, tipproċessa u tħassar tabella temporanja... Se jkun hemm eċċess ta 'żibel akkumulat fit-tabelli tas-sistema, u dan jikkawża brejkijiet żejda għal kull operazzjoni.

B'mod ġenerali, tagħmel dan! F'dan il-każ huwa ħafna aktar effettiv CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS neħħiha miċ-ċiklu tat-tranżazzjoni - imbagħad sal-bidu ta 'kull transazzjoni ġdida t-tabelli diġà huma se teżisti (issejvja sejħa CREATE), iżda se jkun vojt, Grazzi lil TRUNCATE (salvajna wkoll is-sejħa tagħha) meta tlesti t-tranżazzjoni preċedenti.

1.4. LIKE...INKLUŻI...

Semmejt fil-bidu li wieħed mill-każijiet ta 'użu tipiċi għal tabelli temporanji huwa diversi tipi ta' importazzjonijiet - u l-iżviluppatur għajjien jikkopja-pejst il-lista ta 'oqsma tat-tabella fil-mira fid-dikjarazzjoni ta' temporanju tiegħu...

Imma l-għażż huwa l-mutur tal-progress! Għalhekk oħloq tabella ġdida "ibbażata fuq kampjun" jista 'jkun ħafna aktar sempliċi:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Peress li mbagħad tista 'tiġġenera ħafna dejta f'din it-tabella, it-tiftix minnha qatt mhu se jkun mgħaġġel. Iżda hemm soluzzjoni tradizzjonali għal dan - indiċi! U, iva, tabella temporanja jista' jkollha wkoll indiċi.

Peress li, ħafna drabi, l-indiċijiet meħtieġa jikkoinċidu mal-indiċi tat-tabella fil-mira, tista 'sempliċement tikteb LIKE target_table INCLUDING INDEXES.

Jekk għandek bżonn ukoll DEFAULT-valuri (per eżempju, biex timla l-valuri taċ-ċavetta primarja), tista 'tuża LIKE target_table INCLUDING DEFAULTS. Jew sempliċiment - LIKE target_table INCLUDING ALL — tikkopja l-inadempjenzi, l-indiċi, ir-restrizzjonijiet,...

Imma hawn trid tifhem li jekk ħoloqt tabella ta 'importazzjoni immedjatament b'indiċi, allura d-data se tieħu aktar żmien biex titgħabbamilli jekk l-ewwel timla kollox, u mbagħad biss roll up l-indiċi - ħares lejn kif tagħmel dan bħala eżempju pg_dump.

B'mod ġenerali, RTFM!

2. Kif tikteb?

Ħalli ngħid biss - użaha COPY-fluss minflok "pakkett" INSERT, aċċelerazzjoni xi drabi. Tista 'anki direttament minn fajl iġġenerat minn qabel.

3. Kif tipproċessa?

Allura, ejja nħallu l-introduzzjoni tagħna tidher xi ħaġa bħal din:

  • għandek tabella bid-dejta tal-klijenti maħżuna fid-database tiegħek 1M rekords
  • kuljum klijent jibgħatlek waħda ġdida "immaġni" sħiħa
  • mill-esperjenza taf li minn żmien għal żmien mhux aktar minn 10K rekords huma mibdula

Eżempju klassiku ta 'sitwazzjoni bħal din huwa bażi KLADR — b'kollox hemm ħafna indirizzi, iżda f'kull upload ta' kull ġimgħa ftit li xejn ikun hemm bidliet (tismija mill-ġdid ta' insedjamenti, għaqqad ta' toroq, dehra ta' djar ġodda) anke fuq skala nazzjonali.

3.1. Algoritmu ta 'sinkronizzazzjoni sħiħa

Għas-sempliċità, ejja ngħidu li lanqas m'għandek bżonn tirristruttura d-dejta - sempliċement daħħal it-tabella fil-forma mixtieqa, jiġifieri:

  • neħħi dak kollu li m’għadux jeżisti
  • aġġornament dak kollu li diġà kien jeżisti u jeħtieġ li jiġi aġġornat
  • daħħal dak kollu li għadu ma ġarax

Għaliex l-operazzjonijiet għandhom isiru f'din l-ordni? Minħabba li dan huwa kif id-daqs tal-mejda se jikber minimament (ftakar MVCC!).

Ħassar MILL dst

Le, ovvjament tista' tgħaddi b'żewġ operazzjonijiet biss:

  • neħħi (DELETE) kollox b'mod ġenerali
  • daħħal kollha mill-immaġni l-ġdida

Iżda fl-istess ħin, grazzi għall-MVCC, Id-daqs tat-tabella se jiżdied eżattament darbtejn! Il-ksib ta' +1M immaġini ta' rekords fit-tabella minħabba aġġornament ta' 10K huwa daqshekk redundancy...

TRUNCATE dst

Żviluppatur b'aktar esperjenza jaf li l-pillola kollha tista 'titnaddaf b'mod pjuttost irħis:

  • ċara (TRUNCATE) it-tabella kollha
  • daħħal kollha mill-immaġni l-ġdida

Il-metodu huwa effettiv, kultant pjuttost applikabbli, iżda hemm problema... Se nkunu qed inżidu rekords 1M għal żmien twil, għalhekk ma nistgħux naffordjaw li nħallu t-tabella vojta għal dan iż-żmien kollu (kif jiġri mingħajr ma nkebbewha fi tranżazzjoni waħda).

Li jfisser:

  • qed nibdew tranżazzjoni fit-tul
  • TRUNCATE jimponi Aċċess Esklussiva-imblukkar
  • nagħmlu l-inserzjoni għal żmien twil, u kulħadd f'dan iż-żmien lanqas biss SELECT

Xi ħaġa mhux sejra tajjeb...

ALTER TABLE... êEMMI... / DROP TABLE...

Alternattiva hija li timla kollox f'tabella ġdida separata, u mbagħad sempliċement semmiha mill-ġdid minflok dik l-antika. Ftit affarijiet żgħar koroh:

  • xorta wkoll Aċċess Esklussiva, għalkemm ħafna inqas ħin
  • il-pjanijiet/l-istatistika kollha ta' mistoqsijiet għal din it-tabella huma reset, jeħtieġ li tmexxi ANALYZE
  • ċwievet barranin kollha huma miksura (FK) mat-tabella

Kien hemm garża WIP minn Simon Riggs li ssuġġeriet li tagħmel ALTER-operazzjoni biex tissostitwixxi l-korp tal-mejda fil-livell tal-fajl, mingħajr ma tmiss l-istatistika u FK, iżda ma ġabarx kworum.

Ħassar, Aġġorna, Daħħal

Għalhekk, aħna noqogħdu fuq l-għażla li ma jimblukkawx ta 'tliet operazzjonijiet. Kważi tlieta... Kif tagħmel dan bl-aktar mod effettiv?

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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. Ipproċessar wara l-importazzjoni

Fl-istess KLADR, ir-rekords kollha mibdula għandhom jitmexxew addizzjonalment permezz ta 'post-ipproċessar - normalizzati, kliem prinċipali enfasizzati, u mnaqqsa għall-istrutturi meħtieġa. Imma kif taf - x'inbidel eżattmingħajr ma tikkomplika l-kodiċi tas-sinkronizzazzjoni, idealment mingħajr ma tmissu xejn?

Jekk il-proċess tiegħek biss ikollu aċċess għall-kitba fil-ħin tas-sinkronizzazzjoni, allura tista 'tuża grillu li jiġbor il-bidliet kollha għalina:

-- целевые таблицы
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;

Issa nistgħu napplikaw triggers qabel ma nibdew is-sinkronizzazzjoni (jew inħalluhom permezz 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();

U mbagħad niġbdu bil-kalma l-bidliet kollha li neħtieġu mit-tabelli tar-reġistru u nħaddmuhom permezz ta 'handlers addizzjonali.

3.3. Importazzjoni ta' Settijiet Konnessi

Hawn fuq ikkunsidrajna każijiet meta l-istrutturi tad-dejta tas-sors u tad-destinazzjoni huma l-istess. Imma x'jiġri jekk it-tlugħ minn sistema esterna jkollha format differenti mill-istruttura tal-ħażna fid-database tagħna?

Ejja nieħdu bħala eżempju l-ħażna tal-klijenti u l-kontijiet tagħhom, l-għażla klassika "ħafna għal wieħed":

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)
);

Iżda t-tniżżil minn sors estern jiġi għandna fil-forma ta '"kollha f'wieħed":

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

Ovvjament, id-dejta tal-klijent tista 'tiġi duplikata f'din il-verżjoni, u r-rekord ewlieni huwa "kont":

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

Għall-mudell, aħna ser sempliċement daħħal id-dejta tat-test tagħna, imma ftakar - COPY aktar effiċjenti!

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);

L-ewwel, ejja nenfasizzaw dawk il-“qatgħat” li għalihom jirreferu l-“fatti” tagħna. Fil-każ tagħna, il-fatturi jirreferu għall-klijenti:

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

Sabiex nassoċjaw b'mod korrett il-kontijiet mal-IDs tal-klijenti, l-ewwel għandna bżonn insibu jew niġġeneraw dawn l-identifikaturi. Ejja nżidu oqsma taħthom:

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

Ejja nużaw il-metodu ta 'sinkronizzazzjoni tat-tabella deskritt hawn fuq b'emenda żgħira - aħna mhux se naġġornaw jew inħassru xejn fit-tabella fil-mira, għaliex aħna nimportaw klijenti "append-only":

-- проставляем в таблице импорта 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; -- прикладной ключ

Fil-fatt, kollox jinsab fih invoice_import Issa għandna l-qasam tal-kuntatt mimlija client_id, li biha se ndaħħlu l-fattura.

Sors: www.habr.com

Żid kumment