ProHoster > Блог > Whakahaerenga > DBA: te whakarite i nga tukutahi me nga kawemai

DBA: te whakarite i nga tukutahi me nga kawemai

Mo te tukatuka matatini o nga huinga raraunga nui (rereke Nga tukanga ETL: kawemai, hurihanga me te tukutahi me te puna o waho) he maha nga wa e hiahiatia ana "mahara" mo te wa poto ka tere tonu te mahi he mea puhoi.

He ahua penei te ahua o te mahi penei: "I konei tonu te tari kaute i utaina mai i te peeke kiritaki Ko nga utu whakamutunga kua riro, me tere te tuku ake ki te paetukutuku ka hono atu ki o putea"

Engari ka timata te ruri o tenei "mea" ki te ine i roto i nga rau megabytes, a me mahi tonu te ratonga me te paataka 24x7, he maha nga paanga o te taha ka puta ka pakaru to oranga.
DBA: te whakarite i nga tukutahi me nga kawemai
Hei mahi ki a ratou i roto i te PostgreSQL (a ehara i te mea anake), ka taea e koe te whakamahi i etahi arotautanga ka taea e koe te tukatuka i nga mea katoa kia tere ake, me te iti o te kohi rauemi.

1. Ka tukuna ki hea?

Tuatahi, me whakatau tatou ki hea ka taea te tuku i nga raraunga e hiahia ana tatou ki te "tukatuka."

1.1. Ripanga rangitahi (TAPU TAPU)

Ko te tikanga, mo PostgreSQL tepu rangitahi he rite ki etahi atu. Na reira, whakapono whakapono rite "Ko nga mea katoa kei reira ka rongoa i roto i te maharatanga, ka mutu". Engari he maha ano nga rereketanga nui.

Tou ake “mokowāingoa” mō ia hononga ki te pātengi raraunga

Mena e rua nga hononga ka ngana ki te hono i te wa kotahi CREATE TABLE x, katahi ka tino whiwhi tetahi hapa ahurei-kore ahanoa pātengi raraunga.

Engari ki te ngana e rua ki te mahi CREATE TEMPORARY TABLE x, katahi ka mahia e rua, ka whiwhi katoa to kape tepu. A kare he mea e rite ana i waenganui i a raua.

"Whakamate-whaiaro" ka momotuhia

Ina katia te hononga, ka mukua aunoa nga ripanga rangitahi, na ma te ringa DROP TABLE x kahore he take engari ko...

Mena kei te mahi koe pgbouncer i roto i te aratau tauwhitinga, ka whakapono tonu te putunga raraunga kei te kaha tonu tenei hononga, kei roto tonu tenei ripanga rangitahi.

Na reira, ko te ngana ki te hanga ano, mai i tetahi hononga rereke ki te pgbouncer, ka puta he hapa. Engari ka taea tenei ma te whakamahi CREATE TEMPORARY TABLE IF NOT EXISTS x.

He pono, he pai ake kia kaua e mahi i tenei, na te mea ka taea e koe te "ahua" ka kitea i reira nga raraunga e toe ana mai i te "rangatira o mua". Engari, he pai ake te panui i te pukapuka me te kite i te wa e hanga ana he ripanga ka taea te taapiri ON COMMIT DROP - ara, ka oti te tauwhitinga, ka mukua aunoatia te ripanga.

Tukurua kore

Na te mea no tetahi hononga motuhake anake, karekau nga ripanga rangitahi e tukurua. Engari ka whakakorea te hiahia mo te tuhi rua o nga raraunga i roto i te puranga + WAL, no reira ko te KUPU / WHAKAUPAPA / WHAKAMAHI ki roto ka tino tere ake.

Engari i te mea he ripanga "tata noa" tonu te ripanga rangitahi, kaore e taea te hanga i runga i te tauira. I te iti rawa mo tenei wa, ahakoa ko te papaki e rite ana kua huri haere mo te wa roa.

1.2. TEARANGA KARERE

Engari me aha koe, hei tauira, mena kei a koe etahi momo mahi ETL uaua e kore e taea te whakatinana i roto i te tauwhitinga kotahi, engari kei a koe tonu pgbouncer i roto i te aratau tauwhitinga? ..

