DBA: senkronizasyonları ve içe aktarmaları yetkin bir şekilde organize edin

Büyük veri setlerinin karmaşık işlenmesi için (farklı ETL süreçleri: içe aktarmalar, dönüştürmeler ve harici bir kaynakla senkronizasyon) sıklıkla ihtiyaç duyulur geçici olarak “hatırlayın” ve hemen hızlı bir şekilde işleyin hacimli bir şey.

Bu türden tipik bir görev genellikle şuna benzer: "Tam burada muhasebe departmanı müşteri bankasından boşaltıldı Son alınan ödemeleri hızlı bir şekilde web sitesine yüklemeniz ve hesaplarınıza bağlamanız gerekiyor.”

Ancak bu "bir şeyin" hacmi yüzlerce megabaytla ölçülmeye başladığında ve hizmetin veritabanıyla 24/7 çalışmaya devam etmesi gerektiğinde, hayatınızı mahvedecek birçok yan etki ortaya çıkar.

PostgreSQL'de bunlarla başa çıkmak için (ve yalnızca içinde değil), her şeyi daha hızlı ve daha az kaynak tüketimiyle işlemenize olanak sağlayacak bazı optimizasyonları kullanabilirsiniz.

1. Nereye gönderilir?

Öncelikle “işlemek” istediğimiz verileri nereye yükleyebileceğimize karar verelim.

1.1. Geçici tablolar (GEÇİCİ TABLO)

Prensip olarak PostgreSQL için geçici tablolar diğerleriyle aynıdır. Bu nedenle batıl inançlar “Orada olan her şey yalnızca hafızada kayıtlıdır ve bitebilir”. Ancak aynı zamanda birkaç önemli farklılık da var.

Veritabanına her bağlantı için kendi “ad alanınız”

İki bağlantı aynı anda bağlanmaya çalışırsa CREATE TABLE x, o zaman birisi kesinlikle alacak benzersiz olmama hatası veritabanı nesneleri.

Ama eğer ikisi de yürütmeyi denerse CREATE TEMPORARY TABLE x, o zaman her ikisi de bunu normal şekilde yapacak ve herkes senin kopyan tablolar. Ve aralarında hiçbir ortak nokta olmayacak.

Bağlantıyı keserken "kendi kendini imha etme"

Bağlantı kapatıldığında tüm geçici tablolar otomatik olarak silinir, dolayısıyla manuel olarak DROP TABLE x dışında hiçbir anlamı yok...

Eğer çalışıyorsan işlem modunda pgbouncer, daha sonra veritabanı bu bağlantının hala aktif olduğuna ve içinde bu geçici tablonun hala mevcut olduğuna inanmaya devam eder.

Bu nedenle, pgbouncer'a farklı bir bağlantıdan yeniden oluşturmaya çalışmak hatayla sonuçlanacaktır. Ancak bu durum kullanılarak aşılabilir. CREATE TEMPORARY TABLE IF NOT EXISTS x.

Doğru, bunu yine de yapmamak daha iyidir, çünkü o zaman "aniden" "önceki sahibinden" kalan verileri orada bulabilirsiniz. Bunun yerine, kılavuzu okumak ve bir tablo oluştururken eklemelerin mümkün olduğunu görmek çok daha iyidir. ON COMMIT DROP - yani işlem tamamlandığında tablo otomatik olarak silinecektir.

Çoğaltılmama

Yalnızca belirli bir bağlantıya ait oldukları için geçici tablolar çoğaltılmaz. Ancak bu, verilerin çifte kaydedilmesi ihtiyacını ortadan kaldırır yığın + WAL'de, bu nedenle içine INSERT/UPDATE/DELETE çok daha hızlıdır.

Ancak geçici tablo hâlâ "neredeyse sıradan" bir tablo olduğundan kopya üzerinde de oluşturulamaz. En azından şimdilik, ilgili yama uzun süredir dolaşımda olmasına rağmen.

1.2. GÜNLÜK OLMAYAN TABLO

Ancak örneğin tek bir işlemde uygulanamayan bir tür hantal ETL süreciniz varsa ancak yine de elinizde bir işlem varsa ne yapmalısınız? işlem modunda pgbouncer? ..

