ProHoster > Blog > Uprava > DBA: kompetentno organizirati sinhronizacije in uvoze

DBA: kompetentno organizirati sinhronizacije in uvoze

Za kompleksno obdelavo velikih nizov podatkov (razl ETL procesi: uvozi, pretvorbe in sinhronizacija z zunanjim virom) pogosto obstaja potreba začasno »zapomni« in takoj hitro obdela nekaj obsežnega.

Tipična tovrstna naloga običajno zveni nekako takole: "Točno tukaj računovodstvo raztovorjeno iz banke stranke zadnja prejeta plačila, jih morate hitro naložiti na spletno stran in jih povezati s svojimi računi.«

A ko se obseg tega »nečesa« začne meriti v stotinah megabajtov in mora storitev še naprej delovati z bazo 24x7, se pojavijo številni stranski učinki, ki vam bodo uničili življenje.
DBA: kompetentno organizirati sinhronizacije in uvoze
Da bi se z njimi spopadli v PostgreSQL (in ne samo v njem), lahko uporabite nekaj optimizacij, ki vam bodo omogočile, da boste vse obdelali hitreje in z manjšo porabo virov.

1. Kam poslati?

Najprej se odločimo, kam lahko naložimo podatke, ki jih želimo »obdelati«.

1.1. Začasne tabele (TEMPORARY TABLE)

Načeloma so začasne tabele za PostgreSQL enake kot vse druge. Zato vraževerje kot "Vse tam je shranjeno samo v spominu in lahko se konča". Obstaja pa tudi več pomembnih razlik.

Vaš lasten »imenski prostor« za vsako povezavo z bazo podatkov

Če se dve povezavi poskušata povezati hkrati CREATE TABLE x, potem bo nekdo zagotovo dobil napaka needinstvenosti predmeti baze podatkov.

Če pa oba poskušata izvršiti CREATE TEMPORARY TABLE x, potem bosta oba to storila normalno in vsi bodo dobili vaš izvod mize. In med njima ne bo nič skupnega.

"Samouničenje" ob odklopu

Ko se povezava prekine, se vse začasne tabele samodejno izbrišejo, torej ročno DROP TABLE x ni smisla razen...

Če delate skozi pgbouncer v načinu transakcije, potem baza podatkov še naprej verjame, da je ta povezava še vedno aktivna in v njej ta začasna tabela še vedno obstaja.

Zato bo poskus ponovnega ustvarjanja iz druge povezave s pgbouncer povzročil napako. Toda to je mogoče zaobiti z uporabo CREATE TEMPORARY TABLE IF NOT EXISTS x.

Res je, da je bolje, da tega vseeno ne storite, ker potem lahko "nenadoma" najdete podatke, ki so ostali od "prejšnjega lastnika". Namesto tega je veliko bolje prebrati priročnik in videti, da je pri ustvarjanju tabele mogoče dodajati ON COMMIT DROP - to pomeni, da bo tabela samodejno izbrisana, ko bo transakcija končana.

Nepodvajanje

Ker pripadajo le določeni povezavi, se začasne tabele ne podvajajo. Ampak to odpravlja potrebo po dvojnem zapisovanju podatkov v kupu + WAL, zato je INSERT/UPDATE/DELETE vanj veliko hitrejši.

Ker pa je začasna tabela še vedno »skoraj navadna« tabela, je tudi na replici ni mogoče ustvariti. Vsaj za zdaj, čeprav ustrezen popravek kroži že dolgo.

1.2. NEPRIJAVLJENA TABELA

Toda kaj storiti, na primer, če imate nekakšen okoren postopek ETL, ki ga ni mogoče implementirati v eni transakciji, vendar imate še vedno pgbouncer v načinu transakcije? ..

Ali pa je pretok podatkov tako velik, da Na eni povezavi ni dovolj pasovne širine iz baze podatkov (beri, en proces na CPU)?..

Ali pa potekajo kakšne operacije asinhrono v različnih povezavah?..

Tukaj je samo ena možnost - začasno ustvarite nezačasno tabelo. Pun, ja. To je:

  • ustvaril "moje" tabele z maksimalno naključnimi imeni, da se ne bi križal z nikomer
  • Izvleček: napolnil jih je s podatki iz zunanjega vira
  • Transform: pretvorjeno, izpolnjena ključna povezovalna polja
  • Obremenitev: prelil pripravljene podatke v ciljne tabele
  • izbrisal "moje" tabele

In zdaj - muha v mazilu. Pravzaprav, vsi zapisi v PostgreSQL se zgodijo dvakrat - prvi v WAL, nato pa v telo tabele/indeksa. Vse to je narejeno za podporo ACID in pravilno vidnost podatkov med COMMIToreh in ROLLBACK'nične transakcije.

Ampak tega ne potrebujemo! Imamo celoten postopek Ali je bilo popolnoma uspešno ali pa ne.. Ni pomembno, koliko vmesnih transakcij bo - ne zanima nas "nadaljevanje postopka od sredine", še posebej, če ni jasno, kje je bil.

