ProHoster > Blog > Administratioun > DBA: kompetent Synchronisatiounen an Importer organiséieren

DBA: kompetent Synchronisatiounen an Importer organiséieren

Fir komplex Veraarbechtung vu groussen Datesets (verschidden ETL Prozesser: Importer, Konversiounen a Synchroniséierung mat enger externer Quell) gëtt et dacks e Besoin temporär "erënneren" an direkt séier Prozess eppes voluminös.

Eng typesch Aufgab vun dëser Aart kléngt normalerweis sou eppes: "Genau hei Comptablesmethod Departement vun der Client Bank entlooss déi lescht empfaange Bezuelungen, musst Dir se séier op d'Websäit eropluede an se op Är Konten verbannen"

Awer wann de Volume vun dësem "eppes" ufänkt an Honnerte vu Megabytes ze moossen, an de Service muss weider mat der Datebank 24x7 schaffen, entstinn vill Nebenwirkungen, déi Äert Liewen ruinéieren.
DBA: kompetent Synchronisatiounen an Importer organiséieren
Fir mat hinnen am PostgreSQL ze këmmeren (an net nëmmen an et), kënnt Dir e puer Optimisatiounen benotzen, déi Iech erlaben alles méi séier a mat manner Ressourceverbrauch ze veraarbecht.

1. Wou ze Schëff?

Als éischt, loosst eis entscheeden wou mir d'Donnéeën eropluede kënnen, déi mir wëllen "veraarbecht".

1.1. Temporär Dëscher (temporär Dëscher)

Am Prinzip, fir PostgreSQL temporär Dëscher sinn déi selwecht wéi all aner. Dofir, Iwwerglaubungen wéi "Alles do ass nëmmen an der Erënnerung gespäichert, an et kann ophalen". Awer et ginn och e puer bedeitend Differenzen.

Ären eegene "Nummraum" fir all Verbindung mat der Datebank

Wann zwou Verbindungen probéieren gläichzäiteg ze verbannen CREATE TABLE x, da kritt een definitiv Net-Eenzegaartegkeet Feeler Datebank Objete.

Awer wa béid probéieren auszeféieren CREATE TEMPORARY TABLE x, da wäerte béid et normalerweis maachen, a jidderee kritt Är Kopie Dëscher. An et wäert näischt gemeinsam tëscht hinnen ginn.

"Selbstzerstéierung" beim Trennen

Wann d'Verbindung zou ass, ginn all temporär Dëscher automatesch geläscht, also manuell DROP TABLE x et ass kee Sënn ausser ...

Wann Dir schafft duerch pgbouncer am Transaktiounsmodus, da gleeft d'Datebank weider datt dës Verbindung nach ëmmer aktiv ass, an datt dës temporär Tabell nach ëmmer existéiert.

Dofir, probéiert et erëm ze kreéieren, vun enger anerer Verbindung zum pgbouncer, féiert zu engem Feeler. Awer dëst kann ëmgoen andeems Dir benotzt CREATE TEMPORARY TABLE IF NOT EXISTS x.

Richteg, et ass besser net souwisou ze maachen, well da kënnt Dir "plötzlech" d'Donnéeën aus dem "virdrun Besëtzer" fannen. Amplaz ass et vill besser d'Handbuch ze liesen a gesinn datt wann Dir en Dësch erstellt et méiglech ass ze addéieren ON COMMIT DROP - dat ass, wann d'Transaktioun fäerdeg ass, gëtt den Dësch automatesch geläscht.

Net-Replikatioun

Well se nëmmen zu enger spezifescher Verbindung gehéieren, ginn temporär Dëscher net replizéiert. Mee dëst eliminéiert de Besoin fir duebel Opnam vun Daten am Heap + WAL, also INSERT / UPDATE / DELETE dran ass wesentlech méi séier.

Awer well en temporäre Dësch nach ëmmer en "bal normalen" Dësch ass, kann en och net op enger Replika erstallt ginn. Op d'mannst fir de Moment, obwuel de entspriechende Patch scho laang zirkuléiert.

1.2. ONLOGGED TABEL

Awer wat sollt Dir maachen, zum Beispill, wann Dir eng Aart vun ëmständleche ETL-Prozess hutt, deen net an enger Transaktioun ëmgesat ka ginn, awer Dir hutt nach ëmmer pgbouncer am Transaktiounsmodus? ..

