ProHoster > Blog > uprava > DBA: kompetentno organizirati sinkronizacije i uvoze

DBA: kompetentno organizirati sinkronizacije i uvoze

Za složenu obradu velikih skupova podataka (različit ETL procesi: uvozi, konverzije i sinkronizacija s vanjskim izvorom) često postoji potreba privremeno "zapamtiti" i odmah brzo obraditi nešto voluminozno.

Tipičan zadatak ove vrste obično zvuči otprilike ovako: "Upravo ovdje računovodstvo iskrcano iz banke klijenta posljednje primljene uplate, morate ih brzo učitati na web stranicu i povezati sa svojim računima.”

Ali kada se obujam tog “nečega” počne mjeriti u stotinama megabajta, a usluga mora nastaviti raditi s bazom podataka 24x7, pojavljuju se mnoge nuspojave koje će vam uništiti život.
DBA: kompetentno organizirati sinkronizacije i uvoze
Da biste se nosili s njima u PostgreSQL-u (i ne samo u njemu), možete koristiti neke optimizacije koje će vam omogućiti da sve obradite brže i s manje potrošnje resursa.

1. Gdje poslati?

Najprije odlučimo gdje možemo prenijeti podatke koje želimo "obraditi".

1.1. Privremene tablice (TEMPORARY TABLE)

U principu, za PostgreSQL privremene tablice su iste kao i sve druge. Stoga, praznovjerja poput “Sve je tamo pohranjeno samo u memoriji i može završiti”. Ali postoji i nekoliko značajnih razlika.

Vaš vlastiti “namespace” za svaku vezu s bazom podataka

Ako se dvije veze pokušaju spojiti u isto vrijeme CREATE TABLE x, onda će netko sigurno dobiti pogreška nejedinstvenosti objekti baze podataka.

Ali ako oboje pokušaju izvršiti CREATE TEMPORARY TABLE x, onda će oboje to učiniti normalno, i svi će dobiti tvoj primjerak stolovi. I neće biti ništa zajedničko između njih.

"Samouništenje" prilikom odspajanja

Kada se veza prekine, sve privremene tablice se automatski brišu, dakle ručno DROP TABLE x nema svrhe osim...

Ako radite kroz pgbouncer u načinu transakcije, tada baza podataka nastavlja vjerovati da je ta veza još uvijek aktivna i da u njoj još uvijek postoji ova privremena tablica.

Stoga će pokušaj ponovnog stvaranja s druge veze na pgbouncer dovesti do pogreške. Ali to se može zaobići korištenjem CREATE TEMPORARY TABLE IF NOT EXISTS x.

Istina, bolje je to ne činiti, jer onda tamo možete "iznenada" pronaći podatke koji su ostali od "prethodnog vlasnika". Umjesto toga, puno je bolje pročitati priručnik i vidjeti da je prilikom izrade tablice moguće dodati ON COMMIT DROP - odnosno kada se transakcija završi, tablica će se automatski izbrisati.

Nerepliciranje

Budući da pripadaju samo određenoj vezi, privremene tablice se ne repliciraju. Ali ovo eliminira potrebu za dvostrukim snimanjem podataka u heap + WAL, tako da je INSERT/UPDATE/DELETE u njega mnogo brži.

Ali budući da je privremena tablica još uvijek "gotovo obična" tablica, ne može se kreirati ni na replici. Barem za sada, iako pripadajući patch kruži već duže vrijeme.

1.2. NEZAPISUJENA TABLICA

Ali što biste trebali učiniti, na primjer, ako imate neku vrstu glomaznog ETL procesa koji se ne može implementirati unutar jedne transakcije, ali još uvijek imate pgbouncer u načinu transakcije? ..

Ili je protok podataka toliko velik da Nema dovoljno propusnosti na jednoj vezi iz baze podataka (čitaj, jedan proces po CPU-u)?..

Ili su u tijeku neke operacije asinkrono u različitim vezama?..

Ovdje postoji samo jedna opcija - privremeno stvoriti neprivremenu tablicu. Dosjetka, da. To je:

  • stvorio "moje vlastite" tablice s maksimalno nasumičnim imenima kako se ne bi presijecao ni s kim
  • Ekstrakt: napunio ih podacima iz vanjskog izvora
  • Transformirati: pretvoreno, popunjena ključna polja povezivanja
  • Opterećenje: izlio spremne podatke u ciljne tablice
  • izbrisao “moje” tablice

A sada - muha u glavi. Zapravo, sva pisanja u PostgreSQL se događaju dva puta - prvi u WAL-u, zatim u tablicu/indeks tijela. Sve je to učinjeno za podršku ACID-u i ispravnu vidljivost podataka između COMMIT'orašast i ROLLBACK'nulte transakcije.