He nui ranei te rere o te raraunga He iti rawa te bandwidth i runga i te hononga kotahi mai i te pātengi raraunga (pānui, kotahi te tukanga mo ia PTM)?..

Kei te haere ranei etahi mahi tukutahi i roto i nga hononga rereke?..

Kotahi noa te whiringa i konei - hangahia he ripanga mo te wa poto. Pun, yeah. Koina:

  • i hanga tepu "aku ake" me nga ingoa matapōkere rawa kia kore ai e whiti ki tetahi
  • Tango: whakakiia ana ki nga raraunga mai i te puna o waho
  • Ka huri: kua hurihia, kua whakakiia ki nga mara hono matua
  • Utaina: i ringihia nga raraunga kua rite ki nga ripanga whaainga
  • i mukua "aku" ripanga

Na inaianei - he rere i roto i te hinu. Ko te tikanga, Ko nga tuhi katoa i PostgreSQL ka rua nga wa - tuatahi i te WAL, ka uru ki roto i nga tinana ripanga/taupū. Ka mahia enei mea katoa hei tautoko i te ACID me te whakatika i nga kitenga raraunga i waenga COMMIT'nati me ROLLBACK'nga whakawhitinga kore.

Engari kaore matou e hiahia ki tenei! Kei a matou te tukanga katoa Ahakoa i tino angitu, kaore ranei.. He aha te maha o nga tauwhitinga takawaenga - kaore matou e aro ki te "haere tonu i te mahi mai i waenganui," ina koa kaore i te maarama kei hea.

Ki te mahi i tenei, ko nga kaiwhakawhanake PostgreSQL, i hoki mai i te putanga 9.1, i whakauruhia he mea penei UNLOGGED ripanga:

Ma tenei tohu, ka hangaia te teepu kia kore e uru. Ko nga raraunga ka tuhia ki nga ripanga karekau e uru ki roto i te raarangi tuhi-i mua (tirohia te Upoko 29), ka puta ke he tere ake te mahi i mua. Heoi ano, kare ratou e parea ki te kore; ki te rahua te tūmau, te katinga ohorere ranei, he teepu kua kore te takiuru ka tapahia aunoatia. I tua atu, ko nga ihirangi o te ripanga kaore i tuhia kaore i tukuruatia ki nga tūmau taurekareka. Ko nga taurangi i hangaia i runga i te ripanga karekau i te raarangi ka kore e takiuru.

I te poto, ka tere ake, engari ki te "hinga" te tūmau pātengi raraunga, ka kore e pai. Engari e hia nga wa e pa ana tenei, a kei te mohio to tukanga ETL ki te whakatika tika "mai i waenganui" i muri i te "whakaora" i te putunga korero?..

Mena kaore, ka rite te keehi i runga ake nei ki a koe, whakamahia UNLOGGEDengari kaore rawa kaua e taea tenei huanga ki runga i nga ripanga tuturu, nga raraunga e arohaina ana e koe.

1.3. ON COMMIT { MUMU ROWS | WHAKATOKANGA}

Ma tenei hanga ka taea e koe te tautuhi i te whanonga aunoa ina oti he tauwhitinga ina hanga tepu.

i runga i ON COMMIT DROP Kua tuhia e au i runga ake nei, ka whakaputa DROP TABLE, engari me ON COMMIT DELETE ROWS he pai ake te ahuatanga - ka hangaia i konei TRUNCATE TABLE.

I te mea ko nga hanganga katoa mo te penapena i te whakaahua-meta o te tepu rangitahi he rite tonu ki tera o te teepu noa, katahi ka Ko te hanga me te whakakore i nga ripanga rangitahi ka arahi ki te "pupuhi" kino o nga ripanga punaha pg_class, pg_huanga, pg_attrdef, pg_depend,…

Whakaarohia inaianei kei a koe tetahi kaimahi i runga i te hononga tika ki te paataka raraunga, e whakatuwhera ana i tetahi tauwhitinga hou ia hekona, ka hanga, ka whakakiia, ka tukatuka me te whakakore i tetahi tepu rangitahi... Ka nui atu nga parapara kua kohia ki roto i nga ripanga punaha, a na tenei ka whai waahi atu mo ia mahi.

