DBA: sinxronizasiya və idxalı bacarıqla təşkil edin

Böyük məlumat dəstlərinin kompleks emalı üçün (müxtəlif ETL prosesləri: idxal, dönüşüm və xarici mənbə ilə sinxronizasiya) tez-tez ehtiyac var müvəqqəti olaraq "yadda saxla" və dərhal tez emal edin həcmli bir şey.

Bu cür tipik tapşırıq adətən belə səslənir: "Burada müştəri bankdan boşaldılmış mühasibat şöbəsi son alınan ödənişlər üçün siz onları tez bir zamanda vebsayta yükləməli və öz hesablarınızla əlaqələndirməlisiniz.”

Ancaq bu "nəyinsə" həcmi yüzlərlə meqabaytla ölçülməyə başlayanda və xidmət 24x7 verilənlər bazası ilə işləməyə davam etməli olduqda, həyatınızı məhv edəcək bir çox yan təsirlər yaranır.

PostgreSQL-də (və təkcə onda deyil) onlarla məşğul olmaq üçün hər şeyi daha sürətli və daha az resurs istehlakı ilə emal etməyə imkan verəcək bəzi optimallaşdırmalardan istifadə edə bilərsiniz.

1. Hara göndərilməlidir?

Əvvəlcə gəlin “emal etmək” istədiyimiz məlumatları hara yükləyə biləcəyimizə qərar verək.

1.1. Müvəqqəti cədvəllər (TEMPORARY TABLE)

Prinsipcə, PostgreSQL üçün müvəqqəti cədvəllər digərləri ilə eynidir. Buna görə də, xurafatlar kimi "Orada hər şey yalnız yaddaşda saxlanılır və bitə bilər". Ancaq bir sıra əhəmiyyətli fərqlər də var.

Verilənlər bazasına hər bir əlaqə üçün öz “ad sahəsi”

İki əlaqə eyni vaxtda qoşulmağa çalışırsa CREATE TABLE x, onda kimsə mütləq alacaq qeyri-adilik xətası verilənlər bazası obyektləri.

Ancaq hər ikisi icra etməyə çalışsa CREATE TEMPORARY TABLE x, onda hər ikisi bunu normal edəcək və hamı alacaq surətiniz masalar. Və onların arasında ortaq heç nə olmayacaq.

Bağlantı kəsilərkən "özünü məhv etmə"

Bağlantı bağlandıqda, bütün müvəqqəti cədvəllər avtomatik olaraq əl ilə silinir DROP TABLE x başqa mənası yoxdur...

Əgər işləyirsinizsə pgbouncer əməliyyat rejimində, onda verilənlər bazası bu əlaqənin hələ də aktiv olduğuna inanmağa davam edir və onda bu müvəqqəti cədvəl hələ də mövcuddur.

Buna görə də, onu başqa bir əlaqədən pgbouncerə yenidən yaratmağa çalışmaq xəta ilə nəticələnəcək. Ancaq istifadə etməklə bunun qarşısını almaq olar CREATE TEMPORARY TABLE IF NOT EXISTS x.

Düzdür, hər halda bunu etməmək daha yaxşıdır, çünki o zaman "əvvəlki sahibdən" qalan məlumatları "birdən" tapa bilərsiniz. Bunun əvəzinə təlimatı oxumaq və cədvəl yaratarkən əlavə etmək mümkün olduğunu görmək daha yaxşıdır ON COMMIT DROP - yəni əməliyyat başa çatdıqda cədvəl avtomatik olaraq silinəcək.

Replikasiya edilməməsi

Onlar yalnız müəyyən bir əlaqəyə aid olduqları üçün müvəqqəti cədvəllər təkrarlanmır. Amma bu, məlumatların ikiqat qeydinə ehtiyacı aradan qaldırır yığın + WAL-da, ona görə INSERT/UPDATE/DELETE əhəmiyyətli dərəcədə sürətlidir.

Lakin müvəqqəti cədvəl hələ də “demək olar ki, adi” cədvəl olduğundan, onu replikada da yaratmaq olmaz. Ən azı indiyə qədər, baxmayaraq ki, müvafiq yamaq uzun müddətdir.

1.2. QEYD EDİLMİŞ CƏDVƏL

Bəs, məsələn, bir əməliyyat çərçivəsində həyata keçirilə bilməyən bir növ çətin ETL prosesiniz varsa, lakin hələ də pgbouncer əməliyyat rejimində? ..

Və ya məlumat axını o qədər böyükdür ki Bir əlaqədə kifayət qədər bant genişliyi yoxdur verilənlər bazasından (oxu, hər CPU üçün bir proses)?..

