🥇DBA: mandamina tsara ny fampifanarahana sy ny fanafarana

Ho an'ny fanodinana sarotra angon-drakitra lehibe (samy hafa Ny fizotran'ny ETL: fanafarana, fiovam-po ary fampifanarahana amin'ny loharano ivelany) matetika no ilaina "tadidio" vonjimaika ary haingana dia haingana zavatra voluminous.

Ny asa mahazatra toy izany dia matetika toa izao: "Eto indrindra sampana fitantanam-bola navoaka avy amin'ny banky mpanjifa ny fandoavam-bola voaray farany, mila mampiakatra azy haingana any amin'ny tranokala ianao ary mampifandray azy ireo amin'ny kaontinao"

Saingy rehefa manomboka mirefy amin'ny megabytes an-jatony ny habetsahan'ity "zavatra" ity, ary ny serivisy dia tsy maintsy manohy miasa miaraka amin'ny angon-drakitra 24x7, maro ny voka-dratsiny izay hanimba ny fiainanao.

Mba hiatrehana azy ireo ao amin'ny PostgreSQL (ary tsy ao anatin'izany ihany), dia azonao atao ny mampiasa optimizations izay ahafahanao mandamina haingana kokoa ny zava-drehetra ary amin'ny fanjifana loharano kely kokoa.

1. Aiza no halefa?

Voalohany, andeha isika hanapa-kevitra hoe aiza no ahafahantsika mampakatra ny angon-drakitra tiantsika “hodinkodina”.

1.1. Latabatra vonjimaika (TABLE TEMPORARY)

Amin'ny ankapobeny, ho an'ny tabilao vonjimaika PostgreSQL dia mitovy amin'ny hafa rehetra. Noho izany, finoanoam-poana toy ny “Ny zavatra rehetra ao dia voatahiry ao anaty fitadidiana ihany, ary mety hifarana”. Saingy misy ihany koa ny fahasamihafana lehibe maromaro.

Ny "namespace" anao manokana ho an'ny fifandraisana tsirairay amin'ny angon-drakitra

Raha misy fifandraisana roa manandrana mifandray amin'ny fotoana iray ihany CREATE TABLE x, dia hisy hahazo tokoa fahadisoana tsy maha-tokana database objects.

Fa raha samy miezaka manatanteraka CREATE TEMPORARY TABLE x, dia samy hanao izany ara-dalàna, ary ny rehetra dia hahazo ny kopiao latabatra. Ary tsy hisy zavatra iombonan’izy ireo.

"Self-destruct" rehefa tapaka

Rehefa mikatona ny fifandraisana dia voafafa ho azy ny latabatra vonjimaika rehetra, ka atao amin'ny tanana DROP TABLE x tsy misy dikany afa-tsy...

Raha miasa ianao pgbouncer amin'ny fomba fifampiraharahana, dia mbola mino ny angon-drakitra fa mbola mavitrika io fifandraisana io, ary ao anatin'izany dia mbola misy io latabatra vonjimaika io.

Noho izany, ny fiezahana hamorona azy indray, avy amin'ny fifandraisana hafa mankany amin'ny pgbouncer, dia hiteraka hadisoana. Fa izany dia azo fehezina amin'ny fampiasana CREATE TEMPORARY TABLE IF NOT EXISTS x.

Na izany aza, tsara kokoa ny tsy manao izany na izany aza, satria afaka "tampoka" ianao hahita ao ny angona sisa tavela amin'ny "tompony teo aloha". Fa kosa, tsara kokoa ny mamaky ny boky ary jereo fa rehefa mamorona latabatra dia azo atao ny manampy ON COMMIT DROP - izany hoe rehefa vita ny fifampiraharahana dia ho voafafa ho azy ny latabatra.

Tsy replication

Satria an'ny fifandraisana manokana ihany izy ireo dia tsy averina averina ny latabatra vonjimaika. SAINGY izany dia manafoana ny filana firaketana an-tsoratra indroa ny angon-drakitra in heap + WAL, ka INSERT/UPDATE/DELETE ao dia haingana kokoa.

Saingy satria ny latabatra vonjimaika dia mbola latabatra "saika mahazatra", dia tsy azo noforonina amin'ny kopia ihany koa. Farafaharatsiny amin'izao fotoana izao, na dia efa nivezivezy hatry ny ela aza ny patch mifanaraka amin'izany.

1.2. TABLET TSY MISY LOGE

Fa inona no tokony hataonao, ohatra, raha manana karazana dingana ETL sarotra ianao izay tsy azo ampiharina ao anatin'ny fifampiraharahana iray, fa mbola manana pgbouncer amin'ny fomba fifampiraharahana? ..