Oder den Datefloss ass sou grouss datt Et gëtt net genuch Bandbreedung op enger Verbindung vun enger Datebank (liesen, ee Prozess pro CPU)? ..

Oder e puer Operatiounen lafen asynchron a verschiddene Verbindungen?..

Et gëtt nëmmen eng Optioun hei - temporär engem Net-temporär Dësch schafen. Pun, jo. Dat ass:

  • "meng eegen" Dëscher mat maximal zoufälleg Nimm erstallt fir net mat iergendeen ze kräizen
  • Extrait: gefëllt se mat Daten aus enger externer Quell
  • Verännerung: ëmgerechent, a Schlësselverbindungsfelder ausgefëllt
  • Lueden: gegoss prett Donnéeën an Zil- Dëscher
  • geläscht "meng" Dëscher

An elo - eng Méck an der Sallef. Tatsächlech, all Schreiwen am PostgreSQL geschitt zweemol - éischt am WAL, dann an den Dësch / Index Kierper. All dëst gëtt gemaach fir ACID z'ënnerstëtzen a korrekt Datenvisibilitéit tëscht COMMIT'nout an ROLLBACK'null Transaktiounen.

Mee mir brauchen dat net! Mir hunn de ganze Prozess Entweder et war komplett erfollegräich oder et war net.. Et ass egal wéivill Zwëschentransaktiounen et wäert sinn - mir sinn net interesséiert "de Prozess vun der Mëtt weiderzeféieren", besonnesch wann et net kloer ass wou et war.

Fir dëst ze maachen, hunn d'PostgreSQL Entwéckler, zréck an der Versioun 9.1, sou eppes agefouert wéi UNLOGGED Dëscher:

Mat dëser Indikatioun gëtt den Dësch als ongeloggt erstallt. Daten, déi op net ageloggt Dëscher geschriwwe ginn, ginn net duerch de Write-ahead Log (kuckt Kapitel 29), sou datt esou Dëscher schaffen vill méi séier wéi soss. Si sinn awer net immun géint Echec; am Fall vun Server Echec oder Noutfall ausschalten, engem unlogged Dësch automatesch ofgeschnidden. Zousätzlech, den Inhalt vun der unlogged Dësch net replizéiert zu Sklave Serveren. All Indizes, déi op engem net ageloggten Dësch erstallt ginn, ginn automatesch ofgemellt.

Kuerz gesot, et wäert vill méi séier ginn, awer wann den Datebankserver "fällt", wäert et désagréabel sinn. Awer wéi oft geschitt dat, a weess Ären ETL-Prozess wéi Dir dëst richteg "vun der Mëtt" korrigéiert nodeems Dir d'Datebank "revitaliséiert" huet?

Wann net, an de Fall hei uewen ass ähnlech wéi Ären, benotzt UNLOGGEDawer ni aktivéiert dëst Attribut net op realen Dëscher, d'Donnéeën aus deenen Dir léif ass.

1.3. ON COMMIT { LËSCHTEN REILEN | DROP}

Dëse Konstrukt erlaabt Iech automatesch Verhalen ze spezifizéieren wann eng Transaktioun fäerdeg ass wann Dir en Dësch erstellt.

op ON COMMIT DROP Ech geschriwwen schonn uewen, et generéiert DROP TABLE, awer mat ON COMMIT DELETE ROWS d'Situatioun ass méi interessant - et gëtt hei generéiert TRUNCATE TABLE.

Well déi ganz Infrastruktur fir d'Meta-Beschreiwung vun enger temporärer Tabell ze späicheren genau d'selwecht ass wéi déi vun engem normalen Dësch, dann Konstant Schafung an Läschen vun temporäre Dëscher féiert zu schwéieren "Schwellung" vu Systemtabellen pg_class, pg_attribute, pg_attrdef, pg_depend,...

Stellt Iech elo vir, datt Dir en Aarbechter op enger direkter Verbindung mat der Datebank hutt, déi all Sekonn eng nei Transaktioun opmaacht, en temporäre Dësch erstellt, fëllt, veraarbecht an läscht ... dëst wäert fir all Operatioun extra Bremsen Ursaach.