Yaxud hansısa əməliyyatlar gedir asinxron müxtəlif əlaqələrdə?..

Burada yalnız bir seçim var - müvəqqəti olaraq müvəqqəti olmayan cədvəl yaradın. Pun, bəli. Yəni:

• heç kimlə kəsişməmək üçün maksimum təsadüfi adlarla “öz” cədvəlləri yaratdım
• Çıxarış: onları xarici mənbədən verilənlərlə doldurdu
• Çevirmək: çevrildi, əsas əlaqələndirici sahələr dolduruldu
• Yük: hazır məlumatları hədəf cədvəllərə tökdü
• "mənim" cədvəlləri silindi

Və indi - məlhəmdə bir milçək. Faktiki olaraq, PostgreSQL-də bütün yazılar iki dəfə baş verir - WAL-da ilk, sonra cədvəl/indeks gövdələrinə. Bütün bunlar ACID-i dəstəkləmək və məlumatların düzgün görünməsini təmin etmək üçün edilir COMMIT'qozlu və ROLLBACK'boş əməliyyatlar.

Ancaq buna ehtiyacımız yoxdur! Bizdə bütün proses var Ya tamamilə uğurlu oldu, ya da olmadı.. Nə qədər aralıq əməliyyatın olacağının əhəmiyyəti yoxdur - biz "prosesi ortadan davam etdirməkdə" maraqlı deyilik, xüsusən də harada olduğu aydın olmayanda.

Bunu etmək üçün PostgreSQL tərtibatçıları 9.1 versiyasında belə bir şey təqdim etdilər QEYD EDİLMİŞ cədvəllər:

Bu göstərici ilə cədvəl qeyd edilməmiş kimi yaradılır. Qeydə alınmamış cədvəllərə yazılan məlumatlar qabaqcadan yazma jurnalından keçmir (bax. Fəsil 29), belə cədvəllərin adi haldan daha sürətli işləyir. Bununla belə, onlar uğursuzluğa qarşı immun deyillər; server nasazlığı və ya fövqəladə söndürülməsi halında, qeyd edilməmiş cədvəl avtomatik olaraq kəsilir. Əlavə olaraq, qeyd edilməmiş cədvəlin məzmunu təkrar olunmur qul serverlərinə. Qeydə alınmamış cədvəldə yaradılmış hər hansı indekslər avtomatik olaraq qeyddən çıxarılır.

Bir sözlə, çox daha sürətli olacaq, lakin verilənlər bazası serveri "düşürsə", bu xoşagəlməz olacaq. Bəs bu nə qədər tez-tez baş verir və ETL prosesiniz verilənlər bazasını “canlandırdıqdan” sonra bunu “ortadan” necə düzgün düzəltməyi bilirmi?..

Əgər yoxsa və yuxarıdakı vəziyyət sizinkinə bənzəyirsə, istifadə edin UNLOGGEDamma heç vaxt real cədvəllərdə bu atributu aktiv etməyin, sizin üçün əziz olan məlumatlar.

1.3. RƏHMƏT EDİN { SƏRƏLƏRİ SİLİN | DAMCI}

Bu konstruksiya cədvəl yaratarkən əməliyyat başa çatdıqda avtomatik davranışı təyin etməyə imkan verir.

haqqında ON COMMIT DROP Artıq yuxarıda yazdım, yaradır DROP TABLE, lakin ilə ON COMMIT DELETE ROWS vəziyyət daha maraqlıdır - burada yaranır TRUNCATE TABLE.

Müvəqqəti cədvəlin meta-təsvirinin saxlanması üçün bütün infrastruktur adi cədvəllə tam olaraq eyni olduğundan, o zaman Müvəqqəti cədvəllərin daimi yaradılması və silinməsi sistem cədvəllərinin kəskin “şişməsinə” səbəb olur pg_class, pg_atribut, pg_attrdef, pg_depend,...

İndi təsəvvür edin ki, verilənlər bazası ilə birbaşa əlaqədə olan, hər saniyə yeni əməliyyat açan, müvəqqəti cədvəl yaradan, dolduran, emal edən və silən bir işçiniz var... Sistem cədvəllərində çoxlu zibil yığılacaq və bu, hər bir əməliyyat üçün əlavə əyləclərə səbəb olacaq.

