ProHoster > Blog > Administrasyon an > DBA: avèk konpetans òganize senkronizasyon ak enpòtasyon

DBA: avèk konpetans òganize senkronizasyon ak enpòtasyon

Pou pwosesis konplèks nan seri done gwo (diferan Pwosesis ETL: enpòtasyon, konvèsyon ak senkronizasyon ak yon sous ekstèn) souvan gen yon bezwen tanporèman "sonje" epi imedyatman byen vit pwosesis yon bagay volumineuz.

Yon travay tipik nan kalite sa a anjeneral son yon bagay tankou sa a: "Jwa isit la Depatman kontablite dechaje nan bank kliyan an dènye peman yo te resevwa, ou bezwen byen vit telechaje yo sou sit entènèt la epi konekte yo ak kont ou yo"

Men, lè volim nan "yon bagay" sa a kòmanse mezire nan dè santèn de megabyte, ak sèvis la dwe kontinye travay ak baz done a 24 x 7, anpil efè segondè leve ki pral ruine lavi ou.
DBA: avèk konpetans òganize senkronizasyon ak enpòtasyon
Pou fè fas ak yo nan PostgreSQL (e pa sèlman nan li), ou ka itilize kèk optimize ki pral pèmèt ou trete tout bagay pi vit epi ak mwens konsomasyon resous.

1. Ki kote yo bato?

Premyèman, ann deside ki kote nou ka telechaje done nou vle "trete".

1.1. Tablo tanporè (TANPORANY TABLE)

Nan prensip, pou PostgreSQL tab tanporè yo se menm jan ak nenpòt lòt. Se poutèt sa, sipèstisyon tankou "Tout bagay la yo estoke sèlman nan memwa, epi li ka fini". Men, gen tou plizyè diferans enpòtan.

Pwòp "namespace" ou pou chak koneksyon ak baz done a

Si de koneksyon eseye konekte an menm tan CREATE TABLE x, Lè sa a, yon moun pral definitivman jwenn erè ki pa inik objè baz done yo.

Men, si tou de eseye egzekite CREATE TEMPORARY TABLE x, Lè sa a, tou de pral fè li nòmalman, ak tout moun pral jwenn kopi ou tab. Epi pa pral gen anyen an komen ant yo.

"Oto-destriksyon" lè dekonekte

Lè koneksyon an fèmen, tout tab tanporè yo otomatikman efase, kidonk manyèlman DROP TABLE x pa gen okenn pwen eksepte...

Si w ap travay nan pgbouncer nan mòd tranzaksyon, Lè sa a, baz done a kontinye kwè ke koneksyon sa a toujou aktif, ak nan li tab tanporè sa a toujou egziste.

Se poutèt sa, eseye kreye li ankò, ki soti nan yon koneksyon diferan ak pgbouncer, sa pral lakòz yon erè. Men, sa a ka kontourne lè l sèvi avèk CREATE TEMPORARY TABLE IF NOT EXISTS x.

Se vre, li pi bon pou pa fè sa de tout fason, paske Lè sa a, ou ka "toudenkou" jwenn done ki rete nan "pwopriyetè anvan an". Olye de sa, li pi bon pou li manyèl la epi wè ke lè w ap kreye yon tab li posib pou ajoute ON COMMIT DROP - se sa ki, lè tranzaksyon an fini, tab la pral otomatikman efase.

Non-replication

Paske yo fè pati sèlman nan yon koneksyon espesifik, tab tanporè yo pa repwodui. Men sa a elimine nesesite pou anrejistreman doub nan done an pil + WAL, kidonk INSERT/UPDATE/DELETE nan li se siyifikativman pi vit.

Men, depi yon tab tanporè se toujou yon tab "prèske òdinè", li pa ka kreye sou yon kopi tou. Omwen pou kounye a, byenke patch ki koresponn lan ap sikile depi lontan.

1.2. UNLOGGED TABLE