Ali ovo nam ne treba! Imamo cijeli proces Ili je bio potpuno uspješan ili nije.. Nije važno koliko će međutransakcija biti - ne zanima nas "nastavak procesa od sredine", pogotovo kada nije jasno gdje je bio.

Da bi to učinili, programeri PostgreSQL-a, još u verziji 9.1, predstavili su nešto poput NEZAPISUJENE tablice:

S ovom indikacijom, tablica se kreira kao nezabilježena. Podaci zapisani u nezabilježene tablice ne prolaze kroz dnevnik pisanja unaprijed (vidi Poglavlje 29), uzrokujući da takve tablice raditi puno brže nego inače. Međutim, oni nisu imuni na neuspjeh; u slučaju kvara poslužitelja ili hitnog gašenja, nezabilježena tablica automatski skraćeno. Dodatno, sadržaj nezabilježene tablice nije replicirano podređenim poslužiteljima. Svi indeksi kreirani na nezabilježenoj tablici automatski postaju nezabilježeni.

Ukratko bit će mnogo brže, ali ako poslužitelj baze podataka “padne”, to će biti neugodno. Ali koliko se često to događa i zna li vaš ETL proces to ispravno ispraviti “iz sredine” nakon “revitalizacije” baze podataka?..

Ako ne, a gornji slučaj je sličan vašem, upotrijebite UNLOGGEDali nikad nemojte omogućiti ovaj atribut na stvarnim tablicama, podaci iz kojih su vam dragi.

1.3. ON COMMIT { DELETE ROWS | PAD}

Ova konstrukcija vam omogućuje da odredite automatsko ponašanje kada je transakcija dovršena prilikom izrade tablice.

na ON COMMIT DROP Već sam gore napisao, generira DROP TABLE, ali sa ON COMMIT DELETE ROWS situacija je zanimljivija - ovdje se stvara TRUNCATE TABLE.

Budući da je cijela infrastruktura za pohranjivanje meta-opisa privremene tablice potpuno ista kao kod obične tablice, tada Konstantno stvaranje i brisanje privremenih tablica dovodi do ozbiljnog "bubrenja" sistemskih tablica pg_class, pg_attribute, pg_attrdef, pg_depend,…

Sada zamislite da imate radnika na direktnoj vezi s bazom podataka koji svake sekunde otvara novu transakciju, kreira, puni, obrađuje i briše privremenu tablicu... Nagomilat će se višak smeća u sistemskim tablicama, a to će izazvati dodatne kočnice za svaku operaciju.

Općenito, nemojte to činiti! U ovom slučaju to je mnogo učinkovitije CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS izvadite iz transakcijskog ciklusa - tada su do početka svake nove transakcije tablice već postojat će (spremite poziv CREATE), ali bit će prazna, zahvaljujući TRUNCATE (također smo spremili njegov poziv) prilikom dovršetka prethodne transakcije.

1.4. LIKE...UKLJUČUJUĆI...

Na početku sam spomenuo da je jedan od tipičnih slučajeva korištenja privremenih tablica razne vrste uvoza - a programer umorno kopira i lijepi popis polja ciljne tablice u deklaraciju svoje privremene...

Ali lijenost je motor napretka! Zato izradi novu tablicu "na temelju uzorka" može biti puno jednostavnije:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Budući da tada možete generirati mnogo podataka u ovu tablicu, pretraživanje po njoj nikada neće biti brzo. Ali za to postoji tradicionalno rješenje - indeksi! i, da, privremena tablica također može imati indekse.

Budući da se traženi indeksi često podudaraju s indeksima ciljne tablice, možete jednostavno pisati LIKE target_table INCLUDING INDEXES.

Ako i vama treba DEFAULT-vrijednosti (na primjer, za popunjavanje vrijednosti primarnog ključa), možete koristiti LIKE target_table INCLUDING DEFAULTS. Ili jednostavno - LIKE target_table INCLUDING ALL — kopira zadane postavke, indekse, ograničenja,...

Ali ovdje morate shvatiti da ako ste stvorili odmah uvezite tablicu s indeksima, tada će učitavanje podataka trajati duljenego da prvo sve popuniš, pa tek onda smotaš indekse - vidi kako to radi na primjeru pg_dump.

U cjelini, RTFM!

2. Kako pisati?

Samo da kažem - iskoristite ga COPY-protok umjesto "pack" INSERT, ubrzanje na trenutke. Možete čak i izravno iz unaprijed generirane datoteke.

3. Kako obraditi?

Dakle, neka naš uvod izgleda otprilike ovako:

  • imate tablicu s podacima o klijentima pohranjenu u vašoj bazi podataka 1M zapisa
  • svaki dan vam klijent pošalje novu puna "slika"
  • iz iskustva znaš da s vremena na vrijeme ne mijenja se više od 10K zapisa

Klasičan primjer takve situacije je KLADR baza — ukupno ima puno adresa, ali u svakom tjednom uploadu ima vrlo malo promjena (preimenovanja naselja, spajanje ulica, izgled novih kuća) čak i na nacionalnoj razini.