Na ny fikorianan'ny data dia midadasika be Tsy ampy ny bandwidth amin'ny fifandraisana iray avy amin'ny angon-drakitra (vakio, dingana iray isaky ny CPU)?..

Na mandeha ny hetsika sasany asynchronously amin'ny fifandraisana samihafa?..

Iray ihany ny safidy eto - mamorona latabatra tsy vonjimaika. Pun, eny. Izany hoe:

namorona latabatra "ny ahy" miaraka amin'ny anarana kisendrasendra indrindra mba tsy hifaneraserana amin'iza na iza
hanesorana: nameno azy ireo angona avy amina loharano ivelany
hanova: avadika, fenoina amin'ny sehatra fampifandraisana
Load: nandraraka angona efa vonona ho amin'ny latabatra kendrena
voafafa ny latabatra “my”.

Ary ankehitriny - lalitra amin'ny menaka. Raha ny tena izy, ny fanoratana rehetra ao amin'ny PostgreSQL dia mitranga indroa - voalohany amin'ny WAL, avy eo mankany amin'ny vatana latabatra/index. Izany rehetra izany dia natao hanohanana ny ACID sy hanitsy ny fahitana ny angon-drakitra eo anelanelan'ny COMMIT'nosy ary ROLLBACK' null transactions.

Saingy tsy mila izany isika! Manana ny dingana manontolo isika Na nahomby tanteraka izany na tsia.. Tsy maninona na firy na firy ny fifampiraharahana eo anelanelany - tsy liana amin'ny "fanohizana ny dingana avy eo afovoany" izahay, indrindra rehefa tsy mazava ny toerana nisy azy.

Mba hanaovana izany, ny mpamorona PostgreSQL, niverina tamin'ny version 9.1, dia nampiditra zavatra toy ny tabilao UNLOGGED:

Miaraka amin'io famantarana io, ny latabatra dia noforonina ho tsy voarakitra. Ny angon-drakitra voasoratra amin'ny tabilao tsy voarakitra dia tsy mandeha amin'ny diary fanoratana (jereo ny Toko 29), ka mahatonga ny tabilao toy izany miasa haingana kokoa noho ny mahazatra. Tsy afa-bela amin’ny tsy fahombiazana anefa izy ireo; raha sendra ny tsy fahombiazan'ny server na ny fanakatonana vonjy maika, latabatra tsy voarakitra tapaka ho azy. Fanampin'izany, ny votoatin'ny latabatra tsy voarakitra tsy averina manandevo mpizara. Izay fanondroana noforonina eo amin'ny latabatra tsy voarakitra dia lasa tsy voarakitra ho azy.

Raha fintinina, ho haingana kokoa izany, fa raha "milatsaka" ny mpizara database, dia tsy hahafinaritra izany. Saingy impiry no mitranga izany, ary mahafantatra ny fomba hanitsiana an'io araka ny tokony ho izy "avy eo afovoany" ve ny fizotran'ny ETL anao aorian'ny "hamelombelona" ny angon-drakitra?..

Raha tsy izany, ary ny tranga etsy ambony dia mitovy amin'ny anao, ampiasao UNLOGGEDfa na oviana na oviana aza avela eo amin'ny latabatra tena izy io toetra io, ny angon-drakitra izay tena tianao.

1.3. ON COMMIT { DELETE ROWS | DROP}

Ity fananganana ity dia ahafahanao mamaritra ny fihetsika mandeha ho azy rehefa vita ny fifampiraharahana rehefa mamorona latabatra.

amin'ny ON COMMIT DROP Efa nosoratako etsy ambony, miteraka DROP TABLE, fa miaraka amin'ny ON COMMIT DELETE ROWS mahaliana kokoa ny toe-javatra - dia novokarina eto TRUNCATE TABLE.

Satria ny fotodrafitrasa manontolo amin'ny fitehirizana ny meta-famariparitana ny latabatra vonjimaika dia mitovy tanteraka amin'ny an'ny latabatra mahazatra, dia Ny famoronana tsy tapaka sy ny famafana ny latabatra vonjimaika dia mitarika ho amin'ny "fivontosana" mafy amin'ny latabatra rafitra pg_class, pg_attribute, pg_attrdef, pg_depend,…

Alao sary an-tsaina izao fa manana mpiasa ianao amin'ny fifandraisana mivantana amin'ny angon-drakitra, izay manokatra fifampiraharahana vaovao isaky ny segondra, mamorona, mameno, manamboatra ary mamafa latabatra vonjimaika... Hisy ny fako mihodinkodina miangona ao amin'ny latabatra rafitra, ary izany dia hiteraka freins fanampiny isaky ny fandidiana.

