DBA: синхрондоштурууну жана импортту компетенттүү уюштуруу

Чоң маалымат топтомун комплекстүү иштетүү үчүн (ар кандай ETL процесстери: импорттоо, конверсиялоо жана тышкы булак менен синхрондоштуруу) көп учурда муктаждык бар убактылуу "эсте" жана дароо тез иштетүү көлөмдүү нерсе.

Мындай типтүү тапшырма адатта төмөнкүдөй угулат: "Ушул жерде бухгалтердик бөлүм кардар банктан түшүрүлгөн акыркы кабыл алынган төлөмдөр, сиз аларды тез арада веб-сайтка жүктөшүңүз жана аларды аккаунттарыңызга байланыштырышыңыз керек.

Бирок бул "бир нерсенин" көлөмү жүздөгөн мегабайттар менен өлчөй баштаганда жана кызмат 24x7 маалымат базасы менен иштөөнү улантуу керек болгондо, жашооңузду буза турган көптөгөн терс таасирлер пайда болот.

Алар менен PostgreSQLде (жана анда гана эмес) күрөшүү үчүн, сиз баарын тезирээк жана азыраак ресурс керектөө менен иштетүүгө мүмкүндүк берген кээ бир оптималдаштырууларды колдонсоңуз болот.

1. Кайда жөнөтүү керек?

Биринчиден, биз "иштегибиз" келген маалыматтарды кайда жүктөй аларыбызды чечели.

1.1. Убактылуу таблицалар (УБАКЫТТЫ ТАБЛИЦ)

Негизи, PostgreSQL үчүн убактылуу таблицалар башкаларга окшош. Ошондуктан, ырым-жырымдарга жагат "Ал жерде бардыгы эстутумда гана сакталат жана ал бүтүшү мүмкүн". Бирок бир нече олуттуу айырмачылыктар да бар.

Маалыматтар базасына ар бир туташуу үчүн өзүңүздүн "ат мейкиндигиңиз"

Эгерде эки байланыш бир эле учурда кошулууга аракет кылса CREATE TABLE x, анда кимдир бирөө сөзсүз алат уникалдуу эмес ката маалымат базасы объекттери.

Бирок экөө тең аткарууга аракет кылса CREATE TEMPORARY TABLE x, анда экөө тең муну кадимкидей аткарышат жана баары алышат сиздин көчүрмөңүз столдор. Жана алардын ортосунда жалпы эч нерсе болбойт.

Ажыратууда "өзүн-өзү жок кылуу"

Туташуу жабылганда, бардык убактылуу таблицалар автоматтык түрдө жок кылынат, ошондуктан кол менен DROP TABLE x башка эч кандай мааниси жок ...

Эгер сиз иштеп жаткан болсоңуз pgbouncer транзакция режиминде, анда маалымат базасы бул байланыш дагы эле активдүү экенине ишене берет жана анда бул убактылуу таблица дагы эле бар.

Ошондуктан, аны башка туташуудан pgbouncerге кайра түзүүгө аракет кылуу катага алып келет. Бирок бул колдонуу менен айланып өтүүгө болот CREATE TEMPORARY TABLE IF NOT EXISTS x.

Ырас, баары бир муну кылбаганыңыз жакшы, анткени анда сиз "мурунку ээсинен" калган маалыматтарды "капысынан" таба аласыз. Анын ордуна, колдонмону окуп чыгып, таблица түзүүдө аны кошууга болоорун көрүү алда канча жакшы ON COMMIT DROP - башкача айтканда, транзакция аяктаганда, таблица автоматтык түрдө жок кылынат.

Репликацияланбоо

Алар белгилүү бир байланышка гана таандык болгондуктан, убактылуу таблицалар кайталанбайт. Бирок бул маалыматтарды эки жолу жаздыруу зарылдыгын жок кылат үймөктө + WAL, андыктан ага INSERT/UPDATE/DELETE кыйла тезирээк болот.

Бирок убактылуу таблица дагы эле "дээрлик кадимки" таблица болгондуктан, аны репликада да түзүү мүмкүн эмес. Тиешелүү жамаачы көптөн бери айланып келе жатса да, жок дегенде азыр.

1.2. БӨЛГӨН ТАБЛИЦ

Бирок, мисалы, сизде бир транзакциянын ичинде ишке ашырылбай турган оор ETL процесси болсо, эмне кылышыңыз керек, бирок сизде дагы эле бар. pgbouncer транзакция режиминде? ..

