DBA: cekap mengatur penyegerakan dan import

Untuk pemprosesan kompleks set data yang besar (berbeza proses ETL: import, penukaran dan penyegerakan dengan sumber luaran) selalunya terdapat keperluan "ingat" buat sementara waktu dan segera proses sesuatu yang besar.

Tugas biasa seperti ini biasanya berbunyi seperti ini: "Di sini jabatan perakaunan dipunggah daripada bank pelanggan pembayaran terakhir diterima, anda perlu memuat naiknya dengan cepat ke tapak web dan memautkannya ke akaun anda"

Tetapi apabila jumlah "sesuatu" ini mula diukur dalam ratusan megabait, dan perkhidmatan itu mesti terus berfungsi dengan pangkalan data 24x7, banyak kesan sampingan timbul yang akan merosakkan hidup anda.

Untuk menanganinya dalam PostgreSQL (dan bukan sahaja di dalamnya), anda boleh menggunakan beberapa pengoptimuman yang akan membolehkan anda memproses semuanya dengan lebih pantas dan dengan penggunaan sumber yang lebih sedikit.

1. Ke mana hendak dihantar?

Mula-mula, mari kita tentukan tempat kita boleh memuat naik data yang ingin kita "proses".

1.1. Jadual sementara (JADUAL SEMENTARA)

Pada dasarnya, untuk jadual sementara PostgreSQL adalah sama seperti yang lain. Oleh itu, khurafat seperti "Semua yang ada disimpan hanya dalam ingatan, dan ia boleh berakhir". Tetapi terdapat juga beberapa perbezaan yang ketara.

"Ruang nama" anda sendiri untuk setiap sambungan ke pangkalan data

Jika dua sambungan cuba sambung pada masa yang sama CREATE TABLE x, maka seseorang pasti akan mendapat ralat bukan keunikan objek pangkalan data.

Tetapi jika kedua-duanya cuba untuk melaksanakan CREATE TEMPORARY TABLE x, maka kedua-duanya akan melakukannya seperti biasa, dan semua orang akan mendapat salinan anda meja. Dan tidak akan ada persamaan di antara mereka.

"Memusnahkan diri" apabila memutuskan sambungan

Apabila sambungan ditutup, semua jadual sementara dipadamkan secara automatik, jadi secara manual DROP TABLE x tiada gunanya melainkan...

Jika anda sedang berusaha pgbouncer dalam mod transaksi, maka pangkalan data terus percaya bahawa sambungan ini masih aktif, dan di dalamnya jadual sementara ini masih wujud.

Oleh itu, cuba menciptanya semula, daripada sambungan yang berbeza kepada pgbouncer, akan mengakibatkan ralat. Tetapi ini boleh dielakkan dengan menggunakan CREATE TEMPORARY TABLE IF NOT EXISTS x.

Benar, lebih baik tidak melakukan ini, kerana kemudian anda boleh "tiba-tiba" mencari di sana data yang tinggal dari "pemilik sebelumnya". Sebaliknya, adalah lebih baik untuk membaca manual dan melihat bahawa apabila membuat jadual adalah mungkin untuk menambah ON COMMIT DROP - iaitu, apabila transaksi selesai, jadual akan dipadamkan secara automatik.

Bukan replikasi

Kerana ia hanya milik sambungan tertentu, jadual sementara tidak direplikasi. Tetapi ini menghapuskan keperluan untuk merekodkan data berganda dalam timbunan + WAL, jadi INSERT/KEMASKINI/DELETE ke dalamnya adalah lebih pantas.

Tetapi memandangkan jadual sementara masih merupakan jadual "hampir biasa", ia tidak boleh dibuat pada replika sama ada. Sekurang-kurangnya buat masa ini, walaupun patch yang sepadan telah beredar untuk masa yang lama.

1.2. MEJA TIDAK DILOGOK

Tetapi apakah yang perlu anda lakukan, sebagai contoh, jika anda mempunyai beberapa jenis proses ETL yang menyusahkan yang tidak dapat dilaksanakan dalam satu transaksi, tetapi anda masih mempunyai pgbouncer dalam mod transaksi? ..

