ProHoster > Blog > Ma'muriyat > DBA: sinxronizatsiya va importni malakali tashkil qilish

DBA: sinxronizatsiya va importni malakali tashkil qilish

Katta ma'lumotlar to'plamlarini kompleks qayta ishlash uchun (turli ETL jarayonlari: import, konversiya va tashqi manba bilan sinxronlash) ko'pincha ehtiyoj bor vaqtincha "eslab qolish" va darhol tezda qayta ishlash hajmli narsa.

Bunday turdagi odatiy vazifa odatda shunday eshitiladi: "Mana shu yerda mijoz bankdan tushirilgan buxgalteriya bo'limi Oxirgi qabul qilingan to'lovlar uchun siz ularni tezda veb-saytga yuklashingiz va ularni hisoblaringizga bog'lashingiz kerak"

Ammo bu "narsa" ning hajmi yuzlab megabaytlarda o'lchay boshlaganda va xizmat 24x7 ma'lumotlar bazasi bilan ishlashni davom ettirishi kerak bo'lsa, hayotingizni buzadigan ko'plab nojo'ya ta'sirlar paydo bo'ladi.
DBA: sinxronizatsiya va importni malakali tashkil qilish
PostgreSQL-da (va nafaqat unda) ular bilan ishlash uchun siz hamma narsani tezroq va kamroq resurslarni sarflashga imkon beradigan ba'zi optimallashlardan foydalanishingiz mumkin.

1. Qayerga jo'natish kerak?

Birinchidan, keling, “qayta ishlamoqchi bo‘lgan” ma’lumotlarni qaerga yuklashimiz mumkinligini hal qilaylik.

1.1. Vaqtinchalik jadvallar (VAQTINCHI TABLO)

Printsipial jihatdan PostgreSQL uchun vaqtinchalik jadvallar boshqa har qanday jadvallar bilan bir xil. Shuning uchun, xurofotlar yoqadi "U erda hamma narsa faqat xotirada saqlanadi va u tugashi mumkin". Ammo bir qator muhim farqlar ham mavjud.

Ma'lumotlar bazasiga har bir ulanish uchun o'zingizning "nomlar maydoni"

Agar ikkita ulanish bir vaqtning o'zida ulanishga harakat qilsa CREATE TABLE x, keyin kimdir albatta oladi noyob bo'lmagan xato ma'lumotlar bazasi ob'ektlari.

Ammo ikkalasi ham bajarishga harakat qilsalar CREATE TEMPORARY TABLE x, keyin ikkalasi ham buni odatdagidek bajaradi va hamma oladi sizning nusxangiz jadvallar. Va ular orasida umumiy narsa bo'lmaydi.

O'chirishda "o'z-o'zini yo'q qilish"

Ulanish yopilganda, barcha vaqtinchalik jadvallar avtomatik ravishda o'chiriladi, shuning uchun qo'lda DROP TABLE x boshqa hech qanday ma'no yo'q ...

Agar siz ishlayotgan bo'lsangiz pgbouncer tranzaksiya rejimida, keyin ma'lumotlar bazasi ushbu ulanish hali ham faol ekanligiga ishonishda davom etadi va unda bu vaqtinchalik jadval hali ham mavjud.

Shuning uchun, uni boshqa ulanishdan pgbouncerga qayta yaratishga urinish xatoga olib keladi. Ammo foydalanish orqali buni chetlab o'tish mumkin CREATE TEMPORARY TABLE IF NOT EXISTS x.

To'g'ri, baribir buni qilmaslik yaxshiroqdir, chunki u erda siz "oldingi egasi" dan qolgan ma'lumotlarni "to'satdan" topishingiz mumkin. Buning o'rniga, qo'llanmani o'qib chiqish va jadval yaratishda uni qo'shish mumkinligini ko'rish yaxshiroqdir ON COMMIT DROP - ya'ni tranzaksiya tugagach, jadval avtomatik ravishda o'chiriladi.