Da bi to naredili, so razvijalci PostgreSQL v različici 9.1 predstavili nekaj takega, kot je NEPRIJAVLJENE tabele:

S to indikacijo je tabela ustvarjena kot neprijavljena. Podatki, zapisani v nezabeležene tabele, ne gredo skozi dnevnik vnaprejšnjega pisanja (glejte 29. poglavje), zaradi česar se takšne tabele deluje veliko hitreje kot običajno. Vendar pa niso imuni na neuspeh; v primeru okvare strežnika ali zaustavitve v sili, neprijavljena tabela samodejno okrnjena. Poleg tega vsebina nezabeležene tabele ni ponovljeno podrejenim strežnikom. Vsi indeksi, ustvarjeni v nezabeleženi tabeli, samodejno postanejo odjavljeni.

Skratka, bo veliko hitreje, če pa strežnik baze podatkov »pade«, bo neprijetno. Toda kako pogosto se to zgodi in ali zna vaš ETL proces to pravilno popraviti "od sredine" po "revitalizaciji" baze podatkov?..

Če ne in je zgornji primer podoben vašemu, uporabite UNLOGGEDampak nikoli ne omogočite tega atributa na pravih tabelah, podatki iz katerega so vam dragi.

1.3. ON COMMIT { DELETE ROWS | DROP}

Ta konstrukcija vam omogoča, da pri ustvarjanju tabele določite samodejno vedenje, ko je transakcija zaključena.

na ON COMMIT DROP Zgoraj sem že napisal, ustvarja DROP TABLE, ampak z ON COMMIT DELETE ROWS situacija je bolj zanimiva - tukaj se ustvari TRUNCATE TABLE.

Ker je celotna infrastruktura za shranjevanje metaopisa začasne tabele popolnoma enaka infrastrukturi običajne tabele, potem Nenehno ustvarjanje in brisanje začasnih tabel povzroči močno "nabrekanje" sistemskih tabel pg_class, pg_attribute, pg_attrdef, pg_depend,…

Zdaj pa si predstavljajte, da imate delavca na direktni povezavi z bazo, ki vsako sekundo odpre novo transakcijo, ustvari, polni, obdeluje in briše začasno tabelo... V sistemskih tabelah se bo nabralo preveč smeti in to bo povzročilo dodatne zavore za vsako operacijo.

Na splošno tega ne počnite! V tem primeru je veliko bolj učinkovito CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS vzeti iz transakcijskega cikla - potem so do začetka vsake nove transakcije mize že bo obstajal (shranite klic CREATE), ampak bo prazna, zahvale gredo TRUNCATE (shranili smo tudi njegov klic) ob zaključku prejšnje transakcije.

1.4. VŠEČ ... VKLJUČNO ...

Na začetku sem omenil, da je eden od tipičnih primerov uporabe začasnih tabel različne vrste uvozov - in razvijalec utrujeno kopira-prilepi seznam polj ciljne tabele v deklaracijo svoje začasne ...

Toda lenoba je motor napredka! Zato ustvarite novo tabelo "na podlagi vzorca" lahko je veliko bolj preprosto:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Ker lahko nato v to tabelo ustvarite veliko podatkov, iskanje po njej nikoli ne bo hitro. Toda za to obstaja tradicionalna rešitev - indeksi! In ja, začasna tabela ima lahko tudi indekse.

Ker pogosto zahtevani indeksi sovpadajo z indeksi ciljne tabele, lahko preprosto pišete LIKE target_table INCLUDING INDEXES.

Če tudi potrebujete DEFAULT-vrednosti (na primer za izpolnjevanje vrednosti primarnega ključa), lahko uporabite LIKE target_table INCLUDING DEFAULTS. Ali preprosto - LIKE target_table INCLUDING ALL — kopira privzete vrednosti, indekse, omejitve,...

Ampak tukaj morate razumeti, da če ste ustvarili uvozite tabelo takoj z indeksi, potem se bodo podatki nalagali dljekot če najprej vse izpolniš in šele nato zviješ indekse - poglej kako to počne na primeru pg_dump.

Na splošno RTFM!

2. Kako pisati?

Naj samo rečem - uporabite ga COPY-tok namesto "pack" INSERT, pospešek na trenutke. Lahko celo neposredno iz vnaprej ustvarjene datoteke.

3. Kako obdelati?

Torej, naj bo naš uvod videti nekako takole:

  • v vaši zbirki podatkov imate shranjeno tabelo s podatki o strankah 1 milijon zapisov
  • vsak dan vam stranka pošlje novega polna "slika"
  • iz izkušenj veš, da od časa do časa ne spremeni se več kot 10K zapisov

Klasičen primer takšne situacije je Baza KLADR — naslovov je skupaj veliko, vendar je v vsakem tedenskem nalaganju zelo malo sprememb (preimenovanje naselij, združevanje ulic, pojav novih hiš) tudi v državnem merilu.

