ProHoster > blog > Gweinyddiaeth > DBA: trefnu cydamseriadau a mewnforion yn gymwys

DBA: trefnu cydamseriadau a mewnforion yn gymwys

Ar gyfer prosesu setiau data mawr yn gymhleth (gwahanol prosesau ETL: mewnforion, trawsnewidiadau a chydamseru â ffynhonnell allanol) yn aml mae angen “cofio” dros dro a phrosesu ar unwaith yn gyflym rhywbeth swmpus.

Mae tasg nodweddiadol o'r math hwn fel arfer yn swnio'n rhywbeth fel hyn: "Yn union yma adran gyfrifo wedi'i dadlwytho o'r banc cleient y taliadau diwethaf a dderbyniwyd, mae angen ichi eu huwchlwytho’n gyflym i’r wefan a’u cysylltu â’ch cyfrifon.”

Ond pan fydd cyfaint y “rhywbeth” hwn yn dechrau mesur mewn cannoedd o megabeit, a rhaid i'r gwasanaeth barhau i weithio gyda'r gronfa ddata 24x7, mae llawer o sgîl-effeithiau yn codi a fydd yn difetha'ch bywyd.
DBA: trefnu cydamseriadau a mewnforion yn gymwys
Er mwyn delio â nhw yn PostgreSQL (ac nid yn unig ynddo), gallwch ddefnyddio rhai optimizations a fydd yn caniatáu ichi brosesu popeth yn gyflymach a gyda llai o ddefnydd o adnoddau.

1. Ble i llong?

Yn gyntaf, gadewch i ni benderfynu lle gallwn uwchlwytho'r data yr ydym am ei “brosesu.”

1.1. Tablau dros dro (TABL DROS DRO)

Mewn egwyddor, ar gyfer tablau dros dro PostgreSQL yr un fath ag unrhyw un arall. Felly, ofergoelion fel “Dim ond yn y cof y mae popeth yn cael ei storio, a gall ddod i ben”. Ond mae yna hefyd nifer o wahaniaethau arwyddocaol.

Eich “gofod enw” eich hun ar gyfer pob cysylltiad â'r gronfa ddata

Os bydd dau gysylltiad yn ceisio cysylltu ar yr un pryd CREATE TABLE x, yna bydd rhywun yn bendant yn cael gwall an-unigrywiaeth gwrthrychau cronfa ddata.

Ond os yw'r ddau yn ceisio gweithredu CREATE TEMPORARY TABLE x, yna bydd y ddau yn ei wneud fel arfer, a bydd pawb yn cael eich copi byrddau. Ac ni fydd dim yn gyffredin rhyngddynt.

"Hunan-ddinistrio" wrth ddatgysylltu

Pan fydd y cysylltiad ar gau, caiff yr holl dablau dros dro eu dileu yn awtomatig, felly â llaw DROP TABLE x does dim pwynt heblaw...

Os ydych yn gweithio drwy pgbouncer yn y modd trafodiad, yna mae'r gronfa ddata yn parhau i gredu bod y cysylltiad hwn yn dal i fod yn weithredol, ac ynddo mae'r tabl dros dro hwn yn dal i fodoli.

Felly, bydd ceisio ei greu eto, o gysylltiad gwahanol i pgbouncer, yn arwain at wall. Ond gellir osgoi hyn trwy ddefnyddio CREATE TEMPORARY TABLE IF NOT EXISTS x.

Yn wir, mae'n well peidio â gwneud hyn beth bynnag, oherwydd yna gallwch chi "yn sydyn" ddod o hyd i'r data sy'n weddill gan y "perchennog blaenorol". Yn hytrach, mae'n llawer gwell darllen y llawlyfr a gweld ei bod yn bosibl ychwanegu wrth greu tabl ON COMMIT DROP - hynny yw, pan fydd y trafodiad wedi'i gwblhau, bydd y tabl yn cael ei ddileu yn awtomatig.

Peidio â dyblygu

Oherwydd eu bod yn perthyn i gysylltiad penodol yn unig, ni chaiff tablau dros dro eu hailadrodd. Ond mae hyn yn dileu'r angen i gofnodi data ddwywaith mewn pentwr + WAL, felly mewnosoder/DIWEDDARU/DILEU ynddo yn gynt o lawer.

Ond gan fod bwrdd dros dro yn dal i fod yn dabl “bron yn gyffredin”, ni ellir ei greu ar replica ychwaith. O leiaf am y tro, er bod y darn cyfatebol wedi bod yn cylchredeg ers amser maith.

1.2. TABL ANGHOFIEDIG

Ond beth ddylech chi ei wneud, er enghraifft, os oes gennych chi ryw fath o broses ETL feichus na ellir ei gweithredu o fewn un trafodiad, ond mae gennych chi o hyd pgbouncer yn y modd trafodiad? ..