Men, kisa ou ta dwe fè, pou egzanp, si ou gen yon kalite pwosesis ETL ankonbran ki pa ka aplike nan yon sèl tranzaksyon, men ou toujou genyen. pgbouncer nan mòd tranzaksyon? ..

Oswa koule done a tèlman gwo ke Pa gen ase Pleasant sou yon sèl koneksyon soti nan yon baz done (li, yon pwosesis pou chak CPU)? ..

Oswa kèk operasyon k ap pase asynchrone nan diferan koneksyon?...

Gen yon sèl opsyon isit la - tanporèman kreye yon tab ki pa tanporè. Pun, wi. Sa vle di:

  • kreye "pwòp mwen" tab ak non maksimòm o aza pou yo pa kwaze ak nenpòt moun
  • Èkstre: ranpli yo ak done ki soti nan yon sous ekstèn
  • Transfòme: konvèti, ranpli nan jaden kle ki lye
  • Chaj: vide done pare nan tab sib
  • efase tab "mwen".

Epi, koulye a - yon vole nan odè a. An reyalite, tout ekri nan PostgreSQL rive de fwa - premye nan WAL, Lè sa a, nan kò yo tab / endèks. Tout bagay sa yo fè pou sipòte ACID ak kòrèk vizibilite done ant COMMIT'nutty ak ROLLBACK'tranzaksyon nil.

Men nou pa bezwen sa! Nou gen tout pwosesis la Swa li te konplètman siksè oswa li pa t '.. Li pa enpòtan konbyen tranzaksyon entèmedyè pral genyen - nou pa enterese nan "kontinye pwosesis la soti nan mitan an," espesyalman lè li pa klè kote li te ye.

Pou fè sa, devlopè yo PostgreSQL, tounen nan vèsyon 9.1, prezante yon bagay tankou UNLOGGED tab:

Avèk endikasyon sa a, tab la kreye kòm dekonekte. Done ki ekri sou tab ki pa anrejistre yo pa ale nan repòtaj ekri davans la (gade Chapit 29), sa ki lakòz tab sa yo travay pi vit pase nòmal. Sepandan, yo pa iminitè kont echèk; an ka ta gen echèk sèvè oswa fèmen ijans, yon tab unlogged otomatikman tronke. Anplis de sa, sa ki nan tab la unlogged pa repwodui nan sèvè esklav. Nenpòt endis ki te kreye sou yon tab dekonekte otomatikman vin dekonekte.

Nan ti bout tan li pral pi vit, men si sèvè baz done a "tonbe", li pral dezagreyab. Men, konbyen fwa sa rive, epi èske pwosesis ETL ou a konnen ki jan yo korije sa a kòrèkteman "soti nan mitan an" apre "revitalize" baz done a?..

Si ou pa, ak ka ki anwo a sanble ak pa w la, sèvi ak UNLOGGEDmen pa janm pa pèmèt atribi sa a sou tab reyèl, done ki soti nan ki renmen ou.

1.3. SOU COMMIT { DELETE ROWS | DROP}

Konstriksyon sa a pèmèt ou presize konpòtman otomatik lè yon tranzaksyon fini lè w ap kreye yon tab.

sou ON COMMIT DROP Mwen deja ekri pi wo a, li jenere DROP TABLE, men avèk ON COMMIT DELETE ROWS sitiyasyon an pi enteresan - li se pwodwi isit la TRUNCATE TABLE.

Depi tout enfrastrikti pou estoke meta-deskripsyon yon tab tanporè se egzakteman menm jan ak yon tab regilye, lè sa a Konstan kreyasyon ak efase tab tanporè mennen nan "anfle" grav nan tab sistèm yo pg_class, pg_attribute, pg_attrdef, pg_depend,...

Koulye a, imajine ke ou gen yon travayè sou yon koneksyon dirèk nan baz done a, ki ouvè yon nouvo tranzaksyon chak segonn, kreye, ranpli, trete ak efase yon tab tanporè ... Pral gen yon depase fatra akimile nan tab yo sistèm, ak sa a pral lakòz fren siplemantè pou chak operasyon.

