🥇DBA: zvine hunyanzvi kuronga kuwiriranisa uye kunze kwenyika

Nekuomarara kugadzirisa kwemahombe data seti (yakasiyana ETL maitiro: kunze kwenyika, shanduko uye kuwiriranisa neyekunze sosi) kazhinji pane chinodiwa kwenguva pfupi "rangarira" uye nekukurumidza gadzira chinhu chakakura.

Basa rakajairwa rerudzi urwu rinowanzoita seizvi: "Ikoko dhipatimendi reakaunzi rakaburitswa kubva kubhangi revatengi mubhadharo wekupedzisira wagamuchirwa, unofanirwa kukurumidza kuaisa kuwebhusaiti uye woabatanidza kumaakaundi ako."

Asi kana vhoriyamu yeichi "chimwe chinhu" ichitanga kuyera mumazana ema megabytes, uye sevhisi inofanirwa kuramba ichishanda neiyo 24x7 dhatabhesi, yakawanda mhedzisiro inomuka iyo ichaparadza hupenyu hwako.

Kuti uite navo muPostgreSQL (uye kwete mairi chete), unogona kushandisa mamwe magadzirirwo ayo anozobvumidza iwe kugadzirisa zvese nekukurumidza uye nekushomeka kwekushandisa zviwanikwa.

1. Ndekupi kutumira?

Kutanga, ngationei kwatingaisa iyo data yatinoda "kugadzirisa."

1.1. Matafura enguva pfupi (TEMPORARY TABLE)

Mumusimboti, wePostgreSQL matafura enguva pfupi akafanana nechero chero. Naizvozvo, mashura akadai “Zvese zviripo zvinongochengetwa mundangariro, uye zvinogona kupera”. Asi kunewo misiyano yakawanda inokosha.

Yako wega "namespace" yekubatana kwega kwega kune database

Kana zvibatanidza zviviri zvinoedza kubatana panguva imwe chete CREATE TABLE x, ipapo mumwe munhu achawana zvechokwadi isiri-yakasarudzika kukanganisa Database zvinhu.

Asi kana vaviri vakaedza kuita CREATE TEMPORARY TABLE x, ipapo vose vachazviita zvakajairika, uye munhu wose achawana kopi yako matafura. Uye hapazovi nechinhu chakafanana pakati pavo.

"Kuzviparadza" kana uchibvisa

Kana iyo yekubatanidza yakavharwa, ese matafura enguva pfupi anobviswa otomatiki, saka nemaoko DROP TABLE x hapana chinobuda kunze...

Kana uri kushanda kuburikidza pgbouncer mune transaction mode, ipapo dhatabhesi inoramba ichitenda kuti kubatana uku kuchiri kushanda, uye mairi iyi tafura yenguva pfupi ichiripo.

Naizvozvo, kuyedza kuigadzira zvakare, kubva kune yakasiyana yekubatanidza kune pgbouncer, zvinoguma nekukanganisa. Asi izvi zvinogona kudziviswa nekushandisa CREATE TEMPORARY TABLE IF NOT EXISTS x.

Ichokwadi, zviri nani kusaita izvi zvakadaro, nokuti ipapo iwe unogona "kamwe kamwe" kuwana ipapo data yakasara kubva kune "muridzi wekare". Pane kudaro, zviri nani kuverenga bhuku rekushandisa uye ona kuti kana uchigadzira tafura zvinokwanisika kuwedzera ON COMMIT DROP - ndiko kuti, kana kutengeserana kwapera, tafura inobviswa otomatiki.

Kusadzokorora

Nekuti iwo ndeechechete chekubatanidza, matafura enguva pfupi haana kudzokororwa. Asi izvi zvinobvisa kudiwa kwekurekodha kaviri data mumurwi + WAL, saka INSERT/UPDATE/DELETE mairi inokurumidza kukurumidza.

Asi sezvo tafura yenguva pfupi ichiri "inenge yakajairika" tafura, haigone kugadzirwa pane replica zvakare. Zvirinani parizvino, kunyangwe chigamba chinoenderana chave kutenderera kwenguva yakareba.

1.2. TAFURA IRI KUSVIRWA

Asi chii chaunofanira kuita, semuenzaniso, kana uine imwe mhando yeinorema ETL maitiro ayo asingagone kuitwa mukati mekutengesa kumwe chete, asi uchine pgbouncer mune transaction mode? ..