I te nuinga, kaua e mahi i tenei! I roto i tenei take he nui ake te whai hua CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS tangohia mai i te huringa tauwhitinga - katahi ka timata i ia tauwhitinga hou kua tae ke nga ripanga ka noho (tiakina he waea CREATE), engari ka noho kau, whakawhetai ki TRUNCATE (i tiakina ano e matou tana piiraa) i te whakaotinga o te tauwhitinga o mua.

1.4. PAI...WAI...

I korero ahau i te timatanga ko tetahi o nga keehi whakamahi angamaheni mo nga teepu rangitahi he maha nga momo kawemai - a ka ngenge te kaiwhakawhanake i te kape-whakapiri i te rarangi o nga mara o te teepu whaainga ki roto i te whakapuakanga o tana wa poto...

Engari ko te mangere te miihini o te ahunga whakamua! Koia te take hanga he ripanga hou "i runga i te tauira" he tino ngawari ake:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

I te mea ka taea e koe te whakaputa i te maha o nga raraunga ki tenei ripanga, kare rawa e tere te rapu. Engari he otinga tuku iho mo tenei - tohu tohu! A, ae, Ka taea hoki e te ripanga rangitahi te whai tohu.

I te mea, i te nuinga o nga wa, ko nga tohu e hiahiatia ana ka rite ki nga tohu o te ripanga whaainga, ka taea e koe te tuhi noa LIKE target_table INCLUDING INDEXES.

Mena ka hiahia koe DEFAULT-uara (hei tauira, ki te whakaki i nga uara matua matua), ka taea e koe te whakamahi LIKE target_table INCLUDING DEFAULTS. Kare noa ranei - LIKE target_table INCLUDING ALL — kape taunoa, taupū, herenga,...

Engari i konei me maarama koe mena i hanga e koe kawemai te ripanga tonu me nga tohu, katahi ka roa ake te utaina o nga raraungai te mea ka whakakiia e koe nga mea katoa i te tuatahi, katahi ka hurihia nga tohu tohu - tirohia me pehea te mahi hei tauira pg_putunga.

I te nuinga, RTFM!

2. Me pehea te tuhi?

Me kii noa ahau - whakamahia COPY-rere hei utu mo te “pako” INSERT, te whakatere i etahi wa. Ka taea e koe te tika mai i te konae i hangaia i mua.

3. Me pehea te tukatuka?

Na, kia penei ta tatou intro:

  • kei a koe he ripanga me nga raraunga kiritaki kei roto i to papaunga raraunga 1M rekoata
  • ia ra ka tukuna e te kiritaki he mea hou ki a koe "whakaahua" katoa
  • mai i nga wheako ka mohio koe i tera wa ki tera wa kaua e neke ake i te 10K nga rekoata ka whakarereketia

He tauira matarohia o taua ahuatanga ko Ko te turanga o KLADR — he maha nga wahi noho katoa, engari i ia tukunga ia wiki he iti noa nga huringa (whakaingoa ingoa o nga kainga, whakakotahi i nga tiriti, ahua o nga whare hou) ahakoa i runga i te taumata o te motu.

3.1. Katoa te tukutahi algorithm

Mo te ngawari, me kii kaore koe e hiahia ki te whakatikatika i nga raraunga - me kawe noa te ripanga ki te ahua e hiahiatia ana, ara:

  • tango nga mea katoa kua kore e noho
  • whakahou nga mea katoa o mua, me whakahou ano
  • whakauru nga mea katoa kaore ano kia puta

He aha me mahi nga mahi i roto i tenei raupapa? Na te mea ka iti ake te rahi o te tepu (mahara MVCC!).

MUMU MAI i te dst

Kao, o te akoranga ka taea e koe ma te rua noa nga mahi:

  • tango (DELETE) nga mea katoa
  • whakauru katoa mai i te ahua hou

Engari i te wa ano, he mihi ki a MVCC, Ko te rahi o te teepu ka piki rite rua! Ko te whiwhi +1M whakaahua o nga rekoata i te ripanga na te 10K whakahōutanga he tino kore...

TRUNCATE dst

Kei te mohio tetahi kaiwhakawhanake mohio ka taea te horoi i te papa katoa he iti rawa te utu:

  • marama (TRUNCATE) te tepu katoa
  • whakauru katoa mai i te ahua hou

