ProHoster > Blag > Riarachán > DBA: sioncrónaithe agus allmhairí a eagrú go hinniúil

DBA: sioncrónaithe agus allmhairí a eagrú go hinniúil

Le haghaidh próiseála casta ar thacair mhóra sonraí (difriúil Próisis ETL: allmhairí, tiontuithe agus sioncrónú le foinse sheachtrach) is minic a bhíonn gá leis go sealadach "cuimhnigh" agus láithreach a phróiseáil go tapa rud toirtiúil.

Fuaimeann tasc tipiciúil den chineál seo rud mar seo de ghnáth: "Díreach anseo roinn chuntasaíochta díluchtaithe ón mbanc cliant na híocaíochtaí deiridh a fuarthas, ní mór duit iad a uaslódáil go tapa chuig an suíomh Gréasáin agus iad a nascadh le do chuntais.”

Ach nuair a thosaíonn toirt an “rud éigin” seo a thomhas sna céadta meigibheart, agus caithfidh an tseirbhís leanúint ar aghaidh ag obair leis an mbunachar sonraí 24x7, tagann go leor fo-iarsmaí chun cinn a scriosfaidh do shaol.
DBA: sioncrónaithe agus allmhairí a eagrú go hinniúil
Chun déileáil leo i PostgreSQL (agus ní amháin ann), is féidir leat roinnt optimizations a úsáid a ligfidh duit gach rud a phróiseáil níos tapúla agus le níos lú tomhaltas acmhainní.

1. Cá háit a long?

Ar dtús, déanaimis cinneadh cén áit ar féidir linn na sonraí a theastaíonn uainn a “phróiseáil” a uaslódáil.

1.1. Táblaí sealadacha (TEMPORARY TABLE)

I bprionsabal, tá táblaí sealadacha PostgreSQL mar an gcéanna le táblaí sealadacha ar bith eile. Dá bhrí sin, is maith le piseoga “Ní stóráiltear gach rud ach i gcuimhne, agus is féidir deireadh a chur leis”. Ach tá roinnt difríochtaí suntasacha ann freisin.

Do “spás ainm” féin do gach nasc leis an mbunachar sonraí

Má dhéantar iarracht dhá nasc a nascadh ag an am céanna CREATE TABLE x, ansin gheobhaidh duine éigin cinnte earráid neamh-uathúlacht réada bunachar sonraí.

Ach má tá an dá iarracht a fhorghníomhú CREATE TEMPORARY TABLE x, ansin déanfaidh an dá rud de ghnáth, agus gheobhaidh gach duine do chóip táblaí. Agus ní bheidh aon rud i gcoiteann eatarthu.

"Féin-scrios" nuair a dhéantar dícheangal

Nuair a dhúntar an nasc, scriostar na táblaí sealadacha go léir go huathoibríoch, mar sin de láimh DROP TABLE x níl aon phointe ann ach ...

Má tá tú ag obair tríd pgbouncer i mód idirbhirt, ansin creideann an bunachar sonraí go bhfuil an nasc seo fós gníomhach, agus tá an tábla sealadach seo fós ann.

Dá bhrí sin, ag iarraidh é a chruthú arís, ó nasc difriúil go pgbouncer, beidh sé mar thoradh ar earráid. Ach is féidir é seo a shárú trí úsáid a bhaint as CREATE TEMPORARY TABLE IF NOT EXISTS x.

Fíor, is fearr gan é seo a dhéanamh ar aon nós, mar ansin is féidir leat "go tobann" a fháil ann na sonraí atá fágtha ón "úinéir roimhe seo". Ina áit sin, tá sé i bhfad níos fearr a léamh ar an lámhleabhar agus a fheiceáil go bhfuil nuair a chruthú tábla is féidir a chur leis ON COMMIT DROP - is é sin, nuair a bheidh an t-idirbheart críochnaithe, scriosfar an tábla go huathoibríoch.

Neamh-mhacasamhlú

Toisc nach mbaineann siad ach le nasc sonrach, ní dhéantar táblaí sealadacha a mhacasamhlú. Ach cuireann sé seo deireadh leis an ngá atá le taifeadadh dúbailte ar shonraí ina gcarn + Wal, mar sin IONSÁIGH/Nuashonraigh/Scrios isteach é i bhfad níos tapúla.

Ach toisc gur tábla “beagnach gnáth” é tábla sealadach, ní féidir é a chruthú ar mhacasamhail ach an oiread. Ar a laghad le tamall, cé go bhfuil an paiste comhfhreagrach i gcúrsaíocht ar feadh i bhfad.

1.2. TÁBLA NEAMHLOGGED