Neu mae'r llif data mor fawr Nid oes digon o led band ar un cysylltiad o gronfa ddata (darllenwch, un broses fesul CPU)?..

Neu mae rhai llawdriniaethau yn digwydd yn asyncronig mewn gwahanol gysylltiadau?..

Dim ond un opsiwn sydd yma - creu tabl nad yw'n un dros dro dros dro. Pun, ie. Hynny yw:

  • creu tablau “fy hun” gydag enwau ar hap i'r eithaf er mwyn peidio â chroestorri ag unrhyw un
  • Detholiad: eu llenwi â data o ffynhonnell allanol
  • Trawsnewid: trosi, llenwi mewn meysydd cysylltu allweddol
  • Llwyth: arllwys data parod i'r tablau targed
  • dileu “fy” tablau

Ac yn awr - pryf yn yr eli. Yn wir, mae pob ysgrifen yn PostgreSQL yn digwydd ddwywaith - gyntaf yn WAL, yna i mewn i'r tabl/cyrff mynegai. Gwneir hyn i gyd i gefnogi ACID a chywiro gwelededd data rhwng COMMIT' nutty a ROLLBACK' trafodion null.

Ond nid oes angen hyn arnom! Mae gennym y broses gyfan Naill ai roedd yn gwbl lwyddiannus neu nid oedd.. Nid oes ots faint o drafodion canolradd a fydd - nid oes gennym ddiddordeb mewn “parhau â’r broses o’r canol,” yn enwedig pan nad yw’n glir ble’r oedd.

I wneud hyn, cyflwynodd datblygwyr PostgreSQL, yn ôl yn fersiwn 9.1, y fath beth â Byrddau HEB LOGGED:

Gyda'r arwydd hwn, mae'r tabl yn cael ei greu fel un heb ei logio. Nid yw data a ysgrifennir i dablau heb eu logio yn mynd trwy'r log ysgrifennu ymlaen llaw (gweler Pennod 29), gan achosi tablau o'r fath i gweithio'n llawer cyflymach nag arfer. Fodd bynnag, nid ydynt yn imiwn i fethiant; rhag ofn y bydd y gweinydd yn methu neu'n cau i lawr mewn argyfwng, bwrdd heb ei logio wedi'i gwtogi'n awtomatig. Yn ogystal, mae cynnwys y tabl heb ei logio heb ei ailadrodd i weinyddion caethion. Mae unrhyw fynegeion sy'n cael eu creu ar dabl heb ei logio yn dod yn ddi-log yn awtomatig.

Yn fyr, bydd yn llawer cyflymach, ond os bydd gweinydd y gronfa ddata yn “cwympo”, bydd yn annymunol. Ond pa mor aml mae hyn yn digwydd, ac a yw eich proses ETL yn gwybod sut i gywiro hyn yn gywir “o'r canol” ar ôl “adfywio” y gronfa ddata?..

Os na, ac mae'r achos uchod yn debyg i'ch un chi, defnyddiwch UNLOGGEDond byth peidiwch â galluogi'r nodwedd hon ar dablau go iawn, mae'r data ohono yn annwyl i chi.

1.3. AR YMRWYMIAD { DILEU RHESi | DROP}

Mae'r lluniad hwn yn caniatáu ichi nodi ymddygiad awtomatig pan fydd trafodiad wedi'i gwblhau wrth greu tabl.

Про ON COMMIT DROP Ysgrifennais uchod eisoes, mae'n cynhyrchu DROP TABLE, ond gyda ON COMMIT DELETE ROWS mae'r sefyllfa'n fwy diddorol - fe'i cynhyrchir yma TRUNCATE TABLE.

Gan fod y seilwaith cyfan ar gyfer storio meta-ddisgrifiad bwrdd dros dro yn union yr un fath â bwrdd rheolaidd, yna Mae creu a dileu tablau dros dro yn gyson yn arwain at “chwydd” difrifol mewn tablau system pg_class, pg_attribute, pg_attrdef, pg_depend,…

Nawr dychmygwch fod gennych weithiwr ar gysylltiad uniongyrchol â'r gronfa ddata, sy'n agor trafodiad newydd bob eiliad, yn creu, yn llenwi, yn prosesu ac yn dileu bwrdd dros dro... Bydd gormodedd o garbage yn cronni yn nhablau'r system, a bydd hyn yn achosi breciau ychwanegol ar gyfer pob llawdriniaeth.

