ProHoster > Blog > Amministrazione > DBA: urganizà cumpetente sincronizazioni è impurtazioni

DBA: urganizà cumpetente sincronizazioni è impurtazioni

Per u trattamentu cumplessu di grande setti di dati (differenti I prucessi ETL: impurtazioni, cunversione è sincronizazione cù una fonte esterna) spessu ci hè bisognu temporaneamente "ricordate" è subitu subitu prucessu qualcosa di voluminoso.

Un compitu tipicu di stu tipu di solitu sona qualcosa cusì: "Propriu quì dipartimentu di cuntabilità scaricatu da a banca di u cliente l'ultimi pagamenti ricivuti, avete bisognu di caricalli rapidamente à u situ web è ligami cù i vostri cunti "

Ma quandu u voluminu di questu "qualcosa" cumencia à misurà in centinaie di megabytes, è u serviziu deve cuntinuà à travaglià cù a basa di dati 24x7, parechji effetti latu chì arruvinanu a vostra vita.
DBA: urganizà cumpetente sincronizazioni è impurtazioni
Per trattà cun elli in PostgreSQL (è micca solu in questu), pudete aduprà qualchi ottimisazioni chì vi permettenu di processà tuttu più veloce è cun menu cunsumu di risorse.

1. Induve spedinu ?

Prima, decidemu induve pudemu cullà i dati chì vulemu "prucessa".

1.1. Tavule Temporary (TABLE TEMPORARY)

In principiu, per PostgreSQL e tavule tempuranee sò listessi cum'è qualsiasi altru. Dunque, superstizioni cum'è "Tuttu ci hè guardatu solu in memoria, è pò finisce". Ma ci sò ancu parechje differenzi significati.

U vostru propiu "namespace" per ogni cunnessione à a basa di dati

Sì duie cunnessione pruvate à cunnette à u stessu tempu CREATE TABLE x, allora qualcunu certamenti uttene errore di non-unicità oggetti di basa di dati.

Ma si tutti dui pruvate à eseguisce CREATE TEMPORARY TABLE x, allura tramindui a feranu nurmale, è tutti utteneranu a vostra copia tavule. È ùn ci sarà nunda in cumunu trà elli.

"Autodistruzzione" quandu si disconnette

Quandu a cunnessione hè chjusa, tutte e tavule tempuranee sò automaticamente sguassate, cusì manualmente DROP TABLE x ùn ci hè nunda eccettu ...

Sè vo site à travaglià pgbouncer in modu di transazzione, allura a basa di dati cuntinueghja à crede chì sta cunnessione hè sempre attiva, è in questu sta table temporale esiste sempre.

Per quessa, pruvà à creà di novu, da una cunnessione diversa à pgbouncer, hà da esse un errore. Ma questu pò esse evitata cù l'usu CREATE TEMPORARY TABLE IF NOT EXISTS x.

True, hè megliu micca di fà questu in ogni modu, perchè allora pudete "subbitu" truvà quì i dati chì restanu da u "pruprietariu precedente". Invece, hè assai megliu per leghje u manuale è vede chì quandu crea una tavola hè pussibule aghjunghje ON COMMIT DROP - vale à dì, quandu a transazzione cumpleta, a tavula serà automaticamente sguassata.

Non-replicazione

Perchè appartenenu solu à una cunnessione specifica, i tavule tempurane ùn sò micca replicati. Ma questu elimina a necessità di doppia registrazione di dati in heap + WAL, cusì INSERT/UPDATE/DELETE in questu hè significativamente più veloce.

Ma postu chì una tavula temporale hè sempre una tavola "quasi ordinaria", ùn pò micca esse creatu ancu nantu à una replica. Almenu per avà, ancu s'è u patch currispundente hè in circulazione per un bellu pezzu.

1.2. TABELLA UNLOGGED

Ma chì duvete fà, per esempiu, sè avete qualchì tipu di prucessu ETL ingombrante chì ùn pò micca esse implementatu in una transazzione, ma avete sempre pgbouncer in modu di transazzione? ...

O u flussu di dati hè cusì grande chì Ùn ci hè micca abbastanza larghezza di banda in una cunnessione da una basa di dati (lettu, un prucessu per CPU) ?...

O certe operazioni sò in corso in modu asincronu in diverse cunnessione?...

Ci hè solu una opzione quì - temporaneamente creà una tavola non-tempuranea. Pun, sì. Hè:

  • criatu "u mo propiu" tavulinu cù nomi massimu casuale per ùn intersecà cù nimu
  • Extract: li pieni di dati da una fonte esterna
  • Trasfurmà: cunvertitu, cumpletu in i campi di ligame chjave
  • Load: versò dati pronti in tavule di destinazione
  • sguassati "i mo" tavule

È avà - una mosca in l'unguentu. In fattu, tutte e scritture in PostgreSQL succede duie volte - prima in WAL, poi in i corpi di tabella / indici. Tuttu chistu hè fattu per sustene l'ACID è a visibilità di dati curretta trà COMMIT'noce è ROLLBACK'transazzione nulla.