Atau aliran data sangat besar sehingga Tiada lebar jalur yang mencukupi pada satu sambungan daripada pangkalan data (baca, satu proses setiap CPU)?..

Atau beberapa operasi sedang dijalankan tak segerak dalam hubungan yang berbeza?..

Hanya ada satu pilihan di sini - buat sementara jadual bukan sementara. Pun, ya. Itu dia:

• mencipta jadual "saya sendiri" dengan nama rawak maksimum supaya tidak bersilang dengan sesiapa
• Mengekstrak: mengisinya dengan data daripada sumber luaran
• Ubahlah: ditukar, diisi dalam medan pemautan utama
• Muatkan: menuangkan data sedia ke dalam jadual sasaran
• telah memadamkan jadual "saya".

Dan sekarang - lalat dalam salap. sebenarnya, semua penulisan dalam PostgreSQL berlaku dua kali - pertama dalam WAL, kemudian ke dalam badan jadual/indeks. Semua ini dilakukan untuk menyokong ACID dan keterlihatan data yang betul antara COMMIT'kacang dan ROLLBACK'urus niaga batal.

Tetapi kita tidak memerlukan ini! Kami mempunyai keseluruhan proses Sama ada ia berjaya sepenuhnya atau tidak.. Tidak kira berapa banyak transaksi perantaraan yang akan berlaku - kami tidak berminat untuk "meneruskan proses dari tengah," terutamanya apabila tidak jelas di mana ia berada.

Untuk melakukan ini, pembangun PostgreSQL, kembali dalam versi 9.1, memperkenalkan perkara seperti meja UNLOGGED:

Dengan petunjuk ini, jadual dibuat sebagai tidak dilog. Data yang ditulis pada jadual tidak dilog tidak melalui log tulis ke hadapan (lihat Bab 29), menyebabkan jadual sedemikian bekerja lebih cepat daripada biasa. Walau bagaimanapun, mereka tidak terlepas daripada kegagalan; dalam kes kegagalan pelayan atau penutupan kecemasan, jadual tidak dilog dipangkas secara automatik. Selain itu, kandungan jadual yang tidak dilog tidak ditiru kepada pelayan hamba. Sebarang indeks yang dibuat pada jadual tidak dilog secara automatik menjadi tidak dilog.

Secara ringkasnya, ia akan menjadi lebih cepat, tetapi jika pelayan pangkalan data "jatuh", ia akan menjadi tidak menyenangkan. Tetapi berapa kerap perkara ini berlaku, dan adakah proses ETL anda tahu cara membetulkannya dengan betul "dari tengah" selepas "menggiatkan semula" pangkalan data?..

Jika tidak, dan kes di atas adalah serupa dengan anda, gunakan UNLOGGEDtetapi tidak pernah jangan dayakan atribut ini pada jadual sebenar, data yang anda sayangi.

1.3. ON COMMIT { PADAM BARIS | DROP}

Pembinaan ini membolehkan anda menentukan tingkah laku automatik apabila transaksi selesai semasa membuat jadual.

pada ON COMMIT DROP Saya sudah menulis di atas, ia menjana DROP TABLE, tetapi dengan ON COMMIT DELETE ROWS keadaan lebih menarik - ia dijana di sini TRUNCATE TABLE.

Oleh kerana keseluruhan infrastruktur untuk menyimpan perihalan meta jadual sementara adalah sama seperti jadual biasa, maka Penciptaan dan pemadaman berterusan jadual sementara membawa kepada "bengkak" jadual sistem yang teruk pg_class, pg_attribute, pg_attrdef, pg_depend,…

Sekarang bayangkan bahawa anda mempunyai pekerja dalam sambungan terus ke pangkalan data, yang membuka transaksi baharu setiap saat, mencipta, mengisi, memproses dan memadam jadual sementara... Akan ada lebihan sampah terkumpul dalam jadual sistem, dan ini akan menyebabkan brek tambahan untuk setiap operasi.

