ProHoster > Log > administrasjon > DBA: organiser kompetent synkroniseringer og importer

DBA: organiser kompetent synkroniseringer og importer

For kompleks behandling av store datasett (forskjellig ETL-prosesser: importer, konverteringer og synkronisering med en ekstern kilde) ofte er det behov midlertidig "husk" og behandle umiddelbart raskt noe omfangsrikt.

En typisk oppgave av denne typen høres vanligvis omtrent slik ut: "Akkurat her regnskapsavdelingen losses fra klientbanken de siste mottatte betalingene, må du raskt laste dem opp til nettstedet og koble dem til kontoene dine.»

Men når volumet av dette "noe" begynner å måle seg i hundrevis av megabyte, og tjenesten må fortsette å jobbe med databasen 24x7, oppstår det mange bivirkninger som vil ødelegge livet ditt.
DBA: organiser kompetent synkroniseringer og importer
For å håndtere dem i PostgreSQL (og ikke bare i det), kan du bruke noen optimaliseringer som lar deg behandle alt raskere og med mindre ressursforbruk.

1. Hvor skal sendes?

Først, la oss bestemme hvor vi kan laste opp dataene vi vil "behandle".

1.1. Midlertidige tabeller (MIDLERTIDIG TABELL)

I prinsippet, for PostgreSQL er midlertidige tabeller de samme som alle andre. Derfor overtro som "Alt der er bare lagret i minnet, og det kan ta slutt". Men det er også flere vesentlige forskjeller.

Ditt eget "navneområde" for hver tilkobling til databasen

Hvis to tilkoblinger prøver å koble til samtidig CREATE TABLE x, da vil noen definitivt få ikke-unikthetsfeil databaseobjekter.

Men hvis begge prøver å utføre CREATE TEMPORARY TABLE x, så vil begge gjøre det normalt, og alle vil få det din kopi tabeller. Og det vil ikke være noe felles mellom dem.

"Selvdestruksjon" ved frakobling

Når forbindelsen er lukket, slettes alle midlertidige tabeller automatisk, altså manuelt DROP TABLE x det er ingen vits bortsett fra...

Hvis du jobber gjennom pgbouncer i transaksjonsmodus, så fortsetter databasen å tro at denne forbindelsen fortsatt er aktiv, og i den eksisterer fortsatt denne midlertidige tabellen.

Derfor vil det å prøve å opprette det igjen, fra en annen tilkobling til pgbouncer, resultere i en feil. Men dette kan omgås ved å bruke CREATE TEMPORARY TABLE IF NOT EXISTS x.

Riktignok er det bedre å ikke gjøre dette uansett, for da kan du "plutselig" finne dataene som gjenstår fra den "forrige eieren". I stedet er det mye bedre å lese manualen og se at når du lager en tabell er det mulig å legge til ON COMMIT DROP - det vil si at når transaksjonen er fullført, vil tabellen automatisk slettes.

Ikke-replikering

Fordi de bare tilhører en spesifikk tilkobling, replikeres ikke midlertidige tabeller. Men dette eliminerer behovet for dobbel registrering av data i heap + WAL, så INSERT/UPDATE/DELETE inn i den er betydelig raskere.

Men siden en midlertidig tabell fortsatt er en "nesten vanlig" tabell, kan den heller ikke opprettes på en replika. I hvert fall foreløpig, selv om den tilsvarende lappen har sirkulert lenge.

1.2. ULOGGGET TABELL

Men hva bør du gjøre, for eksempel hvis du har en slags tungvint ETL-prosess som ikke kan implementeres innenfor én transaksjon, men du fortsatt har pgbouncer i transaksjonsmodus? ..

Eller dataflyten er så stor at Det er ikke nok båndbredde på én tilkobling fra en database (les, én prosess per CPU)?..

Eller noen operasjoner pågår asynkront i forskjellige sammenhenger?

Det er bare ett alternativ her - midlertidig opprette en ikke-midlertidig tabell. Ordspill, ja. Det er:

  • laget "mine egne" tabeller med maksimalt tilfeldige navn for ikke å krysse noen
  • Pakk: fylte dem med data fra en ekstern kilde
  • Transform: konvertert, fylt ut viktige koblingsfelt
  • Laste: helte klare data inn i måltabeller
  • slettet "mine" tabeller

Og nå - en flue i salven. Faktisk, alle skrivinger i PostgreSQL skjer to ganger - først i WAL, og deretter inn i tabellen/indeksen. Alt dette er gjort for å støtte ACID og riktig datasynlighet mellom COMMIT'nøttete og ROLLBACK'nulltransaksjoner.