Ach cad ba cheart duit a dhéanamh, mar shampla, má tá próiseas cumbersome ETL de chineál éigin agat nach féidir a chur i bhfeidhm laistigh d'aon idirbheart amháin, ach tá tú fós pgbouncer i mód idirbhirt?..

Nó tá an sreabhadh sonraí chomh mór sin Níl go leor bandaleithead ar nasc amháin ó bhunachar sonraí (léigh, próiseas amháin in aghaidh an LAP)?..

Nó tá roinnt oibríochtaí ar siúl asincrónach i naisc éagsúla? ..

Níl ach rogha amháin anseo - tábla neamhshealadach a chruthú go sealadach. Punc, sea. Is é sin:

  • chruthaigh “mo chuid féin” táblaí le hainmneacha randamacha uasta ionas nach dtrasnaíonn siad le duine ar bith
  • Sliocht: iad a líonadh le sonraí ó fhoinse sheachtrach
  • Trasfhoirmigh: tiontaithe, líonta i bpríomhréimsí nasctha
  • Luchtaigh: sonraí réidh a dhoirteadh isteach i sprioctháblaí
  • scriostar “mo tháblaí”.

Agus anois - eitilt sa ointment. Go deimhin, tarlaíonn gach scríobh i PostgreSQL faoi dhó - den chéad uair i WAL, ansin isteach sa tábla/comhlachtaí innéacs. Déantar é seo go léir chun tacú le ACID agus infheictheacht sonraí a cheartú idir COMMIT' nutty agus ROLLBACK'idirbhearta null.

Ach níl sé seo ag teastáil uainn! Tá an próiseas ar fad againn D’éirigh go hiomlán leis nó níor éirigh.. Is cuma cé mhéad idirbheart idirmheánach a bheidh ann - níl suim againn i “leanúint leis an bpróiseas ón lár,” go háirithe nuair nach bhfuil sé soiléir cá raibh sé.

Chun seo a dhéanamh, thug na forbróirí PostgreSQL, ar ais i leagan 9.1, a leithéid de rud isteach Táblaí UNLOGGED:

Leis an léiriú seo, cruthaítear an tábla mar neamhlogáilte. Ní théann sonraí a scríobhtar chuig táblaí neamhlogáilte tríd an loga réamhscríofa (féach Caibidil 29), rud a fhágann go mbíonn táblaí den sórt sin ann. oibriú i bhfad níos tapúla ná mar is gnách. Mar sin féin, níl siad díolmhaithe ó theip; i gcás teipe freastalaí nó múchadh éigeandála, tábla neamhlogáilte teasctha go huathoibríoch. Ina theannta sin, ábhar an tábla neamhlogáilte gan a mhacasamhlú chuig freastalaithe sclábhaithe. Déanfar aon innéacsanna a chruthaítear ar tábla neamhlogáilte a dhílogáil go huathoibríoch.

I mbeagán focal, beidh sé i bhfad níos tapúla, ach má "thiteann" freastalaí an bhunachair shonraí, beidh sé míthaitneamhach. Ach cé chomh minic a tharlaíonn sé seo, agus an bhfuil a fhios ag do phróiseas ETL conas é seo a cheartú i gceart “ón lár” tar éis “athbheochan” a dhéanamh ar an mbunachar sonraí?..

Mura bhfuil, agus go bhfuil an cás thuas cosúil leis an gceann atá agatsa, bain úsáid as UNLOGGEDach riamh ná cumasaigh an tréith seo ar fhíortháblaí, a bhfuil na sonraí uathu daor duit.

1.3. FAOI CHOIMEÁD { SCRIOS SCÁTHA | DROP}

Ligeann an tógáil seo duit iompar uathoibríoch a shonrú nuair a bhíonn idirbheart críochnaithe agus tábla á chruthú.

Про ON COMMIT DROP Scríobh mé thuas cheana féin, gineann sé DROP TABLE, ach le ON COMMIT DELETE ROWS tá an scéal níos suimiúla - gintear anseo é TRUNCATE TABLE.

Ós rud é go bhfuil an bonneagar iomlán chun meiti-thuairisc a stóráil ar thábla sealadach díreach mar a chéile leis an mbonneagar ar tábla rialta, mar sin Cruthaíonn agus scriostar táblaí sealadacha go seasta “at” géar ar na táblaí córais pg_class, pg_attribute, pg_attrdef, pg_depend,…

Anois samhlaigh go bhfuil oibrí agat ar nasc díreach leis an mbunachar sonraí, a osclaíonn idirbheart nua gach soicind, a chruthaíonn, a líonann, a phróiseálann agus a scriosann tábla sealadach... Beidh farasbarr truflais carntha i dtáblaí an chórais, agus beidh sé seo ina chúis le coscáin bhreise do gach oibríocht.