Secara umum, jangan lakukan ini! Dalam kes ini ia lebih berkesan CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS keluarkannya daripada kitaran urus niaga - kemudian pada permulaan setiap transaksi baharu, jadual sudah ada akan wujud (simpan panggilan CREATE), tetapi akan kosong, terima kasih kepada TRUNCATE (kami juga menyimpan panggilannya) apabila menyelesaikan transaksi sebelumnya.

1.4. SUKA...TERMASUK...

Saya menyebut pada mulanya bahawa salah satu kes penggunaan biasa untuk jadual sementara ialah pelbagai jenis import - dan pembangun dengan penat menyalin-tampal senarai medan jadual sasaran ke dalam pengisytiharan sementara...

Tetapi kemalasan adalah enjin kemajuan! sebab tu buat jadual baharu "berdasarkan sampel" ia boleh menjadi lebih mudah:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Oleh kerana anda kemudiannya boleh menjana banyak data ke dalam jadual ini, carian melaluinya tidak akan menjadi pantas. Tetapi ada penyelesaian tradisional untuk ini - indeks! Dan ya, jadual sementara juga boleh mempunyai indeks.

Oleh kerana, selalunya, indeks yang diperlukan bertepatan dengan indeks jadual sasaran, anda hanya boleh menulis LIKE target_table INCLUDING INDEXES.

Jika anda juga memerlukan DEFAULT-nilai (contohnya, untuk mengisi nilai kunci utama), anda boleh gunakan LIKE target_table INCLUDING DEFAULTS. Atau hanya - LIKE target_table INCLUDING ALL — menyalin lalai, indeks, kekangan,...

Tetapi di sini anda perlu memahami bahawa jika anda mencipta import jadual serta-merta dengan indeks, maka data akan mengambil masa yang lebih lama untuk dimuatkandaripada jika anda mula-mula mengisi semuanya, dan hanya kemudian menggulung indeks - lihat bagaimana ia melakukan ini sebagai contoh pg_dump.

Secara umum, RTFM!

2. Bagaimana cara menulis?

Biar saya katakan - gunakannya COPY-aliran bukannya "pek" INSERT, pecutan pada masa-masa tertentu. Anda juga boleh terus daripada fail pra-jana.

3. Bagaimana untuk memproses?

Jadi, mari kita biarkan intro kita kelihatan seperti ini:

• anda mempunyai jadual dengan data pelanggan yang disimpan dalam pangkalan data anda 1M rekod
• setiap hari pelanggan menghantar anda yang baru "imej" penuh
• dari pengalaman anda tahu bahawa dari semasa ke semasa tidak lebih daripada 10K rekod ditukar

Contoh klasik situasi sedemikian ialah pangkalan KLADR — terdapat banyak alamat secara keseluruhan, tetapi dalam setiap muat naik mingguan terdapat sangat sedikit perubahan (penamaan semula penempatan, menggabungkan jalan, penampilan rumah baharu) walaupun pada skala nasional.

3.1. Algoritma penyegerakan penuh

Untuk memudahkan, katakan anda tidak perlu menstruktur semula data - cuma bawa jadual ke dalam bentuk yang diingini, iaitu:

• keluarkan segala yang sudah tiada
• menaik taraf semua yang telah ada dan perlu dikemas kini
• masukkan semua yang belum berlaku

Mengapa operasi perlu dilakukan dalam susunan ini? Kerana ini adalah bagaimana saiz meja akan berkembang secara minimum (ingat MVCC!).

PADAM DARI dst

Tidak, sudah tentu anda boleh bertahan dengan hanya dua operasi:

• keluarkan (DELETE) semuanya secara umum
• masukkan semua dari imej baru

Tetapi pada masa yang sama, terima kasih kepada MVCC, Saiz meja akan meningkat dua kali ganda! Mendapatkan +1J imej rekod dalam jadual disebabkan kemas kini 10K adalah lebihan...

TRUNCATE dst

Pembangun yang lebih berpengalaman mengetahui bahawa keseluruhan tablet boleh dibersihkan dengan agak murah:

• очистить (TRUNCATE) seluruh meja
• masukkan semua dari imej baru

