ProHoster > Blog > Pulega > DBA: fa'atulaga lelei fa'atasi ma fa'aulufale mai

DBA: fa'atulaga lelei fa'atasi ma fa'aulufale mai

Mo faʻalavelave lavelave o seti faʻamaumauga tetele (eseese ETL faiga: fa'aulufale mai, fa'aliliuga ma fa'atasi ma se puna mai fafo) e masani ona i ai se mana'oga "manatua" mo se taimi le tumau ma vave faʻagasolo se mea tele.

O se galuega masani o lenei ituaiga e masani ona foliga mai e pei o lenei: “Iinei lava matagaluega faatausitusi la'u ese mai le faletupe o tausia o tupe totogi mulimuli na maua, e tatau ona e faʻapipiʻi vave i luga o le upega tafaʻilagi ma faʻafesoʻotaʻi i au faʻamatalaga"

Ae a amata ona fuaina le tele o lenei "mea" i le fiaselau o megabytes, ma e tatau ona faʻaauau pea ona galulue le tautua ma le database 24x7, e tele aʻafiaga e tulaʻi mai e faʻaleagaina ai lou olaga.
DBA: fa'atulaga lelei fa'atasi ma fa'aulufale mai
Ina ia taulimaina i latou i PostgreSQL (ma e le gata i totonu), e mafai ona e faʻaogaina nisi faʻataʻitaʻiga e mafai ai ona e faʻagasolo vave mea uma ma faʻaitiitia le faʻaaogaina o punaoa.

1. O fea e lafo ai?

Muamua, sei o tatou filifili po o fea e mafai ona tatou lafoina ai faʻamaumauga tatou te mananaʻo e "faʻagasolo."

1.1. laulau le tumau

I le faʻavae, mo PostgreSQL laulau le tumau e tutusa ma isi. O le mea lea, e pei o talitonuga faanuupo “O mea uma o iai e teuina i le na o le manatua, ma e mafai ona muta”. Ae e iai foʻi le tele o eseesega taua.

O lau lava "igoa igoa" mo soʻotaga taʻitasi i le database

Afai e lua feso'ota'iga taumafai e fa'afeso'ota'i i le taimi e tasi CREATE TABLE x, ona maua ai lea e se tasi sese le tulaga ese meafaitino fa'amaumauga.

Ae afai e taumafai uma e faatino CREATE TEMPORARY TABLE x, ona fai ai lea e lua e masani, ma o le a maua e tagata uma lau kopi laulau. Ma o le a leai se mea e tutusa ai i latou.

"Faʻaleagaina e le tagata lava ia" pe a motusia

A tapunia le feso'ota'iga, e otometi lava ona tape uma laulau le tumau, fa'apea ma le lima DROP TABLE x e leai se aoga vagana ai...

Afai o lo'o e galue pgbouncer i faiga fa'atauga, ona faʻaauau ai lea ona talitonu le database o loʻo galue pea lenei fesoʻotaʻiga, ma o loʻo iai pea lenei laulau le tumau.

O le mea lea, o le taumafai e toe faia, mai se isi fesoʻotaʻiga i le pgbouncer, o le a iʻu ai i se mea sese. Ae e mafai ona aloese mai lenei mea i le faʻaaogaina CREATE TEMPORARY TABLE IF NOT EXISTS x.

E moni, e sili atu le aua le faia lenei mea, aua e mafai ona e "faafuaseʻi" maua iina faʻamaumauga o totoe mai le "tagata muamua". Nai lo lena, e sili atu le faitau i le tusi lesona ma vaʻai pe a fatuina se laulau e mafai ona faʻaopoopo ON COMMIT DROP - o lona uiga, pe a maeʻa le fefaʻatauaiga, o le laulau o le a otometi lava ona tape.

Le toe faia

Talu ai e na'o se feso'ota'iga fa'apitoa, o laulau le tumau e le toe faia. Ae e fa'aumatia ai le mana'oga mo le fa'amauina fa'alua o fa'amaumauga i le faupuega + WAL, o lea INSERT / UPDATE / DELETE i totonu e sili atu le vave.

Ae talu ai o se laulau le tumau o se laulau "toetoe lava masani", e le mafai foi ona faia i luga o se kopi. Le itiiti ifo mo le taimi nei, e ui lava o le patch fetaui ua faʻasalalau mo se taimi umi.

1.2. LE LAVA LAVA

Ae o le a le mea e tatau ona e faia, mo se faʻataʻitaʻiga, pe afai e iai sau faiga faʻalavelave ETL e le mafai ona faʻatinoina i totonu o se fefaʻatauaiga e tasi, ae o loʻo e iai pea. pgbouncer i faiga fa'atauga? ..

Po'o le tele o fa'amatalaga e fa'apea E le lava le bandwidth i le tasi so'oga mai se fa'amaumauga (faitau, tasi le faagasologa i le PPU)?..

Po'o nisi gaioiga o lo'o faia asynchronously i sootaga eseese?..

E na o le tasi le filifiliga iinei - faia mo sina taimi se laulau le tumau. Pun, ioe. O lena lava:

  • faia "a'u lava" laulau ma le tele o igoa fa'afuase'i ina ia 'aua ne'i feso'ota'i ma so'o se tasi
  • Aveese: fa'atumuina i fa'amaumauga mai se puna mai fafo
  • Suiga: liua, fa'atumu i fanua feso'ota'iga autu
  • laʻu: sasa'a fa'amaumauga saunia i laulau fa'atatau
  • tapeina "o'u" laulau

Ma o lenei - o se lago i le suauu. Ae o le mea moni, tusi uma i PostgreSQL tupu faalua - muamua i le WAL, ona i totonu o le laulau/index tino. O nei mea uma e faia e lagolago ai le ACID ma faʻasaʻo faʻamatalaga faʻamatalaga i le va COMMIT'nutti ma ROLLBACK'null feuiaiga.

Ae matou te le manaʻomia lenei mea! Ua ia i tatou le faagasologa atoa Pe na manuia atoatoa pe leai.. E le afaina pe o le a le tele o fefaʻatauaiga vavalalata o le ai ai - matou te le fiafia i le "faʻaauauina o le faagasologa mai le ogatotonu," aemaise lava pe a le o manino poʻo fea na i ai.

Ina ia faia lenei mea, o le PostgreSQL developers, i tua i le version 9.1, faʻafeiloaʻi se mea e pei o UNLOGGED laulau:

Faatasi ai ma lenei faʻailoga, ua faia le laulau e pei o le unloggged. O fa'amaumauga o lo'o tusia i laulau e le'i fa'amauina e le alu i totonu o le fa'amaumauga tusitusia i luma (silasila i le Mataupu 29), ma fa'atupuina ai ia laulau. galue vave atu nai lo le masani. Peitaʻi, e lē puipuia i latou mai le lē taulau; i le tulaga o le faaletonu o le server poʻo le faʻalavelave faʻafuaseʻi, se laulau e leʻi faʻaogaina tipi otometi. E le gata i lea, o mea o loʻo i totonu o le laulau e leʻi faʻapipiʻiina e le toe faia e pologa pologa. So'o se fa'ailoga e faia i luga o se laulau e le'i fa'amauina e otometi lava ona fa'atalatala.

I se faaupuga puupuu, o le a sili atu le vave, ae afai e "pa'ū" le server database, o le a le fiafia. Ae faʻafia ona tupu lenei mea, ma e iloa e lau faʻagasologa ETL le faʻasaʻo saʻo "mai le ogatotonu" pe a uma ona "toe faʻafouina" le database?..

Afai e leai, ma o le mataupu o loʻo i luga e tutusa ma oe, faʻaaoga UNLOGGEDae leai lava aua le fa'atagaina lenei uiga i luga o laulau moni, o faʻamatalaga e pele ia te oe.

1.3. ON COMMIT { TA'E LALA | PA'U}

O lenei faufale e mafai ai ona e faʻamaonia amioga faʻapitoa pe a maeʻa se fefaʻatauaiga pe a fatuina se laulau.

i ON COMMIT DROP Ua uma ona ou tusia i luga, e gaosia DROP TABLE, ae faatasi ma ON COMMIT DELETE ROWS o le tulaga e sili atu ona manaia - e gaosia iinei TRUNCATE TABLE.

Talu ai o atinaʻe atoa mo le teuina o le meta-faʻamatalaga o se laulau le tumau e tutusa lelei ma le laulau masani, ona O le faia pea ma le tapeina o laulau le tumau e oʻo atu ai i le ogaoga o le "fua" o laulau faʻaoga pg_vasega, pg_attribute, pg_attrdef, pg_faalagolago,…

Vaʻai nei o loʻo i ai sau tagata faigaluega i luga o se fesoʻotaʻiga tuusaʻo i le database, lea e tatalaina ai se fefaʻatauaiga fou i sekone taʻitasi, fatuina, faʻatumu, faʻagasologa ma tapeina se laulau le tumau ... O le ai ai le tele o lapisi faʻaputuina i totonu o laulau faʻapipiʻi, ma o le a mafua ai taofi faaopoopo mo gaioiga taitasi.

I se tulaga lautele, aua le faia lenei mea! I lenei tulaga e sili atu le aoga CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS ave ese mai le taamilosaga o fefaʻatauaiga - ona oʻo atu lea i le amataga o fefaʻatauaiga fou taʻitasi ua uma ona fai laulau o le a iai (sefe se telefoni CREATE), ae o le a gaogao, faafetai ia TRUNCATE (na matou faasaoina foi lona valaau) pe a maeʻa le fefaʻatauaiga muamua.

1.4. PEI...E A'AI...

Na ou taʻua i le amataga o se tasi o faʻaoga masani mo laulau le tumau o ituaiga eseese o faʻaulufale mai - ma o le tagata e faʻatupuina ma le vaivai kopi-faʻapipiʻi le lisi o fanua o le laulau faʻatatau i le taʻutinoga o lona le tumau...

Ae o le paie o le afi lea o le alualu i luma! Mea tonu lava lena fai se laulau fou "fa'avae i luga o fa'ata'ita'iga" e mafai ona sili atu ona faigofie:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Talu ai e mafai ona e gaosia le tele o faʻamatalaga i totonu o lenei laulau, o le suʻesuʻeina o le a le vave lava. Ae o loʻo i ai se fofo masani i lenei mea - indexes! ma, ioe, o se laulau le tumau e mafai foi ona i ai faasino igoa.

Talu ai, e masani lava, o faʻamatalaga manaʻomia e fetaui ma faʻamatalaga o le laulau faʻatatau, e mafai ona e tusitusi LIKE target_table INCLUDING INDEXES.

Afai e te manaʻomia foʻi DEFAULT-values ​​​​(mo se faʻataʻitaʻiga, e faʻatumu ai mea taua autu), e mafai ona e faʻaogaina LIKE target_table INCLUDING DEFAULTS. Pe na'o - LIKE target_table INCLUDING ALL - kopi fa'aletonu, fa'asinomaga, fa'agata,...

Ae o iinei e tatau ona e malamalama pe afai na e faia fa'aulufaleina laulau vave fa'atasi ma fa'asino igoa, ona umi lea o le utaina o fa'amaumauganai lo pe afai e te faʻatumu muamua mea uma, ona taʻavale lea o faʻamatalaga - vaʻai pe faʻapefea ona fai lenei mea e fai ma faʻataʻitaʻiga pg_dump.

I se aotelega, RTFM!

2. E faapefea ona tusi?

Se'i ou fai atu - fa'aaoga COPY-tafe nai lo le "fa'auma" INSERT, fa'avavevave i nisi taimi. E mafai fo'i ona e sa'o mai se faila na faia muamua.

3. Fa'afefea ona fa'agasolo?

O lea la, seʻi o tatou tuʻuina atu e pei o lenei:

  • e iai sau laulau ma fa'amaumauga o tagata o tausia o lo'o teuina i lau fa'amaumauga 1M faamaumauga
  • o aso uma e lafo atu ai e le kalani se mea fou ia te oe atoa "ata"
  • mai aafiaga e te iloa lena mea mai lea taimi i lea taimi e le sili atu i le 10K faamaumauga ua suia

O se faʻataʻitaʻiga masani o sea tulaga o KLADR faavae - e tele tuatusi i le aofaʻi, ae i vaiaso taʻitasi faʻapipiʻi e itiiti lava suiga (suiga o nofoaga, tuʻufaʻatasia auala, foliga o fale fou) e oʻo lava i luga o le atunuʻu.

3.1. Fa'atasi uma algorithm

Mo le faigofie, seʻi tatou fai atu e te le manaʻomia le toe faʻatulagaina o faʻamaumauga - naʻo le aumaia o le laulau i le mea e manaʻomia, o lona uiga:

  • aveese mea uma ua lē o toe iai
  • faʻafouina mea uma ua uma ona i ai ma e manaʻomia ona faʻafouina
  • ia faʻaofi mea uma e le'i tupu

Aisea e tatau ai ona faia gaioiga i lenei faasologa? Aua o le auala lea o le a laititi ai le lapopoa o le laulau (manatua MVCC!).

TA'E MAI Dst

Leai, o le mea moni e mafai ona e maua i le na o le lua gaioiga:

  • aveese (DELETE) mea uma i le lautele
  • ia faʻaofi uma mai le ata fou

Ae i le taimi lava e tasi, faafetai i le MVCC, Ole tele ole laulau ole a fa'aopoopo fa'alua! O le mauaina o le +1M ata o fa'amaumauga i le laulau ona o le 10K fa'afouga ua matua'i toe fa'afoliga...

TULUCATE dst

E iloa e se tagata e sili atu le poto masani e mafai ona faʻamamaina le laulau atoa i le taugofie:

  • kilia (TRUNCATE) le laulau atoa
  • ia faʻaofi uma mai le ata fou

O le auala e aoga, o nisi taimi e talafeagai, ae o loʻo i ai se faʻafitauli ... O le a matou faʻaopoopoina faʻamaumauga 1M mo se taimi umi, o lea e le mafai ai ona matou tuʻu le laulau e avanoa mo lenei taimi atoa (e pei ona tupu e aunoa ma le afifiina i se fefaʻatauaiga e tasi).

O lona uiga:

  • ua tatou amata fefa'ataua'iga umi
  • TRUNCATE fa'atupu AccessExclusive- poloka
  • matou te faia le faʻaofiina mo se taimi umi, ma isi tagata uma i lenei taimi e le mafai foi SELECT

E iai se mea e lē o sologa lelei...

SUIA LE LAVA... TO'U IGOA... / FA'ATU LE LA'AU...

O le isi filifiliga o le fa'atumu o mea uma i se isi laulau fou, ona toe fa'aigoa lea i le mea tuai. O nai mea laiti leaga:

  • pea foi AccessExclusive, e ui ina matua itiiti le taimi
  • ua toe setiina fuafuaga/fuainumera uma o fesili mo lenei laulau, mana'omia le tamo'e ILOILO
  • ua gau uma ki fafo (FK) i le laulau

Sa i ai se WIP patch mai Simon Riggs na fautuaina le faia ALTER-o se gaioiga e sui ai le laulau laulau i le tulaga faila, e aunoa ma le paʻi i fuainumera ma FK, ae leʻi aoina le korama.

TA'E, FA'ATAU, FA'ATU

O lea la, matou te faʻamautu i luga o le filifiliga e le polokaina o gaioiga e tolu. Toeitiiti tolu... Fa'afefea ona fai lenei mea i se tulaga sili ona lelei?

-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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. Fa'aulufaleina pe a mae'a fa'agasologa

I le KLADR lava e tasi, o fa'amaumauga uma ua suia e tatau ona fa'aopoopoina e ala i le fa'agasologa o fa'asologa - fa'avasega, fa'ailoga fa'ailoga, ma fa'aitiitia i fausaga mana'omia. Ae fa'afefea ona e iloa- o le a tonu le suigae aunoa ma le faʻalavelaveina o le code synchronization, lelei e aunoa ma le paʻi i ai?

Afai na'o lau fa'agasologa e iai le avanoa tusitusi i le taimi o le fa'amaopoopoina, ona mafai lea ona e fa'aogaina se fa'aoso e aoina uma ai suiga mo i matou:

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

Ole taimi nei e mafai ona tatou faʻaogaina faʻaoso aʻo leʻi amataina le faʻamaopoopoina (pe faʻatagaina i latou e ala i 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();

Ona matou aveese mai lea ma le filemu uma suiga matou te manaʻomia mai laulau ogalaau ma faʻataʻitaʻiina e ala i isi tagata faʻapipiʻi.

3.3. Fa'aulufaleina Seti Feso'ota'i

I luga atu na matou iloiloina mataupu pe a tutusa le fausaga o faʻamaumauga o le puna ma le mea e alu i ai. Ae faʻafefea pe a fai o le faʻapipiʻiina mai se faiga i fafo o loʻo i ai se faatulagaga e ese mai le fausaga o le teuina i totonu o la tatou faʻamaumauga?

Seʻi o tatou fai ma faʻataʻitaʻiga le teuina o tagata faʻatau ma a latou tala, le filifiliga masani "tele-i-tasi":

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

Ae o le download mai se puna mai fafo e oʻo mai ia i matou i le tulaga o le "uma i le tasi":

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

E manino lava, e mafai ona fa'aluaina fa'amaumauga a tagata fa'atau i lenei fa'amatalaga, ma o le fa'amaumauga autu o le "tupe":

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

Mo le faʻataʻitaʻiga, naʻo le faʻaofiina o matou faʻamaumauga o suʻega, ae manatua - COPY sili atu ona lelei!

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

Muamua, seʻi o tatou faamatilatila na “vaega” lea e faasino i ai a tatou “mea moni”. I la matou tulaga, o pili e faʻatatau i tagata faʻatau:

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

Ina ia mafai ona faʻafesoʻotaʻi saʻo tala ma ID tagata faʻatau, e manaʻomia muamua ona tatou suʻeina pe faʻatupuina nei faʻamatalaga. Se'i o tatou fa'aopoopo fanua i lalo:

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

Sei o tatou fa'aoga le metotia fa'atasi o le laulau o lo'o fa'amatalaina i luga ma sina teuteuga la'ititi - matou te le toe fa'afouina pe tape se mea i le laulau fa'atatau, aua matou te fa'aulufale mai tagata fa'atau "na'o":

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

O le mea moni, o mea uma o loʻo i totonu invoice_import O lea la ua fa'atumuina le avanoa fa'afeso'ota'i client_id, lea o le a matou faʻaofi ai le pili.

puna: www.habr.com

Faaopoopo i ai se faamatalaga