ProHoster > Blog > Rêveberî > DBA: bi şareza hevdengkirin û îthalatê organîze bikin

DBA: bi şareza hevdengkirin û îthalatê organîze bikin

Ji bo pêvajoyek tevlihev a daneyên daneyên mezin (cuda pêvajoyên ETL: îtxalkirin, veguhertin û hevdemkirina bi çavkaniyek derveyî) pir caran hewcedarî heye bi demkî "bi bîr bîne" û tavilê zû pêvajo bike tiştekî mezin.

Karûbarek tîpîk a bi vî rengî bi gelemperî tiştek wusa dixuye: "Rast li vir beşa muhasebeyê ji banka muwekîlê daxist dravdanên paşîn ên hatine wergirtin, hûn hewce ne ku zû wan li malperê bar bikin û wan bi hesabên xwe ve girêdin."

Lê gava ku qebareya vê "tiştekî" bi sedan megabyte dest pê dike, û pêdivî ye ku karûbar bi databasa 24x7 re xebata xwe bidomîne, gelek bandorên alî derdikevin holê ku dê jiyana we xera bike.
DBA: bi şareza hevdengkirin û îthalatê organîze bikin
Ji bo ku hûn di PostgreSQL de bi wan re mijûl bibin (û ne tenê di wê de), hûn dikarin hin xweşbîniyan bikar bînin ku dê bihêle hûn her tiştî zûtir û bi xerckirina çavkaniyê kêmtir pêvajoyê bikin.

1. Li ku derê bişînin?

Pêşîn, em biryar bidin ku em dikarin daneyên ku em dixwazin "pêvajoyê" bikin li ku derê barkirin.

1.1. Tabloyên demkî (TAbloya DÊMÎ)

Di prensîbê de, ji bo PostgreSQL tabloyên demkî wekî yên din in. Ji ber vê yekê, xurafeyên mîna "Her tişt li wir tenê di bîranînê de tê hilanîn, û ew dikare biqede". Lê di heman demê de çend cûdahiyên girîng jî hene.

Ji bo her girêdana bi databasê re "navê cîhê" we

Ger du girêdan di heman demê de hewl bidin ku pêve bibin CREATE TABLE x, wê hingê kesek bê guman dê bigihîje xeletiya ne-yekserî tiştên databasê.

Lê heke her du jî hewl bidin ku îdam bikin CREATE TEMPORARY TABLE x, wê hingê her du jî wê bi gelemperî bikin, û her kes dê bigihîje kopiya te tables. Û dê di navbera wan de tiştek hevpar tune.

Dema veqetandinê "xwe-hilweşandin".

Dema ku girêdan girtî be, hemî tabloyên demkî bixweber têne jêbirin, ji ber vê yekê bi destan DROP TABLE x ji xeynî xalek tune ye...

Heke hûn bi rê ve dixebitin pgbouncer di moda danûstendinê de, wê demê databas berdewam dike ku bawer bike ku ev girêdan hîn çalak e, û tê de ev tabloya demkî hîn jî heye.

Ji ber vê yekê, hewldana afirandina wê dîsa, ji pêwendiyek cûda bi pgbouncer, dê bibe sedema xeletiyek. Lê ev dikare bi karanîna xwe were dorpêç kirin CREATE TEMPORARY TABLE IF NOT EXISTS x.

Rast e, çêtir e ku meriv vê yekê jî neke, ji ber ku wê hingê hûn dikarin "ji nişka ve" daneyên ku ji "xwediyê berê" mane bibînin. Di şûna wê de, pir çêtir e ku meriv manualê bixwîne û bibîne ku dema çêkirina tabloyek gengaz e ku meriv lê zêde bike ON COMMIT DROP - ango dema danûstendin biqede, tablo dê bixweber jê bibe.

Non-replication

Ji ber ku ew tenê girêdayî girêdanek taybetî ne, tabloyên demkî nayên dubare kirin. Lebê ev hewcedariya tomarkirina ducarî ya daneyan ji holê radike di heap + WAL de, ji ber vê yekê INSERT / NAVENDA NÛ / JÊKIRIN tê de pir zûtir e.

Lê ji ber ku tabloyek demkî hîn jî tabloyek "hema hema asayî" ye, ew nikare li ser kopiyek jî were afirandin. Bi kêmanî heya niha, her çend peşka têkildar ji demek dirêj ve belav bûye.