Kaedahnya berkesan, kadang-kadang agak boleh digunakan, tetapi ada masalah... Kami akan menambah rekod 1M untuk masa yang lama, jadi kami tidak mampu untuk membiarkan meja kosong selama ini (seperti yang akan berlaku tanpa membungkusnya dalam satu transaksi).

Yang bermaksud:

• kita mula transaksi jangka panjang
• TRUNCATE mengenakan Akses Eksklusif-menyekat
• kami melakukan sisipan untuk masa yang lama, dan orang lain pada masa ini tak boleh pun SELECT

Sesuatu tidak berjalan lancar...

ALTER JADUAL… NAMA SEMULA… / DROP JADUAL…

Alternatifnya ialah mengisi segala-galanya ke dalam jadual baharu yang berasingan, dan kemudian hanya menamakannya sebagai ganti yang lama. Beberapa perkara kecil yang jahat:

• masih juga Akses Eksklusif, walaupun masa yang lebih sedikit
• semua rancangan/statistik pertanyaan untuk jadual ini ditetapkan semula, perlu menjalankan ANALYZE
• semua kunci asing rosak (FK) ke meja

Terdapat tampung WIP daripada Simon Riggs yang mencadangkan untuk dibuat ALTER-operasi untuk menggantikan badan jadual di peringkat fail, tanpa menyentuh statistik dan FK, tetapi tidak mengumpul korum.

PADAM, KEMASKINI, MASUKKAN

Jadi, kami menyelesaikan pilihan tanpa menyekat tiga operasi. Hampir tiga... Bagaimana untuk melakukan ini dengan paling berkesan?

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

Dalam KLADR yang sama, semua rekod yang diubah mesti dijalankan tambahan melalui pemprosesan pasca - dinormalkan, kata kunci diserlahkan dan dikurangkan kepada struktur yang diperlukan. Tetapi bagaimana anda tahu - apa sebenarnya yang berubahtanpa merumitkan kod penyegerakan, idealnya tanpa menyentuhnya sama sekali?

Jika hanya proses anda mempunyai akses tulis pada masa penyegerakan, maka anda boleh menggunakan pencetus yang akan mengumpulkan semua perubahan untuk 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;

Kini kita boleh menggunakan pencetus sebelum memulakan penyegerakan (atau membolehkannya melalui 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();

Dan kemudian kami dengan tenang mengeluarkan semua perubahan yang kami perlukan daripada jadual log dan menjalankannya melalui pengendali tambahan.

3.3. Mengimport Set Berpaut

Di atas kami mempertimbangkan kes apabila struktur data sumber dan destinasi adalah sama. Tetapi bagaimana jika muat naik daripada sistem luaran mempunyai format yang berbeza daripada struktur storan dalam pangkalan data kami?

Mari kita ambil sebagai contoh penyimpanan pelanggan dan akaun mereka, pilihan "banyak-ke-satu" 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)
);

Tetapi muat turun daripada sumber luaran datang kepada kami dalam bentuk "semua dalam satu":

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

Jelas sekali, data pelanggan boleh diduplikasi dalam versi ini, dan rekod utama ialah "akaun":

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

Untuk model, kami hanya akan memasukkan data ujian kami, tetapi ingat - COPY lebih cekap!

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

Mula-mula, mari kita serlahkan "pemotongan" yang dirujuk oleh "fakta" kita. Dalam kes kami, invois merujuk kepada pelanggan:

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

Untuk mengaitkan akaun dengan betul dengan ID pelanggan, kami perlu mengetahui atau menjana pengecam ini terlebih dahulu. Mari tambah medan di bawahnya:

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

Mari gunakan kaedah penyegerakan jadual yang diterangkan di atas dengan pindaan kecil - kami tidak akan mengemas kini atau memadam apa-apa dalam jadual sasaran, kerana kami mengimport pelanggan "tambah sahaja":

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

Sebenarnya, semuanya ada dalam invoice_import Sekarang kami telah mengisi medan kenalan client_id, yang dengannya kami akan memasukkan invois.

Sumber: www.habr.com