DBA: hoʻonohonoho pono i ka hoʻonohonoho ʻana a me ka lawe ʻana mai
No ka hana paʻakikī o nā pūʻulu ʻikepili nui (ʻokoʻa Nā hana ETL: lawe mai, hoʻololi a me ka hoʻonohonoho ʻana me kahi kumu o waho) pinepine ka pono "hoʻomanaʻo" no ka manawa pōkole a hana koke kekahi mea nui.
ʻO kahi hana maʻamau o kēia ʻano ke kani pinepine ʻia e like me kēia: "Maʻaneʻi ʻoihana helu helu i hoʻokuʻu ʻia mai ka waihona mea kūʻai aku nā uku i loaʻa hope loa, pono ʻoe e hoʻouka koke iā lākou i ka pūnaewele a hoʻopili iā lākou i kāu mau moʻokāki"
Akā ke hoʻomaka ka nui o kēia "mea" e ana i nā haneli megabytes, a pono e hoʻomau ka lawelawe me ka waihona 24x7, nui nā hopena ʻaoʻao e hōʻino i kou ola.
No ka hana ʻana iā lākou ma PostgreSQL (a ʻaʻole wale i loko), hiki iā ʻoe ke hoʻohana i kekahi mau optimizations e hiki ai iā ʻoe ke hana wikiwiki i nā mea āpau a me ka liʻiliʻi o ka hoʻohana waiwai.
1. Ma hea e moku ai?
ʻO ka mea mua, e hoʻoholo kākou i kahi e hiki ai iā mākou ke hoʻouka i ka ʻikepili a mākou e makemake ai e "hana."
1.1. Nā pākaukau manawa (TEMPORARY TABLE)
Ma ke kumu, no ka PostgreSQL nā papa ʻaina manawa like me nā mea ʻē aʻe. No laila, like nā superstitions "Ua mālama ʻia nā mea a pau ma ka hoʻomanaʻo, a hiki ke hoʻopau ʻia". Akā, aia kekahi mau ʻokoʻa koʻikoʻi.
ʻO kāu "namespace" no kēlā me kēia pili i ka waihona
Inā ho'āʻo nā pilina ʻelua e hoʻohui i ka manawa like CREATE TABLE x, a laila e loaʻa i kekahi hewa kūʻokoʻa ʻole nā mea waihona.
Akā inā ho'āʻo lāua e hoʻokō CREATE TEMPORARY TABLE x, a laila e hana maʻamau nā mea ʻelua, a loaʻa nā mea a pau kou kope nā papaʻaina. A ʻaʻohe mea like ma waena o lākou.
"Hoʻopau iā ia iho" i ka wā e wehe ai
Ke pani ʻia ka pilina, holoi ʻia nā papa ʻaina āpau, no laila me ka lima DROP TABLE x ʻaʻohe kumu koe wale nō ...
Inā ʻoe e hana nei pgbouncer ma ke ʻano kālepa, a laila hoʻomau ka manaʻoʻiʻo i ka hoʻomau ʻana o kēia pilina, a i loko o ia mea e noho mau nei kēia papa ʻaina.
No laila, ke ho'āʻo nei e hana hou, mai kahi pili ʻē aʻe i pgbouncer, e hopena i kahi hewa. Akā hiki ke pale ʻia kēia ma ka hoʻohana ʻana CREATE TEMPORARY TABLE IF NOT EXISTS x.
ʻOiaʻiʻo, ʻoi aku ka maikaʻi o ka hana ʻole ʻana i kēia, no ka mea hiki iā ʻoe ke "hōʻea" i laila i ka ʻikepili i koe mai ka "mea nona mua". Akā, ʻoi aku ka maikaʻi o ka heluhelu ʻana i ka manual a ʻike i ka wā e hana ai i kahi papaʻaina hiki ke hoʻohui ON COMMIT DROP - ʻo ia hoʻi, i ka pau ʻana o ke kālepa, e holoi ʻia ka papaʻaina.
Hoʻopili ʻole
No ka mea pili wale lākou i kahi pilina kikoʻī, ʻaʻole i hana hou ʻia nā papa ʻaina. Akā hoʻopau kēia i ka pono o ka hoʻopaʻa paʻa ʻana o ka ʻikepili ma ka puʻu + WAL, no laila ʻoi aku ka wikiwiki o INSERT/UPDATE/DELETE i loko.
Akā, no ka mea he papa ʻaina "aneane maʻamau" ka papa ʻaina, ʻaʻole hiki ke hana ʻia ma kahi kope. ʻO ka liʻiliʻi loa i kēia manawa, ʻoiai ua hoʻolaha ʻia ka patch pili no ka manawa lōʻihi.
1.2. PAPA OLE
Akā he aha kāu e hana ai, no ka laʻana, inā loaʻa iā ʻoe kekahi ʻano kaʻina hana ETL paʻakikī ʻaʻole hiki ke hoʻokō ʻia i loko o hoʻokahi kālepa, akā aia nō ʻoe. pgbouncer ma ke ʻano kālepa? ..
A i ʻole ka nui o ka ʻikepili ʻAʻole lawa ka bandwidth ma kahi pilina mai kahi waihona (heluhelu, hoʻokahi kaʻina no ka CPU)?..
A i ʻole ke hele nei kekahi mau hana asynchronously i nā pilina like ʻole?..
Hoʻokahi wale nō koho ma aneʻi - hana i ka papaʻaina no ka manawa ʻole. Pun, ʻae. ʻo ia:
Ua hana ʻo ia i nā papa "koʻu ponoʻī" me nā inoa maʻamau i ʻole e hui pū me kekahi
extract: hoʻopiha iā lākou me ka ʻikepili mai kahi kumu waho
Hoʻololi: hoohuliia, hoopihaia ma na kahua pili ki
haawe: ninini ʻikepili mākaukau i loko o nā papa kuhikuhi
holoi ʻia nā papa "koʻu".
A i kēia manawa - he lele i ka ʻaila. I ka ʻoiaʻiʻo, kākau nā mea a pau ma PostgreSQL i ʻelua manawa - mua ma WAL, a laila i loko o nā kino papa/index. Hana ʻia kēia mau mea e kākoʻo i ka ACID a me ka ʻike pololei ʻana i ka ʻike ma waena COMMIT' nutty a ROLLBACK'mau hana null.
Akā ʻaʻole pono mākou i kēia! Loaʻa iā mākou ka hana holoʻokoʻa A i ʻole ua kūleʻa loa a ʻaʻole paha.. ʻAʻole pili i ka nui o nā kālepa waena - ʻaʻole mākou makemake i ka "hoʻomau i ke kaʻina hana mai ka waena," ʻoiai inā ʻaʻole maopopo kahi i loaʻa ai.
No ka hana ʻana i kēia, ua hoʻopuka nā mea hoʻomohala PostgreSQL, hoʻi i ka mana 9.1, i kahi mea e like me UNLOGGED pākaukau:
Me kēia hōʻike, ua hana ʻia ka papa ʻaina e like me ka unloggged. ʻAʻole hele ka ʻikepili i kākau ʻia i nā papa i hoʻopaʻa ʻole ʻia ma ka log kākau mua (e ʻike i ka Mokuna 29), e hoʻomaka ana ia mau papa. ʻoi aku ka wikiwiki o ka hana ma mua o ka maʻamau. Eia naʻe, ʻaʻole palekana lākou i ka hāʻule ʻole; inā pilikia ka server a i ʻole ka hoʻopaʻa ʻana i ka pilikia, he papa ʻaina ʻole ʻoki ʻakomi. Eia kekahi, nā mea i loko o ka papa i hoʻopaʻa ʻole ʻia ʻaʻole i hana hou ʻia e hookauwa aku. ʻO kēlā me kēia papa kuhikuhi i hana ʻia ma ka papa ʻaina ʻole i hoʻopaʻa ʻia e lilo i mea ʻole.
I ka pōkole e oi aku ka wikiwiki, akā inā "hāʻule" ke kikowaena waihona, e ʻoluʻolu ʻole ia. Akā pehea ka manawa e hana ai kēia, a ʻike anei kāu kaʻina ETL pehea e hoʻoponopono pololei ai i kēia "mai waena" ma hope o ka "hoʻoulu hou" i ka waihona?..
Inā ʻaʻole, a ua like ka hihia ma luna me kāu, e hoʻohana UNLOGGEDaka, aole loa mai hoʻohana i kēia ʻano ma nā papa ʻaina maoli, ka ʻikepili mai kahi mea aloha iā ʻoe.
1.3. ON COMMIT { HELE LALANI | HAULE}
Hāʻawi kēia kūkulu iā ʻoe e kuhikuhi i ka hana maʻalahi ke hoʻopau ʻia kahi kālepa i ka wā e hana ai i kahi papaʻaina.
maluna o ON COMMIT DROP Ua kākau mua wau ma luna, hana DROP TABLE, aka me ON COMMIT DELETE ROWS ʻoi aku ka hoihoi o ke kūlana - hana ʻia ma ʻaneʻi TRUNCATE TABLE.
No ka mea, ʻo ka ʻōnaehana holoʻokoʻa no ka mālama ʻana i ka meta-description o kahi papaʻaina manawa like like me ka papa maʻamau, a laila ʻO ka hana mau ʻana a me ka holoi ʻana i nā papa manawa pōkole e alakaʻi i ka "huhū" koʻikoʻi o nā papa ʻōnaehana pg_class, pg_attribute, pg_attrdef, pg_depend,…
I kēia manawa, e noʻonoʻo ʻoe he limahana kāu ma kahi pili pololei i ka waihona, e wehe ana i kahi kūʻai hou i kēlā me kēia kekona, hana, hoʻopiha, kaʻina a holoi i kahi papaʻaina manawa ... E nui ka ʻōpala i hōʻiliʻili ʻia i nā papa ʻōnaehana, a e hana kēia i nā kaʻa keu no kēlā me kēia hana.
Ma keʻano laulā, mai hana i kēia! I kēia hihia, ʻoi aku ka maikaʻi CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS e lawe i waho o ke kaʻina hana - a laila ma ka hoʻomaka ʻana o kēlā me kēia hana hou ua pau nā papa e noho ana (E mālama i kahi kelepona CREATE), akā e nele, mahalo iā TRUNCATE (ua mālama pū mākou i kāna kelepona) i ka wā e hoʻopau ai i ka hana mua.
1.4. LIKE...KO...
Ua ʻōlelo wau i ka hoʻomaka ʻana ʻo kekahi o nā hihia maʻamau no nā papa ʻaina he ʻano like ʻole o ka lawe ʻana mai - a ʻo ka mea hoʻomohala me ka luhi kope-paʻi i ka papa inoa o nā kahua o ka papa kuhikuhi i loko o ka haʻi ʻana o kāna manawa pōkole ...
Akā ʻo ka palaualelo ke ʻenekini o ka holomua! ʻo ia ke kumu hana i papaʻaina hou "ma muli o ka laʻana" hiki ke maʻalahi loa:
CREATE TEMPORARY TABLE import_table(
LIKE target_table
);
No ka mea hiki iā ʻoe ke hana i nā ʻikepili he nui i loko o kēia pākaukau, ʻaʻole wikiwiki ka huli ʻana ma ia. Akā aia kahi hopena kuʻuna i kēia - indexes! A, ʻae, Hiki ke loaʻa i ka papa ʻaina manawa nā indexes.
No ka mea, ʻo ka manawa pinepine, ʻokoʻa nā kuhikuhi i makemake ʻia me nā kuhikuhi o ka papa kuhikuhi, hiki iā ʻoe ke kākau wale LIKE target_table INCLUDING INDEXES.
Inā pono ʻoe DEFAULT-values (no ka laʻana, e hoʻopiha i nā kumu waiwai nui), hiki iā ʻoe ke hoʻohana LIKE target_table INCLUDING DEFAULTS. A i ʻole - LIKE target_table INCLUDING ALL - kope i nā mea paʻamau, nā kuhikuhi, nā palena,...
Akā ma ʻaneʻi pono ʻoe e hoʻomaopopo inā hana ʻoe hoʻokomo koke i ka papa kuhikuhi me nā kuhikuhi, a laila e lōʻihi ka hoʻouka ʻana o ka ʻikepilima mua o ka hoʻopiha mua ʻana i nā mea āpau, a laila e ʻōwili i nā indexes - e nānā i ke ʻano o kēia hana ma ke ʻano he laʻana pg_puʻu.
E ʻōlelo wau - e hoʻohana COPY- kahe ma kahi o "pack" INSERT, ka wikiwiki i kekahi manawa. Hiki iā ʻoe ke pololei mai kahi faila i hana mua ʻia.
3. Pehea e hana ai?
No laila, e hoʻokuʻu i kā mākou intro e like me kēia:
loaʻa iā ʻoe kahi papa me ka ʻikepili o ka mea kūʻai aku i mālama ʻia i kāu waihona 1M mooolelo
i kēlā me kēia lā hoʻouna ka mea kūʻai aku iā ʻoe i kahi mea hou piha "kiʻi"
mai ka ʻike ʻike ʻoe i kēlā me kēia manawa ʻaʻole i ʻoi aku ma mua o 10K mau moʻolelo i hoʻololi ʻia
ʻO kahi hiʻohiʻona maʻamau o ia kūlana KLADR kumu — He nui nā helu wahi, akā i kēlā me kēia pule e hoʻouka ʻia he liʻiliʻi loa nā hoʻololi (ka inoa inoa o nā wahi noho, ka hui ʻana i nā alanui, ke ʻano o nā hale hou) ʻoiai ma ka pae aupuni.
3.1. ʻO ka algorithm hoʻonohonoho piha
No ka maʻalahi, e ʻōlelo mākou ʻaʻole pono ʻoe e hoʻoponopono hou i ka ʻikepili - e lawe wale i ka papa i ke ʻano makemake, ʻo ia hoʻi:
wehe na mea a pau i noho hou ole
hōʻano hou nā mea a pau i loaʻa a pono e hoʻonui
hookomo nā mea a pau i hiki ʻole mai
No ke aha e hana ʻia ai nā hana ma kēia ʻano? No ka mea, ʻo kēia ke ʻano e ulu liʻiliʻi ai ka nui o ka pākaukau (hoʻomanaʻo iā MVCC!).
HELE MAI dst
ʻAʻole, ʻoiaʻiʻo hiki iā ʻoe ke loaʻa me nā hana ʻelua wale nō:
wehe (DELETE) nā mea a pau ma ka laulā
hookomo nā mea a pau mai ke kiʻi hou
Akā i ka manawa like, mahalo iā MVCC, E hoʻonui ʻia ka nui o ka papaʻaina i ʻelua manawa! ʻO ka loaʻa ʻana o +1M kiʻi o nā moʻolelo ma ka pākaukau ma muli o ka hoʻonui ʻana he 10K he mea hoʻonui loa...
OKIIA dst
Ua ʻike ka mea hoʻomohala ʻoi aku ka maikaʻi e hiki ke hoʻomaʻemaʻe ʻia ka papa holoʻokoʻa.
maopopo (TRUNCATE) ka papaʻaina holoʻokoʻa
hookomo nā mea a pau mai ke kiʻi hou
He pono ke ala, i kekahi manawa pili loa, akā aia kahi pilikia... E hoʻohui mākou i nā moʻolelo 1M no ka manawa lōʻihi, no laila ʻaʻole hiki iā mākou ke waiho i ka papaʻaina me ka hakahaka no kēia manawa a pau (e like me ka hana ʻana me ka ʻole o ke kāwili ʻana i hoʻokahi kālepa).
ʻO ia hoʻi:
ke hoʻomaka nei mākou hana lōʻihi
TRUNCATE hoʻokau Loaʻa Kūʻokoʻa-ākeʻa
hana mākou i ka hoʻokomo no ka manawa lōʻihi, a me nā mea ʻē aʻe i kēia manawa hiki ole SELECT
ʻAʻole maikaʻi kekahi mea...
HOʻOLI PĀLĀLĀ… HOʻONO hou…
ʻO kahi koho ʻē aʻe ʻo ka hoʻopiha ʻana i nā mea āpau i kahi papaʻaina hou ʻokoʻa, a laila hoʻololi wale i ka inoa ma kahi o ka mea kahiko. ʻElua mau mea liʻiliʻi liʻiliʻi:
eia no hoi Loaʻa Kūʻokoʻa, ʻoiai ʻoi aku ka liʻiliʻi o ka manawa
ua hoʻonohonoho hou ʻia nā hoʻolālā nīnau/helu helu no kēia pākaukau, pono e holo ANALYZE
ua haki nā kī haole a pau (FK) i ka papaʻaina
Aia kahi ʻāpana WIP mai Simon Riggs i manaʻo e hana ALTER-he hana e hoʻololi i ke kino papa ma ka pae waihona, me ka hoʻopā ʻole i nā helu helu a me FK, akā ʻaʻole i hōʻiliʻili i ka quorum.
HELE, HOOLAHA, HOokomo
No laila, hoʻoholo mākou i ke koho pale ʻole o nā hana ʻekolu. Aneane ekolu... Pehea e hana pono ai?
-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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. Hoʻokomo ma hope o ka hana ʻana
Ma ka KLADR like, pono e hoʻopili ʻia nā moʻolelo i hoʻololi ʻia ma hope o ka hoʻoponopono ʻana - maʻamau, hōʻike ʻia nā huaʻōlelo, a hoʻemi ʻia i nā hale i makemake ʻia. Akā pehea ʻoe e ʻike ai - ka mea i hoololi ponome ka hoʻopili ʻole ʻana i ke code synchronization, kūpono me ka hoʻopā ʻole ʻana iā ia?
Inā loaʻa i kāu kaʻina hana ke komo kākau i ka manawa o ka hoʻonohonoho ʻana, a laila hiki iā ʻoe ke hoʻohana i kahi kumu e hōʻiliʻili ai i nā loli āpau no mākou:
-- целевые таблицы
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;
I kēia manawa hiki iā mākou ke hoʻohana i nā trigger ma mua o ka hoʻomaka ʻana i ka synchronization (a i ʻole hiki iā lākou ma o 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();
A laila lawe mālie mākou i nā hoʻololi a pau a mākou e pono ai mai nā papa lāʻau a holo iā lākou ma o nā mea lawelawe hou.
3.3. Ke lawe mai nei i nā pūʻulu pili
Ma luna aʻe ua noʻonoʻo mākou i nā hihia inā like nā ʻano ʻikepili o ke kumu a me kahi e hele ai. Akā, pehea inā he ʻano ʻokoʻa ka hoʻouka ʻana mai kahi ʻōnaehana waho mai ka hale waihona i kā mākou waihona?
E lawe kākou i laʻana i ka waiho ʻana o nā mea kūʻai aku a me kā lākou mau moʻokāki, ke koho "nui-a-hoʻokahi" maʻamau:
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)
);
Akā ʻo ka hoʻoiho ʻana mai kahi kumu waho e hele mai iā mākou ma ke ʻano o "nā mea āpau":
ʻO ka mea mua, e hōʻike i kēlā mau "ʻoki" i kuhikuhi ʻia e kā mākou "ʻoiaʻiʻo". I kā mākou hihia, pili nā invoice i nā mea kūʻai:
CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
client_inn inn
, client_name "name"
FROM
invoice_import;
No ka hoʻopili pololei ʻana i nā moʻokāki me nā ID mea kūʻai aku, pono mākou e ʻimi mua a hana paha i kēia mau ʻike. E hoʻohui i nā kahua ma lalo o lākou:
ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;
E hoʻohana i ke ʻano hana hoʻonohonoho papaʻaina i hōʻike ʻia ma luna me kahi hoʻololi liʻiliʻi - ʻaʻole mākou e hōʻano hou a holoi paha i kekahi mea ma ka papa kuhikuhi, no ka mea, lawe mākou i nā mea kūʻai aku "hoʻohui wale":
-- проставляем в таблице импорта 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 kaʻoiaʻiʻo, aia nā mea a pau invoice_import I kēia manawa ua hoʻopiha ʻia ka kahua pili client_id, a mākou e hoʻokomo ai i ka invoice.