DBA: korraldame sünergiate ja importide haldamise

Suure andmehulkade keerulise töötlemise korral (erinevad ETL-protsessid: impordid, konvertimised ja sünkroonimised välise allikaga) tekib sageli vajadus ajutiselt "mäletada" ja kohe kiiresti töödelda midagi mahukat.

Kliiniline ülesanne sellistel juhtudel kõlab tavaliselt umbes nii: "Siin raamatupidamine ekstrakteeris kliendipangast viimased sisse laekunud maksed, tuleb need kiiresti saidile üles laadida ja siduda kontodega"

Kuid kui selle "midagi" maht hakkab mõõtma sadu megabaite ning teenus peab sellegipoolest töötama andmebaasiga režiimis 24×7, siis tekib palju kõrvalmõjusid, mis rikuvad teie elu.

Et nendega toime tulla PostgreSQL-is (ja mitte ainult seal), saab kasutada teatud optimeerimise võimalusi, mis võimaldavad kõik kiiremini töödelda ja vähem ressursse tarbida.

1. Kuhu laadida?

Esiteks, määratleme, kuhu me saame laadida andmeid, mida me tahame "töödelda".

1.1. Ajutised tabelid (TEMPORARY TABLE)

Põhimõtteliselt on PostgreSQL-is ajutised tabelid samasugused tabelid nagu kõik teised. Seega on vale arvamus, et «seal kõik on ainult mälus, ja see võib otsa lõppeda». Kuid on ka mõned olulised erinevused.

Iga andmebaasiühenduse jaoks oma «nimetust»

Kui kaks ühendust üritavad samaaegselt teostada CREATE TABLE x, siis keegi peab kindlasti saama unikaalsuse viga andmebaasiobjektide osas.

Aga kui mõlemad üritavad teostada CREATE AJUTINE TABEL x, siis mõlemad teevad seda normaalselt, ja igaüks saab oma eksemplari tabelist. Ja neil ei ole omavahel midagi ühist.

«Enesetapu» disconnecti puhul

Ühenduse sulgemisel kustutatakse kõik ajutised tabelid automaatselt, seega pole mõtet «käsitsi» teostada DROP TABLE x välja arvatud…

Kui te töötate pgbounceri tehingurežiimis, siis andmebaas arvab ikkagi, et see ühendus on endiselt aktiivne, ja selles ajutises tabelis on endiselt olemas.

Seega toob selle uuesti loomine, juba teisest pgbounceri ühendusest, kaasa veateate. Kuid seda saab ringiga minna, kasutades LOO TEMPORARY TABLE KUI EI OLE x.

Tõsi, parem oleks seda siiski mitte teha, kuna võid hiljem „järsku“ avastada seal endiselt „eelmise omaniku“ andmed. Selle asemel on palju parem lugeda juhendit ja näha, et tabeli loomisel on võimalik juurde kirjutada. KOHUSTUSEGA DROP — see tähendab, et tehingu lõpetamisel tabel eemaldatakse automaatselt.

Mitte-replikatsioon

Ajutiste tabelite replikatsiooni ei toimu, kuna need kuuluvad ainult kindlale ühendusele. Kuid see vabastab vajadusest andmete kahekordse salvestamise järele heap + WAL, seetõttu on INSERT/UPDATE/DELETE selles tunduvalt kiirem.

Kuna ajutine tabel on siiski „peaaegu tavaline“ tabel, ei saa seda ka replikatsiooni luua. Vähemalt praegu, kuigi vastav patch on juba ammu olemas.

1.2. Mitte-logitud tabelid (UNLOGGED TABLE)

Aga mis siis, kui teil on mõni mahukas ETL-protsess, mida ei õnnestu teostada ühe tehingu raames, ja teil on tegelikult pgbounceri tehingurežiimis?..

Või andmevoog on nii suur, et ühe ühenduse läbilaskevõime ei piisa andmebaasi (loe: ühe CPU protsessi)?..

Või osa operatsioonidest toimub asünkroonselt erinevates ühendustes?..

Siin on vaid üks võimalus — ajutiselt luua mitte-ajutisi tabeleid. Mänguline väljend, eks. See tähendab:

• loodud „oma” tabelitega maksimaalselt juhuslike nimedega, et mitte kellegagi kokku puutuda
• Ekstraheerimine: laaditi neisse andmed välisest allikast
• Transformeerimine: muundatud, täideti võtme sidumise väljad
• Load: valmitud andmed kanti sihttabelitesse
• kustutas „oma” tabelid