Же маалымат агымы ушунчалык чоң Бир туташууда өткөрүү жөндөмдүүлүгү жетишсиз маалымат базасынан (окуу, ар бир CPU үчүн бир процесс)?..

Же кандайдыр бир операциялар жүрүп жатат асинхрондуу ар кандай байланышта?..

Бул жерде бир гана вариант бар - убактылуу убактылуу эмес таблица түзүү. Пун, ооба. Башкача айтканда:

• эч ким менен кесилишпес үчүн, максималдуу кокус аттары менен "өзүмдүн" таблицаларды түздү
• Чыгаруу: аларды тышкы булактан алынган маалыматтар менен толтурду
• өзгөртүүсү: айландырылган, негизги шилтеме талаалары толтурулган
• жүк: даяр маалыматтарды максаттуу таблицаларга төктү
• "менин" таблицалары жок кылынды

Ал эми азыр - майга чымын. Мааниси боюнча, PostgreSQLде бардык жазуулар эки жолу болот - WAL биринчи, андан кийин таблица/индекс органдарына. Мунун баары ACID колдоо жана ортосунда туура маалымат көрүнүү үчүн жасалат COMMIT'жаңгак жана ROLLBACK'нөл транзакциялар.

Бирок бизге мунун кереги жок! Бизде бүт процесс бар Же ал толугу менен ийгиликтүү болду, же болгон жок.. Канча аралык транзакциялар болору маанилүү эмес - биз "процессти ортодон улантууга" кызыкдар эмеспиз, айрыкча анын кайда экени белгисиз болгондо.

Бул үчүн, PostgreSQL иштеп чыгуучулары 9.1 версиясында мындай нерсени киргизишти LOGGED столдор:

Бул көрсөткүч менен таблица журналдан чыгарылбаган катары түзүлөт. Катталбаган таблицаларга жазылган маалыматтар алдын ала жазуу журналынан өтпөйт (29-бөлүмдү караңыз), мындай таблицалар демейдегиден алда канча тезирээк иштешет. Бирок, алар ийгиликсиздикке каршы эмес; сервер иштебей калса же авариялык өчүрүлгөн учурда, журналдан чыгарылбаган таблица автоматтык түрдө кыскартылат. Кошумчалай кетсек, журналдан чыгарылган таблицанын мазмуну кайталанган эмес кул серверлерине. Катталбаган таблицада түзүлгөн бардык индекстер автоматтык түрдө журналдан чыгарылат.

Кыскача айтканда, ал алда канча тезирээк болот, бирок маалымат базасы сервери "түшүп кетсе", ал жагымсыз болот. Бирок бул канчалык көп болот жана ETL процессиңиз маалымат базасын "жанданткандан" кийин муну "ортодон" кантип туура оңдоону билеби?..

Эгерде жок болсо жана жогорудагы окуя сиздикине окшош болсо, колдонуңуз UNLOGGEDБирок эч качан чыныгы таблицаларда бул атрибутту иштетпеңиз, маалыматтар сиз үчүн кымбат.

1.3. КОМИТЕТТЕ { КАТТАРДЫ ЖОК | TROP}

Бул конструкция таблица түзүүдө транзакция аяктаганда автоматтык жүрүм-турумду көрсөтүүгө мүмкүндүк берет.

боюнча ON COMMIT DROP Мен жогоруда жаздым, ал жаратат DROP TABLE, Бирок менен ON COMMIT DELETE ROWS жагдай кызыктуураак - бул жерде түзүлгөн TRUNCATE TABLE.

Убактылуу таблицанын мета-сыпаттамасын сактоонун бардык инфраструктурасы кадимки таблицаныкына окшош болгондуктан, анда Убактылуу таблицаларды тынымсыз түзүү жана жок кылуу системалык таблицалардын катуу "шишишине" алып келет pg_class, pg_attribute, pg_attrdef, pg_depend,…

Эми сизде ар бир секунд сайын жаңы транзакцияны ачкан, убактылуу таблицаны түзүп, толтуруп, иштетип жана жок кылган маалымат базасына түз байланышта жумушчу бар деп элестетиңиз... Системалык таблицаларда ашыкча таштандылар топтолуп калат жана бул ар бир операция үчүн кошумча тормоздорду пайда кылат.