Replikatsiya qilmaslik

Ular faqat ma'lum bir ulanishga tegishli bo'lganligi sababli, vaqtinchalik jadvallar takrorlanmaydi. Lekin bu ma'lumotlarni ikki marta yozib olish zaruratini yo'q qiladi to'p + WAL ichida, shuning uchun unga INSERT/UPDATE/DELETE juda tezroq bo'ladi.

Ammo vaqtinchalik jadval hali ham "deyarli oddiy" jadval bo'lgani uchun uni replikatsiyada ham yaratib bo'lmaydi. Hech bo'lmaganda hozircha, mos keladigan yamoq uzoq vaqtdan beri aylanib yurgan bo'lsa-da.

1.2. BO'LGAN JADVAL

Misol uchun, agar sizda bitta tranzaksiya doirasida amalga oshirib bo'lmaydigan og'ir ETL jarayoni bo'lsa, nima qilish kerak, lekin sizda hali ham mavjud. pgbouncer tranzaksiya rejimida? ..

Yoki ma'lumotlar oqimi shunchalik kattaki Bitta ulanishda tarmoqli kengligi yetarli emas ma'lumotlar bazasidan (o'qing, har bir CPU uchun bitta jarayon)?..

Yoki ba'zi operatsiyalar davom etmoqda asinxron turli aloqalarda?..

Bu erda faqat bitta variant bor - vaqtinchalik vaqtinchalik bo'lmagan jadval yaratish. Pun, ha. Ya'ni:

  • hech kim bilan kesishmaslik uchun maksimal tasodifiy nomlar bilan "o'zimning" jadvallarini yaratdi
  • Extract: ularni tashqi manba ma'lumotlari bilan to'ldirdi
  • O'tkazish: aylantirildi, asosiy bog'lovchi maydonlar to'ldiriladi
  • yuk: tayyor ma'lumotlarni maqsadli jadvallarga quydi
  • "mening" jadvallarim o'chirildi

Va endi - malhamda chivin. Aslida, PostgreSQL-dagi barcha yozishlar ikki marta sodir bo'ladi - birinchi WAL, keyin jadval/indeks jismlariga. Bularning barchasi ACIDni qo'llab-quvvatlash va o'rtasidagi ma'lumotlarning ko'rinishini to'g'rilash uchun amalga oshiriladi COMMIT'yong'oq va ROLLBACK'null tranzaksiyalar.

Ammo bu bizga kerak emas! Bizda butun jarayon bor Yoki u butunlay muvaffaqiyatli bo'ldi yoki yo'q edi.. Qancha oraliq tranzaktsiyalar bo'lishi muhim emas - biz "jarayonni o'rtadan davom ettirishdan" manfaatdor emasmiz, ayniqsa uning qaerdaligi aniq bo'lmaganda.

Buni amalga oshirish uchun PostgreSQL ishlab chiquvchilari 9.1 versiyasida shunga o'xshash narsani taqdim etdilar UNLOGGED jadvallar:

Ushbu ko'rsatkich bilan jadval o'chirilgan sifatida yaratiladi. Ochilmagan jadvallarga yozilgan ma'lumotlar oldindan yozish jurnalidan o'tmaydi (29-bobga qarang), bunday jadvallar odatdagidan ko'ra tezroq ishlaydi. Biroq, ular muvaffaqiyatsizlikka qarshi immunitetga ega emaslar; server ishlamay qolsa yoki favqulodda o'chirilgan bo'lsa, o'chirilgan jadval avtomatik ravishda kesiladi. Bundan tashqari, o'chirilgan jadvalning mazmuni takrorlanmaydi qul serverlariga. Ochilmagan jadvalda yaratilgan barcha indekslar avtomatik ravishda o'chiriladi.

Qisqasi ancha tez bo'ladi, lekin agar ma'lumotlar bazasi serveri "yiqilib tushsa", bu yoqimsiz bo'ladi. Ammo bu qanchalik tez-tez sodir bo'ladi va sizning ETL jarayoni ma'lumotlar bazasini "jonlantirgandan" keyin buni "o'rtadan" qanday qilib to'g'ri tuzatishni biladimi?..

Agar yo'q bo'lsa va yuqoridagi holat siznikiga o'xshash bo'lsa, foydalaning UNLOGGEDlekin hech qachon haqiqiy jadvallarda ushbu atributni yoqmang, siz uchun qadrli bo'lgan ma'lumotlar.

1.3. QO'YISHDA { QATRLARNI O'CHIRISH | DOPLASH}