He whai hua te tikanga, i etahi wa e tika ana, engari he raru... Ka taapirihia e matou he rekoata 1M mo te wa roa, no reira kaore e taea e matou te waiho i te teepu kia noho kau mo tenei wa katoa (penei ka tupu me te kore e takai ki te hokohoko kotahi).

Ko te tikanga:

  • kei te timata tatou tauwhitinga roa
  • TRUNCATE whakatau Whakauru Motuhake-aukati
  • ka mahi matou i te whakauru mo te wa roa, me era atu katoa i tenei wa e kore e taea SELECT

Kei te pai tetahi mea...

AHUA TAPU… WHAKAINGA HOKI… / WHAKATOHU TE TAPA…

Ko tetahi atu ko te whakakii i nga mea katoa ki tetahi ripanga hou motuhake, ka whakaingoatia noa hei whakakapi i te mea tawhito. E rua nga mea iti kino:

  • tonu ano Whakauru Motuhake, ahakoa he iti rawa te wa
  • kua tautuhia ano nga mahere patai/tatauranga mo tenei ripanga, me oma KAUPAPA
  • kua pakaru katoa nga taviri ke (FK) ki te tepu

He papaki WIP na Simon Riggs i kii kia mahia ALTER-he mahi hei whakakapi i te tinana tepu i te taumata o te konae, me te kore e pa ki nga tatauranga me te FK, engari kaore i kohia te koorama.

MUMU, WHAKAUPAPA, KUPU

Na, ka whakatauhia e matou te whiringa aukati-kore o nga mahi e toru. Tata ki te toru... Me pehea te mahi tino whai hua?

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

I roto i taua KLADR, ko nga rekoata kua whakarereketia me whakahaere ano i muri i te tukatuka - kua whakaritea, kua tohuhia nga kupu matua, me te whakaheke ki nga hanganga e hiahiatia ana. Engari me pehea koe e mohio ai - he aha te mea i whakarerekeme te kore e whakararuraru i te waehere tukutahi, me te kore rawa e pa atu?

Mena ka whai urunga tuhi to tukanga i te wa o te tukutahi, ka taea e koe te whakamahi i te keu hei kohi i nga huringa katoa mo 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;

Inaianei ka taea e taatau te tono keu i mua i te tiimata o te tukutahi (ka taea ranei ma te 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();

Na ka tangohia marietia e matou nga huringa katoa e hiahiatia ana mai i nga tepu raarangi ka whakahaere ma roto i etahi atu kaihautu.

3.3. Te kawemai i nga huinga hono

I runga ake i whakaarohia e matou nga keehi ka rite nga hanganga raraunga o te puna me te haerenga. Engari ka pehea mena he rereke te whakatakotoranga o te tukunga mai i te punaha o waho mai i te hanganga rokiroki kei roto i ta maatau putunga korero?

Me tango hei tauira mo te rokiroki o nga kaihoko me o raatau kaute, ko te waahanga "maha-ki-tetahi" matarohia:

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

Engari ko te tango mai i tetahi puna o waho ka tae mai ki a maatau i te ahua o "katoa i te kotahi":

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

Ma te mohio, ka taea te taarua nga raraunga a nga kaihoko ki tenei putanga, ko te rekoata matua ko te "pukete":

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 te tauira, ka whakauru noa i a maatau raraunga whakamatautau, engari kia mahara - COPY pai ake!

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

A tahi, e tapao ana‘e i te mau “tapao” ta ta tatou mau “meka” e parau ra. I roto i ta maatau keehi, ko nga nama e pa ana ki nga kaihoko:

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

Kia tika ai te hono i nga kaute ki nga ID kaihoko, me rapu tuatahi, me whakaputa ranei enei tohu. Me tāpiri āpure ki raro i a rātou:

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

Me whakamahi i te tikanga tukutahi ripanga i whakaahuatia i runga ake nei me te whakatikatika iti - e kore matou e whakahōu, e mukua ranei tetahi mea i roto i te ripanga whaainga, na te mea ka kawemai matou i nga kaihoko "whakapiri-anake":

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

Mau, kei roto nga mea katoa invoice_import Inaianei kua whakakiia te mara whakapā client_id, ka whakauruhia e matou te nama.

Source: will.com

Tāpiri i te kōrero