1.2. MASÊ NELOGGED

Lê divê hûn çi bikin, mînakî, heke we cûreyek pêvajoyek ETL ya dijwar heye ku nekare di yek danûstendinê de were bicîh kirin, lê hûn hîn jî hene pgbouncer di moda danûstendinê de? ..

An jî herikîna daneyan ew qas mezin e ku Li ser yek pêwendiyek têra bandê tune ji databasek (bixwîne, her CPU yek pêvajo)?..

An jî hin operasyon didomin asynchronously di têkiliyên cuda de?..

Li vir tenê vebijarkek heye - bi demkî tabloyek ne-demkî çêbike. Pun, erê. Ku heye:

  • tabloyên "min xwe" bi navên herî zêde rasthatî çêkirine da ku bi kesî re nekevin hev
  • Extract: wan bi daneya çavkaniyeke derve dagirtî
  • Guhertin: veguherandin, li qadên girêdana sereke dagirtin
  • Gazîname: Daneyên amade rijand nav tabloyên armancê
  • tabloyên "min" jêbirin

Û niha - firînek di rûnê de. Di rastî, hemî nivîsên di PostgreSQL de du caran diqewimin - yekem li WAL, paşê di nav tablo / laşên index. Hemî ev ji bo piştgirîkirina ACID-ê û dîtina daneya rast di navbera de têne kirin COMMIT'gûz û ROLLBACK'danûstandinên betal.

Lê em ne hewceyê vê yekê ne! Me hemû pêvajo heye An bi tevahî serketî bû an jî nebû.. Ne girîng e ka dê çend danûstendinên navîn hebin - em eleqedar nabin ku "pêvajoyê ji navîn bidomînin", nemaze dema ku ne diyar e ku ew li ku bû.

Ji bo vê yekê, pêşdebirên PostgreSQL, di guhertoya 9.1-ê de vegere, tiştek weha destnîşan kir maseyên UNLOGGED:

Bi vê nîşanê re, tablo wekî neqeydkirî tê afirandin. Daneyên ku li ser tabloyên neqeydkirî hatine nivîsandin di têketina pêş-nivîsandinê re derbas nabin (binihêre Beşa 29), û dibe sedem ku tabloyên weha kar pir zûtir ji ya normal. Lêbelê, ew ji têkçûnê bêpar in; di bûyera têkçûna serverê an girtina acîl de, tabloyek ne têketin bixweber qut kirin. Wekî din, naveroka tabloya neqeydkirî nayê dubare kirin ji bo pêşkêşkerên xulam. Nîşaneyên ku li ser tabloyek neqeydkirî hatine çêkirin bixweber nayên qeyd kirin.

Bi kin ew ê pir zûtir be, lê heke servera databasê "keve", ew ê ne xweş be. Lê ev çend caran diqewime, û gelo pêvajoya weya ETL dizane ku piştî "vejîna" databasê vê yekê rast "ji navîn" rast bike?..

Heke ne, û doza li jor mîna ya we ye, bikar bînin UNLOGGEDlê qet vê taybetmendiyê li ser tabloyên rastîn çalak nekin, daneyên ku ji we re ezîz in.

1.3. LI SER GIRTIN { RÊZAN JIBIRIN | DILOPKIRIN}

Ev avahî dihêle hûn gava ku danûstendinek di dema çêkirina tabloyekê de biqede, tevgera otomatîkî diyar bikin.

li ser ON COMMIT DROP Min berê li jor nivîsî, ew diafirîne DROP TABLE, lê bi ON COMMIT DELETE ROWS rewş balkêştir e - li vir tê çêkirin TRUNCATE TABLE.

Ji ber ku tevahiya binesaziya ji bo hilanîna meta-ravekirina tabloyek demkî tam wekî ya tabloyek asayî ye, wê hingê Afirandin û jêbirina domdar a tabloyên demkî rê li ber "werimandina" giran a tabloyên pergalê vedike pg_class, pg_attribute, pg_attrdef, pg_depend,…

Naha bifikirin ku we xebatkarek heye ku rasterast bi databasê re têkiliyek heye, ku her saniye danûstendinek nû vedike, tabloyek demkî diafirîne, tijî dike, pêvajo dike û jêdibe... Dê di tabloyên pergalê de zibilek zêde were berhev kirin, û ev dê bibe sedema frenên zêde ji bo her operasyonê.