Am Allgemengen, maacht dat net! An dësem Fall ass et vill méi effektiv CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS huelt et aus dem Transaktiounszyklus - dann um Ufank vun all neier Transaktioun sinn d'Dëscher schonn wäert existéieren (späicheren en Uruff CREATE), awer wäert eidel sinn, merci un TRUNCATE (mir hunn och säin Uruff gespäichert) beim Ofschloss vun der viregter Transaktioun.

1.4. LIKE ... INKLUDERT ...

Ech hunn am Ufank gesot datt ee vun den typesche Benotzungsfäll fir temporär Dëscher verschidden Aarte vun Importer ass - an den Entwéckler kopéiert midd d'Lëscht vun de Felder vun der Ziltabelle an d'Deklaratioun vu sengem temporäre ...

Awer Lazy ass de Motor vum Fortschrëtt! Dat ass wouvir en neien Dësch erstellen "baséiert op Probe" et kann vill méi einfach sinn:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Well Dir dann vill Daten an dës Tabell generéiere kënnt, ass d'Sich duerch se ni séier. Awer et gëtt eng traditionell Léisung fir dëst - Indizes! An, jo, eng temporär Dësch kann och Index hunn.

Zënter dacks déi erfuerderlech Indizes mat den Indizes vun der Ziltabelle zesummefalen, kënnt Dir einfach schreiwen LIKE target_table INCLUDING INDEXES.

Wann Dir och braucht DEFAULT-Wäerter (zum Beispill fir déi primär Schlësselwäerter auszefëllen), kënnt Dir benotzen LIKE target_table INCLUDING DEFAULTS. Oder einfach - LIKE target_table INCLUDING ALL - kopéiert Defaults, Indizes, Contrainten, ...

Awer hei musst Dir verstoen datt wann Dir erstallt hutt importéiert Dësch direkt mat Indexen, da wäerten d'Donnéeë méi laang daueren fir ze luedenwéi wann Dir fir d'éischt alles ausfëllt, an nëmmen dann d'Indexen oprullt - kuckt wéi et dat als Beispill mécht pg_dump.

Kuerz gesot, RTFM!

2. Wéi schreift een?

Loosst mech just soen - benotzt et COPY-flow amplaz "packen" INSERT, Beschleunegung heiansdo. Dir kënnt souguer direkt vun enger pre-generéierter Datei.

3. Wéi veraarbecht?

Also loosse mer eis Intro sou eppes ausgesinn:

  • Dir hutt en Dësch mat Client Daten an Ärer Datebank gespäichert 1M records
  • all Dag schéckt e Client Iech en neien voll "Bild"
  • aus Erfahrung kennt een dat vun Zäit zu Zäit net méi wéi 10K records sinn geännert

E klassescht Beispill vun esou enger Situatioun ass KLADR Basis - et gi vill Adressen am Ganzen, awer an all wëchentlech Eroplueden ginn et ganz wéineg Ännerungen (Nennennung vu Siedlungen, Kombinéiere vu Stroossen, Erscheinung vun neien Haiser) och op nationaler Skala.

3.1. Voll Synchroniséierung Algorithmus

Fir Simplicitéit, loosst eis soen datt Dir net emol d'Donnéeën ëmstrukturéiere musst - bréngt just den Dësch an déi gewënscht Form, dat ass:

  • ze läschen alles wat net méi gëtt
  • erfrëscht alles wat schonn existéiert a muss aktualiséiert ginn
  • anzeginn alles wat nach net geschitt ass

Firwat sollen d'Operatiounen an dëser Reiefolleg gemaach ginn? Well dëst ass wéi d'Tablegréisst minimal wäert wuessen (erënneren MVCC!).

LÄSCHEN VUN dst

Nee, natierlech kënnt Dir mat nëmmen zwou Operatiounen duerchkommen:

  • ze läschen (DELETE) alles am Allgemengen
  • anzeginn alles aus dem neie Bild

Awer gläichzäiteg, dank dem MVCC, D'Gréisst vum Dësch wäert genee zweemol eropgoen! +1M Biller vun Opzeechnungen an der Tabell ze kréien wéinst engem 10K Update ass sou-sou Redundanz ...

TRUNCATE dst