An jeneral, pa fè sa! Nan ka sa a li pi efikas CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS retire li nan sik tranzaksyon an - Lè sa a, nan kòmansman chak nouvo tranzaksyon tab yo deja pral egziste (sove yon apèl CREATE), men pral vid, Gras a TRUNCATE (nou tou sove apèl li) lè w fin ranpli tranzaksyon anvan an.

1.4. LIKE... KI ENKLI...

Mwen te mansyone nan kòmansman an ke youn nan ka yo itilize tipik pou tab tanporè se divès kalite enpòtasyon - ak pwomotè a fatigeman kopye-kopi lis la nan jaden nan tab la sib nan deklarasyon an nan tanporè li ...

Men parès se motè pwogrè! Se poutèt sa kreye yon nouvo tab "ki baze sou echantiyon" li ka pi senp:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Depi lè sa a ou ka jenere yon anpil nan done nan tablo sa a, rechèch nan li pa janm pral rapid. Men, gen yon solisyon tradisyonèl sa a - endèks! Epi, wi, yon tab tanporè ka gen tou endèks.

Depi, souvan, endèks ki nesesè yo kowenside ak endis yo nan tab la sib, ou ka senpleman ekri LIKE target_table INCLUDING INDEXES.

Si ou bezwen tou DEFAULT-valè (pou egzanp, ranpli valè kle prensipal yo), ou ka itilize LIKE target_table INCLUDING DEFAULTS. Oswa tou senpleman - LIKE target_table INCLUDING ALL - kopi default, endèks, kontrent, ...

Men, isit la ou bezwen konprann ke si ou te kreye enpòte tab imedyatman ak endèks, Lè sa a, done yo pral pran plis tan pou chajepase si ou premye ranpli tout bagay moute, epi sèlman Lè sa a, woule endis yo - gade nan ki jan li fè sa kòm yon egzanp pg_dump.

An jeneral RTFM!

2. Ki jan yo ekri?

Kite m jis di - sèvi ak li COPY-flow olye de "pake" INSERT, akselerasyon pafwa. Ou ka menm dirèkteman nan yon dosye pre-pwodwi.

3. Ki jan yo trete?

Se konsa, kite intro nou an gade yon bagay tankou sa a:

  • ou gen yon tab ak done kliyan ki estoke nan baz done ou a 1M dosye
  • chak jou yon kliyan voye yon nouvo pou ou "imaj" konplè
  • de eksperyans ou konnen sa de tan zan tan pa gen plis pase 10K dosye yo chanje

Yon egzanp klasik nan yon sitiyasyon konsa se KLADR baz — gen anpil adrès nan total, men nan chak telechajman chak semèn gen anpil chanjman (nomen non koloni yo, konbine lari, aparans nan nouvo kay) menm sou yon echèl nasyonal.

3.1. Algorithm senkronizasyon konplè

Pou senplisite, an n di ke ou pa menm bezwen restriktire done yo - jis pote tab la nan fòm ou vle a, se sa ki:

  • retire tout sa ki pa egziste ankò
  • mete ajou tout sa ki te deja egziste e ki bezwen mete ajou
  • insert tout sa ki poko rive

Poukisa operasyon yo ta dwe fèt nan lòd sa a? Paske se konsa gwosè tab la ap grandi yon minimòm (sonje sou MVCC!).

Efase nan dst

Non, nan kou ou ka jwenn ak jis de operasyon:

  • retire (DELETE) tout bagay an jeneral
  • insert tout soti nan nouvo imaj la

Men an menm tan, gras ak MVCC, Gwosè tab la ap ogmante egzakteman de fwa! Jwenn +1M imaj nan dosye ki nan tablo a akòz yon aktyalizasyon 10K se konsa-konsa redondans...

TRONKE dst

Yon pwomotè ki gen plis eksperyans konnen ke tout tablèt la ka netwaye byen bon mache:

  • klè (TRUNCATE) tout tab la
  • insert tout soti nan nouvo imaj la

