ProHoster > Blog > Administrasi > DBA: kompeten ngatur sinkronisasi sareng impor

DBA: kompeten ngatur sinkronisasi sareng impor

Pikeun ngolah kompléks set data ageung (béda prosés ETL: impor, konvérsi sareng singkronisasi sareng sumber éksternal) sering aya anu peryogi samentara "inget" jeung geura prosés gancang hal voluminous.

Tugas has sapertos ieu biasana disada sapertos kieu: "Di dieu departemén akuntansi unloaded ti bank klien pangmayaran anu terakhir nampi, anjeun kedah gancang unggah kana halaman wéb sareng ngaitkeun kana rekening anjeun.

Tapi lamun volume ieu "hal" dimimitian pikeun ngukur dina ratusan megabytes, sarta jasa kudu neruskeun gawé bareng database 24x7, loba efek samping timbul anu bakal ngaruksak hirup anjeun.
DBA: kompeten ngatur sinkronisasi sareng impor
Pikeun nungkulan aranjeunna dina PostgreSQL (na mah ngan di dinya), anjeun tiasa nganggo sababaraha optimizations nu bakal ngidinan Anjeun pikeun ngolah sagalana gancang tur kalawan konsumsi sumberdaya kirang.

1. Dimana ngirim?

Kahiji, hayu urang mutuskeun dimana urang tiasa unggah data anu urang hoyong "prosés".

1.1. Méja samentara (TABLE SAMPINGAN)

Sacara prinsip, pikeun tabel samentara PostgreSQL sami sareng anu sanés. Ku alatan éta, tahayul kawas "Sagala aya disimpen ukur dina mémori, sarta éta bisa mungkas". Tapi aya ogé sababaraha béda anu signifikan.

Anjeun sorangan "spasi ngaran" pikeun tiap sambungan kana database

Lamun dua sambungan coba sambungkeun dina waktos anu sareng CREATE TABLE x, mangka batur pasti bakal meunang kasalahan non-uniqueness objék database.

Tapi lamun duanana nyoba ngaéksekusi CREATE TEMPORARY TABLE x, lajeng duanana bakal ngalakukeun eta normal, jeung dulur bakal meunang salinan anjeun tabél. Sarta bakal aya nanaon di umum antara aranjeunna.

"Self-destruct" nalika megatkeun sambungan

Nalika sambungan ditutup, sadaya tabel samentara otomatis dihapus, jadi sacara manual DROP TABLE x teu aya gunana kecuali...

Lamun anjeun digawé ngaliwatan pgbouncer dina modeu urus, lajeng database terus yakin yén sambungan ieu masih aktip, sarta dina eta tabel samentara ieu masih aya.

Ku alatan éta, nyoba nyieun deui, ti sambungan béda jeung pgbouncer, bakal ngakibatkeun kasalahan. Tapi ieu bisa circumvented ku ngagunakeun CREATE TEMPORARY TABLE IF NOT EXISTS x.

Leres, langkung saé henteu ngalakukeun ieu waé, sabab teras anjeun tiasa "ujug-ujug" mendakan aya data anu sésana tina "boga saméméhna". Gantina, éta leuwih hadé maca manual tur tingal yén nalika nyieun méja kasebut nyaéta dimungkinkeun pikeun nambahkeun ON COMMIT DROP - nyaeta, nalika urus réngsé, tabél bakal otomatis dihapus.

Non-replikasi

Kusabab aranjeunna milik ukur sambungan husus, tabel samentara teu replicated. Tapi ieu ngaleungitkeun kabutuhan pikeun ngarékam ganda data dina tumpukan + WAL, jadi INSERT / UPDATE / DELETE kana eta leuwih gancang.

Tapi saprak hiji méja samentara masih mangrupa "ampir biasa" méja, eta teu bisa dijieun dina replica boh. Sahenteuna pikeun ayeuna, sanajan patch pakait geus beredar lila.

1.2. méja UNLOGGED

Tapi naon anu anjeun kedah laksanakeun, contona, upami anjeun ngagaduhan prosés ETL anu pajeulit anu teu tiasa dilaksanakeun dina hiji transaksi, tapi anjeun masih gaduh pgbouncer dina modeu urus? ..

