ProHoster > Blog > administration > DBA: Organiser kompetent synkroniseringer og importer

DBA: Organiser kompetent synkroniseringer og importer

Til kompleks behandling af store datasæt (forskellige ETL processer: importer, konverteringer og synkronisering med en ekstern kilde) ofte er der behov midlertidigt "husk" og straks hurtigt behandle noget omfangsrigt.

En typisk opgave af denne art lyder normalt sådan her: "Lige her regnskabsafdeling aflæsset fra klientbanken de sidst modtagne betalinger, skal du hurtigt uploade dem til hjemmesiden og linke dem til dine konti"

Men når volumen af ​​dette "noget" begynder at måle sig i hundredvis af megabyte, og tjenesten skal fortsætte med at arbejde med databasen 24x7, opstår der mange bivirkninger, som vil ødelægge dit liv.
DBA: Organiser kompetent synkroniseringer og importer
For at håndtere dem i PostgreSQL (og ikke kun i det), kan du bruge nogle optimeringer, der giver dig mulighed for at behandle alt hurtigere og med mindre ressourceforbrug.

1. Hvor skal sendes?

Lad os først beslutte, hvor vi kan uploade de data, som vi vil "behandle".

1.1. Midlertidige tabeller (MIDLERTIDIG TABEL)

I princippet er midlertidige tabeller for PostgreSQL de samme som alle andre. Derfor kan overtro gerne "Alt der er kun gemt i hukommelsen, og det kan ende". Men der er også flere væsentlige forskelle.

Dit eget "navneområde" for hver forbindelse til databasen

Hvis to forbindelser forsøger at forbinde på samme tid CREATE TABLE x, så vil nogen helt sikkert få ikke-entydighedsfejl database objekter.

Men hvis begge prøver at udføre CREATE TEMPORARY TABLE x, så vil begge gøre det normalt, og alle får din kopi borde. Og der vil ikke være noget til fælles mellem dem.

"Selvdestruktion" ved frakobling

Når forbindelsen er lukket, slettes alle midlertidige tabeller automatisk, altså manuelt DROP TABLE x der er ingen mening undtagen...

Hvis du arbejder igennem pgbouncer i transaktionstilstand, så fortsætter databasen med at tro, at denne forbindelse stadig er aktiv, og i den eksisterer denne midlertidige tabel stadig.

Derfor vil et forsøg på at oprette det igen, fra en anden forbindelse til pgbouncer, resultere i en fejl. Men dette kan omgås ved at bruge CREATE TEMPORARY TABLE IF NOT EXISTS x.

Sandt nok er det bedre ikke at gøre dette alligevel, for så kan du "pludselig" finde de data, der er tilbage fra den "tidligere ejer". I stedet er det meget bedre at læse manualen og se, at når man laver en tabel, er det muligt at tilføje ON COMMIT DROP - det vil sige, når transaktionen er gennemført, slettes tabellen automatisk.

Ikke-replikation

Fordi de kun tilhører en bestemt forbindelse, replikeres midlertidige tabeller ikke. Men dette eliminerer behovet for dobbeltregistrering af data i heap + WAL, så INSERT/UPDATE/DELETE ind i det er betydeligt hurtigere.

Men da en midlertidig tabel stadig er en "næsten almindelig" tabel, kan den heller ikke oprettes på en replika. I hvert fald for nu, selvom det tilsvarende plaster har cirkuleret længe.

1.2. ULOGGET TABEL

Men hvad skal du for eksempel gøre, hvis du har en form for besværlig ETL-proces, der ikke kan implementeres inden for én transaktion, men du stadig har pgbouncer i transaktionstilstand? ..

Eller datastrømmen er så stor, at Der er ikke nok båndbredde på én forbindelse fra en database (læs, én proces pr. CPU)?..

Eller nogle operationer er i gang asynkront i forskellige sammenhænge?..

Der er kun én mulighed her - midlertidigt oprette en ikke-midlertidig tabel. Ordspil, ja. Det er:

  • oprettet "mine egne" tabeller med maksimalt tilfældige navne for ikke at krydse nogen
  • Uddrag: fyldte dem med data fra en ekstern kilde
  • Transform: konverteret, udfyldt nøglelinkningsfelter
  • Load: hældte klar data i måltabeller
  • slettede "mine" tabeller

Og nu - en flue i salven. Faktisk, alle skrivninger i PostgreSQL sker to gange — først i WAL, derefter ind i tabellen/indekset. Alt dette er gjort for at understøtte ACID og korrekt datasynlighed mellem COMMIT'nøddeagtig og ROLLBACK'nul transaktioner.