Veya veri akışı o kadar büyük ki Bir bağlantıda yeterli bant genişliği yok bir veritabanından (okuma, CPU başına bir işlem)? ..

Veya bazı operasyonlar sürüyor asenkron olarak farklı bağlantılarda mı?..

Burada tek bir seçenek var - geçici olarak geçici olmayan bir tablo oluşturun. Kelime oyunu, evet. Yani:

• kimseyle kesişmeyecek şekilde maksimum rastgele adlarla "kendi" tablolarımı oluşturdum
• Çıkarmak: onları harici bir kaynaktan gelen verilerle doldurdu
• Dönüştürmek: dönüştürüldü, anahtar bağlantı alanları dolduruldu
• Yük: hazır verileri hedef tablolara döktü
• "benim" tablolarım silindi

Ve şimdi - merhemdeki bir sinek. Aslında, PostgreSQL'deki tüm yazma işlemleri iki kez gerçekleşir - WAL'da ilk, ardından tablo/dizin gövdelerine. Bütün bunlar ACID'yi desteklemek ve aralarındaki veri görünürlüğünü düzeltmek için yapılır. COMMIT'çılgın ve ROLLBACK'boş işlemler.

Ama buna ihtiyacımız yok! Tüm süreç elimizde Ya tamamen başarılıydı ya da değildi.. Kaç tane ara işlem olacağı önemli değil - özellikle nerede olduğu belli olmadığında "süreci ortadan devam ettirmekle" ilgilenmiyoruz.

Bunu yapmak için PostgreSQL geliştiricileri 9.1 sürümünde şöyle bir şey tanıttılar: GÜNLÜK OLMAYAN tablolar:

Bu gösterge ile tablo unloged olarak oluşturulur. Günlüğü olmayan tablolara yazılan veriler yazma öncesi günlüğünden geçmez (bkz. Bölüm 29), bu tür tabloların normalden çok daha hızlı çalışın. Ancak başarısızlıktan muaf değiller; Sunucu arızası veya acil kapatma durumunda, günlüğe kaydedilmemiş bir tablo otomatik olarak kesildi. Ek olarak, günlüğe kaydedilmemiş tablonun içeriği kopyalanmadı köle sunucularına. Günlüğe kaydedilmemiş bir tabloda oluşturulan tüm dizinlerin oturumları otomatik olarak kaldırılır.

Kısacası çok daha hızlı olacak, ancak veritabanı sunucusu "düşerse" bu rahatsız edici olacaktır. Ancak bu ne sıklıkta oluyor ve ETL süreciniz veritabanını "yeniden canlandırdıktan" sonra bunu "ortadan" doğru şekilde nasıl düzelteceğini biliyor mu?..

Değilse ve yukarıdaki durum sizinkine benzerse, şunu kullanın: UNLOGGEDama asla bu özelliği gerçek tablolarda etkinleştirmeyin, sizin için değerli olan veriler.

1.3. KABUL ETMEDE { SATIRLARI SİL | DÜŞÜRMEK}

Bu yapı, bir tablo oluştururken bir işlem tamamlandığında otomatik davranışı belirlemenize olanak tanır.

Hakkında ON COMMIT DROP Yukarıda zaten yazdım, yaratıyor DROP TABLE, fakat ON COMMIT DELETE ROWS durum daha ilginç - burada oluşturuldu TRUNCATE TABLE.

Geçici bir tablonun meta açıklamasını depolamak için gereken altyapının tamamı normal bir tablonunkiyle tamamen aynı olduğundan, o zaman Geçici tabloların sürekli olarak oluşturulması ve silinmesi, sistem tablolarının ciddi şekilde "şişmesine" neden olur pg_class, pg_attribute, pg_attrdef, pg_depend,…

Şimdi, veritabanına doğrudan bağlanan, her saniye yeni bir işlem açan, geçici tablo oluşturan, dolduran, işleyen ve silen bir çalışanınız olduğunu hayal edin... Sistem tablolarında aşırı miktarda çöp birikecek ve bu her işlemde ekstra fren yapılmasına neden olacaktır.