Men vi trenger ikke dette! Vi har hele prosessen Enten var det helt vellykket eller så var det ikke.. Det spiller ingen rolle hvor mange mellomtransaksjoner det vil være - vi er ikke interessert i å "fortsette prosessen fra midten", spesielt når det ikke er klart hvor det var.

For å gjøre dette introduserte PostgreSQL-utviklerne, tilbake i versjon 9.1, noe som ULOGGDE tabeller:

Med denne indikasjonen blir tabellen opprettet som ulogget. Data skrevet til uloggede tabeller går ikke gjennom fremskrivningsloggen (se kapittel 29), noe som fører til at slike tabeller jobbe mye raskere enn vanlig. Imidlertid er de ikke immune mot feil; i tilfelle serverfeil eller nødavstenging, en ulogget tabell automatisk avkortet. I tillegg innholdet i den uloggede tabellen ikke replikert til slaveservere. Alle indekser som opprettes på en ulogget tabell blir automatisk avlogget.

Kort sagt, det vil gå mye raskere, men hvis databaseserveren "faller", vil det være ubehagelig. Men hvor ofte skjer dette, og vet ETL-prosessen din hvordan du korrigerer dette riktig "fra midten" etter å ha "revitalisert" databasen?

Hvis ikke, og tilfellet ovenfor ligner på ditt, bruk UNLOGGEDmen aldri ikke aktiver dette attributtet på ekte tabeller, dataene som er kjære for deg.

1.3. ON COMMIT { SLETT RADER | MISTE}

Denne konstruksjonen lar deg spesifisere automatisk oppførsel når en transaksjon er fullført når du oppretter en tabell.

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

Siden hele infrastrukturen for lagring av metabeskrivelsen til en midlertidig tabell er nøyaktig den samme som for en vanlig tabell, Konstant oppretting og sletting av midlertidige tabeller fører til alvorlig "hevelse" av systemtabeller pg_class, pg_attribute, pg_attrdef, pg_depend,...

Tenk deg nå at du har en arbeider på en direkte tilkobling til databasen, som åpner en ny transaksjon hvert sekund, oppretter, fyller, behandler og sletter en midlertidig tabell... Det vil være et overskudd av søppel samlet i systemtabellene, og dette vil føre til ekstra bremser for hver operasjon.

Generelt, ikke gjør dette! I dette tilfellet er det mye mer effektivt CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS ta den ut av transaksjonssyklusen - så er tabellene allerede ved begynnelsen av hver nye transaksjon vil eksistere (lagre en samtale CREATE), men vil være tom, Takk til TRUNCATE (vi lagret også samtalen) når du fullførte forrige transaksjon.

1.4. LIKER...INKLUDERT...

Jeg nevnte i begynnelsen at en av de typiske brukstilfellene for midlertidige tabeller er ulike typer import - og utvikleren kopierer og limer trøtt inn listen over felt i måltabellen i erklæringen til sin midlertidige...

Men latskap er motoren til fremskritt! Derfor lag en ny tabell "basert på eksempel" det kan være mye enklere:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Siden du da kan generere mye data i denne tabellen, vil det aldri gå raskt å søke gjennom den. Men det finnes en tradisjonell løsning på dette - indekser! Og ja, en midlertidig tabell kan også ha indekser.

Siden de nødvendige indeksene ofte faller sammen med indeksene til måltabellen, kan du ganske enkelt skrive LIKE target_table INCLUDING INDEXES.

Hvis du også trenger DEFAULT-verdier (for eksempel for å fylle ut primærnøkkelverdiene), kan du bruke LIKE target_table INCLUDING DEFAULTS. Eller rett og slett - LIKE target_table INCLUDING ALL - kopierer standarder, indekser, begrensninger, ...

Men her må du forstå at hvis du opprettet importer tabell umiddelbart med indekser, så vil dataene ta lengre tid å lasteenn om du først fyller opp alt, og først deretter ruller opp indeksene - se på hvordan den gjør dette som et eksempel pg_dump.

Generelt, RTFM!

2. Hvordan skrive?

La meg bare si - bruk det COPY-flyt i stedet for "pakke" INSERT, akselerasjon til tider. Du kan til og med direkte fra en forhåndsgenerert fil.

3. Hvordan behandle?

Så la oss la introen vår se omtrent slik ut:

  • du har en tabell med klientdata lagret i databasen din 1 million rekorder
  • hver dag en klient sender deg en ny fullt "bilde"
  • av erfaring vet du det fra tid til annen ikke mer enn 10 XNUMX poster endres