3.1. Potpuni algoritam sinkronizacije

Radi jednostavnosti, recimo da ne morate niti restrukturirati podatke - samo dovedite tablicu u željeni oblik, to jest:

  • ukloniti sve što više ne postoji
  • ažuriranje sve što je već postojalo i treba ažurirati
  • umetnuti sve što se još nije dogodilo

Zašto bi se operacije trebale izvoditi ovim redoslijedom? Jer tako će veličina tablice minimalno rasti (zapamtite MVCC!).

IZBRIŠI IZ dst

Ne, naravno da možete proći sa samo dvije operacije:

  • ukloniti (DELETE) sve općenito
  • umetnuti sve iz nove slike

Ali u isto vrijeme, zahvaljujući MVCC-u, Veličina tablice će se povećati točno dva puta! Dobivanje +1 milijuna slika zapisa u tablici zbog ažuriranja od 10K je tako-tako redundancija...

KRITI dst

Iskusniji programer zna da se cijeli tablet može očistiti prilično jeftino:

  • čist (TRUNCATE) cijelu tablicu
  • umetnuti sve iz nove slike

Metoda je učinkovita, ponekad sasvim primjenjivo, ali postoji problem... Dugo ćemo vrijeme dodavati 1M zapisa, tako da si ne možemo priuštiti da tablica ostane prazna sve ovo vrijeme (kao što će se dogoditi bez omotavanja u jednu transakciju).

Što znači:

  • počinjemo dugotrajna transakcija
  • TRUNCATE nameće Ekskluzivni pristup-blokiranje
  • mi radimo umetanje za dugo vremena, a svi ostali u ovom trenutku ne može čak SELECT

Nešto ne ide dobro...

PROMIJENI TABLICU… PREIMENUJ… / ISPUSTI TABLICU…

Alternativa je ispuniti sve u zasebnu novu tablicu, a zatim je jednostavno preimenovati umjesto stare. Par gadnih sitnica:

  • još uvijek previše Ekskluzivni pristup, iako znatno kraće vrijeme
  • svi planovi/statistika upita za ovu tablicu su poništeni, treba pokrenuti ANALIZA
  • svi strani ključevi su pokvareni (FK) do stola

Postojala je WIP zakrpa od Simona Riggsa koja je sugerirala izradu ALTER-operacija zamjene tijela tablice na razini datoteke, bez diranja statistike i FK, ali nije prikupio kvorum.

IZBRIŠI, AŽURIRAJ, UMETNI

Dakle, odlučili smo se za opciju bez blokiranja od tri operacije. Skoro tri... Kako to učiniti najučinkovitije?

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

U istom KLADR-u sve promijenjene zapise potrebno je dodatno proći kroz postprocesiranje – normalizirati, istaknuti ključne riječi i svesti na tražene strukture. Ali kako znaš - što se točno promijenilobez kompliciranja koda za sinkronizaciju, idealno da ga uopće ne diramo?

Ako samo vaš proces ima pristup pisanju u trenutku sinkronizacije, tada možete koristiti okidač koji će prikupiti sve promjene za 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;

Sada možemo primijeniti okidače prije početka sinkronizacije (ili ih omogućiti putem 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();

A onda mirno izdvajamo sve promjene koje su nam potrebne iz tablica dnevnika i pokrećemo ih kroz dodatne rukovatelje.

3.3. Uvoz povezanih skupova

Gore smo razmotrili slučajeve kada su strukture podataka izvora i odredišta iste. Ali što ako prijenos iz vanjskog sustava ima format drugačiji od strukture pohrane u našoj bazi podataka?

Uzmimo kao primjer pohranu klijenata i njihovih računa, klasičnu opciju "više prema jednom":

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

Ali preuzimanje s vanjskog izvora dolazi nam u obliku "sve u jednom":

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

Očito, podaci o kupcima mogu se duplicirati u ovoj verziji, a glavni zapis 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 ćemo jednostavno umetnuti naše testne podatke, ali zapamtite - COPY učinkovitije!

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

Prvo, istaknimo one “rezove” na koje se odnose naše “činjenice”. U našem slučaju, fakture se odnose na kupce:

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

Kako bismo ispravno povezali račune s korisničkim ID-ovima, prvo moramo pronaći ili generirati te identifikatore. Dodajmo polja ispod njih:

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

Upotrijebimo gore opisanu metodu sinkronizacije tablice s malom dopunom - nećemo ništa ažurirati niti brisati u ciljnoj tablici jer uvozimo klijente "samo za dodavanje":

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

Zapravo, sve je unutra invoice_import Sada imamo popunjeno polje za kontakt client_id, uz koje ćemo umetnuti fakturu.

Izvor: www.habr.com

Dodajte komentar