ProHoster > Blog > Tsamaiso > DBA: Hlophisa likhokahano le thepa e tsoang kantle ho naha ka bokhabane

DBA: Hlophisa likhokahano le thepa e tsoang kantle ho naha ka bokhabane

Bakeng sa ts'ebetso e rarahaneng ea li-data tse kholo (tse fapaneng Mekhoa ea ETL: thepa, liphetoho le ho lumellana le mohloli o ka ntle) hangata ho na le tlhokahalo "hopola" ka nakoana 'me hang-hang sebetsa ntho e kgolo.

Mosebetsi o tloaelehileng oa mofuta ona hangata o utloahala tjena: "Hona mona lefapha la accounting le theotsoe bankeng ea bareki litefo tsa ho qetela tse fumanoeng, o hloka ho li kenya kapele webosaeteng ebe o li hokahanya le li-account tsa hau"

Empa ha molumo oa "ntho" ena e qala ho lekanya ka megabyte e makholo, 'me tšebeletso e tlameha ho tsoela pele ho sebetsa le database 24x7, ho hlaha litla-morao tse ngata tse tla senya bophelo ba hau.
DBA: Hlophisa likhokahano le thepa e tsoang kantle ho naha ka bokhabane
Ho sebetsana le bona ho PostgreSQL (mme eseng ho eona feela), o ka sebelisa lisebelisoa tse ling tse tla u lumella ho sebetsana le ntho e 'ngoe le e' ngoe ka potlako le ka tšebeliso e fokolang ea lisebelisoa.

1. Ho romeloa hokae?

Taba ea pele, ha re etse qeto ea hore na re ka kenya data eo re batlang ho e "sebetsa" hokae.

1.1. Litafole tsa nakoana (TEMPORARY TABLE)

Ha e le hantle, bakeng sa litafole tsa nakoana tsa PostgreSQL li tšoana le tse ling. Ka hona, litumela-khoela li rata “Ntho e ’ngoe le e ’ngoe e teng e bolokiloe mohopolong feela, ’me e ka fela”. Empa ho boetse ho na le liphapang tse 'maloa tsa bohlokoa.

"Sebaka sa mabitso" sa hau bakeng sa khokahano ka 'ngoe ho database

Haeba likhokahano tse peli li leka ho hokahana ka nako e le 'ngoe CREATE TABLE x, joale ka sebele motho o tla fumana phoso e sa ikgethang dintho tsa database.

Empa haeba bobeli ba leka ho etsa CREATE TEMPORARY TABLE x, joale bobeli ba tla e etsa ka tloaelo, 'me bohle ba tla fumana kopi ea hau litafole. 'Me ho ke ke ha e-ba le letho le tšoanang pakeng tsa bona.

"Ho itshenyetsa" ha o kgaola

Ha khokahano e koetsoe, litafole tsohle tsa nakoana lia hlakoloa, kahoo ka letsoho DROP TABLE x ha ho letho ntle le ...

Haeba u sebetsa ka thata pgbouncer ka mokhoa oa transaction, joale database e tsoela pele ho lumela hore khokahano ena e ntse e sebetsa, 'me ho eona tafole ena ea nakoana e ntse e le teng.

Ka hona, ho leka ho e etsa hape, ho tloha ho khokahanyo e fapaneng ho ea ho pgbouncer, ho tla fella ka phoso. Empa sena se ka qojoa ka ho sebelisa CREATE TEMPORARY TABLE IF NOT EXISTS x.

'Nete, ho molemo hore u se ke ua etsa sena leha ho le joalo, hobane joale u ka khona "ka tšohanyetso" ho fumana data e setseng ho "mong'a pele". Ho e-na le hoo, ho molemo haholo ho bala bukana le ho bona hore ha u bōpa tafole ho ka khoneha ho eketsa ON COMMIT DROP - ke hore, ha transaction e phethela, tafole e tla hlakoloa ka bo eona.

Ho se phete

Hobane ke tsa khokahano e itseng feela, litafole tsa nakoana ha li phetoe. Empa sena se felisa tlhoko ea ho rekota habeli ea data ka qubu + WAL, kahoo INSERT/UPDATE/DEELETE ho eona e potlakile haholo.

Empa kaha tafole ea nakoana e ntse e le "tafole e batlang e tloaelehile", e ke ke ea etsoa ka setšoantšo se seng. Bonyane hajoale, leha patch e tsamaellanang e se e ntse e potoloha ka nako e telele.

1.2. TS'ELISITSOE TAFOLE

Empa u lokela ho etsa eng, ka mohlala, haeba u na le mofuta o itseng oa ts'ebetso e boima ea ETL e ke keng ea kenngoa ts'ebetsong e le 'ngoe, empa u ntse u e-na le eona. pgbouncer ka mokhoa oa transaction? ..