Bi gelemperî, vê yekê nekin! Di vê rewşê de, ew pir bi bandor e CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS wê ji çerxa danûstendinê derxînin - wê hingê di destpêka her danûstendina nû de tablo jixwe ne dê hebin (telefonek xilas bike CREATE), lê dê vala bibe, bi xêra TRUNCATE (me banga wê jî xilas kir) dema ku danûstendina berê temam kir.

1.4. LIKE... TEWLÎ...

Min di destpêkê de behs kir ku yek ji dozên karanîna tîpîk ji bo tabloyên demkî cûrbecûr hinardekirin e - û pêşdebir bi westayî navnîşa zeviyên tabloya armancê di danezana xweya demkî de kopî-pêç dike...

Lê tembelî motora pêşveçûnê ye! Li rê da "li ser nimûneyê" tabloyek nû çêbikin ew dikare pir hêsan be:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Ji ber ku hûn hingê dikarin di vê tabloyê de gelek daneyan biafirînin, lêgerîna di nav wê de qet zû zû nabe. Lê ji bo vê çareseriyek kevneşopî heye - index! Û, erê, tabloyek demkî jî dikare indexan hebe.

Ji ber ku, pir caran, navnîşên pêwîst bi navnîşên tabloya armancê re hevaheng in, hûn dikarin bi hêsanî binivîsin LIKE target_table INCLUDING INDEXES.

Heke hûn jî hewce ne DEFAULT-nirxan (mînak, dagirtina nirxên sereke yên sereke), hûn dikarin bikar bînin LIKE target_table INCLUDING DEFAULTS. An jî bi tenê - LIKE target_table INCLUDING ALL - pêşnumayan, navnîşan, astengan,…

Lê li vir hûn hewce ne ku hûn fêm bikin ku heke we afirandin tabloya tavilê bi îndeksan re derxînin, wê hingê barkirina daneyan demek dirêj digireji bilî ku hûn pêşî her tiştî tijî bikin, û tenê wê hingê navnîşan berhev bikin - binihêrin ka ew çawa wekî mînakek çawa dike pg_dump.

Bi gelemperî RTFM!

2. Meriv çawa dinivîse?

Bila ez tenê bêjim - wê bikar bînin COPY-herikîn li şûna "pakêtê" INSERT, lezkirin carinan. Tewra hûn dikarin rasterast ji pelek pêş-çêkirî jî.

3. Çawa pêvajoyê?

Ji ber vê yekê, bila danasîna me tiştek wusa xuya bike:

  • we tabloyek bi daneyên xerîdar di databasa we de hatî hilanîn heye 1M tomar
  • her roj xerîdarek nû ji we re dişîne tam "wêne"
  • ji ezmûnê hûn dizanin ku dem bi dem bêtir ji 10K tomar nayê guhertin

Mînaka klasîk a rewşeke wiha ye Bingeha KLADR — Bi tevayî gelek navnîşan hene, lê di her barkirina heftane de guhertinên pir hindik hene (binavkirina navçeyan, berhevkirina kolanan, xuyangkirina xaniyên nû) tewra di asta neteweyî de.

3.1. Algorîtmaya hevdemkirinê ya tevahî

Ji bo sadebûnê, em bibêjin ku hûn ne hewce ne ku daneyan ji nû ve ava bikin - tenê tabloyê bînin forma xwestinê, ev e:

  • derxistin her tiştê ku êdî tune ye
  • nûve bikin her tiştê ku berê hebû û pêdivî ye ku were nûve kirin
  • danîne her tiştê ku hîn nebûye

Çima divê operasyon bi vî awayî bên kirin? Ji ber ku bi vî rengî mezinahiya sifrê dê kêm zêde mezin bibe (MVCC bi bîr bîne!).

JI dst JI BIKIN

Na, bê guman hûn dikarin tenê bi du operasyonan bi dest bixin:

  • derxistin (DELETE) her tişt bi giştî
  • danîne hemû ji wêneyê nû

Lê di heman demê de, spas ji MVCC re, Mezinahiya sifrê dê tam du caran zêde bibe! Ji ber nûvekirinek 1K girtina +10M wêneyên tomaran di tabloyê de ew qas zêde zêde ye...

TRUNCATE dst

Pêşdebirek bi tecrûbetir dizane ku tevahiya tabletê bi erzan dikare were paqij kirin:

  • zelal (TRUNCATE) tevaya maseyê
  • danîne hemû ji wêneyê nû