Men det har vi ikke brug for! Vi har hele processen Enten var det fuldstændig vellykket, eller også var det ikke.. Det er ligegyldigt, hvor mange mellemliggende transaktioner der vil være - vi er ikke interesserede i at "fortsætte processen fra midten", især når det ikke er klart, hvor det var.

For at gøre dette introducerede PostgreSQL-udviklerne tilbage i version 9.1 sådan noget som UNLOGGED tabeller:

Med denne indikation oprettes tabellen som ulogget. Data skrevet til uloggede tabeller går ikke gennem fremskrivningsloggen (se kapitel 29), hvilket får sådanne tabeller til at arbejde meget hurtigere end normalt. De er dog ikke immune over for fiasko; i tilfælde af serverfejl eller nødlukning, en ulogget tabel automatisk afkortet. Derudover indholdet af den uloggede tabel ikke replikeret til slaveservere. Alle indekser, der er oprettet på en ulogget tabel, bliver automatisk aflogget.

Kort sagt, det vil være meget hurtigere, men hvis databaseserveren "falder", vil det være ubehageligt. Men hvor ofte sker dette, og ved din ETL-proces, hvordan man retter dette korrekt "fra midten" efter at have "revitaliseret" databasen?..

Hvis ikke, og ovenstående tilfælde ligner din, brug UNLOGGEDmen aldrig Aktiver ikke denne egenskab på rigtige tabeller, hvorfra dataene er kære for dig.

1.3. ON COMMIT { SLET RÆKKER | DRÅBE}

Denne konstruktion giver dig mulighed for at angive automatisk adfærd, når en transaktion er gennemført, når du opretter en tabel.

Про ON COMMIT DROP Jeg har allerede skrevet ovenfor, det genererer DROP TABLE, men med ON COMMIT DELETE ROWS situationen er mere interessant - den genereres her TRUNCATE TABLE.

Da hele infrastrukturen til lagring af metabeskrivelsen af ​​en midlertidig tabel er nøjagtig den samme som for en almindelig tabel, så Konstant oprettelse og sletning af midlertidige tabeller fører til alvorlig "hævelse" af systemtabeller pg_class, pg_attribute, pg_attrdef, pg_depend,...

Forestil dig nu, at du har en arbejder på en direkte forbindelse til databasen, som åbner en ny transaktion hvert sekund, opretter, udfylder, behandler og sletter en midlertidig tabel... Der vil være et overskud af skrald akkumuleret i systemtabellerne, og dette vil forårsage ekstra bremser for hver operation.

Generelt, gør ikke dette! I dette tilfælde er det meget mere effektivt CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS tag den ud af transaktionscyklussen - så er tabellerne allerede ved begyndelsen af ​​hver ny transaktion vil eksistere (gem et opkald CREATE), men vil være tom, tak til TRUNCATE (vi gemte også dets opkald), da vi gennemførte den forrige transaktion.

1.4. LIKE ... HERUNDER ...

Jeg nævnte i begyndelsen, at en af ​​de typiske use cases for midlertidige tabeller er forskellige former for import - og udvikleren kopierer træt listen af ​​felter i måltabellen ind i erklæringen om sin midlertidige...

Men dovenskab er fremskridtets motor! Derfor opret en ny tabel "baseret på prøve" det kan være meget enklere:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Da du så kan generere en masse data i denne tabel, vil det aldrig være hurtigt at søge igennem den. Men der er en traditionel løsning på dette - indekser! Og, ja, en midlertidig tabel kan også have indekser.

Da de nødvendige indekser ofte falder sammen med måltabellens indekser, kan du blot skrive LIKE target_table INCLUDING INDEXES.

Hvis du også har brug for DEFAULT-værdier (for eksempel til at udfylde de primære nøgleværdier), kan du bruge LIKE target_table INCLUDING DEFAULTS. Eller simpelthen - LIKE target_table INCLUDING ALL — kopierer standardindstillinger, indekser, begrænsninger,...

Men her skal du forstå, at hvis du har skabt importer tabel med det samme med indekser, så vil dataene tage længere tid at indlæseend hvis du først fylder alt op, og først derefter ruller indekserne op - se som et eksempel, hvordan det gør det pg_dump.

Generelt RTFM!

2. Hvordan skriver man?

Lad mig bare sige - brug det COPY-flow i stedet for "pack" INSERT, acceleration til tider. Du kan endda direkte fra en forudgenereret fil.

3. Hvordan behandles?

Så lad os lade vores intro se sådan her ud:

  • du har en tabel med klientdata gemt i din database 1 mio. rekorder
  • hver dag en kunde sender dig en ny fuldt "billede"
  • af erfaring ved du det fra tid til anden ikke mere end 10 poster ændres