Yn gyffredinol, peidiwch â gwneud hyn! Yn yr achos hwn, mae'n llawer mwy effeithiol CREATE TEMPORARY TABLE x ... ON COMMIT DELETE ROWS ei gymryd allan o'r cylch trafodion - yna erbyn dechrau pob trafodiad newydd mae'r tablau eisoes bydd yn bodoli (arbed galwad CREATE), ond bydd yn wag, Diolch i TRUNCATE (fe wnaethom hefyd arbed ei alwad) wrth gwblhau'r trafodiad blaenorol.

1.4. HOFFI...GAN GYNNWYS...

Soniais ar y dechrau mai un o'r achosion defnydd nodweddiadol ar gyfer tablau dros dro yw gwahanol fathau o fewnforion - ac mae'r datblygwr yn blino'n copïo-gludo'r rhestr o feysydd yn y tabl targed i ddatganiad ei ...

Ond diogi yw peiriant y cynnydd! Dyna pam creu tabl newydd “yn seiliedig ar sampl” gall fod yn llawer symlach:

CREATE TEMPORARY TABLE import_table(
  LIKE target_table
);

Gan y gallwch chi wedyn gynhyrchu llawer o ddata i'r tabl hwn, ni fydd chwilio trwyddo byth yn gyflym. Ond mae yna ateb traddodiadol i hyn - mynegeion! Ac, ie, gall tabl dros dro hefyd gael mynegeion.

Gan fod y mynegeion gofynnol yn aml yn cyd-fynd â mynegeion y tabl targed, gallwch chi ysgrifennu'n syml LIKE target_table INCLUDING INDEXES.