Et klassisk eksempel på en slik situasjon er KLADR base — det er mange adresser totalt, men i hver ukentlig opplasting er det svært få endringer (omdøping av tettsteder, sammenslåing av gater, utseende av nye hus) selv i nasjonal målestokk.

3.1. Full synkroniseringsalgoritme

For enkelhets skyld, la oss si at du ikke engang trenger å omstrukturere dataene - bare ta tabellen inn i ønsket form, det vil si:

  • fjerne alt som ikke eksisterer lenger
  • oppdatering alt som allerede eksisterte og må oppdateres
  • innsats alt som ikke har skjedd ennå

Hvorfor skal operasjonene gjøres i denne rekkefølgen? Fordi dette er hvordan bordstørrelsen vil vokse minimalt (husk MVCC!).

SLETT FRA dst

Nei, du kan selvfølgelig klare deg med bare to operasjoner:

  • fjerne (DELETE) alt generelt
  • innsats alt fra det nye bildet

Men samtidig, takket være MVCC, Størrelsen på bordet vil øke nøyaktig to ganger! Å få +1 million bilder av poster i tabellen på grunn av en 10K-oppdatering er så som så redundans...

TRUNCATE dst

En mer erfaren utvikler vet at hele nettbrettet kan rengjøres ganske billig:

  • ren (TRUNCATE) hele bordet
  • innsats alt fra det nye bildet

Metoden er effektiv, noen ganger ganske anvendelig, men det er et problem... Vi kommer til å legge til 1M poster i lang tid, så vi har ikke råd til å la bordet stå tomt i hele denne tiden (som vil skje uten å pakke det inn i en enkelt transaksjon).

Som betyr:

  • vi begynner langvarig transaksjon
  • TRUNCATE pålegger Eksklusiv tilgang-blokkering
  • vi gjør innsettingen i lang tid, og alle andre på dette tidspunktet kan ikke engang SELECT

Noe går ikke bra...

ENDRE TABELL... GJENNOMFØR... / SLIP TABELL...

Et alternativ er å fylle alt inn i en separat ny tabell, og deretter gi den et nytt navn i stedet for den gamle. Et par stygge småting:

  • fortsatt også Eksklusiv tilgang, selv om det er betydelig kortere tid
  • alle spørringsplaner/statistikker for denne tabellen er tilbakestilt, må kjøre ANALYSE
  • alle fremmednøkler er ødelagte (FK) til bordet

Det var en WIP-lapp fra Simon Riggs som foreslo å lage ALTER-en operasjon for å erstatte tabellkroppen på filnivå, uten å berøre statistikk og FK, men samlet ikke beslutningsdyktig.

SLETT, OPPDATERING, SETTT INN

Så vi bestemmer oss for det ikke-blokkerende alternativet med tre operasjoner. Nesten tre... Hvordan gjøre 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. Importer etterbehandling

I samme KLADR må alle endrede poster i tillegg kjøres gjennom etterbehandling - normalisert, nøkkelord uthevet og redusert til de nødvendige strukturene. Men hvordan vet du - hva som endret seguten å komplisere synkroniseringskoden, ideelt sett uten å berøre den i det hele tatt?

Hvis bare prosessen din har skrivetilgang på synkroniseringstidspunktet, kan du bruke en trigger som samler alle endringene for oss:

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

Nå kan vi bruke 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å trekker vi rolig ut alle endringene vi trenger fra loggtabellene og kjører dem gjennom flere behandlere.

3.3. Importere koblede sett

Ovenfor vurderte vi tilfeller der datastrukturene til kilden og destinasjonen er de samme. Men hva om opplastingen fra et eksternt system har et annet format enn lagringsstrukturen i databasen vår?

La oss ta som et eksempel lagringen av klienter og deres kontoer, det klassiske "mange-til-en"-alternativet:

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 nedlastingen fra en ekstern kilde kommer til oss i form av "alt i ett":

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

Selvfølgelig kan kundedata dupliseres i denne versjonen, 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 setter vi ganske enkelt inn testdataene våre, men husk - COPY mer 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);

Først, la oss fremheve de "kuttene" som våre "fakta" refererer til. I vårt tilfelle 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 å kunne knytte kontoer til kunde-ID-er på riktig måte, må vi først finne ut eller generere disse identifikatorene. La oss legge til felt under dem:

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

La oss bruke tabellsynkroniseringsmetoden beskrevet ovenfor med en liten endring - vi vil ikke oppdatere eller slette noe i måltabellen, fordi vi importerer klienter "bare vedlegg":

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

Egentlig er alt inne invoice_import Nå har vi kontaktfeltet fylt ut client_id, som vi legger inn fakturaen med.

Kilde: www.habr.com

Legg til en kommentar