Et klassisk eksempel på en sådan situation er KLADR base — der er mange adresser i alt, men i hver ugentlig upload er der meget få ændringer (omdøbning af bebyggelser, sammenlægning af gader, udseende af nye huse) selv på nationalt plan.

3.1. Fuld synkroniseringsalgoritme

Lad os for nemheds skyld sige, at du ikke engang behøver at omstrukturere dataene - bare bring tabellen i den ønskede form, det vil sige:

  • fjerne alt det, der ikke længere eksisterer
  • opdatering alt, der allerede eksisterede og skal opdateres
  • indsats alt det, der ikke er sket endnu

Hvorfor skal operationerne udføres i denne rækkefølge? Fordi det er sådan, bordstørrelsen vil vokse minimalt (husk MVCC!).

SLET FRA dst

Nej, du kan selvfølgelig klare dig med kun to operationer:

  • fjerne (DELETE) alt generelt
  • indsats alt fra det nye billede

Men på samme tid, takket være MVCC, Størrelsen på bordet vil øges nøjagtigt to gange! At få +1M billeder af poster i tabellen på grund af en 10K opdatering er så som så redundans...

TRUNCATE dst

En mere erfaren udvikler ved, at hele tabletten kan rengøres ganske billigt:

  • ren (TRUNCATE) hele bordet
  • indsats alt fra det nye billede

Metoden er effektiv, nogle gange ret anvendelig, men der er et problem... Vi vil tilføje 1 mio. poster i lang tid, så vi har ikke råd til at lade bordet stå tomt i al denne tid (som det vil ske uden at pakke det ind i en enkelt transaktion).

Hvilket betyder:

  • vi starter langvarig transaktion
  • TRUNCATE pålægger Eksklusiv adgang-blokering
  • vi laver indsættelsen i lang tid, og alle andre på dette tidspunkt kan ikke engang SELECT

Noget går ikke godt...

ÆNDRE TABEL... OMNØV... / SLIP TABEL...

Et alternativ er at udfylde alt i en separat ny tabel og derefter blot omdøbe den i stedet for den gamle. Et par grimme små ting:

  • stadig også Eksklusiv adgang, dog væsentligt kortere tid
  • alle forespørgselsplaner/statistikker for denne tabel nulstilles, skal køre ANALYSE
  • alle fremmednøgler er brudt (FK) til bordet

Der var en WIP-patch fra Simon Riggs, der foreslog at lave ALTER-en operation for at erstatte tabelkroppen på filniveau, uden at røre statistik og FK, men indsamlede ikke beslutningsdygtighed.

SLET, OPDATERING, INDSÆT

Så vi nøjes med den ikke-blokerende mulighed for tre operationer. Næsten tre... Hvordan gør man dette mest effektivt?

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

I samme KLADR skal alle ændrede poster yderligere køres gennem efterbehandling - normaliseret, nøgleord fremhævet og reduceret til de nødvendige strukturer. Men hvordan ved du det - hvad der præcist ændrede siguden at komplicere synkroniseringskoden, ideelt set uden at røre den overhovedet?

Hvis kun din proces har skriveadgang på tidspunktet for synkroniseringen, så kan du bruge en trigger, der samler alle ændringerne for os:

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

Nu kan vi anvende triggere, før vi starter synkronisering (eller aktivere dem 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();

Og så udtrækker vi roligt alle de ændringer, vi har brug for, fra log-tabellerne og kører dem gennem yderligere handlere.

3.3. Importer sammenkædede sæt

Ovenfor har vi overvejet tilfælde, hvor datastrukturerne for kilden og destinationen er de samme. Men hvad hvis uploaden fra et eksternt system har et andet format end lagerstrukturen i vores database?

Lad os som eksempel tage opbevaringen af ​​kunder og deres konti, den klassiske "mange-til-en"-mulighed:

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 download fra en ekstern kilde kommer til os i form af "alt i ét":

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

Det er klart, kundedata kan duplikeres i denne version, og hovedposten er "konto":

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

For modellen indsætter vi blot vores testdata, men husk - COPY mere effektivt!

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

Lad os først fremhæve de "nedskæringer", som vores "fakta" refererer til. I vores tilfælde refererer fakturaer til kunder:

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

For at kunne forbinde konti korrekt med kunde-id'er skal vi først finde ud af eller generere disse identifikatorer. Lad os tilføje felter under dem:

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

Lad os bruge tabelsynkroniseringsmetoden beskrevet ovenfor med en lille ændring - vi vil ikke opdatere eller slette noget i måltabellen, fordi vi importerer klienter "kun vedhæft":

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

Faktisk er alt inde invoice_import Nu har vi udfyldt kontaktfeltet client_id, hvormed vi vil indsætte fakturaen.

Kilde: www.habr.com

Tilføj en kommentar