Amin'ny ankapobeny, aza manao izany! Amin'ity tranga ity dia mahomby kokoa CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS esory amin'ny tsingerin'ny fifampiraharahana izany - dia amin'ny fiandohan'ny fifanakalozana vaovao tsirairay dia efa misy ny latabatra hisy (mitahiry antso CREATE), fa ho foana, misaotra an'i TRUNCATE (voatahiry ihany koa ny antso) rehefa vita ny fifampiraharahana teo aloha.

1.4. TIA...TAO...

Nolazaiko tany am-piandohana fa ny iray amin'ireo tranga fampiasa mahazatra ho an'ny latabatra vonjimaika dia karazana fanafarana isan-karazany - ary ny mpamorona dia mandika sy mametaka ny lisitry ny saha misy ny latabatra kendrena amin'ny fanambarana ny vonjimaika azy...

Fa ny hakamoana no motera fandrosoana! Izany no antony mamorona latabatra vaovao "mifototra amin'ny santionany" mety ho tsotra kokoa izany:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Koa satria afaka mamorona angon-drakitra maro ao anatin'ity tabilao ity ianao, dia tsy ho haingana mihitsy ny fitadiavana azy io. Saingy misy vahaolana nentim-paharazana amin'izany - index! ary eny, Ny latabatra vonjimaika dia afaka manana indexes ihany koa.

Satria, matetika, ny fanondroana ilaina dia mifanandrify amin'ny fanondroan'ny latabatra kendrena, dia azonao atao ny manoratra tsotra izao LIKE target_table INCLUDING INDEXES.

Raha mila koa ianao DEFAULT-values (ohatra, hamenoana ny soatoavina fototra fototra), azonao ampiasaina LIKE target_table INCLUDING DEFAULTS. Na tsotra fotsiny - LIKE target_table INCLUDING ALL - kopia ny default, indexes, constraints,...

Fa eto dia mila mahatakatra fa raha namorona manafatra latabatra avy hatrany miaraka amin'ny indexes, avy eo dia haharitra ela ny angon-drakitra entinanoho ny raha fenoinao aloha ny zava-drehetra, ary avy eo ahodino ny fanondroana - jereo ny fomba anaovany izany ho ohatra pg_dump.

Raha fintinina, RTFM!

2. Ahoana ny fanoratana?

Avelao aho hiteny - ampiasao izany COPY- mikoriana fa tsy "pack" INSERT, fanafainganana indraindray. Azonao atao mihitsy aza ny mivantana avy amin'ny rakitra efa novolavolaina.

3. Ahoana ny fikarakarana?

Noho izany, andeha hojerentsika toy izao ny intro:

manana latabatra misy angona mpanjifa voatahiry ao amin'ny angon-drakitrao ianao 1M firaketana
isan'andro misy mpanjifa mandefa vaovao aminao "sary" feno
avy amin'ny traikefa dia fantatrao izany indraindray tsy mihoatra ny 10K no ovaina

Ohatra mahazatra amin'ny toe-javatra toy izany dia KLADR base — be dia be ny adiresy, fa isaky ny upload isan-kerinandro dia misy fiovana kely (fanombanana ny anaran'ny tanàna, fampifangaroana arabe, fisehon'ny trano vaovao) na dia eo amin'ny sehatra nasionaly aza.

3.1. Algorithm synchronization feno

Ho an'ny fahatsorana, andao lazaina fa tsy mila manamboatra ny angon-drakitra akory ianao - ento fotsiny amin'ny endrika tiana ny latabatra, izany hoe:

esory izay rehetra tsy misy intsony
vaovao farany izay rehetra efa nisy ary mila havaozina
Mampidira ny zavatra rehetra mbola tsy nitranga

Nahoana no tokony hatao amin'io lamina io ny hetsika? Satria toy izany no hampitombo kely ny haben'ny latabatra (tsarovy ny MVCC!).

EFA amin'ny dst

Tsia, mazava ho azy fa afaka manao zavatra roa fotsiny ianao:

esory (DELETE) ny zavatra rehetra amin’ny ankapobeny
Mampidira rehetra avy amin'ny sary vaovao

Saingy miaraka amin'izay koa, misaotra ny MVCC, Hitombo indroa katroka ny haben'ny latabatra! Ny fahazoana sary +1M amin'ny rakitsoratra ao amin'ny latabatra noho ny fanavaozana 10K dia tsy ampy loatra...

TRUNCATE dst

Ny mpamorona efa za-draharaha kokoa dia mahafantatra fa ny tablette iray manontolo dia azo diovina amin'ny vidiny mora:

madio (TRUNCATE) ny latabatra manontolo
Mampidira rehetra avy amin'ny sary vaovao