3.1. Algoritem popolne sinhronizacije

Za poenostavitev povejmo, da vam ni treba niti prestrukturirati podatkov - samo prinesite tabelo v želeno obliko, to je:

  • odstranite vse kar ne obstaja več
  • Nadgradnja vse, kar je že obstajalo in ga je treba posodobiti
  • vstaviti vse kar se še ni zgodilo

Zakaj je treba operacije izvajati v tem vrstnem redu? Ker bo tako velikost mize minimalno narasla (zapomni si MVCC!).

IZBRIŠI IZ dst

Ne, seveda lahko preživite samo z dvema operacijama:

  • odstranite (DELETE) vse na splošno
  • vstaviti vse iz nove slike

Toda hkrati, zahvaljujoč MVCC, Velikost mize se bo povečala natanko dvakrat! Pridobivanje +1 milijona slik zapisov v tabeli zaradi posodobitve 10K je tako zelo odvečno ...

OKREŽI dst

Bolj izkušen razvijalec ve, da je mogoče celotno tablico očistiti precej poceni:

  • jasno (TRUNCATE) celotno tabelo
  • vstaviti vse iz nove slike

Metoda je učinkovita, včasih kar uporabno, vendar obstaja težava ... Dolgo časa bomo dodajali 1 milijon zapisov, zato si ne moremo privoščiti, da bi tabela ves ta čas ostala prazna (kot se bo zgodilo, ne da bi jo zavili v eno transakcijo).

Kar pomeni:

  • začenjamo dolgotrajna transakcija
  • TRUNCATE vsiljuje Ekskluziven dostop-blokiranje
  • mi delamo vstavljanje dolgo časa, vsi ostali pa v tem času niti ne morem SELECT

Nekaj ​​ne gre dobro ...

SPREMENI TABELO… PREIMENI…/IZPUSTI TABELO…

Druga možnost je, da vse izpolnite v ločeno novo tabelo in jo nato preprosto preimenujete namesto stare. Par neprijetnih malenkosti:

  • še vedno tudi Ekskluziven dostop, čeprav bistveno manj časa
  • vsi načrti poizvedb/statistika za to tabelo so ponastavljeni, morate zagnati ANALIZO
  • vsi tuji ključi so pokvarjeni (FK) k mizi

Obstajal je WIP popravek Simona Riggsa, ki je predlagal izdelavo ALTER-operacija za zamenjavo telesa tabele na ravni datoteke, brez dotika statistike in FK, vendar ni zbrala kvoruma.

IZBRIŠI, POSODOBI, VSTAVI

Torej smo se odločili za možnost treh operacij brez blokiranja. Skoraj tri ... Kako to narediti najbolj učinkovito?

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

V istem KLADR-u je treba vse spremenjene zapise dodatno speljati v naknadno obdelavo – normalizirati, poudariti ključne besede in reducirati na zahtevane strukture. Ampak kako veš - kaj točno se je spremenilobrez kompliciranja sinhronizacijske kode, idealno ne da bi se je sploh dotaknili?

Če ima samo vaš proces dostop za pisanje v času sinhronizacije, potem lahko uporabite sprožilec, ki bo zbral vse spremembe namesto nas:

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

Zdaj lahko uporabimo sprožilce pred začetkom sinhronizacije (ali jih omogočimo prek 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();

Nato mirno izvlečemo vse spremembe, ki jih potrebujemo, iz dnevniških tabel in jih poženemo skozi dodatne obdelovalce.

3.3. Uvažanje povezanih nizov

Zgoraj smo obravnavali primere, ko sta podatkovni strukturi vira in cilja enaki. Kaj pa, če ima nalaganje iz zunanjega sistema obliko, ki se razlikuje od strukture shranjevanja v naši bazi podatkov?

Vzemimo za primer shranjevanje strank in njihovih računov, klasično možnost »več proti enemu«:

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

Toda prenos iz zunanjega vira pride k nam v obliki »vse v enem«:

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

Očitno je mogoče podatke o strankah v tej različici podvojiti, glavni zapis pa je »račun«:

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

Za model bomo preprosto vstavili naše testne podatke, vendar ne pozabite - COPY bolj učinkovit!

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

Najprej izpostavimo tiste “reze”, na katere se nanašajo naša “dejstva”. V našem primeru se računi nanašajo na stranke:

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

Da lahko pravilno povežemo račune z ID-ji strank, moramo najprej poiskati ali ustvariti te identifikatorje. Pod njimi dodamo polja:

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

Uporabimo zgoraj opisano metodo sinhronizacije tabel z majhno spremembo - ne bomo posodobili ali izbrisali ničesar v ciljni tabeli, ker uvozimo odjemalce »samo za dodajanje«:

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

Pravzaprav je vse noter invoice_import Sedaj imamo kontaktno polje izpolnjeno client_id, s katerim bomo vstavili račun.

Vir: www.habr.com

Dodaj komentar