Kapa phallo ea data e kholo hoo Ha ho bandwidth e lekaneng khokahanong e le 'ngoe ho tsoa ho database (bala, ts'ebetso e le 'ngoe ka CPU)?..

Kapa ts'ebetso e 'ngoe e ntse e tsoela pele asynchronously ka likamano tse fapaneng?..

Ho na le khetho e le 'ngoe feela mona - theha tafole eo e seng ea nakoana. Pun, yeah. Ke hore:

  • o thehile litafole tsa "ka" tse nang le mabitso a sa tloaelehang haholo hore a se ke a kopana le mang kapa mang
  • Tšoaea: li tlatsitse ka data ho tsoa mohloling o kantle
  • Fetola: e fetotsoe, e tlatsitsoe libakeng tsa bohlokoa tsa ho hokahanya
  • mojaro: e tšetse data e seng e lokisitsoe litafoleng tsa sepheo
  • e hlakotsoe "ka" litafole

'Me joale - fofa ka setlolo. Haele hantle, tsohle li ngola ho PostgreSQL li etsahala habeli - ea pele ho WAL, ebe ka har'a lihlopha tsa tafole / index. Sena sohle se etsoa ho tšehetsa ACID le ho lokisa ponahalo ea data lipakeng tsa COMMIT'nutty le ROLLBACK'li-transactions tse se nang letho.

Empa ha re hloke sena! Re na le ts'ebetso eohle E ka 'na eaba e atlehile ka ho feletseng kapa ha ea ka ea atleha.. Ha ho na taba hore na ho tla ba le litšebelisano tse kae tsa lipakeng - ha re na thahasello ea ho "tsoela pele ts'ebetso ho tloha bohareng," haholo ha ho sa hlaka hore na e ne e le hokae.

Ho etsa sena, bahlahisi ba PostgreSQL, morao ka mofuta oa 9.1, ba hlahisitse ntho e kang TAFOLE TSA BOHLOKOA:

Ka pontšo ena, tafole e entsoe e sa ngolisoa. Lintlha tse ngolletsoeng litafole tse sa ngolisoang ha li kenelle lethathamong la pele-pele (sheba Khaolo ea 29), e leng se etsang hore litafole tse joalo li be teng. sebetsa kapele hofeta mehleng. Leha ho le joalo, le bona ba hlōlehile; haeba seva se hloleha kapa ho koaloa ha tšohanyetso, tafole e sa ngolisoang ka tsela e iketsang truncated. Ho feta moo, litaba tsa tafole e sa ngolisoang ha e phetisoe ho li-server tsa makhoba. Li-index leha e le life tse entsoeng tafoleng e sa ngolisoang li fetoha tse sa ngolisoang.

Ka bokhutšoanyane e tla ba kapele haholo, empa haeba seva sa database se "oela", se tla ba se sa thabiseng. Empa sena se etsahala hangata hakae, 'me na ts'ebetso ea hau ea ETL e tseba ho lokisa see ka nepo "ho tloha bohareng" kamora ho "tsosolosa" database?

Haeba ho se joalo, 'me nyeoe e ka holimo e tšoana le ea hau, sebelisa UNLOGGEDempa ha ho mohla se ke oa lumella tšobotsi ena ho litafole tsa 'nete, data eo u e ratang haholo.

1.3. HO TLA { HLAKOLA MELA | LAHLA}

Mohaho ona o u lumella ho hlakisa boits'oaro bo itekanetseng ha transaction e phethiloe ha u theha tafole.

ka ON COMMIT DROP Ke se ke ngotse ka holimo, e hlahisa DROP TABLE, empa le ON COMMIT DELETE ROWS boemo bo thahasellisa ho feta - bo hlahisoa mona TRUNCATE TABLE.

Kaha lisebelisoa tsohle tsa ho boloka meta-tlhaloso ea tafole ea nakoana li tšoana hantle le tsa tafole e tloaelehileng, joale. Ho bōptjoa kamehla le ho tlosoa ha litafole tsa nakoana ho lebisa "ho ruruha" ho matla ha litafole tsa tsamaiso pg_class, pg_attribute, pg_attrdef, pg_depend,…

Joale ak'u inahanele hore u na le mosebeletsi mabapi le khokahanyo e tobileng ho database, e bulang transaction e ncha motsotsoana o mong le o mong, e bōpa, e tlatsa, e tlatsa, e hlakola le ho tlosa tafole ea nakoana ... Ho tla ba le litšila tse ngata tse bokelloang litafoleng tsa tsamaiso, 'me sena se tla etsa hore mariki a eketsehileng bakeng sa tshebetso ka 'ngoe.