Жалпысынан алганда, мындай кылба! Бул учурда ал алда канча натыйжалуу болот CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS аны транзакция циклинен алып салыңыз - анда ар бир жаңы транзакциянын башталышында таблицалар мурунтан эле бар болот (чалууну сактоо CREATE), бирок бош болот, рахмат TRUNCATE мурунку транзакцияны аяктаганда (биз анын чалуусун да сактадык).

1.4. ЖАКШЫ...КОНУНДА...

Мен башында убактылуу таблицаларды колдонуунун типтүү учурларынын бири импорттун ар кандай түрлөрү экенин айттым - жана иштеп чыгуучу чарчап-чаалыкпай максаттуу таблицанын талааларынын тизмесин өзүнүн убактылуу таблицасынын декларациясына көчүрөт...

Бирок жалкоолук прогресстин кыймылдаткычы! Ошол үчүн "үлгүгө негизделген" жаңы таблица түзүү алда канча жөнөкөй болушу мүмкүн:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Сиз андан кийин бул таблицага көптөгөн маалыматтарды түзө алсаңыз, аны издөө эч качан тез болбойт. Бирок мунун салттуу чечими бар - индекстер! Жана Ооба, убактылуу таблицада индекстер да болушу мүмкүн.

Көбүнчө талап кылынган индекстер максаттуу таблицанын индекстери менен дал келгендиктен, сиз жөн гана жаза аласыз LIKE target_table INCLUDING INDEXES.

Сизге да керек болсо DEFAULT- баалуулуктарды (мисалы, негизги ачкыч маанилерин толтуруу үчүн) колдоно аласыз LIKE target_table INCLUDING DEFAULTS. Же жөн эле - LIKE target_table INCLUDING ALL — демейкилерди, индекстерди, чектөөлөрдү,...

Бирок бул жерде сиз жараткан болсо, түшүнүү керек индекстер менен дароо таблицаны импорттоо, анда маалыматтарды жүктөө үчүн көбүрөөк убакыт талап кылынатЭгер сиз адегенде баарын толтурсаңыз, андан кийин гана индекстерди жыйып алсаңыз - муну мисал катары караңыз pg_dump.

Жалпысынан, RTFM!

2. Кантип жазуу керек?

Мен жөн гана айта кетейин - колдонуңуз COPY- "пакеттин" ордуна агым INSERT, кээде ылдамдатуу. Сиз түз эле алдын ала түзүлгөн файлдан да аласыз.

3. Кантип иштетүү керек?

Андыктан, келгиле, биздин киришүү төмөнкүдөй болсун:

• Сиздин маалымат базаңызда сакталган кардар маалыматтары бар таблица бар 1 миллион жазуу
• күн сайын кардар сизге жаңысын жөнөтөт толук "сүрөт"
• тажрыйбадан сиз мезгил-мезгили менен билесиз 10 миңден ашык жазуулар өзгөртүлбөйт

Мындай кырдаалдын классикалык мисалы болуп саналат KLADR базасы — жалпысынан даректер көп, бирок ар бир жумалык жүктөөдө республикалык масштабда да аз гана өзгөрүүлөр (конуштардын атын өзгөртүү, көчөлөрдү бириктирүү, жаңы үйлөрдүн көрүнүшү) болот.

3.1. Толук синхрондоштуруу алгоритми

Жөнөкөйлүк үчүн, сизге маалыматтарды реструктуризациялоонун деле кереги жок дейли - таблицаны каалаган формага келтириңиз, башкача айтканда:

• жок кылуу мындан ары жок нерсенин баары
• жаңыртуу бар болгон жана жаңыланууга муктаж болгон нерселердин баары
• коюу баары али боло элек

Эмне үчүн операциялар ушундай тартипте жасалышы керек? Анткени үстөлдүн көлөмү минимум түрдө өсөт (MVCC эсте!).

DELETE FROM dst

Жок, албетте, сиз эки гана операция менен кете аласыз:

• жок кылуу (DELETE) жалпысынан бардыгы
• коюу баары жаңы образдан

Бирок, ошол эле учурда, MVCC урматында, Столдун көлөмү так эки эсе көбөйөт! 1K жаңыртуудан улам таблицадагы +10M жазуулардын сүрөттөрүн алуу өтө ашыкча...

TRUNCATE дст

Тажрыйбалуу иштеп чыгуучу бүт планшетти абдан арзан тазалоого болорун билет:

• таза (TRUNCATE) бүт таблицаны
• коюу баары жаңы образдан