Go ginearálta, ná déan é seo! Sa chás seo tá sé i bhfad níos éifeachtaí CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS é a thógáil amach as an timthriall idirbhirt - ansin faoi thús gach idirbhirt nua tá na táblaí cheana féin beidh ann (shábháil glao CREATE), ach beidh folamh, a bhuíochas sin do TRUNCATE (shábháil muid a ghlao freisin) nuair a chríochnaigh muid an t-idirbheart roimhe seo.

1.4. Cosúil...Á ÁIRÍTEAR...

Luaigh mé ag an tús gurb é ceann de na cásanna úsáide tipiciúla le haghaidh táblaí sealadacha ná cineálacha éagsúla allmhairí - agus déanann an forbróir liosta réimsí an spriocchláir a chóipeáil go tuirseach i ndearbhú a shealadach...

Ach is é an leisce inneall an dul chun cinn! Sin é an fáth cruthaigh tábla nua “bunaithe ar shampla” is féidir é a bheith i bhfad níos simplí:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Ós rud é gur féidir leat a lán sonraí a ghiniúint sa tábla seo, ní bheidh cuardach tapa ann choíche. Ach tá réiteach traidisiúnta ar seo - innéacsanna! Agus, tá, is féidir innéacsanna a bheith ag tábla sealadach freisin.

Ós rud é, go minic, na hinnéacsanna riachtanacha comhthráthach leis na hinnéacsanna an tábla sprice, is féidir leat scríobh go simplí LIKE target_table INCLUDING INDEXES.

Más gá duit freisin DEFAULT-values ​​​​(mar shampla, chun na príomhluachanna a líonadh isteach), is féidir leat é a úsáid LIKE target_table INCLUDING DEFAULTS. Nó go simplí - LIKE target_table INCLUDING ALL — mainneachtainí, innéacsanna, srianta,...

Ach anseo ní mór duit a thuiscint go má chruthaigh tú tábla allmhairiú láithreach le hinnéacsanna, ansin beidh na sonraí a ghlacadh níos faide a luchtúná má líonann tú gach rud suas ar dtús, agus gan ach ansin na hinnéacsanna a rolladh suas - féach ar conas a dhéanann sé seo mar shampla pg_dumpáil.

Go ginearálta, RTFM!

2. Conas a scríobh?

Lig dom a rá go díreach - é a úsáid COPY-flow in ionad "pacáiste" INSERT, luasghéarú uaireanta. Is féidir leat fiú go díreach ó chomhad réamh-ghinte.

3. Conas a phróiseáil?

Mar sin, déanaimis breathnú ar ár n-intro rud éigin mar seo:

  • tá tábla agat le sonraí cliant atá stóráilte i do bhunachar sonraí Taifid 1M
  • gach lá seolann cliant ceann nua chugat iomlán "image"
  • ó thaithí tá a fhios agat sin ó am go chéile ní athraítear níos mó ná 10K taifead

Is sampla clasaiceach de chás den sórt sin bonn KLADR — tá go leor seoltaí san iomlán, ach i ngach uaslódáil sheachtainiúil is beag athruithe (athainmniú lonnaíochtaí, sráideanna a chomhcheangal, cuma tithe nua) fiú ar scála náisiúnta.

3.1. Algartam sioncrónaithe iomlán

Ar mhaithe le simplíocht, déarfaimid nach gá duit fiú na sonraí a athstruchtúrú - díreach tabhair an tábla isteach san fhoirm atá ag teastáil, is é sin:

  • bain úsáid as gach rud nach ann a thuilleadh
  • athnuachan gach rud a bhí ann cheana féin agus ar gá é a nuashonrú
  • cuir isteach gach rud nár tharla fós

Cén fáth ar chóir na hoibríochtaí a dhéanamh san ord seo? Toisc gurb é seo an chaoi a n-éireoidh méid an bhoird chomh beag agus is féidir (cuimhnigh ar MVCC!).

SCRÍOBH Ó dst

Ní hea, ar ndóigh is féidir leat dul tríd le dhá oibríocht amháin:

  • bain úsáid as (DELETE) gach rud i gcoitinne
  • cuir isteach go léir ón íomhá nua

Ach ag an am céanna, buíochas le MVCC, Méadóidh méid an tábla faoi dhó díreach! Is iomarcaíocht é íomhánna +1M de thaifid a fháil sa tábla mar gheall ar nuashonrú 10K...

TRUNCATE dst