Kana kuyerera kwedata kwakakura zvekuti Iko hakuna bandwidth yakakwana pane imwe yekubatanidza kubva kune dhatabhesi (verenga, imwe maitiro paCPU)?..

Kana kuti mamwe maoparesheni ari kuitika asynchronously mukubatana kwakasiyana?..

Pane sarudzo imwe chete pano - gadzira kwenguva pfupi tafura isiri yenguva pfupi. Pun, hongu. Ndiko kuti:

ndakagadzira "zvangu" matafura ane mazita akawandisa asina kujairika kuitira kuti asapindirane nemunhu
wabvisa: akaazadza nedata kubva kune yekunze sosi
Shanduko: yakashandurwa, yakazadzwa mumakiyi ekubatanidza minda
mutoro: yakadururira data yakagadzirira mumatafura anotangwa
dzakadzimwa “dzangu” matafura

Uye zvino - nhunzi mumafuta. Saizvozvo, zvese zvinonyora muPostgreSQL zvinoitika kaviri - kutanga muWAL, wozopinda mutafura/index miviri. Zvese izvi zvinoitirwa kutsigira ACID uye kugadzirisa data kuoneka pakati COMMIT'nutty uye ROLLBACK'null transactions.

Asi isu hatidi izvi! Tine maitiro ese Pamwe yakabudirira zvachose kana kuti haina.. Hazvina mhosva kuti vangani vepakati kutengeserana kuchave - isu hatifarire "kuenderera mberi nemaitiro kubva pakati," kunyanya kana zvisiri pachena kuti yaive kupi.

Kuti uite izvi, vanogadzira PostgreSQL, kumashure mushanduro 9.1, vakaunza chinhu chakadai MAtafura asina kunyorwa:

Nechiratidzo ichi, tafura inogadzirwa seisina kurogwa. Dhata yakanyorerwa matafura asina kurogwa haifambi nekunyora-mberi log (ona Chitsauko 29), zvichiita kuti matafura akadaro shanda nekukurumidza kudarika nguva dzose. Zvisinei, havasati vachizokundikana; muchiitiko chekutadza kweseva kana kudzima kwechimbichimbi, tafura isina kunyorwa automatic truncated. Uyezve, zviri mukati metafura isina kurogwa kwete kudzokororwa kuvaranda. Chero ma indexes akagadzirwa patafura isina kurogwa anobva angoburitswa.

Muchidimbu zvichakurumidza kukurumidza, asi kana sevha yedatabase "inodonha", zvichange zvisingafadzi. Asi izvi zvinoitika kangani, uye maitiro ako eETL anoziva kugadzirisa izvi nemazvo "kubva pakati" mushure me "kumutsidzira" dhatabhesi?..

Kana zvisiri, uye nyaya iri pamusoro yakafanana neyako, shandisa UNLOGGEDasi kwete usagonesa hunhu uhu pamatafura chaiwo, iyo data kubva iyo inodikanwa kwauri.

1.3. PAKUITA { DELETE ROWS | DONHEDZA}

Ichi chivakwa chinokutendera kuti utaure otomatiki maitiro kana kutengeserana kwapera paunenge uchigadzira tafura.

pamusoro ON COMMIT DROP Ndakatonyora pamusoro, inogadzira DROP TABLE, asi ne ON COMMIT DELETE ROWS mamiriro acho anonyanya kufadza - anogadzirwa pano TRUNCATE TABLE.

Sezvo zvese zvivakwa zvekuchengeta meta-tsananguro yetafura yenguva yakangofanana neiyo yetafura yenguva dzose, ipapo. Kusikwa nguva dzose uye kubviswa kwematafura enguva pfupi kunotungamirira ku "kuzvimba" kwakanyanya kwematafura ehurongwa pg_class, pg_attribute, pg_attrdef, pg_depend,…

Iye zvino fungidzira kuti iwe une mushandi pane zvakananga kubatana kune database, iyo inovhura kutengeserana kutsva sekondi yega yega, inogadzira, inozadza, inogadzirisa uye inobvisa tafura yenguva pfupi ... Pachava nekuwanda kwemarara akaunganidzwa mumatafura ehurongwa, uye izvi zvichakonzera mamwe mabhureki ega ega oparesheni.