Метод эффективдүү, кээде абдан ылайыктуу, бирок көйгөй бар... Биз 1M жазууларды көп убакытка кошобуз, ошондуктан биз үстөлдү ушул убакыт бою бош калтыра албайбыз (бир транзакцияга ороп койбостон ушундай болот).

Бул дегенди билдирет:

• баштайбыз узак мөөнөттүү транзакция
• TRUNCATE таңуулайт AccessExclusive- бөгөт коюу
• биз кыстарууну көпкө жасайбыз жана ушул убакта башкалар да албайт SELECT

Бир нерсе жакшы болбой жатат...

ТАБЛИЦАНЫ ӨЗГӨРТҮҮ… Атын ӨЗГӨРТҮҮ… / ТАБЛИЦАНЫ ТҮШТҮҮ…

Альтернатива - бардыгын өзүнчө жаңы таблицага толтуруу, андан кийин жөн гана эскинин ордуна анын атын өзгөртүү. Бир нече жагымсыз кичинекей нерселер:

• дагы деле AccessExclusiveбир кыйла аз убакыт болсо да
• Бул таблица үчүн бардык суроо пландары/статистика баштапкы абалга келтирилген, ANALYZE иштетүү керек
• бардык чет ачкычтар бузулган (FK) үстөлгө

жасоону сунуш кылган Саймон Риггс WIP патч бар болчу ALTER-статистикага жана ФКга тийбестен, файлдын деңгээлинде таблицанын корпусун алмаштыруу операциясы, бирок кворумду чогулткан жок.

ЖОЮУ, ЖАҢЫРТУУ, КЫШЫРУУ

Ошентип, биз үч операциянын бөгөттөлбөгөн вариантына токтолобуз. Дээрлик үч... Муну кантип эң натыйжалуу жасоо керек?

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

Ошол эле KLADRде бардык өзгөртүлгөн жазуулар кошумча түрдө пост-процессинг аркылуу ишке ашырылышы керек - нормалдаштырылган, ачкыч сөздөр баса белгиленип, талап кылынган структураларга кыскартылган. Бирок сен кайдан билесиң - так эмне өзгөрдүсинхрондоштуруу кодун татаалдаштырбастан, идеалдуу түрдө ага такыр тийбестен?

Эгерде процессиңиздин синхрондоштуруу учурунда жазуу мүмкүнчүлүгү гана болсо, анда сиз биз үчүн бардык өзгөртүүлөрдү чогулта турган триггерди колдонсоңуз болот:

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

Эми биз синхрондоштурууну баштоодон мурун триггерлерди колдоно алабыз (же аларды 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();

Анан биз тынч түрдө журнал таблицаларынан керектүү өзгөрүүлөрдүн баарын чыгарып, аларды кошумча иштеткичтер аркылуу иштетебиз.

3.3. Шилтемеленген топтомдорду импорттоо

Жогоруда биз булактын жана көздөгөн жердин маалымат структуралары бирдей болгон учурларды карадык. Бирок тышкы тутумдан жүктөөнүн форматы биздин маалымат базабыздагы сактоо структурасынан башкача болсочу?

Мисал катары кардарлардын жана алардын эсептеринин сакталышын, классикалык "көптөн бирге" опциясын алалы:

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

Бирок тышкы булактан жүктөө бизге "баары бирде" түрүндө келет:

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

Албетте, кардарлардын маалыматтары бул версияда кайталанышы мүмкүн жана негизги жазуу "эсеп" болуп саналат:

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

Модель үчүн биз жөн гана сыноо маалыматтарыбызды киргизебиз, бирок эсиңизде болсун - COPY натыйжалуураак!

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

Биринчиден, келгиле, биздин «фактыларыбыз» тиешелүү болгон «кесектерди» белгилейли. Биздин учурда, эсеп-фактуралар кардарларга тиешелүү:

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

Эсептерди кардар идентификаторлору менен туура байланыштыруу үчүн, адегенде бул идентификаторлорду таап же генерациялашыбыз керек. Алардын астына талааларды кошолу:

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

Келгиле, жогоруда сүрөттөлгөн жадыбалды синхрондоштуруу ыкмасын кичине түзөтүү менен колдонолу - биз максаттуу таблицада эч нерсени жаңыртпайбыз же жок кылбайбыз, анткени биз кардарларды "тиркелет" гана импорттойбуз:

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

Чынында, баары бар invoice_import Азыр бизде байланыш талаасы толтурулду client_id, аны менен биз эсеп-фактураны киргизебиз.

Source: www.habr.com