Genel olarak bunu yapmayın! Bu durumda çok daha etkili oluyor CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS bunu işlem döngüsünden çıkarın - daha sonra her yeni işlemin başlangıcında tablolar zaten var olacak (bir çağrıyı kaydet CREATE) fakat boş olacak, sayesinde TRUNCATE (çağrısını da kaydettik) önceki işlemi tamamlarken.

1.4. GİBİ... DAHİL...

Başlangıçta geçici tablolar için tipik kullanım durumlarından birinin çeşitli türde içe aktarmalar olduğunu ve geliştiricinin hedef tablonun alanlarının listesini kendi geçici tablosunun bildirimine yorgun bir şekilde kopyalayıp yapıştırdığını belirtmiştim.

Ancak tembellik ilerlemenin motorudur! Bu yüzden “örneğe dayalı” yeni bir tablo oluşturun çok daha basit olabilir:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Daha sonra bu tabloya çok fazla veri oluşturabildiğiniz için, içinde arama yapmak hiçbir zaman hızlı olmayacaktır. Ancak bunun geleneksel bir çözümü var: indeksler! Ve evet, geçici bir tablonun dizinleri de olabilir.

Çoğu zaman gerekli dizinler hedef tablonun dizinleriyle çakıştığı için şunu yazabilirsiniz: LIKE target_table INCLUDING INDEXES.

Ayrıca ihtiyacınız varsa DEFAULT-değerler (örneğin, birincil anahtar değerlerini doldurmak için) kullanabilirsiniz LIKE target_table INCLUDING DEFAULTS. Ya da sadece - LIKE target_table INCLUDING ALL — varsayılanları, dizinleri, kısıtlamaları kopyalar...

Ama burada şunu anlamalısınız ki eğer yarattıysanız tabloyu hemen dizinlerle içe aktarırsanız verilerin yüklenmesi daha uzun sürerönce her şeyi doldurup daha sonra indeksleri toparlamak yerine - örnek olarak bunu nasıl yaptığına bakın pg_dump.

Genel olarak, RTFM!

2. Nasıl yazılır?

Sadece şunu söyleyeyim - kullanın COPY- "paketlemek" yerine akış INSERT, zaman zaman hızlanma. Doğrudan önceden oluşturulmuş bir dosyadan bile yapabilirsiniz.

3. Nasıl işlenir?

O halde intromuzun şöyle görünmesine izin verelim:

• veritabanınızda saklanan müşteri verilerinin bulunduğu bir tablonuz var 1 milyon kayıt
• her gün bir müşteri sana yeni bir tane gönderir tam "görüntü"
• deneyimlerden biliyorsun ki zaman zaman 10'den fazla kayıt değiştirilmez

Böyle bir duruma klasik bir örnek KLADR tabanı — toplamda çok sayıda adres var, ancak her haftalık yüklemede ulusal ölçekte bile çok az değişiklik (yerleşim yerlerinin yeniden adlandırılması, sokakların birleştirilmesi, yeni evlerin görünümü) oluyor.

3.1. Tam senkronizasyon algoritması

Basitleştirmek adına, verileri yeniden yapılandırmanıza bile gerek olmadığını varsayalım; tabloyu istediğiniz forma getirmeniz yeterli, yani:

• kaldırmak artık var olmayan her şey
• güncelleştirme zaten var olan ve güncellenmesi gereken her şey
• eklemek henüz gerçekleşmemiş her şey