Kazhinji, usaite izvi! Muchiitiko ichi inonyanya kushanda CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS ibudise kunze kweiyo transaction cycle - ipapo nekutanga kweimwe neimwe itsva transaction matafura atove zvichavapo (save a call CREATE), asi ichave isina chinhu, ndatenda TRUNCATE (isu takachengetawo kufona kwayo) pakupedza kutengeserana kwapfuura.

1.4. LIKE...KUsanganisira...

Ini ndakataura pakutanga kuti imwe yeanowanzo shandisa matafura enguva pfupi marudzi akasiyana ekunze - uye mugadziri aneta anoteedzera rondedzero yeminda yetafura inotangwa mukuzivisa kwake kwenguva pfupi ...

Asi usimbe ndiyo injini yekufambira mberi! Ndosaka gadzira tafura itsva "yakavakirwa pamuenzaniso" zvinogona kunge zviri nyore:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Sezvo iwe uchizogona kugadzira data rakawanda mutafura iyi, kutsvaga kuburikidza nayo hakuzombofa kwakakurumidza. Asi pane mhinduro yechinyakare kune iyi - indexes! Uye, hongu, tafura yenguva pfupi inogonawo kuva nema indexes.

Sezvo, kazhinji, ma index anodiwa achienderana nema indexes etafura inotarirwa, unogona kungonyora LIKE target_table INCLUDING INDEXES.

Kana uchidawo DEFAULT-values (semuenzaniso, kuzadza iwo ekutanga makiyi kukosha), unogona kushandisa LIKE target_table INCLUDING DEFAULTS. Kana nyore - LIKE target_table INCLUDING ALL - makopi defaults, indexes, constraints, ...

Asi pano unofanira kunzwisisa kuti kana wakasika pinza tafura nekukasira nemaindex, ipapo iyo data inotora nguva yakareba kurodhakupfuura kana iwe ukatanga kuzadza zvese kumusoro, uye chete wozopeta ma indexes - tarisa kuti zvinozviita sei izvi semuenzaniso pg_dump.

Muchidimbu, RTFM!

2. Kunyora sei?

Rega nditaure - shandisa COPY-yerera pachinzvimbo che "pakiti" INSERT, kukurumidza dzimwe nguva. Iwe unogona kunyange zvakananga kubva kune pre-yakagadzirwa faira.

3. Nzira yekugadzirisa sei?

Saka, ngatiregei yedu intro itaridzike seizvi:

une tafura ine data yemutengi yakachengetwa mudhatabhesi rako 1M zvinyorwa
zuva nezuva mutengi anokutumira imwe itsva zvizere "mufananidzo"
kubva pane zvakaitika munozviziva kuti nguva nenguva hapana kupfuura 10K zvinyorwa zvinoshandurwa

Muenzaniso wekare wemamiriro ezvinhu akadaro KLADR base - kune akawanda kero muhuwandu, asi mune yega yega yevhiki yekurodha kune mashoma mashoma shanduko (kutumidza zita rekugara, kubatanidza migwagwa, kutaridzika kwedzimba nyowani) kunyangwe pamwero wenyika.

3.1. Yakazara synchronization algorithm

Kuti zvive nyore, ngatiti iwe hautombodi kugadzirisa iyo data - ingounza tafura muchimiro chaunoda, ndiko kuti:

bvisa zvese zvisisipo
kugadzirisa zvese zvanga zviripo uye zvinoda kuvandudzwa
isa zvese zvisati zvaitika

Sei maoparesheni achifanira kuitwa nenzira iyi? Nekuti iyi ndiyo nzira iyo saizi yetafura inokura zvishoma (rangarira MVCC!).

DELETE KUBVA dst

Aiwa, hongu iwe unogona kupfuurira nemaitiro maviri chete:

bvisa (DELETE) zvese zvakazara
isa zvese kubva pamufananidzo mutsva

Asi panguva imwe chete, nekuda kweMVCC, Saizi yetafura ichawedzera zvakapetwa kaviri! Kuwana + 1M mifananidzo yemarekodhi patafura nekuda kweiyo 10K yekuvandudza saka-saka redundancy ...

TRUNCATE dst

Mugadziri ane ruzivo anoziva kuti piritsi rese rinogona kucheneswa zvakachipa:

pachena (TRUNCATE) tafura yose
isa zvese kubva pamufananidzo mutsva