Os oes angen hefyd DEFAULT-values ​​​​(er enghraifft, i lenwi'r gwerthoedd allweddol cynradd), gallwch ddefnyddio LIKE target_table INCLUDING DEFAULTS. Neu yn syml - LIKE target_table INCLUDING ALL — copïau o ragosodiadau, mynegeion, cyfyngiadau,...

Ond yma mae angen i chi ddeall hynny os gwnaethoch chi greu tabl mewnforio ar unwaith gyda mynegeion, yna bydd y data yn cymryd mwy o amser i lwythonag os ydych chi'n llenwi popeth yn gyntaf, a dim ond wedyn rholio'r mynegeion i fyny - edrychwch ar sut mae'n gwneud hyn fel enghraifft tud_dymp.

Yn gyffredinol RTFM!

2. Sut i ysgrifennu?

Gadewch i mi ddweud - defnyddiwch hi COPY-lif yn lle “pecyn” INSERT, cyflymiad ar adegau. Gallwch hyd yn oed yn uniongyrchol o ffeil a gynhyrchwyd ymlaen llaw.

3. Sut i brosesu?

Felly, gadewch i ni edrych rhywbeth fel hyn i'n cyflwyniad:

  • mae gennych dabl gyda data cleient wedi'i storio yn eich cronfa ddata cofnodion 1M
  • bob dydd mae cleient yn anfon un newydd atoch llawn "delwedd"
  • o brofiad rydych chi'n gwybod hynny o bryd i'w gilydd dim mwy na 10K o gofnodion yn cael eu newid

Enghraifft glasurol o sefyllfa o'r fath yw sylfaen KLADR — mae llawer o gyfeiriadau i gyd, ond ym mhob uwchlwythiad wythnosol ychydig iawn o newidiadau (ailenwi aneddiadau, cyfuno strydoedd, ymddangosiad tai newydd) hyd yn oed ar raddfa genedlaethol.

3.1. Algorithm cydamseru llawn

Er mwyn symlrwydd, gadewch i ni ddweud nad oes angen i chi hyd yn oed ailstrwythuro'r data - dewch â'r tabl i'r ffurf a ddymunir, hynny yw:

  • tynnu popeth nad yw'n bodoli mwyach
  • adnewyddu popeth sydd eisoes yn bodoli ac sydd angen ei ddiweddaru
  • mewnosoder popeth sydd heb ddigwydd eto

Pam y dylid gwneud y gweithrediadau yn y drefn hon? Oherwydd dyma sut y bydd maint y bwrdd yn tyfu cyn lleied â phosibl (cofiwch MVCC!).

DILEU O dst

Na, wrth gwrs gallwch chi fynd heibio gyda dim ond dwy lawdriniaeth:

  • tynnu (DELETE) popeth yn gyffredinol
  • mewnosoder i gyd o'r ddelwedd newydd

Ond ar yr un pryd, diolch i MVCC, Bydd maint y tabl yn cynyddu'n union ddwywaith! Mae cael delweddau +1M o gofnodion yn y tabl oherwydd diweddariad 10K yn gymaint o ddiswyddiad...

TRWYTHO dst

Mae datblygwr mwy profiadol yn gwybod y gellir glanhau'r dabled gyfan yn eithaf rhad:

  • i glirio (TRUNCATE) y bwrdd cyfan
  • mewnosoder i gyd o'r ddelwedd newydd

Mae'r dull yn effeithiol, weithiau yn eithaf cymwys, ond mae yna broblem... Byddwn yn ychwanegu cofnodion 1M am amser hir, felly ni allwn fforddio gadael y bwrdd yn wag am yr holl amser hwn (fel fydd yn digwydd heb ei lapio mewn un trafodiad).

Sy'n meddwl:

  • rydyn ni'n dechrau trafodiad hir-redeg
  • TRUNCATE yn gosod Mynediad Unigryw-blocio
  • rydym yn gwneud y mewnosodiad am amser hir, a phawb arall ar yr adeg hon methu hyd yn oed SELECT

Mae rhywbeth ddim yn mynd yn dda...

TABL ALTER… AILENWIO… / DROP TABL…

Dewis arall yw llenwi popeth i fwrdd newydd ar wahân, ac yna ei ailenwi yn lle'r hen un. Cwpl o bethau bach cas:

  • dal hefyd Mynediad Unigryw, er yn sylweddol llai o amser
  • mae'r holl gynlluniau/ystadegau ymholiad ar gyfer y tabl hwn yn cael eu hailosod, angen rhedeg DADANSODDIAD
  • holl allweddi tramor yn cael eu torri (FK) at y bwrdd

Roedd darn WIP gan Simon Riggs yn awgrymu gwneud ALTER-gweithrediad i ddisodli'r corff bwrdd ar lefel y ffeil, heb gyffwrdd ag ystadegau a FK, ond nid oedd yn casglu cworwm.

DILEU, DIWEDDARIAD, MEWNOSOD

Felly, rydym yn setlo ar yr opsiwn di-flocio o dri gweithrediad. Bron i dri... Sut i wneud hyn yn fwyaf effeithiol?

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

Yn yr un KLADR, rhaid i bob cofnod sydd wedi'i newid gael ei redeg hefyd trwy ôl-brosesu - wedi'i normaleiddio, wedi'i amlygu i eiriau allweddol, a'i leihau i'r strwythurau gofynnol. Ond sut ydych chi'n gwybod - beth yn union newidioddheb gymhlethu'r cod cydamseru, yn ddelfrydol heb ei gyffwrdd o gwbl?

Os mai dim ond eich proses sydd â mynediad ysgrifennu ar adeg y cysoni, yna gallwch ddefnyddio sbardun a fydd yn casglu'r holl newidiadau i ni:

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

Nawr gallwn gymhwyso sbardunau cyn dechrau cydamseru (neu eu galluogi trwy 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();

Ac yna rydym yn tynnu'n dawel yr holl newidiadau sydd eu hangen arnom o'r tablau log a'u rhedeg trwy drinwyr ychwanegol.

3.3. Mewnforio Setiau Cysylltiedig

Uchod, fe wnaethom ystyried achosion pan fo strwythurau data'r ffynhonnell a chyrchfan yr un peth. Ond beth os oes gan y llwythiad o system allanol fformat gwahanol i'r strwythur storio yn ein cronfa ddata?

Gadewch i ni gymryd fel enghraifft storio cleientiaid a'u cyfrifon, yr opsiwn "llawer-i-un" clasurol:

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

Ond mae'r lawrlwythiad o ffynhonnell allanol yn dod atom ar ffurf "i gyd mewn un":

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

Yn amlwg, gellir dyblygu data cwsmeriaid yn y fersiwn hon, a'r prif gofnod yw “cyfrif”:

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

Ar gyfer y model, yn syml byddwn yn mewnosod ein data prawf, ond cofiwch - COPY yn fwy effeithlon!

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

Yn gyntaf, gadewch i ni dynnu sylw at y “toriadau” hynny y mae ein “ffeithiau” yn cyfeirio atynt. Yn ein hachos ni, mae anfonebau yn cyfeirio at gwsmeriaid:

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

Er mwyn cysylltu cyfrifon yn gywir â rhifau adnabod cwsmeriaid, yn gyntaf mae angen i ni ddarganfod neu gynhyrchu'r dynodwyr hyn. Gadewch i ni ychwanegu meysydd oddi tanynt:

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

Gadewch i ni ddefnyddio'r dull cydamseru tabl a ddisgrifir uchod gyda diwygiad bach - ni fyddwn yn diweddaru nac yn dileu unrhyw beth yn y tabl targed, oherwydd rydym yn mewnforio cleientiaid “atodiad-yn-unig”:

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

Mewn gwirionedd, mae popeth i mewn invoice_import Nawr mae gennym y maes cyswllt wedi'i lenwi client_id, y byddwn yn mewnosod yr anfoneb ag ef.

Ffynhonnell: hab.com

Ychwanegu sylw