Ushbu konstruksiya jadval yaratishda tranzaksiya tugallanganda avtomatik harakatni belgilash imkonini beradi.

haqida ON COMMIT DROP Men allaqachon yuqorida yozganman, u yaratadi DROP TABLE, lekin bilan ON COMMIT DELETE ROWS Vaziyat qiziqroq - bu erda yaratilgan TRUNCATE TABLE.

Vaqtinchalik jadvalning meta-tavsifini saqlash uchun barcha infratuzilma oddiy jadvalniki bilan bir xil bo'lganligi sababli, u holda Vaqtinchalik jadvallarni doimiy ravishda yaratish va o'chirish tizim jadvallarining qattiq "shishishi" ga olib keladi pg_class, pg_attribute, pg_attrdef, pg_depend,…

Endi tasavvur qiling-a, sizda ma'lumotlar bazasiga to'g'ridan-to'g'ri ulanish bo'yicha ishchi bor, u har soniyada yangi tranzaksiya ochadi, vaqtinchalik jadvalni yaratadi, to'ldiradi, qayta ishlaydi va o'chiradi... Tizim jadvallarida ortiqcha chiqindilar to'planadi va bu har bir operatsiya uchun qo'shimcha tormozlarga olib keladi.

Umuman olganda, buni qilmang! Bunday holda, u ancha samarali bo'ladi CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS uni tranzaksiya siklidan chiqarib tashlang - keyin har bir yangi tranzaksiya boshida jadvallar allaqachon mavjud mavjud bo'ladi (qo'ng'iroqni saqlang CREATE), lekin bo'sh bo'ladi, Rahmat TRUNCATE (biz uning qo'ng'irog'ini ham saqlab qo'ydik) oldingi tranzaksiyani yakunlashda.

1.4. LAYK...Jumladan...

Men boshida eslatib o'tdimki, vaqtinchalik jadvallardan foydalanishning odatiy holatlaridan biri bu har xil turdagi importlardir - va ishlab chiquvchi charchagan holda maqsadli jadval maydonlari ro'yxatini o'zining vaqtinchalik jadvallari deklaratsiyasiga nusxa ko'chiradi ...

Ammo dangasalik taraqqiyotning dvigatelidir! Shunung uchun "namuna asosida" yangi jadval yaratish ancha sodda bo'lishi mumkin:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Keyinchalik siz ushbu jadvalda juda ko'p ma'lumotlarni yaratishingiz mumkinligi sababli, uni qidirish hech qachon tez bo'lmaydi. Ammo buning an'anaviy yechimi bor - indekslar! Va ha, vaqtinchalik jadvalda indekslar ham bo'lishi mumkin.

Ko'pincha kerakli indekslar maqsadli jadval indekslariga to'g'ri kelganligi sababli, siz shunchaki yozishingiz mumkin. LIKE target_table INCLUDING INDEXES.

Agar sizga ham kerak bo'lsa DEFAULT-qiymatlar (masalan, asosiy kalit qiymatlarini to'ldirish uchun), siz foydalanishingiz mumkin LIKE target_table INCLUDING DEFAULTS. Yoki oddiygina - LIKE target_table INCLUDING ALL — standart sozlamalar, indekslar, cheklovlar va...