E méi erfuerene Entwéckler weess datt de ganze Tablet relativ bëlleg ka gebotzt ginn:

  • kloer (TRUNCATE) de ganzen Dësch
  • anzeginn alles aus dem neie Bild

D'Method ass effektiv, heiansdo ganz applicabel, awer et gëtt e Problem ... Mir wäerten 1M records fir eng laang Zäit derbäi ginn, sou datt mir et net leeschte kënnen den Dësch fir all dës Zäit eidel ze loossen (wéi et geschitt ouni et an enger eenzeger Transaktioun ze packen).

Dat heescht:

  • mir fänken un laangfristeg Transaktioun
  • TRUNCATE imposéiert AccessExklusiv- blockéieren
  • mir maachen d'Insertion fir eng laang Zäit, an all déi aner zu dëser Zäit kann net souguer SELECT

Eppes geet net gutt...

ALTER TABLE… RENAME… / DROP TABLE…

Eng Alternativ ass alles an eng separat nei Dësch ze fëllen, an dann einfach ëmbenennen am Plaz vun der aler. E puer béis kleng Saachen:

  • nach och AccessExklusiv, obwuel däitlech manner Zäit
  • all Ufro Pläng / Statistike fir dës Tabell sinn zréckgesat, muss ANALYSE lafen
  • all auslännesch Schlëssele sinn gebrach (FK) op den Dësch

Et gouf e WIP Patch vum Simon Riggs dee proposéiert huet ze maachen ALTER-eng Operatioun den Dësch Kierper um Fichier Niveau ze schounen, ouni beréieren Statistiken an FK, awer net Quorum sammelen.

LËSCHEN, UPDATEN, INSERT

Also, mir settelen op déi net-blockéierend Optioun vun dräi Operatiounen. Bal dräi ... Wéi maachen ech dat am effektivsten?

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

Am selwechte KLADR mussen all geännert records zousätzlech duerch Postveraarbechtung lafen - normaliséiert, Schlësselwieder markéiert a reduzéiert op déi erfuerderlech Strukturen. Awer wéi wësst Dir - wat genee geännert huetouni de Synchroniséierungscode ze komplizéieren, am Idealfall ouni et iwwerhaapt ze beréieren?

Wann nëmmen Äre Prozess Schreifzougang zur Zäit vun der Synchroniséierung huet, da kënnt Dir en Ausléiser benotzen deen all d'Ännerunge fir eis sammelt:

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

Elo kënne mir Trigger applizéieren ier Dir d'Synchroniséierung starten (oder se aktivéieren via 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();

An dann extrahéieren mir roueg all d'Ännerungen déi mir brauchen aus de Logtabellen a lafen se duerch zousätzlech Handler.

3.3. Verlinkt Sets importéieren

Uewen hu mir Fäll betruecht wann d'Datestrukture vun der Quell an der Destinatioun d'selwecht sinn. Awer wat wann den Eroplueden vun engem externe System e Format anescht wéi d'Späicherstruktur an eiser Datebank huet?

Loosst eis als Beispill d'Lagerung vu Clienten an hire Konten huelen, déi klassesch "vill-zu-eent" Optioun:

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

Awer den Download vun enger externer Quell kënnt eis a Form vun "all in one":

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

Natierlech kënnen d'Clientdaten an dëser Versioun duplizéiert ginn, an den Haaptrekord ass "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

Fir de Modell setzen mir eis Testdaten einfach an, awer erënnere mech - COPY méi efficace!

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

Als éischt loosse mer déi "Schnëtt" ervirhiewen, op déi eis "Fakten" bezéien. An eisem Fall bezéie Rechnungen op Clienten:

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

Fir d'Konten korrekt mat Client IDen ze verbannen, musse mir als éischt dës Identifizéierer erausfannen oder generéieren. Loosst eis Felder ënner hinnen addéieren:

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

Loosst eis d'Tabellsynchroniséierungsmethod hei uewen beschriwwen mat enger klenger Ännerung benotzen - mir wäerten näischt an der Ziltabelle aktualiséieren oder läschen, well mir Clienten "nëmmen addéieren" importéieren:

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

Eigentlech ass alles an invoice_import Elo hu mir de Kontaktfeld ausgefëllt client_id, mat där mir d'Rechnung aginn.

Source: will.com

Setzt e Commentaire