Ma ùn avemu micca bisognu di questu! Avemu tuttu u prucessu O era cumplettamente successu o ùn era micca.. Ùn importa micca quantu transazzione intermedie seranu - ùn avemu micca interessatu à "cuntinuà u prucessu da u mità", soprattuttu quandu ùn hè micca chjaru induve era.

Per fà questu, i sviluppatori di PostgreSQL, in a versione 9.1, anu introduttu una cosa cum'è tabelle UNLOGGED:

Cù sta indicazione, a tavula hè creata cum'è unlogged. I dati scritti in e tabelle unlogged ùn passanu micca in u log di scrittura anticipata (vede u Capitulu 29), facendu chì tali tabelle travaglià assai più veloce di u solitu. Tuttavia, ùn sò micca immune à fallimentu; in casu di fallimentu di u servitore o arrestu di emergenza, una tavola unlogged truncatu automaticamente. Inoltre, u cuntenutu di a tavola unlogged micca replicatu à i servitori slave. Ogni indici creati nantu à una tabella unlogged diventanu automaticamente unlogged.

In brevi, serà assai più veloce, ma se u servitore di basa di dati "caduta", serà dispiacevule. Ma quantu spessu succede questu, è u vostru prucessu ETL sapi cumu corregge questu currettamente "da u mità" dopu à "rivitalizza" a basa di dati?...

Se no, è u casu sopra hè simile à u vostru, aduprate UNLOGGEDma mai ùn attivate micca stu attributu nantu à e tavule veri, i dati da quale hè caru per voi.

1.3. ON COMMIT { ELIMINA RIGHE | DROP}

Questa custruzzione permette di specificà u cumpurtamentu automaticu quandu una transazzione hè cumpleta quandu crea una tavola.

nantu ON COMMIT DROP Aghju digià scrittu sopra, genera DROP TABLE, ma cun ON COMMIT DELETE ROWS a situazione hè più interessante - hè generatu quì TRUNCATE TABLE.

Siccomu tutta l'infrastruttura per almacenà a meta-descrizzione di una tavola temporale hè esattamente uguale à quella di una tavola regulare, allora A creazione constante è l'eliminazione di e tavule tempuranee porta à una "inflazione" severa di e tavule di u sistema pg_class, pg_attribute, pg_attrdef, pg_depend,...

Avà imaginate chì avete un travagliadore nantu à una cunnessione diretta à a basa di dati, chì apre una nova transazzione ogni secondu, crea, riempie, processa è sguassate una tavola temporale... Ci sarà un eccessu di basura accumulatu in i tavule di u sistema, è questu pruvucarà freni extra per ogni operazione.

In generale, ùn fate micca questu! In questu casu, hè assai più efficace CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS piglià fora di u ciculu di transazzione - dopu à u principiu di ogni nova transazzione i tavule sò digià esisterà (Salvà una chjama CREATE), ma sarà viotu, grazie à TRUNCATE (avemu ancu salvatu a so chjama) quandu compie a transazzione precedente.

1.4. LIKE... INCLUSI...

Aghju menzionatu à u principiu chì unu di i casi d'usu tipici per i tavulini tempuranee hè varii tipi di impurtazioni - è u sviluppatore stancu copià-incollà a lista di campi di a tavola di destinazione in a dichjarazione di u so tempurale ...

Ma a pigrizia hè u mutore di u prugressu ! Hè perchè creà una nova tavola "basata nantu à u sample" pò esse assai più simplice:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Dapoi vi ponu tandu generà assai di dati in sta tavula, a ricerca à traversu ùn sarà mai prestu. Ma ci hè una suluzione tradiziunale à questu - indici! È, sì, una tavula tempuranea pò ancu avè indici.

Siccomu, spessu, l'indici necessarii coincidenu cù l'indici di a tavola di destinazione, pudete simplificà scrive LIKE target_table INCLUDING INDEXES.

Sè ancu avete bisognu DEFAULT-valori (per esempiu, per riempie i valori di chjave primaria), pudete aduprà LIKE target_table INCLUDING DEFAULTS. O simpricimenti - LIKE target_table INCLUDING ALL - copia predefiniti, indici, restrizioni, ...

Ma quì avete bisognu di capisce chì si avete creatu tavula impurtà subitu cù l'indici, allura i dati pigghianu più tempu per caricachè s'è tù prima riempie tuttu, è solu dopu roll up l'indici - fighjate cumu fà questu cum'è un esempiu pg_dump.

In brevi, RTFM!

2. Cumu scrive ?

Lasciami dì solu - aduprà COPY-flow invece di "pack" INSERT, accelerazione a volte. Pudete ancu direttamente da un schedariu pre-generatu.

3. Cumu prucessu?

Allora, lasciamu chì a nostra intro pare cusì cusì:

  • avete un tavulu cù dati di u cliente guardatu in a vostra basa di dati 1 M records
  • ogni ghjornu un cliente vi manda un novu piena "imagine"
  • da a sperienza sapete chì da u tempu à u tempu micca più di 10K records sò cambiati