Lekin bu erda siz yaratgan bo'lsangiz, buni tushunishingiz kerak jadvalni indekslar bilan darhol import qiling, keyin ma'lumotlarni yuklash ko'proq vaqt oladiAgar siz avval hamma narsani to'ldirsangiz va shundan keyingina indekslarni yig'ib qo'ysangiz - buni misol sifatida ko'ring. pg_dump.

Umuman, RTFM!

2. Qanday yoziladi?

Aytmoqchiman - undan foydalaning COPY- "paket" o'rniga oqim INSERT, ba'zida tezlashuv. Siz hatto to'g'ridan-to'g'ri oldindan yaratilgan fayldan ham mumkin.

3. Qayta ishlash qanday?

Shunday qilib, keling, intromiz shunday ko'rinishga ega bo'lsin:

  • Sizning ma'lumotlar bazasida saqlangan mijoz ma'lumotlari bilan jadval mavjud 1 million yozuvlar
  • har kuni mijoz sizga yangisini yuboradi to'liq "tasvir"
  • Vaqti-vaqti bilan buni tajribadan bilasiz 10K dan ortiq yozuvlar o'zgartirilmaydi

Bunday vaziyatning klassik misoli KLADR bazasi — jami manzillar juda ko‘p, lekin har haftalik yuklashda hatto milliy miqyosda ham juda kam o‘zgarishlar (aholi punktlari nomini o‘zgartirish, ko‘chalarni birlashtirish, yangi uylar ko‘rinishi) bo‘ladi.

3.1. To'liq sinxronizatsiya algoritmi

Oddiylik uchun aytaylik, siz hatto ma'lumotlarni qayta qurishingiz shart emas - shunchaki jadvalni kerakli shaklga keltiring, ya'ni:

  • olib tashlang endi mavjud bo'lmagan hamma narsa
  • yangilash allaqachon mavjud bo'lgan va yangilanishi kerak bo'lgan hamma narsa
  • kiritish uchun hali sodir bo'lmagan hamma narsa

Nima uchun operatsiyalar shu tartibda amalga oshirilishi kerak? Chunki stol hajmi shunday minimal darajada oshadi (MVCC esda tuting!).

Dst.DAN OʻCHIRISH

Yo'q, albatta, siz ikkita operatsiyani bajarishingiz mumkin:

  • olib tashlang (DELETE) umuman hamma narsa
  • kiritish uchun hammasi yangi rasmdan

Shu bilan birga, MVCC tufayli, Jadvalning o'lchami ikki baravar ko'payadi! Jadvaldagi 1K yangilanish tufayli +10M yozuvlar tasvirini olish shunchalik ortiqcha...

TRUNCATE dst

Tajribali ishlab chiquvchi butun planshetni juda arzonga tozalash mumkinligini biladi:

  • aniq (TRUNCATE) butun jadval
  • kiritish uchun hammasi yangi rasmdan

Usul samarali, ba'zan juda qo'llaniladi, lekin muammo bor... Biz 1M yozuvlarni uzoq vaqt davomida qo'shamiz, shuning uchun biz bu vaqt davomida jadvalni bo'sh qoldirishga qodir emasmiz (xuddi bitta tranzaksiyaga o'ralmagan holda sodir bo'ladi).

Bu degani:

  • boshlaymiz uzoq muddatli tranzaksiya
  • TRUNCATE yuklaydi Eksklyuzivga kirish- blokirovka qilish
  • biz kiritishni uzoq vaqt davomida qilamiz va bu vaqtda boshqalar mumkin emas SELECT

Nimadir yaxshi emas...

JADVALNI O‘ZGARTIRISH… NOMINI O‘ZGARTIRISH… / JADVALNI TO‘SHISH…