İşlemler neden bu sırayla yapılmalı? Çünkü tablo boyutu bu şekilde minimum düzeyde büyüyecektir (MVCC'yi hatırlayın!).

Dst'DEN SİL

Hayır, elbette sadece iki işlemle idare edebilirsiniz:

• kaldırmak (DELETE) genel olarak her şey
• eklemek hepsi yeni resimden

Ancak aynı zamanda MVCC sayesinde Tablonun boyutu tam olarak iki kat artacak! 1K güncelleme nedeniyle tablodaki kayıtların +10 milyon görüntüsünün alınması öylesine fazlalıktır ki...

Dst'yi KES

Daha deneyimli bir geliştirici, tabletin tamamının oldukça ucuza temizlenebileceğini biliyor:

• temiz (TRUNCATE) tüm masa
• eklemek hepsi yeni resimden

Yöntem etkili, bazen oldukça uygulanabilirama bir sorun var... Uzun bir süre 1M kayıt ekleyeceğiz, dolayısıyla bu kadar süre boyunca tabloyu boş bırakmayı göze alamayız (tek bir işleme sarmadan olacağı gibi).

Ve bu demek oluyor ki:

• başlıyoruz uzun süren işlem
• TRUNCATE dayatır Özel Erişim-engelleme
• yerleştirme işlemini uzun süredir yapıyoruz ve şu anda diğer herkes bile yapamam SELECT

Bir şeyler yolunda gitmiyor...

TABLOYU DEĞİŞTİR… YENİDEN ADLANDIR… / TABLOYU DÜŞÜR…

Bir alternatif, her şeyi ayrı bir yeni tabloya doldurmak ve ardından onu eskisinin yerine yeniden adlandırmaktır. Birkaç kötü küçük şey:

• hala da Özel Erişimönemli ölçüde daha az zaman olmasına rağmen
• bu tabloya ilişkin tüm sorgu planları/istatistikler sıfırlandı, ANALYZE'ı çalıştırmanız gerekiyor
• tüm yabancı anahtarlar bozuk (FK) masaya

Simon Riggs'in yapmayı önerdiği bir Devam Eden Çalışma yaması vardı. ALTER-istatistiklere ve FK'ye dokunmadan, ancak yeterli çoğunluğu toplamadan tablo gövdesini dosya düzeyinde değiştirme işlemi.

SİL, GÜNCELLE, EKLE

Bu nedenle, üç işlemin engellemesiz seçeneğine karar verdik. Neredeyse üç... Bu en etkili şekilde nasıl yapılır?

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

Aynı KLADR'de, değiştirilen tüm kayıtların ek olarak son işlemlerden geçirilmesi, normalleştirilmesi, anahtar kelimelerin vurgulanması ve gerekli yapılara indirgenmesi gerekir. Ama nasıl biliyorsun - tam olarak ne değiştisenkronizasyon kodunu karmaşıklaştırmadan, ideal olarak ona hiç dokunmadan?

Senkronizasyon sırasında yalnızca işleminizin yazma erişimi varsa, tüm değişiklikleri bizim için toplayacak bir tetikleyici kullanabilirsiniz:

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

Artık senkronizasyona başlamadan önce tetikleyicileri uygulayabiliriz (veya bunları aracılığıyla etkinleştirebiliriz. 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();

Daha sonra, ihtiyacımız olan tüm değişiklikleri günlük tablolarından sakin bir şekilde çıkarıyoruz ve bunları ek işleyiciler aracılığıyla çalıştırıyoruz.

3.3. Bağlantılı Kümeleri İçe Aktarma

Yukarıda kaynak ve hedefin veri yapılarının aynı olduğu durumları ele aldık. Peki ya harici bir sistemden yapılan yükleme, veritabanımızdaki depolama yapısından farklı bir formata sahipse?

Örnek olarak müşterilerin ve hesaplarının depolanmasını, yani klasik "çoka-bir" seçeneğini ele alalım:

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

Ancak harici bir kaynaktan indirme bize "hepsi bir arada" şeklinde geliyor:

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

Açıkçası, bu versiyonda müşteri verileri kopyalanabilir ve ana kayıt "hesaptı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 için sadece test verilerimizi ekleyeceğiz ancak şunu unutmayın: COPY daha verimli!

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

Öncelikle “gerçeklerimizin” atıfta bulunduğu “kesintileri” vurgulayalım. Bizim durumumuzda faturalar müşterilere atıfta bulunur:

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

Hesapları müşteri kimlikleriyle doğru şekilde ilişkilendirmek için öncelikle bu tanımlayıcıları bulmamız veya oluşturmamız gerekir. Altlarına alanlar ekleyelim:

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

Yukarıda açıklanan tablo senkronizasyon yöntemini küçük bir değişiklikle kullanalım - istemcileri "yalnızca eklemeli" olarak içe aktardığımız için hedef tablodaki hiçbir şeyi güncellemeyeceğiz veya silmeyeceğiz:

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

Aslında her şey ortada invoice_import Artık iletişim alanını doldurduk client_id, faturayı buna ekleyeceğiz.

Kaynak: habr.com