Aga nüüd — tõrvatilk. Tegelikult, kogu salvestus PostgreSQL-is toimub kahel korral — esmakordselt WAL-is, seejärel juba tabelite/indeksite kehad. Kõik see on tehtud ACID toe ja andmete korrektse nähtavuse toetamiseks COMMIT‘sisenenud ja RULLI TAGASI‘sisenenud tehingute vahel.

Aga me ei vaja seda! Meie kogu protsess kas toimis täiesti või mitte.Pole tähtis, kui palju keskseid tehinguid selle sees on — meid ei huvita „protsessi jätkamine keskelt”, eriti kui ei ole selge, kus see oli.

Selle jaoks tutvustasid PostgreSQL arendajad juba versioonis 9.1 sellist asja nagu mittelogitud (UNLOGGED) tabelid:

Selle juhisega luuakse tabel mittelogitud tabelina. Mittelogitud tabelitesse kirjutatavad andmed ei läbi eeljälgimislogi (vt peatükk 29), mille tulemuseks on sellised tabelid töötavad palju kiiremini kui tavaliselt. Kuid nad ei ole kaitstud rike eest; kui server kukub või selline katkestus toimub, jääb logimata tabel automaatiliselt lühendatud. Lisaks sellele, logimata tabeli sisu ei replitseerita aluselserveritesse. Kõik indeksid, mis luuakse logimata tabeli jaoks, muutuvad automaatselt logimata.

Lühidalt, on see palju kiirem, kuid kui andmebaasi server „kukub” – võib see olla ebameeldiv. Kuid kui sageli see juhtub ja kas teie ETL-protsess suudab seda pärast andmebaasi „elustamist” õigesti kohandada?...

Kui ei, ja ülaltoodud juhtum sarnaneb teie omaga – kasutage UNLOGGED, kuid ärge kandke seda attribuuti tõelistele tabelitele, mille andmed on teile kallid.

1.3. ON COMMIT { DELETE ROWS | DROP }

Seda konstruktsiooni saab tabeli loomisel kasutada automaatse käitumise määramiseks tehingu lõpetamisel.

Umbes KOHUSTUSEGA DROP nagu ma varem mainisin, genereerib ta DROP TABLE, kuid siin on KOHUSTUSEGA KUSTUTA RIDADE huvitavam olukord — siinkohal genereeritakse TRUNCATE TABLE.

Kuna kogu ajutise tabeli metaandmete salvestamise infrastruktuur on täpselt sama, mis tavalisel tabelil, siis ajutiste tabelite pidev loomine ja kustutamine viib süsteemitabelite tugeva "paisumiseni" pg_class, pg_attribute, pg_attrdef, pg_depend,…

Nüüd kujutage ette, et teil on töötaja, kes on otseühenduses andmebaasiga, ja avab iga sekundi järel uue tehingu, loob, täidab, töötleb ja kustutab ajutise tabeli… Süsteemitabelites koguneb liigset prahti, mis toob kaasa igasuguseid viivitusi iga toimingu juures.

Ühesõnaga, nii ei tohiks teha! Sellisel juhul on palju efektiivsem LOO AJUTINE TABEL x ... KOMITTEERIMISEL KUSTUTA RÜNDAD viia välja tehingute tsüklist — nii on igas uues tehingus tabelid juba olemas (säästame väljakutse CREATE), kuid on tühi, tänu TRUNCATE (selle väljakutse me säästsime ka) eelmine tehing lõpetades.

1.4. LIKE… KAAASAARVATUD …

Ma mainisin alguses, et üks tüüpiline kasutusjuht ajutiste tabelite jaoks on erinevad impordid — ja arendaja väsib, kopeerides sihttabeli välja väljade loetelu oma ajutisse tabelisse …

Aga laiskus on edasiviiv jõud! Seetõttu uus tabel "mooduli" järgi on palju lihtsam luua:

LOO AJUTINE TABEL import_table(
  NAGU sihttabel
);

Kuna sellele tabelisse genereerida saab väga palju andmeid, nende otsimine ei ole sugugi kiire. Kuid sellele on traditsiooniline lahendus — indeksid! Ja jah, ajutistel tabelitel võivad samuti olla indeksid.

Kuna sageli vajavad vajalikud indeksid kokku langema sihttabeli indeksitega, saab lihtsalt kirjutada NAGU target_table KAASAS INDEKSD.

Kui vajate veel ka DEFAULT-väärtused (näiteks esmase võtme väärtuste täitmiseks), saab kasutada NAGU target_table KAASAR KOHANDAMINE. Või lihtsalt — NAGU target_table KAASUTADES KÕIKI — kopeerib vaikeseaded, indeksid, piirangud,…

Kuid siin tuleb juba mõista, et kui olete loonud imporditabeli kohe indeksitega, siis andmete laadimine võtab kauem aega, kui kõigepealt kõik laadida ja alles pärast seda indekseid lisada — vaadake näiteks, kuidas seda teeb pg_dump.