Tá a fhios ag forbróir níos mó taithí gur féidir an tablet iomlán a ghlanadh go réasúnta saor:

  • soiléir (TRUNCATE) an tábla ar fad
  • cuir isteach go léir ón íomhá nua

Tá an modh éifeachtach, uaireanta infheidhme go leor, ach tá fadhb ann... Beimid ag cur taifid 1M leis ar feadh i bhfad, mar sin ní féidir linn an tábla a fhágáil folamh go ceann an ama seo (mar a tharlóidh gan é a fhilleadh in aon idirbheart amháin).

rud a chiallaíonn:

  • táimid ag tosú idirbheart fadtéarmach
  • TRUNCATE fhorchuireann Rochtain Eisiach-blocáil
  • déanaimid an cur isteach ar feadh i bhfad, agus gach duine eile ag an am seo ní féidir fiú SELECT

Níl ag éirí go maith le rud éigin...

ALTER TÁBLA… ATHAINMnigh… / TÁBLA TÁBLA…

Rogha eile is ea gach rud a líonadh isteach i dtábla nua ar leith, agus ansin é a athainmniú go simplí in ionad an tseantábla. Cúpla rud beag dána:

  • fós freisin Rochtain Eisiach, cé go suntasach níos lú ama
  • athshocraítear gach plean/staitistic ceiste don tábla seo, gá ANAILÍS a rith
  • tá na heochracha eachtracha go léir briste (FK) chuig an mbord

Bhí paiste WIP ó Simon Riggs a mhol déanamh ALTER- oibríocht chun an comhlacht tábla a athsholáthar ar an leibhéal comhaid, gan teagmháil a dhéanamh le staitisticí agus FK, ach níor bhailigh córam.

Scrios, nuashonraigh, IONSÁIGH

Mar sin, socraímid ar an rogha neamh-blocála de thrí oibríocht. Beagnach trí... Conas is éifeachtaí é seo a dhéanamh?

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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. Iompórtáil iar-phróiseáil

Sa KLADR céanna, ní mór gach taifead athraithe a rith freisin trí iar-phróiseáil - normalaithe, béim ar eochairfhocail, agus a laghdú go dtí na struchtúir riachtanacha. Ach conas a bhfuil a fhios agat - cad é go díreach a d'athraighgan an cód sioncrónaithe a dhéanamh casta, go hidéalach gan teagmháil a dhéanamh leis ar chor ar bith?

Mura bhfuil ach rochtain scríofa ag do phróiseas tráth an tsioncrónaithe, is féidir leat truicear a úsáid a bhaileoidh na hathruithe go léir dúinn:

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

Anois is féidir linn truicear a chur i bhfeidhm sula dtosaíonn tú ar an sioncrónú (nó iad a chumasú trí 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();

Agus ansin déanaimid na hathruithe go léir a theastaíonn uainn a bhaint as na táblaí logála agus iad a reáchtáil trí láimhseálaithe breise.

3.3. Tacair Nasctha á Iompórtáil

Thuas rinneamar breithniú ar chásanna ina bhfuil struchtúir sonraí na foinse agus an chinn scríbe mar a chéile. Ach cad a tharlóidh má tá formáid éagsúil ag an uaslódáil ó chóras seachtrach ón struchtúr stórála inár mbunachar sonraí?

Glacaimis mar shampla stóráil na gcliant agus a gcuntas, an rogha clasaiceach “go leor le duine”:

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

Ach tagann an íoslódáil ó fhoinse sheachtrach chugainn i bhfoirm “uile i gceann”:

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

Ar ndóigh, is féidir sonraí custaiméirí a mhacasamhlú sa leagan seo, agus is é an príomhthaifead ná “cuntas”:

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

Maidir leis an tsamhail, cuirfimid ár sonraí tástála isteach go simplí, ach cuimhnigh - COPY níos éifeachtaí!

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

Ar dtús, dírímid aird ar na “ciorruithe” sin dá dtagraíonn ár “fíricí”. Inár gcás, tagraíonn sonraisc do chustaiméirí:

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

Chun cuntais a chomhcheangal i gceart le haitheantas custaiméirí, ní mór dúinn ar dtús na haitheantóirí seo a fháil amach nó a ghiniúint. Cuirimis réimsí fúthu:

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

Úsáidfimid an modh sioncrónaithe tábla a bhfuil cur síos air thuas le leasú beag - ní nuashonróimid nó ní scriosfaimid aon rud sa sprioctábla, toisc go n-iompórtálaimid cliaint “aguisín amháin”:

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

I ndáiríre, tá gach rud i invoice_import Anois tá an réimse teagmhála líonta againn client_id, lena gcuirfimid isteach an sonrasc.

Foinse: will.com

Add a comment