Iyo nzira inoshanda, dzimwe nguva zvinoshanda chaizvo, asi pane dambudziko ... Tichave tichiwedzera zvinyorwa zve1M kwenguva yakareba, saka hatigoni kusiya tafura isina chinhu kwenguva yose iyi (sezvichaitika pasina kuiputira mune imwe kutengeserana).

Zvinoreva kuti:

tiri kutanga kutengeserana kwenguva refu
TRUNCATE zvinomanikidza AccessExclusive-kuvharira
tinoita yekuisa kwenguva yakareba, uye nevamwe vese panguva ino handikwanise SELECT

Pane zvisiri kufamba zvakanaka...

ALTER TABLE... RENAME... / DROP TABLE...

Imwe nzira ndeyekuzadza zvese mutafura nyowani yakaparadzana, wobva waitumidza zita pachinzvimbo chekare. Zviviri zvezvinhu zvidiki zvakashata:

zvakadaro zvakare AccessExclusive, kunyange zvazvo nguva shoma zvikuru
zvirongwa zvese zvemubvunzo/zviverengero zvetafura iyi zvinogadzikiswa, zvinoda kumhanya ANALYZE
kiyi dzose dzekunze dzakaputswa (FK) kutafura

Paive neWIP chigamba kubva kuna Simon Riggs yaikurudzira kugadzira ALTER-kuvhiya kutsiva muviri wetafura padanho refaira, pasina kubata nhamba uye FK, asi haina kuunganidza quorum.

DELETE, UPDATE, INSERT

Saka, isu tinogadzirisa pane isiri-yekuvhara sarudzo yeatatu maoperation. Vanenge vatatu ... Nzira yekuita izvi zvakanyanya sei?

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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. Import post-processing

MuKLADR imwe chete, marekodhi ese akachinjwa anofanirwa kufambiswazve kuburikidza nekugadzirisa-kumashure - akajairwa, mazwi akakosha anosimbiswa, uye kuderedzwa kune anodiwa zvimiro. Asi unoziva sei - chii chaizvo chachinjapasina kuomesa kodhi yekubatanidza, zvakanaka pasina kuibata zvachose?

Kana chete maitiro ako aine mukana wekunyora panguva yekuwiriranisa, saka unogona kushandisa chinokonzeresa chinozounganidza zvese shanduko kwatiri:

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

Iye zvino isu tinogona kuisa zvinokonzeresa tisati tatanga kuwiriranisa (kana kuvagonesa kuburikidza 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();

Uye tobva tabvisa zvinyararire shanduko dzese dzatinoda kubva pamatafura elogi uye todzimhanyisa kuburikidza neakawedzera mabati.

3.3. Kupinza Akabatanidzwa Sets

Pamusoro takafunga nyaya kana zvimiro zvedata zvekwakabva uye kwekuenda zvakafanana. Asi ko kana iyo yekukwirisa kubva kune yekunze sisitimu ine fomati yakasiyana neyekuchengetera chimiro mune yedu database?

Ngatitorei semuenzaniso kuchengetwa kwevatengi uye maakaundi avo, iyo yekare "vazhinji-kune-imwe" sarudzo:

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

Asi kudhawunirodha kubva kune ekunze sosi kunouya kwatiri nenzira ye "zvese mune imwe":

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

Zviripachena, data revatengi rinogona kudhindwa mune iyi vhezheni, uye rekodhi huru i "account":

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

Kune modhi, isu tinongoisa data redu rekuyedza, asi rangarira - COPY zvakanyanya kushanda!

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

Chekutanga, ngatiratidzei “zvichekwa” izvo zvinorehwa ne“chokwadi” chedu. Mune yedu kesi, invoice inoreva vatengi:

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

Kuti tinyatso batanidza maakaundi nema ID evatengi, isu chekutanga tinoda kuziva kana kugadzira zviziviso izvi. Ngatiwedzere minda pasi pavo:

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

Ngatishandisei nzira yekubatanidza tafura yakatsanangurwa pamusoro negadziriso diki - isu hatingagadzirise kana kudzima chero chinhu chiri patafura yakanangwa, nekuti isu tinopinza vatengi "append-chete":

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

Muchokwadi, zvese zviri mukati invoice_import Iye zvino tave nenzvimbo yekubatanidza yakazadzwa client_id, yatichaisa nayo invoice.

Source: www.habr.com

DBA: zvine hunyanzvi kuronga kuwiriranisa uye kunze kwenyika