Ny fomba dia mahomby, indraindray azo ampiharina, fa misy ny olana... Hanampy rakitra 1M mandritra ny fotoana maharitra izahay, ka tsy afaka mamela ny latabatra ho foana mandritra izany fotoana izany (toy ny hitranga raha tsy mifono amin'ny fifampiraharahana tokana).

Izay midika:

manomboka isika fifampiraharahana maharitra
TRUNCATE mametraka Access Exclusive- fanakanana
manao ny fampidirana nandritra ny fotoana ela, ary ny hafa rehetra amin'izao fotoana izao tsy afaka akory SELECT

Misy zavatra tsy mandeha tsara...

ALTER TABLE… MANOVA ANARANA… / DROP TABLE…

Ny safidy hafa dia ny mameno ny zava-drehetra ao anaty tabilao vaovao misaraka, ary avy eo dia manonona azy fotsiny ho solon'ny taloha. Zavatra kely roa ratsy:

mbola koa Access Exclusive, na dia kely kokoa aza ny fotoana
averina ny drafitra/statistika fanontaniana rehetra momba ity latabatra ity, mila mihazakazaka ANALYZE
tapaka daholo ny fanalahidy vahiny (FK) mankany amin'ny latabatra

Nisy patch WIP avy amin'i Simon Riggs izay nanolo-kevitra ny hanao ALTER-Opération fanoloana ny vatan-databatra eo amin'ny haavon'ny rakitra, tsy mikasika ny antontan'isa sy FK, fa tsy nanangona quorum.

DELETE, UPDATE, INSERT

Noho izany, mipetraka amin'ny safidy tsy manakana ny hetsika telo izahay. Efa ho telo... Ahoana no hanaovana izany amin'ny fomba mahomby indrindra?

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

Ao amin'io KLADR io ihany, ny firaketana novaina rehetra dia tsy maintsy atao amin'ny alàlan'ny fanodinana aorian'ny fanodinana - ara-dalàna, asongadina ny teny fanalahidy ary ahena amin'ny rafitra ilaina. Fa ahoana no ahafantaranao- inona marina no niovatsy manasarotra ny kaody synchronisation, raha tsy mikitika mihitsy?

Raha toa ka ny fizotranao ihany no manana fidirana amin'ny fanoratana amin'ny fotoana fampifanarahana, dia azonao atao ny mampiasa trigger izay hanangona ny fanovana rehetra ho antsika:

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

Ankehitriny dia afaka mampihatra trigger isika alohan'ny hanombohan'ny synchronization (na avelao izy ireo amin'ny alàlan'ny 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();

Ary avy eo dia alaintsika am-pahatoniana ny fanovana rehetra ilaintsika avy amin'ny tabilao log ary mampandeha azy ireo amin'ny alàlan'ny mpitantana fanampiny.

3.3. Manafatra Sets mifandray

Etsy ambony dia nodinihinay ireo tranga izay mitovy ny firafitry ny angon-drakitra momba ny loharano sy toerana haleha. Ahoana anefa raha manana endrika hafa amin'ny rafitra fitahirizana ao amin'ny angon-drakitray ny fampiakarana avy amin'ny rafitra ivelany?

Andeha horaisintsika ho ohatra ny fitahirizana ny mpanjifa sy ny kaontiny, ny safidy mahazatra "maro-to-iray":

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

Fa ny fampidinana avy amin'ny loharano ivelany dia tonga amintsika amin'ny endrika "all in one":

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

Mazava ho azy fa azo adika amin'ity dikan-teny ity ny angona mpanjifa, ary ny "kaonty" no tena firaketana:

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

Ho an'ny modely, ampidiro fotsiny ny angona fitsapana, fa tadidio - COPY mahomby kokoa!

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

Voalohany, andeha hojerentsika ireo “fahatapahan-jiro” resahin’ny “zava-misy”. Amin'ny tranga misy anay, ny faktiora dia manondro ny mpanjifa:

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

Mba hampifandraisana tsara ny kaonty amin'ny karapanondro mpanjifa dia mila mitady na mamorona ireo famantarana ireo aloha isika. Andeha asiana saha eo ambanin'izy ireo:

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

Andao hampiasa ny fomba fampifanarahana latabatra voalaza etsy ambony miaraka amin'ny fanitsiana kely - tsy hanavao na hamafa na inona na inona ao amin'ny latabatra kendrena izahay, satria manafatra mpanjifa "ampiana-ihany" izahay:

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

Raha ny marina, ny zavatra rehetra dia ao anatiny invoice_import Efa feno ny saha fifandraisana client_id, izay hampidiranay ny faktiora.

Source: www.habr.com

DBA: mandamina tsara ny fampifanarahana sy ny fanafarana