Muqobil variant - hamma narsani alohida yangi jadvalga to'ldirish va keyin uni eskisi o'rniga qayta nomlash. Bir nechta yoqimsiz kichik narsalar:

  • hali ham Eksklyuzivga kirish, garchi sezilarli darajada kamroq vaqt
  • ushbu jadval uchun barcha so'rov rejalari/statistik ma'lumotlar qayta o'rnatiladi, ANALYZE ni ishga tushirish kerak
  • barcha chet el kalitlari buzilgan (FK) stolga

Simon Riggsning WIP yamog'i bor edi, u yaratishni taklif qildi ALTER-jadval tanasini statistik ma'lumotlarga va FKga tegmasdan fayl darajasida almashtirish operatsiyasi, lekin kvorum yig'ilmadi.

OʻCHIRISH, YANGILASH, QOʻSHISH

Shunday qilib, biz uchta operatsiyani blokirovka qilmaslik variantiga qaror qilamiz. Deyarli uchta... Buni qanday qilib eng samarali qilish mumkin?

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

Xuddi shu KLADRda barcha o'zgartirilgan yozuvlar qo'shimcha ravishda post-processing orqali bajarilishi kerak - normallashtirilgan, kalit so'zlarni ajratib ko'rsatish va kerakli tuzilmalarga qisqartirish. Ammo siz qayerdan bilasiz - aniq nima o'zgardisinxronizatsiya kodini murakkablashtirmasdan, ideal holda unga tegmasdanmi?

Sinxronizatsiya vaqtida faqat sizning jarayoningiz yozish huquqiga ega bo'lsa, biz uchun barcha o'zgarishlarni to'playdigan triggerdan foydalanishingiz mumkin:

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

Endi biz sinxronlashni boshlashdan oldin triggerlarni qo'llashimiz mumkin (yoki ularni faollashtirish uchun 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();

Va keyin biz xotirjamlik bilan barcha kerakli o'zgarishlarni jurnal jadvallaridan chiqaramiz va ularni qo'shimcha ishlov beruvchilar orqali boshqaramiz.

3.3. Bog'langan to'plamlarni import qilish

Yuqorida biz manba va maqsadning ma'lumotlar tuzilmalari bir xil bo'lgan holatlarni ko'rib chiqdik. Ammo tashqi tizimdan yuklash bizning ma'lumotlar bazasidagi saqlash tuzilmasidan boshqa formatga ega bo'lsa-chi?

Mijozlarni va ularning hisoblarini saqlashni misol qilib olaylik, klassik "ko'pdan birga" varianti:

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

Ammo tashqi manbadan yuklab olish bizga "hamma birida" shaklida keladi:

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

Shubhasiz, ushbu versiyada mijozlar ma'lumotlarini takrorlash mumkin va asosiy yozuv "hisob" dir:

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

Model uchun biz shunchaki sinov ma'lumotlarimizni kiritamiz, lekin esda tuting - COPY samaraliroq!

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

Birinchidan, keling, "faktlarimiz" tegishli bo'lgan "kesishlarni" ajratib ko'rsatamiz. Bizning holatlarimizda hisob-fakturalar mijozlarga tegishli:

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

Hisoblarni mijoz identifikatorlari bilan to‘g‘ri bog‘lash uchun avvalo ushbu identifikatorlarni aniqlashimiz yoki yaratishimiz kerak. Keling, ularning ostiga maydonlarni qo'shamiz:

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

Keling, yuqorida tavsiflangan jadvalni sinxronlashtirish usulini kichik tuzatish bilan ishlataylik - biz maqsadli jadvaldagi hech narsani yangilamaymiz yoki o'chirmaymiz, chunki biz mijozlarni "faqat qo'shish uchun" import qilamiz:

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

Aslida, hamma narsa ichkarida invoice_import Endi bizda aloqa maydoni to'ldirilgan client_id, uning yordamida biz hisob-fakturani kiritamiz.

Manba: www.habr.com

a Izoh qo'shish