Airson giullachd iom-fhillte de sheataichean dàta mòra (eadar-dhealaichte : in-mhalairt, tionndadh agus sioncronadh le stòr bhon taobh a-muigh) gu tric bidh feum air rè ùine “cuimhnich” agus pròiseas luath sa bhad rudeigin voluminous.
Mar as trice bidh obair àbhaisteach den t-seòrsa seo a’ fuaimeachadh rudeigin mar seo: “Dìreach an seo na pàighidhean mu dheireadh a fhuaireadh, feumaidh tu an luchdachadh suas gu sgiobalta chun làrach-lìn agus an ceangal ris na cunntasan agad."
Ach nuair a thòisicheas meud an “rudeigin” seo a’ tomhas ann an ceudan megabytes, agus feumaidh an t-seirbheis cumail ag obair leis an stòr-dàta 24 × 7, bidh mòran de bhuaidhean ag èirigh a sgriosas do bheatha.
Gus dèiligeadh riutha ann am PostgreSQL (agus chan ann a-mhàin ann), faodaidh tu cuid de optimizations a chleachdadh a leigeas leat a h-uile càil a phròiseasadh nas luaithe agus le nas lugha de chaitheamh ghoireasan.
1. Càite an cuir thu air falbh?
An toiseach, leig dhuinn co-dhùnadh càite an urrainn dhuinn an dàta a tha sinn airson a “phròiseasadh” a luchdachadh suas.
1.1. Clàran sealach (TEMPORARY TABLE)
Ann am prionnsapal, airson clàran sealach PostgreSQL tha an aon rud ri clàran eile. Mar sin, saobh-chràbhadh mar “Tha a h-uile dad air a stòradh mar chuimhneachan a-mhàin, agus faodaidh e crìochnachadh”. Ach tha grunn eadar-dhealachaidhean cudromach ann cuideachd.
An “spacespace” agad fhèin airson gach ceangal ris an stòr-dàta
Ma tha dà cheangal a 'feuchainn ri ceangal aig an aon àm CREATE TABLE x, an uairsin gheibh cuideigin gu cinnteach mearachd neo-shònraichte stuthan stòr-dàta.
Ach ma tha an dithis a 'feuchainn ri cur an gnìomh CREATE TEMPORARY TABLE x, an uairsin nì an dithis e gu h-àbhaisteach, agus gheibh a h-uile duine do leth-bhreac bùird. Agus cha bhi ni sam bith cumanta eatorra.
"Fèin-sgrios" nuair a thèid a dhì-cheangal
Nuair a bhios an ceangal dùinte, thèid a h-uile clàr sealach a dhubhadh às gu fèin-ghluasadach, mar sin le làimh DROP TABLE x chan eil adhbhar ann ach ...
Ma tha thu ag obair troimhe pgbouncer ann am modh malairt, an uairsin tha an stòr-dàta fhathast a 'creidsinn gu bheil an ceangal seo fhathast gnìomhach, agus tha an clàr sealach seo ann fhathast.
Mar sin, ma dh'fheuchas tu ri a chruthachadh a-rithist, bho cheangal eadar-dhealaichte gu pgbouncer, thig mearachd. Ach faodar seo a sheachnadh le bhith a 'cleachdadh CREATE TEMPORARY TABLE IF NOT EXISTS x.
Fìor, tha e nas fheàrr gun a bhith a 'dèanamh seo co-dhiù, oir an uairsin faodaidh tu "gu h-obann" a lorg an sin an dàta a tha air fhàgail bhon "sealbhadair roimhe". An àite sin, tha e tòrr nas fheàrr an leabhar-làimhe a leughadh agus faicinn nuair a chruthaicheas tu clàr gu bheil e comasach cuir ris ON COMMIT DROP - is e sin, nuair a bhios an gnothach deiseil, thèid an clàr a dhubhadh às gu fèin-ghluasadach.
Neo-ath-riochdachadh
Leis nach buin iad ach do cheangal sònraichte, chan eil clàran sealach air an ath-aithris. Ach tha seo a’ cur às don fheum air dàta a chlàradh dà uair ann an tiùrr + WAL, mar sin INSERT/UPDATE/DLETE in it tha e tòrr nas luaithe.
Ach leis gu bheil clàr sealach fhathast na bhòrd “cha mhòr àbhaisteach”, chan urrainnear a chruthachadh air mac-samhail nas motha. Co-dhiù airson a-nis, ged a tha am pìos co-fhreagarrach air a bhith a’ cuairteachadh airson ùine mhòr.
1.2. AN T-SLIGHE CHUMHACHD
Ach dè a bu chòir dhut a dhèanamh, mar eisimpleir, ma tha seòrsa de phròiseas ETL trom agad nach gabh a chuir an gnìomh taobh a-staigh aon ghnothach, ach tha thu fhathast pgbouncer ann am modh malairt? ..
No tha an sruth dàta cho mòr sin Chan eil leud-bann gu leòr air aon cheangal bho stòr-dàta (leugh, aon phròiseas airson gach CPU)? ..
No tha cuid de ghnìomhachdan a’ dol air adhart gu neo-chinnteach ann an diofar cheanglaichean? ..
Chan eil ach aon roghainn an seo - cruthaich clàr neo-shealach airson ùine. Seadh, tha. S e sin:
- chruthaich mi clàran “mo chuid fhìn” le ainmean air thuaiream aig a’ char as àirde gus nach tèid iad tarsainn air duine sam bith
- Earrann: lìon iad le dàta bho thùs bhon taobh a-muigh
- Transform: air a thionndadh, air a lìonadh ann am prìomh raointean ceangail
- Luchdaich: dhòirt e dàta deiseil a-steach do chlàran targaid
- cuir às do chlàran “mo”
Agus a-nis - cuileag anns an acainn. Leis an fhìrinn innse, bidh a h-uile sgrìobhadh ann am PostgreSQL a’ tachairt dà uair - , an uairsin a-steach don bhòrd / buidhnean clàr-amais. Tha seo uile air a dhèanamh gus taic a thoirt do ACID agus faicsinneachd dàta ceart eadar COMMIT' nutty agus ROLLBACK'ghnothaichean null.
Ach chan eil feum againn air seo! Tha am pròiseas air fad againn An dàrna cuid bha e gu tur soirbheachail no cha robh.. Chan eil e gu diofar cia mheud gnothach eadar-mheadhanach a bhios ann - chan eil ùidh againn ann a bhith “a’ leantainn air adhart leis a ’phròiseas bhon mheadhan,” gu sònraichte nuair nach eil e soilleir càite an robh e.
Gus seo a dhèanamh, thug luchd-leasachaidh PostgreSQL, air ais ann an dreach 9.1, a-steach a leithid de rud :
Leis a 'chomharra seo, tha an clàr air a chruthachadh mar nach eil clàraichte. Chan eil dàta a chaidh a sgrìobhadh gu bùird nach deach a chlàradh a’ dol tron log sgrìobhaidh air thoiseach (faic Caibideil 29), a’ toirt air na clàran sin obrachadh fada nas luaithe na an àbhaist. Ach, chan eil iad dìonach bho fàilligeadh; ma dh’ fhàillig an t-seirbheisiche no gun tèid a dhùnadh sìos gu èiginn, clàr gun chlàradh air a ghearradh gu fèin-ghluasadach. A bharrachd air an sin, tha susbaint a’ chlàr gun chlàradh nach eil air ath-aithris do sheirbhisich thràillean. Bidh clàran-amais sam bith a thèid a chruthachadh air clàr neo-chlàraichte gu fèin-ghluasadach gun chlàradh.
Ann an ùine ghoirid, bidh e tòrr nas luaithe, ach ma thuiteas frithealaiche an stòr-dàta, bidh e mì-thlachdmhor. Ach dè cho tric a thachras seo, agus a bheil fios aig do phròiseas ETL mar a chuireas tu seo ceart “bhon mheadhan” às deidh “ath-bheothachadh” an stòr-dàta?..
Mura h-eil, agus tha a’ chùis gu h-àrd coltach ris an fhear agadsa, cleachd UNLOGGEDach a-riamh na cuir an comas am feart seo air clàran fìor, tha an dàta às a bheil gaol dhut.
1.3. AIR GEALLADH { Sguab às ROWS | DROP}
Leigidh an togail seo leat giùlan fèin-ghluasadach a shònrachadh nuair a bhios malairt deiseil nuair a chruthaicheas tu clàr.
air a ' ON COMMIT DROP Sgrìobh mi mu thràth gu h-àrd, tha e a’ gineadh DROP TABLE, ach le ON COMMIT DELETE ROWS tha an suidheachadh nas inntinniche - tha e air a ghineadh an seo TRUNCATE TABLE.
Leis gu bheil am bun-structar gu lèir airson a bhith a’ stòradh meta-thuairisgeul clàr sealach dìreach mar a tha clàr àbhaisteach, an uairsin Bidh cruthachadh agus cuir às gu cunbhalach de chlàran sealach a’ leantainn gu “sèid” mòr de chlàran siostam pg_class, pg_attribute, pg_attrdef, pg_depend,…
A-nis smaoinich gu bheil neach-obrach agad air ceangal dìreach ris an stòr-dàta, a bhios a’ fosgladh malairt ùr gach diog, a’ cruthachadh, a’ lìonadh, a’ pròiseasadh agus a’ cuir às do bhòrd sealach... Bidh cus sgudal air a chruinneachadh ann am bùird an t-siostaim, agus bidh seo ag adhbhrachadh breicichean a bharrachd airson gach gnìomh.
San fharsaingeachd, na dèan seo! Anns a 'chùis seo, tha e mòran nas èifeachdaiche CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS thoir a-mach às a’ chearcall malairt e - an uairsin aig toiseach gach malairt ùr tha na clàran mu thràth bidh ann (sàbhail gairm CREATE), ach bidh falamh, taing dha TRUNCATE (shàbhail sinn a ghairm cuideachd) nuair a chuir sinn crìoch air a’ ghnothach roimhe.
1.4. LIKE...A' gabhail a-steach...
Thug mi iomradh aig an toiseach gur e aon de na cùisean cleachdaidh àbhaisteach airson bùird sealach diofar sheòrsan in-mhalairt - agus bidh an leasaiche gu sgìth a ’dèanamh leth-bhreac de liosta raointean a’ chlàr targaid a-steach don fhoillseachadh sealach aige.
Ach is e leisg inneal an adhartais! Is ann air sgàth sin cruthaich clàr ùr “stèidhichte air sampall” faodaidh e a bhith tòrr nas sìmplidh:
CREATE TEMPORARY TABLE import_table(
LIKE target_table
);Leis gun urrainn dhut an uairsin tòrr dàta a ghineadh a-steach don chlàr seo, cha bhith sgrùdadh troimhe cho luath. Ach tha fuasgladh traidiseanta ann airson seo - clàran-amais! Agus, tha, faodaidh clàr-amais a bhith aig clàr sealach cuideachd.
Leis gu bheil, gu tric, na clàran-amais riatanach a’ co-thaobhadh ri clàran-amais a’ chlàr targaid, faodaidh tu dìreach sgrìobhadh LIKE target_table INCLUDING INDEXES.
Ma tha feum agad air cuideachd DEFAULT-values (mar eisimpleir, gus na prìomh luachan a lìonadh a-steach), faodaidh tu a chleachdadh LIKE target_table INCLUDING DEFAULTS. No gu sìmplidh - LIKE target_table INCLUDING ALL - lethbhric de bhun-stèidh, clàran-amais, cuingeadan, ...
Ach an seo feumaidh tu sin a thuigsinn ma chruthaich thu clàr in-mhalairt sa bhad le clàran-amais, agus an uairsin bheir an dàta nas fhaide ri luchdachadhna ma lìonas tu a h-uile càil an toiseach, agus dìreach an uairsin cuir suas na clàran-amais - thoir sùil air mar a nì e seo mar eisimpleir .
Ann an ùine ghoirid, !
2. Ciamar a sgrìobhadh?
Leig leam dìreach a ràdh - cleachd e -flow an àite “pasgan” INSERT, . Faodaidh tu eadhon gu dìreach bho fhaidhle a chaidh a ghineadh ro-làimh.
3. Ciamar a phròiseasadh?
Mar sin, leig leis an ro-ràdh againn rudeigin mar seo a choimhead:
- tha clàr agad le dàta teachdaiche air a stòradh san stòr-dàta agad 1M clàran
- gach latha bidh neach-dèiligidh a’ cur fear ùr thugad làn "image"
- bho eòlas tha fios agad air sin bho àm gu àm chan eil barrachd air clàran 10K air an atharrachadh
Is e eisimpleir clasaigeach de leithid de shuidheachadh - tha tòrr sheòlaidhean ann uile gu lèir, ach anns gach luchdachadh suas seachdaineil tha glè bheag de dh’ atharrachaidhean (ath-ainmeachadh bhailtean, cothlamadh sràidean, coltas thaighean ùra) eadhon aig ìre nàiseanta.
3.1. Algorithm sioncronaidh slàn
Airson sìmplidh, canaidh sinn nach fheum thu eadhon an dàta ath-structaradh - dìreach thoir am bòrd a-steach don fhoirm a tha thu ag iarraidh, is e sin:
- falbh a h-uile rud nach eil ann tuilleadh
- ùrachadh a h-uile dad a bha ann mu thràth agus a dh’ fheumar ùrachadh
- cuir a-steach a h-uile rud nach do thachair fhathast
Carson a bu chòir na h-obraichean a dhèanamh san òrdugh seo? Leis gur ann mar seo a dh’ fhàsas meud a’ bhùird cho beag ().
Sguab às dst
Chan e, gu dearbh gheibh thu seachad le dìreach dà obair-lannsa:
- falbh (
DELETE) a h-uile dad san fharsaingeachd - cuir a-steach uile bhon dealbh ùr
Ach aig an aon àm, le taing do MVCC, Meudaichidh meud a 'bhùird dìreach dà uair! Tha a bhith a’ faighinn ìomhaighean +1M de chlàran anns a’ chlàr mar thoradh air ùrachadh 10K cho mòr gun fheum...
TRUNCATE dst
Tha fios aig leasaiche nas eòlaiche gum faodar an clàr gu lèir a ghlanadh gu math saor:
- soilleir (
TRUNCATE) am bòrd gu lèir - cuir a-steach uile bhon dealbh ùr
Tha an dòigh-obrach èifeachdach, , ach tha duilgheadas ann... Bidh sinn a’ cur clàran 1M ris airson ùine mhòr, agus mar sin chan urrainn dhuinn pàigheadh airson am bòrd fhàgail falamh fad na h-ùine seo (mar a thachras gun a bhith ga phasgadh ann an aon ghnothach).
A tha a' ciallachadh:
- tha sinn a' tòiseachadh malairt fad-ùine
TRUNCATEa' sparradh Cothrom air leth- bacadh- bidh sinn a 'dèanamh an cuir a-steach airson ùine mhòr, agus a h-uile duine eile aig an àm seo chan urrainn eadhon
SELECT
Tha rudeigin nach eil a' dol gu math...
ALTER TABLE… ATH-ainmich… / drop TABLE…
Is e roghainn eile a h-uile càil a lìonadh a-steach do bhòrd ùr air leth, agus an uairsin dìreach ath-ainmich e an àite an t-seann fhear. Rud no dhà beag dona:
- fhathast cuideachd Cothrom air leth, ged a tha mòran nas lugha de ùine ann
- tha a h-uile plana ceist / staitistig airson a’ chlàr seo air ath-shuidheachadh,
- tha na h-iuchraichean cèin uile briste (FK) chun bhòrd
Bha bad WIP ann bho Simon Riggs a mhol dèanamh ALTER- gnìomh gus am buidheann bùird a chuir an àite ìre an fhaidhle, gun a bhith a’ beantainn ri staitistig agus FK, ach cha do chruinnich an cuòram.
DELETE, ÙRACHADH, INSERT
Mar sin, socraichidh sinn air an roghainn neo-bacadh de thrì obrachaidhean. Cha mhòr trì... Ciamar a nì thu seo san dòigh as èifeachdaiche?
-- все делаем в рамках транзакции, чтобы никто не видел "промежуточных" состояний
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. Cuir a-steach post-giullachd
Anns an aon KLADR, feumar a h-uile clàr atharraichte a ruith a bharrachd tro iar-ghiollachd - àbhaisteach, prìomh fhaclan air an comharrachadh, agus air an lughdachadh gu na structaran riatanach. Ach ciamar a tha fios agad - dè dìreach a dh'atharraichgun a bhith a’ dèanamh iom-fhillte air a’ chòd sioncronaidh, mas fheàrr gun a bhith a’ beantainn ris idir?
Mura h-eil ach inntrigeadh sgrìobhaidh aig a’ phròiseas agad aig àm sioncronaidh, faodaidh tu inneal-brosnachaidh a chleachdadh a chruinnicheas na h-atharrachaidhean gu lèir dhuinn:
-- целевые таблицы
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;
A-nis is urrainn dhuinn innealan-brosnachaidh a chuir an sàs mus tòisich sinn air sioncronadh (no an comasachadh tro 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();
Agus an uairsin bidh sinn gu socair a 'toirt a-mach na h-atharrachaidhean gu lèir a dh' fheumas sinn bho na clàran log agus gan ruith tro luchd-làimhseachaidh a bharrachd.
3.3. A' toirt a-steach seataichean ceangailte
Gu h-àrd bheachdaich sinn air cùisean nuair a tha structaran dàta an stòr agus an ceann-uidhe mar an ceudna. Ach dè ma tha cruth eadar-dhealaichte aig an luchdachadh suas bho shiostam a-muigh bhon structar stòraidh san stòr-dàta againn?
Bheir sinn mar eisimpleir stòradh luchd-dèiligidh agus na cunntasan aca, an roghainn clasaigeach “mòran-ri-aon”:
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)
);Ach thig an luchdachadh sìos bho stòr a-muigh thugainn ann an cruth “uile ann an aon”:
CREATE TEMPORARY TABLE invoice_import(
client_inn
varchar
, client_name
varchar
, invoice_number
varchar
, invoice_dt
date
, invoice_sum
numeric(32,2)
);Gu dearbh, faodar dàta teachdaiche a dhùblachadh san dreach seo, agus is e am prìomh chlàr “cunntas”:
0123456789;Вася;A-01;2020-03-16;1000.00
9876543210;Петя;A-02;2020-03-16;666.00
0123456789;Вася;B-03;2020-03-16;9999.00
Airson a’ mhodail, dìreach cuir a-steach an dàta deuchainn againn, ach cuimhnich - COPY nas èifeachdaiche!
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);An toiseach, leig dhuinn cuideam a chuir air na “gearraidhean” sin air a bheil na “fìrinnean” againn a’ toirt iomradh. Anns a 'chùis againn, tha fàirdealan a' toirt iomradh air luchd-ceannach:
CREATE TEMPORARY TABLE client_import AS
SELECT DISTINCT ON(client_inn)
-- можно просто SELECT DISTINCT, если данные заведомо непротиворечивы
client_inn inn
, client_name "name"
FROM
invoice_import;Gus cunntasan a cheangal gu ceart ri IDan teachdaiche, feumaidh sinn an toiseach na aithnichearan sin a lorg no a ghineadh. Nach cuir sinn raointean fon deidhinn:
ALTER TABLE invoice_import ADD COLUMN client_id integer;
ALTER TABLE client_import ADD COLUMN client_id integer;Cleachdamaid an dòigh sioncronaidh bùird a tha air a mhìneachadh gu h-àrd le atharrachadh beag - cha bhith sinn ag ùrachadh no a’ sguabadh às dad sa chlàr targaid, oir bidh sinn a’ toirt a-steach teachdaichean “pàipear-a-mhàin”:
-- проставляем в таблице импорта 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; -- прикладной ключ
Gu fìrinneach, tha a h-uile dad a-staigh invoice_import A-nis tha an raon conaltraidh againn air a lìonadh a-steach client_id, leis an cuir sinn a-steach am fàirdeal.
Source: www.habr.com