Atawa aliran data téh jadi badag éta Aya henteu cukup rubakpita dina hiji sambungan tina database (baca, hiji prosés per CPU)?..

Atawa sababaraha operasi lumangsung asynchronously dina sambungan anu béda? ..

Aya ngan hiji pilihan di dieu - samentara nyieun tabel non-samentara. Pun, yeuh. nyaeta:

  • dijieun tabel "kuring sorangan" kalawan ngaran maksimum acak ku kituna teu motong jeung saha
  • sari: ngeusi aranjeunna data ti hiji sumber éksternal
  • transformasi: dirobah, dieusian dina widang linking konci
  • momotkeun: dituang data siap kana tabel target
  • ngahapus tabel "abdi".

Tur ayeuna - a laleur dina salep. Kanyataanna, sadayana nyerat dina PostgreSQL lumangsung dua kali - munggaran di WAL, lajeng kana tabél / awak indéks. Sadaya ieu dilakukeun pikeun ngadukung ACID sareng pisibilitas data anu leres antara COMMIT'kacang jeung ROLLBACK'transaksi null.

Tapi urang henteu peryogi ieu! Simkuring gaduh sakabeh proses Boh éta sagemblengna suksés atawa henteu.. Henteu janten masalah sabaraha transaksi perantara anu bakal aya - kami henteu kabetot dina "neraskeun prosés ti tengah," khususna nalika henteu jelas dimana éta.

Jang ngalampahkeun ieu, pamekar PostgreSQL, balik deui dina versi 9.1, ngenalkeun hal sapertos tabél UNLOGGED:

Kalawan indikasi ieu, tabél dijieun salaku unloggged. Data anu ditulis kana tabel anu teu diloggétkeun henteu ngalangkungan log nyerat payun (tingali Bab 29), nyababkeun tabel sapertos kitu damel langkung gancang ti biasana. Sanajan kitu, aranjeunna henteu imun kagagalan; bisi gagal server atawa shutdown darurat, hiji méja unloggged otomatis dipotong. Sajaba ti, eusi tabel unloggged teu replikasi ka server budak. Indéks naon waé anu diciptakeun dina méja anu teu kacatet sacara otomatis janten teu kacatet.

Pondokna eta bakal leuwih gancang, Tapi lamun server database "ragrag", éta bakal pikaresepeun. Tapi sabaraha sering ieu kajadian, sareng prosés ETL anjeun terang kumaha ngabenerkeun ieu leres "ti tengah" saatos "ngarevitalisasi" pangkalan data?

Upami henteu, sareng kasus di luhur sami sareng anjeun, paké UNLOGGED, tapi teu kungsi ulah ngaktipkeun atribut ieu dina tabel nyata, data ti nu dear ka anjeun.

1.3. ON COMMIT { PUPUS BARIS | DROP}

ngawangun ieu ngidinan Anjeun pikeun nangtukeun kabiasaan otomatis lamun urus geus réngsé nalika nyieun méja.

dina ON COMMIT DROP Kuring geus wrote luhur, eta dibangkitkeun DROP TABLE, tapi kalawan ON COMMIT DELETE ROWS kaayaan téh leuwih metot - dihasilkeun di dieu TRUNCATE TABLE.

Kusabab sakabéh infrastruktur pikeun nyimpen meta-deskripsi tabel samentara persis sarua jeung tabel biasa, lajeng Nyiptakeun konstanta sareng ngahapus tabel samentawis nyababkeun "bareuh" tabel sistem anu parah pg_class, pg_attribute, pg_attrdef, pg_depend,…

Ayeuna ngabayangkeun nu boga worker on sambungan langsung ka database, nu muka transaksi anyar unggal detik, nyieun, ngeusi, prosés jeung mupus hiji méja samentara ... Bakal aya kaleuwihan sampah akumulasi dina tabel sistem, jeung ieu bakal ngabalukarkeun rem tambahan pikeun tiap operasi.