Ümumiyyətlə, bunu etməyin! Bu vəziyyətdə daha təsirli olur CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS onu əməliyyat dövründən çıxarın - sonra hər yeni əməliyyatın əvvəlində cədvəllər artıq olur mövcud olacaq (zəngi yadda saxla CREATE), Amma boş olacaq, sayəsində TRUNCATE əvvəlki əməliyyatı tamamlayarkən (zəngini də yadda saxladıq).

1.4. KİMİ...O cümlədən...

Mən əvvəldə qeyd etdim ki, müvəqqəti cədvəllər üçün tipik istifadə hallarından biri müxtəlif idxal növləridir - və tərtibatçı yorğunluqla hədəf cədvəlinin sahələrinin siyahısını öz müvəqqəti cədvəlinin bəyannaməsinə kopyalayıb yapışdırır...

Ancaq tənbəllik tərəqqinin mühərrikidir! Buna görə də "nümunə əsasında" yeni cədvəl yaradın daha sadə ola bilər:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Daha sonra bu cədvəldə çoxlu məlumat yarada bildiyiniz üçün onun vasitəsilə axtarış heç vaxt sürətli olmayacaq. Ancaq bunun ənənəvi həlli var - indekslər! Və bəli, müvəqqəti cədvəldə də indekslər ola bilər.

Çox vaxt tələb olunan indekslər hədəf cədvəlinin indeksləri ilə üst-üstə düşdüyü üçün sadəcə yaza bilərsiniz. LIKE target_table INCLUDING INDEXES.

Sizə də lazım olsa DEFAULT-dəyərlərdən (məsələn, əsas açar dəyərləri doldurmaq üçün) istifadə edə bilərsiniz LIKE target_table INCLUDING DEFAULTS. Və ya sadəcə - LIKE target_table INCLUDING ALL — defoltları, indeksləri, məhdudiyyətləri,...

Amma burada anlamaq lazımdır ki, əgər siz yaratdınız cədvəli dərhal indekslərlə idxal edin, onda məlumatların yüklənməsi daha uzun çəkəcəkəvvəlcə hər şeyi doldursanız və yalnız sonra indeksləri yuvarlasanız - bunun nümunə olaraq necə etdiyinə baxın pg_dump.

Ümumiyyətlə, RTFM!

2. Necə yazmaq olar?

Sadəcə deyim - istifadə edin COPY- “paket” əvəzinə axın INSERT, bəzən sürətlənmə. Siz hətta əvvəlcədən yaradılmış fayldan birbaşa edə bilərsiniz.

3. Necə emal etmək olar?

Beləliklə, gəlin girişimiz belə görünsün:

• verilənlər bazanızda saxlanılan müştəri məlumatları olan bir cədvəliniz var 1 milyon qeyd
• hər gün bir müştəri sizə yenisini göndərir tam "şəkil"
• təcrübədən bilirsən ki, zaman-zaman 10K-dan çox qeyd dəyişdirilmir

Belə bir vəziyyətin klassik nümunəsidir KLADR bazası — ümumilikdə ünvanlar çoxdur, lakin hər həftəlik yükləmədə hətta ölkə miqyasında da çox az dəyişikliklər (qəsəbələrin adlarının dəyişdirilməsi, küçələrin birləşdirilməsi, yeni evlərin görünüşü) olur.

3.1. Tam sinxronizasiya alqoritmi

Sadəlik üçün deyək ki, məlumatların yenidən qurulmasına belə ehtiyac yoxdur - sadəcə cədvəli istədiyiniz formaya gətirin, yəni:

• çıxarın artıq olmayan hər şey
• yeniləmə artıq mövcud olan və yenilənməsi lazım olan hər şey
• daxil etmək hələ baş verməmiş hər şey

Nə üçün əməliyyatlar bu ardıcıllıqla aparılmalıdır? Çünki masanın ölçüsü belə minimal şəkildə artacaq (MVCC-ni xatırlayın!).

SİLİNDƏN dst

Xeyr, əlbəttə ki, yalnız iki əməliyyatla öhdəsindən gələ bilərsiniz:

• çıxarın (DELETE) ümumiyyətlə hər şey
• daxil etmək hamısı yeni görüntüdən

Lakin eyni zamanda MVCC sayəsində Cədvəlin ölçüsü düz iki dəfə artacaq! 1K yeniləmə səbəbiylə cədvəldə +10M qeydlərin şəklini əldə etmək o qədər də lazımsızdır...

TRUNCATE dst

Daha təcrübəli bir tərtibatçı bütün planşetin olduqca ucuz təmizlənə biləcəyini bilir:

• aydındır (TRUNCATE) bütün cədvəl
• daxil etmək hamısı yeni görüntüdən