Üldiselt, RTFM!

2. Kuidas kirjutada?

Ütlen lihtsalt — kasutage COPY-voogu, mitte "partiide" INSERT, kiirus mitmekordne. Saate isegi otse eelnevalt vormistatud failist.

3. Kuidas töödelda?

Nii, oletame, et meie sisend näeb välja ligikaudu nii:

• teil on andmebaasis tabel kliendiandmetega 1M kirje
• iga päev saadab klient teile uue täieliku "pildi"
• kogemuse põhjal teate, et kord-korralt muudetakse mitte rohkem kui 10 000 kirjet

Klassikaline näide sellisest olukorrast on KLADR andmebaas — aadresside kogus on suur, kuid igas nädalas esitatavas muudatuste (asulate nimede muutmine, tänavate ühendamine, uute majade ilmumine) väljavõttes on neid läbi kogu riigi väga vähe.

3.1. Täissünkroonimise algoritm

Lihtsuse huvides oletame, et te ei pea andmeid restruktureerima — lihtsalt viige tabel soovitud vormi, see tähendab:

• kustutage kõik, mis enam ei eksisteeri
• uuendada kõik, mis juba olemas oli, ja vajab uuendamist
• sisestage kõik, mida veel ei olnud

Miks just sellises järjestuses tuleks operatsioone teha? Sest just nii kasvab tabeli suurus minimaalselt (pea meeles MVCC-d!).

DELETE FROM dst

Jah, loomulikult saab hakkama ka ainult kahe operatsiooniga:

• kustutage (DELETE) üldiselt kõik
• sisestage kõik uue kujutise seast

Kuid tänu MVCC-le tabeli suurus kahekordistub! Saada +1M kirjeid tabelisse 10K uuendamise tõttu — see on küllaltki liig.

TRUNCATE dst

Kogenum arendaja teab, et kogu tabeli saab üsna odavalt puhastada:

• puhastage (TRUNCATE) kogu tabel
• sisestage kõik uue kujutise seast

Tõhus meetod, mõnikord täiesti rakendatav, aga on üks probleem… 1M kirjeid lisades läheb meil kaua aega, seega ei saa me endale lubada, et tabel jääb kogu selle aja jooksul tühi (nagu juhtub, kui ei kasuta ühte tehingut).

See tähendab:

• meie jaoks algab pikk tehing
• TRUNCATE kehtestab AccessExclusive-lukustuse
• me teeme kaua lisamist, samal ajal ei saa kõik teised isegi SELECT

Ei näe välja, et see hästi läheb…

ALTER TABLE… RENAME… / DROP TABLE …

Üks variant on kõik eraldi uude tabelisse laadida ja seejärel lihtsalt vana tabeli kohale ümber nimetada. Paar ebameeldivat asja:

• see on ikka ka samasugune AccessExclusive, kuigi märgatavalt vähem aega
• kõik päringute plaanid/statistika selle tabeli kohta kustutatakse, peab jooksma ANALYZE
• kõik välisvõtmed (FK) tabeli suhtes katkestatakse Olemas oli WIP-patch Simon Riggsilt, mis pakkus

ALTER -operatsiooni, et asendada tabeli keha failitasemel, kahjustamata statistikat ja FK, kuid ei saavutanud vajalikku toetust.DELETE, UPDATE, INSERT

Nii et jääme kolme operatsiooni mitte-blokeerimise variandi juurde. Peaaegu kolme… Kuidas seda kõige tõhusamalt teha?

Nii et, peatume kolme toimingu mitte-blokeeriva valiku peal. Peaaegu kolme… Kuidas seda kõige tõhusamalt teha?

-- teeme kõik tehingu raames, et keegi ei näeks "vahepealseid" olekuid
BEGIN;

-- loome ajutise tabeli imporditud andmete jaoks
CREATE TEMPORARY TABLE tmp(
  LIKE dst INCLUDING INDEXES -- sarnane, koos indeksitega
) ON COMMIT DROP; -- tehingu raames ei ole meil seda vaja

-- kiirelt laadime uue kujundi läbi COPY
COPY tmp FROM STDIN;
-- ...
-- .

-- eemaldame puuduvad
DELETE FROM
  dst D
USING
  dst X
LEFT JOIN
  tmp Y
    USING(pk1, pk2) -- primaarkeele väljad
WHERE
  (D.pk1, D.pk2) = (X.pk1, X.pk2) AND
  Y IS NOT DISTINCT FROM NULL; -- "anti-join"

-- uuendame jäänud
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); -- ei ole mõtet uuendada kokku langevaid