Sacara umum, ulah ngalakukeun ieu! Dina hal ieu, éta leuwih éféktif CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS nyandak kaluar tina siklus urus - lajeng ku awal unggal urus anyar tabel nu geus bakal aya (simpen telepon CREATE), tapi bakal kosong, hatur nuhun ka TRUNCATE (urang ogé nyimpen telepon na) nalika ngalengkepan transaksi saméméhna.

1.4. SIGA...kaasup...

Kuring disebutkeun di awal yén salah sahiji kasus pamakéan has pikeun tabel samentara nyaéta rupa-rupa impor - sarta pamekar capé nyalin-pastes daptar widang tabel target kana deklarasi samentara na ...

Tapi puguh mesin kamajuan! Éta alesanana jieun tabel anyar "dumasar kana sampel" éta tiasa langkung saderhana:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Kusabab anjeun teras tiasa ngahasilkeun seueur data kana tabél ieu, milarian éta moal pernah gancang. Tapi aya solusi tradisional pikeun ieu - indéks! Jeung, enya, méja samentara ogé bisa mibanda indéks.

Kusabab, sering, indéks anu diperyogikeun coincide sareng indéks tabel target, anjeun ngan saukur tiasa nyerat LIKE target_table INCLUDING INDEXES.

Upami anjeun ogé peryogi DEFAULT-nilai (contona, pikeun ngeusian nilai konci primér), anjeun tiasa nganggo LIKE target_table INCLUDING DEFAULTS. Atawa ngan saukur- LIKE target_table INCLUDING ALL - salinan standar, indéks, konstrain,...

Tapi di dieu anjeun kedah ngartos yén upami anjeun nyiptakeun tabel impor geuwat kalawan indéks, lajeng data bakal nyandak deui pikeun mukati lamun mimiti ngeusian sagalana nepi, sarta ngan lajeng gulung nepi indexes - tingali kumaha ngalakukeun ieu salaku conto pg_dump.

Sacara umum RTFM!

2. Kumaha cara nyerat?

Hayu atuh ngan nyebutkeun - make eta COPY-ngalir tinimbang "pak" INSERT, akselerasi di kali. Anjeun malah tiasa langsung tina file anu tos didamel.

3. Kumaha ngolahna?

Ku kituna, hayu urang intro urang kasampak kawas kieu:

  • anjeun gaduh tabel sareng data klien disimpen dina database anjeun 1M rékaman
  • unggal dintenna klien ngirim anjeun nu anyar pinuh "gambar"
  • tina pangalaman anjeun terang yén ti jaman ka jaman euweuh leuwih ti 10K rékaman dirobah

Conto klasik tina kaayaan kitu nyaéta dasar KLADR - aya loba alamat dina total, tapi dina unggal unggah mingguan aya saeutik pisan parobahan (ganti ngaran padumukan, ngagabungkeun jalan, penampilan imah anyar) sanajan dina skala nasional.

3.1. Algoritma sinkronisasi pinuh

Pikeun kesederhanaan, hayu urang nyarios yén anjeun henteu kedah nyusun ulang data - ngan ukur bawa tabel kana bentuk anu dipikahoyong, nyaéta:

  • dipiceun sagalana nu geus euweuh
  • update sagalana nu geus aya jeung perlu diropéa
  • nyelapkeun sadayana anu teu acan kajantenan

Naha operasi kedah dilakukeun dina urutan ieu? Kusabab ieu kumaha ukuran tabel bakal tumuwuh minimal (inget MVCC!).

PUPUS TINA dst

Henteu, tangtosna anjeun tiasa kéngingkeun ngan ukur dua operasi:

  • dipiceun (DELETE) sadayana sacara umum
  • nyelapkeun sadayana tina gambar anyar

Tapi dina waktos anu sami, hatur nuhun ka MVCC, Ukuran tabel bakal ningkat persis dua kali! Kéngingkeun +1M gambar rékaman dina méja kusabab pembaruan 10K mangrupikeun redundansi pisan...

POTONGAN dst

Pangembang anu langkung berpengalaman terang yén sadaya tablet tiasa dibersihkeun rada murah:

  • jelas (TRUNCATE) sakabéh méja
  • nyelapkeun sadayana tina gambar anyar