Un esempiu classicu di una tale situazione hè Base KLADR - ci sò assai indirizzi in tuttu, ma in ogni carica settimanale ci sò assai pochi cambiamenti (rinominazione di l'insediamenti, cumminzioni di strade, apparenza di case novi) ancu à una scala naziunale.

3.1. Algoritmu di sincronizazione cumpleta

Per a simplicità, dicemu chì ùn avete mancu bisognu di ristrutturazione di dati - basta à purtà a tavola in a forma desiderata, vale à dì:

  • rimuovi tuttu ciò chì ùn esiste più
  • aghjurnamentu tuttu ciò chì esiste digià è deve esse aghjurnatu
  • inserisce tuttu ciò chì ùn hè ancu accadutu

Perchè l'operazione deve esse fatta in questu ordine? Perchè hè cusì chì a dimensione di a tavola cresce minimamente (ricordate MVCC!).

ELIMINA DA dst

Innò, di sicuru, pudete piglià solu cù duie operazioni:

  • rimuovi (DELETE) tuttu in generale
  • inserisce tuttu da a nova imagine

Mais en même temps, grâce à MVCC, A dimensione di a tavula cresce esattamente duie volte! Ottene + 1M d'imaghjini di registri in a tavula per via di una aghjurnazione di 10K hè cusì cusì ridondante ...

TRUNCATE dst

Un sviluppatore più espertu sapi chì tutta a tableta pò esse pulita à pocu pressu:

  • per liberà (TRUNCATE) tutta a tavola
  • inserisce tuttu da a nova imagine

U metudu hè efficace, a volte abbastanza applicabile, ma ci hè un prublema... Avemu da aghjunghje 1M records per un bellu pezzu, cusì ùn pudemu micca permette di lascià a tavula viota per tuttu questu tempu (cum'è accaderà senza imballà in una sola transazzione).

Chì significa:

  • avemu principiatu transazzione di longa durata
  • TRUNCATE impone Accessu Esclusivu- bluccatu
  • facemu l'inserzione per un bellu pezzu, è tutti l'altri in questu tempu ùn pò mancu SELECT

Qualcosa ùn va micca bè...

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

Una alternativa hè di chjappà tuttu in una nova tavola separata, è dopu solu rinominallu in u locu di u vechju. Un paru di picculi cose brutte:

  • ancu ancu Accessu Esclusivu, anche se significativamente menu tempu
  • tutti i piani di dumanda / statistiche per sta tabella sò resettati, bisognu di eseguisce ANALYSE
  • tutte e chjave straneri sò rotte (FK) à a tavula

Ci era un patch WIP da Simon Riggs chì suggeria di fà ALTER-un funziunamentu di rimpiazzà u corpu di a tavula à u livellu di u schedariu, senza toccu statistiche è FK, ma ùn hà micca cullatu quorum.

ELIMINA, AGGIORNARE, INSERIRE

Dunque, avemu stallatu nantu à l'opzione senza bloccu di trè operazioni. Quasi trè... Cumu fà questu più efficace?

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

In u stessu KLADR, tutti i registri cambiati devenu esse ancu eseguiti per post-processamentu - nurmalizzati, e parolle chjave evidenziate, è ridotte à e strutture richieste. Ma cumu sapete - ciò chì hà cambiatu esattamentesenza cumplicà u codice di sincronizazione, idealmente senza toccu à tuttu?

Se solu u vostru prucessu hà accessu à scrittura à u mumentu di a sincronizazione, pudete aduprà un attivatore chì raccoglie tutti i cambiamenti per noi:

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

Avà pudemu applicà triggers prima di inizià a sincronizazione (o attivà 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();

E poi estrattemu tranquillamente tutti i cambiamenti chì avemu bisognu da e tavule di log è eseguimu attraversu manipulatori supplementari.

3.3. Importazione di Sets Linked

Sopra avemu cunsideratu casi quandu e strutture di dati di a fonte è di destinazione sò listessi. Ma chì se u upload da un sistema esternu hà un formatu sfarente da a struttura di almacenamiento in a nostra basa di dati?

Pigliemu per esempiu l'almacenamiento di i clienti è i so cunti, l'opzione classica "parechje à unu":

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

Ma u scaricamentu da una fonte esterna vene à noi in a forma di "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)
);

Ovviamente, i dati di i clienti ponu esse duplicati in questa versione, è u record principale hè "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

Per u mudellu, avemu da inserisce solu i nostri dati di prova, ma ricordate - COPY più 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);

Prima, mettemu in risaltu quelli "tagli" à quale si riferiscenu i nostri "fatti". In u nostru casu, e fatture si riferiscenu à i clienti:

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

Per associà currettamente i cunti cù l'ID di i clienti, avemu prima bisognu di scopre o generà questi identificatori. Aghjunghjite campi sottu à elli:

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

Utilizemu u metudu di sincronizazione di a tavola descritta sopra cù una piccula emenda - ùn aghjurneremu micca o eliminemu nunda in a tavola di destinazione, perchè impurtate i clienti "append-only":

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

In fatti, tuttu hè in invoice_import Avà avemu u campu di cuntattu cumpletu client_id, cù quale inseriremu a fattura.

Source: www.habr.com

Add a comment