-- lisame puuduvad
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. Postitöötlus impordist

Samas KADRi kõiki muudetud kirjeid tuleb lisaks läbi viia postitöötlus — normaliseerida, eristada märksõnu, viia vajalikesse struktuuridesse. Aga kuidas teada — mida täpselt on muudetud, keerukust suurendamata sünkroniseerimise koodis, ideaalis, mitte puutuda sellesse üldse?

Kui kirjutamisõigus on sünkroniseerimise ajal ainult teie protsessil, saate kasutada käivitusmehhanismi, mis kogub kõik muudatused kokku:

-- siht tabelid
CREATE TABLE kladr(...);
CREATE TABLE kladr_house(...);

-- muudatuste ajalooga tabelid
CREATE TABLE kladr$log(
  ro kladr, -- siin on vanade/uutest kirjetest täis kujutised
  rn kladr
);

CREATE TABLE kladr_house$log(
  ro kladr_house,
  rn kladr_house
);

-- muudatuste logimise üldine funktsioon
CREATE OR REPLACE FUNCTION diff$log() RETURNS trigger AS $$
DECLARE
  dst varchar = TG_TABLE_NAME || '$log';
  stmt text = '';
BEGIN
  -- kontrollime, kas logimine on vajalik kirje uuendamisel
  IF TG_OP = 'UPDATE' THEN
    IF NEW IS NOT DISTINCT FROM OLD THEN
      RETURN NEW;
    END IF;
  END IF;
  -- loome logikirje
  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üüd saame enne sünkroniseerimise alustamist käivitusmehhanismid aktiveerida (või lubada nende kaudu 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();

Ja võime rahulikult logitabelitest välja võtta kõik vajalikud muudatused ja edastada need täiendavatele töötlejatele.

3.3. Seotud komplektide importimine

Ülalpool käsitlesime juhtumeid, kus sisendi ja väljundi andmestruktuurid on samad. Kuid mis juhtub, kui välistest süsteemidest saadud eksport on struktuurilt erinev meie andmebaasi salvestamisest?

Võtame näiteks klientide ja nende arvete säilitamise, klassikalise „palju-ühele” variandi:

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

Kuid välistest allikatest saadud eksport on meil vormis „ kõik ühes”:

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

On ilmne, et klientide andmed võivad sellisel juhul dubleerida, kusjuures põhikirje on „arve”:

0123456789; Vasilij; A-01; 2020-03-16; 1000.00
9876543210; Peeter; A-02; 2020-03-16; 666.00
0123456789; Vasilij; B-03; 2020-03-16; 9999.00

Mudeli jaoks sisestame lihtsalt meie testandmed, kuid peame meeles pidama — COPY efektiivsem!

INSERT INTO invoice_import
VALUES
  ('0123456789', 'Vassilis', 'A-01', '2020-03-16', 1000.00)
, ('9876543210', 'Petja', 'A-02', '2020-03-16', 666.00)
, ('0123456789', 'Vassilis', 'B-03', '2020-03-16', 9999.00);

Esimene samm on tuvastada need 'kategooriad', millele meie 'faktid' viitavad. Meie puhul viitavad arved klientidele:

CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- võib kasutada lihtsalt SELECT DISTINCT, kui andmed on eelnevalt kooskõlas
  client_inn inn
, client_name "name"
FROM
  invoice_import;

Kuna peame arved õigesti seostama klientide ID-dega, peame kõigepealt need identifikaatorid välja selgitama või genereerima. Lisame nende jaoks väljad:

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

Kasutame eespool kirjeldatud tabelite sünkroniseerimise meetodit väikese muudatusega — me ei uuenda ega kustuta midagi sihttabelis, kuna klientide importimine on meil 'append-only':

-- impordime tabelisse juba olemasolevate kirje ID-d
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  client D
WHERE
  T.inn = D.inn; -- unikaalne võti

-- lisame puuduvad kirjed ja määrame nende ID-d
WITH ins AS (
  INSERT INTO client(
    inn
  , name
  )
  SELECT
    inn
  , name
  FROM
    client_import
  WHERE
    client_id IS NULL -- kui ID pole määratud
  RETURNING *
)
UPDATE
  client_import T
SET
  client_id = D.client_id
FROM
  ins D
WHERE
  T.inn = D.inn; -- unikaalne võti

-- määrame klientide ID-d arvete kirjestes
UPDATE
  invoice_import T
SET
  client_id = D.client_id
FROM
  client_import D
WHERE
  T.client_inn = D.inn; -- rakendatav võti

Kokkuvõttes, kõik — invoice_import nüüd on meil seotud välja täidetud client_id, millega me ka arve sisestame.

Allikas: habr.com