Ka kakaretso, u se ke ua etsa sena! Tabeng ena e sebetsa haholo CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS e tlose potolohong ea transaction - joale qalong ea transaction e 'ngoe le e' ngoe e ncha litafole li se li ntse li le teng e tla ba teng (boloka mohala CREATE), empa e tla be e se na letho, ka lebaka la TRUNCATE (re boetse re bolokile mohala oa eona) ha re phethela tšebetso e fetileng.

1.4. LIKE... HO kenyeletsoa...

Ke boletse qalong hore e 'ngoe ea linyeoe tse tloaelehileng tsa tšebeliso ea litafole tsa nakoana ke mefuta e fapaneng ea thepa e tsoang kantle ho naha - mme mohlahlami o khathala o beha lethathamo la likarolo tsa tafole eo a e shebileng phatlalatsong ea hae ea nakoana ...

Empa botsoa ke enjene ea tsoelo-pele! Ke ka lebaka lena theha tafole e ncha "e ipapisitse le sampole" e ka ba bonolo haholoanyane:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Kaha joale o ka hlahisa lintlha tse ngata tafoleng ena, ho e batla ho ke ke ha e-ba bonolo. Empa ho na le tharollo ea setso ho sena - li-index! Mme, ho joalo, tafole ea nakoana le eona e ka ba le li-index.

Kaha, hangata, li-index tse hlokahalang li tsamaellana le li-index tsa tafole eo u batlang ho e ngola, u ka ngola feela LIKE target_table INCLUDING INDEXES.

Haeba le uena u hloka DEFAULT-Values ​​(mohlala, ho tlatsa lintlha tsa bohlokoa tsa mantlha), u ka li sebelisa LIKE target_table INCLUDING DEFAULTS. Kapa feela - LIKE target_table INCLUDING ALL - likopi tse sa feleng, li-index, litšitiso, ...

Empa mona o hloka ho utloisisa hore haeba u bōpile kenya tafole hang hang ka li-index, ebe data e tla nka nako e telele ho kenyaho feta ha u qala ho tlatsa ntho e 'ngoe le e' ngoe, 'me joale feela u phutha li-index - sheba hore na e etsa sena joang e le mohlala pg_lahla.

Ka bokhutšoanyane, RTFM!

2. Ho ngola joang?

Ere ke bolele feela - e sebedise COPY- phalla sebakeng sa "pakete" INSERT, ho potlakisa ka linako tse ling. U ka esita le ka ho toba ho tswa pele ho hlahisa faele.

3. Mokhoa oa ho sebetsa joang?

Kahoo, a re tlohelle selelekela sa rona se shebahale tjena:

  • u na le tafole e nang le data ea bareki e bolokiloeng polokelong ea hau 1M lirekoto
  • letsatsi le letsatsi moreki o u romella e ncha "setšoantšo" se felletseng
  • ka phihlelo o tseba seo nako le nako ha ho lirekoto tse fetang 10K tse fetotsoeng

Mohlala oa khale oa boemo bo joalo ke Motheo oa KLADR - ho na le liaterese tse ngata ka kakaretso, empa ho kenya e 'ngoe le e' ngoe ea beke le beke ho na le liphetoho tse fokolang haholo (ho reha libaka tsa bolulo, ho kopanya literata, ponahalo ea matlo a macha) esita le ka tekanyo ea naha.

3.1. Algorithm e felletseng ea kamahanyo

Bakeng sa ho nolofatsa, ha re re ha u hloke le ho hlophisa datha hape - tlisa tafole ka mokhoa o lakatsehang, ke hore:

  • tlosa tsohle tse seng di le teng
  • khatholla tsohle tse seng li ntse li le teng mme li hloka ho nchafatsoa
  • kenya tsohle tse so kang di etsahale

Hobaneng ha ts'ebetso e lokela ho etsoa ka tatellano ee? Hobane ke kamoo boholo ba tafole bo tla hola hanyane (hopola MVCC!).

HLAKOLA HO dst

Che, ehlile u ka khona ho iphelisa ka lits'ebetso tse peli feela:

  • tlosa (DELETE) tsohle ka kakaretso
  • kenya kaofela ho tsoa setšoantšong se secha

Empa ka nako e ts'oanang, ka lebaka la MVCC, Boholo ba tafole bo tla eketseha hantle habeli! Ho fumana +1M litšoantšo tsa lirekoto ka har'a tafole ka lebaka la ntlafatso ea 10K ho thata haholo ...

HLOKOMELA dst

Moqapi ea nang le boiphihlelo o tseba hore letlapa lohle le ka hloekisoa ka theko e tlase:

  • hlakisa (TRUNCATE) tafole eohle
  • kenya kaofela ho tsoa setšoantšong se secha