Carana efektif, sakapeung rada lumaku, Tapi aya masalah ... Urang bakal nambahan rékaman 1M pikeun lila, sangkan teu mampuh ninggalkeun méja kosong pikeun sakabéh waktu ieu (sakumaha bakal kajadian tanpa wrapping eta dina transaksi tunggal).

Anu hartosna:

  • urang ngamimitian transaksi lila-ngajalankeun
  • TRUNCATE maksakeun Aksés Eksklusif- ngahalangan
  • urang ngalakukeun sisipan pikeun lila, jeung dulur sejenna dina waktos ieu malah teu bisa SELECT

Aya anu teu jalan...

ALTER TABLE… GANTIKAN NGARAN… / DROP TABLE…

Alternatipna nyaéta ngeusian sadayana kana méja énggal anu misah, teras ngan saukur ngaganti ngaran kana tempat anu lami. Sababaraha hal saeutik jahat:

  • masih teuing Aksés Eksklusif, sanajan nyata kirang waktos
  • sadaya rencana query/statistika pikeun tabel ieu direset, kudu ngajalankeun ANALISIS
  • sadaya konci asing rusak (FK) kana méja

Aya patch WIP ti Simon Riggs anu nyarankeun nyieun ALTER-operasi pikeun ngaganti awak tabel di tingkat file, tanpa noel statistik sarta FK, tapi teu ngumpulkeun quorum.

PUPUS, UPDATE, INSERT

Janten, urang netepkeun pilihan non-blocking tina tilu operasi. Ampir tilu ... Kumaha ngalakukeun ieu paling éféktif?

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

Dina KLADR anu sami, sadaya rékaman anu dirobih kedah dijalankeun deui ngaliwatan pamrosésan - dinormalisasi, kecap konci disorot, sareng diréduksi kana struktur anu diperyogikeun. Tapi kumaha anjeun terang - naon kahayang robahtanpa complicating kodeu sinkronisasi, ideally tanpa noél eta pisan?

Upami ngan ukur prosés anjeun gaduh aksés nyerat dina waktos sinkronisasi, maka anjeun tiasa nganggo pemicu anu bakal ngumpulkeun sadaya parobihan pikeun kami:

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

Ayeuna urang tiasa nerapkeun pemicu sateuacan ngamimitian sinkronisasi (atanapi aktipkeun via 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();

Teras we kalem nimba sadaya parobihan anu urang peryogikeun tina tabel log sareng ngajalankeunana ngaliwatan panangan tambahan.

3.3. Importing numbu susunan

Di luhur kami anggap kasus nalika struktur data sumber sareng tujuan sami. Tapi kumaha upami unggah tina sistem éksternal ngagaduhan format anu béda sareng struktur panyimpen dina database urang?

Hayu urang nyandak sabagé conto neundeun klien sareng akunna, pilihan "loba-ka-hiji" klasik:

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

Tapi undeuran tina sumber éksternal sumping ka kami dina bentuk "sadayana dina hiji":

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

Jelas, data palanggan tiasa duplikat dina versi ieu, sareng catetan utama nyaéta "akun":

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

Pikeun modél, urang ngan saukur ngalebetkeun data tés, tapi émut - COPY leuwih efisien!

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

Mimiti, hayu urang nyorot éta "potongan" anu dimaksud "fakta" urang. Dina kasus urang, invoice nujul ka konsumén:

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

Pikeun leres ngahubungkeun akun sareng KTP palanggan, mimitina urang kedah milarian atanapi ngahasilkeun identifier ieu. Hayu urang tambahkeun widang di handapeun aranjeunna:

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

Hayu urang nganggo metodeu sinkronisasi tabel ditétélakeun di luhur kalawan amandemen leutik - urang moal ngamutahirkeun atawa ngahapus nanaon dina tabel target, sabab urang ngimpor klien "tambah-hijina":

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

Sabenerna, sagalana aya dina invoice_import Ayeuna kami geus dieusian widang kontak client_id, kalawan nu urang bakal ngalebetkeun invoice.

sumber: www.habr.com

Tambahkeun komentar