Rêbaz bi bandor e, carinan pir bikêrhatî ye, lê pirsgirêkek heye... Em ê ji bo demek dirêj tomarên 1M lê zêde bikin, ji ber vê yekê em nikanin tabloya vê demê vala bihêlin (wek ku dê di yek danûstendinê de were pêçandin).

Ku tê wê wateyê:

  • em dest pê dikin danûstendina dirêj
  • TRUNCATE ferz dike Access Exclusive- astengkirin
  • em ji bo demek dirêj ve, û her kesê din di vê demê de têxê dikin nikare jî SELECT

Tiştek ne baş e ...

MASÊ GUHERÎNIN… BÛNAVNIN… / MASÊ DIKIN…

Alternatîfek ev e ku meriv her tiştî di tabloyek nû ya cihêreng de tije bike, û dûv re bi tenê navê wê li şûna ya kevin biguhezîne. Çend tiştên piçûk ên nebaş:

  • hê jî Access Exclusive, her çend dem bi girîngî kêmtir be
  • hemî planên pirsê / statîstîkên ji bo vê tabloyê têne vesaz kirin, pêdivî ye ku ANALYZE bimeşîne
  • hemû kilîtên biyanî şikandine (FK) ser sifrê

Patchek WIP-ê ji Simon Riggs hebû ku pêşniyara çêkirinê kir ALTER-Operasyonek ji bo veguheztina laşê sifrê di asta pelê de, bêyî ku dest li ser statîstîk û FK bike, lê hêjmara berhev nekir.

JÊBÛN, NÛKIRIN, TÊKIRIN

Ji ber vê yekê, em li ser vebijarka ne-astengkirina sê operasyonan bi cih dibin. Hema sê... Meriv çawa vê yekê herî bi bandor bike?

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

Di heman KLADR-ê de, hemî tomarên guheztin divê bi pêvekirina paş-pêvajoyê re jî werin meşandin - normalîzekirin, peyvên sereke ronî kirin, û li strukturên hewce bêne kêm kirin. Lê hûn çawa dizanin - çi tam guherîbêyî ku koda hevdengkirinê tevlihev bikin, bi îdeal bêyî ku bi tevahî dest pê bikin?

Ger di dema hevdengkirinê de tenê pêvajoya we gihîştina nivîsandinê heye, wê hingê hûn dikarin tetikek bikar bînin ku dê hemî guhertinan ji me re berhev bike:

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

Naha em dikarin berî destpêkirina hevdengkirinê (an jî wan bi rêya wan çalak bikin). 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();

Dûv re em bi aramî hemî guheztinên ku em hewce ne ji tabloyên têketinê derdixin û wan bi navgînvanên din ve dimeşînin.

3.3. Importing Sets Girêdayî

Li jor me rewşên ku strukturên daneya çavkanî û meqsedê yek in nirxand. Lê heke barkirina ji pergalek derveyî xwedan formatek ji strukturên hilanînê yên di databasa me de cûda be?

Ka em wekî mînak hilanîna xerîdar û hesabên wan, vebijarka klasîk "gelek-bi-yek" bigirin:

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

Lê dakêşana ji çavkaniyek derveyî di forma "hemû di yek" de ji me re tê:

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

Eşkere ye, daneyên xerîdar dikarin di vê guhertoyê de werin dubare kirin, û tomara sereke "hesab" e:

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

Ji bo modelê, em ê tenê daneyên testa xwe têxin nav xwe, lê ji bîr mekin - COPY bikêrtir!

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

Pêşî, werin em wan "birîn"ên ku "rastiyên" me vedibêjin ronî bikin. Di doza me de, fatûre ji xerîdaran re vedibêjin:

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

Ji bo ku em hesabên rast bi nasnameyên xerîdar re têkildar bikin, pêşî hewce ye ku em van nasnameyan bibînin an çêbikin. Ka em zeviyan li binê wan zêde bikin:

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

Ka em rêbaza hevdengkirina tabloyê ku li jor hatî destnîşan kirin bi guheztinek piçûk bikar bînin - em ê di tabloya armancê de tiştek nûve nekin an jêbikin, ji ber ku em xerîdaran "tenê pêvekirin" derdixin:

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

Bi rastî, her tişt tê de ye invoice_import Naha me qada têkiliyê dagirtî ye client_id, bi ku em ê fatûreyê têxin.

Source: www.habr.com

Add a comment