Mokhoa o sebetsa hantle, ka linako tse ling e sebetsa haholo, empa ho na le bothata ... Re tla eketsa lirekoto tsa 1M ka nako e telele, kahoo re ke ke ra khona ho tlohela tafole e se na letho ka nako ena kaofela (joalokaha ho tla etsahala ntle le ho e phuthela ka transaction e le 'ngoe).

Ho bolelang:

  • re qala ts'ebetso ea nako e telele
  • TRUNCATE beha Access Exclusive-thibela
  • re etsa ho kenya nako e telele, le ba bang kaofela ka nako ena ha ke khone le SELECT

Ho na le ntho e sa tsamayeng hantle...

ALTER TABLE... RENAME... / ROPHA TAFOLE...

Tsela e 'ngoe ke ho tlatsa ntho e' ngoe le e 'ngoe tafoleng e ncha e arohaneng, ebe u e reha hape sebakeng sa ea khale. Lintho tse 'maloa tse makatsang:

  • e ntse e le teng Access Exclusive, le hoja nako e nyenyane haholo
  • meralo / lipalo-palo tsohle tsa tafole ena li setiloe bocha, hloka ho matha HLAHLOBA
  • linotlolo tsohle tsa kantle ho naha li robehile (FK) ho ea tafoleng

Ho ne ho e-na le patch ea WIP e tsoang ho Simon Riggs e neng e fana ka tlhahiso ea ho e etsa ALTER- ts'ebetso ea ho nka sebaka sa 'mele oa tafole boemong ba faele, ntle le lipalo-palo tse amang le FK, empa ha ea ka ea bokella quorum.

HLAKOLA, HLAHISA, KENYA

Kahoo, re rarolla khetho e sa thibeleng ea ts'ebetso e meraro. Hoo e ka bang tse tharo ... Joang ho etsa see ka katleho?

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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. Kenya ka morao ho ts'ebetso

KLADR e ts'oanang, lirekoto tsohle tse fetotsoeng li tlameha ho sebetsoa hape ka ts'ebetso ea morao-rao - e tloaelehileng, mantsoe a bohlokoa a totobalitsoe, 'me a fokotsoe ho meaho e hlokahalang. Empa u tseba joang - se fetohileng hantlentle le ho thatafatsa khoutu ea khokahano, ntle le ho e ama ho hang?

Haeba feela ts'ebetso ea hau e na le phihlello ea ho ngola ka nako ea ho hokahanya, o ka sebelisa trigger e tla re bokellela liphetoho tsohle:

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

Joale re ka sebelisa li-triggers pele re qala ho hokahanya (kapa re li nolofalletse ka 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();

'Me joale re ntša ka khutso liphetoho tsohle tseo re li hlokang ho tloha litafoleng tsa log ebe re li tsamaisa ka lisebelisuoa tse ling.

3.3. Ho kenya Lisete tse hoketsoeng

Ka holimo re ile ra nahana ka linyeoe ha libopeho tsa data tsa mohloli le sebaka seo li eang ho sona li tšoana. Empa ho thoe'ng haeba ho romelloa ho tsoa tsamaisong ea ka ntle ho na le sebopeho se fapaneng le sebopeho sa polokelo ho database ea rona?

Ha re nke mohlala oa polokelo ea bareki le liakhaonto tsa bona, khetho ea khale ea "ba bangata-to-one":

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

Empa ho khoasolla ho tsoa mohloling o kantle ho tla ho rona ka mokhoa oa "bohle ka bonngoe":

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

Ho hlakile hore data ea bareki e ka kopitsoa phetolelong ena, 'me rekoto ea mantlha ke "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

Bakeng sa mohlala, re tla kenya data ea rona ea liteko feela, empa hopola - COPY e sebetsang haholoanyane!

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

Taba ea pele, ha re totobatseng "liphapang" tseo "lintlha" tsa rona li lebisang ho tsona. Tabeng ea rona, li-invoice li bua ka bareki:

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

Bakeng sa ho amahanya liakhaonto le li-ID tsa bareki ka nepo, re hloka ho tseba kapa ho hlahisa li-identifiers tsena. Ha re kenye likarolo tlas'a tsona:

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

Ha re sebeliseng mokhoa oa ho hokahanya oa litafole o hlalositsoeng kaholimo ka tokiso e nyane - re ke ke ra ntlafatsa kapa ra hlakola eng kapa eng e tafoleng eo re e batlang, hobane re kenya bareki "append-feela":

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

Ha e le hantle, tsohle li teng invoice_import Hona joale re na le sebaka sa ho ikopanya se tlatsitsoeng client_id, eo re tla kenya invoice ka eona.

Source: www.habr.com

Eketsa ka tlhaloso