Metod effektivdir, bəzən olduqca tətbiq olunur, lakin problem var... Biz uzun müddətdir ki, 1M qeydlər əlavə edəcəyik, ona görə də bütün bu müddət ərzində cədvəli boş buraxa bilmərik (bir tranzaksiyaya bükülmədən baş verəcək).

Hansı deməkdir:

• başlayırıq uzunmüddətli əməliyyat
• TRUNCATE qoyur Eksklüziv daxil olun- bloklama
• biz uzun müddət əlavə edirik və bu anda hər kəs hətta edə bilməz SELECT

Nəsə yaxşı getmir...

CƏDVƏLİ DƏYİŞTİRİN... ADINI DEĞİŞTİRİN... / CƏDVƏLİ DÜŞÜN...

Alternativ olaraq, hər şeyi ayrı bir yeni cədvələ doldurmaq və sonra sadəcə köhnənin yerinə adını dəyişməkdir. Bir neçə xoşagəlməz kiçik şey:

• hələ də Eksklüziv daxil olunəhəmiyyətli dərəcədə az vaxt olsa da
• bu cədvəl üçün bütün sorğu planları/statistika sıfırlanır, ANALYZE işlətmək lazımdır
• bütün xarici açarları xarabdır (FK) masaya

Simon Riggs-dən hazırlamağı təklif edən bir WIP yaması var idi ALTER-Statistikaya və FK-ya toxunmadan, fayl səviyyəsində cədvəlin gövdəsini dəyişdirmək üçün bir əməliyyat, lakin kvorum toplamadı.

SİLİN, YENİLƏNİN, DAXİL EDİN

Beləliklə, üç əməliyyatın bloklanmayan variantı üzərində qərarlaşırıq. Demək olar ki, üç... Bunu ən effektiv şəkildə necə etmək olar?

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

Eyni KLADR-də bütün dəyişdirilmiş qeydlər əlavə olaraq post-processing vasitəsilə həyata keçirilməlidir - normallaşdırılmalı, açar sözlər vurğulanmalı və tələb olunan strukturlara endirilməlidir. Amma hardan bilirsən - tam olaraq nə dəyişdisinxronizasiya kodunu çətinləşdirmədən, ideal olaraq heç toxunmadan?

Sinxronizasiya zamanı yalnız prosesinizin yazma imkanı varsa, o zaman bizim üçün bütün dəyişiklikləri toplayacaq tətikdən istifadə edə bilərsiniz:

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

İndi biz sinxronizasiyaya başlamazdan əvvəl tetikleyicileri tətbiq edə bilərik (və ya onları 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();

Və sonra biz sakitcə log cədvəllərindən bizə lazım olan bütün dəyişiklikləri çıxarırıq və onları əlavə işləyicilər vasitəsilə həyata keçiririk.

3.3. Əlaqədar dəstlərin idxalı

Yuxarıda mənbə və təyinatın məlumat strukturlarının eyni olduğu halları nəzərdən keçirdik. Bəs xarici sistemdən yükləmə bizim verilənlər bazamızda saxlama strukturundan fərqli formata malikdirsə?

Nümunə olaraq müştərilərin və onların hesablarının saxlanmasını, klassik “çoxdan birə” variantını götürək:

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

Ancaq xarici mənbədən yükləmə bizə "hamısı bir yerdə" şəklində gəlir:

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

Aydındır ki, müştəri məlumatları bu versiyada təkrarlana bilər və əsas qeyd "hesabdır":

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 üçün biz sadəcə test məlumatlarımızı daxil edəcəyik, lakin unutmayın - COPY daha səmərəli!

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

Əvvəlcə “faktlarımızın” istinad etdiyi “kesikləri” vurğulayaq. Bizim vəziyyətimizdə hesab-fakturalar müştərilərə aiddir:

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

Hesabları müştəri ID-ləri ilə düzgün əlaqələndirmək üçün ilk növbədə bu identifikatorları tapmalı və ya yaratmalıyıq. Onların altına sahələri əlavə edək:

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

Kiçik bir düzəlişlə yuxarıda təsvir edilən cədvəl sinxronizasiya metodundan istifadə edək - biz hədəf cədvəldə heç nə yeniləməyəcəyik və ya silməyəcəyik, çünki biz müştəriləri “yalnız əlavə etmək üçün” idxal edirik:

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

Əslində hər şey içəridədir invoice_import İndi əlaqə sahəsini doldurmuşuq client_id, onunla fakturanı daxil edəcəyik.

Mənbə: www.habr.com