Metòd la efikas, pafwa byen aplikab, men gen yon pwoblèm... Nou pral ajoute dosye 1M pou yon tan long, kidonk nou pa gen mwayen pou kite tab la vid pandan tout tan sa a (jan sa pral rive san yo pa vlope li nan yon sèl tranzaksyon).

Ki vle di:

  • nap komanse tranzaksyon ki dire lontan
  • TRUNCATE enpoze Aksè eksklizif-bloke
  • nou fè ensèsyon an pou yon tan long, ak tout lòt moun nan moman sa a pa menm kapab SELECT

Yon bagay pa mache byen...

ALTER TABLE... RENOME... / DROP TABLE...

Yon altènatif se ranpli tout bagay nan yon nouvo tab separe, epi tou senpleman chanje non li nan plas ansyen an. Yon koup nan ti bagay anbarasan:

  • toujou tou Aksè eksklizif, byenke siyifikativman mwens tan
  • tout plan demann/estatistik pou tablo sa a yo reset, bezwen kouri ANALYZE
  • tout kle etranje yo kase (FK) sou tab la

Te gen yon patch WIP soti nan Simon Riggs ki sijere fè ALTER-yon operasyon pou ranplase kò tab la nan nivo dosye a, san yo pa manyen estatistik ak FK, men li pa t kolekte kowòm.

EFASE, MIZAJOU, ENTER

Se konsa, nou rezoud sou opsyon ki pa bloke nan twa operasyon. Prèske twa... Ki jan fè sa a pi efikas?

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

Nan menm KLADR a, tout dosye ki chanje yo dwe anplis pase nan apre-pwosesis - nòmalize, mo kle make, epi redwi nan estrikti ki nesesè yo. Men, ki jan ou fè konnen - kisa egzakteman chanjesan yo pa konplike kòd la senkronizasyon, depreferans san yo pa manyen li nan tout?

Si sèlman pwosesis ou a gen aksè ekri nan moman senkronizasyon an, Lè sa a, ou ka itilize yon deklanche ki pral kolekte tout chanjman yo pou nou:

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

Koulye a, nou ka aplike deklanche anvan yo kòmanse senkronizasyon (oswa pèmèt yo atravè 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();

Lè sa a, nou avèk kalm ekstrè tout chanjman nou bezwen nan tab boutèy demi lit yo epi kouri yo atravè lòt moun kap okipe yo.

3.3. Enpòte ansanm ki lye

Pi wo pase nou konsidere ka lè estrikti done sous la ak destinasyon yo se menm bagay la. Men, e si upload ki soti nan yon sistèm ekstèn gen yon fòma diferan de estrikti depo nan baz done nou an?

Ann pran kòm egzanp depo kliyan yo ak kont yo, opsyon klasik "anpil-a-yon":

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

Men, telechaje a soti nan yon sous ekstèn vini nan nou nan fòm lan nan "tout nan yon sèl":

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

Li evidan, done kliyan yo ka kopi nan vèsyon sa a, ak dosye prensipal la se "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

Pou modèl la, nou pral tou senpleman mete done tès nou an, men sonje - COPY pi 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);

Premyèman, ann mete aksan sou "koupe" sa yo ke "reyalite" nou yo fè referans. Nan ka nou an, fakti yo refere a kliyan:

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

Pou nou asosye kòrèkteman kont ak ID kliyan, nou bezwen premye chèche konnen oswa jenere idantifyan sa yo. Ann ajoute jaden anba yo:

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

Ann sèvi ak metòd senkronizasyon tab ki dekri pi wo a ak yon ti amannman - nou pa pral mete ajou oswa efase anyen nan tablo sib la, paske nou enpòte kliyan "sèlman ajoute":

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

Aktyèlman, tout bagay se nan invoice_import Koulye a, nou gen jaden an kontak ranpli client_id, ak ki nou pral mete fakti a.

Sous: www.habr.com

Add nouvo kòmantè